Connectivity with a SQL instance.

Hi,
I installed on a failover cluster, one default virtual sql
server installation named SERVER1 and a other one
installation of virtual server with instance name called
server2\ufa.
When I try to connect true a firewall on SERVER1 with an
UDL file the connection is OK, but When I'm trying the
same test on SERVER2\ufa I've got this error :
[DBNETLIB][ConnectionOpen (Invalid Instance()).]
When I pinging SERVER1 it's OK, When I trying a telnet
command on IP adresse 1433 it's also OK
But on virtual SERVER2 the port 1433 is on the name
SERVER2 so when I try a command telnet SERVER2 1433 it's
OK but SERVER2\UFA is not recognize.
On the firewall the port 1433 is open for Both IP adresses
and we openned also the ports 139 and 445 for the Netbios.
( Name Pipes )
Someone can help me ?You probably need to open up port 1434 or better yet, open the Server
Network Utility on SERVER2 and set the port explicity to something other
than 1433 or 1434, then connect using the port number rather than the
instance name. This is well documented in Books on Line under firewalls.
"Pierre" <anonymous@.discussions.microsoft.com> wrote in message
news:bfde01c4086c$a9472a00$a501280a@.phx.gbl...
> Hi,
> I installed on a failover cluster, one default virtual sql
> server installation named SERVER1 and a other one
> installation of virtual server with instance name called
> server2\ufa.
> When I try to connect true a firewall on SERVER1 with an
> UDL file the connection is OK, but When I'm trying the
> same test on SERVER2\ufa I've got this error :
> [DBNETLIB][ConnectionOpen (Invalid Instance()).]
> When I pinging SERVER1 it's OK, When I trying a telnet
> command on IP adresse 1433 it's also OK
> But on virtual SERVER2 the port 1433 is on the name
> SERVER2 so when I try a command telnet SERVER2 1433 it's
> OK but SERVER2\UFA is not recognize.
> On the firewall the port 1433 is open for Both IP adresses
> and we openned also the ports 139 and 445 for the Netbios.
> ( Name Pipes )
> Someone can help me ?
>|||To be able to connect to a named instance, (both clustered and stand alone)
1) If the client has MDAC 2.5 or lower, then you have to specify the port
number when connecting and make sure this port is open at the firewall.
2)If the client has MDAC 2.6 or higher, then specify the port number when
connecting or make sure UDP port 1434 is open at the firewall (This is true
even if the named instance is listening on port 1433)
The following articles maybe helpful.
286303 INF: Behavior of SQL Server 2000 Network Library During Dynamic Port
http://support.microsoft.com/?id=286303
287932 INF: TCP Ports Needed for Communication to SQL Server Through a
Firewall
http://support.microsoft.com/?id=287932
286303 INF: Behavior of SQL Server 2000 Network Library During Dynamic Port
http://support.microsoft.com/?id=286303
265808 INF: How to Connect to an SQL Server 2000 Named Instance with the
http://support.microsoft.com/?id=265808
Thanks
Deepali
This posting is provided "AS IS" with no warranties, and confers no rights

Connectivity Issue with SQL Server 2005

Hi,
I have a SQL Server 2005 running on my machine with a named intance.
Recently I have got a DSL connection. After that the Microsoft SQL
Server Management Studio started giving connectivity issues.
Here I have copied the error Message. Could anybody help me out in
finding what is actually going wrong after getting a DSL Connection.
Thanks in Advance,
Suresh
TITLE: Connect to Server
--
Cannot connect to SURESH1023\SQLEXPRESS.
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Named Pipes
Provider, error: 0 - The specified network name is no longer
available.) (Microsoft SQL Server, Error: 64)
For help, click:
http://go.microsoft.com/fwlink?Prod...64&LinkId=20476
BUTTONS:
OK
--"Gr8Ongole" <suresh.thotakura@.gmail.com> wrote in message
news:1165071352.266622.28580@.l12g2000cwl.googlegroups.com...
> Hi,
> I have a SQL Server 2005 running on my machine with a named intance.
> Recently I have got a DSL connection. After that the Microsoft SQL
> Server Management Studio started giving connectivity issues.
> Here I have copied the error Message. Could anybody help me out in
> finding what is actually going wrong after getting a DSL Connection.
> Thanks in Advance,
> Suresh
> TITLE: Connect to Server
> --
> Cannot connect to SURESH1023\SQLEXPRESS.
> --
> ADDITIONAL INFORMATION:
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Named Pipes
> Provider, error: 0 - The specified network name is no longer
> available.) (Microsoft SQL Server, Error: 64)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...64&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
Try forcing a TCP/IP connection by connecting to
tcp:SURESH1023\SQLEXPRESS
instead.
David

Connectivity Issue with SQL Server 2005

Hi,
I have a SQL Server 2005 running on my machine with a named intance.
Recently I have got a DSL connection. After that the Microsoft SQL
Server Management Studio started giving connectivity issues.
Here I have copied the error Message. Could anybody help me out in
finding what is actually going wrong after getting a DSL Connection.
Thanks in Advance,
Suresh
TITLE: Connect to Server
--
Cannot connect to SURESH1023\SQLEXPRESS.
--
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Named Pipes
Provider, error: 0 - The specified network name is no longer
available.) (Microsoft SQL Server, Error: 64)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476
--
BUTTONS:
OK
--"Gr8Ongole" <suresh.thotakura@.gmail.com> wrote in message
news:1165071352.266622.28580@.l12g2000cwl.googlegroups.com...
> Hi,
> I have a SQL Server 2005 running on my machine with a named intance.
> Recently I have got a DSL connection. After that the Microsoft SQL
> Server Management Studio started giving connectivity issues.
> Here I have copied the error Message. Could anybody help me out in
> finding what is actually going wrong after getting a DSL Connection.
> Thanks in Advance,
> Suresh
> TITLE: Connect to Server
> --
> Cannot connect to SURESH1023\SQLEXPRESS.
> --
> ADDITIONAL INFORMATION:
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Named Pipes
> Provider, error: 0 - The specified network name is no longer
> available.) (Microsoft SQL Server, Error: 64)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476
> --
> BUTTONS:
> OK
> --
>
Try forcing a TCP/IP connection by connecting to
tcp:SURESH1023\SQLEXPRESS
instead.
David

Connectivity Issue with SQL Server 2005

Hi,
I have a SQL Server 2005 running on my machine with a named intance.
Recently I have got a DSL connection. After that the Microsoft SQL
Server Management Studio started giving connectivity issues.
Here I have copied the error Message. Could anybody help me out in
finding what is actually going wrong after getting a DSL Connection.
Thanks in Advance,
Suresh
TITLE: Connect to Server
Cannot connect to SURESH1023\SQLEXPRESS.
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Named Pipes
Provider, error: 0 - The specified network name is no longer
available.) (Microsoft SQL Server, Error: 64)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLS erver&EvtID=64&LinkId=20476[/url]
BUTTONS:
OK
"Gr8Ongole" <suresh.thotakura@.gmail.com> wrote in message
news:1165071352.266622.28580@.l12g2000cwl.googlegro ups.com...
> Hi,
> I have a SQL Server 2005 running on my machine with a named intance.
> Recently I have got a DSL connection. After that the Microsoft SQL
> Server Management Studio started giving connectivity issues.
> Here I have copied the error Message. Could anybody help me out in
> finding what is actually going wrong after getting a DSL Connection.
> Thanks in Advance,
> Suresh
> TITLE: Connect to Server
> --
> Cannot connect to SURESH1023\SQLEXPRESS.
> --
> ADDITIONAL INFORMATION:
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Named Pipes
> Provider, error: 0 - The specified network name is no longer
> available.) (Microsoft SQL Server, Error: 64)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLS erver&EvtID=64&LinkId=20476[/url]
> --
> BUTTONS:
> OK
> --
>
Try forcing a TCP/IP connection by connecting to
tcp:SURESH1023\SQLEXPRESS
instead.
David

Connectivity issue with SQL 2005 Developer Edition Named Instance

Hi,

I have installed one default and one named instance of SQL Server 2005 Dev. Editition SP2 on my server.

The Named service is configured to use Shared, NamedPipe and TCP (Port 4333).

There is no firewall on the machine. But still i am not able to connect to it form remote computer.

can any one help me with these. this is urgent.

Thanks.

KV

I found it. My SQL Browser service was running under domain account which was not working, i have configured it to run under NetworkService and it has started. sqlsql

connectivity

dears,
I've installed one sql server instance .
It is connecting through Named pipes only
Through TCP/IP its not connecting.
Only the default instance in the machine is connecting through all
protocols
Why is it like that ?
Please help me...chaluvadi specialists (sanujss@.gmail.com) writes:
> dears,
> I've installed one sql server instance .
> It is connecting through Named pipes only
> Through TCP/IP its not connecting.
> Only the default instance in the machine is connecting through all
> protocols
> Why is it like that ?
> Please help me...
Which versions of SQL Server are the two instances running?
How do you conclude that you cannot connect to the named instance
through TCP/IP?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> I've installed one sql server instance .
> It is connecting through Named pipes only
> Through TCP/IP its not connecting.
> Only the default instance in the machine is connecting through all
> protocols
Let's get the obvious out of the way:
What is the possibility that there is a firewall between your client and
your server that is blocking TCP:1433?
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei

Connection: "(local)" vs "ComputerName"

Hi,
I was reading in a book about SQL 2005 about how connections are made to SQL
2005. Assume I have a SQL Server instance named SQL1 and I have a client app
installed on SQL1. The book claimed that if the app's connection string
sets the datasource to "(local)" then the connection uses Shared Memory, and
if it set the datasource to the computer's name, "SQL1", that it uses the
TCP/IP stack which is slower than Shared Memory.
Reading BOL for SQL 2000, it seems to indicate that a local client will
always use Shared Memory, regardless of how the datasource is specified.
Can someone provide a definitive clarification and indicate whether it is
different for SQL 2000 and SQL 2005?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
Hi Daniel,
I am afraid that the book description is not correct. By default any
connection with the following names from local computer will use Shared
Memory protocol:
1. "<computer_name>"
2. "<computer_name>\<instance_name>" for a named instance
3. "(local)"
4. "(local)\<instance_name>" for a named instance
5. "Localhost"
6. "localhost\<instance_name>" for a named instance
7. A single period "."
8. ".\<instance_name>" for a named instance
A local connection may use another protocol under the following
circumstances:
1. Connect to a client alias that specifies a protocol.
2. Prefix the computer name with the protocol (for example,
"np:<computer_name>" or "tcp:<computer_name>").
3. Connect to the IP address which results in a TCP/IP connection.
4. Connect to the fully qualified domain name (FQDN) which results in a
TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
You can run the following statement to check the current protocol for the
local connections:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
For more information, you may refer to:
Default Client Connection Behavior
http://msdn2.microsoft.com/en-us/library/ms190204.aspx
One interesting thing is that for the forms of <computer_name> and
<computer_name>\<instance_name>, they do not always use Shared Memory
protocol. They just by default use Shared Memory protocol.
Actually in registry there is a value that records which protocol is used
in the latest connection. For the next time local connection, it will use
the latest protocol.
For example:
--This results in Shared Memory connection
sqlcmd /SCharlesXP /E
--This results in Named Pipes connection
sqlcmd /Snp:CharlesXP /E
--This keeps using Named Pipes connection
sqlcmd /SCharlesXP /E
--This results in Shared Memory connection
sqlcmd /Slpc:CharlesXP /E
--This keeps using Shared Memory connection
sqlcmd /SCharlesXP /E
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles;
Have you tried it on SQL2005 SP2 running on XP? I tried on two such
instances. Even with Named Pipes disabled, the following query:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
still returns Named Pipes for many of the server name settings in your first
group, which is supposed to use Shared Memory.
On W2K3 servers, I didn't see this behavior.
Linchi
"Charles Wang[MSFT]" wrote:

> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hi Linchi,
Did you restart your SQL Server service after you disable Named Pipes
protocol on your server by using SQL Server Configuration Manager? or did
you just disable the Named Pipes in SQL Native Client Configuration on your
computer? Per my test, it is no use of disabling Named Pipes on client;
but Shared Memory will be used if you disable the Named Pipes protocol on
server.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles,
Thank you very much for the detailed response.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:kdagNzOnHHA.5168@.TK2MSFTNGHUB02.phx.gbl...
> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
>
|||Positive, I did restart the SQL Server service. In fact, I just tried a few
more times to make sure I wasn't hallucinating.
Linchi
"Charles Wang[MSFT]" wrote:

> Hi Linchi,
> Did you restart your SQL Server service after you disable Named Pipes
> protocol on your server by using SQL Server Configuration Manager? or did
> you just disable the Named Pipes in SQL Native Client Configuration on your
> computer? Per my test, it is no use of disabling Named Pipes on client;
> but Shared Memory will be used if you disable the Named Pipes protocol on
> server.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
>
>
|||Hi Linchi,
It is strange. I have checked that on my computer and I could not reproduce
your issue. My OS is also Windows XP. I also checked that on a Windows 2K
x64 computer. It seemed that the Named Pipes was not disabled on your SQL
Server. Could you please check your SQL Server error logs to see if the
Named Pipes protocol was not disabled during the service startup? or you
may check if you can reproduce your issue on another computer.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Connection: "(local)" vs "ComputerName"

Hi,
I was reading in a book about SQL 2005 about how connections are made to SQL
2005. Assume I have a SQL Server instance named SQL1 and I have a client app
installed on SQL1. The book claimed that if the app's connection string
sets the datasource to "(local)" then the connection uses Shared Memory, and
if it set the datasource to the computer's name, "SQL1", that it uses the
TCP/IP stack which is slower than Shared Memory.
Reading BOL for SQL 2000, it seems to indicate that a local client will
always use Shared Memory, regardless of how the datasource is specified.
Can someone provide a definitive clarification and indicate whether it is
different for SQL 2000 and SQL 2005?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel,
I am afraid that the book description is not correct. By default any
connection with the following names from local computer will use Shared
Memory protocol:
1. "<computer_name>"
2. "<computer_name>\<instance_name>" for a named instance
3. "(local)"
4. "(local)\<instance_name>" for a named instance
5. "Localhost"
6. "localhost\<instance_name>" for a named instance
7. A single period "."
8. ".\<instance_name>" for a named instance
A local connection may use another protocol under the following
circumstances:
1. Connect to a client alias that specifies a protocol.
2. Prefix the computer name with the protocol (for example,
"np:<computer_name>" or "tcp:<computer_name>").
3. Connect to the IP address which results in a TCP/IP connection.
4. Connect to the fully qualified domain name (FQDN) which results in a
TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
You can run the following statement to check the current protocol for the
local connections:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
For more information, you may refer to:
Default Client Connection Behavior
http://msdn2.microsoft.com/en-us/library/ms190204.aspx
One interesting thing is that for the forms of <computer_name> and
<computer_name>\<instance_name>, they do not always use Shared Memory
protocol. They just by default use Shared Memory protocol.
Actually in registry there is a value that records which protocol is used
in the latest connection. For the next time local connection, it will use
the latest protocol.
For example:
--This results in Shared Memory connection
sqlcmd /SCharlesXP /E
--This results in Named Pipes connection
sqlcmd /Snp:CharlesXP /E
--This keeps using Named Pipes connection
sqlcmd /SCharlesXP /E
--This results in Shared Memory connection
sqlcmd /Slpc:CharlesXP /E
--This keeps using Shared Memory connection
sqlcmd /SCharlesXP /E
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles;
Have you tried it on SQL2005 SP2 running on XP? I tried on two such
instances. Even with Named Pipes disabled, the following query:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
still returns Named Pipes for many of the server name settings in your first
group, which is supposed to use Shared Memory.
On W2K3 servers, I didn't see this behavior.
Linchi
"Charles Wang[MSFT]" wrote:

> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly
.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>|||Hi Linchi,
Did you restart your SQL Server service after you disable Named Pipes
protocol on your server by using SQL Server Configuration Manager? or did
you just disable the Named Pipes in SQL Native Client Configuration on your
computer? Per my test, it is no use of disabling Named Pipes on client;
but Shared Memory will be used if you disable the Named Pipes protocol on
server.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
Thank you very much for the detailed response.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:kdagNzOnHHA.5168@.TK2MSFTNGHUB02.phx.gbl...
> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>|||Positive, I did restart the SQL Server service. In fact, I just tried a few
more times to make sure I wasn't hallucinating.
Linchi
"Charles Wang[MSFT]" wrote:

> Hi Linchi,
> Did you restart your SQL Server service after you disable Named Pipes
> protocol on your server by using SQL Server Configuration Manager? or did
> you just disable the Named Pipes in SQL Native Client Configuration on you
r
> computer? Per my test, it is no use of disabling Named Pipes on client;
> but Shared Memory will be used if you disable the Named Pipes protocol on
> server.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly
.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>
>
>
>
>|||Hi Linchi,
It is strange. I have checked that on my computer and I could not reproduce
your issue. My OS is also Windows XP. I also checked that on a Windows 2K
x64 computer. It seemed that the Named Pipes was not disabled on your SQL
Server. Could you please check your SQL Server error logs to see if the
Named Pipes protocol was not disabled during the service startup? or you
may check if you can reproduce your issue on another computer.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Connection: "(local)" vs "ComputerName"

Hi,
I was reading in a book about SQL 2005 about how connections are made to SQL
2005. Assume I have a SQL Server instance named SQL1 and I have a client app
installed on SQL1. The book claimed that if the app's connection string
sets the datasource to "(local)" then the connection uses Shared Memory, and
if it set the datasource to the computer's name, "SQL1", that it uses the
TCP/IP stack which is slower than Shared Memory.
Reading BOL for SQL 2000, it seems to indicate that a local client will
always use Shared Memory, regardless of how the datasource is specified.
Can someone provide a definitive clarification and indicate whether it is
different for SQL 2000 and SQL 2005?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHi Daniel,
I am afraid that the book description is not correct. By default any
connection with the following names from local computer will use Shared
Memory protocol:
1. "<computer_name>"
2. "<computer_name>\<instance_name>" for a named instance
3. "(local)"
4. "(local)\<instance_name>" for a named instance
5. "Localhost"
6. "localhost\<instance_name>" for a named instance
7. A single period "."
8. ".\<instance_name>" for a named instance
A local connection may use another protocol under the following
circumstances:
1. Connect to a client alias that specifies a protocol.
2. Prefix the computer name with the protocol (for example,
"np:<computer_name>" or "tcp:<computer_name>").
3. Connect to the IP address which results in a TCP/IP connection.
4. Connect to the fully qualified domain name (FQDN) which results in a
TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
You can run the following statement to check the current protocol for the
local connections:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
For more information, you may refer to:
Default Client Connection Behavior
http://msdn2.microsoft.com/en-us/library/ms190204.aspx
One interesting thing is that for the forms of <computer_name> and
<computer_name>\<instance_name>, they do not always use Shared Memory
protocol. They just by default use Shared Memory protocol.
Actually in registry there is a value that records which protocol is used
in the latest connection. For the next time local connection, it will use
the latest protocol.
For example:
--This results in Shared Memory connection
sqlcmd /SCharlesXP /E
--This results in Named Pipes connection
sqlcmd /Snp:CharlesXP /E
--This keeps using Named Pipes connection
sqlcmd /SCharlesXP /E
--This results in Shared Memory connection
sqlcmd /Slpc:CharlesXP /E
--This keeps using Shared Memory connection
sqlcmd /SCharlesXP /E
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles;
Have you tried it on SQL2005 SP2 running on XP? I tried on two such
instances. Even with Named Pipes disabled, the following query:
SELECT net_transport
FROM sys.dm_exec_connections
WHERE session_id = @.@.SPID;
still returns Named Pipes for many of the server name settings in your first
group, which is supposed to use Shared Memory.
On W2K3 servers, I didn't see this behavior.
Linchi
"Charles Wang[MSFT]" wrote:
> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||Hi Linchi,
Did you restart your SQL Server service after you disable Named Pipes
protocol on your server by using SQL Server Configuration Manager? or did
you just disable the Named Pipes in SQL Native Client Configuration on your
computer? Per my test, it is no use of disabling Named Pipes on client;
but Shared Memory will be used if you disable the Named Pipes protocol on
server.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles,
Thank you very much for the detailed response.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:kdagNzOnHHA.5168@.TK2MSFTNGHUB02.phx.gbl...
> Hi Daniel,
> I am afraid that the book description is not correct. By default any
> connection with the following names from local computer will use Shared
> Memory protocol:
> 1. "<computer_name>"
> 2. "<computer_name>\<instance_name>" for a named instance
> 3. "(local)"
> 4. "(local)\<instance_name>" for a named instance
> 5. "Localhost"
> 6. "localhost\<instance_name>" for a named instance
> 7. A single period "."
> 8. ".\<instance_name>" for a named instance
> A local connection may use another protocol under the following
> circumstances:
> 1. Connect to a client alias that specifies a protocol.
> 2. Prefix the computer name with the protocol (for example,
> "np:<computer_name>" or "tcp:<computer_name>").
> 3. Connect to the IP address which results in a TCP/IP connection.
> 4. Connect to the fully qualified domain name (FQDN) which results in a
> TCP/IP connection (for example, "<computer_name>.<domain_name>.com"
> You can run the following statement to check the current protocol for the
> local connections:
> SELECT net_transport
> FROM sys.dm_exec_connections
> WHERE session_id = @.@.SPID;
> For more information, you may refer to:
> Default Client Connection Behavior
> http://msdn2.microsoft.com/en-us/library/ms190204.aspx
> One interesting thing is that for the forms of <computer_name> and
> <computer_name>\<instance_name>, they do not always use Shared Memory
> protocol. They just by default use Shared Memory protocol.
> Actually in registry there is a value that records which protocol is used
> in the latest connection. For the next time local connection, it will use
> the latest protocol.
> For example:
> --This results in Shared Memory connection
> sqlcmd /SCharlesXP /E
> --This results in Named Pipes connection
> sqlcmd /Snp:CharlesXP /E
> --This keeps using Named Pipes connection
> sqlcmd /SCharlesXP /E
> --This results in Shared Memory connection
> sqlcmd /Slpc:CharlesXP /E
> --This keeps using Shared Memory connection
> sqlcmd /SCharlesXP /E
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>|||Positive, I did restart the SQL Server service. In fact, I just tried a few
more times to make sure I wasn't hallucinating.
Linchi
"Charles Wang[MSFT]" wrote:
> Hi Linchi,
> Did you restart your SQL Server service after you disable Named Pipes
> protocol on your server by using SQL Server Configuration Manager? or did
> you just disable the Named Pipes in SQL Native Client Configuration on your
> computer? Per my test, it is no use of disabling Named Pipes on client;
> but Shared Memory will be used if you disable the Named Pipes protocol on
> server.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>
>
>
>|||Hi Linchi,
It is strange. I have checked that on my computer and I could not reproduce
your issue. My OS is also Windows XP. I also checked that on a Windows 2K
x64 computer. It seemed that the Named Pipes was not disabled on your SQL
Server. Could you please check your SQL Server error logs to see if the
Named Pipes protocol was not disabled during the service startup? or you
may check if you can reproduce your issue on another computer.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Connection to an alias instead of a named server?

Is this doable?
The server name is TOPAZ
The alias name is topaz-alias
The username is somesqldb
The password is password
The following connection statement works:
Connection conn_topaz_byserver =
DriverManager.getConnection
("jdbc:microsoft:sqlserver://topaz;databasename=somesqldb"
,"somesqldb", "password");
The following connection statement fails:
Connection conn_topaz_byalias =
DriverManager.getConnection
("jdbc:microsoft:sqlserver://topaz-alias;databasename=somesqldb"
,"somesqldb", "password");
Galen Boyer
Hi Galen. The driver is trying to open a raw socket
to a machine named whatever you put in the URL
after the '//' and before the ';'. If you can
set up your DNI to find topaz from the alias,
then it will work.
Here's a program that opens a socket like the
typical type-4 driver will do, given the inputs
the URL and properties will supply. If you can
configure your OS dni info to get this program to
succeed opening the socket using the alias you want,
then the driver will too.
Joe Weinstein at BEA
import java.io.*;
import java.net.*;
public class isAnythingListeningOn
{
public static void main(String argv[])
throws Exception
{
if (argv.length != 2)
{
System.out.println("Usage: isAnythingListeningOn <host> <port>");
System.out.println("eg:\n% java isAnythingListeningOn myMachine 1433");
System.exit(0);
}
try
{
System.out.println("\nTrying to open a socket with host "
+ argv[0] + " and port " + argv[1] + " ...");
Socket socket = new Socket(argv[0],(new Integer(argv[1]).intValue()));
System.out.println("\nYes, there is, we got a socket.");
socket.close();
}
catch (Exception e)
{
System.out.println("We failed to open a socket. Here's why:\n");
e.printStackTrace();
System.out.println("\n(Either there is no machine named '" + argv[0]
+ "' or\nnothing is listening there on port "
+ argv[1] +")\n");
}
}
}
galenboyerdev@.hotpop.com wrote:

> Is this doable?
> The server name is TOPAZ
> The alias name is topaz-alias
> The username is somesqldb
> The password is password
> The following connection statement works:
> Connection conn_topaz_byserver =
> DriverManager.getConnection
> ("jdbc:microsoft:sqlserver://topaz;databasename=somesqldb"
> ,"somesqldb", "password");
>
> The following connection statement fails:
> Connection conn_topaz_byalias =
> DriverManager.getConnection
> ("jdbc:microsoft:sqlserver://topaz-alias;databasename=somesqldb"
> ,"somesqldb", "password");
>
|||Joe Weinstein <joeNOSPAM@.bea.com> writes:

> Hi Galen. The driver is trying to open a raw socket
> to a machine named whatever you put in the URL
> after the '//' and before the ';'. If you can
> set up your DNI to find topaz from the alias,
> then it will work.
[...]

> Socket socket = new Socket(argv[0],(new Integer(argv[1]).intValue()));
The following worked:
java.net.Socket socket = new java.net.Socket("topaz",1433);
The following failed:
java.net.Socket socket = new java.net.Socket("topaz-alias",1433);
So, the alias seems to be a "microsoft toolset only" feature.
Thanks.
Galen Boyer

Connection String with Named Instance and Port?

I am trying to create a SQL Server 2000 and 2005-friendly connection
string that will allow me to connect to a named instance on a
non-standard port. I can't get the syntax right, can anyone show an
example?
Thanks.
For data source, use YourServer\YourNamedInstance then a
comma followed by the port number - e.g.
Data Source=YourServer\NamedInstance,1433;
-Sue
On 23 Mar 2006 06:38:16 -0800, bradwiseathome@.hotmail.com
wrote:

>I am trying to create a SQL Server 2000 and 2005-friendly connection
>string that will allow me to connect to a named instance on a
>non-standard port. I can't get the syntax right, can anyone show an
>example?
>Thanks.

Connection String with Named Instance and Port?

I am trying to create a SQL Server 2000 and 2005-friendly connection
string that will allow me to connect to a named instance on a
non-standard port. I can't get the syntax right, can anyone show an
example?
Thanks.For data source, use YourServer\YourNamedInstance then a
comma followed by the port number - e.g.
Data Source=YourServer\NamedInstance,1433;
-Sue
On 23 Mar 2006 06:38:16 -0800, bradwiseathome@.hotmail.com
wrote:
>I am trying to create a SQL Server 2000 and 2005-friendly connection
>string that will allow me to connect to a named instance on a
>non-standard port. I can't get the syntax right, can anyone show an
>example?
>Thanks.

Connection String with Named Instance and Port?

I am trying to create a SQL Server 2000 and 2005-friendly connection
string that will allow me to connect to a named instance on a
non-standard port. I can't get the syntax right, can anyone show an
example?
Thanks.For data source, use YourServer\YourNamedInstance then a
comma followed by the port number - e.g.
Data Source=YourServer\NamedInstance,1433;
-Sue
On 23 Mar 2006 06:38:16 -0800, bradwiseathome@.hotmail.com
wrote:

>I am trying to create a SQL Server 2000 and 2005-friendly connection
>string that will allow me to connect to a named instance on a
>non-standard port. I can't get the syntax right, can anyone show an
>example?
>Thanks.

Connection String has Semicolon (;) - how on earth can I get this working?

Ok, here's my setup. I've got a named instance in a SQL 2000 cluster. I only have dbo rights on my database, because it is a shared infrastructure. Here's my current web.config connection string (the meat, anyway):

When I'm at the office, this is my connection string, pretty normal:

connectionString="Data Source=ServerName\InstanceName;Initial Catalog=blah..."

But, when I connect through the VPN, I can't just connect through the named instance - I have a specific port. This is where things get odd.

First, if I try to connect through SQL Server Management Studio (2005), i get nothing. If I try to connect using"ServerName\InstanceName, (comma) Port Number" it connects, but not to my instance. I get a seperate set of databases that I believe are in the default instance. So, I changed the comma to a semicolon (;) - and it still connected to the same thing - connected to the database, but to the wrong set of databases. So, on a whim, I tried plunking my string, which was now"ServerName\InstanceName;(semicolon) PortNumber" into the SQL 2000 Tools and it worked in both Query Analyzer and in Enterprise Manager. So, I thought, I'll just slam this into my connection string and all will be well. No. I can't use a semicolon in my connection string, and I can't find an escape character to use. Double semicolons don't work, a comma doesn't connect me properly, double colons don't work, the JDBC brackets don't work {} - so I'm at a loss. I'm out of ideas. I've set up aliases, and those don't work earlier.

I'm using ASP.net 2 with VB & C# and Visual Studio 2005 Professional. Thanks for any help anyone can give on this!

Have you tried with a Colon instead of a semicolon?

|||

I have - no dice. I've tried a colon after the servername but before the instance name (i.e.ServerName:Port\InstanceName) as well as at the end of the entire name (ServerName\InstanceName:Port).

|||

Hi jdandison,

The standard format to conifgure your port number in connection string is:string connectionString = "Server=Servername\InstanceName;port=your port number;"

So, try to change your connection string to :connectionString="Data Source=ServerName\InstanceName;port=NewPortNumber;Initial Catalog=blah..."and try again.

Tell us if you have any further questions. thanks

Connection string for default instance of SQL Server 2005, on a box also running a named i

The box I am trying to connect to is running two instances of SQL Server.

There is a SQL Server 2005 instance which is the default. There is a SQL Server 2000 instance which is named 'SQLSERVER'.

I can connect to the SQL Server 2000 instance no problem:

<addkey="ConnectionString"value="server=MYPC\SQLSERVER;database=mydatabase;user id=****;password=****"/>

However, I am having trouble connecting to the Default SQL Server 2005 instance. I have tried:

<addkey="ConnectionString"value="server=MYPC;database=mydatabase;user id=****;password=****"/>

but it doesn't work. I have tried explicitly setting SQL Server 2005 to use port 1434 (as SQL Server 2000 is running on port 1433), and then used:

<addkey="ConnectionString"value="server=MYPC,1434;database=mydatabase;user id=****;password=****"/>

but this doesn't work either.

Am I mssing something here? Any help much appreciated

Thanks...

Since both SQL Server 2000 and 2005 are running on the same machine, definitely one of the instance will be the named instance.

I guess SQL 2005 is a named instance. So the connection should be as follows:

server=MYPC\<Instance name>;database=mydatabase;user id=****;password=****

|||

Hi, thanks for the reply.

No, SQL Server 2000 was the named instance. SQL Server 2005 was the default. It was the default instance (SQL Server 2005) that I was having problems connecting to. Ineed, I was having trouble connecting to it with anything other than Enterprise Manager.

However, I have uninstalled / reinstalled SQL Server 2000 and SQL Server 2005, and made them both named instances. I can now connect to them both no problem.

I suspect that had I reinstalled with one or other of them being a default instance, I also would have had no problems. I had noticed a number of other strange behavioural issues in SQL Server 2005 and I suspect something had gone awry in general.

Connection Problems

Hello,
I have a small workgroup of computers (4) all running windows xp sp2. One
of them is running msde2000. I can connect to a named instance of sql server
but can not connect to the default instance. I am using osql and using
trusted connection. I am not sure what other information is needed so
please do not hesitate to ask! I am really pulling my hair out on this one!
Any help would be greatly appreciated.
Hello,
Did you activated TCPIP network protocol for this default instance (verify
in the error.log for the instance)?
Hope this will help.
Philippe RUELLO
DBA MSSQL
"Michael O'S" <mroresolutions@.woh.rr.com> a crit dans le message de news:
NhMxd.3864$mA3.1811@.fe2.columbus.rr.com...
> Hello,
> I have a small workgroup of computers (4) all running windows xp sp2. One
> of them is running msde2000. I can connect to a named instance of sql
> server but can not connect to the default instance. I am using osql and
> using trusted connection. I am not sure what other information is needed
> so please do not hesitate to ask! I am really pulling my hair out on this
> one!
> Any help would be greatly appreciated.
>
|||Are you sure there is a default instance? If it's just MSDE 2000, there
usually is not a default instance. It installs to a named instance by
default. Look in Administrative Tools/Services for all SQL Server services.
See if you have any running besides the named instance.
"Philippe RUELLO" <pruello@.tibco.fr> wrote in message
news:uReN1wF7EHA.2124@.TK2MSFTNGP15.phx.gbl...
> Hello,
> Did you activated TCPIP network protocol for this default instance
(verify[vbcol=seagreen]
> in the error.log for the instance)?
> Hope this will help.
> --
> Philippe RUELLO
> DBA MSSQL
> "Michael O'S" <mroresolutions@.woh.rr.com> a crit dans le message de news:
> NhMxd.3864$mA3.1811@.fe2.columbus.rr.com...
One[vbcol=seagreen]
needed[vbcol=seagreen]
this
>
|||Thank you for your input! I did check tcpip settings and all were good. I
did however find in the server logs that some intances (including the
default) were not listening or failing to bind to port 1433.
I ended up removing msde instance that I could not connect to and
reinstalling making sure to use the DISABLENETWORKPROTOCOLS=0 parameter.
After I did this, I could see all instances of msde on any computer from any
computer in my workgroup.
Good Luck!
"Michael O'S" wrote:

> Hello,
> I have a small workgroup of computers (4) all running windows xp sp2. One
> of them is running msde2000. I can connect to a named instance of sql server
> but can not connect to the default instance. I am using osql and using
> trusted connection. I am not sure what other information is needed so
> please do not hesitate to ask! I am really pulling my hair out on this one!
> Any help would be greatly appreciated.
>
>