Parameters limited to 900 characters

Question

Parameters limited to 900 characters

BobWman

SSC Rookie

Points: 41
More actions
January 18, 2006 at 2:41 pm

#91385

I am using VB 2005 to populate a SQL Server 2000 table with text columns and some columns with data type of varchar(1000). The data is inserted via a stored procedure, using several parameters. Everything works fine as long as the parameter strings are <= 900 characters. But, if I try to pass a string > 900 characters, the field will be empty for that record.
I don't see anything in the documentation about maximum sizes for parameters. What's going on?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

Not sure if the problem is related to the 'text data' attribute or the varchar(1000) attributes or both! Here are a couple of things to look out for:

1. Make sure that all the parameters in the VB code and the SP script are declared and mapped correctly;

2. Execute the statement and verify that it is not 900:

select objectproperty(object_id('[Table Name]'),'TableTextInRowLimit')

You will need to replace [Table Name] with the name of the table that you are troubleshooting.

Happy troubleshooting!

BobWman SSC Rookie Points: 41 More actions · Answer 2

Thanks for the response. I had misdiagnosed the problem. The data was saved, but didn't show up when using Enterprise Manager to view the data. Apparently EM has a 900 character limit on what data it will display - I didn't know that. Always learning...

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Guess the other lesson that you should add to this is to use Query Analyzer as much as possible...

**ASCII stupid question, get a stupid ANSI !!!**

BobWman SSC Rookie Points: 41 More actions · Answer 4

Yes, the data shows up in Query Analyzer. The reason for using EM is that it allows you to expand the vertical size of each row and easily view data with CrLf. You can't do that with Query Analyzer. So, both have their uses (and limitations - documented or not).

Thanks!

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Actually, if you set the option in QA under "Tools - options - Results" to display as "Results to text", you could view the data....

I always use this option specially if I want to look at something from - eg: - syscomments table...where the text column for some of the stored procedures can be really lengthy..

**ASCII stupid question, get a stupid ANSI !!!**