Post reply

Dynamic SQL BCP

  • February 22, 2007 at 4:53 am

    #116233

    Hi i want to bcp data out to excelll csv.

     

    Problem is my data set is prodsuced by exec(@sql) where @sql is a dynamically built up query.

     

    what syntax would i need here

     

    i have

     

    declare @sql

    set @sql = 'select * from sysobjects'

    declare @cmd varchar(4000)

    set @cmd = 'master..xp_cmdshell ''bcp exec (' @sql  +')" queryout c:\test.csv -n -s(LOCAL) -e '''

    exec (@cmd)

    But ths doesnt seem to work

     

    Thanks very much for any pointers,

     

    Jules

    www.sql-library.com[/url]

  • February 22, 2007 at 5:59 am

    #691120

    Try

    declare @cmd varchar(4000), @sql varchar(100)

    set @sql = 'select * from sysobjects'

    set @cmd = 'master..xp_cmdshell ''bcp "' + @sql + '" queryout c:\test.csv -n -S (LOCAL) -e '''

    exec (@cmd)

    Beware of the type and number od quotes

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 22, 2007 at 8:53 am

    #691187

    thanks. DO you know if there is a limit on the length of the sql statement ececuted?

    www.sql-library.com[/url]

  • February 22, 2007 at 9:12 am

    #691193

    For the query in bcp, I do not know and cannot find any documentation that states a limit.

    For EXEC the limit is the variable and therefore varchar(8000) for SQL2K. However you can split your query into more than one variable and pass them to exec, eg

    EXEC (@cmd1 + @cmd2 + @cmd3)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 22, 2007 at 9:51 am

    #691208

    yep doing that in the bcp call wondered if that might be the problem

    www.sql-library.com[/url]

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

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