Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ghanta
Yak Posting Veteran
96 Posts |
 Posted - 2010-09-30 : 11:14:29
|
| Hi Guys I have a sql procedure in a table that I need to Execute... the statements of the proc is in different rows of a column... how can we execute the create proc statement from the table and create the proc? Or there is a better way to do.. what I need is I need to create procs on the fly and the procs will be in a master database which should be created in other database that I create in one of the procedure... What I have done now is created a script that will insert the scripts of proc in a table.. . I need to execute it or find a better way to do this... Thanks guys for the time. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-30 : 11:26:31
|
quote:
what I need is I need to create procs on the fly and the procs will be in a master database which should be created in other database that I create in one of the procedure...I need to execute it or find a better way to do this
The better way to do this is source control and controlled code deployment. You've basically built a source repository in a SQL Server database, however it won't provide the control and consistency a real source control product will.If you're using Visual Studio with Team Foundation, there is a source control feature built in. Otherwise there are many other source control programs out there, many of them free (CVS, Subversion, Git, Mercurial). |
 |
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2010-09-30 : 11:29:44
|
| Thanks Robvolk for the response... if we have TFS, can we use the proc from other database and create the proc in new database on the fly through stored proc? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-09-30 : 11:55:15
|
quote:
create the proc in new database on the fly through stored proc?
No, you're not creating stored procedures on the fly. You're deploying code, in controlled releases, to one or more databases. It's an important distinction, and it's the only way you'll keep your sanity (and your team's sanity) during code release.The best thing to do is read up on TFS and code deployment, there are plenty of articles on Microsoft's site. It's not a light undertaking, but if you're serious about doing this properly it's a necessary step.You'll have to export your SQL code out of the database into script files and check those files into source control. Based on how (or if) these procedures are related or "grouped" together, you should create separate projects for them in your source control tree. TFS is very strict about working with projects, and you'll get very frustrated with it if you don't plan this ahead of time. If you have a version of TFS with the Database Edition features ("Data Dude"), it is specifically designed to manage databases, including object versioning, schema and data compare, and integrated deployment. |
|
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2010-09-30 : 12:17:27
|
| Yes you are correct I am deploying code on the fly for database that gets created in the stored proc. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-30 : 12:19:45
|
For example our environment (using SVN) has A subfoderstructure likeSQL|- VERSIONNO | | - SCHEMA| | - DATA| | - PROGRAMABILTY|- VERSIONNO2| | - SHEMA........... with individual sql scrips in each place. The scrips are ran alphanumerically from their folders so if an object depends on another object then the dependant object will have a higher name and the script will be executed in the proper sequence.| for schema, data, programmability,Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
SQL Code of Proc in a table