October 4, 2006 at 4:58 am
Hi,
My company has a development server which has both SQL 2000 and 2005 installed. When I connect to the server and do a @@VERSION, it returns Microsoft SQL Server 2000 - 8.00.760
However, I want it to appear outwardly as a SQL 2005 server.
I assume SQL 2005 was installed first, and then SQL 2000 at a later date, and thats why it "behaves" as a SQL 2000 box.
I'm trying to use the Transfer Database Task in SSIS to transfer databases from our VM's (which have SQL 2005 installed) to a proper server so as to simulate stress tests for our latest product. SSIS won't allow this, as it complains about the destination server being a lower version.
I imagine some registry settings got over-written, which may explain the issue. However, I'd be loathe to change settings without futher qualification.
Thanks,
Graham
PS: I'm a .NET developer that doesnt really have a clue about DBA-type work (and there's no-one in the company who has a role of DBA), so any help would be much appreciated.
October 4, 2006 at 7:40 am
Hello Graham,
Whenever SQL Server is installed, it first checks whether there is any default instance available on the server. If so, it will prompt the user whether to upgrade it or install a named instance. As per your information, I assume that both the versions have been installed and SQL Server 2000 might be a default instance and SQL Server 2005 might be a named instance otherwise, both the instances (SQL Server 2000 and SQL Server 2005) might be named instances.
This you can confirm by checking "Services". If you see,
SQLSERVER2000 / SQLAgent - It is a default instance of SQL 2000.
MSSQL$SQLSERVER2000 / SQLAgent$SQLSERVER2000 - Named instance of 2000.
SQL Server (SQLSERVER2005) / SQL Server Agent (SQLSERVER2005) - It is a named instance.
Another thing, you can't access SQL Server 2005 from SQL Server 2000 Enterprise Manager, but you can access SQL Server 2000 from Microsoft SQL Server Management Studio.
I would advise you to open up Microsoft SQL Server Management Studio and register both the server instances i.e., SQL Server 2000 and SQL Server 2005. Now you open up Query window from Management Studio and it might prompt you to which server you want to connect. Select the appropriate server and then give the command SELECT @@VERSION
Hope this information might give you the required information.
Thanks
Lucky
October 4, 2006 at 7:40 am
Hi Graham,
if @@VERSION returns Microsoft SQL Server 2000 - 8.00.760 then you're connected to the SQL Server 2000 instance on that box.
With both SQL 2000 and 2005 installed on the same machine, one of those (seems like it's the 2005 version) is installed as a non-default instance. In other words, to connect to the 2005 server you'll need to supply the instance name in the connection: <server name>\<instance name>
You can find out the instance name by going into the SQL Server Configuration Manager (Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools). Click on the services and in the right hand pane you'll get the services. Look for the SQL Server () service. Whatever name is in the brackets is the instance name (unless it's MSSQLServer, in which case this is the default instance).
Hope that helps,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply