• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 136
  • Last Modified:

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?
0
Mikeyman_01
Asked:
Mikeyman_01
  • 2
  • 2
1 Solution
 
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
 
Mikeyman_01Author 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
 
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
 
Mikeyman_01Author Commented:
Nice.. thanks again for your help..You know your shit!!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now