Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
syraq
Starting Member
7 Posts |
 Posted - 2010-08-05 : 11:22:17
|
| HelloI need to JOIN the outcome from XML (by using OPENXML) to other table data. I do this with OPENQUERY and use LOCALHOST as linked Server. This works fine in SQL Server 2005 but not in SQL Server 2000. I have looked everywhere for a solution to my problem and most of them point to http://support.microsoft.com/kb/839279 and I followed those advices but it did not solve my problem. I have tested this with SQL Server 2000 on both XP and Win 2003 and get the same error.This is a simple example that fails to run on SQL Server 2000:SELECT * FROM OPENQUERY(LOCALHOST, 'SELECT * FROM LOCALHOST.ac.dbo.courses')Server: Msg 7391, Level 16, State 1, Line 1The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]I'm out of ideas so if anyone could shed some light on this matter I would be very grateful. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-05 : 12:02:12
|
| If it is inside a BEGIN TRANSACTION try doing it without a transaction.Pretty sure we had this a lot in SQL 2000 when trying to use OPENQUERY inside a transaction (i.e. connecting to Remote linked servers, not loop-back to LOCALHOST).(FWIW my gut feeling is that we are doing the same thing in SQl2008, as we did in SQL2000, but we can now always use a Transaction all the time, including where we couldn't before - i.e. nothing to do with us having configured things to more readily use transaction coordinator, so I think some "improvement" between SQL2000 and SQL2008) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-05 : 12:03:30
|
| Alternatively LOCALHOST is not a valid self-reference; usually the name of the server can be used (although in SQL2000 I think there is a possible issue if the Server is renamed - under those circumstances SQL Service may retain the old name, or need "help" to know its new name). Either way, there should be a name that will work for "self" |
|
|
|
syraq
Starting Member
7 Posts |
Posted - 2010-08-05 : 14:34:09
|
| Thanks for sharing your thoughts.I actually need to do this in a SP. I would like to create a view but since OPENXML only accepts one XML string at the time I do not see how that would be possible. And according to this article (http://msdn.microsoft.com/en-us/library/aa175782%28SQL.80%29.aspx) I have no option but to use OPENQUERY and OPENXML.Yes I thought about the possibility of a "collision" by using the name LOCALHOST but SQL Server 2000 does not allow me to use any other name and SQL Server 2005 is fine with it.Right now I'm leaning towards installing SQL Server 2008 on the server but it is running other mission critical applications from SQL Server 2000 so it makes me a bit nervous to tinker with the system. |
|
|
|
|
|
|
|
|
OPENQUERY and Linked Servers