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 |
|
nizmaylo
Constraint Violating Yak Guru
258 Posts |
 Posted - 2002-01-10 : 15:23:03
|
| Here is my scenario.Server2 is linked to Server1.MSDTC service is not installed on Server1.If I try to SELECT from Server2 – no problem, but if I try the following:INSERT INTO Server1.database.tbl(Genre_ID,Category,Supported,CategoryIndx,Keywords)SELECT * FROM Server2.database.tbl gmWHERE gm.category NOT IN (SELECT category FROM Server1.database.tbl)I get an error:MSDTC on server 'Server1' is unavailableIf I create a tempTable then the following statement works:INSERT INTO #tempTable(Genre_ID,Category,Supported,CategoryIndx,Keywords)SELECT * FROM Server2.database.tbl gmWHERE gm.category NOT IN (SELECT category FROM Server1.database.tbl)INSERT INTO Server1.database.tbl(Genre_ID,Category,Supported,CategoryIndx,Keywords)SELECT * #tempTableMoreover, I do not get the MSDTC server error for each table I need to update, just for some of them and never get an error using temp tables.Any ideas?helena |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-10 : 16:58:39
|
| msdtc will only be used if you need to incorporate the server in a distributed transaction.My guess is that as tempdb is volatile that the server doesn't bother to incorporate in a transaction. I suspect that if you start an explicit transaction that the situation would be different as it would have to cope with rollbacks.Also if you call remoteserver.db.dbo.sp_executesql to run statements on the remote server you can get some interesting effects.I suspect that this whole area hasn't been implemented too well and should be treated with care. It should be considered an area of risk on any project and a good candidate for feasibility studies.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
MSDTC