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
I have an ODBC connection setup for Oralce RDB. This works fine in say MSAccess. Now I would like to add a SQL Server Linked Server to this ODBC driver (it is for RDB not regular Oracle), but I am not able to get this to work. What is the Provider String and Location / Catalog for using this type of ODBC? I setup DSN as 'name', Provider String as 'UID=user;PWD=pass;' and it seems it tries to connect but gives an error. "%SQL-F-SCHNOTDEF, Schema USER is not defined"
Any help is greatly appreciated.
Hello, I've a problem with a software developed in C# with the framework 2.0. This is the error I receive : The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" returned message "No transaction is active.". If I try directly to restart the process, it works fine. Is there someone who can help me ? This is the process 1. In C# --> Call of a Query : select from the linked server (db in sql 2005) and insert into a table SQL 2005 2. In the C# --> using (TransactionScope scope = new TransactionScope()) and insert in a table in SQL 2005 which is link server Thank in advance.
View 1 Replies View RelatedDoes the x64 version of SQL Server 2005 June CTP contain 64-bit versions of the SQL Server ODBC Driver and the OLE DB Provider? I am not interested in the .NET Managed Provider here, just the legacy ODBC and OLE DB drivers, and only 64-bit versions of those.
View 3 Replies View RelatedWe have created linked server between SQL Server 2000 and Oracle 10g in offshore environment as well as onsite environment,both the environments are almost IDENTICAL.Basically, the whole execution happens on Oracle and the output is passed to SQL Server.When the below query is executed in offshore environment it is returning values perfectly.
SELECT * FROM OPENQUERY(ORACLE_LINK,'{Call TEST.IVR.lo_Dlr_GetInfo(55,{resultset 2,p_ReturnVal})}')
Here: ORACLE_LINK - is the Linked Server Name,
TEST - Oracle Schema Name
IVR - Oracle Package Name
But when the same query is executed on onsite environment it is raising the following error.
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].
The environment details are:
Offshore Environment(Successfully returning values):
1)Operating System - Windows 2000 Advanced Server
2)Service pack on Operating System - SP4
3)Version on MSDORA.dll(C:ProgramFilesCommonFilesSystemOLED B) - 2.81.1117.0
4)Version on tstbestsql1---SQL Database - SQL Server 2000 Enterprise Edition
5)ServicePack on tstbestsql1---SQL Database - 8.00.2040 (SP3a)
Onsite Environment:
1)Operating System - Windows 2000 Advanced Server
2)Service pack on Operating System - SP4
3)Version on MSDORA.dll(C:ProgramFilesCommonFilesSystemOLED B) - 2.81.1117.0
4)Version on tstbestsql1---SQL Database - SQL Server 2000 Enterprise Edition
5)ServicePack on tstbestsql1---SQL Database - 8.00.2040 (SP4)
We are able to execute if we are executing the query as
SELECT * FROM OPENQUERY(ORACLE_LINK,'select column1 from table1') on the Onshore server and it is perfectly returning the value.I believe the problem might be with "Call" if anyone can help with it that will be great.
Thanks,
Raahul
Hi all,
When i begin a distributed transaction..., do i need to always
set xact_abort on?
But when i tried to put custom error handling to roll back a transaction with a linked server , it seems its impossible (as set xact_abort on, already automatically rolls back the transaction)
Does anyone have any ideas on this?
Thanks in advance
Benny
All,
Been serahcing frantically for this provider: "Microsoft OLE DB Provider for ODBC Drivers" for Windows Server 2003 x86. However I can't seem to find it. Supposedly MDAC 2.8 has it but when I go to install MDAC 2.8, nothing happens (this is acknowledged in fact in the support area on Microsoft and the given reason is that Win2K3 Server is already MDAC 2.8-compliant. So no new stuff needs to be installed. But alas this provider isn't in my list of available drivers).
Attempts to install any part of a lesser version of MDAC that says it has it are met with "Can't do that 'cause your current version of MDAC is higher that that."
I have been searching around all day on Msoft and other sites for such a beastie but like Brigadoon vanishing in the mist, it eludes me. Msoft's main MDAC page is here:
http://msdn2.microsoft.com/en-us/data/aa937729.aspx
We need to use this kind of driver apparently with a certain Crystal Reports report or else it just won't run.
Thanks in advance for any advice/insights/DL links that work, etc...
Matt
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 - does anyone have insight? 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.".
Hi all,
I am using MSSQL2005 and created a linked server with Ole DB provider for ORacle to connect to Oracle.
sp_tables_ex linked_server
It shows the tables in oracle, however, it show error with selecting the oracle tables via the linked server,
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA'
reported an error.
[OLE/DB provider returned message: Unspecified error] [OLE/DB provider returned message: ORA-03113: end-of-file on communication channel ] OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBSchemaRowset::GetRowset
returned 0x80004005: ].
Any Idea? Thanks a advance.
Hello all,
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..?
Thanks
-- Krishna
System: Win 2003, SQL Server 2005, Using an AD win account that is not a member of the Admin group on the server.
Error message from Management Studio query window:
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "IBMDADB2" for linked server "Sname".
Event messages associated with this error:
App Event ID: 19036
The OLE DB initialization service failed to load. Reinstall Microsoft Data Access Components. If the problem persists, contact product support for the OLEDB provider.
Sys Event ID: 10016
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {2206CDB0-19C1-11D1-89E0-00C04FD7A829}
to the user domainuser SID (S-1-5-21-126051702-1034962659-2130403006-7826). This security permission can be modified using the Component Services administrative tool.
I€™m getting this error message when trying to run an openquery statement through a linked server to DB2. (SELECT * FROM OPENQUERY(Sname, 'SELECT * FROM tablename€™))
The linked server has a remote login and password that it uses to connect to DB2.
I found this from another post on how to fix this error:
Expand Component Services - Computers - My Computer - DCOM Config Select MSDAINITIALIZE Right Click properties then security
Under Security - Launch Permission: enable Local Launch and Local Activation for your SQL Service account
Under Security - Access permissions: Allow System: Local Access and Remote Access.
After completing these steps I still get the error message but the events are no longer generated. I€™ve also tried different variations of these steps. The only way I can get this to work is to either make the AD user a member of the Admin group on the server or by putting in a user account that has admin rights to the server in the MSDAINITIALIZE properties €“ Identity Tab €“ Run this App as this User.
Can someone please tell me the steps that I€™m missing?
We have a SQL2005 server install running on a Win2003 Server. It has a linked server to iHistorian using the iHOLEDB provider for iHistorian. Randomly and after a few execution to this provider, we start getting errors
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "IhOLEDB.iHistorian" for linked server "fspheafhs1".I created a data source in VS2005 for BI on the SQL server using the iholedb provider. This was successful. However, all queries or OPENQUERY statements to the linked server fail. This seems to continue until SQL Server is restarted. Does anyone know how to reset a provider in SQL Server or tell if the SQL Server is having trouble with the provider? There are no message in the SQL Error Log. Again the provider continues to work outside of SQL Server, but no longer can be accessed from within SQL Server. Thanks in advance.
Hi
i am try to execute a command " SELECT * FROM [SQLSERVER].[DATABASENAME].[DBO].[TABLE] ".. but i am receiving a error message
Msg 7319, Level 16, State 1, Line 19
The OLE DB provider "SQLNCLI" for linked server "SQLSERVER" returned a "NON-CLUSTERED and NOT INTEGRATED" index "MSmerge_index_1348915877" with the incorrect bookmark ordinal 0.
but when i try to check the linked server it's shows me the linked is okay.. The following command i use to check wethare the link is fine or not..
CREATE TABLE #foo
(
pingResult SYSNAME NULL
);
INSERT #foo
EXEC master..xp_cmdshell
'ping SQLSERVER;
IF EXISTS
(
SELECT 1
FROM #foo
WHERE pingResult LIKE '%TTL%'
)
BEGIN
PRINT 'Feel free to use linked server.';
END
ELSE
BEGIN
PRINT 'Linked server not available.';
END
DROP TABLE #foo;
and Following result i receive..
(13 row(s) affected)
Feel free to use linked server.
Can any One tells me where i m making mistake or how can i resolve this problem
Hallo
i'm trying to connect a VFP DBC as a linked server on MSSQL2000sp3a, using following parameters:
Product name: VFP
Data source: \networkpath odbc
Provider: VFPOLEDB.1
No security context
I'm trying the same operation both from a W2000sp4 server (+ MDAC 2.8sp1 ?) and a WXPPsp2, against two identical copies of the DBC that resides on two different path (one on the final path and one on a test path).
From WXPPsp2 everything runs ok, from W2000 i'm always getting this error:
Error 7399: OLE/DB Provider 'VFPOLEDB' IUnknown::QueryINterface returned 0x80070005: Access Denied
(already reported in this thread, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1832362&SiteID=1 but it didn't helped me)
The error happens independently from the user the query runs under (basicly AD account, both domain admin and normal user).
MSSQL runs under a domain account which have full permissions (for test purpose) on the directories where DBC reside.
I did no further modifications on the WXP machine in order to have the linked server running, simply it ran smoothly from the beginning.
Clearly the difference rely on the different OS... but what else?
i ran component checker to determine the MDAC version but some components version numbers slightly differs from the expected ones (eg. resulting version is 2.0.1064 versus 2.0.1022 expected in MDAC 2.8) so i suppose to have MDAC2.8 sp1 on the server.
thanks in advance
Hello, everyone!
I'm currently having an issue with a linked server. Here's the surrounding information:
A) I have a clustered SQL Server 2005 Instance (A) and a SQL Server 2000 instance (B).
B) There is a linked server on A to B. When I set it up, I did run the fix to ensure A could talk to B (There was an issue with communication between 2005 and 2000 servers). It has been there since I installed A, and has worked fine.. Until last week.
C) This linked server uses static credentials to connect to B.
D) Named Pipes are enabled on both servers to listen to both connections on both A and B in cliconfg. So A has a named pipe listening for B, and B has a named pipe listening for A.
E) A has a view that looks at a table on B - It's a table view, very simple, just pulls in all the data from the table on B.
So, I go to do a select statement from the view, connected as a user other then 'sa'. I then get the error:
Named Pipes Provider: Could not open a connection to SQL Server Linked Server
Error Source: ncli Client (Paraphrasing, didn't copy that down, but it was the ODBC connector)
Well, that's odd. It's been working fine for months now..
I then go and connect as sa on A to query B. It works! Mind you, absolutely NO QUERY from A to B will run again until I run SOME kind of query has been run as sa on A.
Mind you, this is not a credential issue. Every user who connects to the linked server to B uses a stored credential that is DBO to the database on B. The same User Name and Password exists on both A and B.
Also of note, I check the activity monitor. There's a process that is "dormant" every time I run a query against the view. The details of this connection are:
sp_reset_connection;1
My question is, why is it resetting the connection on B when being queried from A? Why is it "all of a sudden" a problem? Are there any changes that coudl ahve been made that would cause this?
Any help with this confusing issue would be appreciated.
Thanks!
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
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
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.".
Ok here is a run down of the situation:
I'm running Server 'A' and Server 'B' which are both on SQL 2005 SP2. Server B connects to Server A using the linked server functionality via the SQLNCLI provider. I am issuing an update statement from a web api in a nested transaction that uses a distrubted transaction.
However, I am receiving the following error :
Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "A". A nested transaction was required because the XACT_ABORT option was set to OFF.
OLE DB provider "SQLNCLI" for linked server "A" returned message "Cannot start more transactions on this session.".
I've researched the issue and understand how XACT_ABORT works.
Also, I looked at the Linked Server provider options for SQLNCLI and came accross the Nested Queries option being unchecked. I checked the option and applied it to the Linked Server.
Okay, so after my long post my questions are: Do i need to restart SQL in order for this to take effect? If not, what do i need to do? I 've restarted IIS to no avail .
I keep recieving a "Invalid use of schema or catalog for OLE DB provider "MSDAORA" for linked server D"CUSTOMERLINK". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema."
I'm attempting to set up a linked server from SQL 2005 to Oracle 10.2. I run the following sp_addlinkedserver and sp_addlinkedsrvlogin procedures:
sp_addlinkedserver
@server = 'CUSTOMERLINK',
@provider = 'MSDAORA',
@srvproduct = 'ORACLE',
@datasrc = 'CUSTOMER'
GO
sp_addlinkedsrvlogin
@rmtsrvname = 'CUSTOMERLINK',
@useself = 'False',
@rmtuser = 'CUSTOMER1',
@rmtpassword = 'PASSWORD!'
These procedures complete successfully. I then run a sp_tables_ex procedure:
sp_tables_ex @table_server=CUSTOMERLINK, @table_schema='CUSTOMER1'
This procedure completes successfully and gives me all of the table names in my oracle database that relate to the Customer1 schema.
Then when I go to run a query
select id from [CUSTOMERLINK].[CUSTOMERDB].[CUSTOMER1].[CLIENT] Where name = 'codm'
I recieve a "Invalid use of schema or catalog for OLE DB provider "MSDAORA" for linked server D"CUSTOMERLINK". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema." error.
Any suggestions?
Hi,
I'm working on an OLE DB provider. This provider is supposed to retrieve data from managed application running in background. The data retrieval is handled by C# component communicating with source application via remoting, the data are then exposed via component's COM interface. OLE DB provider itself instantiates the communication component.
Now, the problem: this worked ok in SQL server 2000, but it doesn't seem to work in SQL server express edition. Concretely, the communication component fails to get created (CoCreateInstance ends with 'class not registered') and SQL server logs this:
2007-08-24 14:48:49.42 spid51 Error: 6511, Severity: 16, State: 20.
2007-08-24 14:48:49.42 spid51 Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131022. You may fix the problem and try again later.
I've googled a bit and found out someone else getting this message, and he was told something like 'trying to load CLR in some cases is disabled in newer (>2000) versions of SQL server'.
Now, I have very little knowledge of SQL server, CLR integration etc., so I'm not sure whether this applies to my case as well, but I think so - that problematic COM object is C#-based.
I'd be glad for any hints, ideas how to solve this issue, I just need the OLE DB provider to be able to access that managed application.
Thanks,L.
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'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
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
I'm trying to define a linked server using Microsoft OLE DB Provider for Data Mining Services. MSDMine data provider is missing. Do you know where I can get this? Thanks in advance.
I'm have a Xeon 64bit proc. using SP2. And had installed:
SQLServer2005_ADOMD_x64.msi, SLQServer2005_ASOLEDB9_x64.msi,SQLServer2005_OLAPDM_x64.msi, and SQLServer2005_XMO_x64.msi.
I find this most perplexing.
I have two servers, DEV and PROD. Now my DEV server works just great, I can connect to the linked server, query, etc... all is well.
So I'm setting up my PROD server and when I go to add the linked server I get:
Cannot initialize the data source object of OLE DB provider "SQLNCLI".... and Unable to complete login process due to delay in opening server connection.
Now I am running SQL Server 2005 and connecting to an SQL 2000 server.
The odd part is that this works just fine on DEV.
When I go to create the linked server I set:
Linked Server: "LinkedServerName"
Server Type: "SQL Server"
and that's it.
I go to Security and enter my DOMAINUSER.ACCOUNT and then enter the login creds for the linked server.
When I click "OK" I get the above mentioned error code.
Any thoughts?
Hi,
I have been trying to export data from a Microsoft Excel 2007, Tester.xlsx to a SQL Server 2005 table as :
SELECT * INTO CUSTMAS FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:Tester.xls;Extended Properties=Excel 8.0')...[Providers$]
I have an error:
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server"(null)"
I wanna to create a linked server which connect to oracle. Â Here are my steps:
1.Install Oracel drivers Oracle Data Access Components.
2.Configure the Oracle Ole DB provider to run inside the SQL Server process and configure it to accept parameters. exec
master.dbo.sp_MSset_oledb_prop
'ORAOLEDB.Oracle',
N'AllowInProcess', 1
exec master.dbo.sp_MSset_oledb_prop
'ORAOLEDB.Oracle',
N'DynamicParameters', 1Â
3.Create linked server with scripts:
exec sp_addlinkedserver N'MyOracle1', 'Oracle', 'ORAOLEDB.Oracle', N'//10.154.14.235', N'FetchSize=2000', ''
exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'root', @rmtpassword='Sqlexp!23' Â Â
4. After create successful and testing the connection ,I got the error below.(My windows firewall is turn off)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle".OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle" returned message "ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA". (Microsoft SQL Server, Error: 7303)
I just installed a SQL Server 2005 Express SP2 instance on a server with an existing SQL Server 2000 SP3 installation. (I need SQL Server 2005's INSERT from an EXEC capability). It's working great now except for one thing: I can't link any other SQL servers! I've already successfully added and queried a linked Oracle server, but attempting to add a linked SQL server gives me the following error, no matter which SQL provider I try to use:
Code Snippet
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
Additional information:
--> An exception occured while executing a Transact-SQL statement or batch (Microsoft.SqlServer.Express.ConnectionInfo)
--> Cannot create an instance of OLE DB provider "SQLNCLI" for linked server "SERVERNAME". (Microsoft SQL Server, Error: 7302)
The technical details reveal the error source as "sp_testlinkedserver".
I've scoured the net and premier support for advice on this problem, but the little I found wasn't helpful. I've tried reinstalling the SQL Native Client, but it didn't help. I've tried uninstalling SQL Server 2005 Express completely (including management studio and native client), rebooting, and reinstalling everything, with no luck. The server (obviously) does not have a firewall enabled. I've tried stopping the SQL 2000 Server installed on the system, no help. If I create a test .UDL file on the system, pointing to any of the SQL servers I'm trying to link, clicking the "Test Connection" button returns successful. I'm also able to connect to and query the servers directly from the SQL 2005 Express Management Studio. I just can't add them as linked servers.
The server is running Windows 2003 SP1, SQL Server 2000 SP3, and SQL Server 2005 Express SP2 (the default "SQLExpress" named instance). I've tried setting up SQL 2005 Express to run under the network service account and under a domain account used by the other SQL 2000 servers.
From other SQL 2000 servers, I can connect and link to the SQL Server 2005 Express instance successfully. I can also successfully add linked SQL servers in SQL Server 2000 instance installed on the same server. Only adding linked SQL servers in 2005 Express seems to be broken.
Anyone have any other suggestions? I'm totally baffled. Thank you so much for any helpful advice.
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
View 1 Replies View Related
Hi All,
I have a strange problem with one of my Linked server connections. I am connecting from
SQL Server 2005 Standard Edition (9.00.3042.00) to and Oracle 10g database using the Microsoft OLE DB Provider for Oracle. The connection works and I am able to select from all the Oracle tables except for 1 table. When selecting from this table I get the following error:
OLE DB provider "MSDAORA" for linked server "DBNAME" returned message "ORA-01476: divisor is equal to zero".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "DBNAME".
This probem happens if the table has rows or has no rows (empty table).
I have tried SELECT * and count(*) along with selecting a specific column from the table.
I can use SQL*plus from the system that the SQL database server running on it and select from the table using the same login/password that the linked server uses to rule out a permissions problem.
Any suggestions on what I can try next?
Thanks.
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.