Finding which control or scripts writes in a table

General Discussion
1

Hi Everyone:

I have a question for you guys. I have a table in a SQL DB, and ‘something’ is writing in this table, but I don’t know which control or scripts :angry: . I’ve looking for some ‘INSERT INTO’ statements but I can’t find anything. :scratch:
I even wonder if some external process is writing in the table, but I don’t know how to track it.

Any suggestions, comments will be appreciated. :thumb_left:

Thank you very much in advance.

2

Hi! Welcome to the forums!

Did you try the find/replace utility (ctrl-F)?

By default it will only look in open windows, but changing it to ALL windows will look in… well… all windows… :blush:

3

Jordan:

Thank you very much for the welcome. :smiley:
And, yes. I used this utility for search the ‘INSERT INTO’ statements but I can’t find anything. :frowning:

I appreciate your help.

4

If you have a transaction group that writes to the table, then searching for INSERT INTO probably won’t work. Try looking for the table name.

5

Jordan,

Thank you. I’ve been looking for the TS group, but without success.
I keep trying.

Regards.

6

[quote=“jruben84”]Jordan,

Thank you. I’ve been looking for the TS group, but without success.
I keep trying.

Regards.[/quote]

The fastest way to find it would be to do exactly what Jordan just said and use the find/replace utility but search for the table name instead.

7

Is there any chance you have a stored procedure you are calling from Ignition that writes to the table? You wouldn’t find either INSERT or the table name in the project if there is an indirect path in the database.

8

The other thing I was thinking was perhaps a transaction group sitting in a different project.

9

Guys,

Thank you. I also search for the table name, and looking for in different projects.
I will be some kind of crazy because this table writes null data, and I don’t know why… :scratch:

10

Temporarily rename or delete the table and see what part of your system starts throwing errors.

11

Ok!! I will do it. Thank you! :thumb_left:

12

Sadly, I can’t find which part of ignition writes in the table. I decided write a SQL function to solve this issue. :open_mouth:

Thank you! all of you!!
:thumb_right:

13

I know now :slight_smile: . It was a ‘trigger’ in SQL Server. Someone programmed it, and runs inside the server.
I didn’t notice that. Thanks for all guys! :smiley:

14

Could you share the function you wrote?

15

Sure, it’s about 200 lines, but I can share the main parts:

USE [myDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[machine_Culls](@fecha_ini DATETIME, @fecha_fin DATETIME)
RETURNS @culls TABLE 
	(Fecha DATETIME, Turno INT, Variables VARCHAR(15), Nombre NVARCHAR(80), Componente NVARCHAR(50), Valor BIGINT)

AS
BEGIN	
	DECLARE @tabla TABLE (Fecha DATETIME, Turno INT, Variables VARCHAR(15), Valor INT)
	INSERT INTO @tabla
		SELECT Fecha, Turno, Variables, MAX(Clase) as Valor 
		FROM ( SELECT 					
					CONVERT(VARCHAR(10),t_stamp,120) as Fecha, turno,
					lot_of_variables_here
				FROM
					some_table_here
			  ) AS Source_table
		UNPIVOT( Clase
				 for Variables in (lot_of_variables_here)
				) AS un_pvt
		GROUP BY Fecha, Turno, variables
	
	INSERT INTO @culls 
	SELECT Fecha, Turno, Variables
	CASE Variables	
		WHEN 'some_string' THEN 'other_string'
		-- a lot of cases here
	END AS Nombre,
	CASE Variables	
		WHEN 'some_string' THEN 'some_class'
	END AS Componente,
	Valor
	
	FROM @tabla
	
RETURN
END

But well, exists another function that writes in the table. It was inside a trigger and (not programmed by me). The function that I wrote was a -temporal solution- for my problem until I knew about the trigger.

Thanks!