June 30, 2008 at 7:10 am
Hi guys,
I am trying to run the SQL Server 2005 Upgrade Advisor on a SQL Server 2000 database server in order to highlight any problems that we may run into if we decide to upgrade from 2000 to 2005 on that server. I’m relatively new to this company and so I’m not entirely sure why it’s been set up in this way, but even though there is only one instance of SQL Server on this server, it is a named instance and not a default instance. This seems to cause problems for the Upgrade Advisor as it can’t connect to the named instance and repeatedly gives the following error message:
“Unable to connect to server. Reason: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)”
I’ve been doing some reading around this error and it seems that this is caused by the fact that the SQL Server 2005 Upgrade Advisor relies on the “SQL Server Browser” service in order to function (http://support.microsoft.com/kb/908454).
This confuses me a little as I’m fairly sure that the SQL Server Browser service didn’t come in until SQL Server 2005 and SQL Server 2000 uses SQL Server Resolution Protocol (SSRP) instead. Now please correct me if I’m wrong (which I probably am) but doesn’t this mean that the SQL Server 2005 Upgrade Advisor is useless for named instances of SQL unless you already have SQL Server 2005 installed? If so, has anybody else run into this problem and had to think of an innovative solution that I can borrow?
Many thanks,
Dan
PS – Remote Server connections are enabled on this server, as are Named Pipes and TCP/IP (port 1433)
June 30, 2008 at 7:19 am
Also Try using the IP Address instead.
Maninder
www.dbanation.com
June 30, 2008 at 7:22 am
Hi Mani,
Thanks for the link.
I actually stumbled across that blog earlier and found it very useful as a starting point in understanding what was going on with the Upgrade Advisor and how it seems to work but none of the tips on the page made any difference to the error I've been getting...
Dan
June 30, 2008 at 7:33 am
in the default instance text box try \\ or \ in the start (first characters) Or try MSSQLSERVER\\NAME
Maninder
www.dbanation.com
June 30, 2008 at 7:54 am
Hi again Mani,
I tried that too! I wanted to enter server name/instance name but as soon as I entered the '\' it disables the next button and gives an error message:
"The specified instance of SQL Server is not valid. Check the instance name and then try again."
I don't think it's possible to specify anything with a \ in it...
Dan
July 16, 2008 at 6:44 am
Just put MSSQLSERVER as instancename. That's the default one.
October 13, 2008 at 3:27 pm
Using the text MSSQLSERVER worked for me as well.
Maybe I'm being too harsh or picky buit it seems to me that if a product (the upgrade advisor in this scenario) is going to act either non-intuitive or counter intuitive then the products own dialogs and messages should mention this. When the Upgrade Advisor returns the warning about not being able to connect and to look at Remote COnnections, it sure would have been beneficial to many a user to also see -
"Oh, and BTW, if the instance you are connecting to is the local install (not a secondary or tertiary instance on the local machine) then you must use MSSQLSERVER and not the actual name of the local server".
After all if this one tool is going to not work with the same name/text as the SQL Server tools before it then it really should mention this somewhere!
This is what you get when someone making the decission 'assumes' everyone else thinks like they do and sees things the way they do.
Kindest Regards,
Just say No to Facebook!January 7, 2009 at 2:33 am
I had the same problem trying to upgrade sql 7. using the ip address worked for me.
January 7, 2009 at 2:52 am
danchard (6/30/2008)
I wanted to enter server name/instance name
the correct syntax to use is
servername\instance
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply