September 8, 2014 at 2:30 pm
Hi,
I have a procedure on SQLSERVER DB1 where i insert into few tables on SQL SERVER DB1 and delete a table on remote server SQL SERVER DB2 via linked server. Unfortunately, the delete is taking for ever. Procedure never completes. Data in that table is close to 500 records.
I have delete statement like
Exec(delete linkserv.onedb.stg.tab1)
Later i modified the delete query as below but no luck
DELETE OPENQUERY(linkserv, 'Select * from onedb.stg.tab1')
What can i do here to get this fixed?
Please see linked server properties below
Enable promotion of distributed transactions for RPC:True
Use Remote Collation: True
All other properties : False
How can i debug my SQL Server procedure and find the issue. I came to know that i do not have permission to use debug feature on SSMS.
I appreciate your responses
September 8, 2014 at 3:26 pm
EXEC ('DELETE FROM onedb.stg.tab1') AT [linkserv]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 16, 2014 at 10:46 am
I would try creating a stored procedure on the linked server to do the delete and then just call the one stored procedure from the other server. I have seen this improve performance. Something to try...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply