Link to home
Start Free TrialLog in
Avatar of T Hoecherl
T HoecherlFlag for United States of America

asked on

Add '#' to end of file

I have a stored procedure which is called by a vb.net program and the end result of the process is that a text file is generated.  This is the T-SQL code for the stored procedure:

ALTER PROCEDURE [dbo].[udp_INTERCEPT_FILE] @Batch_Num VARCHAR(20)
AS

select col1
 from (

 Select -2 AS row_num, '<V1.0>,"' + substring(@Batch_Num, 11, 1 ) + substring(@Batch_Num, 14, 2) + 'I"' AS col1
 UNION ALL
 Select -1 AS row_num, '*,"' + substring(@Batch_Num, 7, 5) + substring(@Batch_Num, 14, 2) + 'i",,"PONUM",,,"' +
                 + substring(@Batch_Num, 7, 5) + substring(@Batch_Num, 14, 2) + 'i.dat"'
 UNION ALL
 Select  ROW_NUMBER() Over ( order by order_number, line_number,winpart,winpos) AS row_num,
               Cast( (ROW_NUMBER() Over ( order by order_number, line_number,winpart,winpos)) as varchar( 100 )) + ','
             + Cast(Quantity as varchar(25)) + ',0,"IG",'
             + CASE winpart
                   when 'FIXED' then Cast(xwpFixedUnitWidth as varchar(25))
                   else Cast(xwpVentUnitWidth as varchar(25))
                   end + ','
             + CASE winpart
                   when 'FIXED' then Cast(xwpFixedUnitHeight as varchar(25))
                   else Cast(xwpVentUnitHeight as varchar(25))
                   end
              + ',,"'
           + Isnull ( ( select top 1 item_number from pct_order_bom where pct_order_bom.Order_Number = pct_glass_labels.ORDER_NUMBER
             AND pct_order_bom.LINE_Number = pct_glass_labels.LINE_Number
             AND STOCKED = 'SPACER' ), 'Not Found') + '","GAS",,,,,,,,"' + substring(Order_Number,7,6) + '-' + Cast(line_number as varchar(5)) + '"' AS COL1_AFTER_ROW_NUM
 from pct_glass_labels
 where batch_Number = @Batch_Num

 ) as derived
 order by row_num

It works perfectly except for one thing:  I need to have a '#' at the end of the file on a row by itself.  If I add this code:

UNION ALL
SELECT '#'

just before the ") as derived", I get this error:

"All queries combined using UNION, INTERSECT OR EXCEPT must have an equal number of expressions in their target list."

How can I get this '#' added to the end of my file?
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of T Hoecherl

ASKER

Thanks for your help.  This resolved my issue.