Must declare the scalar variable @header

Question

I have an issue with bcp to create xml file.

If I execute the single query

SELECT @header, @inland 
FOR XML RAW(''), ROOT('root'), ELEMENTS, TYPE

I receive the correct XML data.

But if i insert the query into exec master..xp_cmdshell

declare @cmd varchar(2000) = 'bcp "SELECT @header,@inland FOR XML RAW(''''),ROOT(''root''), ELEMENTS, TYPE" queryout "\\server01\TEMP_SW\XML_TMS\test_'+@num+'.xml" -U xx -P xxxxx -c -C ANSI -t;' ;
exec master..xp_cmdshell @cmd;

The system returns the error

SQLState = 37000, NativeError = 137
Error = [Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable "@header".

SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.

@header is declared as XML

Where is the error?

Thanks and regards

the value is set with query sql with FOR XML..... SET @@header =(SELECT distinct FH.Name AS 'agent/name',FH.Surname AS 'agent/surnmae',FH.City AS 'City' FROM [DBTest].[dbo].[FINAL] FH WHERE FH.City = @@city FOR XML PATH('header'), Type) – Consider @@ as one @ — Francesco Isetto
Can you concatenate the header & inland variables into the cmd variable? Like 'bcp ' + @@header + ',' + @@inland + ' FOR XML RAW..... You might want to add double quote before @@header. — Han
system give me this error ---> Messaggio 402, livello 16, stato 1, riga 93 The data types varchar and xml are incompatible in the add operator. — Francesco Isetto
To answer the question "where is the error" the answer below addresses that. However, you have a different issue now... "how to pass XML data into xp_cmdshell". I suggest you start a new question for advice on how to do what you are trying to do. I would ask if you need to use XML datatype for your variables. Can you just use a character datatype? This should be addressed in a new/separate question. Good luck! — jamie

jamie jamie · Accepted Answer · 2019-07-09T22:24:46

The error is just what is being reported. When using QUERYOUT option of BCP you get a new, separate SQL connection within which your SQL statement is executed (really it's the xp_cmdshell that first opens a new OS thread... then bcp-queryout opens a new SQL connection). This statement knows nothing of any earlier "declare" or "set/select" statements you may have executed before your xp_cmdshell command. So, looking at your SQL statement as the stand-alone query that it is, of course you will get an error that your variables have not been defined.

To do what you are trying to do, can you just resolve the values of your variables as you are buidling the @cmd string? Like this:

declare @cmd varchar(2000)
select @cmd = 'bcp "SELECT ' + @header + ',' + @inland + ' FOR XML RAW(''''),ROOT(''root''), ELEMENTS, TYPE" queryout "\\server01\TEMP_SW\XML_TMS\test_'+@num+'.xml" -U xx -P xxxxx -c -C ANSI -t;' ;
exec master..xp_cmdshell @cmd;

It looks like you are aleady doing this with the @num variable you add into the string later on. Just do the same with your @header and @inland variables.

Must declare the scalar variable @header

1 Answers