November 2, 2015 at 2:17 pm
All:
We have a process that uses a stored procedure: sp_MysteryProcedure....
The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.
One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).
So my question is multi-part.....
1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name?
2). How do you search *.dtsx files?
Thanks....
November 2, 2015 at 2:22 pm
For SQL jobs, you could do something like this:
SELECT j.name, js.*
FROM msdb.dbo.sysjobsteps js
LEFT OUTER JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
WHERE js.command LIKE '%sp[_]MysteryProcedure%'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 2, 2015 at 5:17 pm
Have you considered logging the caller from the procedure itself?
Something like this.
use tempdb;
go
/* Create a table to log calls to stored procedures */
create table LogSP (
id int identity(1,1) not null
, executed_on datetime2 not null constraint df_executed_on default(getdate())
, executed_by sysname not null constraint df_executed_by default(original_login())
, executed_app sysname not null constraint df_executed_from default(app_name())
, executed_ip sql_variant not null constraint df_executed_ip default(connectionproperty('client_net_address'))
, executed_proc sysname not null constraint df_executed_proc default(OBJECT_NAME(@@PROCID))
, constraint pk_logSP primary key (id asc) with(fillfactor=100)
) ;
go
/* create a sample stored procedure to demonstrate the logging */
create proc testProc
as
-- log access to the SP
[highlight="#ffff11"]insert logSP default values;[/highlight]
-- Do some work
go
/* execute the test */
exec testProc;
go
/* check the results */
select *
from logSP;
go
/* Clean up */
drop proc testProc;
drop table logSP;
go
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 2, 2015 at 6:10 pm
I'd do it the same way as Magoo did it above. Instead of sifting through a pile of manure to try to figure out what the horse was thinking, it's much better to just ask the horse. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2015 at 6:02 am
As much as I appreciate the suggestion and code, I should have stated I do not have the permission required to create a new table, nor make changes to an existing stored procedure, which is why I am stuck doing it manually 🙁
November 4, 2015 at 7:34 am
andrew.mills 44498 (11/3/2015)
As much as I appreciate the suggestion and code, I should have stated I do not have the permission required to create a new table, nor make changes to an existing stored procedure, which is why I am stuck doing it manually 🙁
I feel for you. I just love it when management gives folks a task and then forbids them the tools they need to get it done. If things are so locked down, they must have a DBA or two. They should do this because they're not allowing you to do it.
You could write a query to interrogate cache but it would have to run often and you would need a place to store the results. Just in case someone brings it up, you could cheat like hell and put a semi-permanent real table in Temp DB but don't do that. It will take up some memory (like any table) and if something goes awry, it'll be your butt for sidestepping "the rules".
What you could do it write it all up with the change to the proc and the creation of the table and submit it to the DBAs for review and promotion. That way, you'll be perceived as having done your best not only to solve the problem but to stay within the rules, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2015 at 6:03 am
You are correct. There are a "bunch" of DBAs who seem to rule the land. Everything we try to do, we do not have permission but then are told when we ask them "it isn't our job. App teams own the data, we own the structure." They are not part of any development, but only do what we tell them to do because we do not have any permissions to do anything. Makes no sense to me that we give them all the scripts to run and all they do is run it. Why not eliminate the waste and just let the app teams do it. But I digress.
I do like your suggestions about giving them the 'stuff' so our part is done. Thank you for the rational approach.
November 5, 2015 at 8:36 am
When I had a similar problem trying to find out which packages used certain tables, I used "Find in Files" option (Ctrl+Shift+F) in Notepad++.
Of course, this only works if the packages are in the file system and have a common root folder.
Scott already posted code to search in Jobs, you would be missing procedures that might call that procedure and everything that calls those procedures.
You could also use SQL Search for SQL Server objects.
November 5, 2015 at 1:14 pm
Luis: thank you for the help, but unfortunately this didn't help. I searched the folder in question for the stored procedure, and Notepad++ wasn't able to find any SSIS / *.dtsx file that uses this.
However: after looking at every package based on name, I was able to locate the package that calls this stored procedure. It was in the same folder I used to search automatically using Notepad++
Are you saying Notepad++ is able to search the package details (*.dtsx) file for a stored procedure call?
November 5, 2015 at 1:34 pm
andrew.mills 44498 (11/5/2015)
Luis: thank you for the help, but unfortunately this didn't help. I searched the folder in question for the stored procedure, and Notepad++ wasn't able to find any SSIS / *.dtsx file that uses this.However: after looking at every package based on name, I was able to locate the package that calls this stored procedure. It was in the same folder I used to search automatically using Notepad++
Are you saying Notepad++ is able to search the package details (*.dtsx) file for a stored procedure call?
SSIS packages (or dtsx files) are nothing more than xml files interpreted to perform actions. Notepad++ is able to read them as plain text files (which they are even if they're "human readable"). After dealing with packages in text only mode, you'll get an idea of what each thing is referring to. It's easier if you have experience with biml.
November 6, 2015 at 1:31 pm
mister.magoo (11/2/2015)
.../* create a sample stored procedure to demonstrate the logging */
create proc testProc
as
-- log access to the SP
[highlight="#ffff11"]insert logSP default values;[/highlight]
-- Do some work
go
/* execute the test */
exec testProc;
[/code]
Would you please elaborate on how the [font="Courier New"]insert logSP default values [/font]statement works - how does it manage to retrieve the required data and first of all what is the syntax that this statement obeys ?
Thanks in advance.
November 6, 2015 at 1:40 pm
j-1064772 (11/6/2015)
mister.magoo (11/2/2015)
.../* create a sample stored procedure to demonstrate the logging */
create proc testProc
as
-- log access to the SP
[highlight="#ffff11"]insert logSP default values;[/highlight]
-- Do some work
go
/* execute the test */
exec testProc;
[/code]
Would you please elaborate on how the [font="Courier New"]insert logSP default values [/font]statement works - how does it manage to retrieve the required data and first of all what is the syntax that this statement obeys ?
Thanks in advance.
If you check the table definition, all of the columns have default constraints assigned (except for the identity column). Using DEFAULT VALUES in an INSERT INTO clause will insert a row with all the default values.
Reference: https://msdn.microsoft.com/en-us/library/ms174335.aspx
November 6, 2015 at 1:48 pm
Yes, I should have known to look at books on line for the syntax, I just assumed I knew all there was to know about the INSERT statement I have been using for years.
I just got baffled on how it managed to retrieve system info. One more thing I have to do now is to look into the [font="Courier New"]constraint df_executed_from default(app_name())[/font] business.
Learn something new everyday on this forum.
Thanks
November 6, 2015 at 2:06 pm
.
November 7, 2015 at 4:11 am
j-1064772 (11/6/2015)
Yes, I should have known to look at books on line for the syntax, I just assumed I knew all there was to know about the INSERT statement I have been using for years.I just got baffled on how it managed to retrieve system info. One more thing I have to do now is to look into the [font="Courier New"]constraint df_executed_from default(app_name())[/font] business.
Learn something new everyday on this forum.
Thanks
The constraints are just default values for the columns, I explicitly named them like that rather than just saying default(xyz) because it's a good habit to get into or you will have lots of issues with code comparison tools showing up changes just because of an automatically named constraint.
The reason I used default values rather than passing them explicitly in the insert statement is simply because it is easier to remember "default values" than a list of function names that I don't use very often.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply