I have MS SQL 7 server named Financial with Linked Server(AUTHTEST) pointing to an Informix Dynamic Server(named Authority) using MSDASQL as the provider therefore I am connecting via ODBC. I want to achieve automatic data transfer through these two different databases.
Through Enterprise Manager I can see the Linked Server and its tables. I can also through Query Analyser do a Select on the tables in the linked server and retrieve the data. I can also run updates and deletes on the tables and its data. However when I run an Insert on the tables I get the following Error....
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure. [OLE/DB provider returned message: Query cannot be updated because the FROM clause is not a single simple table name.]
The Insert syntax is ...
INSERT into AUTHTEST.Authority.auth.Nar(NameId, Contact)
This happens with all tables in the linked server. Can anyone help with this one?? Or is there a way to debug this and get a more detailed error message?? Or can just someone point me in the right direction??