Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GavinS
Starting Member
14 Posts |
 Posted - 2009-02-11 : 09:46:06
|
| Hi Everyone,I'm trying to search a database that is full of stored procedures to see if they reference any Local DTS Packages. Can I construct a query that searches the whole set of stored procedures within a given database (or a set of databases) for references to any given DTS Package?Thanks,Gavin |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-11 : 09:48:15
|
| you mean you call dts package from stored procedures using jobs? |
 |
|
|
GavinS
Starting Member
14 Posts |
Posted - 2009-02-11 : 10:00:09
|
| Not quite. In one of the stored procedures I have come across, the author has constructed a dtsrun line into a string and then executed it using xp_cmdshell.I am not sure how many more of these there are and I am currently clearing up a lot of DTS Packages which appear to have expired. I therefore cannot clear them out until I can be sure they are not referenced in the same way in stored procedures (and there are thousands of them). I wondered if I could run a query search through all the stored procedures in a database searching for the name of the DTS Packages or even just the 'dtsrun' reference. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-11 : 11:00:28
|
| then have a look at syscomments table. something likeSELECT DISTINCT OBJECT_NAME(id) FROM syscoments where text like '%yourdtspackagename%' |
|
|
|
GavinS
Starting Member
14 Posts |
Posted - 2009-02-11 : 11:47:45
|
| Thanks, that works great. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-14 : 02:23:57
|
welcome  |
|
|
|
|
|
|
Searching for DTS Packages in Stored Procedures