Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 OPENROWSET between SQL 7.0 and SQL 2000
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/09/2001 :  12:42:43  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Mason writes "I am currently connected to SQL server 2000 and need to perform a very simple update on a different server, which runs SQL 7.0. I am trying to use OPENROWSET. The select OPENROWSET will run on the server, but the update OPENROWSET query gives me an error.
Here is what my query looks like:

UPDATE OPENROWSET('SQLOLEDB', 'servername'; 'sa'; 'password', 'SELECT col_1, col_2 FROM DB_NAME..tblName') SET col_2 = @newVal WHERE col_1 = @PK_ID


And my error says this:
"MSDTC on server 'myServer' is unavailable.
The operation could not be performed becuase the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction."

Is this because of the different server versions? I'm pretty sure most everything is right... but the update doesn't happen. I was first trying to do this from an ASP page, didn't receive any errors, but the update never happened. So then I decided to put it into a sproc and got this error.

Any help would be much obliged! Thanks!
Mason
"

katnthebag
Starting Member

USA
3 Posts

Posted - 12/09/2001 :  18:25:22  Show Profile  Reply with Quote
I am not an ADO or ASP guru, but it looks like the DTC(Distributed Transaction Coordinator) Service is not running on your SQL Server 'myServer'. Check the SQL Service manager to see if it is running -- or if you are running NT/2000 on 'myServer' you can even look in the control panel/services area to see if the MSDTC service is running. Hope that helps...

--katnthbag


Cogito cogito ergo cogito sum. - I think that I think, therefore I think that I am.
Go to Top of Page

smccreadie
Aged Yak Warrior

USA
505 Posts

Posted - 12/09/2001 :  19:53:03  Show Profile  Visit smccreadie's Homepage  Reply with Quote
Try incorporating the update statement within a dynamic SQL statement and using sp_executeSQL. That always works for me.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000