Declare string in quote in SQL Server

Question

I am trying to run a SQL command but I am getting error message of Not a Valid Identifier.

If I am using this command,

EXEC xp_cmdshell N'mkdir C:\Users\PC\Desktop\Backup\1042012'
GO

then this command works properly

But, if I am using this command

DECLARE @CurrentDate varchar(50)
DECLARE @Path varchar(200)
DECLARE @Command varchar(200)

SET @CurrentDate = GETDATE()
SET @Path = N'C:\Users\PC\Desktop\Backup'
SET @Command = N'xp_cmdshell " ' + 'MKDIR' + ' '+ @Path + '\' +  @CurrentDate + '" '

EXEC @Command
GO

I am getting that error message. Is something to do with declaring a string properly or not.

To assist your troubleshooting, you can issue a SELECT @Command instead of EXEC to see what the string looks like that you are building. — StingyJack, Oct 04 '12 at 17:12

score 7 · Answer 1 · edited May 23 '17 at 10:27

7

I've added EXEC, a unicode identifier (N), and single quotes in place of your double-quotes. Please let us know how this works.

DECLARE @CurrentDate varchar(50)
DECLARE @Path varchar(200)
DECLARE @Command varchar(200)

SET @CurrentDate = convert(varchar(30), GETDATE(), 112)
SET @Path = N'C:\Users\PC\Desktop\Backup'
SET @Command = N'EXEC xp_cmdshell N''' + 'MKDIR' + ' '+ @Path + '\' +  @CurrentDate + ''''

EXEC (@Command)
GO

UPDATE

Testing revealed the need for formatting the date to yyyymmdd (other formats available) and also parenthesis around EXEC as always (thanks, @Lamak). This gives us the following directory, today:

C:\Users\PC\Desktop\Backup\20121004

As an aside, I do recommend this date format as it will sort alphabetically in chronological order in your filesystem, whereas mmddyyyy or ddmmyyyy would not.

edited May 23 '17 at 10:27

Community

1
1

answered Oct 04 '12 at 17:11

Tim Lehner

14,813
4
59
76

don't `EXEC` needs parentheses to execute a string? – Lamak Oct 04 '12 at 17:18
@TimLehner: I am getting this error message Could not find stored procedure 'EXEC xp_cmdshell N'MKDIR C:\Users\PC\Desktop\Backup\Oct 4 2012 1:25PM''. – Rushabh Shah Oct 04 '12 at 17:26
Please try this most recent version. I've even tested it as-is on my own server (yes, I definitely should've done that first!). – Tim Lehner Oct 04 '12 at 17:28

score 2 · Accepted Answer · answered Oct 04 '12 at 17:18

2

I think that : is not allowed in directory names, I converted it to a string but it doesn't have the time on it:

DECLARE @CurrentDate varchar(50)
DECLARE @Path varchar(200)
DECLARE @Command varchar(200)

SET @CurrentDate = CONVERT(VARCHAR(20), GETDATE(), 110)
SET @Path = N'C:\Users\PC\Desktop\Backup'
SET @Command = 'MKDIR' + ' '+ @Path + '\' +  @CurrentDate
PRINT @Command
EXEC xp_cmdshell @Command

answered Oct 04 '12 at 17:18

Decker97

1,643
10
11

3 years later and still very helpful! :) I can never quite get the quotes right when setting up command line variables. – plditallo Sep 19 '15 at 00:15

Declare string in quote in SQL Server

2 Answers2