Hi community,

I'm trying to avoid that error that only occurs when I'm using WF8105 (I don't have it under WF7705M) when using a SQL passthru:

Task error: IBFSException 6000: Too many messages

I disabled the ECHO and SQLTRACE but nothing change, always got the error. Seems that I'm still missing something.

Sample code:

-SET &ECHO = OFF;
SET TRACEOFF  = ALL

ENGINE SQLMSS SET DEFAULT_CONNECTION MYCON
SQL SQLMSS
INSERT INTO [dbo].[MYTABLE]
           ([FLD1],[FLD2])
     VALUES ('VAL1', 'VAL2');
SQL COMMIT;
END
-RUN

I'm trying to remove this message which is the cause of the above error and repeated at each insert (because I have a loop for the INSERT):

 (FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND : 1/INSERT

Any help will be greatly appraciated

This message has been edited. Last edited by: MartinY,

Can't you combine those INSERT's into single PASSTHRU statement? Seems a bit of a waste of resources to INSERT line-by-line...

INSERT INTO [dbo].[MYTABLE]
           ([FLD1],[FLD2])
     VALUES ('VAL1', 'VAL2'),
           ('VAL3', 'VAL4'),
           ('VAL5', 'VAL6');

That would reduce the number of ROWS AFFECTED lines and improve performance at the same time.
That is, if MS SQL can do it this way.

If the message you are referring to is the rows affected, then use:

SET EMGSRV=OFF/ON

Wep5622 : unfurtunately I can't because I need to read the row's data (and then format...) from somewhere else to then insert it in table.

dhagen : it is working with your solution, thanks.

MS SQL has a BULK INSERT statement that lets you do the inserts from a file. Is that something you could apply to your case?

To give some idea, we use it to create temp tables we need to populate a bunch of graphs from text files with several columns of data:

-IF &KeepTable NE 0 THEN GOTO :KeepTable;
-SET &TableName = 'g_' || &FileName.&First;
-SET &TmpTableName0 = '"#' || &TableName || '"';
-SET &TmpTableName = TRUNCATE(&TmpTableName0);
-SET &KeepTable = 1;

ENGINE SQLMSS SET DEFAULT_CONNECTION XXX

SQL SQLMSS
IF OBJECT_ID('dbo.&TmpTableName', 'U') IS NOT NULL
	DROP TABLE dbo.&TmpTableName;
END
-RUN

SQL SQLMSS
CREATE TABLE dbo.&TmpTableName (
	seq INT IDENTITY(1, 1) NOT NULL
,	legend VARCHAR(45) NULL
,	part_no VARCHAR(40) NULL
-REPEAT :FLDLP1 FOR &I FROM 1 TO &N;
,	field_&I FLOAT
-:FLDLP1
);
END
-RUN

-:KeepTable

-* Import this data-file into our table
SQL SQLMSS
BULK INSERT dbo.&TmpTableName
 FROM '&FilePath.&Index'
 WITH (
	FIRSTROW = &FirstDataRow,
	FORMATFILE = '&FormatURI'
 );
END
-RUN

I'll look at this option Wep, thanks.