How can I return SQL Server SP result(Dataset) directly into ignition 7.9 tag or table

[Richita_Pandey]

Hello,

We are running an SQL Server SP(under named query) using gateway trigger every 1 min. SP is returning a dataset which I need to display on vision window table.
Named query doesn’t return the dataset output.How can we achieve the dataset out of SP directly as a result of named query.
Named Query:
EXEC dbo.wi_current

[gmiroclosa]

@Richita_Pandey

dataset

Hi Richita,
Did you try with template repeater?¿ Maybe you'll need to change the named query to a simple SQL query.
But It will throw the table of returned elements.

[pturmel]

So, you have a timer script calling a named query that uses EXEC to run a stored procedure? Any reason you can’t just use system.db.createSProcCall() ? It can control every aspect of parameter exchange and result set retrieval. Which you can then write to a dataset memory tag for the rest of the system to bind/monitor.

[Richita_Pandey]

Thank you pturmel.
I tried using the below script in console, and it returns None. In actual the SP has data with 53 row and 5 Column.
Can you help/guide me if I am missing somthing?

call = system.db.createSProcCall(“SPName”,“DBName”)
system.db.execSProcCall(call)
results = call.getResultSet()
print results

[lrose]

Does the SP take any parameters?

[pturmel]

Please show the stored procedure definition (obscuring anything private is fine). Please use the “preformatted text” button after highlighting the code you paste here, so it is readable.

[Richita_Pandey]

No, SP doesn’t consist of any input parameter.

[Richita_Pandey]

SP Defination

drop table if exists #TEMP1,#temp23
CREATE TABLE #temp23   (station_id int , serial_number varchar(50), wi_id int ,total_step int,step_number int, start_time datetime, end_time datetime)
SELECT distinct [equipment_id] INTO #TEMP1 FROM [MedScada].[dbo].[equipment] WITH (NOLOCK) where area_id=4
DECLARE @No_Of_Rows int=(select count(*) from #TEMP1),@i int=1,@station_id int,@serial_number int,@wip_id int,@wi_id int,@start_time datetime,@end_time datetime,@step_number int,@total_step int,@stn_name varchar(50),@Count int,@CPIL varchar(50)
WHILE (@i <= @No_Of_Rows)-----------------Find the 1st 1 in the [Trigger_Production_Time column]
BEGIN
    SET @station_id=(select top(1) [equipment_id] FROM #TEMP1)
    SET @serial_number=(SELECT  top(1) [serial_num] FROM [MedScada].[dbo].[wip] WITH (NOLOCK) where equipment_id =@station_id order by [timestamp_in] desc)
    SET @wip_id=(SELECT  top(1) wip_id FROM [MedScada].[dbo].[wip] WITH (NOLOCK) where equipment_id =@station_id order by [timestamp_in] desc)
    SET @wi_id =(SELECT  top(1) [wi_id] FROM [MedScada].[dbo].[wip] WITH (NOLOCK) where equipment_id =@station_id order by [timestamp_in] desc)
    SET @total_step=(SELECT count(*)  FROM [MedScada].[dbo].[wip_step] WITH (NOLOCK)  where wip_id=@wip_id)
    SET @step_number=(SELECT top(1) JSON_VALUE([property_dict],'$.step_number') as step_number FROM [MedScada].[dbo].[event_history] WITH (NOLOCK) where equipment_id=   @station_id and serial_number=@serial_number and JSON_VALUE([property_dict],'$.step_number') >0 order by  [timestamp] desc )
    SET @start_time=(SELECT TOP (1) [timestamp] FROM [MedScada].[dbo].[event_history] WITH (NOLOCK) where equipment_id=@station_id and serial_number=@serial_number and  [event_type] = 'Work Instruction Started'  order by [timestamp] desc)
    SET @end_time=(SELECT TOP (1) [timestamp] FROM [MedScada].[dbo].[event_history] WITH (NOLOCK) where equipment_id=@station_id and serial_number=@serial_number and  [event_type] = 'Work Instruction Complete'  order by [timestamp] desc)
    SET @stn_name=(SELECT [equipment_name]  FROM [MedScada].[dbo].[equipment] WITH (NOLOCK) where [equipment_id]=@station_id)
    SET @CPIL=(SELECT [CPIL] FROM [MedScada].[dbo].[wip] WITH (NOLOCK) where equipment_id =@station_id and serial_num=@serial_number and wi_id=@wi_id) 
    INSERT INTO #temp23  VALUES (@station_id , @serial_number, @wi_id ,@total_step,@step_number, @start_time, @end_time )
    DELETE TOP (1) FROM #TEMP1  --Deleting top row from the #temp1 table after every loop
  SET @i = @i + 1 --Increementing the loop counter
END
Select * from #temp23
END

[PGriffith]

Try declaring the NOCOUNT parameter in your stored procedure:

SQL Query Returns Different Results MSSQL vs Database Query Browser

In your stored procedure you need to add the line “SET NOCOUNT ON;” to the immediate beginning. ALTER PROCEDURE [dbo].[recordSiloLevel] -- Add the parameters for the stored procedure here @inputXML as NVARCHAR(MAX) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Your Procedure Here... END

This is often an issue with MSSQL; Management Studio connects to the database in a different 'mode' than Ignition does by default.

[Richita_Pandey]

Thank you. This helped, on adding SET NOCOUNT ON; its giving the required dataset in result.