sql - 当本地数据库上没有 -S 参数时，SQL 命令中的 BCP 给出 NativeError = 2

Question

我在 SQL 中有这个命令:

DECLARE @cmd NVARCHAR(4000)
DECLARE @pathAndFileName NVARCHAR(MAX)
DECLARE @result INT
SET @pathAndFileName = 'C:\Temp\file.csv'
SET @cmd = 'bcp "SELECT TOP 1 * FROM SomeDB.dbo.SomeTable" queryout "'
  + @pathAndFileName + '" -w -T -t; '       
EXEC @result = xp_cmdshell @cmd 
SELECT @result

它输出:

SQLState = 08001, NativeError = 2 Error = [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [2]. 
SQLState = 08001, NativeError = 2 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. 
SQLState = S1T00, NativeError = 0 Error = [Microsoft][SQL Server Native Client 11.0]Login timeout expired

@result 为 1

仅当我添加带有服务器名称和数据库名称的 -S 参数时它才有效(例如 -S "MYCOMPUTERNAME\DBINSTANCENAME")。

但是当我在远程服务器上尝试此操作时，无需 -S 参数即可工作。

如何设置本地数据库，这样我就不再需要 -S 了？

Answer 1

如果您省略 -S 那么它将尝试默认值 localhost unnamed instance :

-S server_name[ \instance_name]
Specifies the instance of SQL Server to which to connect. If no server is specified, the bcp utility connects to the default instance of SQL Server on the local computer.

从您的示例来看，您似乎确实有一个实例名称，因此无法在不显式指定 -S 的情况下进行连接。此外，如果您采取显式指定 -S 参数的额外步骤，您的代码将始终更具可移植性并且更易于排除故障。使用SERVERPROPERTY(MachineName)和 SERVERPROPERTY(InstanceName) ，确保代码是集群感知的。