473,320 Members | 2,052 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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(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?
Jul 21 '08 #1
4 1534
ck9663
2,878 Expert 2GB
Can you do this part in a query window?

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO #TempTable(Field1, Field2)
  2. EXEC('SELECT * FROM OPENQUERY(AMD,''SELECT COL1, COL2 FROM TABLE'')'
-- CK
Jul 21 '08 #2
Can you do this part in a query window?

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO #TempTable(Field1, Field2)
  2. 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:

Expand|Select|Wrap|Line Numbers
  1.     INSERT INTO #TableList(Category, TableSchema, TableName, TableType, TableNotes)
  2.     EXEC sp_tables_ex 'AMD'
  3.  
Thanks,

Zach
Jul 21 '08 #3
ck9663
2,878 Expert 2GB
Can't you use stored proc instead?


-- CK
Jul 21 '08 #4
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
Jul 21 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Nick Truscott | last post by:
<? // scoreinput.php - input a match score when match selected from list ?> <html> <head> <basefont face="Verdana"> </head> <body>
0
by: Nick Kew | last post by:
Rationale ========= Many applications today benefit from an SGML and/or XML Entity Catalogue to dereference entities referenced by a Public Identifier. For a validating SGML parser this is an...
2
by: Jo Siffert | last post by:
Hi all, I would like to perform an INSERT INTO LINKEDSVR.dbo.xyz.abc SELECT ... FROM dbo.dfg where LINKEDSVR is a linked server on another machine. Both servers are running SQLServer 2000...
8
by: Rob S | last post by:
I have UDB 8.1 Personal Edition installed. I'm using Development centre to develop JAVA Stored Procedues. I am unable to debug them. I have installed IBM Distributed Debugger and have set...
0
by: Steve Nihan | last post by:
I've created the following distributed application: Solution EDSQLWSVC EDEmployerWinClient Project3 Web Service winform app asp.net web app My web services are fine....and I can get data...
1
by: ammar_fake | last post by:
Hello! I have an Oracle linked server connected through MSDAORA. Linked server queries work perfectly - the "openquery" ones as well as the 4-part-named ones. The problem I have is with...
0
by: Matik | last post by:
Hi, MSSQL 2000 booth servers. Booth running DTC. Now, the client application, is starting in DB1 a procedure. The connection open to db, is within the transation opened from client. In the...
4
by: JB | last post by:
Hi All, I need to write my first "distributed" application and due to my lack of knowledge and experience in that area, I'm stuck on the first big design decision. Reading a lot on distributed...
4
by: zion4ever | last post by:
Hello good people, Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.