Execute stored proc with OPENQUERY

sql sql-server tsql sybase openquery
ctrlalt313373 picture ctrlalt313373 · Nov 11, 2010 · Viewed 69.6k times · Source

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.

Answer

mrwaim picture mrwaim · Aug 22, 2011

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

Related questions

Inserting Multiple Rows in Sybase ASE

(Similar question related to SQL Server : SO Link) I know in Sql Server 2008 or above, you can insert multiple rows doing the following: INSERT INTO MyTable (Name, ID) VALUES ('First',1), ('Second',2), ('Third',3) However, it seems like this syntax DOES NOT …

Read More
SQL Parent/Child recursive call or union?

I can't seem to find a relevant example out there. I'm trying to return a sub-set of a table, and for each row in that table, I want to check how many children it has, and return that number as …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More