extended stored procedures: is there a 256 character limit on

Bert,
For some system extended stored procedures, the parameters
are defined as varchar(256). For those, there is no way around
unless you rewrite the xp (not a good idea I think). For xp's you
write yourself, I'm not aware of any limit. Are you having a
particular problem you need help with?
Steve Kass
Drew University
Bert Szoghy wrote:

>Hello,
>On SQL Server 2000 (SP3), for extended stored procedures: is there a
>256 character limit on INPUT parameters?
>And if so, is there a way around that?
>Thanks,
>Bert
>Bert,
The thread you posted indicates there is a 256-character limit
for DBLib, not the SQL Server xp architecture. According to John
Kane in that thread:
Anil,
ESP or (Extended Stored Procs) can use the OLE-DB API and if you are using
the DBLib API you are cutting your own code's future as it is most likely
(un-confirmed) that Microsoft will drop, i.e.. remove the DB-Library API
from future SQL Server versions, such as SQL Server 2005 (Yukon).
See SQL Server 2000 BOL titles "Running Stored Procedures (OLE DB)",
"Extended Stored Procedure Architecture", "Programming Extended Stored
Procedures" and "Samples" and specifically the sample app xp-ODBC for more
info.
This is not a SQL Server limitation, but a limitation of the old DBLib
interface
only. Rewriting the xp to use a more modern interface, as John suggested,
is probably what you need to do.
SK
Bert Szoghy wrote:

>Steve Kass <skass@.drew.edu> wrote in message news:<OJxPTgSSFHA.3716@.TK2MSFT
NGP14.phx.gbl>...
>
>Hello,
>Thank you for your responses.
>I have an extended stored proc DLL with C++ code which just looks fine
>to me, which it refuses to give me more than 256 characters.
>I found the following reference before posting here which made me
>suspect it was a SQL Server limitation:
>http://groups.google.ca/groups?hl=e...K2MSFTNGP15.phx
.gbl%26rnum%3D1
>I am using a varchar(8000) in the trigger calling the extended stored
>proc.
>I'm about to try stepping through the DLL but the code is a bit nasty
>and it would be nice if a SQL Server guru would point out a detail to
>tweak.
>Thanks guys!
>Thanks again,
>Bert
>

Expression Editor - avg problem

This is my data set

PersonIdType (varchar 20)

153 NewlyEnrolled

58 8

248 Enrolled

85723

I am trying to show an avg for all the numbers in the ‘Type’ field

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, CDbl(Fields!Type.value)))

I get the error “Input string was not in a correct format.”

If I use:

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, 1))

It works and it returns ‘1’

so the problem is CDbl(Fields!Type.value). i'm tring to convert it to a number so it can be used in the avg function.

any idea what I'm doing wrong?

CDbl returns a double from (typically) a floating point number.

How about trying out CInt or StrConv?

|||

CInt, Cdbl, Cdec, Csng.... all do the same thing.

StrConv is for string manipulation, cant use that to convert to a number

|||

What happens when you simply take out Cdbl?

I suppose you get an error stating that you can't average a varchar field?

Try this:

=CStr(CInt(Fields!Type.Value))

|||

What happens when you simply take out Cdbl?

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

=CStr(CInt(Fields!Type.Value))

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

I also tried

=CInt(CStr(Fields!Type.Value))

Input string was not in a correct format.

|||I just made a new field that held the numbers and did an average on that. Not what I wanted but it worked.

Expression Editor - avg problem

This is my data set

PersonIdType (varchar 20)

153 NewlyEnrolled

58 8

248 Enrolled

85723

I am trying to show an avg for all the numbers in the ‘Type’ field

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, CDbl(Fields!Type.value)))

I get the error “Input string was not in a correct format.”

If I use:

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, 1))

It works and it returns ‘1’

so the problem is CDbl(Fields!Type.value). i'm tring to convert it to a number so it can be used in the avg function.

any idea what I'm doing wrong?

CDbl returns a double from (typically) a floating point number.

How about trying out CInt or StrConv?

|||

CInt, Cdbl, Cdec, Csng.... all do the same thing.

StrConv is for string manipulation, cant use that to convert to a number

|||

What happens when you simply take out Cdbl?

I suppose you get an error stating that you can't average a varchar field?

Try this:

=CStr(CInt(Fields!Type.Value))

|||

What happens when you simply take out Cdbl?

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

=CStr(CInt(Fields!Type.Value))

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

I also tried

=CInt(CStr(Fields!Type.Value))

Input string was not in a correct format.

|||I just made a new field that held the numbers and did an average on that. Not what I wanted but it worked.