March 29, 2005 at 1:46 am
Actually i'm able to access the data from linked server using select statement in Query Analyzer but when i use the same in
< Begin Transaction >
It gives me error
One week earlier everything was working fine this problem occur only after restart of server ( Environment is Win2k and S2k)
Vss
March 29, 2005 at 2:33 am
March 29, 2005 at 11:14 pm
MSDTC is almost to call a bug in Windows 2003 in combination with SQL 2000. As much in clustered systems as in stand alone systems.
What is the problem with MSDTC?
MSDTC needs to be started before SQL Server 2000 starts on the machine. There is no check and start command in Windows 2003 or SQL Server 2000 wich could take care of this dependency.
If you run in to this problem:
You can check if it is ok if in the SQL Server 2000 errorlog you will not find the following line:
Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
If the problem persists:
Repeat the steps above and also between step 3 and 4 above:
If this still does not help, check if the MSDTC service is running under the NT AUTHORITY\NetworkService account and if the MSDTC service is set to automatic starting in the services console on the machine.
Hope this helps! In my opinion, Microsoft has not done a very well done thinking job on MSDTC in conjunction with SQL Server 2000 and Windows 2003.
March 30, 2005 at 1:19 pm
Having trouble with this whole day..
One moment it works fine, next minute it doesn't! Too weird!
I have one setup with Win 2000 Server and SQL 2000 and everything works fine.
Another setup is Win2003 and SQL 2000 and I'm having troubles with this same error. Can't get data from linked server. Well, I can get it when I write query wih openquery, but when I put this into procedure, and make query from string, and then execute through sp_executesql, it just won't work. Sometimes one query works, but other doesn't, sometimes neither one works. I'm feeling kind a crazy..having ghosts in server...
Tommorrow I'll try your solutin, I'll let you know.
March 31, 2005 at 3:07 am
Today I'm gettin crazy even more.
Here's my setup:
Win 2003 Server
SQL server 2000
I'm having linked server to DB2 database, using IBM DB2 ODBC driver.
Everything works just fine on the other server with Win 2000 Server.
Here, I have problems.
When I try to run procedure which is getting data from this linked server, it throws me an error:
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [IBM][CLI Driver] CLI0119E Unexpected system failure. SQLSTATE=58004]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Procedure has two queries getting the data from linked server into temp tables, calculating something, and returning recordset.
Here's the fun part:
I take the body of the procedure and run it. It throws the same error.
I take only the first part of the procedure (first query), run it, same error.
I remove insert into temp table, IT WORKS.
I return insert as it was (uncomment), IT WORKS.
I do the same with the other query, IT WORKS.
Now the whole procedure body, which is the same as before, works fine!
Then I copy/paste this body into another window in query analyzer and IT DOESN't WORK!
When I repeat procedure as before, it works.
Please help.
March 31, 2005 at 3:09 am
Do you have the error
Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
in the SQL errorlog?
March 31, 2005 at 3:12 am
No error at all!
March 31, 2005 at 3:17 am
That case I have no solution for you.
What platform is the connection to? Unix, AIX, Windows?
March 31, 2005 at 3:22 am
IBM AS/400
March 31, 2005 at 3:58 am
I am sorry, but I have no experience with connections to AS/400.
March 31, 2005 at 4:02 am
Thanky ou anyway
May 31, 2005 at 6:39 am
Did you ever find a solution for this problem as I am experiencing the same?
Kind regards
Chris
June 1, 2005 at 12:12 am
I've had some fun issues with distributed transactions to the AS/400. In my experience, they're not DTC errors, but problems with the IBM AS/400 OLE DB driver.
Experiment with wrapping the distributed part of the stored proc with BEGIN DISTRIBUTED TRANSACTION ... COMMIT
So, if you're query from the 400 looks like
INSERT INTO #Temp
SELECT * FROM OPENQUERY(...)
try
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO #Temp
SELECT * FROM OPENQUERY(...)
COMMIT
Also, if you're inserting into a # table, try creating a real table and inserting into that.
If you're doing SELECT * INTO #Table FROM OPENQUERY(...)
try replacing it with the INSERT INTO... SELECT * FROM structure.
I've had simmilar errors before, and similar behaviour and it's often a trial and error process to fix it.
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2005 at 4:41 am
Take a look in Evvent viewer.
I had an error, MSDTC, id 4445. There was a solution
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply