Execute store procedure in SQL database problems

Question

Question

Antonio Garcia ... · Feb 9, 2018

Hi, I am trying to execute a store procedure within an ensemble operation to connect to aMsSQL database and I am having problem to pass the input arguments... I have tried several things but only one seems to work and it is not the ideal solution.

ADAPTER = "EnsLib.SQL.OutboundAdapter";

This is the code that works...as you can see the parameter is added to the sql query...not the best solution but the only that works at the moment.

SET SQLQuery = "EXEC [dbo].[xxxx] '" _ pRequest.RawContent _ "'"
SET tSC=..Adapter.ExecuteProcedure(,,SQLQuery,)

I have tried to call a store procedure that does not expect any parameters...and is also working:

SET SQLQuery2 = "{ call Test }"
SET tSC = ..Adapter.ExecuteProcedure(,,SQLQuery2,)

But when I am trying to do the call right based on the documentation...it is not working:

SET SQLQuery3 = "{ call xxxx(?) }"
SET tSC = ..Adapter.ExecuteProcedure(,,SQLQuery3,,"aaaa")

What the ExecuteProcedure is returning is a error message that includes the following:

<Ens>ErrGeneraln SQLState: (HY001) NativeError: [0] Message: [Microsoft][ODBC SQL Server Driver]Memory allocation failure

Store procedure:

ALTER PROCEDURE [dbo].[xxxx]
@HL7MSG varchar(max)AS
BEGIN

Looking at the documentation that call seems to be right and looking at some examples I got from internet...everybody seems to use that call same way...but not sure why it is nor working for me.

Can anyone see any problem or help me with this please?

Thanks

Discussion (9)0

Log in or sign up to continue

Robert Cemper · Feb 9, 2018

from last comment:
https://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx

Difference between varchar(max) and varchar(8000)

Varchar(8000) stores a maximum of 8000 characters. Varchar(max) stores a maximum of 2 147 483 647 characters.

See Books Online, the page titled "char and varchar" for more info.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)

so it can be marked as done.

2 0

score 1 · Answer 1 · 2018-02-09T04:36:34-05:00

Robert Cemper · Feb 9, 2018

did you try ?

SET SQLQuery3 = "{ call xxxx('aaaa') }"
SET tSC = ..Adapter.ExecuteProcedure(,,SQLQuery3)

1 0

score 0 · Answer 2 · 2018-02-09T05:22:54-05:00

Thanks but that would be similar as the code that worked for me...What I really want is to pass the parameter as a parameter in the call to execute the procedure.

Anyway as I say in the new comment...I don't think the problem is within the code but with the odbc or with the "MAX" for the input parameter.

Thanks

score 0 · Answer 3 · 2018-02-09T05:21:07-05:00

Well...after trying and trying for a while I have more information to share:

As the testing store procedure with no input parameters was working fine... I decided to play with it and copy the code from the other procedure into this testing one. I did, and magically the call worked. But it was still not working with the store procedure I want to use. I checked the input parameter in case that was the problem and it could be the "MAX" for the varchar. Maybe this is what is causing issues. If I set 1000, 2000 or any other value...it works fine, no issues at all, even I manage to run the store procedure I want with that input parameter set to a numeric value and not "MAX" and it worked...but then I changed it to "MAX" and after a few messages that did work...stopped working and I am having the same problem now...

Memory allocation failure

So I know the code is fine, the call to the store procedure is fine...I guess is something with the connection or the way the ODBC talks to the database or how that input parameter is passed to the database.

I would appreciate any suggestion...

Thanks

score 0 · Answer 4 · 2018-02-09T05:33:00-05:00

Robert Cemper · Feb 9, 2018

did you consider that max could mean maximum allowed size ? while string max has no value.

0 0

score 0 · Answer 5 · 2018-02-09T05:48:14-05:00

The point is that storeprocedure has been used for ages with another integration engine and with other applications...and that call never had any problem. That store procedure was configure to accept an incoming string with any length...that is why the VARCHAR(MAX) was used.

Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

score 1 · Answer 6 · 2018-02-09T06:06:24-05:00

from:
https://www.sqlservercentral.com/Forums/Topic647815-145-1.aspx

Difference between varchar(max) and varchar(8000)

Varchar(8000) stores a maximum of 8000 characters. Varchar(max) stores a maximum of 2 147 483 647 characters.

See Books Online, the page titled "char and varchar" for more info.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)

score 0 · Answer 7 · 2018-02-09T09:23:23-05:00

Thanks for your help Robert...

I have changed the input parameter in the storeprocedure to VARCHAR(8000) instead of VARCHAR(MAX) and is working so far...

Thanks

score 0 · Answer 8 · 2018-02-09T09:31:54-05:00

Robert Cemper · Feb 9, 2018

moved last comment to ANSWER.

0 0