September 28, 2011 at 9:51 am
Can anyone spot what is wrong with this?
EXEC [LinkedServerName].master.sys.sp_executesql 'BACKUP DATABASE DB TO DISK =
''\UNCPath\DB.BAK''
WITH COPY_ONLY, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
I'm getting a Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. error.
These are all single quotes.
Thanks
Steve
September 28, 2011 at 9:54 am
Try changing it to this:
DECLARE @sql NVARCHAR(4000)
SET @sql = 'BACKUP DATABASE DB TO DISK = ''\UNCPath\DB.BAK''
WITH COPY_ONLY, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
EXEC master.sys.sp_executesql @sql
You have to pass a variable, not a string to the executesql SP.
September 28, 2011 at 9:58 am
Yes that's what I'm actually doing - I posted was the contents of the variable were.
September 28, 2011 at 9:59 am
Oh hang on i see what you mean.
September 28, 2011 at 10:00 am
hindle.steve (9/28/2011)
Can anyone spot what is wrong with this?
EXEC [LinkedServerName].master.sys.sp_executesql 'BACKUP DATABASE DB TO DISK =
''\UNCPath\DB.BAK''
WITH COPY_ONLY, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
I'm getting a Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. error.
These are all single quotes.
Thanks
Steve
Your code in the OP uses VARCHAR... SP_ExecuteSQL requires it to be an NVARCHAR... For your code put an "N" before the first single quote
EXEC [LinkedServerName].master.sys.sp_executesql N'BACKUP DATABASE DB TO DISK =
''\UNCPath\DB.BAK''
WITH COPY_ONLY, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply