How to determine LocalDb instance name in SQL script?

Question

For a non-LocalDb SQL Server, I can SELECT SERVERPROPERTY('ServerName') to get the name of the server and the instance that I'm running on. For a LocalDb server, however, I get SERVERNAME\LOCALDB#SOMEHASH. How can I get the local instance name?

SOLUTION:

The solution for getting the instance, working on both LocalDB and "normal" SQL Server instances:

DECLARE @serverName NVARCHAR(151)
IF SERVERPROPERTY('IsLocalDb') = 1
    SELECT @serverName = 'np:\\.\pipe\' + CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceName')) + '\tsql\query'
ELSE
    SELECT @serverName = CONVERT(NVARCHAR(128), SERVERPROPERTY('ServerName'))

Some background that I should have mentioned in the original question: We have a configuration database for our application. Among other things, it stores connection strings. For developer machines and integration tests, we want to be able to generate a database with a script, and have the connection string refer to the local instance. Thus, I need to have a working connection string (which LOCALDB#SOMEHASH is not). Since there is some spread in edition of database server, I needed something that could handle both cases.

@RaySaltrelli: Because it cannot be used to connect to the localdb from code in my tests — carlpett, Feb 28 '13 at 16:23
@granadaCoder: It is the same as `SERVERPROPERTY('ServerName')` — carlpett, Feb 28 '13 at 16:35
Sorry. I think I goofed. Can you run a Select @@VERSION and post? — granadaCoder, Feb 28 '13 at 16:39
does it have to be dynamic? or is your server/instance name static? — RandomUs1r, Feb 28 '13 at 16:39
I needed a solution for running bcp from a stored proc. I'm building the stored proc on my localdb and will deploy to sql server 2016. But I need to deploy via automated script. So I cannot edit the proc beforehand. The @@servername on localdb returns a name that bcp does not accept. But, the above solution worked for me in dev and qa/prod. Thanks! — Ken Hadden, Jul 22 '20 at 17:36

shibormot · Accepted Answer · 2013-02-28T16:45:42.863

4

Is this:

SELECT SERVERPROPERTY ('InstanceName')

EDIT

from link

Connecting to a Shared Instance of LocalDB

To connect to a shared instance of LocalDB add .\ (dot + backslash) to the connection string to reference the namespace reserved for shared instances. For example, to connect to a shared instance of LocalDB named AppData use a connection string such as (localdb).\AppData as part of the connection string. A user connecting to a shared instance of LocalDB that they do not own must have a Windows Authentication or SQL Server Authentication login.

and

If your application uses a version of .NET before 4.0.2 you must connect directly to the named pipe of the LocalDB. The Instance pipe name value is the named pipe that the instance of LocalDB is listening on. The portion of the Instance pipe name after LOCALDB# will change each time the instance of LocalDB is started. To connect to the instance of LocalDB by using SQL Server Management Studio, type the Instance pipe name in the Server name box of the Connect to Database Engine dialog box. From your custom program you can establish connection to the instance of LocalDB using a connection string similar to SqlConnection conn = new SqlConnection(@"Server=np:\\.\pipe\LOCALDB#F365A78E\tsql\query");

edited Feb 28 '13 at 16:45

answered Feb 28 '13 at 16:21

shibormot

1,638
2
12
23

That returns the `LOCALDB#SOMEHASH` part, unfortunately. – carlpett Feb 28 '13 at 16:23
are you familiar with [this](http://msdn.microsoft.com/en-us/library/hh510202.aspx) – shibormot Feb 28 '13 at 16:32
Yes, and thus my question. In my quick test at least, I could not connect to `(localdb)\LOCALDB#SOMEHASH`. Should I expect to be able to? – carlpett Feb 28 '13 at 16:34
@carlpett and one more time) – shibormot Feb 28 '13 at 16:46
1

Excellent! Wasn't aware of being able to use the named pipe in a connection string. Updated question with solution – carlpett Mar 01 '13 at 06:43

How to determine LocalDb instance name in SQL script?

1 Answers1