FoxPro database connector - OLEDB Newbie - how to ignore deleted rows?

Author

OLEDB Newbie - how to ignore deleted rows?

Richard

2005-11-29, 8:25 pm

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

2005-11-29, 8:25 pm

Send VFP the command SET DELETED ON.

Dan

Richard wrote:
> 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.

Cindy Winegarden

2005-11-30, 3:24 am

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

Hi 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 .

> 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.

> 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?

> 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.

--
Cindy Winegarden Microsoft MVP
cindy_winegarden@msn
.com

Richard

2005-11-30, 7:24 am

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

Richard

2005-12-01, 7:24 am

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.

Anders

2005-12-06, 1:24 pm

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

"Richard" <rrose@nospaminforms.co.uk> skrev i meddelandet
news:%23N1L01l9FHA.2816@tk2msftngp13.phx.gbl...
>
> 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

2006-02-02, 9:29 am

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

Regards,

Richard

Anders wrote:
> Try sending the command "SET DELETED ON" through VFPOledb
> -Anders
>
> "Richard" <rrose@nospaminforms.co.uk> skrev i meddelandet
> news:%23N1L01l9FHA.2816@tk2msftngp13.phx.gbl...
>
>
>
>
> --------------------------------------------------------------------------------
>
>
>
>
>
>
> --------------------------------------------------------------------------------
>
>
>
>
>
>
> --------------------------------------------------------------------------------
>
>
>
>
>
>
> --------------------------------------------------------------------------------
>
>
>
>
>
>
> --------------------------------------------------------------------------------
>
>
>
>
>
>
> --------------------------------------------------------------------------------
>
>
>
>
>

Sponsored Links