Sql-server – How to use OPENQUERY to properly execute a Stored Procedure that updates a linked server table

linked-serversql server

I'm having a hard time trying to figure this out. I'm using OPENQUERY to execute Stored Procedures on a linked server. I managed to find a way to do it, by using the following:

SELECT *    
FROM   OPENQUERY(
[LINKEDSRV\SQLSRV],  
'SET FMTONLY OFF; SET NOCOUNT ON; EXEC [Database_Name_Example].Data.usp_GetNames 5,''S''')   
GO

The reason I use SET FMTONLY OFF and SET NOCOUNT ON is because when I tried the code above without them, this message appeared:

Msg 7357, Level 16, State 1, Line 1 Cannot process the object "EXEC
[Database_Name_Example].Data.usp_GetNames 5,'S'". The OLE DB provider
"SQLNCLI" for linked server "LINKEDSRV\SQLSRV" indicates that either
the object has no columns or the current user does not have
permissions on that object.

Reading a lot online helped me find the answer. This blog for example: http://www.sommarskog.se/share_data.html#OPENQUERY

So far so good, until I stomped into a Stored Procedure that use SELECT and UPDATE some tables on the linked server. When I execute the OPENQUERY I get the results from the SELECT statement but I noticed that it does not update the table.

The Stored Procedure goes like this:

CREATE PROCEDURE Data.usp_GetNames (@year tinyint=NULL, @online char(1)=NULL)
if @online = 'S'    
  BEGIN
    select nam_codig, nam_desc from Names
    where nam_status = 'P'
    order by nam_date

    UPDATE Names
    SET nam_status = 'S'
    where nam_status = 'P'
END

When I execute it directly in the linked server it updates the table, so the second time I execute it, there will be no more nam_status = 'P'

But if I execute it with OPENQUERY the results are the same, it doesn't UPDATE it just select the data.

What can I do to allow the permissions?

Thank you!!!

Best Answer

Try using four-part naming instead of using OPENQUERY to execute stored procedures via a linked server:

EXECUTE [LINKEDSRV\SQLSRV].[Database_Name_Example].Data.usp_GetNames 5,'S'

Related Solutions

Sql-server – Run Multiple Remote Jobs

I'd say the easiest possible way to do this is through PowerShell and SMO. Take the following code for instance:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
    Out-Null

$SqlServerNames = "Server1", "Server2"
$SqlJobName = "YourJob"

foreach ($SqlServerName in $SqlServerNames) {
    $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

    $SqlServer.JobServer.Jobs[$SqlJobName].Start()
}

All this does it loop through a list of servers (explicitly stated at as string array variable, but can easily be obtained also from a SQL Server database table, a text file, etc.), connects to the current server, and then starts the job with the job named whatever you set the $SqlJobName variable to.

It's also worth noting that the Start() function will not wait for the job to complete before continuing code execution.

As you can see, in very few lines of PowerShell code you are able to accomplish this task. But I'd take it a step further, such as error handling. You don't want this to bomb out and not know what failed, when it failed, and what servers it did/didn't run on.

foreach ($SqlServerName in $SqlServerNames) {
    try {
        $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

        $SqlServer.JobServer.Jobs[$SqlJobName].Start()
    }
    catch {
        # add your error handling code here
        ## for instance, write the error to a text file
    }
}

And, as always with any code, test this out in a non-production environment to ensure that it does what you think it will and should do. Re-work it for your needs/environment.

As per RoKa's comment:

Can this be edited to check for whether the job is currently running, error handling?

Great point, and definitely something to check for (as well as if the job actually exists by checking for a non-null value):

foreach ($SqlServerName in $SqlServerNames) {
    try {
        $SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

        $SqlJob = $SqlServer.JobServer.Jobs[$SqlJobName]

        if (($SqlJob) -and ($SqlJob.CurrentRunStatus -eq [Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus]::Idle)) {
            $SqlJob.Start()
        }

        else {
            # handle/log accordingly if the job doesn't exist or it's not idle
        }

    }
    catch {
        # add your error handling code here
        ## for instance, write the error to a text file
    }
}

EDIT: While re-reading your question, I see SQL Server 2000. Are those going to be the target instances? If that is the case, and you do decide to go with the PowerShell/SMO answer I provided, then definitely definitely definitely test and ensure it is going to do what you want. Test this out very far from production and be 100% sure it does what you think it will.

EDIT: It looks like SMO versions before SQL Server 2012 (i.e. 2005, 2008, and 2008 R2) support managing SQL Server 2000 instances. I'm finding this information through this BOL reference.

Sql-server – Most efficient way to insert data to MS SQL table from OpenQuery on DB2 linked server

I ran the query with Visual Explain and the query completes in about 1 second.

Then it probably didn't fetch 2.5 million rows, which is likely your bottleneck.

I am trying to identify the bottleneck here and I believe it is the INSERT.

Then try just running

select * 
from OPENQUERY(linked_server, 'select * from linktable')

To eliminate the INSERT.

running this as SELECT * INTO may increase performance

Test that too. And test INSERT INTO with a Temp Table.

Using a 4-part name should not improve performance.

Best Answer

Related Solutions

Sql-server – Run Multiple Remote Jobs

Sql-server – Most efficient way to insert data to MS SQL table from OpenQuery on DB2 linked server

Related Question