bcp query out

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)
stevendeveloperAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
stevendeveloperAuthor 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 ''.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pawan KumarDatabase ExpertCommented:
Checking.
0
 
stevendeveloperAuthor Commented:
Again  I am in debt to you FINE JOB worked great thanks you very much
0
 
Pawan KumarDatabase ExpertCommented:
Welcome. Happy to help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.