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

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 OPENROWSET between SQL 7.0 and SQL 2000

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2001-12-09 : 12:42:43
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!

Starting Member

3 Posts

Posted - 2001-12-09 : 18:25:22
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...


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

Aged Yak Warrior

505 Posts

Posted - 2001-12-09 : 19:53:03
Try incorporating the update statement within a dynamic SQL statement and using sp_executeSQL. That always works for me.

Go to Top of Page

- Advertisement -