Performance Issues With Linked Server's With OLEDB && ODBC
Jan 6, 2004
Hello,
I have a SQL Server instance on my local computer and an Oracle
Database on a remote server. I want to run queries from tables
within both databases and am using linked servers to accomplish
this.
I configure my linked server in SQL Server using the Microsoft
OLE DB Provider for Oracle and can run queries using sql server
tables and oracle tables. However, even the simplest queries
take more than 10 minutes to run.
I have the Oracle 9 Client Installed and MDAC 2.7. I configured
my registry settings to match oracle 9's settings.
However nothing i do improves the performance of the queries
through the Microsoft OLEDB Provider for Oracle.
When I use MS Access, or use an ASP page with the following
string:
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "dsn=Oberon;uid=mfs;pwd=mfs;"
I implement the ODBC driver that I configured in my system DSN
and both run the same queries very fast. The data comes back
without a problem.
So i believe I have narrowed down my problem to the OLEDB
Provider. However, SQL Server does not give me a choice to use
the ORACLE native ODBC Provider.
So then I tried using Pass-Through Queries and this worked alot
faster in SQL Server...I am completely confused as to whats
going on.
Linked Server Query that takes over 10 minutes:
SELECT * FROM OBERON..LOGS.DATA_PHOTO ldp where Machine=301 AND C3='I051097';
Pass-Through Query that works faster:
select * from Openquery(OBERON, 'SELECT * FROM LOGS.DATA_PHOTO ldp where MACHINE=301 AND C3=''I051097''')
From researching pass through queries, my understanding is that
it actually uses ODBC to give the whole query to the remote
database where the query is then run and the results are passed
back as a table, thats why you say select * from (query)...however if my understanding is correct, then you cant combine tables in different databases very easily. And will it work with .asp and .aspx pages?
I changed from Access97 to AccessXP and I have immense performanceproblems.Details:- Access XP MDB with Jet 4.0 ( no ADP-Project )- Linked Tables to SQL-Server 2000 over ODBCI used the SQL Profile to watch the T-SQL-Command which Access ( whocreates the commands?) creates and noticed:1) some Jet-SQL commands with JOINS and Where-Statements aretranslated very well, using sp_prepexe and sp_execute, including thesimilar SQL-Statement as in JET.2) other Jet-SQL commands with JOINS and Where-Statements aretranslated very bad, because the Join wasn´t sent as a join, Accesscollects the data of the individual tables seperately.Access sends much to much data over the network, it is a disaster!3) in Access97 the same command was interpreted wellCould it be possible the Access uses a wrong protocol-stack, perhapsJet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server orJet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead ofJet to ODBC and ODBC direct to SQL-ServerDoes anyone knows anything about:- Command-Interpreter of JetODBC, Parameters, how to influence thecommand-interpreter- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server applicationThanks , Andreas
I am writing an interface between SQL Server and Access. I have chosen to use a linked server using the Microsoft.Jet.OLEDB.4.0 provider. One of the requirements is to have multiple SQL statements in one transaction, which is where I am encountering the problem. (see below)
BEGIN TRAN
INSERT INTO ACCES...Table
Msg 7390, Level 16, State 2, Line 2
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "HES" does not support the required transaction interface.
I am having a really really hard time finding documentation on the Microsoft.Jet.OLEDB.4.0 provider, specifically I want to know for sure if it supports the ITransactionJoin interface.
The main question is - does MS Access linked servers support transactions?
Thank you for your help,
I have exausted all resources available to me in finding out the answer.
When executing the select statement I get following error message
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLS" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLS".
Strange is that when I run this code on dev machine it works fine. But when I try to execute it on my second (pilot) machine it ends with the error message posted. I'm not a admin on the pilot machine so I don't know it's exact configuration but I'm beeing assured that the only difference is that on pilot machine there is no Visual Studio installed.
I want to create a table in MS-Access. So I have written the following query.
Code Snippet select * from Openquery(KKACC, 'select * Into Temp from Table select 1 as col1')
But I am getting the following error.
Code Snippet
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "KKACC" returned message "Syntax error in FROM clause.". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "select * Into Temp from Table select 1 as col1" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "KKACC".
How can I resolve this error. Does any one have any work arround for this..?
I have an SSIS job that is pumping to a SQL Server Destination, hundreds of gigabytes of raw text files. Today I received this strange error ? Also, how would I make the data tasks more stable and robust so that this doesn't cause package failure (retries, or something?)
[SQL Server Destination [4076]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
I have a linked server defined on a SQL Server 2005 SP2 standard edition server using the IBMDASQL OLEDB driver. The linked server has been defined and working for months when used from a SQL Server authenticated session. I started converting our developers to Windows Authentication and access to the linked server is denied when used from a Windows Authenticated session. Here are the error messages:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "IBMDASQL" for linked server "DB2ARUBA" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "IBMDASQL" for linked server "DB2ARUBA".
The linked server security is defined to login to the IBM iSeries using a fixed user and password regardless of the login using the linked server.
In order to analyze the problem, I created one sysadmin SQL Server authenticated login and one sysadmin Windows Authenticated login. The Windows Authenticated login refers to a domain login having administrator priviledges on the local SQL Server box.
So, when I connect to SQL Server using the SQL Authenticated login, a query against the linked server works. When I connect to SQL Server using the Windows Authenticated login, the exact same query against the linked server fails with the messages above.
After installing sql2005 sp2 a simple select query to a linked server reports the following error message:
Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.Msg 0, Level 20, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded. Before installing SP2 we used sql2005 without any service packs, the linked server worked fine.
The linked server is a Visual FoxPro database.
After uninstalling and installing the 'Microsoft OLE DB Provider for Visual FoxPro 9.0' the issue stil remains.
I have a linked server set up to a FoxPro database. I have used the 'Microsoft OLE DB Provider for ODBC' as the Provider name, the datasource name is 'TestFoxPro' and the Catalog is AssetTracker.
I have set it up and I am able to see the table objects in the Enterprise manager, but I am unable to select from the tables in the Query analyzer. I I have tried
select * from Test2.assettracker.dbo.assets
but i get the following error
Server: Msg 7312, Level 16, State 1, Line 1 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.
So i have tried three part name, but then it does not find the object.
Hi,, I am trying to create linked server in sql2000 using odbc which point to foxpro free files. I tryed a lot but the following errro message is displayed:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]File 'data1020.dbf' does not exist.]
I created a Linked ODBC Server named HP3000, I can view the tables in enterprise manager, but I cannot SELECT the server in my SQL statement. Any pointers would be greatly appreciated.
I cant find any odbc provider from my linked server.(SQL 2005) I am using MDAC 2.8 SP1 ON WINDOWS XP SP2 on my machine. What can i do to get odbc provider..?
I'm currently trying to access data from a Cache DB using MSSQL. I havelinked the Cache server through an ODBC connection. I can see in the LinkedServerexpansion all the tables in Cache for the File(?) I want to access.Here is my problem: Normally to access a linked sever I would do thefollowing:select *from ServerName.DatabaseName.dbo.TableNameI have triedselect *from ServerName.DSN.dbo.TableNameselect *from ServerName.DSN.SQLUser.TableName ("SQLUser" is the owner in Cache)None have worked (error no such object...)What is the syntax to select data on a linked sever via an ODBC connection?In the Linked server set up I have also check the RPC and RPC Out options...My ultimate goal is join tables in Cache and MSSQL into MSSQL.Any help would be greatly appreciated!Thanks,-p
I have a linked server using an ODBC System DSN setup. A DB with a view to access on of the table from the Acomba DB.
This view work just fine when I logged in using the Windows Authentification.
If I create a user (bla) with "sysadmin" as server roles the user bla is the owner of the DB (he also created the DB) He can't access that view. But can access any tables from the same DB
The error I keep having is the following :
Executed SQL statement : SELECT * FROM OPENQUERY(ACOMBABD, 'SELECT * FROM Customer') AS derivedtbl_1 Error Source: .NEt sqlClient DataProvider Error Message: Cannot initialize the data srouce object of OLE DB prodiver "MSDASQL" for linked server "ACOMBA"
This is a problem that never get solved, sometime I can use other way to avoid it, but havn't found a solution yet, i hope I can get some more idea here.
I am using SQL 2005, when I run
select * into #import1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\ws8webjeff2.xls', 'select * from [jeff2$]')
I get
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
when I try to compile a SP with that statement in it, I get the same error, like
create stored procedure test
as begin
select * into #import1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\ws8webjeff2.xls', 'select * from [jeff2$]')
end
so it seems the error may not relate to the real file, since at the compile stage, it should not check the real file?
On my live db, after I restart the SQL service, the statement will work, after a while, one or several days, I get the same error again. I can not restart my live db quite often for sure, so now I have another backup db server, I need run the statement on the backup server and then read the data from there.
I have the same problem at two places, both use SQL 2005.
So far there are three questions
1, why it works after restart, but only last for a while? something about memory? since the backup db seldom need restart and work fine after many days.
2, why it gives error in compile stage?
3, why two dbs in different Enviroment has the same problem
The most answer I have gathered so far is permission issue, true I got similar error if the import file is located in a place which SQL has no right to access. But in this case, it should not be.
I have a SQL server that I am trying to link to a number of Oracle environments. After much tuning, we managed to achieve this although the four-part naming was not possible and we had to use Openquery and run pass throughs.
Nothing in our configuration has changed and SQL Server is no longer able connect to the linked databases. The Oracle client on the PC is fine and is able tnsping any of the remote databases. I am also able to create ODBC connections to the remote databases on the SQL box that are fine.
Using a datalink in DTS, I can connect to the remote databases. This suggests to me that there is something wrong within the actual database links. I have set them up using the working ODBC DSN's on the SQL box.
If I try and run a query against them in Query Analyser, I get the following error message :
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA' reported an error. [OLE/DB provider returned message: ORA-12154: TNS:could not resolve service name ] OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
If I click on the tables icon in EM to view the remote catalogues I get the following error :
Error 7399: OLE DB provider 'MSDORA' reported an error. OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
Any help that could be give on this would be greatly appreciated.
HiWe're trying to use call a stored procedure to update information in aremote Ingres database. We've linked the server, and can readinformation using SELECT * FROM OPENQUERY (..........), but we can'tfind a suitable syntax for executing a procedure.Using SELECT * FROM OPENQUERY and passing the EXEC statement in astring gives a message about not returning any columns - not surprisingas there aren't any, and trying to execute the procedure more directlyusing:-EXECUTE abrs..vipdba.ats_reader_pi0 ........Gives the errorCould not execute procedure 'ats_reader_pi0' on remote server 'abrs'.[OLE/DB provider returned message: Parameter type cannot be determinedfor at least one variant parameter.]Any bright ideas?Chloe
I have an Oracle 9i server. To access the data in Oracle, I setup an ODBC connection to it and am able to return data from it using WinSQL (a general ODBC database client). The SQL statement is simply "SELECT * FROM COLOR" and all 133 records returned properly.
I need to copy the data from the Oracle server to SQL Express, therefore I set up linked server (by Microsoft ODBC provider) using the same ODBC connection as described above.
The problem is: only 32 records returned.
May I ask if there is any problem to this linked server setup?
What can be the reason(s) why I can't get data from a linked server using an ODBC datasource that works fast & fine from MS Access?
I have an ODBC connection (System DSN) configured for an Informix ODBC driver. The Test button (belonging to this driver-setup) reports a successfull connection test. Getting data from this database by linking tables in MS Access works fast and easy. But I have tried for many days now to setup a linked server from SQL Server (2005) Creation goes fine, but as soon as I issue a query, (e.g. 'select * from infrem723...remotetable' or using 'openquery') I get the following error: ---
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "infrem723" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "infrem723".
---
Why is Access able to read what "MSDASQL" cannot? I am desperate - can anybody help?Thanks a lot!
Is anyone using OLEDB instead of ODBC? If so why did you choose to use OLEDB vs ODBC? Our application is written in VB and we have a developer that believes switching to OLEDB from ODBC would be beneficial to us. Please give any information that you have! Thanks.
I am working on a large ASP application(s) which uses SQL Server 2K on the back end, ADO 2.7 for Data Access. The current connection strings are setup to use older ODBC drivers. I wanted to change them to OLEDB for performance gains and better support in the future.
The problem encountered is that once the driver is changed the pages become riddled with errors. There are many piece of ADO code that are supported using the new driver. Many are cursor issues or code dealing with identity columns.
Has anyone else gone through this process?
Is there a comprehensive listing of those methods that are not compliant between these two drivers?
Hi,I used sp_addlinkedserver to link to a remote server through ODBC.When I execute select count(*) from LinkSrv.SI.DBO.SIHeader in SQL QueryAnalyzer. It returns 13705 records. But when I execute select * fromLinkSrv.SI.DBO.SIHeader. It only return 885 records. If I specify somecolumns, select ODCOMP, ODPONO, ODVDCD from LinkSrv.SI.DBO.SIHeader.It returns more records, 1213 records.I guess there is something limit the return storage, but I can notfind it.Any suggestion will be appreciated. Thank you
pls. let me know where I could post if this is the wrong place.
I have a Firebird 1.5 application. I created a linked server from my SQL Server 2000 to the firebird database. In SQL Server Query Analyzer I get errors from various ODBC drivers with "normal" queryies like
SELECT LVNR FROM LINKEDSRV...LVVERW
Pls. note, this all works perfectly in MS Access databases with ODBC-Links to Firebird!
From a programmer of a commercial ODBC driver I heard that this problem may be caused internally by SQL Server, there may be no solution possible in the ODBC driver. One workaround would be to use the OPENQUERY-Syntax like
SELECT * FROM OPENQUERY(LINKEDSRV, 'select LVNR from LVVERW ')
Are there any other solutions? Are there any known issues with firebird odbc-drivers and sql server? Are there any known good drivers for the use with sql-server? What is the purpose of OPENQUERY - workaround ODBC problems? Are there any settings in SQL Server 2000 (2005 Express) that could help? Are there any settings in ODBC DSN that would help?
regards
arno
PS: Here are my favorite error messages
Error -2147217900 [OLE/DB provider returned message: Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 89 "Col1014"] (Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 01000) (NativeError: 7312)Error -2147217900 OLE DB-Fehlertrace [OLE/DB Provider 'MSDASQL' ICommandPrepare:repare returned 0x80004005: ]. (Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 01000) (NativeError: 7300)Error -2147217900 Der OLE DB-Provider 'MSDASQL' meldete einen Fehler. (Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 42000) (NativeError: 7399)
This "tricky" query does not work: SELECT LVNR FROM LINKEDSRV...LVVERW;
Error -2147217900 OLE DB-Fehlertrace [Non-interface error: Column 'ERHALTENABSCHLAG' (compile-time ordinal 35) of object 'LVVERW' was reported to have a DBTYPE of 5 at compile time and 131 at run time]. (Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 01000) (NativeError: 7300)Error -2147217900 Der OLE DB-Provider 'MSDASQL' hat inkonsistente Metadaten für eine Spalte übergeben. Die Metadateninformationen wurden zur Ausführungszeit geändert. (Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 42000) (NativeError: 7356)
We are trying to extract data from DB2 on an as400 machine. Can we simply use SQL statements to extract the proper data that we need from as400 using the SQL Server 7.0 functionality? How do we do that?
We think this needs to be done via DTS(Data Transformation Service).
Using a query through a linked server is giving tremendously reduced performance. Is part of the problem linking from a SQL 2000 to a SQL 7.0 database? Are there any other tips out there? Thanks.
I am now facing a problem related to the linked server. I created the connection between server A and B as linked servers. When I execute the following SQL statement on server A,
select * from B.database1.dbo.tableA where id ='12345'
I can get the results within couple of seconds. But the similar query would take several minutes if I switch the server name in the query from B to A and run it on server B! The tables on server A and B actually have the same sizes and the same indexes.
Do you have any clues and suggestions on this issue?
Anyone have any suggestion on increasing the performance going over a linked server? IS this connection persistent? Or does it establish a connection with each reference? Any other Providers for the connection other than Microsofts OLE DB Provider for Oracle? I imagine that building index's on the referenced oracle tables would help?
Actually i have linked server in SQL 2000 with Provider:Microsoft OLEDB for ODBC which is working perfectly fine. I don't know the password for this linked server too.
Now i want to create same linked server with same provider in SQL server 2005 for Microsoft oledb for ODBC but they don't have this provider in 2005. How can i make it to work.ANy expert who knows this.
and how to use same password in linkedserver in 2005
i notice that in toolbox panel, there are these kind of different dataadapter and dataset, what is difference between them, and under which situation we use which one?
I am currently querying two databases - One in SQL Server and one Oracle, to find records which are in one but not the other (essentially a reconcilliation) this is working fine in MS Access, using 2 passthrough queries to return the results of the 2 databases, then another query to find the data in one but not the other.
I decided to try the linked server approach as I thought this would give me enhanced performance - but strangely enough when I query the oracle database from MS Access the query takes about 22 seconds - whereas when I query the linked server from SQL Server the same query takes about 55 seconds.
The only difernce I can see is the passthrough query in MS Access is connecting using ODBC, an the linked server is connecting using Microsoft OLE DB Provider for Oracle.
