Solved

Copy store procedure to another database?

Posted on 2004-10-17
11
736 Views
Last Modified: 2008-02-20
I have some store procedure in a database. I usually have to create new database and have to create the sp above into new database. Can you tell me how to automate all that work:
- create new database
- copy some sp from old db to new db

I have SQL2K and coding all in Visual Basic.

Thanks

0
Comment
Question by:tungduong
11 Comments
 
LVL 3

Expert Comment

by:AlphaMan74
ID: 12335878
You want a complete copy of the database, with all tables, Stored Procedures and data? Or an empty database with all tables and SPs? Or something else?

To get all tables, Stored procedures and data, you can backup the database in enterprise manager then restore it under the new database name. Let us know if you need some further help with that.

If you want an empty database, then what you want to do is script the database in enterprise manager, then save that script. Change the name of the database in the CREATE DATABASE part of the script before you run it. Again, if you need detailed instructions, post back and we can help.
0
 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
ID: 12335979
To expand a bit, if you want to create a script,  Open enterprise manager, right click on your database, Choose All Tasks, Generate SQL Scripts.  You can choose to script the database and choose the stored procs you want to script.

So, if you wanted to code it from VB, you could use the above script and execute it against an ADO connection object.

Brett
0
 
LVL 11

Expert Comment

by:rdrunner
ID: 12336649
If you REALLY want to do this in VB (Its only a few Clicks in EM like arbert said) you should look at the SQLDMO object which is installed when you install the SQL-Server-Connectivity.

This object allows you to list all SP's and Tabels in an SQL Server (well actually you can do anything you want in it with it....) I think it can also generate the code you need to script the SP and tabels. But i would not go this way since it is so easy in EM :=)
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:tungduong
ID: 12337061
Thanks t all of help.

My problem is I have database with meny table and store procedure in it. Each month I have to create new database and want to restore all store procedure and some table (not all the tables). The store procedure then may be modify by the SQL Server admin (it rearly change) With the table I can excute command
SELECT .. INTO...
to transfer data to new database. The problem is I dont know how to do with the store procedures.

0
 
LVL 34

Expert Comment

by:arbert
ID: 12337924
"Each month I have to create new database"

Sounds like a maintenance nightmare....
0
 

Author Comment

by:tungduong
ID: 12343467
I've heard that sql_helptext can solve the problem. So can you tell me how to do with it in VB6?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12343879
You mean sp_helptext?

sp_helptext just returns the SQL for a routine/proc or the text of a view.  You could use it in VB--simply execute it with the connection object and pass the "object" as a parameter for which you want the text back (you can assign a recordset to get the results).

set rs=conn.execute ("sp_helptext 'yourstoredprocnamehere'")
0
 

Author Comment

by:tungduong
ID: 12344111
Thanks, arbert. I can you tell me how to run .sql script file from visual basic if I use your first guide to generate a script which content all of my store procedure.
I save script to name: create_sp.sql
I try to run with command:
gConn.Excute("EXEC ") & App.Path & "\create_sp.sql"
But it failed.

0
 
LVL 34

Expert Comment

by:arbert
ID: 12344461
You would actually have to read the contents of the file into a variable and exec that....the execute method (you have .Excute, but I assume that's a typo) won't read a file...
0
 

Author Comment

by:tungduong
ID: 12355797
I did as you said, arbert, but it fial to excute. You now there are much of quote (') mark in the store procedure.

How can I do?
0
 

Author Comment

by:tungduong
ID: 12366066
Well, thanks alot. I can do it now.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question