Results 1 to 9 of 9

Thread: Modifying conection string to connect to multiple installed instances of SQL Server

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2012
    Location
    Las Vegas, NV
    Posts
    41

    Question Modifying conection string to connect to multiple installed instances of SQL Server

    I am struggling to figure out how to connect to multiple instances of SQL Server with my VB.NET application. I have SQL Server 2008 and 2012 installed on my machine. If I try to open an SQL Server 2008 database, it automatically opens it in 2012, updates it, and renders it unusable to SQL Server 2008. Is there a way to modify my connection string to open a 2008 database in SQL Server 2008, and a 2012 database in SQL Server 2012? I have found out how to differentiate the file version already.

    My connection string is as follows:

    Code:
    m_cn.ConnectionString = "Server=.\SQLEXPRESS; AttachDBFilename=" & strFileName & ";Integrated Security = True; Connect Timeout = 30;" & "User Instance = True"
    Thanks

  2. #2
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: Modifying conection string to connect to multiple installed instances of SQL Serv

    Am I missing some great complication here? You have a connection string for each version and you apply it according to your file version detection method with a simple If. If you are correctly opening connections only when needed and closing them immediately after the necessary functions have completed then there will be no problems with this at all.
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  3. #3

    Thread Starter
    Member
    Join Date
    Aug 2012
    Location
    Las Vegas, NV
    Posts
    41

    Re: Modifying conection string to connect to multiple installed instances of SQL Serv

    No, I think the problem is that I am missing something simple here.

    I have attempted to create specific connection strings depending on the version of SQL Server to be used, but I am struggling to figure out the server name to connect to for SQL Server 2008. The ".\SQLEXPRESS" works just fine for the SQL Server 2012 version. I know this is simple to do, I think I am just overlooking what I need to be doing here. Any suggestions?

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Modifying conection string to connect to multiple installed instances of SQL Serv

    Ok - this is really simple

    You have two installs of SQL - and one is called .\SQLEXPRESS - and that one is for the 2012 version.

    What is the instance name of the SQL 2008 version you have installed??

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: Modifying conection string to connect to multiple installed instances of SQL Serv

    Ah right. This is what you get for accepting default installations! You'll need to change the server name for one of the versions so that you can refer to it distinctly in the connection strings. What information I've gleaned suggests that the easiest way to do that is to reinstall and name at the appropriate stage. There is a non-reinstall option suggested here but as my own SQL installation is mush at the moment (I'm waiting for a new computer to do a complete overhaul - it may be rather a long wait unfortunately!) I'm not able to test it..
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  6. #6
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    Re: Modifying conection string to connect to multiple installed instances of SQL Serv

    Quote Originally Posted by szlamany View Post
    Ok - this is really simple

    You have two installs of SQL - and one is called .\SQLEXPRESS - and that one is for the 2012 version.

    What is the instance name of the SQL 2008 version you have installed??
    Er .... .\SQLEXPRESS, I'll warrant!
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Modifying conection string to connect to multiple installed instances of SQL Serv

    I've got three installs of SQL on this laptop - it takes several minutes to boot now - I really could use a new machine with a fresh O/S - wish I had the time (and funds!)...

    Look at that bloat...
    Attached Images Attached Images  

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8

    Thread Starter
    Member
    Join Date
    Aug 2012
    Location
    Las Vegas, NV
    Posts
    41

    Re: Modifying conection string to connect to multiple installed instances of SQL Serv

    Gotcha. I thought that I had this working before, just by changing the "SQLEXPRESS" instance name to the instance name of the SQL Server 2008 installation. I did not realize that accepting a default installation would cause this trouble. The other instance name was what I thought to be MSSQLSERVER, but this does not work. When I log into the SQL Server 2008 program, the Server name is "(local)", and this doesn't seem to work too well in a my connection string.

    I will try to reinstall these versions and enter specific instance names. Is the default instance the (local) or the SQLEXPRESS? Or is this up to me to figure out?

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Modifying conection string to connect to multiple installed instances of SQL Serv

    Try using the MACHINE name - not a "." - to connect to SQL.

    That is the default instance - no name at all. That is what you see as MSSQLSERVER in my attached image.

    \SQLExpress is a named instance - name is \SQLExpress - and I'm sure when you install SQL Express version it defaults to a named instance and said name is \SQLExpress

    As you can see I named one \SQL2012...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width