OLEDB Newbie - how to ignore deleted rows?

[Richard]

Hi,

I am importing data from FoxPro 8 tables into SQL Server 2005, using the
Microsoft OLEDB driver for Visual FoxPro 8. The development
environment is VS2005, but that does not matter, it is the OLEDB
component behaviour I need to understand.

My question is this:
Can I set it to ignore deleted rows when I ask the component to return a
whole table? If I use a SQL Statement like SELECT * FROM tblFoxData then
I get the correct rows. If I connect using the table name in the
component, then I get the deleted rows as well, with no way then of
ignoring them.
I would prefer to use the non-SQL route, as tests are showing it is
faster, and my tables are >5 million rows each.

Sorry to trouble you all, but having scanned the help, and MSDN and
googled everywhere I still can't find the answer to this one.

Thanks in advance,

Richard R.

[Dan Freeman]

Send VFP the command SET DELETED ON.

Dan

Post by Richard
Hi,
I am importing data from FoxPro 8 tables into SQL Server 2005, using
the Microsoft OLEDB driver for Visual FoxPro 8. The development
environment is VS2005, but that does not matter, it is the OLEDB
component behaviour I need to understand.
Can I set it to ignore deleted rows when I ask the component to
return a whole table? If I use a SQL Statement like SELECT * FROM
tblFoxData then I get the correct rows. If I connect using the table
name in the component, then I get the deleted rows as well, with no
way then of ignoring them.
I would prefer to use the non-SQL route, as tests are showing it is
faster, and my tables are >5 million rows each.
Sorry to trouble you all, but having scanned the help, and MSDN and
googled everywhere I still can't find the answer to this one.
Thanks in advance,
Richard R.

[Cindy Winegarden]

"Richard" <***@nospaminforms.co.uk> wrote in message news:%23x$***@TK2MSFTNGP15.phx.gbl...

Hi Richard,

Post by Richard
I am importing data from FoxPro 8 tables into SQL Server 2005, using the
Microsoft OLEDB driver for Visual FoxPro 8. ....

Please be sure you're using the latest FoxPro and Visual FoxPro OLE DB data
provider. It's downloadable from
http://msdn.microsoft.com/vfoxpro/downloads/updates .

Post by Richard
Can I set it to ignore deleted rows when I ask the component to return a
whole table?

The OLE DB data provider ignores deleted records by default.

Post by Richard
If I use a SQL Statement like SELECT * FROM tblFoxData then I get the
correct rows. If I connect using the table name in the component, then I
get the deleted rows as well, with no way then of ignoring them.

I'm not sure what you mean by "using the table name in the component." Can
you post some code or the details of your linked server?

Post by Richard
Sorry to trouble you all, ....

That's what we're here for. If we felt "troubled" by answering questions we
wouldn't be here, so ask away until you've solved your problem.

[Richard]

Hi All,

Thanks for replying so promptly. I'm not sure I've made myself very
clear. I can't really post code samples, as the VS environment hides all
that, so I'll have to create some screen shots and post those up, with a
more complete explanation.

Will do that this evening.

Thanks,


Richard

[Anders]

Try sending the command "SET DELETED ON" through VFPOledb
-Anders

OK, Hopefully this makes things a little clearer.
I've attached six screen shots which are the dialog and properties
boxes available to me in the VS 2005 environment.
I am using the Business Intelligence Studio in SQL2005 - which is just
VS2005. I create a task moving data from FoxPro to SQL, see the
attachment "DataFlow Task.bmp"
I can change the type of command I execute on the OLEDB source to be
either a SQL command (see "OLEDB SQL Source.gif" and "OLEDB SQL
Properties.gif"), or a connection "directly to the table" (see "OLEDB
Table Source.gif" and "OLEDB Table Properties.gif").
The difference between the two is that the SQL command returns only the
valid rows, whereas the direct connection using the Open Rowset method
returns the deleted rows as well. There is also a speed difference for
the complete task of about 8%, the SQL being the slower.
The available properties of the OLEDB connection are shown in the
screenshot "OLEDB Manager.gif". One of these might be the one to change
to ignore deleted rows?
I am not sure how FoxPro works in detail. I don't think it is
client-server, so I assume properties are set at the connection level,
rather than at the database level? If properties are set at the database
level, then there is nothing I can do there, as this is a live
transaction system.
Finally, I realise that this is still using the FoxPro 8.1 OLEDB driver.
The database is FoxPro 8. Should I update the OLEDB driver anyway?
Many thanks,
Richard.

--------------------------------------------------------------------------------






--------------------------------------------------------------------------------






--------------------------------------------------------------------------------






--------------------------------------------------------------------------------






--------------------------------------------------------------------------------






--------------------------------------------------------------------------------

[Richard]

Solution is to use SQL statements "SELECT * FROM ..."
This works OK every time

Regards,

Richard

Post by Anders
Try sending the command "SET DELETED ON" through VFPOledb
-Anders

OK, Hopefully this makes things a little clearer.
I've attached six screen shots which are the dialog and properties
boxes available to me in the VS 2005 environment.
I am using the Business Intelligence Studio in SQL2005 - which is just
VS2005. I create a task moving data from FoxPro to SQL, see the
attachment "DataFlow Task.bmp"
I can change the type of command I execute on the OLEDB source to be
either a SQL command (see "OLEDB SQL Source.gif" and "OLEDB SQL
Properties.gif"), or a connection "directly to the table" (see "OLEDB
Table Source.gif" and "OLEDB Table Properties.gif").
The difference between the two is that the SQL command returns only the
valid rows, whereas the direct connection using the Open Rowset method
returns the deleted rows as well. There is also a speed difference for
the complete task of about 8%, the SQL being the slower.
The available properties of the OLEDB connection are shown in the
screenshot "OLEDB Manager.gif". One of these might be the one to change
to ignore deleted rows?
I am not sure how FoxPro works in detail. I don't think it is
client-server, so I assume properties are set at the connection level,
rather than at the database level? If properties are set at the database
level, then there is nothing I can do there, as this is a live
transaction system.
Finally, I realise that this is still using the FoxPro 8.1 OLEDB driver.
The database is FoxPro 8. Should I update the OLEDB driver anyway?
Many thanks,
Richard.

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------