February 11, 2010 at 2:22 pm
Hello,
Recently, I have been asked to support a application that uses SQL Database 2000.
Person, who used to support this application has moved on to a different job.
Here is my question:
I have been asked to search for a job running in the production environment that uses a view (I know the view name).
The job runs daily.
Based on the above information, is there a easy way to find out which DTS package using the view?
For now, I am opening each DTS package to see if it is using the view. This involves, selecting every icon in the design IDE and to look for the view name. This is taking a lot of time.
Note that the view name can be found either in the stored procedure or in the DTS package.
Is there a more efficient way to do this?
Please help.
Thanks.
February 11, 2010 at 2:25 pm
If you take a trace of the system, you'll be able to see what time the view is accessed. then just look at the jobs scheduled to run at the same time as you see the view accessed. It's not perfect because you could miss something that may only execute once a month, once a year etc., but if you're sure it's a job scheduled to run daily then...
Hope that at least gets you moving in the right direction.
-Luke.
February 11, 2010 at 2:29 pm
Thanks for the quick reply.
I checked with our SQL Server 2000 DBA and he says that if a trace is used the database will be very slow, this will impact the response time for the end-users.
Is there any other way to resolve this?
Please let me know.
Thanks.
February 11, 2010 at 2:31 pm
Well you can ask your DBA to filter it out only that Database and also in the Application Name filter it to something like " % DTS Designer %", so only those sessions are traced.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
February 11, 2010 at 7:19 pm
Well your DBA is somewhat correct. Will a trace affect performance of the database? Yes. If setup correctly as Bru mentioned above, it should have very minor impact. Also, I'm talking about a server side trace not a trace run from the Profiler gui. It's has less of an impact on the server. You can read about how to setup a server side trace Here[/url]. Gail Shaw goes into it step by step. Obviously, you'd need to setup it up a bit differently as you're looking for access to a specific object not for general performance tuning information. Also check out the link she has to Grant's blog about why you don't want to use profiler.
-Luke.
February 12, 2010 at 7:14 am
So I've given this a bit more thought and did some testing. I'm not sure if it would save you any time, but another approach you could take to this would be to save all of your DTS jobs as Visual Basic Files (open the package in design view, PAckage, Save AS, Location, Visual Basic File). Then you could use grep or some other file searching tool to search inside the basic files for your view name. If you open it with a vb editor or notepad you'll be able to see all of the code including the SQL code.
Still means touching each package but could perhaps save you some time.
-Luke.
February 12, 2010 at 3:51 pm
Thanks, great idea!!!
I will try this and let you know if it worked.
Regards,
Vaibhav
February 24, 2010 at 12:51 pm
The following script describes how to script dts packages to text files:
Script ALL DTS packages on server to text files
The below should take you straight the the article on this site:
http://www.sqlservercentral.com/scripts/Miscellaneous/31613/
I have used a version of this I altered to suit my needs many times in my job to find all sorts of things such as packages that use vbscript, packages that connect to a certain server or packages that call a certain stored procedure or view.
Simple Musings from a Simple Developer
TonjaB
March 11, 2010 at 4:05 pm
Thanks, that helped.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply