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 - 2002-03-08 : 10:15:58
|
| DONNAJ writes "I have linked sql servers, all version 7, sp2(I think), with a stats database residing on one of the test servers(on purpose).I have written stored procs to get the space usage info and store it in very simple tables in this stats db. Here is the goofy problem: I get a unique key index violation on the insert statement when I run the proc from one of the linked servers. Everything works fine on the server where the table resides.What would the code be doing on a linked server that makes the server think it has tried to insert a duplicate row??I am getting the max number from the key and adding 1 to make the new key. I display the number and it is the correct one, but, it still will not insert the row. Has anyone dealt with linked servers a great deal? Does this sound familiar?Thanks" |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-03-12 : 06:30:43
|
| Sounds like you have multiple linked servers inserting with the same "seed" value. Can't you change the table to have an identity column, so that you don't have to do the select (MAX) and then add 1 ?(I'm assuming that , on a specific server, you go through each database 1 at a time ?)If not, then try to ensure that you are not running the inserts from multiple machines simultaneously - how do you have the work scheduled? If via job scheduler, try and ensure that the jobs are sequential - you should avoid the problem. alternatively, you can try and make sure that you are locking the table correctly to ensure that no to threads get the same "seed" number.PS - are you using the sysperfinfo table to get this information ? If not, you might want to have a look at it...hth |
 |
|
|
Jay99
468 Posts |
|
|
donnaj
Starting Member
3 Posts |
Posted - 2002-03-20 : 13:59:01
|
| Hey,It's DonnaJ. Thanks for the input. I found that the updates worked if the insert was written with the 'values' included as variables rather than 'insert into x select * from y'Why this worked I do not know. The proc is now working like a champ and loading to a database on one of our servers. I have linked all the servers, 6 sql7 and 1 sql2k and it works from each. It is not too bad on execution time, either.Thanks again |
|
|
|
|
|
|
|
|
SQL SERVER 7 LINKED SERVERS