Set Deleted ON in the connection props of the OLEDB Driver

[lowvato]

Does any one know how to set deleted on in the connection string?
I am setting up Sql Server Business Int packages for importing VFP
data. I cannot specify NOT DELETED() in a query for the data since the
tables are too large and I cannot find a property that works in the
data connections for BI that works. I thought that deleted ON was the
default! It is with ODBC. It seems wierd that this is not a bigger
issue.

Thanks,

Lowvato

[AA]

Send an Exec "SET DELETED ON" to VFP. Including NOT DELETED() in multitables
queries does not work, at least not correctly or the way you would hope, NOT
DELETED(alias) does not work either as VFP opens temporary tables again
while executing a query, with temporary aliases we know nothing about.

-Anders

Post by lowvato
Does any one know how to set deleted on in the connection string?
I am setting up Sql Server Business Int packages for importing VFP
data. I cannot specify NOT DELETED() in a query for the data since the
tables are too large and I cannot find a property that works in the
data connections for BI that works. I thought that deleted ON was the
default! It is with ODBC. It seems wierd that this is not a bigger
issue.
Thanks,
Lowvato

[lowvato]

That works if I have a connection object that I can send the command
through but in this case I am working wiht the Sql Server Integration
Services packages. I found out what the problem was though. Using
direct table access instead of a query is that fastest way to go for
importing the VFP data. When SSIS uses the tables directly it looks at
the file row by row, ignoring the deleted setting (which i think is
OFF, meaning ignore the Deleted() records), when I use a query it does
not bring back the deleted records. This really sucks since I am
importing close to 2 mill records and a query just locks up the server.

I think I'll have to write a custom "chunking" algorythm to bring the
data back.
Thanks,

Lowvato

Post by AA
Send an Exec "SET DELETED ON" to VFP. Including NOT DELETED() in multitables
queries does not work, at least not correctly or the way you would hope, NOT
DELETED(alias) does not work either as VFP opens temporary tables again
while executing a query, with temporary aliases we know nothing about.
-Anders

Post by lowvato
Does any one know how to set deleted on in the connection string?
I am setting up Sql Server Business Int packages for importing VFP
data. I cannot specify NOT DELETED() in a query for the data since the
tables are too large and I cannot find a property that works in the
data connections for BI that works. I thought that deleted ON was the
default! It is with ODBC. It seems wierd that this is not a bigger
issue.
Thanks,
Lowvato

[Cindy Winegarden]

Hi Lowvato,

The default setting for VFP OLE DB corresponds to the VFP command SET
DELETED ON which causes deleted rows to be filtered out. It's
counterintuitive, but you had the right idea.