row limit in MSSQL

There is at present (as we accidently found) limitation of MSSQL to return per row maximally 8060 bytes. Message like this comes: "Cannot create a row of size 8279 which is greater than the allowable maximum of 8060".
My questions are :
- Is there any way how to pass it? If I split into more tables (as I have it now) and ask for result where these tables are connected over any ID the result is the same. If I use stored procedures it seems to be ok. Any other idea?
- Will be this ok in SQL server 2005?There is no way to overcome this limit in SQL 2000 other than to split the one table into multiple tables (as it seems you have already done).

Another workaround might be to take some of your longer varchar fields and convert them into text; however, you will lose some functionality (searching and indexing) if you choose this option.

Regards,

hmscott

There is at present (as we accidently found) limitation of MSSQL to return per row maximally 8060 bytes. Message like this comes: "Cannot create a row of size 8279 which is greater than the allowable maximum of 8060".
My questions are :
- Is there any way how to pass it? If I split into more tables (as I have it now) and ask for result where these tables are connected over any ID the result is the same. If I use stored procedures it seems to be ok. Any other idea?
- Will be this ok in SQL server 2005?|||Actually, the 8060 byte limit is for storing rows in a table. Result set rows can be many times larger than 8060 bytes.

-PatP|||Actually, the 8060 byte limit is for storing rows in a table. Result set rows can be many times larger than 8060 bytes.

-PatP
I seems to be different for me. I have row size in table roughly 5k but I need to connect more tables. Than (during SELECT command) this error comes.|||Does someone know if this will be fixed in MS SQL 2005 ?|||Try this one on for size, it generates lots of 16 Kb+ rows in the result set.
CREATE TABLE Sladky0 (
pk INT IDENTITY PRIMARY KEY (pk)
, junque00 CHAR(255) NOT NULL DEFAULT '00'
, junque01 CHAR(255) NOT NULL DEFAULT '01'
, junque02 CHAR(255) NOT NULL DEFAULT '02'
, junque03 CHAR(255) NOT NULL DEFAULT '03'
, junque04 CHAR(255) NOT NULL DEFAULT '04'
, junque05 CHAR(255) NOT NULL DEFAULT '05'
, junque06 CHAR(255) NOT NULL DEFAULT '06'
, junque07 CHAR(255) NOT NULL DEFAULT '07'
, junque08 CHAR(255) NOT NULL DEFAULT '08'
, junque09 CHAR(255) NOT NULL DEFAULT '09'
, junque0a CHAR(255) NOT NULL DEFAULT '0a'
, junque0b CHAR(255) NOT NULL DEFAULT '0b'
, junque0c CHAR(255) NOT NULL DEFAULT '0c'
, junque0d CHAR(255) NOT NULL DEFAULT '0d'
, junque0e CHAR(255) NOT NULL DEFAULT '0e'
, junque0f CHAR(255) NOT NULL DEFAULT '0f'
)

CREATE TABLE Sladky1 (
pk INT NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
, junque10 CHAR(255) NOT NULL DEFAULT '10'
, junque11 CHAR(255) NOT NULL DEFAULT '11'
, junque12 CHAR(255) NOT NULL DEFAULT '12'
, junque13 CHAR(255) NOT NULL DEFAULT '13'
, junque14 CHAR(255) NOT NULL DEFAULT '14'
, junque15 CHAR(255) NOT NULL DEFAULT '15'
, junque16 CHAR(255) NOT NULL DEFAULT '16'
, junque17 CHAR(255) NOT NULL DEFAULT '17'
, junque18 CHAR(255) NOT NULL DEFAULT '18'
, junque19 CHAR(255) NOT NULL DEFAULT '19'
, junque1a CHAR(255) NOT NULL DEFAULT '1a'
, junque1b CHAR(255) NOT NULL DEFAULT '1b'
, junque1c CHAR(255) NOT NULL DEFAULT '1c'
, junque1d CHAR(255) NOT NULL DEFAULT '1d'
, junque1e CHAR(255) NOT NULL DEFAULT '1e'
, junque1f CHAR(255) NOT NULL DEFAULT '1f'
)

CREATE TABLE Sladky2 (
pk INT NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
, junque20 CHAR(255) NOT NULL DEFAULT '20'
, junque21 CHAR(255) NOT NULL DEFAULT '21'
, junque22 CHAR(255) NOT NULL DEFAULT '22'
, junque23 CHAR(255) NOT NULL DEFAULT '23'
, junque24 CHAR(255) NOT NULL DEFAULT '24'
, junque25 CHAR(255) NOT NULL DEFAULT '25'
, junque26 CHAR(255) NOT NULL DEFAULT '26'
, junque27 CHAR(255) NOT NULL DEFAULT '27'
, junque28 CHAR(255) NOT NULL DEFAULT '28'
, junque29 CHAR(255) NOT NULL DEFAULT '29'
, junque2a CHAR(255) NOT NULL DEFAULT '2a'
, junque2b CHAR(255) NOT NULL DEFAULT '2b'
, junque2c CHAR(255) NOT NULL DEFAULT '2c'
, junque2d CHAR(255) NOT NULL DEFAULT '2d'
, junque2e CHAR(255) NOT NULL DEFAULT '2e'
, junque2f CHAR(255) NOT NULL DEFAULT '2f'
)

CREATE TABLE Sladky3 (
pk INT NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
, junque30 CHAR(255) NOT NULL DEFAULT '30'
, junque31 CHAR(255) NOT NULL DEFAULT '31'
, junque32 CHAR(255) NOT NULL DEFAULT '32'
, junque33 CHAR(255) NOT NULL DEFAULT '33'
, junque34 CHAR(255) NOT NULL DEFAULT '34'
, junque35 CHAR(255) NOT NULL DEFAULT '35'
, junque36 CHAR(255) NOT NULL DEFAULT '36'
, junque37 CHAR(255) NOT NULL DEFAULT '37'
, junque38 CHAR(255) NOT NULL DEFAULT '38'
, junque39 CHAR(255) NOT NULL DEFAULT '39'
, junque3a CHAR(255) NOT NULL DEFAULT '3a'
, junque3b CHAR(255) NOT NULL DEFAULT '3b'
, junque3c CHAR(255) NOT NULL DEFAULT '3c'
, junque3d CHAR(255) NOT NULL DEFAULT '3d'
, junque3e CHAR(255) NOT NULL DEFAULT '3e'
, junque3f CHAR(255) NOT NULL DEFAULT '3f'
)

DECLARE
@.loop INT
, @.pk INT

SET @.loop = 10

WHILE 0 < @.loop
BEGIN
INSERT INTO Sladky0 (junque00) VALUES (DEFAULT)
SET @.pk = @.@.identity
INSERT INTO Sladky1 (pk) VALUES (@.pk)
INSERT INTO Sladky2 (pk) VALUES (@.pk)
INSERT INTO Sladky3 (pk) VALUES (@.pk)
SELECT @.loop = @.loop - 1
END

SELECT *
FROM Sladky0
JOIN Sladky1 ON (Sladky1.pk = Sladky0.pk)
JOIN Sladky2 ON (Sladky2.pk = Sladky0.pk)
JOIN Sladky3 ON (Sladky3.pk = Sladky0.pk)-PatP|||ok , thanks|||I had a bit different SQL command in my application. I have join done over where condition and used DISTINCT in command.
But I found if I use DISTICNT keyword before that this error comes even with your offered SQL command with JOINs. So it seems to me different with DISTINCT or not - but why? So I cannot use keyword distinct for bigger result over tables - I guess.

(
SELECT DISTINCT *
FROM Sladky0
JOIN Sladky1 ON (Sladky1.pk = Sladky0.pk)
JOIN Sladky2 ON (Sladky2.pk = Sladky0.pk)
JOIN Sladky3 ON (Sladky3.pk = Sladky0.pk)
) doesn't work|||Avoid using DISTINCT this way (it implicity creates a table), use a GROUP BY instead.

-PatP|||Does it mean that it is better to mention all columns in GROUP BY statement than use DISCTINCT expression? So I have very long statements. To mention only some of them is not possible I think.|||Listing every returned column in a GROUP BY is the only was I know to avoid creating the work table that is created by DISTINCT.

-PatP|||I have tryied it - it really works fine. Thanks|||I know that it is something of a pain to have to deal with the problem by changing your code, but the designers made some trade off decisions in the query engine that work very well for 99.9% of the queries, but clobber queries like yours. It is annoying to have your code be the "lucky" one that needs to be changed, but at least the change is better than waiting for another database upgrade (at least in most cases)!

-PatP|||I agree with you. I am glad that I have solution for now for MSSQL server.
In our application I try to have as much as possible common code for MySQL (ODBC, native), MSSQL (ODBC), ORACLE(ODBC, native),... but you can imagine a lot of different parts.|||Yukon allows rows to span across multiple pages.