Scott Hanselman's Computer Zen - Fixing Instance Failure when connecting to SQL Server 2005 Express

First time here? Check out the site's "greatest hits" or read a post from the archives. Feel free to leave a comment or ask a question, and consider subscribing to the latest posts via RSS or e-mail. Thanks for visiting!

« The VB equivalent to C# typeof() keyword... | Main | Improving LINQ Code Smell with Explicit ... »

Fixing Instance Failure when connecting to SQL Server 2005 Express

Posted in ASP.NET | Learning .NET | Programming.

I was getting Instance Failure when connecting to my SQL Server Express 2005 Database in my C# ASP.NET application. Very weird.

Instance failure. - Windows Internet Explorer

<connectionStrings>
    <add name="NorthwindConnString" 
         connectionString="Data Source=.\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True" 
         providerName="System.Data.SqlClient"/>
</connectionStrings>

Trial and error just taught me that the problem was "Data Source=.\\SQLEXPRESS;" - It's the double \\. That's an escape sequence in C#. Everything worked when I switched the connection string to .\SQLEXPRESS.

Posted at 2007-08-30 06:08 PM by Scott
Comments [8]
Permalink

Thursday, August 30, 2007 6:34:37 PM (Pacific Standard Time, UTC-08:00)

That was probably the cause of the problem, but I'm not sure it's related to \\ being an escape sequence in C#. If it were being taken as C# code, wouldn't ".\\SQLEXPRESS" translate to ".\SQLEXPRESS" and ".\SQLEXPRESS" translate to ".SQLEXPRESS"? I don't think C# escape sequences apply to strings in a configuration file.

Jose Lamas Rios

Thursday, August 30, 2007 8:50:16 PM (Pacific Standard Time, UTC-08:00)

Jose, I'm starting to think you're right, but interesting it works with \\ under VB...Hm...what is going on?

Scott Hanselman

Thursday, August 30, 2007 8:50:19 PM (Pacific Standard Time, UTC-08:00)

The config file is an XML file which has nothing to do with C#. Thus in XML the attribute connection string will still be .\\SQLEXPRESS and is invalid.

Brett

Thursday, August 30, 2007 8:51:20 PM (Pacific Standard Time, UTC-08:00)

What happens is : the string is read in c# and is escaped so \\ becomes \\\\. And I know that the hard way :((.
Took 2 hours once to find the stupid thing.

sirrocco

Thursday, August 30, 2007 9:11:07 PM (Pacific Standard Time, UTC-08:00)

Brett: Ah, so you're saying it was always wrong?

Sirrocco: See, that makes more sense to me.

Scott Hanselman

Thursday, August 30, 2007 9:40:42 PM (Pacific Standard Time, UTC-08:00)

Yes, in a .config file or XML file it is always wrong and as Sirrocco said the .\\SQLEXPRESS would be read in as .\\\\SQLEXPRESS. However, if you had hard coded the connection string into C# code, in order for C# to interpret it, you would have had to do some thing like:
string connectionString = "Data Sorce=.\\SQLExpress";

What I am basically getting to, the config file (aka XML) is read in using an XML parser where \ is not a escape character thus will be treated litterally. i.e. \ = \.

In the C# source code (note I said source code) above, the C# parser will treat \ as an escape character, thus \\ is read in as \.

To answer your question, yes for a config file it is always wrong as it is XML. However for C# source code it is correct.

Brett

Friday, August 31, 2007 4:11:19 AM (Pacific Standard Time, UTC-08:00)

It alos seems like the ADO.NET API could give you a better exception message than "instance failure".

Kris

Friday, October 12, 2007 7:01:52 AM (Pacific Standard Time, UTC-08:00)

Thank you! You saved me from a bad headache. :) I had copy-pasted the code from a C# area of my program and had forgotten that the double backslash was for the benefit of C#'s string escapes and that XML didn't need it. Thank you!!!

Utah Custom Software Consulting

Jordan Brough

Comments are closed.