Post reply

TSQL Quotes Within Quotes

  • September 28, 2011 at 9:51 am

    #243797

    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

    #1387890

    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

    #1387893

    Yes that's what I'm actually doing - I posted was the contents of the variable were.

  • September 28, 2011 at 9:59 am

    #1387895

    Oh hang on i see what you mean.

  • September 28, 2011 at 10:00 am

    #1387897

    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