Dynamic SQL in SQL Server

Hi, This is the piece of code for undocumented proc xp_delete_file. It executes fine with dynamic SQL but does not work with execute command. Can anyone please give the reason for this ?

 DECLARE @currentdate nvarchar(19)
 DECLARE @CurrentDirectory nvarchar(max)
 declare @sql nvarchar(maX)
 set @currentdate=CONVERT(nvarchar(19),getdate(),126)
 set @CurrentDirectory=N'C:\SQL Backups'
 set @sql='EXECUTE  [master].dbo.xp_delete_file 0, N''' + @CurrentDirectory + ''', ''' + 'bak' + ''', ''' + @currentdate + ''' '
 execute(@sql) --Works
 EXECUTE  [master].dbo.xp_delete_file 0,@CurrentDirectory , 'bak' , @currentdate --Does not work
more ▼

asked Feb 21, 2012 at 09:58 PM in Default

avatar image

211 18 19 25

When you say it doesn't work, does that mean you get an error message or that nothing at all happens?

Feb 21, 2012 at 10:13 PM Magnus Ahlkvist

I get error message for invalid parameter like Msg 22049, Level 15, State 0, Line 0 Error executing extended stored procedure: Invalid Parameter

But same works with execute(@sql).

Feb 21, 2012 at 10:16 PM inder
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The problem is the with the datatype of the variable @CurrentDirectory. It cannot be passed as nvarchar(max). The maximum you may pass could be

 DECLARE @CurrentDirectory nvarchar(4000)

But I doubt that there would be such big path for the directory. I guess nvarchar(500) should be enough.

more ▼

answered Feb 22, 2012 at 04:44 AM

avatar image

Usman Butt
13.9k 6 13 21

Thanks, Usman, It worked

Feb 22, 2012 at 05:53 PM inder
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 21, 2012 at 09:58 PM

Seen: 1258 times

Last Updated: Feb 23, 2012 at 06:10 AM

Copyright 2017 Redgate Software. Privacy Policy