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
 Other Forums
 ClearTrace Support Forum
 Trace file read exception on large statement

Author  Topic 

aschiffl
Starting Member

2 Posts

Posted - 2012-02-14 : 18:17:48
I am getting this exception

Unhandled Exception: ClearData.ClearTrace.SqlParsingException: Infinite loop in FixComments (2008.38.0.0)
at ClearData.ClearTrace.Helper.FixComments(String sql)
at ClearData.ClearTrace.Helper.NormalizeSqlText(String sql, String eventClass, Int32 spid, Boolean spreadCsv)
at ClearData.ClearTrace.SqlTrace.ProcessFiles(BackgroundWorker worker, DoWorkEventArgs eventArgs)
at ClearData.ClearTrace.ConsoleApp.Program.Main(String[] args)

The SQL statement is huge and formed like this:

declare @p1 PReportType
...
... massive amounts of ...
...
insert into @p1 values(...)
insert into @p2 values(...)
insert into @p3 values(...)
insert into @p4 values(...)
insert into @p5 values(...)
...
exec Component.spSubmitData @PReports=@p1,@SReports=@p2,@PSettings=@p3,@Params=@p4,@Evt=@p5

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2012-02-23 : 08:30:58
Hmmm. I think that loop will break if it hits more than 1000 statements in a batch. Let me look at increasing that in a future release. Or maybe just skipping that statement.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2012-02-23 : 08:38:45
Hmmm. I looked at the code at the loop is at 1 million. Meaning it found a million things it thinks are comments. Can you post some more? How big is this statement?

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

aschiffl
Starting Member

2 Posts

Posted - 2012-03-06 : 19:41:45
quote:
Originally posted by graz

Hmmm. I looked at the code at the loop is at 1 million. Meaning it found a million things it thinks are comments. Can you post some more? How big is this statement?



Sorry - I can't post the statement or give you the trace file as it contains private data. But I can assure you that it is a "monster" statement.

My recommendation would be to make the code more resilient and just ignore such large traces - i.e. if encountered, pop a warning and move on to the next row (currently it fails the application and aborts the loading process).

I would be be happy to try out a private binary on the same file to check if any code updates resolve the issue.
Go to Top of Page

Roberto Machado
Starting Member

3 Posts

Posted - 2012-03-16 : 15:47:55
I've seen the same things. I con confirm this is due to the length of textdata field. Some people submit load scripts using multiple insert commands. I noticed the any batch longer than a few hundred kbytes cause the problem. There is a work around, though. Load the tracefile to a table, truncate the big value, open the table with profiler, save it as a trace file and re-import it. I believe the write thing to do would be to truncate and not skip it indeed.

Best regards, Roberto
Go to Top of Page

spe109
Starting Member

1 Post

Posted - 2013-02-18 : 06:48:44
Hi,
I've run into this error today. The problem is caused by some horrible SQL statement the users have issued.
The statement goes something like this:-
Select code1, code2
From codes_Table
Where code1 in (what follows then is a comma separated list of codes '12121212' all 48,000 of them).

I know its a terrible bit of sql and I wouldn't expect ClearTrace to be able to process it. I think what would be best if it just dumped it out to its log file and carried on processing the trace file.

At the moment it sometimes crashes out with an exception and sometimes tries to pop up a message box.

Thanks for a great tool.
Paul.
Go to Top of Page
   

- Advertisement -