Post reply

How do i return dynamic t-SQL Steatement into a varibale

  • July 12, 2010 at 1:13 am

    #139774

    Hi All,

    How do i get a result like this

    set @qString = 'xp cmdshell ''rename '+@file1+' '+@file2+''' '

    declare @result int

    exec @result = @qString

    It fails how i correct this? and get a result?

  • July 13, 2010 at 8:22 am

    #1192056

    thinknight (7/12/2010)

    Hi All,

    How do i get a result like this

    set @qString = 'xp cmdshell ''rename '+@file1+' '+@file2+''' '

    declare @result int

    exec @result = @qString

    It fails how i correct this? and get a result?

    It is not xp cmdshell, it should be xp_cmdshell.

    Can you provide full script so that we can look into any errors if any ?

    HTH,

    Cheers,

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • July 13, 2010 at 8:43 am

    #1192077

    corrected : xp_cmdshell

    But my problem is not this. just could not get return value while executing this script.

  • July 13, 2010 at 8:53 am

    #1192090

    TRY..CATCH is the correct solution.

    Thanks All...

  • July 14, 2010 at 1:07 am

    #1192590

    Hi ,,

    for that u can use char(39) where u want to use single quetotes

  • July 14, 2010 at 3:07 am

    #1192634

    declare @file1 varchar(max) set @file1 = 'file1.test'

    declare @file2 varchar(max) set @file2 = 'file2.test'

    declare @ret int

    declare @sql nvarchar(max) set @sql = N'exec @ret = xp_cmdshell ' + char(39) + 'rename ' + @file1 + ' ' + @file2 + CHAR(39)

    declare @params nvarchar(max) set @params = '@ret int OUTPUT'

    exec sp_executesql @sql, @params, @ret OUTPUT

    print @ret

  • July 14, 2010 at 3:49 am

    #1192646

    sp_executesql will do

  • July 14, 2010 at 3:54 am

    #1192649

    Thanks a lot..

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply