bcp query out

[b]Solution[/b]

[code]

SET NOCOUNT ON;
       
DECLARE 
     @filename varchar(150),
     @query varchar(5000),
     @bcp varchar(5000),
     @year char(4) = '2015',
     @month varchar(7) = 'March',
     @debug bit = 0

SET @Query = ' " select co.co_num as ''W/O'',
co.co_Line as Line,
cu.name as ''Customer Name'',
i.product_code as ''Material'',
c.order_date,
LTRIM(RTRIM(REPLACE(SUBSTRING(udfvcld1,0,CHARINDEX(''X'',udfvcld1,0)),''"'',''''))) od,
case when u.udfvcld1 like ''%dia%''Then NULL else  LTRIM(RTRIM(REPLACE(SUBSTRING(udfvcld1,CHARINDEX(''X'',udfvcld1,0)+1,CHARINDEX(''X'',udfvcld1,CHARINDEX(''X'',udfvcld1,0)+1) - CHARINDEX(''X'',udfvcld1,0) -1),''"'','''')))  end as id,
LTRIM(RTRIM(REPLACE(SUBSTRING(udfvcld1,CHARINDEX(''X'',udfvcld1,CHARINDEX(''X'',udfvcld1,0)+1)+1,LEN(udfvcld1)),''"'',''''))) length,
co.qty_ordered as qty,
case when c.type = ''B'' then ''Yes'' else ''NO'' end as blanket,
case when ship_partial = 1 then ''YES'' else ''NO'' end as partial,
co.due_date  --select *
from  coitem_mst co 
      inner join co_mst c on c.co_num = co.co_num 
      inner join item_mst i on i.item = co.item
      inner join custaddr_mst cu on cu.cust_num = c.cust_num            
                                                       and cu.cust_seq =c.cust_Seq
            left join UserDefinedFields u on u.RowId = i.rowpointer
where convert(nvarchar(10),co.createdate,101) = convert(nvarchar(10),getdate(),101) " '
     
SET @filename = '"C:\Pawan\tblName_' + @month + '_' + @year + '.txt" -c -T'

SET @bcp = ' EXEC master..xp_cmdshell '' bcp ' + @query + ' queryout ' + @filename + ''''

EXEC ( @bcp )

SET NOCOUNT OFF;

Select allOpen in new window