What creates dt_procs

Question

What creates dt_procs

Bill Wagman

SSCommitted

Points: 1763
More actions
September 11, 2007 at 5:12 pm

#176578

Greetings,
A new database was created recently. TOday for the first time I did a back up of the database. After doing the backup I noticed a number of procedures were created in this particular database with names of the form dt_<procedure>. I did some searching and found that these stored procedures are apparently created by some Microsoft visual tools, Visual Basic, Visual Source Safe, possibly others. I do find this same collection of procedures in other databses but only those which are being backed up and so guess that they were in fact created by the backup. I did the backup using the Enterprise Manager. If anyone can point me to some additional details on these procedures and why doing a backup of the database would create them I would appreciate it.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 1

The "system" creates them... As you found out, it only creates them when the first instance is needed whether created by the backup code or use of an IDE. Best not to mess with them. Exception might be dt_Properties which must sometimes be modified by the "user" to make older versions of VB and similar things work.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bill Wagman SSCommitted Points: 1763 More actions · Answer 2

Jeff, thank you for the reply. I'm not sure what you mean when you say "it only creates them when the first instance is needed." Can you elaborate on that please. Thank you.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 3

Sure... when you first create a server instance, they are not there. They only appear when something first needs them... then, they persist.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 4

"dt_" objects (not only procedure, tables as well) are used by Enterprise Manager.

They being created when you first time use it on the server.

_____________
Code for TallyGenerator

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 5

dt_ procs are typically created by Visual Studio or other development tools... not really a problem assuming that you're working in an environment where developers are supposed to have access to the database and the ability to change/add things to the database. I don't believe that enterprise manager or SSMS creates those procedures, only Visual Studio though I may be wrong.

Joe

Sergiy SSC Guru Points: 110208 More actions · Answer 6

On one of computers I have clear MS SQL Server 2000 installation.

No Visual Studio, nothing else, just standard SQL Server development tools - EM and QA.

All dt_ procedures are there.

And if you look into table dtProperties you'll figure out it contains data for EM diagrams.

_____________
Code for TallyGenerator

Bill Wagman SSCommitted Points: 1763 More actions · Answer 7

Greetings, that is my situation as well. Visual Studio is not installed nor are any other visual tools. The procedures were created when I did a backup via the enterprose manager. Earlier in the thread a comment was made that the procedures are created whenever they are needed. Does anyone know what tools other than Visual tools needs them and also how they are used? It was mentioned that if developers are supposed to have access to the databae they are ok. It appears they contain information useful for examining the database. I'm just looking for more details. Thanks.

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 8

A little bit of research indicates that there are multiple ways for these proc's which are related to database diagramming to get created - Visual Studio, SSMS, Enterprise Manager, etc. all use them in various forms but they are not created by default on a database - they are created/installed when a tool that needs them is used against the database. There's a KB regarding SQL 2000 (http://support.microsoft.com/kb/327145) and a bit of chatter on the MSDN forums about them... I've always associated them with Visual Studio... learn something new every day it seems.