SQL 2000 connectivety to SQL 2008 database

Question

SQL 2000 connectivety to SQL 2008 database

Katherine Mitchell

SSC Enthusiast

Points: 118
More actions
March 2, 2010 at 10:17 am

#221228

A vendor of ours, who supplies no SQL support, other than creating their databases, has most of their databases on a SQL 2000/Win 2003 platform.
They are slowly moving their individual application databases to SQL 2008 (64-bit)/Win 2008 (64-bit).... one application at a time. This is being done in tandem with respective application upgrade to a newer version.
The servers are "ours".
We have to provide/develop/code any data interfaces needed for our own internal purposes.
I have researched either SQL 2000 Query analyzer connections to SQL 2008 and also SQL 2000 linked server to SQL 2008. Cannot find any discussion regarding 2000 to 2008, and these attempts result in connection failures.
Can SQL 2000 drive the interface to a SQL 2008 database or does it have to be done SQL 2008 back to SQL 2000?
Sure hate to lose the simplicity that is Query Analyzer. I have never been a big fan of GUI-heavy interfaces, which I find SSMS Query to be.
I apologize if this has been answered already, but again, I have been looking and did not find this question directly amongst posts.
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

You must use SSMS for SQL 2005 at least. If you don't use SQL 2008, you will not have any of the wonderful benefits in SSMS, SSIS, SSRS, etc.... You would be better of installed SQL 2008.

The Management Studios are really much better than QA and EM. Took me a little while to get used to them, but now I do not like having to use EM or QA. I only use EM if I have to modify a DTS Package

Andrew SQLDBA

rajganesh.dba SSCrazy Points: 2488 More actions · Answer 2

Just a note -

SQL server 2000 client tools cannot connect SQL 2005 and 2008 ,

SQL server 2005 client tools cannot connect SQL 2008 but connects 2000

SQL server 2008 client tools can connect both SQL 2008 and 2000

Katherine Mitchell SSC Enthusiast Points: 118 More actions · Answer 3

What drove the question was this statement -

[font="Courier New"]Connecting to SQL Server 2005 and newer versions with Enterprise Manager is not supported. Connecting to SQL Server and newer versions with the SQL Server 2000, service pack 4 version of Query Analyzer is supported.[/font]

...at this link: http://msdn.microsoft.com/en-us/library/ms174190.aspx

I have yet to get it to work. And I am not able to create a linked server to SQL 2008 database from SQL 2000. It creates, but is not "operable". Any connection attempt using the linked server fails.

Katherine Mitchell SSC Enthusiast Points: 118 More actions · Answer 4

Got this to work.

SQL Server 2008's server (SQL1) had to have firewall port (1433) opened/enabled for SQL 2000's instance (RM11) to be able to connect to it.

I now have successfully created a linked server in SQL 2000 Enterprise Manager as well as I can open/connect to the SQL 2008 server in Query Analyzer.

Basic query via each work and return rows as of this post.... not saying something more complicated may not work.

But, I only plan to do simple queries of user database data to facilitate data exchanges between applications.

Thanks for all your replies. I appreciate it.

Stegzy SSC Journeyman Points: 86 More actions · Answer 5

I have created a linked server from SQL 2000 to 2008 using SQL OLE DB Provider, but querying the remote SQL 2008 server only works intermittently. I get: Server: Msg 8179, Level 16, State 1, Line 2 Could not find prepared statement with handle -1. I installed the SQL 2008 native client on the SQL 2000 server but still the same. The Windows firewall is not enabled on the server either. Anyone have any ideas?

Thanks