sqlstate 01000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sqlstate 01000

Author

Topic

jsinks
Starting Member

13 Posts

Posted - 2007-03-13 : 06:57:04

Hi;
I wrote a simple script in Sql Server Agent, now am not happy with the "[sqlstate 01000]" statement in the output.

does anyone know how i can elliminate this?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-13 : 07:03:03

show us your script

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jsinks
Starting Member

13 Posts

Posted - 2007-03-13 : 08:33:16

PART of the OUTPUT (i want to elliminate the statements in red):

Job 'UTI Data Transmission' : Step 4, 'Create ASCII File' : Began Executing 2007-03-13 10:42:33

INTS,2,,,20070115,001 [SQLSTATE 01000]
BEGIN,UTPORD,2.008,1 [SQLSTATE 01000]
PORDER,C19477,41086,U,000,A,,,,,103814.00,USD,,N7,,20030311,,BAILEY WILLIAM B,20050429,20050520,,,Nkana Main Mine Store P7,, [SQLSTATE 01000]
...............

Below is the script which is doing the printing:

set nocount on DECLARE @PO_COUNT DECIMAL(7),@PO_NO CHAR(25),@PO_ITEM_TYPE CHAR(1),@PO_ITEM_NO CHAR(4),@ORDER_VALUE DECIMAL(18,2), @STOCK_CODE CHAR(9),@PO DECIMAL(7),
@pline VARCHAR(300),@PO_ITEM_CNT DECIMAL(7),@ITEM_CNT DECIMAL(5),@p_expediting char(10)SET @PO_COUNT=(SELECT COUNT(*) FROM UTI_PORDER)SET @PO=@PO_COUNT SET @PLINE=(SELECT 'INTS,2,,,'+RTRIM(SUBSTRING(CONVERT(CHAR(4),DATEPART(yyyy,GETDATE())),1,2)
+CONVERT(CHAR(6),GETDATE(),12))+',001')PRINT @PLINE SELECT PO_NO INTO #P_ORDER FROM UTI_PORDER ORDER BY PO_NO WHILE @PO_COUNT>0 BEGIN SET @PO_NO=(SELECT TOP 1 PO_NO FROM #P_ORDER)SET @PLINE=(SELECT 'BEGIN,UTPORD,2.008,1') PRINT @PLINE SET @PLINE=(SELECT RTRIM(RECTYPE)+','+RTRIM(PO_NO)+','+'41086'+','+
RTRIM(ACTION_CODE)+','+RTRIM(REV_NO)+','+RTRIM(MODE)+','+RTRIM(RAISING_BRANCH)+','+RTRIM(RAISING_BRANCH_CODE)+','+RTRIM(EXPEDITING_BRANCH)+','+RTRIM(EXPEDITING_BRANCH_CODE)+','+RTRIM(CONVERT(CHAR(18),ORDER_VALUE))+','+RTRIM(CURRENCY)+','+RTRIM(SALE_TERMS)+','+RTRIM(EXPEDITING)+','+RTRIM(BPORDER_CLASS)+','+RTRIM(RAISED_DATE)+','+RTRIM(USERID)+','+RTRIM(BUYER_NAME)+','+RTRIM(READY_DATE)+','+RTRIM(REQ_ON_SITE)+','+RTRIM(RAISED_CLIENT_CODE)+','+RTRIM(RAISED_CLIENT_NAME)+','+RTRIM(END_CONSUMER)+','+RTRIM(E_SHIP_DATE)+','
+RTRIM(L_SHIP_DATE) FROM UTI_PORDER WHERE PO_NO=@PO_NO)PRINT @PLINE SET @PLINE=(SELECT RTRIM(RECTYPE)+','+RTRIM(PO_NO)+','+'41086'+','+RTRIM(SUPPLIER_CLIENT_CODE)+','+RTRIM(SUPPLIER_NAME)+','+RTRIM(SUPPLIER_COMMERCIAL_PERSON)+','+RTRIM(SUPPLIER_COMMERCIAL_EMAIL_ADDRESS)+','+
RTRIM(SUPPLIER_COMMERCIAL_FAX_NUMBER)+','+RTRIM(SUPPLIER_COMMERCIAL_PHONE_NUMBER)+','+RTRIM(SUPPLIER_TECHNICAL_CONTACT_PERSON)+','+RTRIM(SUPPLIER_TECHNICAL_EMAIL_ADDRESS)+','+ RTRIM(SUPPLIER_TECHNICAL_FAX_NUMBER)+','+RTRIM(SUPPLIER_TECHNICAL_PHONE_NUMBER)+','+RTRIM(SUPPLIER_ADDRESS_LINE_1)+','+RTRIM(SUPPLIER_ADDRESS_LINE_2)+','
+RTRIM(SUPPLIER_ADDRESS_LINE_3)+','+RTRIM(SUPPLIER_ADDRESS_LINE_4)+','+RTRIM(SUPPLIER_ADDRESS_LINE_5)FROM UTI_POSUPP WHERE PO_NO=@PO_NO) PRINT @PLINE SET @PO_ITEM_CNT=(SELECT COUNT(*) FROM UTI_POLINE WHERE PO_NO=@PO_NO) SET @ITEM_CNT=0 WHILE @PO_ITEM_CNT > 0 BEGIN SET @ITEM_CNT=@ITEM_CNT+1 IF LEN(@ITEM_CNT)=1 SET @PO_ITEM_NO='00'+CONVERT(CHAR(1),@ITEM_CNT)
ELSE SET @PO_ITEM_NO='0'+CONVERT(CHAR(2),@ITEM_CNT)IF (SELECT COUNT(*)FROM UTI_POLINE WHERE PO_NO=@PO_NO AND LINE_NO=@PO_ITEM_NO)!=1 BEGIN SET @ITEM_CNT=@ITEM_CNT+1 IF LEN(@ITEM_CNT)=1 SET @PO_ITEM_NO='00'+CONVERT(CHAR(1),@ITEM_CNT) ELSE SET @PO_ITEM_NO='0'+CONVERT(CHAR(2),@ITEM_CNT) END SET @PLINE=(SELECT RTRIM(RECTYPE)+','+RTRIM(PO_NO)+','+'41086'+','+RTRIM(LINE_NO)+','+'STY'+','+RTRIM(ITEM_REF_1)+','+'PNO'+','+RTRIM(ITEM_REF_2)+',,,,,'+RTRIM(LINE_DESCRIPTION)+','+
RTRIM(QTY_UNITS)+','+RTRIM(CONVERT(CHAR(13),QTY_ORDERED))+','+RTRIM(CONVERT(CHAR(13),QTY_RECEIVED))+','+RTRIM(EXPECTED_DATE)+','+RTRIM(ONSITE_DATE)+','+LTRIM(WORKS_VALUE)+','+RTRIM(CONVERT(CHAR(18),NETT_VALUE))+','+LTRIM(FOB_VALUE)+','+RTRIM(RAISED_CLIENT_NAME)FROM UTI_POLINE WHERE PO_NO=@PO_NO AND LINE_NO=@PO_ITEM_NO)PRINT @PLINE SET @PO_ITEM_CNT=@PO_ITEM_CNT-1 END DELETE FROM #P_ORDER WHERE PO_NO=@PO_NO
SET @PO_COUNT=@PO_COUNT-1 END SET @PLINE=(SELECT 'INTE,'+RTRIM(LTRIM(CONVERT(CHAR(7),@PO))))PRINT @PLINE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources