MS SQL 7.0 to Sybase via linked server with OLE DB

[G]

We have created linked servers that connect to Sybase databases using the Sybase OLE DB provider and the Sybase ODBC provider.

The OLE DB provider does not allow the execution of a stored procedure. The message sounds like a permissions issue, but the id specified in the linked server has execute priv. for the stored procedure. It also does not allow the fully qualified name <server>.<db>.<owner>.<obj> to be used.

There are other issues with OLD DB and ODBC.

Anyone have any experiences making MS SQL to Sybase work smoothly?

[Anu]

Can mention what error you get.
that will help to give you a proper solution
-ANU





------------
G at 7/5/01 2:44:08 PM


We have created linked servers that connect to Sybase databases using the Sybase OLE DB provider and the Sybase ODBC provider.

The OLE DB provider does not allow the execution of a stored procedure. The message sounds like a permissions issue, but the id specified in the linked server has execute priv. for the stored procedure. It also does not allow the fully qualified name <server>.<db>.<owner>.<obj> to be used.

There are other issues with OLD DB and ODBC.

Anyone have any experiences making MS SQL to Sybase work smoothly?

[G]

Anu,
Thanks for the reply....

For this SQL

declare @ResidualReserve money
exec mytestole...watch_report_reserve_sp &#39;10033505&#39;, @ResidualReserve OUTPUT
select @ResidualReserve

the error message is....

Could not execute procedure &#39;watch_report_reserve_sp&#39; on remote server &#39;mytestole&#39;.

mytestole is defined as a Sybase server using the Sybase 12 OLE DB driver. The user has the correct permissions (execute) for the stored procedure in sybase. This works when I use the ODBC driver.

If we use the four (4) part naming convention, i.e., mytestole.dbWatch.dbo.watch_report_reserve_sp (the prefered method), the MS SQL server crashes!

Georgette

------------
Anu at 7/6/01 3:19:13 PM


Can mention what error you get.
that will help to give you a proper solution
-ANU





------------
G at 7/5/01 2:44:08 PM


We have created linked servers that connect to Sybase databases using the Sybase OLE DB provider and the Sybase ODBC provider.

The OLE DB provider does not allow the execution of a stored procedure. The message sounds like a permissions issue, but the id specified in the linked server has execute priv. for the stored procedure. It also does not allow the fully qualified name <server>.<db>.<owner>.<obj> to be used.

There are other issues with OLD DB and ODBC.

Anyone have any experiences making MS SQL to Sybase work smoothly?

[Anu]

Hi,


Check the sybase server

with

sp_helpserver

see whether the status has all these
rpc,rpc out,data access,use remote collation

otherwise set with sp_serveroption
and try

Also check the remote user login you are using in the linked server has all access to the remote server objects. otherwise create the remote user login and password same as the one you have in your current server.

This will surely work.


-Anu





------------
G at 7/10/01 3:11:50 PM

Anu,
Thanks for the reply....

For this SQL

declare @ResidualReserve money
exec mytestole...watch_report_reserve_sp &#39;10033505&#39;, @ResidualReserve OUTPUT
select @ResidualReserve

the error message is....

Could not execute procedure &#39;watch_report_reserve_sp&#39; on remote server &#39;mytestole&#39;.

mytestole is defined as a Sybase server using the Sybase 12 OLE DB driver. The user has the correct permissions (execute) for the stored procedure in sybase. This works when I use the ODBC driver.

If we use the four (4) part naming convention, i.e., mytestole.dbWatch.dbo.watch_report_reserve_sp (the prefered method), the MS SQL server crashes!

Georgette

------------
Anu at 7/6/01 3:19:13 PM


Can mention what error you get.
that will help to give you a proper solution
-ANU





------------
G at 7/5/01 2:44:08 PM


We have created linked servers that connect to Sybase databases using the Sybase OLE DB provider and the Sybase ODBC provider.

The OLE DB provider does not allow the execution of a stored procedure. The message sounds like a permissions issue, but the id specified in the linked server has execute priv. for the stored procedure. It also does not allow the fully qualified name <server>.<db>.<owner>.<obj> to be used.

There are other issues with OLD DB and ODBC.

Anyone have any experiences making MS SQL to Sybase work smoothly?