Detect what version of SQL Server Express engine is available before connection?

All we need is an easy explanation of the problem, so here it is.

I would like to detect what version of SQL Server Express engine is available so that I can connect either to the (localdb)\v.11 (SQL Server 2012 per https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/hh510202(v=sql.110)?redirectedfrom=MSDN#Anchor_1) or (localdb)\MSSQLLocalDB (SQL Server 2014 and up per https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb?view=sql-server-ver15&redirectedfrom=MSDN&viewFallbackFrom=sql-server-2014#Anchor_1) instance name when attempting to attach a file in the connection string using the AttachDBFileName= mechanism.

I would most likely want to do it from Powershell in some manner, but whatever method is reliable, I can use. I do know that sometimes a LocalDB connection can be a bit slow as it attaches the file and starts up on demand, so I have in the past been pretty lenient on the connect timeout for these LocalDB connections compared to the real SQL Server connections, so I would prefer not trying to connect and waiting for timeout, since I think I’ve already got an exaggerated timeout just for normal successful connection.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can check the Windows registry for installed versions.

For example, in Powershell you can use this to find all installed versions (note there may be multiple versions installed)

(Get-ChildItem
   -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server Local DB\Installed Versions")
.PSChildName

You would want to check that 12.0 or up was in the results.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply