February 4, 2008 at 12:14 pm
I have a SQL Server 2000 with SP4 box and a SQL Server 2005 with SP2 box.
I successfully created a linked server on my sql 2005 box linking to my sql 2000 box.
But I can't successfully create a linked server on my sql 2000 box linking to the sql 2005 box.
I get the following error when I click on "Tables" or "Views" under the Linked Server that I just created within the Enterprise Manager:
"SQL Server does not exist or access denied."
I also get the same error when I try a query hitting the linked server" in query analyzer.
The Server name, login and password i used for the linked server is correct.
Any help would be greatly appreciated!
Gary
February 4, 2008 at 1:34 pm
See if this helps any: http://msdn2.microsoft.com/en-us/library/aa215459(SQL.80).aspx
I've used it to fix the opposite problem you are having (can go from 2000 to 2005 but not 2005 to 2000), but perhaps it's the same type of issue?
Also make sure you have gone into the surface area configuration and have remote connections enabled.
The Redneck DBA
February 4, 2008 at 1:55 pm
Hi Jason,
I have remote connections enabled (TCP/IP).
So you have no problem creating a linked server on SQL 2000 linking to SQL 2005?
I read the suggested article, not sure that it applies to my situation. Running Instcat.sql seems to fix the problem going from 2005 to 2000 which already seems to work fine for me.
Gary
February 11, 2008 at 7:02 am
bump
February 11, 2008 at 9:49 am
Gary,
I got this problem initially and then I tried creating the Linked Server from QA sp_addlinkedserver and then sp_addlinkedsrvlogin with required parameters worked well. But this problem was Creating Linked Server from SQL2005 (SSMS) to SQL2K. Also check your remote server MSDTC Properties whether InBound and OutBound is configured properly if it is Windows Server 2003 with SP1 and above.
Thanks-Mubeen
February 19, 2008 at 8:29 am
Hi
I'm also have the same problem linking SQL Server 2000 and SQL server 2005. From 2005 I can query SQL 2000 databases. Where as in SQL 2000 its coming up with an error SQL Server does not exist or access denied. I have changed Regedt32.exe (MSDTC) from 0 to 1. But still I cann't seem to connect to 2005. Windows 2003 with SP1. Please help!!!
February 19, 2008 at 1:28 pm
Hi SSMMana,
I haven't gotten an answer for this yet.
The reason I had asked this question is that I have a SQL Server 2000 Reporting Server box that currently pulls most of the data from another SQL Server 2000 box.
When I migrate to SQL Server 2005, I wanted to see if I could point the SQL Server 2000 Reporting Server box to the upgraded SQL Server 2005 box, so that I don't have to migrate all the reports right away.
Do you have a similar reason for having a linked server from 2000 to 2005?
February 19, 2008 at 1:51 pm
Can you telnet to the instance on TCP 1433? i.e. Start -> Run -> Cmd
telnet MYSERVER 1433
Tommy
Follow @sqlscribeFebruary 20, 2008 at 12:32 am
No I can't I just run the above command and it returning blank screen. What could be the problem?
February 20, 2008 at 1:16 am
what edition (Developer,Standard or enterprise) of sql server are you using. And is SQL2005 a 64 bit or 32 bit edition.
try the below steps and let me know, login to the sqlserver2000 server and from the server box open Query analyzer and login to the SQLserver2005 server with the same login that you have given in linked server properties. let me know if this works first. thanks
February 20, 2008 at 1:49 am
Sqlserver 2000 Standard edition. Sqlserver 2005 32bit
I opened sqlserver2000 and use SQL Server Authentication. I then Logon with the linked server username and password.
Query Analyzer opened fine. I did the same thing on sqlserver2005 there was an error the user is not assocciated with trusted SQL Server connection. I'm using the same username and password for both instances and they both have admin right
February 20, 2008 at 2:14 am
you said.............
""I did the same thing on sqlserver2005 there was an error the user is not assocciated with trusted SQL Server connection. I'm using the same username and password for both instances and they both have admin right """
to understand more clearly the problem....
what is the sqlserver2005 authentication type, is it mixed or windows...
can you login with sa or any other sql account in sql2005 instance....
kindly confirm..
February 20, 2008 at 2:30 am
Sqlserver 2000 is Mix mode Sqlserver 2005 is windows authentication.
For linked server I setup the same Remote login
February 20, 2008 at 2:40 am
check the windows login account you have configured in remote login, make sure the login account is not locked..
password is not expired...
make sure the windows authentication is working first from your query analyzer in server.........
February 20, 2008 at 7:19 am
Hi Samuel,
I did what you said (trying to connect to SQL 2005 thru query analyzer on my sql 2000 box using SQL Server authentication), and I get the following error msg:
Unable to connect to server softdev1
Server: Msg 11, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General Network Error. Check your network documentation.
Using the same username and password, I can login with no problems when I'm on the SQL 2005 box.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply