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 |
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
 Posted - 2005-08-09 : 17:31:34
|
| Normally, SQL Server will not let you insert, update, or delete a table on a linked server using a locking hint. But you can get around that by using an alias:DELETE AliasName WITH (ROWLOCK) FROM ServerName.Database.Owner.TableName AS AliasName WHERE ...UPDATE AliasName WITH (ROWLOCK) SET Column = 'whatever' FROM ServerName.Database.Owner.TableName AS AliasName WHERE ...However, I can't seem to figure out how to do this for an INSERT statement. Is it possible? Am I missing something obvious? |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-08-09 : 18:05:58
|
| You can get around this via OPENQUERY, though I am unsure how SQL Server deals with this workaround internally. I have read conflicting info in forums; some reporting that the hints are applied naturally (as if they were executed outside of an openquery statement) and some others say that it is merely a syntax workaround and the hints are ignored on the linked server.Im glad you brought this up. Im anxious to hear everyones take on it.Nathan Skerl |
 |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-08-09 : 19:04:58
|
| Thanks for the reply Nathan. How do you write the INSERT with OPENQUERY and a locking hint, because I tried that too and I couldn't get the correct syntax. I don't think that using an alias is merely a syntax workaround, because in the brief tests that I conducted, it seemed like the locking hints were being honored in the DELETE and UPDATE statements that were similar to what I wrote above. |
|
|
|
|
|
|
Insert into linked server table with locking hint