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.
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eric ShermanAccountant/DeveloperCommented:
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
bfuchsAuthor Commented:
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.
Eric ShermanAccountant/DeveloperCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

bfuchsAuthor Commented:
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.
Eric ShermanAccountant/DeveloperCommented:
In VBA I always use the Variant type to declare a variable-length string which can contain up to approximately 2 billion characters.

ET
bfuchsAuthor Commented:
Actually just tried with variant, same results.
Eric ShermanAccountant/DeveloperCommented:
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
bfuchsAuthor Commented:
yes, I agree.
BitsqueezerCommented:
Hi Ben,

first, you don't need to declare a variable in VBA which is variant, a normal string variable is also able to use 2GB of length, no problem.

Second, it doesn't help to have a 2GB variable if you assign it to a property in a form which definitely doesn't have the ability to use such length. Normally you have usually around 32KB for text fields in properties, if not 64KB, but not more. This should of course normally be enough space if you do not have a real monster string. As this is an older property it's of course possible that the length is limited to 256 characters here, MS never had big interest to improve ADPs.

If that's not enough you can try to avoid the InputParameters property and the RecordSource property to execute the SP, you can also execute the SP directly with VBA and get back the Recordset from ADO, then assign this Recordset to the form's Recordset property which also works. The difference here is that Access is not aware how to fill the SP's parameters so if you press F5 to requery the form this will in most cases end with an error. But you can also work around that if you create an AUTOKEYS macro (yes, macro, not VBA) which catches the F5 and F9 key and start a VBA function (not sub), this can use Screen.ActiveForm to find out the active form and then start a Public Sub in this form which can requery the form using the form's VBA to assign the recordset using the correct parameter assembling again. I use this method in my current project, works perfectly. To make sure you have this Public Sub (i.e. "SetRecordSource") I personally use an interface class which I insert into the form using "Implements".

Cheers,

Christian

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
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
BitsqueezerCommented:
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
bfuchsAuthor Commented:
Hi,

My ADP/SQL experts,

Perhaps you can help me with the following

http://www.experts-exchange.com/questions/28899203/ADP-write-conflict-issue-SQL.html#a41371186

Thanks,
Ben
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.