SQL Server 2000 Link with SQL Server 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Server 2000 Link with SQL Server 2005

Author

Topic

Lyong_Min
Starting Member

11 Posts

Posted - 2011-02-25 : 22:33:07

Is it possible or a way to create a link on SQL Server 2000 with SQL Server 2005?

I need to create a linked server on SQL 2000. The linked server will be linked to SQL 2005.

I already tried creating a linked server on SQL 2005 linking to SQL 2000 and its ok. But I tried creating a linked server on SQL 2000 linking to SQL 2005 and no success.

My Reason is I need to create trigger on SQL 2000 that will update and insert records on SQL 2005 because all the data need by SQL 2005 will be coming from SQL 2000.

Any reply and suggestions are welcome....
Thanks in advance.....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-25 : 23:29:09

Yes you can link in both directions. What error did you get?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lyong_Min
Starting Member

11 Posts

Posted - 2011-02-25 : 23:57:58

After making the link on SQL 2000 and clicking on the Tables of the linked server, it will display an error message "Error 17: SQL Server does not exsist or access denied".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-26 : 00:14:02

That's a communication problem. Try adding an alias on the SQL 2000 database server that points to the SQL 2005 database server plus port number, such as Server1,1433.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lyong_Min
Starting Member

11 Posts

Posted - 2011-02-26 : 00:45:59

I already done what you said but I still get the same error message "Error 17: SQL Server does not exsist or access denied."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-26 : 18:58:05

Do a telnet test. Log directly into the 2000 database server and issue a telnet command to the 2005 server over the SQL listening port. If that fails, then you've got a network/firewall issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lyong_Min
Starting Member

11 Posts

Posted - 2011-02-27 : 19:38:51

@tkizer Thanks!! I'll try what you suggested and post the result later.....

Lyong_Min
Starting Member

11 Posts

Posted - 2011-03-06 : 21:37:16

@tkizer

Thanks for the help just solved my problem on Linking SQL 2000 with SQL 2005. Most of my problem occured due to firewall restriction where my SQL 2005 is installed. I found the solution to my problem here:

http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-07 : 13:20:22

Great, glad you got it working.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lyong_Min
Starting Member

11 Posts

Posted - 2011-03-09 : 00:48:46

@tkizer

Is this SQL statement correct on retrieving data from my Linked Server?
SELECT * FROM Payroll.dbo.PAY_LU_WORK_INFORMATION

I keep on getting a error message of:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Payroll.dbo.PAY_LU_WORK_INFORMATION'.

Or what is the best way to retrieve data from linked server?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-09 : 01:43:52

You've only specified the three-part name, you'll need the four-part name to use a linked server:

LinkedServerName.DatabaseName.SchemaName.ObjectName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lyong_Min
Starting Member

11 Posts

Posted - 2011-03-09 : 02:28:16

@tkizer

I tried this one:
SELECT * FROM Payroll.Abenson.dbo.PAY_LU_WORK_INFORMATION

And got this error:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'Payroll' does not contain table '"Abenson"."dbo"."PAY_LU_WORK_INFORMATION"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='Payroll', TableName='"Abenson"."dbo"."PAY_LU_WORK_INFORMATION"'].

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-09 : 02:38:37

Typically SchemaName is dbo. You'll need to verify each part to see what is wrong.

Do you even have a linked server named Payroll setup?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lyong_Min
Starting Member

11 Posts

Posted - 2011-03-09 : 03:15:43

Ms. Tara

Just a question, is displaying data from SQL 2005 and SQL 2000 linked server the same?
Because I tried SELECT * FROM Payroll.Abenson.dbo.PAY_LU_WORK_INFORMATION on SQL 2005 Server Management Studio (TEST SERVER)it just worked ok.

I tried it on SQL 2000 Query Analyzer it does'nt work.

Actually I'm trying it on SQL 2000 because I linked my SQL 2005 to SQL 2000. And SQL 2000 is my primary production server and SQL 2005 is just my secondary.....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-09 : 11:43:05

If you don't have the linked server on your 2000 system, then you can not use it there. The linked server object has to exist on the system where you are initiating the query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lyong_Min
Starting Member

11 Posts

Posted - 2011-03-09 : 19:28:13

I already have my sql 2005 linked with sql 2000 as PAYROLL. I can also see the tables with in sql 2000 linked server. But I don't know how to query, update, insert data to my linked server PAYROLL via sql 2000 enterprise manager.

I know how to do it in sql 2005 but I think its not the same with sql 2000. I keep on getting error I mentioned above.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-09 : 19:38:17

There is no difference in the query. I am confused on which system has the linked server. It has to be on the server where you are initiating the query. If you want to query in both directions, then you need two linked servers.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lyong_Min
Starting Member

11 Posts

Posted - 2011-03-09 : 20:20:49

Only sql 2000 has the linked server. Because my sql 2000 need to forward data to sql 2005.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-10 : 00:42:21

Check permissions for the linked server account on the 2005 server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lyong_Min
Starting Member

11 Posts

Posted - 2011-03-10 : 03:55:32

Ms. Tara

Thanks for the help finally got it working properly. My SQL 2000 is now updating my SQL 2005

Thank you very much Ms. Tara!!! You're a great help......

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-10 : 12:50:49

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources