x

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

inder gravatar image

inder
211 16 19 22

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 newest

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

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x38

asked: Feb 21, 2012 at 09:58 PM

Seen: 1011 times

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