Re: Sybase ASE 12 Stored Procedure / ODBC Adapter / BizTalk 2009 / Windows Server 2008

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › Sybase ASE 12 Stored Procedure / ODBC Adapter / BizTalk 2009 / Windows Server 2008 › Re: Sybase ASE 12 Stored Procedure / ODBC Adapter / BizTalk 2009 / Windows Server 2008

April 7, 2010 at 3:07 AM #24525
community-content

    Resolution

    I’ve found a workaround, which I thought I’d post for anyone following this thread.

    I gave up with the ODBC Adapter, and turned instead to the standard SQL Adapter.  I pointed this at some business functions in SQL Server, which called the Sybase business functions via SQL’s Linked Server functionality.  To successfully link to Sybase, I created an ODBC connection to it on the SQL database server.

    Steps

    1. Install Sybase driver on SQL Server
    2. Create & test an ODBC connection to your Sybase database
    3. Create the linked table in SQL Server
      • exec sp_addlinkedserver @server=’linked_server_name‘, @srvproduct = ‘Sybase’, @provider = ‘MSDASQL’, @datasrc = ‘ODBC_Data_Source_Name‘
      • exec sp_addlinkedsrvlogin @useself=’false‘, @rmtsrvname = ‘linked_server_name‘, @rmtuser = ‘username‘, @rmtpassword = ‘password‘
    4. Enable Remote Procedure Calls
      1. Right click on your linked server (found in SSMS under “Server Objects\Linked Servers”
      2. Select Properties
      3. Go to the “Server Options” page
      4. Change both “RPC” and “RPC Out” to True
    5. Test standard SQL over the linked server
      • select * from linked_server_name.database_name.dbo.tablename;
    6. Test stored procedures over the linked server
      • execute (‘database_name.dbo.stored_procedure_name’) at linked_server_name;
    7. You can now wrap any calls in your own SQL Server stored procedures, and access these from biztalk in the standard way.

    * I believe this solution should work with any database with an ODBC driver.  However, it’s always worth testing first, just to be sure :).