>Hi there, I need to update some fields in a SQL table from a Fox dbf two or three times daily , I currently do this using a linked server and SQL agent but it's proving to be very unreliable - SQL reports that an ODBC error has occurred - low on memory etc etc... and only restarting SQL server will fix it, sometimes it runs for a week without a problem other times only for a day, is there any way to use DTS to perform an update ( I know you can insert and copy ) ?
Have no idea if this applies to you but the serendipity of my stumbling on it an hour before reading your message can't be resisted
http://support.microsoft.com/kb/908915/en-us?spid=2855&sid=globalSYMPTOMS
Consider the following scenario:
• In Microsoft SQL Server, you use Microsoft OLE DB Provider for Visual FoxPro as a linked server by using the OPENQUERY function or the OPENROWSET function.
• In SQL Server, you configure Microsoft OLE DB Provider for Visual FoxPro to run in-process.
• You run repeated queries that return many rows and columns.
In this scenario, you receive an error message that is similar to the following:
Server: Msg 7346, Level 16, State 2, Line 7 Could not get the data of the row from the OLE DB provider 'vfpoledb'. Could not convert the data value due to reasons other than sign mismatch or overflow. OLE DB error trace [OLE/DB Provider 'vfpoledb' IRowset::GetData returned 0x40eda: Data status returned from the provider: [COLUMN_NAME=f100 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=f99 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=f98 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=f97 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=f96 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=f95 STATUS=DBSTATUS_S_OK], [COLUMN_NAME=f94 STATUS=DBSTATUS...
When the memory in the SQL Server MemToLeave region is exhausted, you may receive error messages that are similar to the following:
Server: Msg 701, Level 17, State 1, Line 8 There is insufficient system memory to run this query.
Charles Hankey
Though a good deal is too strange to be believed, nothing is too strange to have happened.
- Thomas Hardy
Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm-- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves.
-- T. S. Eliot
Democracy is two wolves and a sheep voting on what to have for lunch.
Liberty is a well-armed sheep contesting the vote.
- Ben Franklin
Pardon him, Theodotus. He is a barbarian, and thinks that the customs of his tribe and island are the laws of nature.
