Rename a file after an xp_cmdshell bcp command

Currently i have a command that reads:

Exec xp_cmdshell 'bcp "Select Expr1, Col9, Col14 from CompanyV Order By Supplier, Expr1 Desc" query out  "C:\bcptestISP.txt" -S HPELITE\MSSQLSERVER12  -T -c -t,'

I would like the "C:\bcptestISP.txt" to be renamed "C:\bcptestISP_getdate().txt"..obviously using the result not the get date()

Is there way for me to do this?
Michael KatzAsked:
Who is Participating?
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Trying to work out a query in Notepad is always bad, especially when quotation marks are involved :)

I had one less quotation after the -t. It's fixed in the sample below:

DECLARE @stringDate NVARCHAR(50);
DECLARE @sqlCommand NVARCHAR(MAX);

SELECT @stringDate = CONVERT(NVARCHAR(50),GETDATE(),112); --ISO 112 formats date as yyyymmdd


SELECT @sqlCommand = N'Exec xp_cmdshell ''bcp "Select Expr1, Col9, Col14 from CompanyV Order By Supplier, Expr1 Desc" query out  "C:\bcptestISP' + N'_' + @stringDate + N'.txt" -S HPELITE\MSSQLSERVER12  -T -c -t,''';

--PRINT @sqlCommand;
EXEC sp_executesql @sqlCommand;

--RESULT:
--Exec xp_cmdshell 'bcp "Select Expr1, Col9, Col14 from CompanyV Order By Supplier, Expr1 Desc" query out  "C:\bcptestISP_20161029.txt" -S HPELITE\MSSQLSERVER12  -T -c -t,'

Open in new window

0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Well, you can do some string concatenation. Here's a rough draft (I don't have SSMS, so there may be a few things that you need to adjust).

DECLARE @stringDate NVARCHAR(50);
DECLARE @sqlCommand NVARCHAR(MAX);

SELECT @stringDate = CONVERT(NVARCHAR(50),GETDATE(),112); --ISO 112 formats date as yyyymmdd


SELECT @sqlCommand = N'Exec xp_cmdshell ''bcp "Select Expr1, Col9, Col14 from CompanyV Order By Supplier, Expr1 Desc" query out  "C:\bcptestISP' + N'_' + @stringDate + N'.txt" -S HPELITE\MSSQLSERVER12  -T -c -t,'';

EXEC sp_executesql @sqlCommand;

Open in new window

0
 
Michael KatzAuthor Commented:
I can't figure out what to adjust..I have it written as above..

I am getting

Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark after the character string '.txt" -S HPELITE\MSSQLSERVER12 -T -c -t,';
EXEC sp_executesql @sqlCommand;
'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.txt" -S HPELITE\MSSQLSERVER12 -T -c -t,';
EXEC sp_executesql @sqlCommand;
'.

Cant figure out where \ what is missing
0
 
Michael KatzAuthor Commented:
Nice.. thanks again for your help..You know your shit!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.