none
BCP dynamic QUERYOUT file name RRS feed

  • Question

  • HI 

    I have a number of querys that I have to run.    Some of them have went to SSRS to then email but others simply need to be dropped in tto shared folders.   So instead of running the select an by runing results to I thoughtof using BCP.

    create table new_tbl (id int, basket varchar(10), Calc numeric)
     

    insert into new_tbl
    values (1, 'Test', 123.12)
     , (2, 'Test', 456.78)
     , (3, 'Test', 901.12)

     Now current In now using

    exec master..xp_cmdshell
    'BCP "SELECT Name FROM SYS.Columns WHERE OBJECT_NAME(OBJECT_ID) = ''new_tbl '' UNION ALL select cast(id as varchar(10)), basket, cast(Calc as varchar(28)) FROM Tmp..new_tbl " QUERYOUT  C:\temp\test.txt -T -c -t}'

    This works fine its a bit of a bind as on one of the views there is 85 columns.   Any way within the QUERYOUT command can you dynamically out put the file name ??

    So something like this ? which doesnt work

    DECLARE @filename AS nvarchar
    SET @filename = 'Test1.txt'

    exec master..xp_cmdshell
    'BCP "SELECT Name FROM SYS.Columns WHERE OBJECT_NAME(OBJECT_ID) = ''new_tbl '' UNION ALL select cast(id as varchar(10)), basket, cast(Calc as varchar(28)) FROM Tmp..new_tbl " QUERYOUT  C:\temp\@filename -T -c -t}'  ??

    The objective for doing this would be that each time it is run it gives a different file name .

    Any suggestions on this would be great !

    Thanks

    Friday, February 15, 2013 9:35 AM

Answers

All replies