Bad Performance In Queries With Jet4.0 And Linked ODBC-tables To SQL-Server 2000

Jul 20, 2005

I changed from Access97 to AccessXP and I have immense performance
problems.

Details:

- Access XP MDB with Jet 4.0 ( no ADP-Project )
- Linked Tables to SQL-Server 2000 over ODBC

I used the SQL Profile to watch the T-SQL-Command which Access ( who
creates the commands?) creates and noticed:

1) some Jet-SQL commands with JOINS and Where-Statements are
translated very well, using sp_prepexe and sp_execute, including the
similar SQL-Statement as in JET.

2) other Jet-SQL commands with JOINS and Where-Statements are
translated very bad, because the Join wasn´t sent as a join, Access
collects 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 well

Could it be possible the Access uses a wrong protocol-stack, perhaps
Jet to OLEDB, OLEDB to ODBC, ODBC to SQL-Server or
Jet to ODBC, ODBC to OLEDB and OLEDB to SQL-Server instead of
Jet to ODBC and ODBC direct to SQL-Server

Does anyone knows anything about:

- Command-Interpreter of JetODBC, Parameters, how to influence the
command-interpreter
- Protocol-Stack of a Jet4.0 / ODBC / SQL-Server application

Thanks , Andreas

View 6 Replies


ADVERTISEMENT

Queries Against Linked Server To Informix Via ODBC Errors

Oct 9, 2007

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!

View 8 Replies View Related

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?

Anybody have any insight as to whats going on?

View 1 Replies View Related

ODBC Linked Tables

Mar 23, 2001

In order to use Access 97 as your frontend and SQL 7 as your backend on a
network (where the frontend is located on the same computer as the backend and
people just map to Access frontend) where security for the data is recognized
by your domain login account and what domain group you belong to..do you
have to have the same System DSN on everyone's computer for linking? I am
still having problems with people linking to the SQL server through the network and only my account is working correctly from my desktop. Everyone
else who tries to link gets an ODBC failure error message when trying to
open one of the linked tables. I have heard from one person that you must
have the same system DSN on every client's computer but to me that makes no
sense as I do not have the DSN listed on my individual computer...I am just
going through the network as myself..not as an admin..and getting directly
to the data. Everyone else can get to the shared folder on the server but
cannot get to the linked tables. Any ideas?

View 2 Replies View Related

ODBC Linked Tables Go Read Only

Jun 14, 2007

Hi,
I have an Access database Front End which use SQL server as a Back End. The two are connected using ODBC. Occasionally, some of the linked tables in Access go read only. I can't add or edit records. The only way I know to get round the problem is to delete the link and re-create it. Refreshing the link does not work. Can anybody suggest why this would happen, and the best way to fix it when it does occur?

Edited 12:10 06/14/07
Some extra info. At the same time it goes read only, the link loses it's primary key.


Thanks

Colin

View 1 Replies View Related

ODBC Readonly (viewonly) Linked Tables....

Aug 3, 2006

Hi,I have a prod database (main bread & bread DB) and have a small accessdatabase that sales team is using... I created a login inside SQL 2000and gave db_read permission and SELECT permissions... and public.Role of public is EXEC store procs and view some systables...I linked those tables that I gave permissions through ODBC link intoaccess db with the user that I created inside SQL as readonly user...but to my surprise when I ran a insert query from access on SQLtables... I was able to update and insert data... if my ODBC link isonly for readonly.. why was I able to unpdate & insert data into SQLtable?I don't want to give write/update/insert permissions for SQL tables tosales team... or anyone outside SQL 2000 server DB.Is there any easy way to create viewonly users inside SQL?I created users like this: security -logins -new login...select none serverrole.. db access (bread & butter DB)Thank you,hj******Pardon my English...

View 1 Replies View Related

MS Access Linked Tables To SQL Server 2000 Slow On Vista

Mar 13, 2007

I am using two almost idential laptops, one with XP and one with Vista, the only differences is that the XP laptop has 1G of RAM and running Office XP and the Vista has 2G RAM and is running Office 2007.

I have a MS Access database that has linked tables to a SQL Server 2000 database. The performance of the Access database on Vista is 5-10 times slower on the Vista machine. Just flipping through records or opening forms can take 5 - 15 seconds on the Vista machine while the XP machine takes 1 sec or less.

What gives? I looked at the CPU performance and the network performance while the Access database was busy flipping through records, the network traffic was < 2% and the CPU would spike to 40% on one of the CPUs (dual core) but would remain under 5% most of the time.

I also previously had Office XP installed on the Vista machine and it had the same performance issue so bought and install Office 2007 on the Vista machine and it did not solve the problem.

It seems that Vista is doing something that is slowing down Access with linked tables. Is this a issue between Vista and using an ODBC connection to SQL Server?

Thanks in advance for any help on this

View 1 Replies View Related

Performance Problem With Linked Sybase Tables

Jul 20, 2005

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

View 1 Replies View Related

Trouble Using ADOX To Create Linked Tables In Jet Database From An ODBC Datasource

Jun 5, 2007

Hai,

I am using ADOX to create linked tables in a jet database from an ODBC datasource.
The tables in the ODBC data source does not have a primary key.
so I am only able to create read only linked tables.But I want to update the records also.
I tried adding a primary key column to the linked table while creating the link.
but I am getting an error while adding the table to the catalog.

The error message is "Invalid Argument".

I use the following code for creating the linked table

Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal strProviderString As String, ByVal strSourceTbl As String, ByVal strLinkTblName As String)

Dim catDB As ADOX.Catalog
Dim tblLink As ADOX._Table

Dim ADOConnection As New ADODB.Connection

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strTargetDB & ";User Id=admin;Password=;")

catDB = New ADOX.Catalog

catDB.ActiveConnection = ADOConnection

tblLink = New ADOX.Table

With tblLink

' Name the new Table and set its ParentCatalog property
' to the open Catalog to allow access to the Properties
' collection.
.Name = strLinkTblName
.ParentCatalog = catDB

' Set the properties to create the link.
Dim adoxPro As ADOX.Property

adoxPro = .Properties("Jet OLEDB:Create Link")
adoxPro.Value = True

adoxPro = .Properties("Jet OLEDB:Link Provider String")
adoxPro.Value = strProviderString

adoxPro = .Properties("Jet OLEDB:Remote Table Name")
adoxPro.Value = strSourceTbl


End With

'Adding primary key,
'***** the source column name is "Code" ******
tblLink.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Code")

'Append the table to the Tables collection.
'******The exception occurs on the following line***********
catDB.Tables.Append(tblLink)

'Append the primary index to table.
catDB = Nothing

End Sub

If I avoid the line for adding the primary key,everything works fine,but the table ctreated is readonly.

Thanks in advance
Sudeep T S

View 4 Replies View Related

Linked Server ( Not Able To Access Any Tables Under LINKED SERVER From My DESKTOP Enterprise Manager

Mar 25, 2002

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

Thanks

View 5 Replies View Related

Linked Server And Distributed Queries

Feb 6, 2002

Hi there,
I am trying to link one sql server to other sql server(version 7.0). I was able to link server1 to server2 by creating an odbc source and am able to see the tablenames when i click on the linked server tables.
My problem is..when i am trying to query on these tables its giving me error saying "OLE DB provider 'MSDASQL' does not contain table xxxxxx"

i am using select * from servername.tablename.dbo.tablename.

Any help on this will be appreciated.

Ravi

View 1 Replies View Related

Queries Against Linked Server Time Out?

May 15, 2008

Ok, this is a little convoluted so bear with me.

I have a SQL 2000 SP4 box on Windows 2000 Server with the Oracle 11g Client installed.

I've created a linked server to an Oracle database that lives offsite (sites connected with a fairly high speed VPN link).

There's a view on the Oracle database I'm trying to access through the linked server using the four-part naming convention*.

If I execute a fairly simple query against that view, the query runs until it eventually times out two hours later. And by simple, I mean something like this:


select * from CGN_LMS..CGNLMS.V_PRODUCTION_SUM where FISCAL_PERIOD = 200803

That happens if I use the OLE DB provider from Oracle or the one from MS.

Now, if I run the same type of query against that Oracle DB from Excel or Access (using the ODBC driver), the query will complete in seconds, returning about 800 rows of data.

For fun, I created a blank Access database with one linked table that points to that V_PRODUCTION_SUM view on the Oracle database, and one Query within that Access database that SELECTs * FROM the linked table WHERE FISCAL_PERIOD=200803.

I then created a linked server on my SQL 2000 server that points to the Access database. Now, if I hit that saved Access query through the SQL 2000 linked server, it also returns the results in about 9 seconds. If I query the linked table in the Access database and specify WHERE FISCAL_PERIOD = 200803, it completes in about 6-8 minutes.

So I guess my question is... WTF?

How is it that this:


SQL 2000 ->OLE DB provider-> Oracle

Times out after 2 hours, while this:


SQL 2000 -> Jet driver-> Access -> ODBC driver -> Oracle

returns all my results in seconds/minutes?

Couple other side notes...

A lot of the queries that I test with using this linked server work just fine. In fact, with this particular query, if I were to just pull SELECT TOP 800 * with no WHERE clause, the query would run rather quickly (like in 4 seconds).

Also, if I use OpenQuery instead of the 4-part linked server name, the queries return results as quickly as I'd hope. That might work as a last resort, but we have existing applications that use the 4-part names already and we'd rather not have to suss out and change all those if we don't have to.




Any help or insight would definitely be appreciated.

Justin

View 2 Replies View Related

Linked Server - ODBC

Jul 25, 2000

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.

Any suggestions??

paull

View 1 Replies View Related

Linked Server To ODBC

Jul 14, 2002

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.]


thanks

View 1 Replies View Related

IDENTITY Column In SQL 2000 And Linked Tables In MS Access

Nov 30, 2006

Please help

We have an application written in MS Access. The tables are linked to a SQL 2000 database.
The problem is that sometimes insert a new record in a table freezes and times out after a while without anything has happened.
When installing the application the *mdb file is copied over to the C drive and an ODBC connection is written to the registry.
The application is used by many in the company.
We have problems on tables defined with IDENTITY columns. Can this be our problem and how can we solve it?

Regards Anna-Lena

View 6 Replies View Related

Linked Server Named Instance Queries

Feb 23, 2008

I am trying to perform a distributed query however have a situation I haven't dealt with before the linked server I add to do the query is a named instance (DVD_NASDOMINO). How would I specify this in a query as in the FROM part in a sql statement. I tried the obvious DVD_NASDOMINO.qlsdat.dbo.stmenqry and DVD_NAS.DOMINO.qlsdat.dbo.stmenqry.
Both returned errors in the query.

Thanks for the help.

View 1 Replies View Related

Distributed Queries Possible W-out Linked Server Registration?

Mar 13, 2008


We are migrating a database that makes use of distributed queries. In other words, it queries data from other databases on the same server, as well as from databases on linked servers.

The use of linked servers as well as linked databases (for lack of a better term) presents a challenge since we will need to register various new linked servers in the target environment. Ideally, we would like the database to be portable such that we will not have to worry about registering linked servers regardless of where it is hosted.

Is there a way to write distributed queries so that do not rely on linked servers/databases? For example:

SELECT * FROM [ServerName].[Database].[Owner].[TableName]


If there is such a method, it would make our database much more portable in terms of server migration.

TIA.

El Salsero

View 1 Replies View Related

ODBC Linked Server Problems

Oct 27, 1999

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.

View 1 Replies View Related

ODBC Provider For Linked Server.

Mar 4, 2008

Gurus,

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..?

Please help me to do this

Thanks
Krishna

View 2 Replies View Related

Connecting To A Linked Server With ODBC

Jul 20, 2005

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

View 2 Replies View Related

SQL Express Linked Server With ODBC

Nov 5, 2007



I have SQL Server 2005

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"


Major over and out

View 3 Replies View Related

Server Aliases/linked Servers Cause Remote Queries

Apr 4, 2008

Can someone please shed some light on what seems to me to be a common requirement.

If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).

Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.

It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?

If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!

I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.

View 2 Replies View Related

ODBC / Oracle Linked Server Problem

Feb 15, 2004

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.

View 3 Replies View Related

Linked Server Via ODBC And Stored Procedure

Mar 11, 2006

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

View 1 Replies View Related

Linked Server To Oracle Via ODBC, Return Less Records

Apr 22, 2006

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?

Thanks.

View 4 Replies View Related

Vista Business ODBC/DSN To Remote Windows 2000 Server / SQL 2000

Oct 8, 2007

Hi,
Just upgraded some development desktops to Vista Business. However we need
to still connect to some older remote windows 2000/SQL 2000 servers.

Trying to setup an ODBC system DSN on our Vista Business local desktop we get the
following errors -

-START ERROR WINDOW-
Connection Failed:
SQLState: '01000'
SQL Server Error: 772
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(SECDoClientHandshake()0.
Connection failed:
SQLState: '08001'
SQL Server Error: 18
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security Error
-END ERROR WINDOW-

Any help greatly appreciated as this is stopping us from making
database/table connections etc. We've checked the firewall setup and all is well there.

PS - we can still connect fine using XP or windows 2000 desktops and their
local DSNs.

View 8 Replies View Related

Return Records Through A Linked Server That Uses ODBC Data Source

Jul 20, 2005

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

View 3 Replies View Related

Newbie: Why Do I Have To Use OPENQUERY To A Linked Server? (ODBC To Firebird Database)

Jan 17, 2008

Hello,

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)

View 2 Replies View Related

SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables

Oct 7, 2015

I am using the following select statement to get the row count from SQL linked server table.

SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')

MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?

View 1 Replies View Related

Linked Server = Poor Performance

Jan 10, 2006

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.

View 1 Replies View Related

Linked Server And Query Performance

May 14, 2001

Hi,

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?

Thanks in advance.

Keith

View 1 Replies View Related

Linked Oracle Server Performance

Jun 6, 2002

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?

Any help, input or suggestions would help.

David

View 2 Replies View Related

ODBC Problems In Linked Server In SQL Server 2005

Feb 19, 2008

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

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved