Post reply

xp_cmdshell and Dynamic SQL

  • September 29, 2018 at 4:01 am

    #408573

    Hi

    I have an SP where I want to output a query to a csv file.  If I hard code the xp_cmdshell, it exports the file.  If I write it as dynamic sql, I get an error message "sqlcmd is not a recognized command".  I need it to be dynamic as I need to alter the exported file name each time.  Am I missing something?

    DECLARE @FileName VARCHAR(60)
    DECLARE @Select    VARCHAR(950)
    DECLARE @Dyn VARCHAR(1000)

    SET @FileName=('C:\Dave\test.csv')
    SET @Select=('"SELECT * FROM ELTHAM.dbo.udef_top"')
    SET @Dyn=('''sqlcmd -S DS-1203SF2\SQL2016 -d ELTHAM -E -Q '+ @Select +' -o "' + @FileName + '" -h-1 -s"," -w 700'',no_output')

    print 'Hard Coded'
    EXEC master..xp_cmdshell 'sqlcmd -S DS-1203SF2\SQL2016 -d ELTHAM -E -Q "SELECT * FROM ELTHAM.dbo.udef_top" -o "c:\dave\MyData2.csv" -h-1 -s"," -w 700' ,no_output
    print 'Dynamic'
    EXEC master..xp_cmdshell @Dyn
    print @Dyn
    --Printed @Dyn----'sqlcmd -S DS-1203SF2\SQL2016 -d ELTHAM -E -Q "SELECT * FROM ELTHAM.dbo.udef_top" -o "c:\Dave\test.csv" -h-1 -s"," -w 700'

    Thanks for looking

  • September 30, 2018 at 6:03 am

    #2007436

    dave-802573 - Saturday, September 29, 2018 4:01 AM

    Hi

    I have an SP where I want to output a query to a csv file.  If I hard code the xp_cmdshell, it exports the file.  If I write it as dynamic sql, I get an error message "sqlcmd is not a recognized command".  I need it to be dynamic as I need to alter the exported file name each time.  Am I missing something?

    DECLARE @FileName VARCHAR(60)
    DECLARE @Select    VARCHAR(950)
    DECLARE @Dyn VARCHAR(1000)

    SET @FileName=('C:\Dave\test.csv')
    SET @Select=('"SELECT * FROM ELTHAM.dbo.udef_top"')
    SET @Dyn=('''sqlcmd -S DS-1203SF2\SQL2016 -d ELTHAM -E -Q '+ @Select +' -o "' + @FileName + '" -h-1 -s"," -w 700'',no_output')

    print 'Hard Coded'
    EXEC master..xp_cmdshell 'sqlcmd -S DS-1203SF2\SQL2016 -d ELTHAM -E -Q "SELECT * FROM ELTHAM.dbo.udef_top" -o "c:\dave\MyData2.csv" -h-1 -s"," -w 700' ,no_output
    print 'Dynamic'
    EXEC master..xp_cmdshell @Dyn
    print @Dyn
    --Printed @Dyn----'sqlcmd -S DS-1203SF2\SQL2016 -d ELTHAM -E -Q "SELECT * FROM ELTHAM.dbo.udef_top" -o "c:\Dave\test.csv" -h-1 -s"," -w 700'

    Thanks for looking

    Prepend xp_cmdshell to the @Dyn and execute it as one statement rather than pass it to xp_cmdshell.
    😎

    This is how the @Dyn should look like

    'master..xp_cmdshell ''sqlcmd -S DS-1203SF2\SQL2016 -d ELTHAM -E -Q "SELECT * FROM ELTHAM.dbo.udef_top" -o "C:\Dave\test.csv" -h-1 -s"," -w 700'',no_output'

  • September 30, 2018 at 9:37 am

    #2007439

    Thanks, that's sorted it!

  • September 30, 2018 at 5:28 pm

    #2007444

    dave-802573 - Sunday, September 30, 2018 9:37 AM

    Thanks, that's sorted it!

    You are very welcome.
    😎

Viewing 4 posts - 1 through 4 (of 4 total)

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