MS SQL 7.0 To Sybase Via Linked Server With OLE DB
Jul 5, 2001
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?
Cannot create an instance of OLE DB provider "ASEOLEDB" for linked server "[SERVER_NAME]"
The current setup: Our primary database is on Sybase, I needed to extract hierarchical (nested) XML from multiple tables on Sybase. Although Sybase ASE 15.5 has `FOR XML` query capability it does now allow nested XML format. So we used the 'FOR XML', XML functionality in SQL Server (as we had a SQL Server 2008 database available which is used for a smaller system) to query Sybase tables through a Linked Server connection.Our sample nested XML Format is similar to:
I would like to create a linked server from SQL Server to Sybase IQ. I have created linked servers before so I know how to do that. However, I dont know the specifics of creating a linked server to Sybase IQ.
What are the parameters that are necessary to link IQ to SQL Server. Which Provider do I use? Do I need to install a special driver?
Has anyone had problems using an OLEDB linked server connection to Sybase ASE 12.5? I'm having major performance problems when I use string criteria in the where clause. Queries that return 1 row and execute in less than a second using a native Sybase connection take 40 seconds to run using the linked server OLEDB connection. If I use ints in the where clause performance is almost exactly the same between native and linked connections.
I´m trying to create a linked server to a Sybase database (.db file) so i can create some reports in sql 2005 (with Reporting Services and Report Designer). After reading all articles I did the following.
1) Installe Adaptive Server Anyhwere (Interactive Sql) 9.0.2 on the server. 2) Created a USER DSN to the Db file (Control Panel - odbc connections) and it works fine: The Settings for the ODBC are:
Database File: c:Project FolderDataBase.db As I said, when i go for "test connection", works fine.
3) Sql Management Studio - Object Explorer - Server Objects - New Linked Server, with settings as following:
General
Linked Server: lkDbNom
Server Type: Other Data Source
Provider: Sybase Adaptive Server Anywhere Provider 9.0
Product Name: Sybase
Product Name: dbNom (the dsn name, right?)
Security
Be made using this security context.- Remote Login: DBA; Password: SQL (same as DSN) All other settings, as default, click in OK and shows no errors (aparently it creates the linked server successfully). But when i try to query the linked server with:
SELECT * FROM OPENQUERY ('SYBASE', 'SELECT * FROM nom_Robot')
And i get the following message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SYBASE'.
Even better, if i go to the Object Explorer - Server Objects, i test the connection and it´s ok, but when i try to retrieve the catalog an error displays:
TITLE: Microsoft SQL Server Management Studio ------------------------------ Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The OLE DB provider "ASAProv.90" for linked server "SYBASE" reported an error. Access denied. Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "ASAProv.90" for linked server "SYBASE". (Microsoft SQL Server, Error: 7399) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
So, i really don´t understand what is missing in my linked server, i tried with MDASQL (OLEDB for ODBC) but it doesn´t even completes to create the linked server.
The database file is ok because, because in another server (one that doesn´t have SQL, only visual studio) i did the following:
1) Installe Adaptive Server Anyhwere (Interactive Sql) 9.0.2 on the server. 2) Created a DSN to the Db file, and it works fine. (copied the db file, so is local, no remote access), exactly the same setting as i did on the sql 2005 server. 3) Created a connection in visual studio 2008 (server explorer - data connections), i can retrieve sdata...but of course the provider is .NET Framework Data Provider for ODBC and i cannot use the same connection string.
I am having trouble linking to a Sybase server using the ODBC OLEDB provider, with either the sp_addlinkedserver stored proc, or the "linked servers" wizard in Enterprise manager.
The SQL I am executing is:
/* add server */ exec sp_addlinkedserver @Server = 'Linked Server Name', @srvproduct = 'Sybase System 11', @Provider = 'MSDASQL', @provstr = 'Driver={INTERSOLV 3.11 32-BIT Sybase};SRVR=servername;DB=targetdatabase;UID=bob; PWD=bob;'
(Note that this does not use a DSN - I have tried it both ways, but with no joy).
The SQL executes without any errors or warnings. However, when I try to use the linked server connection (eg., visually list the tables available by double clicking the linked server in the Enterprise Manager, or Select * From a table in the Query Analyser) it give the following error:
"Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error." (That is - generic error)
It just seems that I am not putting the connect string components in the right places.
Does anyone have any suggestions on how to set up a linked server to Sybase in this this way?
i successfully linked the sybase database to sqlserver. at first i can select a record, after i execute the same statement the result become different. i was looking for answer in the web but i can't find one. can somebody help me...
i suspect that sqlserver cache the result.. so i try to create a view for sybase database table. when i select the view it gave me the complete result but when i rerun the statement again it became inconsistent.
is there a cache or something in sqlserver. if there is how can i clear it.. so i will always get the fresh copy of the result.
I am getting error when I try Inserting data in sybase 12.5 using linked server from SQL2K5
I am able to select
Following is the code i am using.error is same for both stmts insert into l_syb_ibt.ibtqa.dbo.rajtest (id)values (1) insert openquery(l_syb_ibt, 'select id from rajtest where 1=0') values (1000)
please help.thanks in advance
following is the error OLE DB provider "MSDASQL" for linked server "l_syb_ibt" returned message "Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.". Msg 7343, Level 16, State 2, Line 1 The OLE DB provider "MSDASQL" for linked server "l_syb_ibt" could not INSERT INTO table "[l_syb_ibt].[ibtqa].[dbo].[rajtest]".
I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005. The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67. I have already installed the Sybase client software on the server.
I also created a SystemDSN on the SQL Server to connect to the Sybase server. I tested the connection and it was able to connect.
I ran the following code to create the linked server:
I then ran sp_tables_ex to make sure I could view the tables in the Sybase database. Here is the error message I get:
<code>
OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".
:confused: How do I tap advanced functionality like #temptable and @var from openquery? There is a linked server from SQL Server 2000 to Sybase 12 using Sybase.ASEOLEDBProvider. Executing an openquery statement from MS SQL will retrieve Sybase data; however, I have no advanced functionality available. When I try to use temporary #tables or declare @vars it returns this error... Server: Msg 7357, Level 16, State 2, Line 1, Could not process object '…OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Sybase.ASEOLEDBProvider', Query=…
Linked Server configuration: Provider options: AllowInProcess is checked Server options: Collation Compatible, Data Access, RPC and RPC Out are checked
Does anyone have a suggestion or alternative for this functionality? Thank you very much for your input.
We have been accessing databases on ASE 12.5 (on HPUX) from MS SQL Server 2000 (x32) successfully for years. We do this via linked servers we create on MS SQL Server. When creating the linked server, we would specify the Microsoft OLE Provider for ODBC as the provider, and the data source is the name of a DSN created with the Sybase ASE ODBC driver (4.20.00.67) that points the the HPUX box on which are the Sybase databases.
We have also gotten this to work just fine on MS SQL Server 2005 (x32) Standard Edition SP1. However, we cannot seem to get this to work on MS SQL Server 2005 (x64) Enterprise Edition SP1. The Microsoft OLE DB Provider is not available as an option when creating the linked server on MS SQL Server 2005 (x64). We have tried specifying the SQL Native Client as the provider, but this results in no connection being made and reports the following error:
OLE DB provider "SQLNCLI" for linked server "GCMTESTSQLNATIVE" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "GCMTESTSQLNATIVE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
Do we need to use a different ASE driver when accessing Sybase databases fom the MS SQL Server (x64) platform? Or is there a way to successfully create a linked server on MS SQL Server 2005 (x64) that can communicate with the 32-bit ASE ODBC driver?
I have two linked servers configured on my server(SQLServer 2000, Win2003). Both are configured to use Sybase ASE OLE DB driver, version 12.5. I have two differen OLE DB Obects configured in Sybase Configuration Manager. One object is configured to point to a Sybase 12.5 server in Boston, the other is configured for a server(same version) in Westboro. I can test connect from the SQLServer machine on each of these, and see that I am connected to each of the Sybase servers. However, my linked servers are only able to connect to the first OLE DB data source listed in the Sybase Configuration Manager. The linked servers are configured to use different OLE DB data sources, and no errors are thrown, but I can see that both actually go to the same data source. If I rename/re-order the OLE DB Data Sources, and make the second one the first one, I can connect to the other server. Is this a known issue? We are currently switching over to Sybase ODBC driver(not sure why we used the OLE DB driver in the first place), via which we can connect to both Sybase servers.
We have a need to retrieve Sybase data within a MS SQL Serverapplication. We are using SQL Server's linked database feature withthe Sybase 12.0 OLE DB driver. It takes 5 minutes to run a query thattakes 2 seconds from isql.Any suggestions?Thanks
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master] GO EXEC master.dbo.sp_addlinkedserver    @server = N'Machine123Instance456',    @srvproduct=N'SQL Server' ; GO EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password' Â
A question that may have been asked on many occasions, but what can be advised as the best way to migrate a Sybase 11.x DB to SQL 7.0, (without using BCP). Does DTS support such migrations and if so, where in DTS can this be done? I'd appreciate any basic help here to get me started on this exercise. MAny thanks
We have Trading Applications (Equities & Portfolio written on C++) on MS SQL Server 2000. Now the management is deciding to move it on Sybase 12.5. We currently don't have any issues on any matter, but because everyone is SYbase fan here, thinks applications are critical and we need to move to Sybase. Can anyone who has worked on both knows the major pros & cons of both the system. Actually I wanted these systems to be on MS SQL Server. I need solid reasons except being cheaper than Sybase to show why we should use MS SQL Server
If anyone can share their experiences, it would be really great.
Has anyone ever experienced compatibility and/or performance issues when SQL 2K and Sybase are run concurrently on the same server? I believe the server is using Windows 2K3.
Hi, I have a situation where in I need to insert a record in sybase database table whenever there is an update on a table in my sql server database. Can any one suggesst me hoe can I do it? thanks In advance Mohan
I need to configure snapshot replication (Pull) on Enterprise Edition of SQL Server 2005 (acting as Distributor, Subscriber) for Sybase server (acting as Publisher). I have the Sybase ASE client and ODBC driver installed on the Distributor. Can anyone help me add Sybase server as the publisher? Oracle publisher is allowed in Enterprise edition, but sales representative had said that we could have ODBC compliant server as the publisher. There is no option to select a different publisher other than SQL Server publisher, Oracle Publisher. If anyone knows how to do, please let me know.
To anyone who knows how to use sybase. One of our guys has put in a sybase system and not told the rest of us about it and know their is a problem and it is in my lap. Surprise, surprise. Just asking does antyone know how to rebuild a sybase database after it has been recovered from previous data and is now twice the size that it was origonally. Please help if any one can>
I want read only connection to Sybase server from MS SQL Server 2000. I think Link server should be best solution. Can anyone guide me as help shows link of Oracle but not sybase. any help is apreciated. I've written following on creating link server from ER ProductName : Sybase ASE Datasource : Server A providerstring: DRIVER={Sybase.ASEOLEDBProvider};SERVER=unixbox,45 10;UID=sa;PWD=abc1411'
I've installed Sybase client 12.0 and have created the DSN (it is successful, connection is established) but when I run this ActiveX Script from DTS Package it give me error Data Source Name not found and no default driver specified
Dim RS Dim oConn
Set oConn = CreateObject("ADODB.Connection") msgbox "set oConn" Set RS = CreateObject("ADODB.Recordset") msgbox "RS" (I've tried both open statement in both it gives me same error) oConn.Open = "Driver={Sybase ASE ODBC Drivers};Srvr=Server1;Uid=sa;Pwd=password"
I'm trying to configure replication between a Sybase ASE Database Server and SQL Server 2000. Sybase ASE Database will be the Publisher and SQL Server will be the Subscriber. Can anyone advise me on how I can go about setting up the replication for this?
Hi, Could anyone refer me any kind of tools or process to convert from SYBASE to SQL SERVER 2000. My complany is trying to migrate from SYBASE to SQL SERVER 2000. Any help is greately appreciated! Thanks.
Could anyone please let me know which is the best way to Synchronise a Sybase database with a MS SQL Server 2000 database.That is , the changes made in the Sybase database should be reflected in the SQL Server database.
I have a module in power builder with Sybase database as its backend. The other modules related to the same application are running on Delphi with SQL server as backend.
My requirement is to sync between the Sybase database and SQL server (2000) in order to update the SQL server with all the transactions ( online ) done in the Sybase database.
