Linked Server Error From SQL 2005 X64 To X32
Nov 13, 2007
I have a problem I have spent hours on already. I have found multiple posts, but not a solution to this problem. This is not a rights issue.
I have two SQL 2005 servers (I cannot upgrade to SP2 yet):
ServerA - ver 9.00.2153.00 (X64)
ServerB - ver 9.00.2153.00 (Intel X86)
I create a linked server on ServerB pointed to ServerA, uses 'sa' for all logins, works perfectly.
I create a linked server on ServerA pointed to ServerB, uses 'sa' for all logins, FAILS.
Run on ServerA:
SELECT *
FROM OPENQUERY([ServerB], 'SELECT [field1] FROM database.dbo.tablea')
Returns:
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "ServerB" supplied inconsistent metadata for a column. The column "field1" (compile-time ordinal 1) of object "SELECT [field1] FROM database.dbo.tablea" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.
Run on ServerA:
SELECT [field1] FROM [ServerB].database.dbo.tablea
Returns:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "ServerB" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "ServerB". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema
Any bright ideas?
View 3 Replies
ADVERTISEMENT
Aug 30, 2006
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.
View 4 Replies
View Related
Jan 8, 2008
Hi everyone,
I'v finally managed to get a New Linked Server setup on SQL 2005 Express SP2 using the 'Microsoft OLE DB Provider for ODBC Drivers' MSDASQL. I have a Database setup on the SQL 2005 Express Database Engine which contains a query that OPENQUERY's the linked server. The query executes OK.
The problems in when I link an Access 2003 SP2 .adp file to the SQL 2005 Database Engine database. All objects, including the query containing the OPENQUERY (to the linked server) appear. However, when I run the same query within Access I get the following error:
"The OLE DB Provider for Linked Server "name" reported an error."
"The Provider reported an unexpected catastropic failure"
So the query works running within SQL 2005 Management Studio, but the same query doesn't work running within an Access 2003 SP2 ADP. I've closed down SQL 2005 Management Studio, but the same error is displayed.
I didn't know if the problem lies in Access 2003 SP2 or SQL 2005 Express SP2.
Any takers?? (I haven't logged this Thread in an Access forum)
Any assistance is appreciated.
Alan
View 1 Replies
View Related
Mar 14, 2007
Hello all.
I upgraded a SQL 2000 installation to SQL 2005 this past weekend. Minimal issues overall, but one that did crop up relates to a linked server to an Oracle database. The linked server worked before the upgrade, and stopped working immediately after.
We're running on Windows 2003 x64, latest service pack. The linked server is set up using the MSDAORA provider. The error I'm getting post-upgrade is:
=====================
OLE DB provider "MSDAORA" for linked server "PROD" returned message "ORA-06413: Connection not open.".
Msg 7303, Level 16, State 1, Procedure RDM_GET_REP_LIST_SP, Line 12
Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "PROD".
=====================
A tnsping from the server works fine. I've also tried recreating the linked server. Any help would be appreciated. Let me know what other information would be useful.
Thanks,
Adam
View 3 Replies
View Related
May 27, 2008
Hello,
I have a development and a production SQL server instance environment set up on 2 independent machines. Each machine is running Windows 2003 for an OS, while each server instance is version SQL Server 2005. On friday, I experienced difficulties querying one environment from the other through linked servers. I would get the error below:
.
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "dev_server". The provider supports the interface, but returns a failure code when it is used
The linked servers had been previously set up and had been running without any issues. Dropping and recreating the linked servers did not help at all, and all attempts to google the error led to accounts of either SQL Server 2005-SQL Server 2000 procedures compatibility or 64 bit - 32 bit compatibily related errors. Neither of the two were relevant as both my environment have the same technology, both hardware and software.
Mysteriously, the linked server worked this morning without any issue at all. One co-worker suggests gremlins are at work, while another figures that my set up had 'checked out for the long weekend'. Unfortunately, neither explanation is plausible, so my quest to find out what could have gone wrong, and hopefully put preventitive measures in place for the future goes on. Does anybody have any idea what the issue could have been?
Thanks,
Simba
View 1 Replies
View Related
Jul 27, 2006
We use Windows 2003 Server (64) on AMD64 and SQL Server 2005 Developer Edition x64 + SP1
Problem:
I can not execute any sql on a linked server using the native Provider (SQLCLNI)
Example (create a linked server on the same machine, other database (msdb) and try to execute any simple select using OPENQUERY
sp_addlinkedserver @server='ls',@srvproduct='SQLNCLI',@provider='SQLNCLI'
,@provstr='Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=msdb;Data Source=localhost;'
Select * from OPENQUERY(ls,'Select * from Sysobjects')
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "ls" supplied inconsistent metadata for a column. The column "name" (compile-time ordinal 1) of object "Select * from Sysobjects" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.
View 11 Replies
View Related
Jan 15, 2008
I'm trying to set up a linked table from SQL Server 2005 to MS Access. The .mdb file resides on a network share and I'm unable to connect to it. If i copy the database to a folder on the server that SQL is installed, it works fine. Any help would be greatly appreciated. Below is the error message that I'm receiving.
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LINKSERVER".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LINKSERVER" returned message "The Microsoft Jet database engine cannot open the file '\serversharedatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.". (Microsoft SQL Server, Error: 7303)
View 3 Replies
View Related
May 5, 2008
Hi Guys:
Our company wanted to try out SQL Server 2005 Enterprise Edition (64 Bit). So, we were on free trial of the Enterprise Edition for past 5 months. After which we decided to go for SQL SERVER 2005 Standard Edition (64 Bit). And, Last week, we installed the SQL Server Standard Edition (64 Bit) on our server. After installation, everything was restored as before.
The version we are on right now is:
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Strangely, a job which ran fine till then is failing with the following error: And, to be more specific, when this job is run manually in the form of a stored procedure using the query optimizer it runs fine. But, when its executed as a scheduled job on SQL Agent, it fails. The History logs record the following error.
Error :
The OLE DB provider "SQLNCLI" for linked server "LV-SQL2" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "LV-SQL2". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI" for linked server "LV-SQL2" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412).
I tried everything possible, even recreated the job, but, no avail.
I also considered the possibility if SQL Agent login account did not have enough permissions. So, I changed the SQL Agent login to windows authentication, but the job still fails. So, its the problem has nothing to do with login accounts either.
For the record, I have cross checked all these too.
1. Enabled the remote connections in Surface Area Config
2. Added local server login to remote server login mappings.
3. Checked 'Rpc' and 'Rpc Out' under server options. Also, the Connection Timeout and Query Timeout have been set to zero (0).
Any suggestions pointing towards problem solution appreciated.
Thank you.
View 4 Replies
View Related
Mar 2, 2007
1. Replication Transactional between two servers
-- SQL Server 2005 as Distributors and Subscribers
---SQL Server 2000 as Publishers
2. Linked Servers errors:
" Server (Publication Server) is not configured for DATA ACCESS. (Microsoft SQL Server, Error: 7411)"
Did anyone familiar with this problem?
Thankssss
TJ_1
View 4 Replies
View Related
Nov 13, 2006
I've experienced the problem with creating a linked server from SQL 2005 64-bit to SQL 2000.
"The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
I have resolved this by upgrading the system stored procs on the 2000 machine by running instcat.sql, documented in http://support.microsoft.com/default.aspx/kb/906954/en-us . Works great.
However, I'm now experiencing the same error when linking from SQL 2005 64-bit to SQL 7. I'm concerned about applying 2000 code onto a SQL 7 machine. Any ideas on how to handle this case?
Thanks
Paul
View 1 Replies
View Related
Apr 20, 2006
I have a linked server to Sybase using MSDASQL. I can query the tables from the linked server using SQL Management Studio (windows login). When I put the same query into a job, it failed to work. The query is simple:
SELECT * from MyLinkedSvr.RemotDB.dbo.RemoteTable
The linked server setting include the login mapping, which maps my windows login and sa to the remote sybase login.
I'm using SQL 2005 9.0.1339 on Windows2000 Pro w/ SP4. SQL Agent starts as local system in the Services panel. Shared Memory/TCPIP/Named Pipes are enabled for Server/Client Network.
Thanks!
View 3 Replies
View Related
Mar 8, 2001
could someone please explain the following error msg! the code seg that is gen the error is as follows, followed by the err msg. I"m trying to update a table on a linked server (paeddb1.gold). both servers are running MS SQL7
update paeddb1.gold.dbo.controls_peg
set amt = t.amt
from #temp_peg_control t, paeddb1.gold.dbo.controls_peg p
where t.peg = p.peg
and t.cntl_type = p.cntl_type
and t.fy = p.fy
Could not open table '"gold"."dbo"."controls_peg"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Errors occurred]
View 2 Replies
View Related
Sep 19, 2005
I have a "linked server" configured in my SQL Server 2000 (SP4) server, which used to work correctly. However, I had to reinstall SQL Server (I backed up and restored the master/model/etc databases, so all my settings stayed the same). Since then, I've been getting this error when I try to use the linked server:
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].
The linked server is a FoxPro database, which does not use catalog or schema names. So, my select syntax looks like this:
SELECT * FROM Server...Table
SQL Server is aparently expecting something like this:
SELECT * FROM Server.Catalog.Schema.Table
Does anyone know how I can fix it so that it allows the "empty dot" method to work like it used to?'
Thanks!
Josh
View 9 Replies
View Related
Apr 10, 2008
Hi Pals,
We have an SSIS package within which we are calling a stored procedure which eventually call a sql server dbo.fn() which contains code to lookup data inside oracle using Linked Server for Oracle.
We are calling the Package dynamically from the stored procedure by creating a SQL Server Agent Job, I am getting the below error very often. Can we fix the error in any way?
“EXEC sp_UpdateTname 369,'939390',2008 " failed with the following error: "Cannot initialize the data source object of OLE DB provider "MSDAORA" for linked server "ORATEST".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
When I hard code global variables and ran the package I am able to see the successful execution of the package.
Can anybody point out where could the error lies?
View 1 Replies
View Related
Feb 20, 2008
I got the following error when using linked server:
OLE DB provider "SQLNCLI" for linked server "SACPANRPT" returned message "Cannot start more transactions on this session.".
Msg 7395, Level 16, State 2, Line 1
Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "SACPANRPT". A nested transaction was required because the XACT_ABORT option was set to OFF.
View 1 Replies
View Related
Jul 20, 2005
Hiwhen i try to run a query using linked servers, i get the followingerror.Server: Msg 125, Level 15, State 1, Line 1Case expressions may only be nested to level 10.I do have more than 10 case statements, it works fine when it is lessthan 10. can anyone tell me if there is a way to have more than 10case statements. thanks alot.Jaymy querySelect category, val, Sum(QTY) As QTY , yrFrom(Select val, QTY2 As QTY,KEEP = CaseWhen code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11))Then 'N'When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12))Then 'N'Else 'Y' EndFromamf a Join linkedserver.source.dbo.table2 b On a.COM = b.COMWhere CATEGORY In ('1') And CODE In ('001','003','004','005')And b.YR Between 2003 And 2004 And b.MON <= 1) xWhere KEEP = 'Y'Group By CATEGORY, YR
View 2 Replies
View Related
Nov 23, 2007
HI
I have win 2003 64 sp2 SQL 2005 sp2. I downloaded an informix 64bit driver 3.00FC then set system dsn which works fine when i apply and test the connection.
I tried to create a new linked server using Microsoft OLE simple provider against the odbc when I get this error
Does anyone have any suggestions??
Many thanks
Robert
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSDAOSP" for linked server "CERP". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3186&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
View 28 Replies
View Related
Oct 30, 2007
Hello,
SQL Server version is Enterprise Edition 8.00.2039 SP4
and one oracle database linked via Microsoft OLe DB provider for Oracle. Oracle db name: SCP.
One of the job inserts data to sql database from querying oracle database; randomly fails with the error message below.
**************************
INSERT INTO mom_services ('SQL TABLE')
SELECT *
FROM OPENQUERY(SCP,'SELECT SERIAL_NO_ FROM SCADMIN.DEVICEM1 WHERE CONTAINER=''MOM''')
***************************
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
What I have done;
Oracle client uninstalled server restarted and oracle client installed .
Linked server deleted and recreated but nothing changed.
Any ideas ?
Osman
View 5 Replies
View Related
Jan 21, 2006
<<cross posted from microsoft.public.sqlserver.server>>Environment: Windows 2003 Standard Edition SP1, SQL 2005 EE RTM (bothservers)I am getting the following messages when I run this query:select * from LINKEDSERVER.databasename.dbo.tablenameOLE DB provider "SQLNCLI" for linked server "LINKEDSERVER" returnedmessage "An error has occurred while establishing a connection to theserver. When connecting to SQL Server 2005, this failure may be causedby the fact that under the default settings SQL Server does not allowremote connections.".Msg 782, Level 16, State 1, Line 0SQL Network Interfaces: No credentials are available in the securitypackageOLE DB provider "SQLNCLI" for linked server "LINKEDSERVER" returnedmessage "Client unable to establish connection".Msg 782, Level 16, State 1, Line 0SQL Network Interfaces: No credentials are available in the securitypackageLINKEDSERVER is set up as a SQL Server linked server., with no loginmapping, and connections will be made using the login's currentsecurity context.Both servers allow local and remote connections via TCP/IP, and NamedPipes is disabled on both servers. Both servers are trusted fordelegation, and the service accounts for the MSSQLSERVER service onboth servers are also trusted for delegation.We are auditing successful and failed logins, and are not seeing anyactivity on the linked server.Any help would be appreciated.Randy
View 3 Replies
View Related
Nov 16, 2007
I have a Win 2003 running SQL 2005 Workgroup on a hosted site. I have full access to all servers. I am trying to create a linked server to my old SQL 7 server on a Win 2000 server in another network. I can ping the Win 2000 server from the Win 2003 server. I can connect via VPN from the Win 2003 server to the other network firewall and create a linked server to an AS400 server at 192.168.1.50. On the internal network behind the firewall the Win 2000 server is at 192.168.2.133 and natted to 10.10.50.133 via a load balancer. I cannot ping the Win 2000 server over the VPN even if I add an entry to the hosts file.
The SQL 7 server is autostarted with agent and MSDTC autostarted under SQL/NT authority under the administrator account. I allow remote servers to connect via RPC.
I know just enough to be dangerous and have researched this to death. I need a little help with this. I keep getting "could not open a connection to SQL Server [53]" and have researched that to no end. On the SQL 2005 management studio to create a linked server I use Microsoft OLE DB provider for SQL Server and SQLNCLI. I can't determine the connection string so I leave that blank.
View 9 Replies
View Related
Sep 17, 2007
I am trying to set up the Linked 2005 Server to Teradata v2r5, and have got most of the way there. My problem is that using both methods (OPENQUERY and SELECT with 4-part naming string) seems to submit only the SELECT * part to Teradata with the rest of the SQL waiting until the data is all pulled back to SQL Server. This is a problem when working with big tables! It puts a huge strain on the network, and also runs me out of temp space in Teradata. Is there a way to get ALL the SQL passed to Teradata? I am looking to create a Report Model for Report Builder or Pro-Clarity. I have a dummy SQL Server 2005 database set up with views of the Teradata tables through the Linked Server. Any help would be greatly appreciated!
View 1 Replies
View Related
May 7, 2007
I have database in 2000 which needs to be access from SQL 2005 via linked server ? and vice - versa ?
How do we do that ?
we run select statement we get following error message.
Error 4064 : cannot open user defulat database : login failed.
We are using SQL 2000/sp4
SQL 2005/sp2
Please advice.
Thank you
View 1 Replies
View Related
Mar 24, 2008
Hi,
I've been able to make a linked server to an access document as follows and it works perfectly:
EXECUTE sp_addlinkedserver 'North', 'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0','C:Practice FilesNorthwind.mdb'
SELECT * FROM North...Customers
I have 2x sql 2005 servers at home at different machines. I'm logging on both machines by using windows authentication. How can I make a linked server between them?
Thank you.
View 3 Replies
View Related
Jul 26, 2006
With Linked server you can access DB2 resources as if it is another object in your SQL Server.
You don€™t have to establish links from all nodes to the DB2 Server.
Connection from SQL server 2005 to DB2 Server is sufficient to all SQL server users to access DB2 resources.
1. Install MicroSoft OLE DB provider For DB2 in SQL server 2005.
2. configure as follows:
Start - - -> all programs - - - > MicroSoft OLE DB provider For DB2 - - - > Data Access Tool.
Right click on data sources - - -> New Data Source
In Data Source Wizard - - > Data Source plat form = DB2/MVS or any other appropriate plat form from drop down
Net work type = TCP/IP - - > Next
Address = IP address:xx.xx.xx.xx
Port Number: xxxxxxx - - -> Next
Initial Catelogue = SAMPLE
Package Collection = DB2INST1
Default schema = DB2INST1
Default Qualifier = Leave it blank - - -> NEXT
HOST CCSID = EBCDIC- U.S./Canada[37] or appropriate one
P.C Code Page = ANSI-Latin I[1252] or appropriate one
Process Binary as character = check the box - - -> NEXT
Interactive sign on - - - > select it
Provide login name and password.
Check "Allow Password saving" - - - -> NEXT
Check appropriate boxes like read only etc. - - -> NEXT
Click Connect tab - - -> make sure "Success ful" message is appearing
Example: - "Successfully connected to data source 'DB2-server'.
Server class: DB2
Server version: 8021.00.0000"
- - - -> NEXT
Provide a data source name.(Example: DB2-Server)
Check initialization string option - - - > NEXT
Review completed task list on the window and
Select "FINISH" to complete the work.
The data source which you have added just now(DB2-Server) will be appearing on the grid now.
right click on the data source name and make a note of the path where it has formed
right click on the data source name and click "Display connection string"
cut and paste this connection string on the lower window to note pad for future use.
Start MS SQL server Management studio
Add a fresh linked server in 'SQL Server - - > Server Objects - - -> Linked Server" as follows:
Click Mouse right button on linked server and select "New Linked Server"
linked server = Provide a meaning ful, fully identifiable name for Linked server(Example"DB2-Linux")
Provider = MicroSoft OLE DB provider For DB2
Product name = DB2OLEDB
Data source = IP address
Provider string = Paste the connection string pasted to note pad earlier.
Location = Leave it blank
Catalog = SAMPLE
Click on security on the left side pane
Provide an SQL Server login from the drop down
Do not select check impersonate
Provide DB2 username and password and remote user name and password.
Click OK to save.
You will find the name of liked server appearing below the object Linked Server(Example "DB2-LINUX")
Open a new query window and execute an open query
Example:
SELECT * FROM OPENQUERY("DB2-LINUX", 'SELECT * FROM SAMPLE.DB2INST1.EMPLOYEE')
In the Example "DB2-LINUX" is the name of the linked server
"SAMPLE.DB2INST1.EMPLOYEE" is the name of the table. Sample = name of the db
DB2INST1= name of schema to which the table belongs to
Employee = name of the table
Another Example would be
SELECT empno,FIRSTNME FROM OPENQUERY("DB2-LINUX", 'SELECT * FROM SAMPLE.DB2INST1.EMPLOYEE')
View 1 Replies
View Related
Jan 15, 2007
I'm having issue in connecting to sql 2005 from sql 2000 via linked server.
Please advice what I should do to overcome the problem.
Thanks.
-EC
View 4 Replies
View Related
May 4, 2007
Hi
I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.
I am getting the following error while accessing the linked server in management studio based on the scenario given below ;
------ Error Message Starts
OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" 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 18456, Level 14, State 1, Line 0
Login failed for user 'abc'.
------ Error Message Ends
Consider login name is abc.
Now this login abc has sysadmin rights on sql server 2005.
The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.
I have configured the linked server using the following options;
1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 .
2. I have also tried specifying remote login / password option.
Anyone having any idea, would be of great help.
Regards,
Salman Shehbaz.
View 3 Replies
View Related
Apr 18, 2007
Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.
I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??
View 1 Replies
View Related
Dec 28, 2005
I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005. The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67. I have already installed the Sybase client software on the server.
I also created a SystemDSN on the SQL Server to connect to the Sybase server. I tested the connection and it was able to connect.
I ran the following code to create the linked server:
<code>
EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'Sybase System DSN', @provstr=N'"Provider=Sybase.ASEOLEDBProvider;Server Name=servername,5000;Initial Catalog=databasename;User Id=username;Password=password"'
</code>
I then ran sp_tables_ex to make sure I could view the tables in the Sybase database. Here is the error message I get:
<code>
OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".
</code>
Any ideas what is happening here?
View 10 Replies
View Related
Oct 17, 1999
I'm trying to execute commands on a SQL Server on the same domain. Following the instructions, I called sp_addlinkedserver N'LinkSQLSrvr', ' ', N'SQLOLEDB', N'NetSQLSrvr' then sp_addlinkedsrvlogin N'LinkSQLSrvr', false, N'Joe', N'Visitor', N'VisitorPwd'. No complaints. When I execute a select statement, however (eg. select top 10 colname from LinkSQLSrvr.dbname.dbo.tblname) I get:
Server: Msg 7353, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
Tried it on different combinations of local and remote servers and I get exactly the same error each time. Help!
View 3 Replies
View Related
Feb 10, 2003
SQL 2000
I have a process that calls several stored procs which access a database on a linked server.
code that fails:
SELECT DISTINCT em.er_id, em.er_name, bp.bpo_id, bp.bpo_name
FROM [dbrptc13dayoldprod].ues.dbo.Employer em
Inner Join [dbrptc13dayoldprod].ues.dbo.BPO bp ON
em.er_bpoid = bp.bpo_id
Error message:
Server: Msg 913, Level 16, State 8, Line 1 Could not find database ID 6. Database may not be activated yet or may be in transition.
The database is accessible from query analyzer with a simple select from the linked server. Also if I change any letter in the ues.dbo.Employer em or ues.dbo.BPO bp part to a different case it works fine.
For example: -changed the BPO to BPo- this works!
SELECT DISTINCT em.er_id, em.er_name, bp.bpo_id, bp.bpo_name
FROM [dbrptc13dayoldprod].ues.dbo.Employer em
Inner Join [dbrptc13dayoldprod].ues.dbo.BPo bp ON
em.er_bpoid = bp.bpo_id
Please help I can't figure this one out.
Thanks.
View 1 Replies
View Related
May 19, 2004
Server: Msg 7356, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'customerID' (compile-time ordinal 1) of object '"ABCDB"."dbo"."vwCustomer_xxxxk"' was reported to have a DBCOLUMNFLAGS_ISNULLABLE of 0 at compile time and 32 at run time].
above error messages occurs when I try to run a proc which inside a query statement of select to a linked SQL6.5 database. The thing puzzled me is that
linked query to run outside the proc is just fine, but errors within the proc. the running server is MSDE 2k with sp3. Look at knowledgebase about "
DBCOLUMNFLAGS_ISNULLABLE" is only related to DB2 or SQL7 or SQL2000 before sp3.... man...
thanks
View 3 Replies
View Related
Apr 30, 2002
I'm running SQL Server 2000 and I have a linked server setup to another SQL Server 2000 box. I've got a stored procedure that creates a temporary table and is pulling information from local tables and from tables located on the linked server. When I run this stored procedure it bombs on me when it hits the linked server part returning an error message of:
Server: Msg 7391, Level 16, State 1, Procedure app_GetOfficeOrders, Line 29
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.
Now I can run a query against a table located on the linked server that returns just a table count and everything works fine. I'm just having problems with that one stored procedure. Any suggestions would be appreciated.
P.S. I have verified that it isn't a time-out error and it isn't a permissions issues.
Thanks
View 1 Replies
View Related
Jan 16, 2004
I am trying to call an RPC from SQL2KSP3 to Sybase ASE 12.5. SQL2K is on an IBM Intel Server running Win2KSP4, ASE is on an IBM RS6000 w/ AIX 5.2.
I have the ASE server set up as a linked server in SQL 2000, and doing queries is working fine, i.e. I can
SELECT * FROM asesrvr.testdb.dbo.tablename
and get back all the data in tablename from the testdb database on the ASE server asesrvr.
However, when I try to run an RPC using the same syntax, I get a generic 7212 error (Could not execute procedure 'procname' on remote server 'asesrvr'. I am calling the rpc with
DECLARE @output char(1)
EXEC asesrvr.testdb.dbo.procname @output output
I'm using Sybase's ASE OLEDB provider; on the Linked Server Properties, I have
General Tab
Product name: asesrvr
Data source: devprod
Provider string: <empty>
Location: greyed out
Catalog: <empty>
Security Tab
One local login, sa, which has impersonate checked as the sa passwords on the two servers are the same (I want to eliminate security as an issue while I try to get this working).
Server Options Tab
Collation Compatible: checked
Data Access: checked
RPC: checked
RPC Out: checked
Use Remote Collation: unchecked
Collation Name: <empty>
Connection Timeout: 0
Query Timeout: 0
SQL2K's @@version is
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Any ideas on how I can troubleshoot this further? It does not appear to be limited to this one procedure (this one only has one output parameter and one line of code, so it's about as simple as it can get for testing purposes). Again, queries work fine, so the linked server itself is connected and working, at least for queries.
Thanks very much for your help,
Vince
View 2 Replies
View Related