July 7, 2009 at 9:59 am
Hi, this is my first post on this site since joining, but I am working on upgrading my servers to sql 2008, from sql 2000 and am running into a problem.
We have four servers running sql 2000, and a central server we want to upgrade to sql 2008 to begin our upgrade process. I have read many blogs about installing sp4 on sql2000 as the starting point and how to run the instcat.sql script so that sql 2000 can talk to sql 2008. I have run the instcat.sql script successfully on but my sql 2000 box still gives an invalid instance message when I try to connect to one of our servers running 2008. The s2k sp4 was installed successfully and shows the 8.00.2039 in the @@version. My sql 2008 can talk to my sql 2000 box but not the other way around. I did run the instcat through query analyzer as opposed to command line exec(as some instructions indicate) but it seemed to run ok. The only error I did get was the error about 'cannot add rows to sysdepends.....' in relation to sp_ddopen but. My understanding is that was related to 6.5 sql so I don't believe that to be a problem. Is there something else I am missing or any ideas?
Thanks
Mike
July 7, 2009 at 10:24 am
Is the SQL 2008 installed as a default instance listening on port 1433.
On the SQL 2008 box is the SQL Browser running?
On the boxes that work are you using aliases that reference the SQL 2008 box?
On the box that doesn't work can you ping the SQL 2008 servername NOT THE IP, you are testing for name resolution here.
Can you give us the exact message?
CEWII
July 7, 2009 at 12:28 pm
Is the SQL 2008 installed as a default instance listening on port 1433.
there are multiple instances(all sql 2008)listening on different ports including 1433. I am using TCP/IP to connect instead of named pipes. I've been under the assumption that SQL browser was able to find the default instance no matter which port it was on. Could the multiple instances be the problem?
On the SQL 2008 box is the SQL Browser running?
yes, the service is currently running
On the boxes that work are you using aliases that reference the SQL 2008 box?
I'm working in a test environment right now. This is the first attempt on one test system so I do not have any references to the 2008 system as of yet.
On the box that doesn't work can you ping the SQL 2008 servername NOT THE IP, you are testing for name resolution here.
yes, i can ping and get name resoution
Can you give us the exact message?
Error source: Microsoft OLEDB provider for SQL Server
Error description:[DBNETLIB}{ConnectionOpen(inavlid instance()).]Invalid connection.
Thanks
Mickle
.
July 7, 2009 at 12:40 pm
A quick google search of the txt of that message brings up
User Instance=True in the connection string at:
http://www.bigresource.com/MS_SQL-User-Instance-True-causes-invalid-connection-string-czeUOzHA.html
Some more information here:
Some more here:
http://www.ureader.com/msg/114322.aspx
I am not sure that any of these will resolve it, the one thing I think works is to include the port number. I don't think I agree with the statement that SQL Browser can find the default instance of SQL no matter what its port is.. I believe the libraries try first at 1433 if there isn't an instance name, what is really freaky is that only one box has the problem, could there be a firewall issue on that one box? A stretch I know..
CEWII
July 7, 2009 at 1:33 pm
I did check out the links. I was able to finally connect. My assumption of the default instance is where the problem was as you mentioned. When I changed my connect string to servername\instancename I was able to connect succesfully to my 2008 server from 2000.
Thanks very much for your help!
Mickle
July 7, 2009 at 5:30 pm
Great, glad to hear it!
CEWII
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply