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 |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
 Posted - 2012-03-05 : 14:47:45
|
| We have a federated database supported across a set of servers. There are a few tables on the "Golden" server that every database in the federation can insert into and we use linked server as the means of accessing these "golden" tables. It is a pretty simple coding pattern used:[CODE]INSERT INTO GOLDSERVER.GoldDB.dbo.MyTable(column list)SELECT (local column list)FROM #MyTempTable lLEFT OUTER JOIN GOLDSERVER.GoldDB.dbo.MyTable g ON l.PKey = g.PKeyWHERE g.PKey is NULL[/CODE]Standard stuff; here is where it gets wierd. The developers came to me saying that the INSERT is completing successfully (no errors; accurate @@RowCount) but when they try to display the new records, they're not there. Running the SELECT portion of the above code with an INNER JOIN should return some results; they don't and the COUNT(*) of the target table has not changed.At first I suspected that the linked server might be misconfigured but when I use an explicit INSERT-VALUES statement or an explicit INSERT-SELECT (string of constants) everything performs as desired. I ran Profiler on the golden server to see if any implicit transactions were being used and rolled back but the processing looks about the same as for the INSERT-VALUES code execution.This is being reported against a recently configured non-golden server so my question becomes have I overlooked some aspect of the configuration that impacts remote INSERTs.Many thanks...=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-03-05 : 16:52:37
|
| Before posting I ran Profiler on each side of the linked server. The initiator side showed little of interest and the linked server side showed expected steps and no transaction logic. My thought process was:1) The developers are reporting incorrectly2) The linked server is misconfigured (not pointing at the server that we think it is, etc.)3) An implicit transaction is rolling back the changes4) Some trigger is messing up the resultsThe results are repeatable, selecting from the linked server, from either side, yields good results, no transactions SEEM to be in play and there are no triggers on the table.=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-03-05 : 17:19:04
|
| ADDENDUM: Further testing shows that if I don't use the OUTER JOIN to limit the data being inserted but use a NOT EXISTS approach instead and everything's as expected. The join is on a non-nullable field.=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-03-05 : 17:55:01
|
| ADDEMDUM: This seems to be specific to the source table being a temp table.=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
|
|
|
|
|
|
Linked Server: INSERTs not sticking