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 |
|
AskSQLTeam
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_IDAnd 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
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...--katnthbagCogito cogito ergo cogito sum. - I think that I think, therefore I think that I am. |
 |
|
|
smccreadie
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. |
|
|
|
|
|
|
OPENROWSET between SQL 7.0 and SQL 2000