none
MSSQL connection string

    Question

  • Hi,

    I use the following string to connect to MSSQL 2005 server (not express).

    server=companysqlserver;database=companydatabase;user id=company;password=data

    It works fine.

    When I need to connect to a MSSQL2005 express with the machine and instance name:
    COMPANYSQLSERVER\SQLEXPRESS

    server=COMPANYSQLSERVER\SQLEXPRESS;database=companydatabase;user id=company;password=data

    It cannot be connected.

    I also tried
    server=COMPANYSQLSERVER\\SQLEXPRESS;database=companydatabase;user id=company;password=data

    and

    server=.\SQLEXPRESS;database=companydatabase;user id=company;password=data


    I use VC# and ASP.NET

     

    The following is the code to throw exception and it said "Thread Terminated"

    I did not see this error before.

    Code Snippet
    try{this.Add();}catch (Exception ex){Response.Redirect("error.aspx?error1=" + ex.Message, true);} private void Add()   {   try   {   SqlHelper.ExecuteNonQuery(DBConnection.ConnString, CommandType.Text, this.SQL());   }   catch   {   Response.Redirect("dberror.htm");   }   finally   {   }   }   

     

     

    Thursday, October 16, 2008 8:59 AM

Answers

  • If you're getting the error that you can't locate the instance, it must be either that you're using the wrong name or that something is not configured correctly on the server to enable remote connections. I've never seen a case of this error that wasn't related to one of those two causes.

     

    Take a look at http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277 for the steps to enable remote connections. Even though you've already done these steps, do them again just to be sure. As I've said, every time I see this error, the cause is that one of these steps was missed without realizing it.

     

    Thanks,

    Mike

     

    Sunday, October 19, 2008 7:53 PM
    Moderator

All replies

  • I don't know very much about this, but did you try...

      
    Code Snippet

     

    SqlConnectionStringBuilder cnnString = new SqlConnectionStringBuilder();
    cnnString.DataSource = @"NAME-PC\SQLEXPRESS";
    cnnString.InitialCatalog = "NameDataBase";
    cnnString.UserID = "UserID";
    cnnString.Password = "Psword";
    cnnString.IntegratedSecurity = false;
    //conn = new SqlConnection(cnnString.ConnectionString);
    //conn.Open();


      
    The last two lines I commented them because those are just to connect, but in cnnString.ConnectionString you get the connectionString you were looking for and you don't have to bother if it is '\' or '\\' ...

    This worked for me... hope it helps...
    Thursday, October 16, 2008 9:41 AM
  • If you are running Vista, make sure all permissions are set properly for DB files, for your application to access the directory, etc. You must run your app as administrator.

     

    Thursday, October 16, 2008 10:21 PM
  • I run it in windows 2003 server. I guess it is the SQLExpress issue. as the connection seems different from SQL server.

    Friday, October 17, 2008 12:56 AM
  • SQL Express is SQL Server - they are the same and they use the same connection string. If the connection string that works on your non-Express server is failing on the Express server, then you've configured something differently in Express. What is the exact error you're getting?

     

    Here are some things that could be at issue:

    • You are trying to access the Express server on a remote computer but you have not enabled remote connections and created exceptions in the Firewall.
    • You have the wrong instance name.
    • You're using an account that doesn't have permissions either to the Server or to the Database. (The error message will differentiate these.)
    • You've created you database from within Visual Studio and are using the User Instance rather than the parent instance. User Instances only support local connections from a single user.

    Give us a bit more information and the problem will likely become apparent.

     

    Regards,

    Mike

    Friday, October 17, 2008 1:51 AM
    Moderator
  •  

    Hi

    I read

    http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

    and

    http://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-instance-on-a-cluster.aspx

    Set the firewall exception, enable tcp/ip, all those steps were done.

    However, I still cannot use the connectionstring to connect to DB

    even I use aspnet_regsql.exe  and it threw error: 40 - cannot open SQL Server connection

    C:\>osql -E -S cluster\inst

    [SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

    [SQL Native Client]Login timeout expired

    SQL Browser service is running on the server
    Sunday, October 19, 2008 2:12 PM
  • If you're getting the error that you can't locate the instance, it must be either that you're using the wrong name or that something is not configured correctly on the server to enable remote connections. I've never seen a case of this error that wasn't related to one of those two causes.

     

    Take a look at http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277 for the steps to enable remote connections. Even though you've already done these steps, do them again just to be sure. As I've said, every time I see this error, the cause is that one of these steps was missed without realizing it.

     

    Thanks,

    Mike

     

    Sunday, October 19, 2008 7:53 PM
    Moderator
  • Yes, it is what do I search and it can fix it.

     

    Many thanks for help

    Monday, October 20, 2008 6:08 AM