Querying SQL 6.0 Server From SQL 7.0 - SQLOLEDB Error
Mar 31, 1999
I am remotely logging onto a MS SQL 6.0 server using sp_addlinkedsrvlogin.
When I try to query against the table on this server I get the following error
message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: The data source can not be used, because it DBMS version is less than 6.5.0.]
I know that 6.0 doesn't support OLEDB but shouldn't 7.0 be able to access this
using odbc. Do I need to do anything other than set up the dsn on odbc.
Any feedback on this would be great.
Cheers,
bhanly@apcc.com
Window Server 2003 R2 Standard Edition (x64) SP1 Sql Server 2000 8.00.2039 SP4 Enterprise Edition (32 bit version)
A linked server is configured to a sql 2000 server and when I execute sql statement SELECT * FROM [LinkedServer].[Database].[dbo].[TableName] it gives following error message: -
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IUnknown::QueryInterface returned 0x80004005: The provider did not give any information about the error.].
In case if anyone has a solution to it, please let me know.
I am trying to execute a query on a table which is in 6.5 from 7.0
I get the following error...
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. [OLE/DB provider returned message: The catalog stored procedures installed on server WEB_SERVER are version 6.50.193; version 07.00.0330 or later is required to support IDBSchemaRowset. Please contact your system administrator.]
I've setup a linked server in SQL 2005 x64 SP2 to retrieve data from OS/400 DB2. When I perform a query on the linked server (select * from openquery(<linkedserver>, "select * from LIB.FILE1'), the following error was returned.
Error: Msg 7372, Level 16, State 4, Line 1 Cannot get properties from OLE DB provider "IBMDASQL" for linked server "<linkedserver>".I used the same linked server setup procedure on another SQl server with same configuration (but SP1) 6 months ago and it was OK.SQL Server Configuration:SQL 2005 x64 SP2 Windows 2003 SP1iSeries Access V5R3M0 patch SI24723 Linked Server Script: /****** Object: LinkedServer [OS400] Script Date: 05/02/2007 15:21:24 ******/ EXEC master.dbo.sp_addlinkedserver @server = N'STEMMS1', @srvproduct=N'OS400', @provider=N'IBMDASQL', @datasrc=N'<linkedserver>', @catalog=N'S654803D' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'connect timeout', @optvalue=N'60' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'query timeout', @optvalue=N'120' GO EXEC master.dbo.sp_serveroption @server=N'STEMMS1', @optname=N'use remote collation', @optvalue=N'true' I've tried searching the Internet for solution but yielded no results. Can anyone help?
I created an Image for the Database in sqlserver 2000. When I am querying directly the database as "Select * from employee", it is returning the result set.
But, when I am querying Database image with same query. It is giving error
Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'Employee'
It is expecting the Database Owner. If I do query like "Select * from [Owner].employee", it is working fine.
My requirement is not use owner in queries, it should be simple query ("Select * from employee").
Can any one, help me out. Thanks in advance. Bhupesh
I am having trouble logging into my sql server from an asp application using SQLOLEDB.
I am running XP, IIS5 and SQL Svr 2000 on my developement machine. I am able to log into the database using localhost/application.
When I port the code and database over to the server (Server 2003, IIS6, SQL Svr 2000), I am unable to connection using the actual url of the site.
I set the login up on the logins for the database manager along with password (using multi, not Windows authentication). Added that user to the database security logins, giving it dbo, srvr admin, etc. Like I said, works on my dev machine.
The SQLOLEDB request is Provider=SQLOLEDB;Data Source=(local);User ID=UID;Password=PWD;Initial Catalog=DBName.
I set the database up as a System DSN in ODBC, using SQL Server Authentication. The connection tests out just fine.
When I attempt to connect, I get an ADO message indicating that the user id that I supplied could not sign in to the server.
This is driving me nuts.
Does anyone out there have any ideas as to what I have missed on the server vs local developement machine?
Hi, I was making a linked server connection between 2 SQL Server 2000 servers (both running on Windows 2003 Server), and I was creating an insert trigger in one of the SQL Server table to insert the same value to the other SQL table. But when I try to make an insert to the table, the following error occurred :
Server: Msg 7391, Level 16, State 1, Procedure <triggername>, Line xx The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
I tried to change the trigger into a SELECT statement only and the error still occurred. But when I tried to insert a row directly to the linked server via Query Analyzer, it succeeded. I have read KB 280106, KB 306212, and KB 329332, and I've tried SET XACT_ABORT ON statement, but none of them can solve the problem. If there's anyone who has idea on what's wrong or has a suggestion what I should try please help. Thank you in advance.
I have a DTS package in SQL Server 2000 which brings together data from different sources, including from an Analysis Servcies cube, and compares it. Usually this runs perfectly, but sometimes I get an unexpected internal error from the Data Pump task which queries the cube - no other information, just the error.
This is usually when I have run the package once (manually from Enterprise Manager), then changed a global variable and run it again.
The cube it is querying is a virtual cube and the measures are calculated cells (NOT calculated members).
I have designed a CV database with complete CV stored in a TEXT field. There is a keyword search which queries the TEXT field also. The query conditions are defined in T-SQL submitted through an ASP page. There is about 20,000 records now. Now while querying the database for keyword search I am receiving time out errors. Is there any solution other than Index server to rectify this situation. How can I speed up the query execution time. Please advise.
Hello, I've found some strange behavior of SQLOLEDB provider. When I set "Preserve on Abort" on true and delete some row in transaction, I cannot see this row after transaction rollback. Is this correct? My code: Dim Conn As ADODB.Connection Dim recSet As ADODB.Recordset Dim cmd As ADODB.Command Dim c1 As Long, c2 As Long ' ## Open a connection Set Conn = New ADODB.Connection Conn.Open "Provider='SQLOLEDB';Integrated Security=SSPI;Data Source='PENASSQLEXPRESS';Initial Catalog='PokusDB';" ' ## Open a table Set recSet = New ADODB.Recordset '## Open cursor Set cmd = New ADODB.Command Set cmd.ActiveConnection = Conn cmd.Properties("Preserve on Abort") = True cmd.CommandText = "SELECT * FROM zkouska order by text" recSet.Open cmd, , adOpenStatic, adLockOptimistic, adCmdUnknown '## TransBegin Conn.BeginTrans '## CursorMove recSet.MoveFirst '## RecordDelete recSet.Delete adAffectCurrent recSet.Requery adOptionUnspecified '## TransAbort Conn.RollbackTrans '## CursorMove recSet.MoveFirst c2 = recSet.Fields("cislo").Value If c1 <> c2 Then Err.Raise 1000, "Transaction test failed" End If
When I call Requery after RollbackTrans everything is OK, but what is then "Preserve on Abort" for? Also I must call Requery after delete or I will get an error after MoveFirst. Do I miss something? Hello, I've found some strange behavior of SQLOLEDB provider. When I set "Preserve on Abort" on true and delete some row in transaction, I cannot see this row after transaction rollback. Is this correct? My code: Dim Conn As ADODB.Connection Dim recSet As ADODB.Recordset Dim cmd As ADODB.Command Dim c1 As Long, c2 As Long ' ## Open a connection Set Conn = New ADODB.Connection Conn.Open "Provider='SQLOLEDB';Integrated Security=SSPI;Data Source='PENASSQLEXPRESS';Initial Catalog='PokusDB';" ' ## Open a table Set recSet = New ADODB.Recordset '## Open cursor Set cmd = New ADODB.Command Set cmd.ActiveConnection = Conn cmd.Properties("Preserve on Abort") = True cmd.CommandText = "SELECT * FROM zkouska order by text" recSet.Open cmd, , adOpenStatic, adLockOptimistic, adCmdUnknown '## TransBegin Conn.BeginTrans '## CursorMove recSet.MoveFirst '## RecordDelete recSet.Delete adAffectCurrent recSet.Requery adOptionUnspecified '## TransAbort Conn.RollbackTrans '## CursorMove recSet.MoveFirst c2 = recSet.Fields("cislo").Value If c1 <> c2 Then Err.Raise 1000, "Transaction test failed" End If
When I call Requery after RollbackTrans everything is OK, but what is then "Preserve on Abort" for? Also I must call Requery after delete or I will get an error after MoveFirst. Do I miss something?
Can I use the "SQLOLEDB" provider to connect to a SQL Native Client? Follow-up question: does the "SQLOLEDB" provider use the same TDS transport protocol as the SQL Native Client provider?
It seams as if the Connect Timeout property is not working correctly if the server that one is trying to connect to is down. This is my connection string.
if we see provider=sqloledb in the connection string of a legacy sql server 2000/2005 system we're trying to understand better, can we conclude that ODBC is definitely not in the client picture, that possibly OLEDB is and that SQL Native isnt? Can we conclude for sure what middleware must be in use?
My limited understanding of this "middleware" space is that ODBC is old and restrictive, OLEDB is a lot more state of the art and general, and that SQL Native is more proprietary than OLEDB.
Even if we can conclude what middleware must be in use, is it generally as simple as changing a connection string and ensuring installation of the preferred middleware in upgrading to better performing middleware?
Is there a way to 'refresh' the value of adodb.connection.state? and When my ado class reconnects after a dropped connection i still get connection failure errors.
I'm trying to make the connection b/n the my work's app and mssql server more 'robust'.
Our ado class tests the connection in the 'execute' method ie if connect() execute db task
.connect() is
IF this.loConnection.State = adStateOpen *-- The connection is already open. ELSE create connection ENDIF
return (this.loConnection.State = adStateOpen)
Now, this works fine when starting up the app, and everything runs fine, but *in theory* it should be able re-establish the connection at any time. So i kill the connection @ on the sql server while the app is running and try to perform a task that requires a db connection.
problem is that 'this.loConnection.State' is still equal to 1 even though the connection is no longer there.
It gets changed to 0 when the .execute fails but the failure is less than graceful... and although it reconnects (sp_who shows a new connection), it seems like the ado object is trying to connect using the old connection rather than the new one. command.activeconnection is set to the new connection in the .connect method so i dunno what's up.
I have a client application that creates a COM object using the program ID 'SQLOLEDB enumerator' to get an ISourcesRowset. Once I retrieve the ISourcesRowset, I call ISourcesRowset.GetSourcesRowset to obtain a list of available servers.
This works if the client computer (the one running the application) also has SQL Server (or SQL Express) installed. But on a clean computer after installing SQLNCLI.MSI, the call to GetSourcesRowset returns an error 80004005.
My question is, what do I need to install on my client's machines so I can retrieve available servers using this method? Is there another MSI that needs to be installed? Do I need to install SQL Express Client_Components? Or is there something else?
Thanks in advance for any information you can provide.
I have 2 identical sql servers one of which is linked to the other via a linked server config which for one particular query gives back the error I give in the subject of this thread: java.sql.SQLException: Could not get the data of the row from the OLE DB provider 'SQLOLEDB'
if I run the exact same sql string in query analyser it runs with no problem. if have similar queries also running from java which use the linked server and these do not give an error. I am at a loss as to explain why this is happening.
the query in question joins together a number of views which reference the linked server.
I have tried everything I can think of including recreating the views and linked server definition. If all is OK in query analyser, how can it fail when called over jbdc (especially when all other db requests succeed) ?
I'm having issues with a query to a view that references a view on a linked server, that in turn references a view on a third server.
Query to view on Server A --> View on Server B --> View on Server C
I can query from A to B using the linked server. I can query from B to C. I can query from A to C.
All servers at the same location on the same domain.
I cannot query a view on A, that references a view on B, where the view on B references a view/table on C
Each linked server configuration uses "Be made using this security context", with the user specified being 'sa'. I was hoping to get security out of the equation for debug purposes, hence the sa use.
I have tried from Query Analyzer using both windows and sql auth.
MSDTC is running on all servers as local system.
The full error returned is:
Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
I am getting following Error: Oracle linked server in SQL server 2005 to update tables in Oracle database. I have used OPENQuery to update tables in Oracle from a SQL Server 2005 Stored procedure.
If we run query from sql management studio for inserting or updating table of linked server then it runs fine. But if we use Begin distributed transaction then its gives error -
Server: Msg 7391, Level 16, State 1, Procedure sp_test, Line 105 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. We are using Sql server stored procedure to run the open query syntax for updating oracle interface table.
I am using the AutoFetch Option with Fast Forward Cursors with SQLOLEDB to access SQL Server 2005. This really works out but for the first I execute the query only.
But as I use parameterized statements, when I re-use the cursor, just re-binding new variables and re-executing it again, the AutoFetch does not work any more.
I noticed that thru the SQL Server Profiler. I see a sp_cursorfetch been called at the second time the cursor is re-executed.
I've been digging for a while to try to find something, even had a consultant looking for me, but came up empty haned.
What I want to do is this:
I activated the Security Audit level for all logins. I want to be able to report last login date for every user from the log, and all login events for SA or any other defined super users.
Is it possible to Query the SQL Server Logs to report this information?
This is for proposed Sarbox Database Security settings.
I know that a heterogeneous query joining tables from two different servershas performance penalties but is the same true when joining tables from twodifferent databases on the same SQL Server 2000 instance?We are looking at setting up a Data Warehouse using DTS on a SQL Server boxand I'm wondering about the best way to logically set it up; i.e. one bighonking db or several dbs determined by some logical organization. With thelatter there will still be some queries that would need data from more thanone db and I'm wondering if that will have worse performance than if theywere all in one db.I thought that was the case in older versions of SS, but I couldn't findanything in 2000's BOL that indicated a problem with that.TIA
Hello,I'm struggling a giant fight with my webprovider (ASP.net 2.0) in the following case:I'm using the default connectionstring which automatic is provided when I create an ASP.net webstarterskit. This connectionstring is working fine when i deployed that kit to a W2003 server testenvironment and is also running on my laptop. But when I tried to deploy the website to my (new) hostingserver, also a W2003 server, I landed in ERROR-land. The last error I received was:Cannot open database "Club" requested by the login. The login failed.> Login failed for user 'NT AUTHORITYNETWORK SERVICE'.NB: I gave that user the right privileges and roles. My provider is not willing in sending me an example of a good working connectionstring and is directing me to their FAQ-site in which the right connectionstring is mentioned. That string looks like this (for a MS SQL database):strConnectie = "Provider=SQLOLEDB;" & _ "Data Source=(local)SQLEXPRESS;" & _ "Initial Catalog=[GEBRUIKERSNAAM];" & _ "User Id=[GEBRUIKERSNAAM];" & _ "Password=[WACHTWOORD]"My connectionstring in web.config looks like this:<connectionStrings><clear/><add name="ClubSiteDB" connectionString="Data Source=.SQLExpress;Integrated Security=true;AttachDBFileName=|DataDirectory|Club.mdf;User Instance=True" providerName="System.Data.SqlClient"/><remove name="LocalSqlServer"/><add name="LocalSqlServer" connectionString="Data Source=.SQLEXPRESS;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" /></connectionStrings>Is there anybody in this forum that can help me out and explain to me how to use the string, as provided by the host, in the default connectionstring i use in web.config ??Any help is welcome and appreciated.NB: I'm using VWD 2005 Express Edition and SQL Server Management Studio ExpressHarry Elzinga
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
I have a Insert statement which i am using to insert data into a remote server from my local server. I have added the remote server as a linked server in my local server but when i try to execute the statement i am getting the above mentioned error. I am using Windows Enterprise Edition 2003 and SQL Server 2000.
I have also enabled the Allow InBound and Allow OutBound in the security configuration of My Computer properties but still i am facing the same problem. Kindly let me know how to resolve this issue.
How do I specify the Catalogue while querying a linked server?
For exmaple, I have a remote SQL 2000 server as a linked server on my server. I will refer to it as "ServerRemote." I was given access to that server through my Active Directory Domain account and the catalogue 'master' was set as my default database.
Aside from having the admin change my default database, is there a way to query a linked server and specify the catalogue in the query?
Right now I use the following code to query the default catalogue for the linked server: SELECT * FROM ServerRemote...ViewName
I've tried various queryies for a different catalogue but always get a table not found error:
SELECT * FROM ServerRemote..DatabaseName.ViewName or SELECT * FROM ServerRemote.DatabaseName..ViewName or SELECT * FROM ServerRemote.DatabaseName.ViewName all return errors
When I try querying the xml document in SQL, I get nothing back, unless I remove the schema information. I'm using this:
declare @x xml select @x = P from openrowset (bulk 'E:VehicleOption0514.xml', single_blob) as Products(P) declare @hdoc int exec sp_xml_preparedocument @hdoc output, @x
I am currently migrating our Intranet from SQL Server 7 to SQL Server2000 and have hit a problem with one of the applications. Theapplication in question executes the folloiwing query against thedatabase:select * from employee where emp_id = 760In SQL Server 7 this works without a problem, however, in SQL server2k the following error is returned:[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error convertingthe varchar value 'Con25' to a column of data type int.This is clearly because the column emp_id is a varchar, I understandthat this code has not exactly followed best practices but do not wantto have to trawl through the application and surround all instanceswith apostrophes.Is it possible to make SQL 2k act in the same way as SQL 7 or will Ihave to trawl through the code and correct the SQL statements.Thanks for any help.John
Can I run queries against a SQL DB that is running on SQL Server 2005 Enterprise from the Express edition? I just need to get the info in a table. I have been told I have windows authentication to the DB, but the DB does not show up in my Express' list of networked severs.
Your help is appreciated. I have only worked with MySQL so this is all new to me.
The stored procedure executes successfully and I can also succesfully DROP the linked server. However when I try to query tables in linked databases using:
SELECT * FROM DB_INTRANET...Employees
I get the following error:
OLE DB provider "SQLNCLI" for linked server "DB_Intranet" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0 Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
The logins are Windows Authentication and even if I use sp_addlinkedsrvlogin to map logins it still gives me the same error.
I have no problems linking and querying linked Access Databases but can't do it for the SQL Server DBs.
