passing large parameter to sql stored procedure

Pandasama

Hi

I'm trying to pass a large text parameter to SQL stored procedure (where it declared as NVARCHAR(MAX) )

I do the following:

paramHistoryTempList := myADOCommand.CreateParameter('@HistoryTempList',200,1,1024,HistoryTempList);

where HistoryTempList is BigText

And get error like this:

---

Microsoft Dynamics NAV

---

This message is for C/AL programmers:

This data type is not supported by C/SIDE. You can access data from
any of the following data types:
VT_VOID, VT_I2, VT_I4, VT_R4, VT_R8, VT_CY, VT_DATE, VT_BSTR and VT_BOOL

---

OK

---

If i trying to pass a variant, like

varHistoryTempList := HistoryTempList;
paramHistoryTempList := myADOCommand.CreateParameter('@HistoryTempList',200,1,1024,varHistoryTempList);

And get same error

I'm working with NAV5

Could anybody tell me, how can I pass a large text to my stored procedure?

vremeni4

Hi,

I think this is not possible in NAV 5.
Anyhow what you can do is to use an ID of the table where BLOB is stored.
In the stored procedure, you can use the ID to find the BLOB.
The ID will be passed as Integer from NAV 5.

I hope I managed to explain the idea.

Thanks.

KeeperRU

Pandasama wrote:

Hi

Could anybody tell me, how can I pass a large text to my stored procedure?

Create many parameters in your stored procedure, each 1024.

NAV can't transfer text variables longer than 1024 to all possible functions (internal functions, Excel Automation etc).
But you can transfer 1024*4 bytes using 4 different VARs.

Try something like:
1)
lADOParameter := lADOCommand.CreateParameter('@Query_Body_1', 200, 1, 1024, HistoryTempList[1]);
lADOParameter := lADOCommand.CreateParameter('@Query_Body_2', 200, 1, 1024, HistoryTempList[2]);
....
...

2) or
lADOParameter := lADOCommand.CreateParameter('@Query_Body_1', 200, 1, 1024, HistoryTempList[1]);
lADOCommand.Parameters.Append(lADOParameter);
lADOParameter := lADOCommand.CreateParameter('@Query_Body_1', 200, 1, 1024, HistoryTempList[2]);
lADOCommand.Parameters.Append(lADOParameter);