Using varchar in the query syntax for BCP utility in SQL Server

Question

I am trying to use BCP utility to export data with these commands, but I get an error in the query ...

I declared many variables, however my problem is with the query, due to I have to filter using a varchar column (CO_LIN is a varchar(6)), but if I use ' to wrap the value of the variable @LINEAPRODUCTO I got an error in SQL Server query tool, but if I do not use anything it give me this error:

Invalid column name 'MEN'

(MEN is the string value I set in the variable declaration)

DECLARE @PATH_ARC VARCHAR(200)
DECLARE @USUARIO VARCHAR(20)
DECLARE @PASSWORD VARCHAR(20)
DECLARE @TABLA VARCHAR(40)
DECLARE @SERVIDOR VARCHAR(100)
DECLARE @ARCHIVOSALIDA VARCHAR(200)
DECLARE @RUTAEJECUTAR VARCHAR(500)
DECLARE @MES VARCHAR(2)
DECLARE @DIA VARCHAR(2)
DECLARE @ANNO VARCHAR(4)
DECLARE @BASEDEDATOS VARCHAR(20)
DECLARE @LINEAPRODUCTO VARCHAR(6)
DECLARE @CONSULTA VARCHAR(300)

SET NOCOUNT ON;

-- Asignación de valores de las variables para la ejecución del stored procedure
SET @TABLA ='dbo.art'
SET @SERVIDOR = 'RJLD-LAPTOP\SQLEXPRESS'
SET @USUARIO = 'exp'
SET @PASSWORD = 'exp'
SET @PATH_ARC = 'C:\PRUEBA\'
SET @BASEDEDATOS = 'DEMO'
SET @LINEAPRODUCTO = 'MEN'

SET @MES = CAST(MONTH(GETDATE()) AS nchar(2))

IF LEN(RTRIM(LTRIM(@MES))) = 1
BEGIN
   SET @MES = '0' + RTRIM(LTRIM(@MES))
END

SET @DIA = CAST(DAY(GETDATE()) AS nchar(2))

IF LEN(RTRIM(LTRIM(@DIA))) =1
BEGIN
    SET @DIA = '0' + RTRIM(LTRIM(@DIA))
END

SET @ANNO = CAST(YEAR(GETDATE()) AS nchar(4))
SET @ARCHIVOSALIDA = 'RI_C58-L02_' + @ANNO  + @MES + @DIA + '.txt'
SET @PATH_ARC = '"' +  @PATH_ARC + @ARCHIVOSALIDA + '"'
SET @CONSULTA = '"select RTRIM(LTRIM(ITEM)),' + @ANNO + ',' +  @MES + ',' + @DIA + ',CO_PROV from ' + @TABLA + ' WHERE CO_LIN=' + @LINEAPRODUCTO + '"'

SET @RUTAEJECUTAR = 'bcp ' +  @CONSULTA + ' queryout ' + @PATH_ARC + ' -S ' + @SERVIDOR + ' -U ' + @USUARIO + ' -P ' + @PASSWORD + ' -c -t "|" -d ' +  @BASEDEDATOS

--  select @RUTAEJECUTAR
EXEC xp_cmdshell @RUTAEJECUTAR

score 0 · Answer 1 · edited May 23 '17 at 12:03

I'm not familiar with the BCP utility, but what I see happening is the same thing I'm seeing in the code formatting StackOverflow is using. Notice how the slash after PRUEBA is resulting in the following apostrophe not being recognized. I found another problem here where someone was having to get around the BCP utility using the slash as the escape character.

If the slash in the filepath is registering as an escape character, then the following code should work:

SET @PATH_ARC = 'C:\\PRUEBA\\'
SET @BASEDEDATOS = 'DEMO'
SET @LINEAPRODUCTO = 'MEN'

Notice that with the code above, the StackOverflow formatting is reading it correctly too.

Hope this helps!

Using varchar in the query syntax for BCP utility in SQL Server

1 Answers1