Linked server and this fail
Hi, i have a trouble with the microsoft sql server 2008 (10.0.2531). I use two servers: SERVER1 a SERVERLINKED. SERVERLINKED is linked server on SERVER1. WHEN i restart SERVERLINKED and i start job on SERVER1, i get this message: This operation conflicts with another pending operation on this transaction. The operation failed. [SQLSTATE 42000] (Error 3970). The step failed. In job is a that seguence: - truncate table1 on SERVERLINKED - insert into table1 on SERVERLINKED - select from table1 on SERVERLINKED usualy it goes down in insert case i think. The solution is manualy truncate table from SERVER1 a then start manualy insert. And everything is OK. Then is possible star the job. Could you help me? Thank you.
November 29th, 2011 2:57am
EXEC ('truncate table tbl') AT SERVERLINKEDBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
November 29th, 2011 3:54am
It is a sequance, what i wrote. I use exactly this: - EXECUTE SERVERLINKED.SomeDB.dbo.sp_executesql N'TRUNCATE TABLE table1' - INSERT INTO SERVERLINKED.SomeDB.dbo.table1 SELECT ....... - SELECT COUNT(*) FROM SERVERLINKED.SomeDB.dbo.table1
November 29th, 2011 4:16am
I am glad that it worked fro you.Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
November 29th, 2011 4:29am
I have still the problem. I don't changed anything a i am still waiting for answer. This code is my old code (what i had a i have just now): - EXECUTE SERVERLINKED.SomeDB.dbo.sp_executesql N'TRUNCATE TABLE table1' - INSERT INTO SERVERLINKED.SomeDB.dbo.table1 SELECT ....... - SELECT COUNT(*) FROM SERVERLINKED.SomeDB.dbo.table1 But Im glad you are trying to help me. Thanks.
November 29th, 2011 5:06am
TRUNCATE TABLE is using only two prefixes naming covention, i.e. schema.Table, and is not allowed against a linked server. Use instead sp_executesql or EXEC() at linkedserver commands. HTH
November 29th, 2011 7:57am
As i wrote i use: EXECUTE SERVERLINKED.SomeDB.dbo.sp_executesql N'TRUNCATE TABLE table1'
November 29th, 2011 8:24am
You might want to take a look at http://support.microsoft.com/kb/949678/
November 29th, 2011 9:50am
Hi Michalmalina, Since the issue is most related to DataBase Engine, I suggest you post the question to our SQL Server Database Engine forum as below: http://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/threads There are many experts focus on it, and hope the issue could be resolved soon with helps from appropriate team. Thanks, Eileen
December 1st, 2011 2:09am
Hi Eileen Zhao, thank you very much for your advise. I did it as you said.
December 5th, 2011 6:33am