I have a customer who wants to send me a text file that is being created from the following SQL Query:
co.FEIN as ISA05, co.[name] as ISA06,
CASE st.shiptonum WHEN '' THEN cu.CustID ELSE cu.CustID +'-'+ st.shiptonum END as ISA08,
CONVERT(VARCHAR(10),GETDATE(),101) as ISA09, CONVERT(VARCHAR(8),GETDATE(),108) as ISA10,
'ST' as Set_Header, '810' as Set_ID, convert(varchar,id.Invoicenum) + Replace(STR(id.invoiceline, 3),' ', '0') as [Set_Ctrl_Num],
'BIG' as [Set_Header_BIG], iv.invoicedate as [InvoiceDate], iv.invoicenum as [InvoiceNumber], oh.orderdate as [PODate], iv.ponum as [PONumber], iv.invoicetype as [TypeCode],
'REF' as [Set_Header_REF], '' as [Ref_ID], '' as [DUNS],
'N1' as [Set_Header_N1] , cu.CustID as [NameID], st.Name as[Name], '' as [IDCodeQualifier], '' as [IDCode],
'N2' as [Set_Header_N2] , COALESCE(cc.Name, '') as [AttentionName],
'N3' as [Set_Header_N3] , st.Address1 as [Address1],
'N4' as [Set_Header_N4] , st.city as [City], st.state as [State], st.Zip as [Zip],
'ITD' as [Set_Header_ITD], iv.TermsCode as [ITD01], '' as [ITD06],
'IT1' as [Set_Header_IT1], id.poline as [POLine], id.ourshipqty as [Qty], id.ium as [UoM], id.docunitprice as [UnitPrice], '' as [ProdIDQual], id.partnum as [PartNum],
'PID' as [Set_Header_PID], 'F' as [PID01], convert(varchar(255),id.linedesc) as[PartDesc],
'TDS' as [Set_Header_TDS], iv.docinvoiceamt as [TotalInvoiceAmt],
'TX1' as [Set_Header_TX1], COALESCE(ix.TaxCode, '') as [TaxCode], COALESCE(ix.doctaxamt, 0) as [TaxAmount],
'SAC' as [Set_Header_SAC], 'C' as [ChargeID], COALESCE(im.MiscCode, '') as [ChargeCode], COALESCE(im.docmiscamt, 0) as [ChargeAmount]
FROM MfgSys803.dbo.InvcHead iv WITH (NOLOCK)
LEFT JOIN MfgSys803.dbo.Company co ON co.company = iv.company
LEFT JOIN MfgSys803.dbo.InvcDtl id ON id.company = iv.company AND id.invoicenum = iv.invoicenum
LEFT JOIN MfgSys803.dbo.ShipTo st ON st.company = id.company AND st.custnum = id.custnum AND st.Shiptonum = id.shiptonum
LEFT JOIN MfgSys803.dbo.CustCnt cc ON cc.custnum = id.custnum AND cc.shiptonum = id.shiptonum AND cc.connum = id.shpconnum
LEFT JOIN MfgSys803.dbo.Customer cu ON cu.company = id.company AND cu.custnum = id.custnum
LEFT JOIN MfgSys803.dbo.InvcTax ix ON ix.invoicenum = id.invoicenum AND ix.invoiceline = id.invoiceline
LEFT JOIN MfgSys803.dbo.InvcMisc im ON im.invoicenum = id.invoicenum AND im.invoiceline = id.invoiceline
LEFT JOIN MfgSys803.dbo.OrderHed oh ON oh.company = iv.company AND oh.ordernum = iv.ordernum
The problem is that when we get the file, it shows the following when viewing in hex:
The file should look like this:
Somehow it's starting the file with the hex values of FF FE and inserting a hex 00 in between each character.
When you view the file in Notepad, no errant characters, only when you use UltraEdit in hex mode do you find the problem.
Any ideas on how to fix? I need the data as text only for processing and these extra hex values are really screwing things up. I'm anticipating a lot of data, so daily manual manipulation of the data is not an option.
Any help you can give is appreciated.