March 21, 2018 at 7:33 am
Good Day,
I have written a stored procedure that updates our document repository which in turns updates the local server with document references. Both are SQL Server 2012 and using Linked Server. The SQL ID is the same on both servers. When running the stored procedure from a scheduled job on the local server, it completed but the remote table does not update. To trouble shoot the issue, I have been trying to run the update query manual in order to resolve with no success. The query is written as follows:
Update dm
Set dm.doc = cd.newdoc,
Dm.editdate = GETUTCDATE()
From [Linked Server].[BBB].[MG].DocManager dm
Inner Join [MiddleWare].[dbo].vw_ChangeDoc cd On (dm.docnum = cd.docnum And cd.databasename = ‘BBB’)
Where dm.Doc != cd.newdoc
The query above runs without any results. I have let it run for 40 minutes just to see if anything happens. I am able to convert the above to a Select Query and which run and provide results.
Select dm.doc, dm.docnum, dm.editdate, cd.newdoc
From [Linked Server].[BBB].[MG].DocManager dm
Inner Join [MiddleWare].[dbo].vw_ChangeDoc cd On (dm.docnum = cd.docnum And cd.databasename = ‘BBB’)
Where dm.Doc != cd.newdoc
I am able to hard code my values in the Update query which works.
Update dm
Set dm.doc = ‘123456_Test_987654.pdf’,
Dm.editdate = GETUTCDATE()
From [Linked Server].[BBB].[MG].DocManager dm
Where dm.Docnum = ‘85245691’
Lastly, I have created a temp table with 3 columns and used that in the Update query and it just hangs.
Between the Select and hard coded values Update query success, it eliminated and issues with permissions. I am not sure what is missing. Why does joins make the query hangs? Does the user on the remote server need sysadmin role?
Thanks,
Maurice
March 23, 2018 at 4:21 pm
If you are trying to update data on Server B with a procedures or script running on Server A, then you are attempting what is called a "distributed transaction". Requirements for distributed transactions are spelled out at the following link.
Transact-SQL Distributed Transactions
However I would advise you against doing large-volume distributed transactions because joins across servers can be AGONIZINGLY slow. You might call a procedure that runs on the REMOTE server and pulls the source data from the local server into a temp table on the remote server. Then builds convenient indexes on the temp table. Then runs the update on the remote server joining to the temp table (which now exists on the remote server). The downside to this is that you do not have a single BEGIN TRANSACTION.... COMMIT block to handle errors on either end. You will have to come up with another mechanism to verify that all inserts or updates have taken place on both systems.
If I misunderstood what you are trying to do, please correct me.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 26, 2018 at 8:38 am
Hi The Dixie Flatline,
You are understanding the issue. We have everything in a store procedure which keeps showing as success, but nothing gets updated on Server B. To troubleshoot the issue, we ran each query independently to see where what is causing the issue. It was first determined that the SQL ID being used did not have permission to another database on Server A to get changed data. Once the permission was granted, the update query ran without return any results. When rewriting the query to use OpenRowSet(), an error stating that AD Distributed Transaction was not permitted. We are in the process of checking both server for MSDTC and Ad Hoc Distributed Transaction are turned on.
Thanks for the feedback
Maurice
March 27, 2018 at 10:50 am
Good luck with that. Be advised that our company has quit using linked servers because of security issues.
Also, using SSIS or multiple "pulls" of data are permitted without enabling distributed transactions. When a procedure or script on server A tries to update data to linked server B, that is a distributed transaction. But, when server B pulls data from server A and performs the same update, it is not a distributed transaction. Again, performance will be better if you move the data first, store it in a temporary table, and only then do your joins, inserts, etc.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply