Search code examples
sql-serversql-server-2014bcpsqlcmd

SQLCMD, BCP - stored procedures don't exist

I'm trying to get a 'Hello World' going as a proof of concept using sqlcmd but I'm having a hard time:

enter image description here

Command:

 sqlcmd 'cmd.exe'

Error:

Could not find stored procedure 'sqlcmd'.

I'm having a similar issue with BCP:

 bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable"

Could not find stored procedure 'bcp'.

This is a SQL Server 2014 machine. What's going on and how do I fix it?

Solution

  • As @marc_s already pointed out, SQLCMD and BCP are external command-line tools. These cannot be called by starting with bcp ... or sqlcmd ... inside SQL Server.

    If you want to run these commands from SQL Server, you need to form the command in a VARCHAR and execute the command through the use of the master.sys.xp_cmdshell stored procedure. Calling this procedure will pass the command to the operating system to execute. Running this command will produce a resultset with each row being a line of the output of running this command.

    Access to the master.sys.xp_cmdshell procedure first has to be enabled. This has to be done only once for your SQL Server installation. You can use this script to do that:

    EXEC sp_configure 'show advanced options', 1 
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1 
GO
RECONFIGURE
GO

    After that you can use this stored procedure. I'll give an example based on the use of BCP. This will output all your table information (from INFORMATION_SCHEMA.TABLES) to C:\Temp\information_schema.txt.

    DECLARE @stmt VARCHAR(8000);
SET @stmt='BCP "SELECT*FROM INFORMATION_SCHEMA.TABLES" QUERYOUT "C:\Temp\information_schema.txt" -c -T -S ' + @@SERVERNAME + ' -d ' + DB_NAME();
EXEC master.sys.xp_cmdshell @stmt;