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.
