none
Executing Sybase Stored procedure from Sql Server 2008 using Linked Server

    Question

  • I have checked previous threads but no use....here is my issue

    I am using Linked server created in SQL Server 2008 R2 to connect to Sybase db. The Linked Server works as it gives required results when data is selected from tables. But i am having issues when I execute Stored procedures which accepts like 20-25 parameters it throws error. I have tried different ways of Execution of stored procedure.

    Method 1 

    exec Linkedserver.Database.group.storedprocedure @Parameter1, @Parameter2, @Parameter3, @Parameter4,.........,@Parameter20,@Parameter21 Out,@Parameter22 Out

    It throws below error 

    OLE DB provider "ProviderName" for linked server "LinkedServer" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

    Msg 7212, Level 17, State 1, Line 34
    Could not execute procedure 'StoredProcedure' on remote server 'LinkedServer'.

    Method 2

    Exec  ('EXEC Database.group.Storedprocedure @Parameter1,@Parameter2,@Parameter3,@Parameter4,........,@Parameter20',@Parameter21 Out,@Parameter22 Out) AT LinkedServer

    It throws below error 

    OLE DB provider "ProviderName" for linked server "LinkedServer" returned message "[Sybase][ODBC Driver][Adaptive Server Enterprise]Must declare variable '@Parameter1'.
    ".
    Msg 7215, Level 17, State 1, Line 59
    Could not execute statement on remote server 'LinkedServer'.

    Method 3

    SELECT *
    FROM OPENQUERY(LinkedServer, 'SET FMTONLY OFF;  EXEC  Database.group.StoredProcedure @Parameter1,@Parameter2,@Parameter3,@Parameter4,........,@Parameter20 Out,@Parameter21 Out,@Parameter22 Out)

    It throws below error 

    Msg 7357, Level 16, State 2, Line 50
    Cannot process the object "SET FMTONLY OFF; exec Database.group.StoredProcedure  @Parameter1,@Parameter2,@Parameter3,@Parameter4,........,@Parameter20 Out,@Parameter21 Out,@Parameter22 Out". The OLE DB provider "Provider" for linked server "LinkedServer" indicates that either the object has no columns or the current user does not have permissions on that object.


    Is it something I am doing wrong, I don't know? Can someone help me I am struggling with this from past 2 days?

    Thanks in advance.

    Jaya



    Thursday, December 05, 2013 11:07 AM

Answers

  • I can't say why the first method fails, but it appears that you are using MSADSQL + ODBC driver. Try using an OLE DB provider for Sybase directly. The error you are facing occurs in the MSDASQL provider or the ODBC driver, so it's difficult to say what is going on.

    Why method two fails is, on the other hand, clearcut. You have not declared the variables. Keep in mind that the SQL string in EXEC constitutes a scope of its own and do not see any variables in the surrounding scope. But since EXEC AT accepts parameters you can do:

    EXEC (' DECLARE @out1 int, @out2 int
            EXEC db.owner.some_sp ?, ?. ?, @out1 OUTPUT, @out2 OUTPUT
            SELECT ? = @out1, ? @out2',
            @par1, @par2, @par3, @outputpar1, @outputpar2) AS LINKEDSERVER


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Kalman TothModerator Thursday, December 05, 2013 12:35 PM
    • Marked as answer by Jaya17 Thursday, December 12, 2013 1:53 PM
    Thursday, December 05, 2013 12:02 PM
  • Sybase stored prcedures can be executed in below way from Sql Server 

    Declare @Parameter1 NUMERIC(4)
    Declare @Parameter2 VARCHAR(1)
    Declare @Parameter3 DATETIME
    Declare @Parameter4 NUMERIC(8)
    Declare @Parameter5 VARCHAR(12)
    Declare @Parameter6 VARCHAR(28)
    Declare @Parameter7 NUMERIC(38) 
    Declare @Parameter8 NUMERIC(6)
    Declare @Parameter9 VARCHAR(250)


    select @Parameter1 = 1
    select @Parameter2 = 'A'
    select @Parameter3 = NULL
    select @Parameter4 = NULL
    select @Parameter5 = NULL
    select @Parameter6 = NULL
    select @Parameter7 = NULL
    select @Parameter8 = NULL
    select @Parameter9 = NULL


    EXEC ('EXEC [DB].[dbo].[StoredProcedure] ?,?,?,?,?,?,? ,? ,? ',@Parameter1,@Parameter2,@Parameter3,@Parameter4,@Parameter5,
    @Parameter6,@Parameter7 Output,@Parameter8 Output,@Parameter9 output)  AT [LINKSERVER]

    Select @Parameter7 ,@Parameter8,@Parameter9


    Jaya


    • Marked as answer by Jaya17 Thursday, January 16, 2014 4:51 PM
    • Edited by Jaya17 Thursday, January 16, 2014 4:54 PM
    Thursday, January 16, 2014 4:51 PM

All replies

  • I can't say why the first method fails, but it appears that you are using MSADSQL + ODBC driver. Try using an OLE DB provider for Sybase directly. The error you are facing occurs in the MSDASQL provider or the ODBC driver, so it's difficult to say what is going on.

    Why method two fails is, on the other hand, clearcut. You have not declared the variables. Keep in mind that the SQL string in EXEC constitutes a scope of its own and do not see any variables in the surrounding scope. But since EXEC AT accepts parameters you can do:

    EXEC (' DECLARE @out1 int, @out2 int
            EXEC db.owner.some_sp ?, ?. ?, @out1 OUTPUT, @out2 OUTPUT
            SELECT ? = @out1, ? @out2',
            @par1, @par2, @par3, @outputpar1, @outputpar2) AS LINKEDSERVER


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Kalman TothModerator Thursday, December 05, 2013 12:35 PM
    • Marked as answer by Jaya17 Thursday, December 12, 2013 1:53 PM
    Thursday, December 05, 2013 12:02 PM
  • Thanks for the reply

    I will try using the OLE DB provider and check EXEC AT method also.

    Jaya


    Jaya

    Thursday, December 05, 2013 1:25 PM
  •  have tried the Oledb Provider  and works for few stored procedures. All of a sudden even MSADSQL +ODBC driver also works.

    "Execute [LS].[DB].[Own].[StoredProcName]"  Method works for stored procedures without parameters.

    "Execute At" gives the results for procedures with input parameters only, not with both Input & Output parameter.

    My Final target is to loop through a Sql table using cursors and insert data in Sybase table using the Sybase Stored procedures.

    Any Help Appreciated.


    Jaya

    Thursday, December 12, 2013 1:49 PM
  • "Execute At" gives the results for procedures with input parameters only, not with both Input & Output parameter.

    So how does your calls look like?

    My Final target is to loop through a Sql table using cursors and insert data in Sybase table using the Sybase Stored procedures.

    Maybe the best and simplest is to write a program in C#, Perl, or whatever you prefer, so you need to battle with linked servers.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 12, 2013 11:08 PM
  • Hi

    This is my Execute AT statement

     

     Exec  ('declare @al_channel_id NUMERIC(2)
    declare @as_user_id VARCHAR(8)
    declare @as_machine_name VARCHAR(40)
    declare @al_api_code NUMERIC(4)
    declare @al_company NUMERIC(4)
    declare @as_account VARCHAR(35)
    declare @ad_from_date DATETIME
    declare @ad_to_date DATETIME
    declare @as_vt_flag VARCHAR(1)
    declare @as_card VARCHAR(20)
    declare @al_card_present NUMERIC(1,0)
    declare @al_last_N NUMERIC(4)
    declare @ol_record_no numeric(6)
    declare @ol_error NUMERIC(6)
    declare @os_message VARCHAR(1000)

     select @al_channel_id = 2
    select @as_user_id = ''TEST USR''
    select @as_machine_name = ''USR_SVR''
    select @al_api_code = 132
    select @al_company = 1
    select @as_account =  ''00000000001000006000''
    select @ad_from_date = ''01/01/2008''
    select @ad_to_date = ''12/12/2015''
    select @as_vt_flag = ''V''
    select @as_card = NULL
    select @al_card_present = 0
    select @al_last_N = NULL
    select @ol_record_no = NULL
    select @ol_error = NULL
    select @os_message = NULL

     EXEC [DB].dbo.[StoredProcedure] @al_channel_id, @as_user_id, @as_machine_name, @al_api_code, @al_company, @as_account, 
     @ad_from_date, @ad_to_date, @as_vt_flag, @as_card, @al_card_present, @al_last_N, @ol_record_no out, @ol_error out, @os_message out
    Select @ol_record_no, @ol_error,@os_message
     ') AT LinkedServer 

    It gives the result of output parameters but not the actual data result. Do we need to write any more select statement to get the result.

    I am creating a SSIS package to read some text files and push the data to SQL database. After validating the data i Insert into Sybase. So I creating an Stored procedure to to loop through data and call Sybase DB. May be I need re think about the process of inserting data in a better way.

    Thank you very much.


    Jaya

    Friday, December 13, 2013 9:07 AM
  • You should be able to write the statement as:

     Exec  ('EXEC [DB].dbo.[StoredProcedure]
            ?, ?, ?, ?, ?,
            ?, ?, ?, ?, ?,
            ?, ?, ? OUTPUT, ? OUTPUT, ? OUTPUT',
            2, 'TEST USR', 'USR_SVR', 132, 1,
            '00000000001000006000', '01/01/2008', '12/12/2015', 'V', NULL,
            0, NULL, @ol_record_no OUTPUT, @o1_error OUTPUT, @os_message OUTPUT)
            AT LinkedServer

    At least in theory. The OLE DB provider has to be cooperative.

    It gives the result of output parameters but not the actual data result. Do we need to write any more select statement to get the result.

    So the stored procedure produces a result set, which you don't see? Only the result set produced by the last statement?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 13, 2013 11:40 AM
  • Input parameters for Sproc are passed from table data due to which I am testing the sproc as mentioned previously... Is there any other way I can execute sybase sproc by passing input & output parameters.

    Thanks in advance



    Jaya

    Tuesday, December 17, 2013 1:27 PM
  • Input parameters for Sproc are passed from table data due to which I am testing the sproc as mentioned previously... Is there any other way I can execute sybase sproc by passing input & output parameters.

    Did you try my suggestion? It should work perfectly for the situation you describe, whereas in the example you posted, you had the values hard-coded.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 17, 2013 2:26 PM
  • Sybase stored prcedures can be executed in below way from Sql Server 

    Declare @Parameter1 NUMERIC(4)
    Declare @Parameter2 VARCHAR(1)
    Declare @Parameter3 DATETIME
    Declare @Parameter4 NUMERIC(8)
    Declare @Parameter5 VARCHAR(12)
    Declare @Parameter6 VARCHAR(28)
    Declare @Parameter7 NUMERIC(38) 
    Declare @Parameter8 NUMERIC(6)
    Declare @Parameter9 VARCHAR(250)


    select @Parameter1 = 1
    select @Parameter2 = 'A'
    select @Parameter3 = NULL
    select @Parameter4 = NULL
    select @Parameter5 = NULL
    select @Parameter6 = NULL
    select @Parameter7 = NULL
    select @Parameter8 = NULL
    select @Parameter9 = NULL


    EXEC ('EXEC [DB].[dbo].[StoredProcedure] ?,?,?,?,?,?,? ,? ,? ',@Parameter1,@Parameter2,@Parameter3,@Parameter4,@Parameter5,
    @Parameter6,@Parameter7 Output,@Parameter8 Output,@Parameter9 output)  AT [LINKSERVER]

    Select @Parameter7 ,@Parameter8,@Parameter9


    Jaya


    • Marked as answer by Jaya17 Thursday, January 16, 2014 4:51 PM
    • Edited by Jaya17 Thursday, January 16, 2014 4:54 PM
    Thursday, January 16, 2014 4:51 PM