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) A socialist is someone who will give you the shirt off *someone else's* back.
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