| Author |
Topic |
|
ButchieZ
Starting Member
7 Posts |
 Posted - 2004-07-16 : 12:26:20
|
| background info.Have a SQL2kEE server that has linked servers set up to 2 SQL 7 database server. On the SQL2k has I have a job that runs every hour that exec a SP in a user DB. The sp calls other sp's that do insert and updates selecting data from two sql 7 dbs. This job worked till we split are network and the SQL2k box is in one domain and the 7.0 boxes are in another domain. holes a punched between the two.Now when the SP is run I get the following errorServer: Msg 7391, Level 16, State 1, Procedure spTransferData, Line 60The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-16 : 13:07:07
|
| If the linked servers are using Windows Authentication, then is there a trust setup between the domains? Is the Distributed Transaction Coordinator started on both machines?Tara |
 |
|
|
ButchieZ
Starting Member
7 Posts |
Posted - 2004-07-16 : 13:24:00
|
| If the linked servers are using Windows Authentication, then is there a trust setup between the domains? Is the Distributed Transaction Coordinator started on both machines?No using a SQL login to connect.Yes DTC is started |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ButchieZ
Starting Member
7 Posts |
Posted - 2004-07-20 : 16:16:08
|
| Thnaks for the info but no of the boxes are a 2003 one is 2000 and the other is NT |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-20 : 20:07:55
|
| Have you tried setting up a domain admin user with a trust on all domains to run the DTC services. If you try this, the user will need to have ALL access that Network Service has on 2003.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
markos
Starting Member
13 Posts |
Posted - 2004-07-26 : 08:00:42
|
| I have the same problems on my system. I have a SQLServer 2000 on Win2k and Sql 7.0 on NT 4.0 Sp6a. The thing is that I can run select * from nt_server.database.dbo.tablename , but I cannot create a view :Select * from win2k_server.database.dbo.tablenameUNION ALLSelect * from nt_server.database.dbo.tablename |
|
|
|
ButchieZ
Starting Member
7 Posts |
Posted - 2004-07-26 : 09:29:33
|
| I have fixed the problem. I rewrote the query and the error went away. String thing was that the first 6 link server calls worked. I got the error on the 7th call. I changes the insert select to cursor and insert values. I know the cursor is not the best thing for performance but this was a production problem that need fixed right away. markoscan you run Select * from win2k_server.database.dbo.tablenameUNION ALLSelect * from nt_server.database.dbo.tablenamefrom query analyzer? |
|
|
|
markos
Starting Member
13 Posts |
Posted - 2004-07-26 : 10:39:07
|
quote:
Originally posted by ButchieZ
I have fixed the problem. I rewrote the query and the error went away. String thing was that the first 6 link server calls worked. I got the error on the 7th call. I changes the insert select to cursor and insert values. I know the cursor is not the best thing for performance but this was a production problem that need fixed right away. markoscan you run Select * from win2k_server.database.dbo.tablenameUNION ALLSelect * from nt_server.database.dbo.tablenamefrom query analyzer?
Unfortunatelly no . Not the same collation:-( I guess that was the problem |
|
|
|
kmarshba
Starting Member
24 Posts |
Posted - 2004-08-05 : 15:50:59
|
| In the linked server properties on the "Server Options" tab make sure the check box "User Remote Collation" is checked, regardless if the servers are using the same collation or not. |
|
|
|
|
unable to begin a distributed transaction