SQL Server output to a file using variable name

I want to send SQL Server output to a file which is working as expected. But if I pass the file path in a variable, It is not creating the file.

Working

:out 'C:\Temp.txt开发者_C百科'

Not Working

DECLARE @BCVFileName VARCHAR(500)
SET @BCVFileName= 'C:\Temp.txt'

:out @BCVFileName

Could anyone please help me on this??

Thanks, Mahesh

For this you need to store your query in Query file and then you can execute from command to store result to a text file as shown below.

>sqlcmd -E -S(Local)\SQLExpress -YourDBName -iC:\myQuery -oC:\Output.txt

Another way to use T-SQL and create a batch statement to execute via XP_CMDSHELL. Following script will help you to do the same just replace your query, ServerName with used variables.

SET NOCOUNT ON;
GO
DECLARE @sqlcmd varchar(1000);
PRINT 'using SqlCMD.exe';
SET @sqlcmd = 'sqlcmd -S' + @@SERVERNAME + ' -E -oc:\outfile.txt '
DECLARE @cmd varchar(1000);
SET @cmd = '-Q"SELECT RIGHT(REPLICATE('' '' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID, CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate FROM AdventureWorks.Person.Address ORDER BY ModifiedDate;"';
SET @sqlcmd = @sqlcmd + @cmd;
--SELECT @sqlcmd;
EXEC xp_cmdshell @sqlcmd, no_output;
PRINT 'using BCP.exe';
SET @cmd = '"SELECT RIGHT(REPLICATE('' '' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID, CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate FROM AdventureWorks.Person.Address ORDER BY ModifiedDate;"';
SET @sqlcmd = 'bcp ' + @cmd + ' queryout "c:\outfile2.txt" -c -T -S' + @@SERVERNAME 
--SELECT @sqlcmd;
EXEC xp_cmdshell @sqlcmd, no_output;

regards