SQL Server 2000 Problem

[Sorrido]

Hey Guys, I'm a developer without a DBA who is having an odd problem with SQL Server 2000, and was hoping someone here could shed some light on what is going on.

I'm trying to insert a fairly lengthy VarChar (around 6500 characters) into a VarChar column in SQL Server 2000 using a stored procedure. Now, the size is set to 8000 for both the column and the variable in the stored proc.

When the insert of the lengthy string happens, I get an implicit conversion error (from nText to VarChar). The insert works just fine when other smaller strings are used, and I've already parsed the string out for odd characters.

I've discovered that if I cut the string to this one point, the insert works, but if I add more text (even just typing plain text into the editor field), the error occurs. It has to be a size issue, but I'm at a loss because the size is way below the published max size for the type.

I found an article on MSDN that listed the error I'm getting (

http://support.microsoft.com/kb/928965),

and says that it's better not to have any varchar field in SQL Server 2000 that's larger than 4000 characters... but, isn't 8000 characters the max? I've found countless websites that list 8000 as the max, and seem to use this without issue.

Can someone shed some light on what size a varchar field should REALLY be for SQL Server 2000 to behave? Is it 8000 or is it 4000, and if it's 4000, why? The published max size for varchar in SQL Server 2000 is 8000, so why is it better to keep it half that? I'm confused.

Of course, I'm often confused.

Thanks to anyone who can answer this!

 

[gmmastros]

Often times you will see people recommend 4000 characters because that is the max length for [!]n[/!]varchar. The [!]n[/!] versions for string types nText, nVarChar, and nChar allow you to store unicode characters. Unicode requires 2 bytes per character whereas ASCII stores 1 byte.

Tables are limited to 8060 bytes per row.

varchar limit is 8000 characters
[!]n[/!]varchar limit is 4000 character (8000 bytes).

You will run in to serious problems with your application if you have defined a table that allows you to store more than 8060 bytes per row. To determine this, add up the storage requirements for each column. If it exceeds 8060 bytes, then you need to redesign your tables right now. Do not wait.

Make sense now?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Can you show the query you are using?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Sorrido]

Well, the article I linked to lists both nvarchar and varchar as being effected by the same issue, and states "make sure that varchar columns in a SQL Server 2000 database do not contain more than 4000 characters", which is what confused me. This is why I posted. I couldn't figure out why they would say that all varchar types should be no larger than 4000 characters.

As for the size of the table itself, technically it does allow for more than 8060 bytes per row. Do you think this is the reason it's erroring? The rest of the data for this particular row doesn't bring the whole count even close to 8060, though.

The table was designed a long time ago, and has been working fine up until now. A redesign is a good idea, I agree, but I doubt it will happen soon. Luckily, though, the app for this particular table has a very small user base and after several years of use only has 500 rows total.

 

[Sorrido]

I'm using a stored proc. It's fairly basic, but here are the relevant parts:

--The Variables
 @ID As Int
 ,@strAgendaTitle As VarChar(250)
 ,@strAgendaContent As VarChar(8000)  --This is the issue.
 ,@strOwner As VarChar(150)
 ,@strAgendaWhat As VarChar(250)
 ,@strAgendaWhere As VarChar(250)
 ,@strAgendaWhen As VarChar(250)
 ,@strAgendaWho As VarChar(250)
 ,@strIDEnteredBy As VarChar(10)
 ,@intIDAgendaTypeID As Int
 ,@dtAgendaDateRequested As DateTime
 ,@Return As VarChar(250) OUTPUT

--The statement
 INSERT INTO [GSM].[dbo].[usr_HelpDesk_MeetingAgendaItems]
 	   ([strAgendaTitle]
 	   ,[strAgendaContent]  -- This is the issue
 	   ,[strOwner]
 	   ,[strAgendaWhat]
 	   ,[strAgendaWhere]
 	   ,[strAgendaWhen]
 	   ,[strAgendaWho]
 	   ,[intIDEnteredBy] 
 	   ,[intIDAgendaTypeID]
 	   ,[dtAgendaDateRequested])
  VALUES
 	   (@strAgendaTitle
 	   ,@strAgendaContent
 	   ,@strOwner
 	   ,@strAgendaWhat
 	   ,@strAgendaWhere
 	   ,@strAgendaWhen
 	   ,@strAgendaWho
 	   ,@intIDEnteredBy --This is declared locally, and set using another statement.  This is unrelated to the problem.
 	   ,@intIDAgendaTypeID
 	   ,@dtAgendaDateRequested)

 

[gmmastros]

Stab in the dark.

[tt][blue]
VALUES
(@strAgendaTitle
,[!]SubString([/!]@strAgendaContent[!], 1, 8000)[/!]
,@strOwner
[/blue][/tt]

That microsoft knowledge base article made it seem like the SQL Native driver was converting the parameter to text. So... maybe using substring to convert it back to varchar(8000) will work. It's worth a shot.

Please let me know if this resolves your problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Sorrido]

@gmmastros - hmmmm, but if that were the case, then this stored proc should not never have worked, right? The stored proc works with everything except an extra long string in this particular field. Nevertheless, I'll give it a shot tomorrow. Thanks for the suggestion!

And I wanted to note that the insert works fine in 2005 with the extra long string. I know this because, unfortunately, my environment is set up so that test is in 2005, prod is in 2000. Yeah, I know, but it can't be helped right now.

 

[gmmastros]

Here's what I'm thinking....

First, to quote the article:

CAUSE

This problem occurs because the SqlClient provider converts the varchar or nvarchar data type to a text or ntext data type. However, you cannot convert a text or ntext data type to a varchar or nvarchar data type in SQL Server 2000.

You cannot convert text or ntext to varchar or nvarchar. That's true. But the SubString function works with the text data type. So, if the parameter was converted to text for you, then using substring to get the first 8000 characters should also work.

At least.... this seems reasonable. At least as reasonable as the SQL Client driver converting your parameters to text. Right? Honestly, I don't know if this will work, but it should be simple enough to make the change and then test.

>> this stored proc should not never have worked, right?

That would be a reasonable assumption. However, if the real problem here is a bug in the SQL Native driver. And the nature of the bug is such that any parameter larger than 4000 characters is converted to text, then, yeah... the proc would work with less than 4000 characters, but not with more.

Make sense?

Not really. But, since this is a bug, there may be creative way to work around too. I hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom