Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Thursday, November 17, 2016 6:31 AM
I still have SQL 2000 installed to support one lone client hanging on to the 2000 version. I also have SQL Server 2008 and have just installed SQL Server 2014 SP2 (Developer's). Although I can connect to the 2000 engine from the 2008 management studio and of course with Enterprise Manager, I am unable to connect using SSMS 2014.
Is it possible to connect using SSMS 2014. If so, I would appreciate the answer.
Thanks in advance for any help you may provide!
Charles S. Cotton
Thursday, November 17, 2016 6:42 AM
Hello Charles,
I don't think it will work, current Version of SSMS supports to connect to older SQL Server Version, but not to the very, very old SQL Server 2000.
See SQL Server Management Studio => Supported SQL Server Versions: 2008 - 2016
Olaf Helper
Thursday, November 17, 2016 7:08 AM
Hi Charles, according to the Connect bug below, it is not supported to connect SSMS 2014 to SQL Server 2000.
"Thank you for submitting this feedback. SQL Server 2000 reached the end of its support lifecycle on April 9, 2013. For reference, see http://support.microsoft.com/lifecycle/search/default.aspx?alpha=sql+server+2000&Filter=FilterNO
SQL Server 2000 instances can be managed using earlier versions of SQL Server Management Studio, but SQL Server 2000 management from SQL Server Management Studio 2014 is not a supported scenario."
Thanks,
Sam Lester (MSFT)
http://blogs.msdn.com/samlester
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.
Thursday, November 17, 2016 7:46 AM
While Samuel is correct in that it is not formally supported, it is certainly possible, see below - the screen shot is from SSMS 2016.
What happens when you try?
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Thursday, November 17, 2016 8:10 AM
No you can't.
SQL Server 2000 requires Enterprise Manager; SSMS was introduced with SQL Server 2005 and it is completely different as there are significant changes between SQL Server 2000 and 2005, e.g. SSIS instead of DTS; SQL Server Agent is no longer a part of the "Management Structure", Maintenance Plans were introduced. As such SSMS cannot "organise" and therefore "present" any previous version of a SQL Server Database.
You need the set of Management Tools that comes with SQL Server 2000, e.g. the aforementioned Enterprise Manager and Query Analyser.
Please click "Mark As Answer" if my post helped. Tony C.
Thursday, November 17, 2016 2:35 PM
Thanks, Erland.
Did you have to do anything special to achieve the connection between SSMS 2016 and the 2000 database?
If so, the same might work for SSMS 2014.
Charles.
Charles S. Cotton
Thursday, November 17, 2016 3:45 PM
Erland, that's only a simple query window; what about "Object Explorer", does that one works to (just beeing curious)?
Olaf Helper
Thursday, November 17, 2016 4:12 PM
Erland, that's only a simple query window; what about "Object Explorer", does that one works to (just beeing curious)?
Olaf Helper
Hi Olaf. As stated in my response further up; a SQL 2000 database cannot be managed from any version of SSMS; it requires something called Enterprise Manager due to it's different construct, layout, fundamental differences in technology etc
Please click "Mark As Answer" if my post helped. Tony C.
Thursday, November 17, 2016 5:47 PM
To the OP's question of "can you connect", you responded "no" - which Erland has proven can be done. OP already indicated that he currently uses SSMS from 2008 to successfully manage the 2000 server, so your response is not accurate (being generous here). Nor is it appropriate for you to mark your own response as an answer. It certainly isn't supported - but you went much, much further than that.
Thursday, November 17, 2016 10:13 PM
Did you have to do anything special to achieve the connection between SSMS 2016 and the 2000 database?
No.
If you want help with why it does not work for you, you really need to tell us what error message you get.
As Anthony points out, you cannot expect management dialogs to work, but a plain query window works. Object Explorer? Nah, when you expand a database, this fails on has_perms_by_name missing.
Thursday, November 17, 2016 11:27 PM
I don't believe you were talking to me, but I get no error message when trying to connect to SQL Server 2000 from withing SMSS 2014. The connect attempt just hangs until Cancel is clicked. This is true for both Windows Authentication and with the SQL Server admin (sa) user.
Charles S. Cotton
Friday, November 18, 2016 10:15 PM
No, that's funny. One would expect that you would either get connected or get a connection error after 15 seconds or so. Unless, that is, you have actually configured a very long connection timeout.
One possibility is that SSMS is running some initial query that gets blocked on your SQL 2000 instance.
What happens if you try to connect from SQLCMD on the same machine?
Saturday, November 19, 2016 4:23 AM
Hi Olaf. As stated in my response further up; a SQL 2000 database cannot be managed from any version of SSMS
Anthony, you can manage a SQL Server 2000 very well by using SSMS 2005, that works; I was using this for several years without any issue.
Even for current version of SSMS (=>Download SQL Server Management Studio (SSMS)) there is mentioned: "There is no explicit block for SQL Server 2000 or SQL Server 2005, but some features may not work properly."
Olaf Helper
Wednesday, August 14, 2019 7:32 AM
Hi! Charles,
Since this thread/question has been 3 years ago, I don't know if you have solved this problem or not.
I also had the same question: how to connect to SQL Server 2000 using MS SSMS 2014. I was stuck at the server's instance name. I only know the server name and the database name and whether I need the Windows authentication or SQL Server authentication. I just put "SQL Server" for my instance name and it would give me the error 26.
At first, I put "[server name]\SQL Server" for my Server Name and choose the corresponding authentication. It would just prompt me "... (provider: SQL Network Interfaces, error: 26 - ..." Then I just clear the guessing part and only enter the server's name. And it just connects!!
Rosa Hoh