We help IT Professionals succeed at work.

bcp query out

steven
steven asked
on
158 Views
Last Modified: 2017-11-03
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

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.
Database Expert
Awarded 2016
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
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 :)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions