Connecting SQL Server 2008 R2 to SQL Server 2000

Question

Initially, my desktop has been installed with SQL Server 2000 Standard Edition SQL Client. Recently, I installed SQL Server 2008 R2 client and server instance. I'm using SQL Server 2008 Management Studio to replace the old SQL Server 2000 Query Analyzer and Enterprise Manager.

Yesterday I try to install SQL Server 2000 instance server but I cannot connect because when I register the server instant on Entrprise Manager I get a message that I should use the SQL Server Management Studio or SQL Server Management Objects (SMO). Where is my SQL Server 2000 instance?

Answer 1

So this question is kind of confusing, you can connect to your SQL Server 2000 instances via SSMS 2008 R2.

It sounds like you are saying that you originally had an instance of SQL Server 2000 on your desktop which you used to connect to your instance via Enterprise Manager (which is now called SSMS), then you installed an instance of SQL Server 2008 R2 with SSMS, then you tried to install another instance of SQL Server 2000? So now you can not find your original instance of SQL Server 2000?

If this were me, I would just use SSMS that you installed with SQL Server 2008 R2 and try and connect to your instances using that. You can find what instances you have installed by checking your registry HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL.

I hope that I understand your question correctly and this helps.

Answer 2

Running SQL Server Management Studio on SQL Server 2000 is not really as supported option. Your best bet is to either upgrade the 2000 instance or to use SQLCMD (which should still work) to manage the instance from the command line.

You might be able to connect to a SQL Server 2000 from management studio 2005 (if you can find the install media).