May 9, 2008 at 2:39 pm
I have a proc that creates a txt file. The sp uses a local variable that is declared as a varchar(8000). I just ran into the problem that the text file can be well over 8000 characters. So I tried to use the text datatype and I got the errror: "The text, ntext, and image data types are invalid for local variables."
The SP uses a cursor (I know I know) to build the @Body variable which fills the txt file. I would like to only write the file once and not append to it.
Is this possible?
How do some of you handle a large amount of characters like this?
May 9, 2008 at 5:10 pm
Who told you that MS SQL Server is a text editor?
Don't trust that person any more. It's not true.
You should chose appropriate task for every job.
To spike with a microscope - not the best idea.
_____________
Code for TallyGenerator
May 9, 2008 at 5:48 pm
I agree with Sergiy... however, sometimes you gotta do what ya gotta do.
Post your code so we can try things out...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2008 at 6:07 pm
Jeff Moden (5/9/2008)however, sometimes you gotta do what ya gotta do.
Never.
Period.
Unless you're an amateur and don't have plans to become a professional.
What would your car mechanic say if you'd try to tell him what to do with your car and which way?
Right, if he is a professional he would just hand toll over to you and say "Do it if you know what to do".
Why IT industry must be any different?
_____________
Code for TallyGenerator
May 12, 2008 at 5:40 am
Sergiy and Jeff,
Thanks for the vote of reassurance!! LOL! Seeing how SQL is not a text editor what would be your recommendation for situations like these?
This is a case of I've got to get it done. I could trim the data down to well below the 8000 char size and generate a file for each < 8000 char segment but that's not what I really want to do. Espescially if business activity increases (which it will).
Okay I trimmed the proc down and included only the cursor portion so it was a little less lengthy. Below is the code. Really all this proc does is fetch data and build a Body based on this data then create a txt file. The txt file should look like this:
"Header Record",^
"01","210","4","20080325","0815","004010"^
"HR","00","28976","130482A","20060310","1219.80","20060303","","","USD,^
"LI","1",""CHARGE","","","","","119.80","","","","",^
"HR","00","28007","","USD,^
"LI","1","Main CHARGE","","","","","725.00","","","","",^
"LI","2","Secondary CHARGE","","","","","140.76","","","","",^
"Trailing Data",^
In the below code the first cursor creates the HR lines and the nested cursor creates the LI lines. Each Order needs to have a "HR" line and at least one "LI" line. So the above example would be the result of two orders. Once the body is built I add on a "Header Record" (the first line) and a "Trailing Data" footer (not included in the below code).
Create Proc s210Cursor
@Body varchar(8000) out,
@Error int out,
@ControlNumber varchar(15) out
as
declare @OrderNumber int,
@RecordID char(2) ,
--@PurposeCode char(2) ,
@InvoiceNumber varchar(22) ,
@BOLNumber varchar(30) ,
@BillingDate varchar(8) ,
@NetAmountDue decimal(8,2) ,
@PONumber varchar(22) ,
@ShipperName varchar(35) ,
@ShipperAddress varchar(35) ,
@ShipperCity varchar(30) ,
@ShipperState char(2) ,
@ShipperZip varchar(6) ,
@ShipperID int ,
@ConsigneeName varchar(35) ,
@ConsigneeAddress varchar(35) ,
@ConsigneeCity varchar(30) ,
@ConsigneeState char(2) ,
@ConsigneeZip varchar(6) ,
@ConsigneeID int ,
@BillToName varchar(35),
@BillToAddress varchar(35) ,
@BillToCity varchar(30) ,
@BillToState char(2) ,
@BillToZip varchar(6) ,
@BillToID int ,
@TotalGrossWeight decimal(10,0) ,
@TotalNetWeight decimal(10,0),
@Charge decimal(8,2) ,
@TotalLadingQuantity decimal(9,0) ,
@ShipPay char(2) , --PP,CC,TP
@EDIRef varchar(30) ,
@AccountCode varchar(30) ,
@PickupDate varchar(8) ,
@DeliveryDate varchar(8) ,
@LineItemID int,
@LineItemDesc varchar(20),
@LineItemCharge decimal(12,2),
@LineItemFreightRate varchar(9),
@LineItemBilledRate varchar(9),
@LineItemRateQualifier varchar(9),
@LineItemAccessorialCode char(3),
@OrderID int,
@LineItemNumber int,
@MainBody varchar(8000),
@InvoiceCount int,
@CurrentDate datetime,
@Month varchar(2),
@Day varchar(2),
@Hour varchar(2),
@min-2 varchar(2),
@sec varchar(2),
@Date varchar(8),
@DesCartesHeader varchar(100),
@IpsWWHeader varchar(100),
@IPSWWTrailer varchar(20),
@ErrorMsg varchar(100),
@ReturnCode int,
@RowCount int,
@HeaderDetailReturnCode int,
@InvoiceBody varchar(8000),
@LineItemBody varchar(8000),
@BatchMsg varchar(100),
@BatchStatus varchar(100),
@ParamList varchar(500)
--Get BatchControl Number
BEGIN
INSERT INTO t_210_BatchControl (Customer,BatchStatus) VALUES (@CustomerName,'Gathering Invoices')
SET @ControlNumber = Scope_Identity()
IF isnull(@ControlNumber,0) = 0 BEGIN Print 'ControlNumber 0 or null.' SET @Error = 1 Set @ErrorMSG = 'Failure' Goto Error END
END
Declare EDI Cursor For
select Number from t_ORDER where number in (28296,30298,28807)
OPEN EDI
FETCH NEXT FROM EDI INTO @OrderNumber
IF @@Fetch_Status = 0
BEGIN
SET @InvoiceCount = 1
END
WHILE @@Fetch_Status = 0
BEGIN
SET @LineItemNumber = 1
--Get Details
Exec dbo.s_EDI_210_HeaderDetail @OrderNumber,@OrderID out,@RecordID out,@PurposeCode out,@InvoiceNumber out,@BOLNumber out,@BillingDate out,@NetAmountDue out,@PONumber out,@SCAC out,@ShipperName out,@ShipperAddress out,@ShipperCity out, @ShipperState out,@ShipperZip out,@ShipperID out,
@ConsigneeName out,@ConsigneeAddress out,@ConsigneeCity out,@ConsigneeState out,@ConsigneeZip out,@ConsigneeID out,@BillToName out,@BillToAddress out,@BillToCity out,@BillToState out,@BillToZip out,@BillToID out, @TotalGrossWeight out,@TotalNetWeight out,@Charge out,@TotalLadingQuantity out,@ShipPay out,
@EDIRef out,@AccountCode out,@PickupDate out,@DeliveryDate out,@HeaderDetailReturnCode out
--=============== Establish Body ==========================
SET @MainBody =
'"HR"' +','+ --Recode ID
convert(varchar(20),@OrderNumber) +'","'+ --Invoice Number
@BOLNumber +'","'+ -- BOL Number
@BillingDate +'","'+ --Billing Date
convert(varchar(20),@NetAmountDue) +'","'+ --Net Amount Due
@DeliveryDate +'","'+--Delivery Date
@PoNumber +'","'+-- Purchase Order #
@ShipperName +'","'+ --ShipperName
@ShipperAddress +'","'+ --Shipper Address
@ShipperCity +'","'+ --Shipper City
@ShipperState +'","'+ --Shipper State
@ShipperZip +'","'+ --Shipper ZIP
convert(varchar(20),@ShipperID) +'","'+ --Shipper ID
@ConsigneeName +'","'+ --Consignee Name
@ConsigneeAddress +'","'+ --ConsigneeAddress
@ConsigneeCity +'","'+ --Consignee City
@ConsigneeState +'","'+ --Consignee State
@ConsigneeZip +'","'+ --Consignee Zip
convert(varchar(20),@ConsigneeID) +'","'+ --Consignee ID
@BillToName +'","'+ --BillTo Name
@BillToAddress +'","'+ --BillTo Address
@BillToCity +'","'+ --BillTo City
@BillToState +'","'+ --BillTo State
@BillToZip +'","'+ --BillTo Zip
convert(varchar(20),@BillToID) +'","'+ --BillTo ID
convert(varchar(20),@TotalGrossWeight)+'","'+ --Total Gross Weight
'","'+--Total Net Weight
convert(varchar(20),@NetAmountDue) +'","'+--Charge
'USD,'+--Ctry (Country) Currency Code
'^' +Char(13) +char(10)--Record Terminator
---Invalid Header Detail Error Trap
IF @HeaderDetailReturnCode = 1
BEGIN
exec dbo.s_EDI_210_InvoiceErrorInsert @OrderNumber,@MainBody,@ControlNumber,'Invalid Header Detail',@ReturnCode out
IF @ReturnCode > 0 BEGIN PRINT 'Header Detail has bad data. Error Insert successful' END
IF isnull(@ReturnCode ,0) = 0
BEGIN
PRINT 'Invoice Error Insert Failed Terminating Batch'
Set @Error = 3
SET @ErrorMSG = 'Invoice Error Insert Failed'
CLOSE EDI
DEALLOCATE EDI
Goto Error
END
SET @InvoiceCount = @InvoiceCount- 1
GOTO NextOrder
END
--Insert each invoice into table if Valid
exec dbo.s_EDI_210_InvoiceInsert @ControlNumber,@OrderNumber,'Gathering Invoices',@ReturnCode out
IF @ReturnCode > 0 BEGIN PRINT 'Insert Successfull' END
IF isnull(@ReturnCode,0) = 0
BEGIN
PRINT 'Invoice Insert Failed Terminating Batch'
Set @Error = 2
SET @ErrorMSG = 'Invoice Insert Failed'
CLOSE EDI
DEALLOCATE EDI
Goto Error
END
--IF @Body has data append to it othewise set it to the mainbody only
IF @Body > '' BEGIN SET @Body = @Body + char(13)+Char(10) +@MainBody END
IF isnull(@Body,'') = '' BEGIN SET @Body = @MainBody END
--Set Each Invoice Body Individually so we can remove it later if there is a problem
SET @InvoiceBody = @MainBody
--============ Line Item Cursor and Details ===============
Declare EDID Cursor for
select a.ID
from t_Transaction_Detail a
JOIN t_Transaction b on a.Transaction_Id = b.ID
left outer join (select ID,ENG_ITEMDESC from t_XREF) Fee on Fee.ID = a.ITEMTYPE_ID
where a.order_id =@OrderID
OPEN EDID
FETCH NEXT FROM EDID INTO @LineItemID
WHILE @@Fetch_Status = 0
BEGIN
--Get Line Item Details
Select LineItemDesc=Fee.Eng_Itemdesc,@LineItemCharge =Rate*Quantity from t_Transaction_Detail a JOIN t_Transaction b on a.Transaction_Id = b.ID
left outer join (select ID,ENG_ITEMDESC from t_XREF) Fee on Fee.ID = a.ITEMTYPE_ID
where a.id =@LineItemID
--Esablish Line Item Body
SET CONCAT_NULL_YIELDS_NULL OFF
SET @LineItemBody = '"LI' +'","'+ --Record ID
convert(varchar(20),@LineItemNumber)+'","'+ --Line Item Number
@LineItemDesc+'","'+ --Line Item Desc
convert(varchar(20),@LineItemCharge) +--Charge
'^' --Record Terminator
--IF Line Item Data is invalid Replace and Log
IF isnull(@LineItemBody,'') = '' or isnull(@LineItemDesc,'') = '' or convert(varchar(20),isnull(@LineItemCharge,'')) =''
BEGIN
exec dbo.s_EDI_210_InvoiceErrorInsert @OrderNumber,@LineItemBody,@ControlNumber,'Invalid Line Item Detail',@ReturnCode out
IF @ReturnCode > 0 BEGIN PRINT 'Line Item has bad data. Error Insert successful' END
IF isnull(@ReturnCode ,0) = 0
BEGIN
PRINT 'Invoice Error Insert Failed Terminating Batch'
Set @Error = 3
SET @ErrorMSG = 'Invoice Error Insert Failed'
Close EDID
Deallocate EDID
CLOSE EDI
DEALLOCATE EDI
Goto Error
END
--Remove Bad Invoice Body and decrement Invoice Count
SET @Body = replace(@Body,@InvoiceBody,'')
SET @InvoiceCount = @InvoiceCount - 1
END
--Set Main Body
SET @BODY = @Body + @LineItemBody
--Set Individual Invoice Body so we can remove if any problems
SET @InvoiceBody = @InvoiceBody + @LineItemBody
--Fetch Next Record,Append to @Body and Increment LineItemNumber
FETCH NEXT FROM EDID INTO @LineItemID
IF @@Fetch_Status = 0
BEGIN
SET @Body = @Body +Char(13)+char(10)
SET @InvoiceBody = @InvoiceBody + Char(13) + Char(10)
SET @LineItemNumber =@LineItemNumber + 1
END
END
IF isnull(@LineItembody,'') = '' --Line Item Body will be null if an Order Number doesn't have any rows in the transaction tables thus returning no L1 data.
BEGIN
--Update Statment that doesn't work goes here
exec dbo.s_EDI_210_InvoiceErrorInsert @OrderNumber,'Line Item Null or Blank',@ControlNumber,'Invalid Line Item Detail',@ReturnCode out
IF @ReturnCode > 0 BEGIN PRINT 'Line Item has bad data. Error Insert successful' END
IF isnull(@ReturnCode ,0) = 0
BEGIN
PRINT 'Invoice Error Insert Failed Terminating Batch'
Set @Error = 3
SET @ErrorMSG = 'Invoice Error Insert Failed'
Close EDID
Deallocate EDID
CLOSE EDI
DEALLOCATE EDI
Goto Error
END
--Remove Bad Invoice Body and decrement Invoice Count
SET @Body = replace(@Body,@InvoiceBody,'')
SET @InvoiceCount = @InvoiceCount - 1
END
Close EDID
Deallocate EDID
NextOrder:
FETCH Next From EDI Into @OrderNumber
--Reset LineItemNumber once Next Shipment is fetched
SET @LineItemNumber = 0
IF @@Fetch_Status = 0
--Increment Invoice Count only if another record is retrieved
BEGIN SET @InvoiceCount = @InvoiceCount + 1 END
END
CLOSE EDI
DEALLOCATE EDI
May 12, 2008 at 6:16 am
How did you intend to place the results in a file? Also, can you use xp_CmdShell or not?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2008 at 8:02 am
Jeff,
Just i am thinking, will Tally table be a good replacement for this cursor ?
karthik
May 12, 2008 at 10:38 am
I call that cursor proc from another proc "sFileCreate". This proc actually calls that cursor proc then takes the output and creates a txt file:
Create Proc sFileCreate
as
declare
@HR int, --HResult
@OutputFile varchar(100),
@ObjectID int,
@FileExistsDirectory varchar(100),
@FileExists int,
@FileObjectID int,
@FilePath varchar(100),
@ArchiveFilePath varchar(100),
@Body text, --varchar(8000),
@EmailBody varchar(1000),
@Error int,
@ControlNumber varchar(15),
@ArchiveFileDate char(8)
SET NOCOUNT ON
exec sCursor @Body out,@Error out,@ControlNumber out
--IF len(@Body) as BodyCharCount
IF @Error > 0
BEGIN PRINT 's_Cursor Errored Terminating File creation. Error: ' + @Body RETURN END
IF @Body = 'No Records'
BEGIN update t_MTSTM_EDI_210_BatchControl set BatchStatus = 'File Not Created' where ID = @ControlNumber
PRINT 'No Records in Body. File Not Created'
GoTo ArchiveFile
END
------------------------------ Create 210.txt File -----------------------------
---Create Object ----
exec @HR = sp_OACreate "Scripting.FileSystemObject",@ObjectId OUT
IF @HR <> 0
BEGIN Exec sp_DisplayOAErrorInfo @ObjectId,@HR RETURN END
--File Path
SET @FIlePath = '\\MachineName\c:\210.txt'
--============ Check If File Exists Before Creating New File ============
exec master.dbo.xp_FileExist @FilePath,@FileExists out
IF @FileExists = 1
BEGIN
update t_MTSTM_EDI_210_BatchControl set BatchStatus = 'File Not Created' where ID = @ControlNumber
PRINT 'File Exists'
RETURN
END
--Create File Object from "FileSystemObject"
exec @HR = sp_OAMethod @ObjectID,'CreateTextFile',@FileObjectID out,@FilePath
--Create Write Object From "File Object"
exec @HR = sp_OAMethod @FileObjectId,'WriteLine',null,@Body
--Destroy "FileSystemObject" Object
exec @HR = sp_OADestroy @ObjectID
--Destroy "File Object"
exec @HR = sp_OADestroy @FileObjectID
--============ Check If File Exists After Creating New File ============
exec master.dbo.xp_FileExist @FilePath,@FileExists out
IF @FileExists = 1
BEGIN
PRINT 'File Created Successfully'
update tBatchControl set BatchStatus = 'File Created' where ID = @ControlNumber
END
May 12, 2008 at 7:56 pm
karthikeyan (5/12/2008)
Jeff,Just i am thinking, will Tally table be a good replacement for this cursor ?
Hi Karthik,
I could be wrong, but I'm pretty sure the answer is "No". This is EDI code which makes XML look like a cake walk. Every set of related records must have a header and the format of the header is necessarily different than the detail records. Further, it's important that the header record be immediately above the detail records it represents. It's like having a million little DBIII tables in the form of a text file.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2008 at 8:02 pm
It would appear that you're trying to write everything as a single line using SP_OA... instead of trying to jamb everything into a single line of text, why not continue the trend of a loop? Populate a temp table with as many 4-8000 character lines you need (EDI 210 must be shorter than than) and the loop the SP_OA writelines to build a file as large as you need. No need to bother with the TEXT datatype at all.
Of course, I could be wrong 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2008 at 11:28 pm
Jeff, I believe you're ovecomplicationg the problem.
I would suggest:
1) create simple ReportTable
(
SeqNo smallint NOT NULL PRIMARY KEY,
ReportString varchar(8000) NULL
)
2) Populate this table with lines to be reported, lines must be built as they need to appear in report, I mean with formatting, delimiters, etc.
3) Run simple bcp (or osql) call from whatever invokes this report (xp_cmdshell, Windows application, web-service, etc.) as
SELECT ReportString FROM ReportTable ORDER BY SeqNo
and direct report to a file.
4) Report success to the boss.
🙂
_____________
Code for TallyGenerator
May 12, 2008 at 11:35 pm
Sergiy (5/12/2008)
Jeff, I believe you're ovecomplicationg the problem.I would suggest:
1) create simple ReportTable
(
SeqNo smallint NOT NULL PRIMARY KEY,
ReportString varchar(8000) NULL
)
2) Populate this table with lines to be reported, lines must be built as they need to appear in report, I mean with formatting, delimiters, etc.
3) Run simple bcp (or osql) call from whatever invokes this report (xp_cmdshell, Windows application, web-service, etc.) as
SELECT ReportString FROM ReportTable ORDER BY SeqNo
and direct report to a file.
4) Report success to the boss.
🙂
Yep... you're right... I was temporarily blinded by all the cursors and the use of SP_OA. :sick: Thanks for the correction, Sergiy. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply