Help: 900 byte limit on stored procedure parameter?

[ZRexRider]

Hi,

I have a .NET application that I want to save the Config.EXE contents
to my SQL database for remote review/testing. This config file is
3700+ bytes long. I created a field in one of my tables with a VARCHAR
4800 and then created a stored procedure that receives a parameter
(also VARCHAR(4800).

However it fails to write anything if the length of the value that I
pass is anything greater than 900. If I pass exactly 900 characters or
less - the data is written to the field. If I pass 901 characters I
get nothing.

I'm suspicious since it is exactly 900. I seriously doubt it's some
limitation of MS-SQL so I need a nudge in the right direction.

Thanks

[SQL]

Re: Help: 900 byte limit on stored procedure parameter?

You probably have an index on that column, an index can not exceed 900
bytes


Denis the SQL Menace

http://sqlservercode.blogspot.com/

[ZRexRider]

Re: Help: 900 byte limit on stored procedure parameter?

Thanks Denis - you had my hopes up but nope - this field is not an
index.

I am able to cut and paste any size into this field through SQL
Enterprise Manager . It's just when I try to pass a chunk of data over
900 bytes through a strored procedure parameter.

I hate to build a direct SQL Update into my application but ......

[Tony Rogerson]

Re: 900 byte limit on stored procedure parameter?

Please post the stored procedure and the full CREATE TABLE definition -
including indexes, constraints etc...

Tony

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"ZRexRider" <jerryg@ptd.net > wrote in message
news:1144415792 .035582.134890@ z34g2000cwc.goo glegroups.com.. .[color=blue]
> Hi,
>
> I have a .NET application that I want to save the Config.EXE contents
> to my SQL database for remote review/testing. This config file is
> 3700+ bytes long. I created a field in one of my tables with a VARCHAR
> 4800 and then created a stored procedure that receives a parameter
> (also VARCHAR(4800).
>
> However it fails to write anything if the length of the value that I
> pass is anything greater than 900. If I pass exactly 900 characters or
> less - the data is written to the field. If I pass 901 characters I
> get nothing.
>
> I'm suspicious since it is exactly 900. I seriously doubt it's some
> limitation of MS-SQL so I need a nudge in the right direction.
>
> Thanks
>[/color]

[ZRexRider]

Re: 900 byte limit on stored procedure parameter?

Thanks Tony...

I appreciate your help. Just as I was about to do that however .....
gulp... what I failed to mention was I was trying to view these 900+
bytes usining Visual Studio.NET Server Explorer. Although Sever
explorer refuses to display ANYTHING in the column when there are 900+
bytes MS SQL Enterprise Manager does.

In other words - the data is there but just looks like it's not there
when using VisualStudio to view it.

This is an odd limitation but it appears that my table is designed ok
and my stored procedure was working just fine.

If I learn anything more I will repost to this thread.

[DickChristoph]

Re: Help: 900 byte limit on stored procedure parameter?

Hi

Have you checked the length of the parameter defined in the .Net
application?

--
-Dick Christoph
"ZRexRider" <jerryg@ptd.net > wrote in message
news:1144419858 .910381.155540@ g10g2000cwb.goo glegroups.com.. .[color=blue]
> Thanks Denis - you had my hopes up but nope - this field is not an
> index.
>
> I am able to cut and paste any size into this field through SQL
> Enterprise Manager . It's just when I try to pass a chunk of data over
> 900 bytes through a strored procedure parameter.
>
> I hate to build a direct SQL Update into my application but ......
>[/color]

[ZRexRider]

Re: Help: 900 byte limit on stored procedure parameter?

The parameters are fine. As I said earlier - everthing in my code and
stored procedures are working perfectly.

The problem was, for some odd reason when you use Visual Studio to view
the table it shows a blank field but if you look at the exact same
field using SQL Server Enterprise you see that the data really is
there.


So the "issue" turns out to be - Microsoft Visual Studio 2003 will not
display the contents of a varchar field that has more than 900 bytes.
And in my case, I tried to fix everything else!!


DickChristoph wrote:[color=blue]
> Hi
>
> Have you checked the length of the parameter defined in the .Net
> application?
>
> --
> -Dick Christoph
> "ZRexRider" <jerryg@ptd.net > wrote in message
> news:1144419858 .910381.155540@ g10g2000cwb.goo glegroups.com.. .[color=green]
> > Thanks Denis - you had my hopes up but nope - this field is not an
> > index.
> >
> > I am able to cut and paste any size into this field through SQL
> > Enterprise Manager . It's just when I try to pass a chunk of data over
> > 900 bytes through a strored procedure parameter.
> >
> > I hate to build a direct SQL Update into my application but ......
> >[/color][/color]

[ZRexRider]

Re: Help: 900 byte limit on stored procedure parameter?

Here's a thread from another guy who pulled his hair out over this...
FUN!

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/d7700619811dc19b/96770e1d6617279d?lnk=st&q=visual+studio+900+byte+varchar&rnum=1&hl=en#96770e1d6617279d

[Liang Zhang]

Re: Help: 900 byte limit on stored procedure parameter?

Yeah, I also found this problem of visual studio some time.

"ZRexRider" <jerryg@ptd.net > wrote in message
news:1144717149 .274781.120350@ u72g2000cwu.goo glegroups.com.. .[color=blue]
> The parameters are fine. As I said earlier - everthing in my code and
> stored procedures are working perfectly.
>
> The problem was, for some odd reason when you use Visual Studio to view
> the table it shows a blank field but if you look at the exact same
> field using SQL Server Enterprise you see that the data really is
> there.
>
>
> So the "issue" turns out to be - Microsoft Visual Studio 2003 will not
> display the contents of a varchar field that has more than 900 bytes.
> And in my case, I tried to fix everything else!!
>
>
> DickChristoph wrote:[color=green]
>> Hi
>>
>> Have you checked the length of the parameter defined in the .Net
>> application?
>>
>> --
>> -Dick Christoph
>> "ZRexRider" <jerryg@ptd.net > wrote in message
>> news:1144419858 .910381.155540@ g10g2000cwb.goo glegroups.com.. .[color=darkred]
>> > Thanks Denis - you had my hopes up but nope - this field is not an
>> > index.
>> >
>> > I am able to cut and paste any size into this field through SQL
>> > Enterprise Manager . It's just when I try to pass a chunk of data over
>> > 900 bytes through a strored procedure parameter.
>> >
>> > I hate to build a direct SQL Update into my application but ......
>> >[/color][/color]
>[/color]