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 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.
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
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]".
can anybody tell me about Linked Servers and their uses and how to add a linked server to my Sql Server 2005. any help on this would be highly appreciated.
I have been trying to Link two sql servers on two different machines over the Internet without any luck. Can someone point me to information about doing this with good examples?
I currently have a main SQL Server which had a column on the majority of the tables. This column also had a check contraint on it. I dropped the column and the constraint and I now get the following message when trying to query the tables through a linked server 'OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.'.
Hi All, I have successfully linked a server and had SQL queries running OK for a few weeks, but today I get the following message....
"OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time." (Server: Msg 7353, Level 16, State 1, Line 1)
I've dropped the link and recreated it but I still get the same error message. Can anyone help???
I have a DTS package that requires me to link 2 servers and query both in order to get my set of records that I want to Transform. My query runs fine in QA. I copy and paste it into the SQL Query window of the transformation. I click Preview and all that happens is a quick flicker of the DTS window. When I go to Transformations, there are no source columns.
Here is a copy of my code that I'm trying to use: ------------------------------------------------------- declare@start_dteas datetime, @end_dteas datetime
SELECTT70.* FROM CRMDEV02.MDCORE.DBO.T70_MD_UNIVERSE_RELATIONSHIP_M A NGT AS T70 JOIN #RUNS ONT70.RUN_ID = #RUNS.RUN_ID
DROP TABLE #RUNS ------------------------------------------------------ Is there an issue with Linked Servers and DTS? Any help would be greatly appreciated.
Hi Gurus, I am looking for literature to find out Pros and Cons of Using DTS Verses Linked Servers in SQL 7.0.
My requirement can be done by either DTS or Linked servers. But I would like to know more about resorce utilisation of these tecniques before making a decission.
When I create linked server using integrated security and <they will be impersonated> option I get this: Server: Msg 18456, Level 14, State 1, Line 1 Login failed for user '' MS Technet says that SQL Server 7 doesn't allow double hops and to use mappings to standard security login to work around. @#%%~~@@#@#&^%@# Impersonating to version 6.5 works fine. Is there any way to link servers using ONLY integrated security? Any help would be much appreciated. Thanks
I have setup a linked Informix server in SQL7.0 and I am trying to create a simple View with the following SQL statement
SELECT doc_code FROM FOURSITE.foursite.informix.watdoc WHERE (order_no = [PV01963B ])
I am getting the following error:
--------------------------- SQL Server Enterprise Manager --------------------------- [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema. --------------------------- OK Help ---------------------------
Hi. I'm working with SQL Server 7.0 and I've been reading about the linked server option. My question is, if it's possible to link a server with a SQLBase database 7.01.
I have download some drivers to my computer, the odbc driver for sqlbase and the sqlbase ole db driver as well, but when I try to add a new linked server in the enterprise, in the provider name option I can not see any provider name addressing to Centura or Sql base driver.
Has anybody an idea how to make this connection then? or the right way for doing it??
Thanks for any help can be offered. Regards, Elvia. :)
I have two 7.0 servers and I want to run a distributed query.
I did sp_addlinkedserver to link server B to server A and I did sp_addlinkedsrvlogin for a specific login. After adding login when I try to access a table on server B it says login failed
All the logins are NT authenticated (in both the servers, So both servers have same logins.)
Hi all I try to configure Linked Servers from the query analyze , and have some troubles. My steps are : 1. from the enterprise mennenger , add 2 new servers : srv1 , srv2 (both SQL7) 2. from the query analyzer of srv1.master: "sp_addlinkedserver 'srv2'" 3. from the query analyzer of srv1.master: "sp_addlinkedsrvlogin 'srv2',false,null,'sa','myPass'" 4. select * from srv2.pubs.srv2Table 5. I get the next error :"error 6 : Specified SQL srever not found."
This is my problem I have a Server A and Server B. There is a master table in Table A. I link Server A and Server B. When I look in Under Linked Server tables of B I can only C tables in Server A not the Server B 's Tables. Dpn't know what is happening. Could SOmebody help
I have two servers and they are linked servers. When I query a table on ON Box2 from Box1 The distributed query works fine but when I tried the oppisite it says 'SQL Server does not exist or access denied.' What could be the reason.
