New post
Question
· Oct 19, 2017

EnsLib.SQL.Snapshot - Limitation of RowCount???

#Business Operation #Business Process (BPL) #Object Data Model #ObjectScript #SQL #Ensemble

We are trying to convert some of our SQL Service Integration Service jobs from Visual Studio to Ensemble. If we execute a Stored Procedure within SQL Server Management Studio it is returning approx 12,000 rows. However when Ensemble executes the same Stored Procedure it is only returning 250 rows.

Is there a limitation to EnsLib.SQL.Snapshot?

This is how we are calling the Stored procedure

Method uspInterfaceEnsSelectPER355MC(pRequest As osuwmc.SSIS.DataStructures.InputPER355MC, Output pResponse As EnsLib.SQL.Snapshot) As %Status
{
Set SPQuery = "{ ?= call dbo.usp_Interface_Ens_Select_PER355MC()}"

s parm=1
s parm(1,"SqlType")=$$$SQLVARCHAR
s parm(1,"IOTypes")=$$$SQLPARAMOUTPUT

set tSC = ..Adapter.ExecuteProcedureParmArray(.SelectPER355MC,.output,SPQuery,"oi",.parm)

if tSC = 1
{
set pResponse = SelectPER355MC.GetAt(1)
}

Quit tSC
}

Thanks

Scott

Discussion (4)1
Log in or sign up to continue
Robert Cemper · Oct 19, 2017

Hi Scott,

analyzing  ##class( EnsLib.SQL.Common).ExecuteProcedureParmArray(.....)
I think you should be able to provide a prepared Snapshot to set  the required parameters.

At least $$$sysTRACE("Using initialized SnapShot "_(tNumRS+1))  points in this direction

So your code might look like this:

Set SelectPER355MC=##class(%ListOfObjects).%New()
Set preset=##class(EnsLib.SQL.Snapshot).%New()
Set preset.MaxRowsToGet=12000
do SelectPER355MC.SetAt(preset,1)

set tSC = ..Adapter.ExecuteProcedureParmArray(.SelectPER355MC,.output,SPQuery,"oi",.parm)

I have no environment to check the approach.
So it's up to you to verify it.

1 0