Execute stored proc with OPENQUERY

问题:

I have SQL Server 2008 with a linked Sybase server and I am trying to execute a stored procedure on the Sybase server using OPENQUERY. If I have a stored proc that doesn't take parameters it succeeds fine. If I have a stored proc with parameters it fails. I even tried a very basic stored proc that only took an int an that still failed. Below is the syntax I am using:

select * from 
OPENQUERY([LINKSERVER],'exec database.user.my_stored_proc ''AT'',''XXXX%'',''1111'',1')

Msg 7357, Level 16, State 2, Line 3 Cannot process the object "exec database.user.my_stored_proc 'AT','XXXX%','1111',1". The OLE DB provider "ASEOLEDB" for linked server "LINKSERVER" indicates that either the object has no columns or the current user does not have permissions on that object.

As the proc will execute just fine without parameters, I don't think it is a permission issue.

回答1:

Linked Servers and OPENQUERY, Gems to MS SQL Server...that are wolves in sheep clothing. I've found the following solutions to work when dealing with parameters

1. If the SP is basically just SELECT statements, the move the same to a VIEW and just pass SQL statements via OPENQUERY.

2. Build the OPENQUERY as a string and then use execute_sql.

回答2:

This worked for me,

SELECT * FROM OPENQUERY(LOCALSERVER, 'SET FMTONLY OFF EXEC snr.dbo.GetAllSignals @controlRunId = 25, @experimentRunId = 26')

I was creating temporary tables, and that's why i got access denied

Here is more info http://www.sommarskog.se/share_data.html#OPENQUERY

回答3:

I create a sp that doesn't return any value and it doesn't work. Your SP in mysql have to return a value! for example I do this in "mysql":

CREATE DEFINER=`root`@`localhost` PROCEDURE `MyPro`(IN `Name` VARCHAR(50), IN `Id` INT, OUT `Result` INT)
MODIFIES SQL DATA
BEGIN
DECLARE Result INT;
    SET Result = 0;
INSERT into MyTable (Id,Name)  VALUES(Id,Name);
SELECT Result;

END

That "Id" and "Name" is input parameter and "Result" is output parameter and create linked server in SQL SERVER and call it like this:

select * from openquery
(
    Test,'call mydb.MyPro(''Name'',''16'', @P0);'
)

It works for me :D

回答4:

You could also see if it works to precede exec with SET FMTONLY ON:

OPENQUERY([LINKSERVER],'SET FMTONLY ON; exec database.user.my_stored_proc ''AT'',''XXXX%'',''1111'',1')

If you try this and it works, you should probably Google FMTONLY+OPENQUERY to get an idea of what it means.