Odd issue connecting to a named instance.

Hi everyone,

Please forgive me for the newbie question, I've done almost all of my DBA work on Oracle(on UNIX so I'm learning alot of AD stuff too) and am new to MS SQL Server.

I've got a SQL 2005 standard server that I'm trying to get to act as a publisher for a SQL 2005 Express instance, and I think I've narrowed down my problem to a connection issue.

(FYI, the Publisher works fine with another 2005 Standard server)

When I try to connect from the standard server to the express server using 'XYZ\SQLEXRESS' as my server name, it fails stating that it couldn't find the service, but I can connect just fine if I use 'XYZ' as the server name.

I think this is why I'm having trouble getting replication going as the replication wizard tells me that I have to connect via 'XYZ\SQLEXPRESS' in order to run the replication scripts.

Can anyone point me to the correct documentation to resolve this problem? Everything was easy-mode DBA work until we tried to add SQL Express to the mix, and I'm sort of running out of time on this one.

Thanks for any help!

The main thing to check is SQLExpress will default to local only connectivity mode. So if you are trying to access SQLExpress from a different machine this will not work unless you enable remote access. To do this run the SQL Server 2005 Surface Area Configuration tool and select your instance and enable remote connections (then restart SQLExpress instance).

If everything is local, then I would suspect that the SQL Browser service is disabled. This service is used to locate named instances. So enable this service and start it and everything should start working.

|||

Ahh, I'm sorry I didn't clairify.

I've got network connections on the SQL Express machine enabled, and is setup on port 1433. I'm attempting to connect from a remote machine via the management tool for testing purposes. If I try to connect using 'XYZ' as my server, it works, but if I try to connect using 'XYZ\SQLEXPRESS', it fails. I can connect as ether if I am doing it locally on the SQL Express machine.

Thanks again!

EDIT: Hahaha! You gave me the answer anyways. I enabled the SQL browser, and it worked. :-)

Thanks for beign a life-saver.

|||

Ahh cool!

ODBC SQL 2005 and Named Instance - won't connect

We have two instances of SQL 2005 installed, with static TCP/IP ports, one
instance on one IP address, port 1433 and the other instance on a different
IP address, port 1433.
Try to create an ODBC System DSN using SQL Native Client and it won't
connect.
If we do the same thing but using a default instance of SQL 2005, ODBC
connects fine.
Any help anyone can give as to why ODBC won't connect to the SQL 2005 names
instances?
I've tried searching thru MSDN and TechNet and such but found nothing of any
help.
Michael MacGregor
Database Architect
Never mind, got it to work, having taken a sledgehammer to the server.
Disabled all IP addresses other than the one specified for the instance,
then restarted the whole box, and hey presto it finally worked.
Now we just have to get LDAP to recognise the named instances. Yeah I know,
old technology and all that stuff.
MTM
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:emWQtlFGGHA.3728@.tk2msftngp13.phx.gbl...
> We have two instances of SQL 2005 installed, with static TCP/IP ports, one
> instance on one IP address, port 1433 and the other instance on a
> different IP address, port 1433.
> Try to create an ODBC System DSN using SQL Native Client and it won't
> connect.
> If we do the same thing but using a default instance of SQL 2005, ODBC
> connects fine.
> Any help anyone can give as to why ODBC won't connect to the SQL 2005
> names instances?
> I've tried searching thru MSDN and TechNet and such but found nothing of
> any help.
> Michael MacGregor
> Database Architect
>

ODBC SQL 2005 and Named Instance - won't connect

We have two instances of SQL 2005 installed, with static TCP/IP ports, one
instance on one IP address, port 1433 and the other instance on a different
IP address, port 1433.
Try to create an ODBC System DSN using SQL Native Client and it won't
connect.
If we do the same thing but using a default instance of SQL 2005, ODBC
connects fine.
Any help anyone can give as to why ODBC won't connect to the SQL 2005 names
instances?
I've tried searching thru MSDN and technet and such but found nothing of any
help.
Michael MacGregor
Database ArchitectNever mind, got it to work, having taken a sledgehammer to the server.
Disabled all IP addresses other than the one specified for the instance,
then restarted the whole box, and hey presto it finally worked.
Now we just have to get LDAP to recognise the named instances. Yeah I know,
old technology and all that stuff.
MTM
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:emWQtlFGGHA.3728@.tk2msftngp13.phx.gbl...
> We have two instances of SQL 2005 installed, with static TCP/IP ports, one
> instance on one IP address, port 1433 and the other instance on a
> different IP address, port 1433.
> Try to create an ODBC System DSN using SQL Native Client and it won't
> connect.
> If we do the same thing but using a default instance of SQL 2005, ODBC
> connects fine.
> Any help anyone can give as to why ODBC won't connect to the SQL 2005
> names instances?
> I've tried searching thru MSDN and technet and such but found nothing of
> any help.
> Michael MacGregor
> Database Architect
>

ODBC Port

Hi, I have just installed a default instance of SQL server 2000 and can
connect ok with Query Analyser. I can connect ok with ODBC via a DSN if I
don't specify the port. However if I specify the port (which is 1433 I've
double checked) in the DSN or a JDBC connection it fails! Can somebody help
me?
Thanks
Adam Sankey
on the sql serve box :
check the programs->sqlserver2000>sql server network utilitity
click tcp and check properties
you should be able to see the port configured, if tcp is enabled
thanks
"Adam Sankey" <AdamSankey@.discussions.microsoft.com> wrote in message
news:5F774885-4F36-436F-9AF9-6AA5A4586426@.microsoft.com...
> Hi, I have just installed a default instance of SQL server 2000 and can
> connect ok with Query Analyser. I can connect ok with ODBC via a DSN if I
> don't specify the port. However if I specify the port (which is 1433 I've
> double checked) in the DSN or a JDBC connection it fails! Can somebody
> help
> me?
> Thanks
> Adam Sankey

ODBC Port

Hi, I have just installed a default instance of SQL server 2000 and can
connect ok with Query Analyser. I can connect ok with ODBC via a DSN if I
don't specify the port. However if I specify the port (which is 1433 I've
double checked) in the DSN or a JDBC connection it fails! Can somebody help
me?
Thanks
Adam Sankeyon the sql serve box :
check the programs->sqlserver2000>sql server network utilitity
click tcp and check properties
you should be able to see the port configured, if tcp is enabled
thanks
"Adam Sankey" <AdamSankey@.discussions.microsoft.com> wrote in message
news:5F774885-4F36-436F-9AF9-6AA5A4586426@.microsoft.com...
> Hi, I have just installed a default instance of SQL server 2000 and can
> connect ok with Query Analyser. I can connect ok with ODBC via a DSN if I
> don't specify the port. However if I specify the port (which is 1433 I've
> double checked) in the DSN or a JDBC connection it fails! Can somebody
> help
> me?
> Thanks
> Adam Sankey