bcp query out

steven
steven used Ask the Experts™
on
Is it possible to use the bcp command to query out the below

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)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this BCP queryout code-

[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;

Open in new window

stevendeveloper

Author

Commented:
Error....


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'W'.
Msg 103, Level 15, State 4, Line 6
The identifier that starts with '',''))) od,
case when u.udfvcld1 like '%dia%'Then NULL else  LTRIM(RTRIM(REPLACE(SUBSTRING(udfvcld1,CHARINDEX('X',udfvcld1,0)+1' is too long. Maximum length is 128.
Msg 103, Level 15, State 4, Line 8
The identifier that starts with '',''))) length,
co.qty_ordered as qty,
case when c.type = 'B' then 'Yes' else 'NO' end as blanket,
case when ship_partial = 1' is too long. Maximum length is 128.
Msg 105, Level 15, State 1, Line 19
Unclosed quotation mark after the character string ''.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Checking.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Database Expert
Awarded 2016
Top Expert 2016
Commented:
SET @Query - (LINE - 11) This code should be on the single line. Do not press enter in between.

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 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 + ''''

PRINT ( @bcp )

SET NOCOUNT OFF;

Open in new window

stevendeveloper

Author

Commented:
Again  I am in debt to you FINE JOB worked great thanks you very much
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Welcome. Happy to help :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial