Passing parameter to SQL Server stored procedure

[JimP]

I'm passing a string of of concatenated order values (comma separated) to a
sp.

If the length of the string exceeds the max length allowed by SQL Server, do
I have other options.

This is documented to be about 8000 bytes. I'm able to pass 4000 characters,
when I need to pass twice that amount.

 

[Jason Lepack]

I would question the reason for this. It's more than likely that you
are being hampered by a database design issue.

Cheers,
Jason Lepack

 

[Robert Morley]

Are you using a varchar or an nvarchar as your parameter type? nvarchar
would reduce it to 4000 (8000 bytes = 4000 unicode characters).

Failing that, I believe you can switch the type to text/ntext without
causing any undue problems.



Rob

 

[JimP]

Thanks for the reply.

I've tried varchar(8000), nvarchar(8000), varchar(4000), nvarchar(4000),
text and ntext with identical results - the first 4000 characters are
returned as table entries

Changing to varchar(3999) or nvarchar(3999) truncates the results table by 1
character.

I probably need to check with the database administrator to find out more.

 

[Robert Morley]

Either the field you're writing to is limited to 4000 characters, something
"in-between" is, or there's some limitation I'm not aware of between Access
and SQL Server.

It's a long and unwieldy way to do it, but you might be best off trying to
run the SP by using a Connection.Execute command:

CurrentProject.Connection.Execute "MySP '" & strMyLongString & "'"

See if this works as expected or if it too gets truncated. If it's
truncated at exactly 4000 characters, then it's something server-side that's
causing the problem.


Rob

 

[JimP]

Not surprisingly, it was something I overlooked. There was a function in the
sp that referred to the same variable - where it had been declared as
varchar(4000).

This problem does not exist in SQL Server 2005 as there is now a
varchar(max) declaration that circumvents this limitation.

Unfortunately, this is a multi-user application where not all users are on
SQL Server 2005, so the 8000 byte limitation is still something that I need
to deal with.