Query exeeds maximum size - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Query exeeds maximum size

Author

Topic

davidiswift
Starting Member

5 Posts

Posted - 2012-08-07 : 07:03:17

We have inherited an application where one of the statements occasionally exceeds the maximum allowed size of 65,536.

The statement updates several tables wrapped within a BEGIN TRAN and COMMIT TRAN.

One of the updates (TLFILE) can update several thousand rows which have to be specified uniquely, for example
BEGIN TRAN
UPDATE RIFILE SET RITRNC=RITRNC+1
IF @@ERROR !=0 GOTO X1
UPDATE TLFILE SET TLBILL='B' WHERE TLSERN = 123651
IF @@ERROR !=0 GOTO X1
UPDATE TLFILE SET TLBILL='B' WHERE TLSERN = 123654
IF @@ERROR !=0 GOTO X1
UPDATE TLFILE SET TLBILL='B' WHERE TLSERN = 123657
IF @@ERROR !=0 GOTO X1
etc.etc. etc.

Some more updates to other tables...
IF @@ ERROR !=0 GOTO X1

GOTO X2
X1: ROLLBACK TRAN
GOTO X3
X2: COMMIT TRAN
X3:

There are several updates to different tables within the query but it is just the updates to TLFILE that make the query too big.

Does anyone know how I can get around this but still rollback if any errors occur?

I thought of batching the updates to TLFILE in blocks of 100 or so but I can't think how to rollback.

Any thoughts would be appreciated.
Regards
Dave

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-07 : 07:47:22

I can't think of a way to parcel it out in multiple chunks and still be in the same transaction, unless you open a transaction from the client side (which .Net does allow you to do http://msdn.microsoft.com/en-us/library/2k2hy99x(v=vs.71).aspx ). But, I am a little weary about it for a variety of reasons.

What you can do is pass the data as an array - for example, as a comma-separated string - to a stored proc and then parse it in the stored proc to do the updates. There are various ways to do this - see Sommarskog's blog here: http://www.sommarskog.se/arrays-in-sql-2000.html

One simple way (which may not perform very well because of the non-sargable predicate) is this:

CREATE PROCEDURE dbo.YourUpdateProc1
	@TLSERNS VARCHAR(8000)
AS
	UPDATE T SET
		TLBILL = 'B'
	FROM 
		TLFILE t
	WHERE
		','+@TLSERNS+',' LIKE '%,'+CAST(TLSERN AS VARCHAR(32))+',%'

You will of course need to open the transaction and do the error handling in the stored proc.

davidiswift
Starting Member

5 Posts

Posted - 2012-08-07 : 11:18:19

Thanks Sunitabek,

I have read a bit more into this subject and it seems I don't have to cram all the sql statement into a single sql transaction. Provided I issue a BEGIN TRAN at the front and check the error status after each update statement, I can split the updates into a number of SQLexec commands (we are using Visual Foxpro on the client side) and then depending if the error status is OK issue a COMMIT TRAN or if the error status is not OK issue a ROLLBACK TRAN right at the end. I presume because every user has their own sqlID, it must keep track.

What do you think?
Regards
Dave Swift

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-07 : 14:16:22

What you have described is what I was referring to as opening a client-side transaction. It has its disadvantages - if you keep the transaction open long, other clients may not be able to access the tables, you will keep it longer than necessary because of the multiple round-trips etc.

At the very least, try the comma-separated method that I showed in my previous post. All you have to do is create that stored proc, and call it with a comma separated list of TLSERNs to test it. It will not be the most efficient, but it will certainly be faster than the current approach you are using and the client side transaction approach.

davidiswift
Starting Member

5 Posts

Posted - 2012-08-08 : 09:31:48

Hi

I have tried your example and it works.

However, all these examples seem to use VARCHAR(8000) where 8000 characters is the maximum allowed. The original problem was that the query was going over 65,536 characters. Each TLSERN number is 6 digits, so if we include the comma between them (so 7 digits), that would allow (8000/7) a maximum of 1142 rows. I think that possibly that might not be enough in some circumstances.

I have tried using TEXT instead of VARCHAR(8000) but when I do a CAST(TLSERN TEXT), it does not allow it.

I have found this example [url]http://www.codeproject.com/Articles/5077/Passing-an-array-or-DataTable-into-a-stored-proced [/url] but I am struggling how to create an image type structure for list of 6 digit numbers from Visual Foxpro.

Regards
Dave

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 10:31:18

I have so little familiarity with FoxPro and with SQL 2000 that I am unable to test the function in the link you posted, or offer any useful suggestions in that regard.

If the maximum number of tokens and their lengths are known in advance, you could conceivably use a finite number of parameters - with each holding some of the TLSERN ids. All except @TLSERNS1 may be null if there are only a few id's to pass in:

CREATE PROCEDURE dbo.YourUpdateProc1
	@TLSERNS1 VARCHAR(8000),
	@TLSERNS2 VARCHAR(8000) = NULL,
	@TLSERNS3 VARCHAR(8000) = NULL
	
AS
	UPDATE T SET
		TLBILL = 'B'
	FROM 
		TLFILE t
	WHERE
		','+@TLSERNS1+',' LIKE '%,'+CAST(TLSERN AS VARCHAR(32))+',%'
		OR 
		','+@TLSERNS2+',' LIKE '%,'+CAST(TLSERN AS VARCHAR(32))+',%'
		OR
		','+@TLSERNS3+',' LIKE '%,'+CAST(TLSERN AS VARCHAR(32))+',%'

davidiswift
Starting Member

5 Posts

Posted - 2012-08-08 : 11:58:43

Thanks sunitabeck, that looks like it would work.

I will test it out, hopefully tomorrow and let you know.

Regards
Dave

davidiswift
Starting Member

5 Posts

Posted - 2012-08-09 : 06:34:54

Hi sunitabeck

Tested, all works fine. It's a bit of a kludge but it works!

Thanks for all your help on this, well done.

Regards
Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources