August 4, 2016 at 10:05 am
We have a backup database that gets updated by our point of sale company every morning. Their tables are updating fine. I have a custom made table that is part of a workflow that is supposed to update after we get the update from the point of sale company but for the last couple days it hasn't been working. I've gotten as far as finding all the jobs in the sql server agent but there are about 30 different jobs, most of them named something like this [6CFY6C1B-8757-4AR5-9A75-04KI9BCBA7P1]. My question is: how do I find which job is "attached" to the table I need to update? I would be extremely grateful to whoever gives me an answer.
Also, I just took over this position and I'm pretty new to SQL
Running windows server 2012 and sql management studio.
Thanks!
August 4, 2016 at 10:14 am
Are the jobs in the SQL Server agent executing stored procedures? Or are they executing raw embedded sql?
August 4, 2016 at 10:18 am
I THINK stored procedures
August 4, 2016 at 10:25 am
If they are in fact stored procedures, there are built in system functions that will return all tables included in the stored procedure. You can take your list of sproc names, and run this query to see if it includes your table.
select *
from sys.dm_sql_referenced_entities('your_sproc_name_with_schema', 'OBJECT')
WHERE referenced_entity_name = 'YourTableName'
Or you can do it the other way and start with your table and get the list of sprocs that use it.
select *
from sys.dm_sql_referencing_entities('YourTableName_with_Schema', 'OBJECT')
WHERE referencing_entity_name = 'YourSprocName'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply