| Author |
Topic |
|
Torch
Starting Member
20 Posts |
 Posted - 2005-07-12 : 21:32:11
|
| I'm trying to write a stored procedure that allows me to copy a (different) existing stored procedure from database to another (both of which are on the same server). I want the copied stored procedure to keep the same name as in the current database. Any help in this matter would be much appreciated. Torch. |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2005-07-12 : 21:51:42
|
| Does it have to be a stored procedure? You can do this very easily by using SQL-DMO....The text for each stored procedure is kept in one of the system tables, but I wouldn't necessarily rely on it for this exercise. HTH,Tim |
 |
|
|
Torch
Starting Member
20 Posts |
Posted - 2005-07-12 : 22:03:14
|
| I would prefer to do it in a stored procedure if possible.Torch. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2005-07-12 : 22:10:56
|
If you must, here's the basic gist:declare @c varchar(5000)select @c = c.[text] from sourcedb.dbo.syscomments c inner join sourcedb.dbo.sysobjects o on c.id = o.idwhere o.name = '<your_sp_name>'exec @c It's not a great solution for a number of reasons, but it should do the trick.Tim |
|
|
|
Torch
Starting Member
20 Posts |
Posted - 2005-07-12 : 22:21:39
|
| If I was to use SQL-DMO how could it be done? Ive never actually used this before. The main reason I wanted to use a Stored Procedure is because I want the stored procedure copied automatically without the user having to do it manually.Torch. |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2005-07-12 : 22:49:38
|
| You still need to trigger the SP somehow. Can I ask why you want to do this? |
|
|
|
Torch
Starting Member
20 Posts |
Posted - 2005-07-12 : 23:58:57
|
| (This post is not related to my previous post)Im not sure of the syntax for creating a copy of an existing database?At the moment the following code simply creates a new database:CREATE DATABASE [NewDatabase]What I want is the new database to be a copy of an existing database.Torch. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-07-13 : 01:17:49
|
quote:
What I want is the new database to be a copy of an existing database.
Take backup of Old Database and restore it in the new oneMadhivananFailing to plan is Planning to fail |
|
|
|
tonik
Starting Member
1 Post |
Posted - 2005-07-28 : 04:29:58
|
| Hi, Torch, Timmy,I have the same situation - a db with a few stored procedures (with input parameters), and I want to create/copy this procedures to the new db every day. I create .sql script which I can run manualy from Query Analyzer and it works/creates fine. Now I want to create this procedures (or run this .sql script) from application written in C# (without Query Analyzer, command line...)I found some SQL-DMO stories, but somehow it doesn't work...Could you please help me?Thank you... |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2005-07-28 : 17:27:18
|
| [code] Dim dmo As SQLDMO.SQLServer2 Dim sp Dim db As SQLDMO.Database Set dmo = New SQLDMO.SQLServer2 dmo.LoginSecure = True dmo.Connect "(local)" Set db = dmo.Databases("dbName") Set sp = db.StoredProcedures(spName) scr = sp.Script(5, scriptPath & "Procedures\" & sp.Name & ".sql") Set sp = Nothing[/code]That's the basic gist of it. The '5' in the sp.Script call is an enum that specifies how you want the script to be formatted (5 means to include the 'DROP' before 'CREATE').HTH,Tim |
|
|
|
|
Copying a stored procedure