7

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.

ctrlalt313373
  • 3,935
  • 7
  • 36
  • 40

7 Answers7

16

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

mrwaim
  • 1,841
  • 3
  • 20
  • 29
  • 1
    +1 this worked for me as well. Thanks! I was not using temporary tables, but I was doing an EXEC(@VariableWithQuery) in the remote proc where that variable was set from a SELECT statement. That did not work until I added "SET FMTONLY OFF;" to the beginning. However, if I set the variable via a SET command then it did work without needing the SET FMTONLY. Odd. – Solomon Rutzky Jun 22 '12 at 21:22
  • Fair warning that there are some potentially unintended consequences to this method, as discussed in this answer: https://stackoverflow.com/a/14299989 ; but, it is the easiest way to "make it work" and avoid dealing with DTC, which is probably worse, so this (`SET FMTONLY OFF`) is a fair work-around. – NateJ Jun 30 '17 at 02:05
  • This lead me in the right direction, but it isn't 100% correct. For me what solved the issue was altering my stored procedure to include 'Set NOCOUNT ON' – Asher May 21 '19 at 06:43
  • "Set NOCOUNT ON" MUST be set inside the stored proc in order for SET FMTONLY=OFF to have effect. I actually added all these inside the stored proc. --DBCC USEROPTIONS SET ARITHABORT ON; SET ANSI_NULL_DFLT_ON ON; SET CONCAT_NULL_YIELDS_NULL ON; SET ANSI_NULLS ON; SET ANSI_WARNINGS ON; SET NOCOUNT ON; – nenea Oct 15 '20 at 14:15
2

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

Amirhossein Yari
  • 2,054
  • 3
  • 26
  • 38
2

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.

Saif Khan
  • 18,402
  • 29
  • 102
  • 147
1

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.

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
Steve Kass
  • 7,144
  • 20
  • 26
1

Try this,

SELECT * FROM OPENQUERY(linked_server_name, 'SELECT postgres_procedure_name (parameters)');

Nandini
  • 21
  • 3
0

I experienced a very similar issue, but my SP wasn't taking any parameters. I tried experimenting with altering the query sent through the openquery to include 'SET NOCOUNT ON' and 'SET FMTONLY OFF' but this had no difference.

The only solution that worked for my stored procedure was dropping the existing version, and altering the code to specifically 'SET NOCOUNT ON'

After doing this I was able to successfully run my stored proc through my linked server connection.

Asher
  • 348
  • 1
  • 3
  • 19
0

First of all you have to add hard code text fields then you have to replace it by your parameters value like FromDate,TillDate,EmpID,CompCode,0,DeptID,DesgId,LocationID,AtnType

DECLARE @startdate varchar(255) =   '2019-12-17'
DECLARE @enddate     varchar(255)    =  '2019-12-17'

Set  @SQL = 'SELECT  *  FROM OPENQUERY(' + quotename(@LinkedServer) + ',' + '''' +
 'SET FMTONLY OFF; exec [TAP].[dbo].[GetAttendanceList] '  + 'FromDate,TillDate,EmpID,CompCode,0,DeptID,DesgId,LocationID,AtnType,1'')'

You have to replace your parameters values shown below

set @SQL=REPLACE(@SQL,'FromDate',+''''+''''+@startdate+''''+'''')

  set @SQL=REPLACE(@SQL,'TillDate',+''''+''''+@enddate+''''+'''')

  set @SQL=REPLACE(@SQL,'CompCode',+''''+''''+@CompCode+''''+'''')

    set @SQL=REPLACE(@SQL,'AtnType',+''''+''''+''''+'''')

 if @EmpID is Null
  begin
   set @SQL=REPLACE(@SQL,'EmpID','null')
  end

  if @DeptID is Null
  begin
   set @SQL=REPLACE(@SQL,'DeptID','null')
  end

    if @DesgId is Null
  begin
   set @SQL=REPLACE(@SQL,'DesgId','null')
  end

   if @LocationID is Null
  begin
   set @SQL=REPLACE(@SQL,'LocationID','null')
  end


  print @SQL
  exec ( @SQL)
Learning_DBAdmin
  • 105
  • 4
Ali Hussain
  • 61
  • 1
  • 3