Need Help with BCP Error

Transact-SQL
1

I am using the BCP utility to copy data from a table in a local database to a network share with the following command:

BCP "SELECT Id, InvoiceNo, Status, ErrorDescription, FileName, IsNull(ModifiedBy,'NU7') AS ModifiedBy, ModifiedOn FROM MyDB.dbo.MyTable WHERE IsDeleted = 0 AND BatchNo IN ('20210723','20210730')" queryout \11.11.144.77\ftp\DataStore\BulkCopy\MyTableNU7.dat -T -n

This command is assigned to a VarChar(MAX) variable named @Cmd and executed as follows from a stored procedure:

EXEC master..xp_cmdshell @Cmd

I am getting the following errors:

SQLState = 08001, NativeError = 2
Error = [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][ODBC Driver 17 for SQL Server]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 config
ured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired

I am at my wit's end trying to figure this out. Could someone please help me?

Thanks in advance!

2

If you don't have sysadmin privs, then xp_CmdShell uses your credentials. If you DO have sysadmin privs, xp_CmdShell uses the login of the SQL Server Service, which must have privs to access the intended destination of your BCP command.

xp_CmdShell is an incredibly powerful tool and it can be pretty easy to use incorrectly especially when it comes to security. The question you ask indicates that you might not have studied it well enough to do so in a safe manner. I strongly recommend that you study the documentation especially about the security aspects of the tool. Used correctly, it's a highly beneficial tool with good security. The opposite can be true if not setup correctly.

3

Thank you so much Jeff! I've been busy with other things and only now have I gotten the time to review this issue.

I tried to do the same BCP command from a DOS command prompt, not by using xp_CmdShell from SSMS. I get the exact same error as when using xp_CmdShell. Is the error supposed to be the same on both?

4

looks like you are missing the -S parameter. It doesn't know where to go.

1 Like
5

Thanks, I'll try that. But it did work on another machine without the -S parameter. I'll try it on the machine that failed as soon as it's available.

6

Thanks, Mike01! Adding the -S parameter fixed the problem. Without the -S parameter, BCP connects to the default instance of SQL Server, which may not be the correct instance that has the source database you want to obtain the data from. The -S parameter ensures that BCP connects to the right server/instance. In the machine where BCP worked without the -S parameter, the default instance just happened to be the correct one so that's why it worked.

To determine the value to be passed for the -S parameter, I used SERVERPROPERTY('ServerName'). It worked on all the five machines that I ran the BCP utility.

7

You can also use @@SERVERNAME and you can also get from the Object Explorer.

1 Like