Accessing SSDT localdb via bcp/SQLCMD ? Is this possible?

Question

Hi

I have a batch script which invokes bcp, to help migrate data from one DB to another and maintaining the dependencies between the tables.

I use DB_NAME() and @@servername to determine the current DB to import to/export from and pass these as variables to SQLCMD command that in turn invokes the bcp commands.

The bcp command is as follows:

bcp db.dbo.table in data.tsv  -T -c -q -v -S server\LOCALDB#7779D34D

because @@servername returns server\LOCALDB#7779D34D as the database identifier.

It seems that the local database is not accessible remotely from the command line.

Is there a means that I can refer to this database so as to connect to it remotely or access it via the bcp utility?

Any help is greatly appreciated.

Regards

Lucas

Answer 1

Hi Lucas,

It is possible to connect to SQL Server remotely via the bcp utility. “-S” option is required when a bcp command is run from a remote computer on the network or a local named instance. For more details, please refer to this article: bcp Utility.

Did you encounter an error when used bcp utility? Please provide the specific error message for further troubleshooting. Besides, for connecting SQL Server remotely, we need to configure the SQL Server to allow remote access. For more details, please refer to the following references:

• http://msdn.microsoft.com/en-us/library/ms179383.aspx
• http://msdn.microsoft.com/en-us/library/ms175483(SQL.105).aspx
• http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

Best Regards,
Ray Chen

Answer 2

Hi Ray,

Many thanks for your response.

My apologies because I failed to point out that I was using SQL Server 2012 Developer edition, with SSDT. I assumed that this would be obvious because I thought that SSDT was only available on SQL Server 2012, but I may be wrong.

I have used the -S option as you noted  in the sample command in my original question where I specify

"-S server\LOCALDB#7779D34D".

One thing important to consider is that I can connect to other servers remotely with the same command successfully.

The issue here is the localDB which is automatically created by SSDT, and I believe specifically for database development and integrating with source code control.

It seems that I am unable to access this database from anywhere other than SSDT.

So the SQL Server Object Explorer in SSDT shows the localDB as follows:

Note that the icon is different to the normal server shown here as WS09990. WS9990 is the server and the instance is SQLSERVER2012.

However, the SQL Server Configuration manager does not show the localDB as an instance or a server; i.e.

Accessing the same bcp command on WS09990\SQLSERVER2012 is always successful, but any attempt to access localDB always fails.

The issue is that I wish to apply the same to the localDB instance in SSDT. So far localDB in SSDT only seems accessible from within SSDT.

The same applys for SQLCMD as follows:

I use "@@servername" to obtain the server instance.

In localDB on SSDT "@@servername" returns "WS09990\LOCALDB#B9CD0A85".

The dynamically compiled command on this server instance is as follows:

SQLCMD -i batch_import.sql -S WS09990\LOCALDB#B9CD0A85 

This raises the following errors.

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is
 configured to allow remote connections. For more information see SQL Server Books Online..
NULL

If I run the same script on WS09990\SQLSERVER2012 which is available on both the SQL Server Management Studio, and SSDT, @@servername returns WS09990\SQLSERVER2012 and the dynamically compiled command on the WS09990\SQLSERVER2012 instance is as follows:

SQLCMD -i batch_import.sql -S WS09990\SQLSERVER2012

This runs successfully.

I originally posted the question on SSDT because it seems that this is only an issue with the localDB which only seems accessible from SSDT.

I also wish to empasise that I am not able to connect to the localDB even from within SSMS.

Any help is greatly appreciated.

Regards

Lucas

Answer 3

Hello Lucas,

The LocalDB feature has a different naming convention for its instances.

You'll need to connect to the instance: (localdb)\iData
As defined in SSDT.

For more info on LocalDB and SQL Server 2012 Express, visit here: http://msdn.microsoft.com/en-us/library/hh510202.aspx

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/

Answer 4

Hi Eitan,

Fantastic! That works great.

I shall read the documentation to find out why (local_DB)\db_name is not returned by @@servername.

Thanks again and much appreciated.

Regards

Lucas

Answer 5

Excellent!

I'm glad I could help.

---

Eitan Blumin; SQL Server Consultant - Madeira SQL Server Services; http://www.madeira.co.il/author/eitan/