Dynamic SQL in SQL Server

Question

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

Answer 1

0

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.

answered Feb 22 '12 at 04:44 AM

Usman Butt
13.8k ● 6 ● 8 ● 14

Thanks, Usman, It worked

Feb 22 '12 at 05:53 PM inder

(comments are locked)

Dynamic SQL in SQL Server

Follow this question

Related Questions