Crazy Stored Procedure ADO Varchar Problem

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

SQL Server Development (2000)

Crazy Stored Procedure ADO Varchar Problem

Author

Topic

filf
Yak Posting Veteran

67 Posts

Posted - 2003-09-23 : 12:54:57

Well I think it is crazy - I have tryed everything to get round this one to no avail - so here I am asking the pros.

Using ado to append parameters to a command obj I am passing a load of vars to a stored proc which is performing an update. But for one particular field this is giving all sorts of crazy problems.

Namely that trying to insert 770 chars into a varchar(8000) field I am receiving odd errors. It has started to do my head in so much that I stepped away from ado etc and went via a query analyzer.

Can anyone explain why the following is happening?
select len('12345678901234567890123456789012345678901234567890123456789012345678901234567890 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890')
go

result: 312

update tbl_kb_articles set kb_article_cont = '12345678901234567890123456789012345678901234567890123456789012345678901234567890 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890' where kb_id = 27
go
Result: Server: Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8144 which is greater than the allowable maximum of 8060.
The statement has been terminated.

select len(kb_article_cont), kb_article_cont from tbl_kb_articles where kb_id = 27

Result: 10
1234567890

I am seriously stumped on this one
Thanks. filf

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 12:58:15

The problem is that the entire row size is larger than 8060. You can't do that unless you use text columns. So the problem isn't with the individual column but with the entire row. Add up all of the bytes in your row and you'll see the problem.

Tara

filf
Yak Posting Veteran

67 Posts

Posted - 2003-09-24 : 04:41:39

Tara.

Thanks for this - classic case of not studying the err msg closely enough. This has now solved the issue for me. Will be aware of this max row size in the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources