Jon Galloway

Also remember that SQL2005 now turns a lot of things off by default. For example Developer Edition install closes TCP connections until you open them with the surface area configuration tool.

> This isn't always practical in a group development situation where each developer is running a SQL Server instance since each machine will have a unique name

Jon -

Good post...

I usually end up with something along these lines but it leaves me feeling uncomfortable in the event that I need to move the application to a new machine. What do you do?

if (Server.Environment.MachineName == "COMPY386")
{
//use this --> System.Configuration.ConfigurationSettings.AppSettings["PRODUCTION_RDBMS"];
}
else
{
// use this --> System.Configuration.ConfigurationSettings.AppSettings["DEV_RDBMS"];
}

Good post! Would there be much of a performance difference between using each option?

Also, keep in mind, there is a performance hit for using named pipes. Named pipes are backed by disk, and too much named pipes usage may cause disk i/o to spike. Combine this with the fact that database servers by nature are disk i/o intensive, it may be better to offload the communication activity to network i/o through the use of TCP.

This was the advice of the SQL Server support team, after identifying perf issues on one of our SQL Servers.

I've always wondered this but was too lazy to ask.

I always thought (local) uses "shared memory" not named pipes. I always turn of named pipes + tcp for my development hosts, and everything works fine.

I also think it is a myth, that named pipes caus disk I/O as said in a comment. Just because something uses a file-like interface does not necessarily mean it accesses the disk - only scsi/ide and other DISK drivers access the disk.

Just a comment to Vic:

One nice way around this is to use the <appSettings file=""> functionality in app.config files. This way you can have a config file for each dev and production machine and not actually have to change your config file itself, or the code using it.

Also, if file="" is used, the app settings in the included file override the same named settings in the app.config file.

Very useful!

Have a look at the following blog entry:
http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

It says that when using MDAC and OLEDB, the "(local)" and "localhost" use TCP connection, but <machinename> use a shared memory connection.

for all connection string you have found here on this site

http://www.connstr.com

Another month. Another collection of links... Sports/Packers/Racing Turiaf will play for Yakama Sun Kings

You are a life saver.. computer nave over local or localhost is exactly what I needed to know.

is there a difference in using server=local and server=(local) ?

Tip: Put Connection Strings in Their Own Configuration File

Critical Error: SiteUrls.Config The file containing the SiteUrl Data could not be loaded. Please contact

Where does using "." fit into the equation? Is it the same as local or localhost?

All I know is that localhost uses TCPIP while (local) and . use a protocol called shared memory and does not use any network protocols.  Am I right on this?

Hi,

sqlDataAdapter wizzrd only connecting one database from sql server 2005 in my pc. Why not other databeses. I have no password or user id.

Is there any default setting that I have to change?

I will appreciate if I have info.

thanks,

Gita

want to know, how to connect to a sql server from serversystem to anther system which is in lan with server but has no sqlserver installed before.

plz send me the solution in detail

Quote:"All I know is that localhost uses TCPIP while (local) and . use a protocol called shared memory and does not use any network protocols. "

We use the "." connection to connect to local SQL, but if the customer connects the computer to a Network and then physically disconnects from the network our program encounters SQL Network errors. We cannot hard code the computer name and really just want to use Named Pipes or Shared Memory. Is there way to specify Named Pipes?

I found this on the MSDN site:

"By default, clients have TCP and Named Pipes as available protocols. You can manipulate the protocol ordering by using the SQL Server Client utility. The client application uses the protocols in the order specified on the client computer. The protocol order is stored at the following registry key location under the value ProtocolOrder: HKLM\Software\Microsoft\MSSQLServer\Client\SuperSocketNetLib

For example, if a client computer has both TCP and Named Pipes available, and the order is:

• TCP

• Named Pipes "

The reg key for SQL Express 2005 is a little different but I found 'SuperSocketNetLib' key and changed the order to NP, TCP. But our program still encounters a network error accessing the local SQL when the network cable is unplugged.

Any suggestions?

Pingback from  Alkampfer&#8217;s Place  &raquo; Blog Archives   &raquo; Really strange error in production server, a tale of named pipes