 | |
Dependencies of stored procedures in DTS packages
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Dependencies of stored procedures in DTS packages -
10-12-2005
, 09:03 AM
#2
| |||
| |||
|
|
I'm working on a little program to document all of my DTS packages using the DTS package library and vb6. I have written the code to enumerate all the DTS packages and step through each one getting the connections and various steps of the ExecuteSQL and Datapump tasks. I'm trying to figure out how to get the dependencies of the Stored Procs called from the ExecuteSQL tasks like in query analyzer. Can anyone point me in the right direction? I'm working with SQL 2000. TIA Matt |
#3
| |||
| |||
|
|
You can get the dependencies between stored procedures, tables and other objects from the sysdepends table. BUT, and this is a big but, the sysdepends table is not always up-to-date, specially if you drop and recreate objects. If you recreate the database from script though (on a development server for example) and make sure that the script doesn't generate any "Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'you stored procedure name here'. The stored procedure will still be created." errors, the information in sysdepends will be correct. -- Jacco Schalkwijk SQL Server MVP "Matt Williamson" <ih8spam (AT) spamsux (DOT) org> wrote in message news:%23ajuwWzzFHA.2792 (AT) tk2msftngp13 (DOT) phx.gbl... I'm working on a little program to document all of my DTS packages using the DTS package library and vb6. I have written the code to enumerate all the DTS packages and step through each one getting the connections and various steps of the ExecuteSQL and Datapump tasks. I'm trying to figure out how to get the dependencies of the Stored Procs called from the ExecuteSQL tasks like in query analyzer. Can anyone point me in the right direction? I'm working with SQL 2000. TIA Matt |
#4
| |||
| |||
|
|
I think sp_sysdepends is giving me exactly what I want. Thanks -Matt "Jacco Schalkwijk" <jacco.please.reply (AT) to (DOT) newsgroups.mvps.org.invalid wrote You can get the dependencies between stored procedures, tables and other objects from the sysdepends table. BUT, and this is a big but, the sysdepends table is not always up-to-date, specially if you drop and recreate objects. If you recreate the database from script though (on a development server for example) and make sure that the script doesn't generate any "Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'you stored procedure name here'. The stored procedure will still be created." errors, the information in sysdepends will be correct. -- Jacco Schalkwijk SQL Server MVP "Matt Williamson" <ih8spam (AT) spamsux (DOT) org> wrote in message news:%23ajuwWzzFHA.2792 (AT) tk2msftngp13 (DOT) phx.gbl... I'm working on a little program to document all of my DTS packages using the DTS package library and vb6. I have written the code to enumerate all the DTS packages and step through each one getting the connections and various steps of the ExecuteSQL and Datapump tasks. I'm trying to figure out how to get the dependencies of the Stored Procs called from the ExecuteSQL tasks like in query analyzer. Can anyone point me in the right direction? I'm working with SQL 2000. TIA Matt |
#5
| |||
| |||
|
|
SP_depends is the stored proc I was referring to. Although, I can't filter the output from it in my code. Is there anyway to see the SQL code for sp_depends so I can add a where clause to it? -TIA Matt "Matt Williamson" <ih8spam (AT) spamsux (DOT) org> wrote in message news:erxr970zFHA.612 (AT) TK2MSFTNGP10 (DOT) phx.gbl... I think sp_sysdepends is giving me exactly what I want. Thanks -Matt "Jacco Schalkwijk" <jacco.please.reply (AT) to (DOT) newsgroups.mvps.org.invalid wrote You can get the dependencies between stored procedures, tables and other objects from the sysdepends table. BUT, and this is a big but, the sysdepends table is not always up-to-date, specially if you drop and recreate objects. If you recreate the database from script though (on a development server for example) and make sure that the script doesn't generate any "Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'you stored procedure name here'. The stored procedure will still be created." errors, the information in sysdepends will be correct. -- Jacco Schalkwijk SQL Server MVP "Matt Williamson" <ih8spam (AT) spamsux (DOT) org> wrote in message news:%23ajuwWzzFHA.2792 (AT) tk2msftngp13 (DOT) phx.gbl... I'm working on a little program to document all of my DTS packages using the DTS package library and vb6. I have written the code to enumerate all the DTS packages and step through each one getting the connections and various steps of the ExecuteSQL and Datapump tasks. I'm trying to figure out how to get the dependencies of the Stored Procs called from the ExecuteSQL tasks like in query analyzer. Can anyone point me in the right direction? I'm working with SQL 2000. TIA Matt |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.