You must Sign In to post a response.
  • Category: SQL Server

    Queryout with Column Names

    CREATE PROCEDURE dbo.proc_generate_txt
    (
    @db_name varchar(100),
    @table_name varchar(100),
    @file_name varchar(100)
    ) AS

    Declare @Headers varchar(1000),@sql varchar(8000), @data_file varchar(100),
    @x varchar(300)

    --Generate column names as a recordset

    Select @Headers = IsNull(@Headers + CHAR(9) ,'') + Column_Name
    From INFORMATION_SCHEMA.COLUMNS
    Where Table_Name = @table_name ORDER BY ORDINAL_POSITION ASC

    print @Headers

    set @sql = 'bcp "select ''' + @Headers + '''" queryout "'+@file_name+'" -c -t'
    print @sql
    exec master..xp_cmdshell @sql
    set @sql = 'exec master..xp_cmdshell ' + @sql
    print @sql
    --Create a dummy file to have actual data
    select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'

    print @data_file
    set @sql = 'bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -t"\t"'
    print @sql
    exec master..xp_cmdshell @sql

    --Copy dummy file to passed txt file
    set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
    print @sql

    exec(@sql)
    --Delete dummy file
    set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
    print @sql
    exec(@sql)
    GO
    ----

    I have run the below query :
    ----------------------------
    exec proc_generate_txt 'AdventureWorksDW', 'DatabaseLog', 'C:\Backup\log1.txt'

    All What I get is :
    -------------------
    User name not provided, either use -U to provide the user name or use -T for Trusted Connection
    usage: bcp {dbtable | query} {in | out | queryout | format} datafile
    [-m maxerrors] [-f formatfile] [-e errfile]
    [-F firstrow] [-L lastrow] [-b batchsize]
    [-n native type] [-c character type] [-w wide character type]
    [-N keep non-text native] [-V file format version] [-q quoted identifier]
    [-C code page specifier] [-t field terminator] [-r row terminator]
    [-i inputfile] [-o outfile] [-a packetsize]
    [-S server name] [-U username] [-P password]
    [-T trusted connection] [-v version] [-R regional enable]
    [-k keep null values] [-E keep identity values]
    [-h "load hints"] [-x generate xml format file]
    NULL
  • #513303
    Did you check if that procedure runs with out errors in query analyser?
    Regards,
    Satish

  • #513318
    The Proceudure runs without any problems.

  • #513329
    try to keep 'C:\Backup\log1.txt' statement is stored procedure and check, not sure why it is happening.

    HTH

    Regards,
    Satish


This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.