?
Solved

Rename a file after an xp_cmdshell bcp command

Posted on 2016-10-28
4
Medium Priority
?
111 Views
Last Modified: 2016-10-28
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
Comment
Question by:Mikeyman_01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 14

Expert Comment

by:Nakul Vachhrajani
ID: 41864297
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
 

Author Comment

by:Mikeyman_01
ID: 41864412
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
 
LVL 14

Accepted Solution

by:
Nakul Vachhrajani earned 2000 total points
ID: 41864551
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
 

Author Closing Comment

by:Mikeyman_01
ID: 41864656
Nice.. thanks again for your help..You know your shit!!!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question