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

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

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.

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.

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

Does the SP take any parameters?

1 Like

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.

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

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

Try declaring the NOCOUNT parameter in your stored procedure:

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

3 Likes

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