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?
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.
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.
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'' ')
Also from the sybase website.. they want you to purchase one of their products. They say:
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.
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)