executing DTS in stored procedure / missing master db procedures

executing DTS in stored procedure / missing master db procedures

Post by Kevin Kuzda » Fri, 01 Feb 2002 02:45:43



We recently upgraded from version 7 to 2000 using the upgrade option. We
execute DTS packages from stored procedures using the following code:



SET NOCOUNT ON






/* Load package */



process_control.dbo.DTS_errors (error_code, error_msg, DTS_package,



/* execute package */


process_control.dbo.DTS_errors (error_code, error_msg, DTS_package,




process_control.dbo.DTS_errors (error_code, error_msg, DTS_package,



Everything works great with the converted server, but when we created our
first named instance of SQL Server 2000, that was not un upgrade, this
stored procedure would not work on the new instance.

After looking at the master database. we discovered that procedure
dt_displayoaerror did not exist.  On further analysis, there were 30 dt_
procedures that did not exist in the pure named-instance SQL Server 2000
installation.

I restored all of our user databases from the converted server to the new
named instance.  All of these user databases contained the 30 dt_
procedures.  Now here is the real mystery.  On Monday morning at 10:04 a.m.,
the 30 dt_  procedures suddenly popped into existence in the master
database.  Neither I or any other member of the staff had anything to do
with it.

Does anyone know why these procedures would not be present on install, but
suddenly pop into existence at a later time?  Are there newer replacements
that should be used instead?

 
 
 

executing DTS in stored procedure / missing master db procedures

Post by Sue Hoegemeie » Fri, 01 Feb 2002 11:59:42


dt_displayoaerror and many of the other dt_xxx stored
procedures are created when you use the database tools in
Visual Studio. They are used with Visual Studio and Source
Safe integration and are not not part of SQL Server so they
are not installed when you install SQL Server.
They really shouldn't be used in T-SQL code. OLE Automation
error information should be obtained using
sp_OAGetErrorInfo.

--Sue

On Wed, 30 Jan 2002 11:45:43 -0600, "Kevin Kuzdas"


>Everything works great with the converted server, but when we created our
>first named instance of SQL Server 2000, that was not un upgrade, this
>stored procedure would not work on the new instance.

>After looking at the master database. we discovered that procedure
>dt_displayoaerror did not exist.  On further analysis, there were 30 dt_
>procedures that did not exist in the pure named-instance SQL Server 2000
>installation.

>I restored all of our user databases from the converted server to the new
>named instance.  All of these user databases contained the 30 dt_
>procedures.  Now here is the real mystery.  On Monday morning at 10:04 a.m.,
>the 30 dt_  procedures suddenly popped into existence in the master
>database.  Neither I or any other member of the staff had anything to do
>with it.

>Does anyone know why these procedures would not be present on install, but
>suddenly pop into existence at a later time?  Are there newer replacements
>that should be used instead?


 
 
 

1. Executing DTS from stored procedure / missing master db procedures

We recently upgraded from version 7 to 2000 using the upgrade option. We
execute DTS packages from stored procedures using the following code:



SET NOCOUNT ON






/* Load package */



process_control.dbo.DTS_errors (error_code, error_msg, DTS_package,



/* execute package */


process_control.dbo.DTS_errors (error_code, error_msg, DTS_package,




process_control.dbo.DTS_errors (error_code, error_msg, DTS_package,



Everything works great with the converted server, but when we created our
first named instance of SQL Server 2000, that was not un upgrade, this
stored procedure would not work on the new instance.

After looking at the master database. we discovered that procedure
dt_displayoaerror did not exist.  On further analysis, there were 30 dt_
procedures that did not exist in the pure named-instance SQL Server 2000
installation.

I restored all of our user databases from the converted server to the new
named instance.  All of these user databases contained the 30 dt_
procedures.  Now here is the real mystery.  On Monday morning at 10:04 a.m.,
the 30 dt_  procedures suddenly popped into existence in the master
database.  Neither I or any other member of the staff had anything to do
with it.

Does anyone know why these procedures would not be present on install, but
suddenly pop into existence at a later time?  Are there newer replacements
that should be used instead?

2. SQL Monitoring tool ..??

3. Stored Procedures with the same name in different DB over-right master DB stored proc

4. Updating Access database

5. DTS Log/Executing DTS from stored procedure

6. System Account must be used to run SQL Server 6.5

7. Executing a Stored Procedure in a Stored Procedure and selecting on the result

8. permission for web user ?

9. executing system stored procedures within a stored procedure

10. Executing stored procedure from another stored procedure.

11. Problems executing Master Extended Stored Procedures from other Databases

12. Missing Execute Button in stored procedure design view

13. Scripting Master DB Stored Procedures