tungduong
asked on
Copy store procedure to another database?
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
- create new database
- copy some sp from old db to new db
I have SQL2K and coding all in Visual Basic.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 :=)
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 :=)
ASKER
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.
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.
"Each month I have to create new database"
Sounds like a maintenance nightmare....
Sounds like a maintenance nightmare....
ASKER
I've heard that sql_helptext can solve the problem. So can you tell me how to do with it in VB6?
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'")
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'")
ASKER
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.
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.
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...
ASKER
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?
How can I do?
ASKER
Well, thanks alot. I can do it now.
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.