Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004

    Unanswered: call sql server stored proc from sybase


    I am hoping someone can help me. I have Sybase 12.5.1 running on a UNIX system and SQL Server 2000 running on server 2003. Can anyone tell me how to have a stored proc on Sybase call a stored procedure on SQL Server?



  2. #2
    Join Date
    Apr 2004

    Post hi

    at first i need to know what develop tool does your application ?

  3. #3
    Join Date
    Jun 2004


    thanks for getting back to me. There are many languages involved.

    But for now...
    One of the application languages is COBOL. We already have it communicating with Sybase running on a UNIX box.

    I wanted to keep the plan as simple as I could. My goal is to get everyone populating table on sybase. When finished they call a stored proc on sybase (which everyone can see) which calls an extended stored proc on sql server. The extended proc on sql server will be an application that does final calculations. This amount will then be passed back.

    I hope this makes sense.

  4. #4
    Join Date
    Jan 2004
    why you want to write the procedure in sql server? Why you cannot write it in sybase itself? Does it use some objects on sql server?

  5. #5
    Join Date
    Feb 2002
    Willy is on vacation
    You can use Microsoft Linked Server feature and make an RPC call to Sybase.

    Linked server uses MS OLEDB for ODBC drivers. Microsoft does not support thirdparty OLEDB until their YUCON - MS SQL Server 9 release

  6. #6
    Join Date
    Jun 2004

    answer to why sybase.

    Sybase is where all of our order data is stored. We have to use sql server because that is what the tax software package we purchased uses. So the call needs to be triggered from the Sybase side (or in a call from our cobol program - that opens up a whole new can of worms).

    Eventually there will be at least 4 sites(different platforms and databases using this) and in the long term probably many more. So I was trying to keep this straightforward. We will develop an extended stored proc on SQl Server that will do tax calculations. A stored proc on our Sybase that will call this extended stored proc. 2 reasons for this:
    1) That way we just need every one to call the stored proc on sybase.
    2) Thought that inter-database connectivity would be easier and less time-consuming than having to write multiple apps on different platforms to accomplish this (thats what I get for thinking ;-) ).

    Does this sound okay? Am I making it harder than it needs to be?

    As far as a call from sql server to sybase:
    I added the sybase db to sql server as a linked server. But as I said right now we want this to be triggered from Sybase. Also though I can do selects from the Sybase tables thru SQL Server, I cannot execute a Sybase stored procedure. Unsure why.

    Thanks for all your responses! Any help you can give is greatly appreciated.


  7. #7
    Join Date
    Mar 2002

    try this for sybase

    to call a stored proc:

    create the linked server in sql server to use the odbc driver I couldnt get the sybase driver to work properly. Documentation stinks when you run into any problems.. others had same issue). The rpc and rpc out settings on the linked server properties must be checked on the server options tab. The stored procedure MUST return data.
    heres a test line that worked for me:
    Select * from openquery(Sybtest2, 'exec SelProfileProc')

    important note I found on another site (sorry.. dont haev the ref handy.. check google)

    Important note for stored procedures:
    Calling stored procedures across a linked server requires the four-part naming convention described earlier for direct SQL access of remote objects. The options of RPC and RPC Out must also be enabled in the configuration of the linked server. Stored procedures will run on the linked server just like queries using the OPENQUERY function.

    Now.. the interesting part: I was able to cause an access violation in sql server by doing one of these two queries:
    Select * from openquery(Sybtest2, 'exec SomeProcName 8, 1.00, 1, ''2004-06-21 12:36:00.000'' ')

    exec sybtest2.dbname.dbo.SomeProcName 4, 1.00, 1, '2004-06-21 12:36:00.000'

    good luck with it all... Im not very fond of sybase.
    If you get it working going from sybase to sql server let me know ( Heres a handy resource:

    Also from the sybase website.. they want you to purchase one of their products. They say:

    Problem Description:
    Cannot initiate Remote procedure calls (RPC's) from MS-SQL Server 2000 to ASE. Is there some product we are missing in between? (With MS SQL 6.5 we can RPC to ASE 11.03 & 12.0.)

    Key Words: RPC remote procedure ca

    Tip or Workaround:
    There has been some changes to the TDS. We do not support sending/receiving data when the connection is initiated by MS-SQL to ASE.
    Sybase supports communication initiated from ASE (in other direction) via a Sybase Connectivity product called Direct Connect/Enterprise Connect Data Access. Recommendation is to use DirectConnect for ASE to MS-SQL server, no matter what the version.

  8. #8
    Join Date
    Jun 2004
    Just a little add on for calling stored procedures across a linked server . You can actually not specify all the parts of the four-part naming convention and Just put

    exec sybtest2...SomeProcName @parameters

    I tried it and seemed to work for me. Anyone see any problems with this ?

  9. #9
    Join Date
    Mar 2002


    shortening the four part name when you have
    exec servername.dbname.dbo.SomeProcName @param
    doesnt matter much. Theres an underlying problem with their code (heck.. it even caused a gpf). It will default to the current user or dbo if the current user doesn't have their own version of the stored proc anyway. LEaving out potions of the four part name is usually considered bad practice since I can have
    and the same stored procedure name for a different user as:
    as well as dbo.MyProc
    you should also fully qualify the name (use dbo, etc)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts