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 |
|
Willy1973
Starting Member
4 Posts |
 Posted - 2007-07-27 : 14:31:08
|
| I have a table that has different sql scripts and I would like to execute these scripts to insert a value into another table.tblScripts (ScriptID int, SqlScript varchar(2000))record examples:1, 'SELECT COUNT(*) FROM tblTable1'2, 'SELECT COUNT(*) FROM tblTable2'I would like to execute the script in field SqlScript in order to use it later.example:insert tblResults (Field1, Field2)select ScriptID, exec(SqlScript)from tblScriptsThe exec here is not working, does anyone know why and how I can work around it.Thanks in advance |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-27 : 14:33:27
|
| You'd have to EXEC the sql separately using dynamic sql get the value into some temp table and use it in your INSERT/SELECT.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Willy1973
Starting Member
4 Posts |
Posted - 2007-07-27 : 15:21:04
|
| Hi dinakar,I really need to run this from the table is there anyway I can do this without using a temporary table? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-27 : 15:27:47
|
| No.Your solution is also going to require dynamic SQL since the statements are in a table.Why do you have such a design like this?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Willy1973
Starting Member
4 Posts |
Posted - 2007-07-27 : 15:32:31
|
| Hi tkizer,The reason I use the scripts in a the table is because it's used in different tools, so if I need to change any scripts I just have one place to change it and all tools will be updated.If you have a better way to do this, please let me know.Thanks in advance.Wilson |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-07-27 : 15:38:41
|
| Your tool should refer to a sql file rather than to a table.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Willy1973
Starting Member
4 Posts |
Posted - 2007-07-27 : 15:42:28
|
| Thanks tkizer. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-27 : 16:33:22
|
You really want to make certain that your security is really tight on the server side for this table too until you sort things out with scripts. Imagine if someone managed to insert something like DROP DATABASE MyVeryImportantDatabase |
|
|
|
|
|
|
Running Script that is in a table