When using the SAP Sybase ASE OLE DB Provider you are able to create linked servers in MSSQL Server (2005, 2008, 2008 R2, 2012) to access remote data sources such as SAP Sybase ASE 15.7. In principle you create a linked server on the MSSQL Server, using the SAP Sybase ASE OLE DB Provider, or ASEOLEDB in short. From a connection on the SQL Server you can run various queries to access the data.

One thing that comes up from time to time is to access stored procedures on the ASE. One convention well known in linked server is to call the stored procedure using the four part naming convention:

EXEC Linked_Server_name.database.schema.stored_procedure_name

For example, if I created a procedure in the pubs2 database:

create proc sp_authors (@p1 varchar(50))

as

select * from authors where state = @p1

I call it from a connection via a tool like isql:

1> sp_authors ‘CA’

2> go

au_id       au_lname                                 au_fname

         phone        address

         city                 state country      postalcode

———– —————————————- ——————–

         ———— —————————————-

         ——————– —– ———— ———-

172-32-1176 White                                    Johnson

         408 496-7223 10932 Bigge Rd.

         Menlo Park           CA    USA          94025

213-46-8915 Green                                    Marjorie

         415 986-7020 309 63rd St. #411

         Oakland              CA    USA          94618

I get back all the records where state is ‘CA’.

On linked server I create linked server named PVLNX1570, which will access my ASE 15.7 on Linux, and I configured it to connect to the pubs2 database. When I use the four part naming convention to execute from a tool like the query tool in the Microsoft SQL Server Management Studio:

PVLNX1570.pubs2.dbo.sp_authors ‘CA’

Result:

Msg 7212, Level 17, State 1, Line 1

Could not execute procedure ‘sp_authors’ on remote server ‘PVLNX1570’.

This type of call isn’t supported from Linked Server when the data source isn’t SQL Server. I found this link from the SQL Server documentation:

2005:

http://msdn.microsoft.com/en-us/library/ms175129%28SQL.90%29.aspx

2008 R2:

Guidelines for Using Distributed Querieshttp://msdn.microsoft.com/en-us/library/ms175129(v=sql.105).aspx

Under Other Guidelines it states:

Stored procedures are supported only against SQL Server data sources.

There is a way to get around this in using the EXEC command. There are many ways to use the command and Microsoft in its wisdom provides a means to call the remote SQL directly with the linked server:

2008 R2:

http://msdn.microsoft.com/en-us/library/ms188332(v=sql.105).aspx

The syntax is as follows:

EXEC (‘sproc plus parameters’) at Linked_Server_name

For the above sample:

(‘sp_authors ”CA”’) at PVLNX1570

au_id au_lname au_fname phone address city state country postalcode

172-32-1176 White Johnson 408 496-7223 10932 Bigge Rd. Menlo Park CA USA 94025

213-46-8915 Green Marjorie 415 986-7020 309 63rd St. #411 Oakland CA USA 94618

238-95-7766 Carson Cheryl 415 548-7723 589 Darwin Ln. Berkeley CA USA 94705

267-41-2394 O’Leary Michael 408 286-2428 22 Cleveland Av. #14 San Jose CA USA 95128

274-80-9391 Straight Dick 415 834-2919 5420 College Av. Oakland CA USA 94609

409-56-7008 Bennet Abraham 415 658-9932 6223 Bateman St. Berkeley CA USA 94705

427-17-2319 Dull Ann 415 836-7128 3410 Blonde St. Palo Alto CA USA 94301

472-27-2349 Gringlesby Burt 707 938-6445 PO Box 792 Covelo CA USA 95428

486-29-1786 Locksley Chastity 415 585-4620 18 Broadway Av. San Francisco CA USA 94130

672-71-3249 Yokomoto Akiko 415 935-4228 3 Silver Ct. Walnut Creek CA USA 94595

724-08-9931 Stringer Dirk 415 843-2991 5420 Telegraph Av. Oakland CA USA 94609

724-80-9391 MacFeather Stearns 415 354-7128 44 Upland Hts. Oakland CA USA 94612

756-30-7391 Karsen Livia 415 534-9219 5720 McAuley St. Oakland CA USA 94609

846-92-7186 Hunter Sheryl 415 836-7128 3410 Blonde St. Palo Alto CA USA 94301

893-72-1158 McBadden Heather 707 448-4982 301 Putnam Vacaville CA USA 95688

And there you have it. For sure, if you develop applications and are used to using the four part naming convention, and who wouldn’t since it is a normal convention, this dilemma poses a difficulty and challenge. However, due to this situation you can get around it by using the variation of the EXEC command. The nice thing here is the linked server doesn’t send extra metadata calls during the execution, it simply sends the command as is and let’s the backend do the work, delivering the desired results.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Mark Libner

    Hi Paul,

    I found this article on your ISUG blog. Thanks for reposting, I would have never found it. I have a (probably stupid) question regarding this. Do the ASE client tools have to be installed on the SQL Server machine? If so do you think that will resolve the error I’m getting below?

    I set up the linked server using the following procs: sp_addlinkedserver, sp_addlinkedsrvlogin and sp_serveroption. I see my linked server with sp_linkedservers. I created an ODBC connection on my machine that points to the ASE server and it successfully connects.

    I call a remote proc like so:

    exec (‘sp_get_branch_info ”0221”, ”CORPORAT”, ”MAILING”’) at testdb14

    I get the following error:

    Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “testdb14”.

    Thanks again,

    Mark

    (0) 
  2. Paul Vero Post author

    Hi
    Mark,

      

    The
    ASE SDK 15.7 software definitely needs to be installed where MSSQL Server
    lives. The server requires the backend drivers to access the backend (ASE in
    this case).

      

    You mention you make an ODBC connection to the ASE? What exact driver (you can get driver name from the ODBC Admin tool)?

    The error:

    Cannot initialize the data source object of OLE DB provider “MSDASQL” for
    linked server “testdb14”.

    tells me you are using an ODBC data source (testdb14) to connect to ASE from linked server. We recommend the use of the ASE OLEDB Provider to do this.

     

    The weird thing about linked server is it doesn’t always provide good messages.

      

    Show me the parameters use for the sproc calls to create the linked server.

     

    What MSSQL Server version?

    What Windows machine (version and whether 32 or 64-bit)

      

    Cheers,

    -Paul

                  

    (0) 
    1. Mark Libner

      I will see if I can get the SDK installed on the SQL Server box.

      I’m using the ASE driver v15.7.0.80 odbc version 03.51. I’ve setup both 32 and 64 bit.

      sp_addlinkedserver @server = ‘testdb14’, @srvproduct = ‘Ase’, @provider = ‘MSDASQL’, @datasrc = ‘testdb14’

      sp_addlinkedsrvlogin @useself = ‘false’, @rmtsrvname = ‘testdb14, @rmtuser = ‘mdl’, @rmtpassword = ‘******’

      sp_serveroption @server = ‘testdb14’, @optname = ‘rpc’ @optvalue = ‘true’

      sp_serveroption @server = ‘testdb14’, @optname = ‘rpc out’ @optvalue = ‘true’

      Microsoft SQL Server 2012 – 11.0.2325.0 (X64)

      Win7 64b

      Thanks again,

      Mark

      (0) 
      1. Paul Vero Post author

        Hi Mark,

        You’re using the 15.7 GA version. I am not sure how you get the ASE SD.  But this shoul dbe okay to start but might be better if you can get a current release (15.7 SP122).

        What you are showing me is using the ASE ODBC Driver (Provider = MSDASQL is the MDAC bridge to bridge the oledb calls to odbc api calls). I suggest you use the ASEOLEDB (The OLE DB Provider) as the provider.  You can use the Data Source tool found under the Sybase folder on the machine to create a data source for the OLEDB Provider and see if it will work.  We encounter issues with using ODBC driver under linked server and Engineering isn’t able to really fix too many issues there but will provide support using OLEDB for linked server when it comes down to it.

        Cheers,

        -Paul

        (0) 
        1. Mark Libner

          Hey Paul,

          I created the OLE datasource and changed the provider to ASEOLEDB. Now I’m getting the message: The OLE DB provider “ASEOLEDB” has not been registered. I don’t see any Sybase related names in the provider folder so I suspect that is because the pcclient isn’t installed on the server.

          They are getting a Java invocation error when trying to install the pcclient on the server box. The server is running the Windows Server 2012 operating system. It apparently doesn’t like the Java install.

          I feel like we’re making progress we’re just not quite there.

          Thanks,

          Mark

          (0) 
          1. Paul Vero Post author

            Hi Mark,

            Where is this pcclient from – what distribution? I am thinking maybe from the ASE developers edition? Or something else? Can you download the SDK from either old Sybase or SAP site – assuming you have licensing for ASE?

            Also, maybe we could do this through email – I can’t reply to your emails from this posting so maybe send your email address and we could communicate that way?

            Sounds like this pcclient isn’t liking to install on windows 7, but I usually don’t see problems with installing the 15.7 client software on windows 7.

            Cheers,

            -Paul

            (0) 
            1. Mark Libner

              The pcclient comes from “Adaptive Server Enterprise 15.7 PC Client CD”. The SDK he has tried to install comes from a zip file named 51045440.zip. I updated my last post to say the SQL Server is running Windows Server 2012. My development machine is Windows 7. Sorry if I misled you earlier.

              My email is mlibnerAtYahooDotCom

              Thanks,

              Mark

              (0) 
              1. Dawn Kim

                Hi Mark,

                PC-Client 15.7 base has issues installing on Windows 2012, this was not certified with Windows 2012 Server. You need to get EBF21161 PC-Client 15.7 SP100. Even with that version there was issues with the odbc administrator crashing with our odbc driver. So you would have to install SDK 15.7 SP110 or higher on top of the PC-Client.

                So your best bet is if you can get SDK 15.7 SP122 EBF 22089 and just install this and don’t worry about the PC-Client 15.7 part unless you need Interactive SQL(DBISQL). They pulled Sybase Central out of the PC-Client 15.7 SP100.

                So basically get SDK 15.7 SP122 and install this.

                Thanks,

                Dawn Kim

                (0) 

Leave a Reply