problem with parameter max length of 128

[mbcmike]

I have a parameter that I am trying to pass to a stored procedure. It happens to be a very long parameter. I get an error saying it is too long and can be a maximum length of 128. Is there a way around this?

 

[gmmastros]

In the stored procedure, you identify the parameters to it, like so...

Create Procedure ProcedureName
@Param1 Integer,
@Param2 DateTime,
@Param3 VarChar(128)
AS
....

By increasing the size of the varchar, you should be able to send more data to it. The maximum length for a varchar variable is 8000.

Hope this helps.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mbcmike]

Its already set to varchar(8000).

All the procedure does is:

CREATE procedure dbo.procedure_name
(
@p_sql_line varchar(8000)
)
as
DECLARE
@SQL varchar(8000)
set @SQL = @p_sql_line
exec (@SQL)
go

 

[gmmastros]

The VALUE of the parameter passed to this must be a valid SQL statement and/or another stored procedure call. Is it possible that the parameter represents a stored procedure call, but that stored procedure has a varchar(128) as a parameter?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Try running this query... It may help you find the problem.

Select * 
From   INFORMATION_SCHEMA.PARAMETERS
Where  Data_Type = 'varchar'
       And Character_Maximum_Length = 128

This should return all parameters within all stored procedures where the data type is varchar and the max length is set to 128.

Hope this helps.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mbcmike]

Well....heres the deal. The parameter is a select statement for a table. I didn't think this information would help, but now it looks like it might....

I have an asp page that sends a parameter (the sql select statement) to an asp.net page (the generic one that our business uses for reports) that sends the parameter to a crystal report. The report then calls a procedure with this paramter.

Now here is where I think the problem may be...If I leave the paramter string intact it causes an error going from the asp page to the .net page because url's can not have certain characters(>>,= etc.) so I replaced those characters with a word(such as = becomes equalsymbol) on the asp page. So the paramter should be coming into the stored procedure through the crystal report with the symbols replaced. Then in the stored procedure I'm doing this:set @SQL = replace(@p_sql_line,"equalsymbol","=") . And then I execute @SQL.

Here is something else: If I run this procedure in SQL with the parameter as it should be (without the replace) it runs fine. And it is over 128 length. However, if I use the replace words, I get the error.

I was trying to figure out this problem without mentioning the crystal report and asp.net page because this is a SQL forum...