Problems with bcp utility

Question

I want to extract a SELCET query with the bcp utility. But it doesn't work. MS SQL underlines "bcp" marks it.

DECLARE @DBName NVARCHAR(MAX);
SET @DBName = (SELECT name FROM master.dbo.sysdatabases where name LIKE '%NAV%');
EXECUTE ('USE [' + @DBName+']');

PRINT (' DATABASE SELECTED: '+ @DBName)

bcp "SELECT [Role ID],[Name] FROM [dbo].[Permission Set]" out "C:\Users\Public\Documents\test.txt" -c -T

The error message is:

Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'bcp'.

Does anybody know the correct syntax? If I compare it with the examples on the docs.microsoft website (https://learn.microsoft.com/en-us/sql/tools/bcp-utility#o) I do not see my mistake.

Answer 1

You need to use xp_cmdshell procedure to execute the bcp command in T-SQL script

Declare @sql varchar(8000)
Select @sql = 'bcp "SELECT [Role ID],[Name] FROM [dbo].[Permission Set]" out "C:\Users\Public\Documents\test.txt" -c -T'
Exec master..xp_cmdshell @sql

Answer 2

bcp is a command line utility. It runs from a shell script (typically), not from T-SQL.

Within SQL code, you would normally use bulk insert. See the documentation here.