Linked Server Problem

Hi!

I have written a Perl Program, which works on a MS SQL SERVER 2000 database. It should import some data from an Oracle8i database. so i created a linked server and a linked server login. There were no problems when i created these things. But if i execute a "select ..."-Statement at the linked server(Oracle), i get the following message:

"[Microsoft][ODBC SQL Server Driver][SQL Server]Die Operation konnte nicht ausgefhrt werden, da der OLE DB-Provider 'MSDAORA' keine verteilte Transaktion beginnen konnte. (SQL-42000)
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB-Fehlertrace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]. (SQL-01000)(DBD: st_execute/SQLExecute err=-1),ERROR = -1"

But the select statement, i used is ok. i started it in the Query Analyzer and everything was fine! This is the statement:

"select * from openquery(SRCLINKWORLD,'SELECT COUNT(*) AS ANZAHL FROM DBADMIN.ABSCHLUSSABR')"

Does anyone know , what the problem is?

NickWell...I know very little German (it is German isn't it), but what login did you use to connect with?

Is it a remote login in Oracle?

This is purely a shot in the dark...|||Strange SELECT you use,

why

"select * from openquery(SRCLINKWORLD,'SELECT COUNT(*) AS ANZAHL FROM DBADMIN.ABSCHLUSSABR')"

instead of

SELECT COUNT(*) AS ANZAHL FROM DBADMIN.ABSCHLUSSABR

Linked server issue

We are utilizing a linked server connection to a proprietary database server in SQL 2005. Connectivity is thru an ODBC driver written by the proprietary database server's vendor. This has been running successfully for several years utilizing SQL 2000, however we are experiencing problems with 2005. It appears that the proprietary database server can only answer queries single-threaded, or in other words, it must complete a query before it can begin another. Whenever the SQL 2005 server sends multiple queries to the linked server before it has a chance to respond, the connection freezes until the SQL 2005 server can be restarted. As support for the proprietary database server is limited, we are trying to identify if there is a way in SQL 2005 to queue the connections to the linked server to prevent this "traffic jam?" Adjusting connection timeouts only reduces the frequency of the "freezes" and we really need to find a solution to this.

Any ideas, short of writing a custom ODBC driver, would be appreciated.

Thanks in advance.

If you are operating on data within SQL server using data from this linked database, you could wrap that functionality in a transaction to enforce serialization and ensure that this strange single threaded server gets only one connection at a time. This would also provide you with transactional logging to ensure that you handle any other errors that come up without data corruption or loss.

Hope this helps,

John

|||This *could* work but I was hoping for a more global solution as the server is passed a lot of Adhoc queries via OLE and ODBC.|||

You could write a stored procedure if you have a common workload and implement your transactions within it to provide similar service across network stacks. Realistically, any approach to serializing an inherently distributed operation will seem nasty, and the real issue is that any operation involving that linked server is non-reentrant.

Typically, non-reentrant code is very dangerous to threaded code, in that global shared state within the non-reentrant function can nuke the results from another. You can skirt this by wrapping all operations that use that function or server in one big critical section, to avoid another connection coming in and stepping all over the shared state; however, this comes at a considerable performance cost. In the long run, this particular service (if it is used extensively and broadly enough) will become a major bottleneck to the performance of your pipeline. There really is no right answer to how to address this problem other than to either upgrade that driver to a multithreaded one, remove that service from your pipeline, or if possible -- replace real time communication with storage to a table in SQL Server with a batch job running nightly to send data across to synchronize the services. If SQL Server is the consumer of data in this relationship, rather than the producer, or if you have a need for real-time updates to and from the linked server then of course this would not address your issue.

I sympathize with your situation, and hope this helps.

John

Linked server error

Hi friends,

I have written an openquery that selects data scattered over two servers in multiple tables. It uses a cursor to read the rows and then performs validations to get other data.

while running it runs for around 4000 lines of table and then fails giving following error--

Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
[OLE/DB provider returned message: Deferred prepare could not be completed.]

any help will be appreciated

regards

anurag

My assumption is that you are constructing dynamic strings. So, that would mean that you are hitting a piece of data that when concatenated into your string is throwing an error. Instead of executing the dynamic queries, have you just tried dumping them out as strings so that you can find the one that is not generating properly?|||Another possible cause, the table which you are accessing doesn't have valid permission.