Hello everyone again,
I'm working on a stored procedure that imports data from a linked server into a local database's tables. The local database is SQL Server 2000 and the linked server is actually the ODBC connection to a web-based application's data that we don't have any control over (except for using the web app and being able to view the data through the ODBC).
I want to be able to do this (in order to loop through information as well as perform insert into other db statements):
INSERT INTO #TempTable(Field1, Field2)
EXEC('SELECT * FROM OPENQUERY(AMD,''SELECT COL1, COL2 FROM TABLE'')'
(remember I'm stuck with SQL Server 2000 on the local end and no control over the other end, which is a SQL Server too [not sure about what verison and all that]; I also tried the 4 part naming way and they don't support that either)
But, when I try to do it I get this error:
ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB Error Trace[OLE/DB
Provider 'MSDASQL' ITransactionJoi JoinTransaction returned 0x8004d00a].
I've been researching this on the Internet all day (even found some posts here), but I've tired all the solutions. I checked all the MSDTC settings and security on our local end, but I can't really do anything (unless as a last resort) on the website app db's end.
Right now, I'm guessing that the web host doesn't have it setup to be compatible.
My question is, is there any kind of work-around to 'fake' the server to return the recordset because I'm able to run an OpenQuery like above if i just do the select statement by itself in a query window--no code or anything). Or is there a completely different way that getting the INSERT INTO EXEC on this linked server to work?
What do you all think?