(SQL 2K SP3a on W2K. VFP 6 data lives on W2K. GIGABIT BACKBONE, gig
cards on the data servers)
I'm sure anyone who attempts to retrieve or alter FP/VFP data through
a SQL link server will commiserate.
I'm getting sick of restarting my instance after attempting a simple
OPENQUERY statement on a VFP 6.0 DBC link server:
select * from openquery(ofsdbc,'select onestinkinfield from mytable
where onestinkinfield = whatever')
and being rewarded with
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager]
Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager]
Driver's SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro
Driver]Cannot open file \\mydevbox\mydbpath\myvfpdata.dbc.]
OLE DB error trace [OLE/DB Provider 'MSDASQL'
IDBInitialize::Initialize returned 0x80004005: ].
The only remedy is to restart the SQL services. The link server then
behaves for a variable number of queries, and then we do the dance
again.
Come on, MS, this is unacceptable. VFP is your own technology and you
can't make SQL play nice with it? Pshaw. This gets old when you're
five or six layers into a BizTalk process trace. ("When you're up to
your arse in alligators, it's tough to remember that the objective was
to drain the swamp.")
I RDP to the SQL box and can ping the datasource server all day long.
I can open the folder in which the DBC and data live all day long. I
can run the DBC in VFP, FROM THE SQL BOX, all day long. I just can't
get a link server to live more than one or two days, or sometimes more
than one or two queries.
NB I've already been through PSS for this garbage, and was essentially
told that MS no longer supports ODBC connections to VFP data from SQL
link servers. Furthermore, they don't support inserts, updates or
deletes through same with the VFP OLEDB provider.
Any and all war stories, tonics, suggestions, rants, corrections,
upbraids, horse laughs, and snickers appreciated in reply.