Question
Scott Roth · Oct 19, 2017

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

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()}"

parm=1
parm(1,"SqlType")=$$$SQLVARCHAR
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

00
1 0 4 494
Log in or sign up to continue

Replies

I found my answer, but the question now is how do we set MaxRowsToGet within the Studio Operation for EnsLib.SQL.Snapshot.

Thanks

Scott

thanks...the documentation isn't took clear on how to at %ListOfObjects to a snapshot.

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.

For those whom may need this, the above is correct, though for me (Cache 2017.2.1) I needed to insert the snapshot into the list of objects rather than using SetAt.


do SelectPER355MC.Insert(preset)
 

Thanks to the above answer and also hope this helps others.