Linked Server Problem From SQL 2005 To SQL 2000 Database
May 8, 2006
I have been unable to get my linked server to work when running queries against it in SQL Server 2005.
Example SQL Statement on my SQL 2005 server that I'm trying to run:
select top 100 * from [linkedservername].databsename.dbo.tablename
Error Message:
Msg 7416, Level 16, State 1, Line 1
Access to the remote server is denied because no login-mapping exists.
Print screen of the security properties of my SQL 2005 Linked Server:
http://www.webfound.net/linkedserver.jpg
The user sqladmin definitely has the right permissions to the right databases on the target linked server.
View 1 Replies
ADVERTISEMENT
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
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
Mar 7, 2008
Hi Guys,
I am trying to Query text file into SQL Server 2005 using linked server.
In SQL Server 2000 here is what I have.
Linked server configured to use following parameters
Product Name : Customer_file
Provider Name: Customer_File ( System DSN)
Here is basic file
0000003700 0000XXXXXX XX14 61J 1154-06-249
0000008600 0000XXXX11 YY17 41F 1144-12-309
0000010700 00023232323233 XX48 9DY 1145-10-299
0000015700 00001212121212 OX1 5RW 1163-08-079
0000026600 00001233333 YY1 31S 1150-12-111
0000027600 00001212121 YY15 8HU 1176-04-0891
0000038700 0000H13344 B120 8RF 1157-05-211
Now when I EXECUTE following command.
select * from openquery (TEST, 'select f1,f2,f3,f4,f5 from t#txt');
I normally get formatted output in columns..
As soon as I setup the same Linked server in SQL Server 2005, and follow same process I get all data in one column with first line as header.
I am basically concerting old import process into new one..so trying to figure out what i am missing?
Anay help?
THanks
SKR
View 1 Replies
View Related
Aug 31, 2007
Hello,
I have been searching the archives for information on problems that occur with creating a linked server to SQL Server 2000 from SQL Server 2005 x64, but the problem I am having seems to be slightly different.
I was able to create the linked server from the 2005 server this way;
Exec sp_addlinkedserver
@Server = '2kServer',
@srvproduct = 'SQL Server'
I was able to add the login this way;
Exec sp_addlinkedsrvlogin
@rmtsrvname = '2kServer',
@useself='False',
@locallogin='domaincdun2',
@rmtuser='domaincdun2',
@rmtpassword='pswd'
I receive 'Command(s) completed successfully' for both. When I attempt to run either of the following queries from a connection to the 2005 server in Management Studio;
select top 100 * from 2kServer.database.dbo.table
select * from Openquery(2kServer,'select top 10 * from database.dbo.table')
I get the following error
OLE DB provider "SQLNCLI" for linked server "2kServer" 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 'domaincdun2'.
I've seen a couple of ideas on this. One states the following;
***************************************
When running 4 part reference query like this:
select * from sql2000.mybase.dbo.mytable
SQL Server 2005 x64 runs the following query on remote SQL2000 server:
exec [mybase]..sp_tables_info_rowset_64 N'mytable', N'dbo', NULL
Unfortunately there is no such a proc on SQL2k. However, sp_tables_info_rowset exists and does the same thing. The solution is to create wrapper on master database like this:
create procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as
declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
And then everything works fine. If you don't want to create "Microsoft like" objects on master database, use openquery instead of 4 part reference.
**************************************
I put this proc on the 2kServer and tried again, but I got the same error. I've also seen this:
I went into the Sql Server Configuration Manager, Sql Native Client
Configuration -> Protocols and disabled Shared Memory and made the TCP/IP
protocol #1 in order. Then just restarted the Sql Service and it all started
working!
I don't have access to the server to do this, but I wonder if it would solve the problem. I am a sys admin on both servers. The servers are in two different domains. Could this be a firewall issue?
Thank you for your help!
cdun2
View 3 Replies
View Related
Oct 16, 2007
Can anyone tell me if there's a recommended way to link from a SQL 2000 SP4 DB through a firewall to SQL 2005? I have tried with SQLOLEDB but this uses NETBios which is not secure. Or is there a way to tell it not to use NETBios?
SET @provdetail = 'DRIVER={SQL SERVER};SERVER=<IP address>;Trusted_connection=Yes'
EXEC Master..sp_addlinkedserver
@server = ,'<IP address>',
@srvproduct = '',
@provider = 'SQLOLEDB',
@provstr = @provdetail ,
@catalog = 'TestDB',
@datasrc ='<IP address>DB1'
View 1 Replies
View Related
Dec 29, 2006
Interesting issue.. I think
I have 2 linked servers on my dev 2005 instance (sp1-win 2003). My 2005 instance shares the physical server with a 2000 instance. This linked server works fine. The second linked server is on a separate server running sql 2000 sp3. I cannot connect to this server at all. I can connect the other way -- from the 2000 to the 2005. I have tried all kinds of combinations of security and options but nothing works. The only resolutions I have found referenced installing Management Studio on the 2000 server or creating an ODBC connection from the 2005 instance. Any ideas???
Here is the error I get:
OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "ALLFLDB6" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.
View 4 Replies
View Related
Feb 9, 2007
I'm trying to connect to another slq server db through a linked server (and synonyms) but I get the following error.
Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. ,Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "BURT". A nested transaction was required because the XACT_ABORT option was set to OFF. , at Westfalia.TDMN.TxObjects.TxBurtOCIInterface.BurtOCIPalletChangedRecords_Get() , --- End of inner exception stack
I've set up MSDTC and executed the procedures noted in MS's article ID 873160. I am using 2005, but the Burt server is 2000. I can run the stored procedure from my server, which queries theirs. But when I execute the sp from a service, I get the error. Any help would be greatly appreciated.
View 1 Replies
View Related
Mar 14, 2007
I plan to migrate a SQL 2000 instance with everything in it to another SQL2000 server, and eventually in the future to SQL 2005.
I've pretty much figured out how to migrate everything execept the linked server configuration (there're many linked servers configured in the old server with different security credentials). I'm wondering if there's way I can save the linked server configuration and restore it to the other server.
Thanks much.
Ed
View 4 Replies
View Related
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 16, 2008
Hello
SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram
SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram
Symptoms
Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.
At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.
Please, any ideas?
Working
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'
Not Working (correctly anyway!)
DECALRE @Value tinyint
SET @Value = 22
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value
View 5 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
Aug 24, 2006
I am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong.
Thank you
View 5 Replies
View Related
Oct 21, 2006
Hi,
I have setup a linked server to a DB2 database using "Microsoft OLE DB Provider for ODBC Drivers",
the connections works fine i am able to run select queries.
but the number of rows returned from the two following statements are different.
select * from linkedserver...tablename
select count(*) from linkedserver...tablename
e.g. The First statement returns 116 rows, whereas the second query returns count as 144....
I am a bit confused ?????
could anybody think of any possible reasons..
Cheers
ARAW012
View 4 Replies
View Related
Sep 20, 2006
Has anyone managed to set up an IBM Universe database environment as a linked server to MS SQL 2005?
If so please convey how you got it done.
Thanks,
View 22 Replies
View Related
Nov 7, 2007
The following installation:
- Microsoft SQL Server 2005 Standard Edition running on Microsoft Windows Server 2003 Standard Edition
- Client Service for Netware installed.
- Access 2000 database in a Novell Directory Services (NDS) Environment
- SQL Server service running under local Administrator account.
Linked server configured as follow:
Linked server: MyDB
Provider: Microsoft Jet 4.0 OLE DB Provider
Product name: Access 2000
Data Source: UNC path to my Access database
Security: Admin with no password (default Access behaviour)
I can connect to my access database by following the next steps:
- Open Management studio on the SQL Server itself (where I'm logged in as Administrator)
- Connect using Windows Authentication (Administrator)
- Create a new query, run EXEC sp_tables_ex 'MyDB'
This will return all the tables from the Access database.
I can also make a view pointing to MyDB...MyTable.
Enough prove to me It can work....
So far so good
But now the tricky part.
When I login as 'sa', I can't connect and will receive the following message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyDB" returned message "'Full UNC to my access database' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyDB".
(In real life I want to connect to my SQL 2005 Server from a ASP.NET (2.0) application using SQL authentication, but as long as I can't get the above working this is not important for now)
View 3 Replies
View Related
Jul 26, 2005
I can't define a linked server in SQL Server 2005 x64 edition (to a SQLServer 2000 instance).
View 29 Replies
View Related
Apr 26, 2008
Hi,
We have a Webshop that is very old and we can't change the SQL server that it access.
But we had to upgrade the MS SQL server to a new hardware so we now have two SQL 2000 servers (the old one and the new one). All relevant data is on the new one but the webshop ONLY read from the old one)
How can I link the database on the old server soit can read and write to teh database on the new server.
What I mean is that I want the webserver to access the old SQL server (with a linked database to the new SQL server) so all data on the old database is the new database.
Is this possible and in that case how?
Both SQL servers are SQL 2000 (old one SP3 and new one SP4) Its no problem to upgrade the old one to SP4 aswell.
/Martin
View 5 Replies
View Related
Apr 24, 2015
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'Machine123Instance456',
@srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'
View 6 Replies
View Related
Jun 2, 2007
Dear all
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a swl statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Thank you so much for the help.
Dan
View 2 Replies
View Related
Jul 25, 2006
We have two sql servers using Windows authentication. One is sql 2000 the other sql 2005. When logged in as "sa", I can link these servers and run a SELECT distributed query without any problem.
But when one of our developers runs the same query (he is "dbo" of each database on each of the servers) he gets the error:
"OLE DB Provider "SQLNCLI" for linked server <Servername> returned message "Communication Link Failure"
...
Login failed for user '(null'). reason: Not associated with a trusted SQL Server connection"
Despite that, I, as "sa" equivalent, have no problem so it appears to be a permisisons issue. Any ideas what is going on here?
TIA,
Barkingdog
View 3 Replies
View Related
Jun 3, 2007
Dear all
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a sql statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Thank you so much for the help.
Dan
View 4 Replies
View Related
Jan 25, 2007
I am working on a project that was written in Visual Studio 2003 using asp.net and C#. The project accesses a SQL Server 2000 database. I need to test the application. What I want to do is to copy the database over to a test server. The problem is that the test server only has SQL Server 2005 on it. Would it be a problem if I backed up the database using SQL Server 2000 and then recreated it on the server as a SQL server 2005 project? Would my source code accept the new database even though it would now be in sql server 2005?
View 2 Replies
View Related
Sep 12, 2007
Hi i am a newbie and an developer i have an issue the Development we are doing is on sql server 2005 and we are wanting to send this database for a front end team to develop fromt end and they are using sql server 2000 , will there be any problems with regarding to version. if there are problems what would be the way to provide them with the Data.
View 3 Replies
View Related
Oct 4, 2005
Our product currently works with Cystal Reports but we decided to enhance it so it works with another reporting platform (MS Reporting Services). We are in the beginning stages of developement and trying to decide which version of RS (2000 or 2005?) to build the reports and integrate our web UI with. Most of our clients are using SQL Server 2000 for their database platform and we don't anticpate them upgrading to 2005 in the near future.
View 19 Replies
View Related
Dec 12, 2006
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database.
I'm not sure the best way to do this.......
Can anyone enlighten me?.....
View 4 Replies
View Related
May 16, 2007
Is there any workaround on this? I need to migrate my data in sql server 2005 into sql server 2000. Thanks in advance.
View 2 Replies
View Related
Mar 12, 2007
I would like to know if it is possible to make a backup of a database in a SQL Server 2005 server and restoring it in a SQL Server 2000 server.
Thanks a lot in advanced.
View 5 Replies
View Related
May 24, 2007
I have a 2005 sample database, but when I try to join it to my SQL Server 2000 server I get this error:
Error 602: ... execute DBCC CHECKTABLE in sysindexes
What should I do to join this database??
View 2 Replies
View Related
Jan 17, 2007
I have a SQL 2k database backup and I restore it in sql server 2005.
Is this now a SQL Server 2005 database or a 2K database in a 2005 server.
Means do I need to convert databases somehow when I migrated from 2000 to 2005. Is the above mentioned way a way to migrate... or are ther some conversation tools.
Any help / links / ressources would be Great...
Felix
View 5 Replies
View Related
Oct 7, 2006
How to import database in SQL server 2005 from the SQL server 2000 ??
View 1 Replies
View Related
Sep 5, 2007
Hi ,
Currently my application running on Sql-Server 2000 and I want to upgrade with Sql-Server 2005
will It work sql- server 2005?
if no what changed i need to do?
Can anyone help me?
Thanx
View 3 Replies
View Related
