Home / server / Questions / 66242
In Process
Fatherjack
Asked: 2009-09-18 05:35:46 +0800 CST

execute a script against a table on a different instance

  • 772

Scenario
Its pretty involved to explain but I have a procedure that updates data in two databases on instance1 and as part of the process (its a cursor for relevant reasons) it needs to make updates to a database on instance2 where certain criteria are met. If the line of code that references instance2 is altered to be the local instance the procedure runs in less than a minute. If it is set to refer to the correct location on instance 2 the procedure takes 30-40 seconds per record (we have never let it complete).

History (this morning)
Reviewing the estimated execution plan, the code that makes the single record update on instance 2 is using a remote scan on a table that is 100k records deep.

I changed this from

Update C set col1 = @val1, col2 = @val2 where col2 = @ID

to

Execute ('Update C set col1 = @val1, col2 = @val2 where col2 = @ID') as user1 at Instance2

where Instance2 is a linked server and user1 is a sql login that has impersonate enable on the linked server. This was so that the update process can make use of the clustered index on col2 and therefore avoid the table scan.

Issue
We are now getting security/authentication errors and the script is failing with

Msg 15274, Level 16, State 1, Procedure "procname", Line 263

Access to the remote server is denied because the current security context is not trusted.

Can anyone advise me what I need to configure to allow this update to execute please? Or, is there a better way that I can get the update to use the index on the table on Instance2? From my knowledge table hints are not allowed on remote queries...?

Many thanks

Jonathan

sql-server

2 Answers

  1. There are two major problems that stood out for me:

    1. Your execute statement is sending the literal strings (e.g. '@val1') within the query to Instance2 because they're all contained within a string. In order to send the values, you would need to change that part of the execute statement to read:

      Execute ('Update C set col1 = ' + @val1 + ', col2 = ' + @val2
         + ' where col2 = ' + @ID) as user1 at Instance2

      Note that the code above assumes both @val1 and @val2 are types which do not need to have quotes around them, otherwise you'd use the following:

      Execute ('Update C set col1 = ''' + @val1 + ''', col2 = ''' + @val2
         + ''' where col2 = ''' + @ID + ''') as user1 at Instance2

    2. Your query is not benefiting from any potential preoptimization on Instance2. Therefore, I would suggest creating a stored procedure on Instance2 so that you could benefit from pre-runtime optimization of the query, and also use all the optimization hints that you might want to place in the query. So on Instance2, you could create a procedure like this (note again that I have assumed integer datatypes):

      CREATE PROCEDURE user1.UpdateC (@val1 int, @val2 int, @ID int) AS
BEGIN
    UPDATE C WITH (ROWLOCK) SET col1 = @val1, col2 = @val2 WHERE col2 = @ID
END

      Then, your local script could replace the corrected code in part 1 above with the following (again, assuming integers):

      EXECUTE ('EXECUTE user1.UpdateC ' + @val1 + ', ' + @val2 + ', ' + @ID)
    AS user1 AT Instance2
    • 1
    • 0