Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

How to overcome parameters limitation in store procedure?

Hi Experts,

I am experiencing a prob when sending parameter values from MS Access to SQL Store procedure, and I assume its due to a limit of characters (perhaps 255).

Does anybody knows of a way how to overcome this limitation?

FYI- FE is Access 2003 ADP SP3.
       BE is SQL Express 2005.
Avatar of Eric Sherman
Eric Sherman
Flag of United States of America image

In VBA a variable-length string can contain up to approximately 2 billion characters.

Declare a Variant variable in VBA and store your parameters in it.

Dim varMyVaraiable as Variant

ET
Avatar of bfuchs

ASKER

Hi,

Below is the code I use to assign & submit the parameters
    For i = 1 To 3
        sInputParam = "@intCategory int = " & i & ", " _
            & "@strFilter nvarchar(4000) = '" & strFinal & "'"
        With Me.Controls("EmpStatisticsSubFrm" & i).Form
            .InputParameters = sInputParam
            
            .RecordSource = "dbo.procStatistics"
            .Requery
        End With
    Next

Open in new window

Now up to a certain limit of characters (length of strFinal) it works fine, above that it doesn't.
Now up to a certain limit of characters (length of strFinal) it works fine, above that it doesn't.

Did you declare your sInputParam as a Variant before you used it??

Dim sInputParam As Variant


ET
Avatar of bfuchs

ASKER

I have that as string

Why do I need that declare it as variant?

In immediate window before sending it, I can see it holding the full contents of the filter string.
In VBA I always use the Variant type to declare a variable-length string which can contain up to approximately 2 billion characters.

ET
Avatar of bfuchs

ASKER

Actually just tried with variant, same results.
In immediate window before sending it, I can see it holding the full contents of the filter string.

If that is the case then the variable is not the problem ... do you agree??

ET
Avatar of bfuchs

ASKER

yes, I agree.
ASKER CERTIFIED SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

Hi Bit,

Actually I had a workaround for this issue like the following.

In FE if length of filter string is more then 250 then save the filter string into a table and just pass the word "Long" as parameter, then in the store procedure I check if the parameter is 'long' then I retreive it from the table..

This is how I got it to work in another instance,

However the reason I posted here is, perhaps someone knows of a patch in Access or a certain setting in SQL that would take care of this issue..

I know this solution (as is) limits for only one user at a time, but I'm okay with that in this case..

Thanks,
Ben
Hi Ben,

sure, you can shorten the filter string on this way but you need to pass some parameter from the frontend and that means at least you need the name of the parameter variable and it's contents, so sometimes you have no chance to further shorten the complete string.

Another method could also be to use only one parameter value which is separated by a character and split that on the server, like "@strVariable = '1|67|text|13'". This would only need to split the string at "|" to get the values. But that needs of course some additional programming to test the parameters, to test the datatype at the position you want, to split the value and so on.

The ADO command method has the advantage that you are not limited because you create separated parameter objects in the parameter collection and fill one by one, so the number of parameters and the content in each is a lot higher than assembling a parameter list as single string.

Cheers,

Christian
Avatar of bfuchs

ASKER

Hi,

My ADP/SQL experts,

Perhaps you can help me with the following

https://www.experts-exchange.com/questions/28899203/ADP-write-conflict-issue-SQL.html?anchorAnswerId=41371186#a41371186

Thanks,
Ben