Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

Trouble connecting to localdb via SQLCMD in PowerShell - what is server instance name?

SQL Server newbie here - I'm trying to access the localdbMSSQLLocalDB server on my computer through PowerShell with the SQLCMD utility. I'm using PowerShell v5, .NET v5.0, and The server name is (localdb)MSSQLLocalDB when I connect to it in Microsoft SQL Server Management Studio 2014.

PS C:> sqlcmd -S localdbMSSQLLocalDB and PS C:> sqlcmd -S .localdbMSSQLLocalDB result in this error:

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

I queried the server name in Management Studio with SELECT @@ServerName and used that after the -S in the above command and got the same error.

PS C:> sqlcmd -S localdb gives this error:

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [53]

Other notes: I'm able to connect to the server and work with a database named testdb01 in a C# console app using System.Data.SqlClient with this connection string:

"Data Source=(localdb)\mssqllocaldb;Initial Catalog=testdb01;Integrated Security=SSPI;"

Any ideas on where to start or how to approach this?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Passing arguments from PowerShell to EXEs can be tricky. I think you need to escape the parens either with single quotes, which means the content is a static literal string, or the backtick escape character:

sqlcmd -S '(localdb)MSSQLLocalDB' -Q "Select @@servername"

sqlcmd -S `(localdb`)MSSQLLocalDB -Q "Select @@servername"

or even using a variable:

PS H:> $myserver = '(localdb)MSSQLLocalDB'

PS H:> sqlcmd -S $myserver -Q "Select @@servername"

Also, if you are using sqlcmd, perhaps look at Invoke-Sqlcmd which is more native to PowerShell:

PS SQLSERVER:> Invoke-Sqlcmd -Server $myserver -Query "Select @@servername"

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...