Distributed query [drawn out problem]?

[zachster17]

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(Fiel d1, 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?

[ck9663]

Can you do this part in a query window?

INSERT INTO #TempTable(Field1, Field2)
EXEC('SELECT * FROM OPENQUERY(AMD,''SELECT COL1, COL2 FROM TABLE'')'

-- CK

[zachster17]

Can you do this part in a query window?

INSERT INTO #TempTable(Field1, Field2)
EXEC('SELECT * FROM OPENQUERY(AMD,''SELECT COL1, COL2 FROM TABLE'')'

-- CK

When I do that above I get:

Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.

I can do this with no errors:

	INSERT INTO #TableList(Category, TableSchema, TableName, TableType, TableNotes)
	EXEC sp_tables_ex 'AMD'

Thanks,

Zach

[ck9663]

Can't you use stored proc instead?


-- CK

[zachster17]

The problem is occuring inside a stored procedure. I think the sp_tables_ex only works since it is a system stored procedure for getting the table names of a linked server.

The thing I want to be able to do an INSERT INTO EXEC inside the stored procedure inserting into a local table and the EXEC being a dynamic open query (the same error also occurs just doing a simple INSERT INTO SELECT into the local db from the linked server).

I can't use stored procedure on the other end since we don't have full access to the linked server's end--I just want to pull data (in code) from the linked server to the local server (whether it be an actual table or a temp table)--which is where the distributed query error comes into play.

Thanks,

Zach