SQL Server Performance Forum – Threads Archive

Nested Scripts in SQL Analyzer?

We have a set of SQL scripts for setting up tables that are run in a prescribed order using SQL Analyzer. We would like to create a "master" script that sequentially runs the other scripts. Is there a command for SQL Analyzer to "embed" other scripts or run other scripts somehow? Or any kind of "macro" feature for running SQL scripts in order?

Im not sure I follow. You have a series of Scripts in seperate files and you want to put them all together. This may sound to obvious but you can put them all together and just seperate them with a GO statement. Again this response may not be of help but can you expalain more of what you are trying to accomplish. Are these scripts all just DDL commands?

If you want to check errors being returned from the SP’s or do some validations in between, create a SP which calls the other scripts using EXEC statement Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Our need is to maintain the scripts separately because we might want to run them in different orders, for example: 1) create tables without indexes; load data; create default constraints NO CHECK; create referential constraints NOCHECK; create indexes, or 2) create tables; create default constraints; create referential constraints; load data; create indexes, etc. However, we’d like to create a script that runs the other scripts so we can hand it off to anyone to run. I think Oracle scripts have a command to run another script; I just assumed SQL Server would have a similar feature. If not, we’ll look for workarounds, one of which is your suggestion. Thanks.

It seem to me that you might be better off incorporating all these processes into a DTS package. Have you looked into using DTS for a solution?

I guess we’d really like something like an "INCLUDE" statement similar to a C compiler, so that a script could include another. Based on your suggestion, I guess we would have to turn our scripts into Stored Procedures and then call them in succession. That may be a viable alternative. I’m also looking at the "isql.exe" utility as a way to run the scripts from a batch file. I just don’t have any experience with it yet; any thoughts are welcome. Thanks.

quote:Originally posted by Lazy_DBA It seem to me that you might be better off incorporating all these processes into a DTS package. Have you looked into using DTS for a solution?

That’s an interesting thought. I just got into DTS for migrating data from Oracle to SQL Server; I’ll have to see what’s involved converting these scripts to a DTS package. Several developers maintain these SQL Scripts (they may need to add a table or column for instance) so I’m not sure how we would work cooperatively with DTS. We also maintain very parallel scripts for Oracle, so it might be awkward to use a tool that is very different on one platform. Eventually we want to use an ER tool such as Embarcadero ER/Studio, but it isn’t quite flexible enough in this area of data loading before/after constraint and index creation.

OSQL.EXE is an excellent tool to run the script. You can create a batch file in whcich the OSQL.EXE sysntax can be stored and then pass the file name which has the script which needs to be run as input. MAIN.BAT ——– OSQL.EXE /S <Server> /U <User> /P &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />SWD> /I %1 /O %1.OUT SCRIPT1.SQL ———– CREATE TABLE…. SCRIPT2.SQL ———– CREATE INDEX…. EXECUTE.BAT ———– CALL MAIN.BAT SCRIPT1.SQL CALL MAIN.BAT SCRIPT2.SQL ….. Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

I recommend you look into DTS. From what you described in your steps it should meet your needs. I did a similiar task recently, I ran "one" script that creates tables, altered tables DRI, added Contraints, then loaded data, then ran another script that Added indexes. All in one simple DTS package.

That looks very good, as I can maintain the OSQL script and others can still develop the SQL scripts as before. I’ll look into OSQL. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by gaurav_bindlish OSQL.EXE is an excellent tool to run the script. You can create a batch file in whcich the OSQL.EXE sysntax can be stored and then pass the file name which has the script which needs to be run as input. MAIN.BAT ——– OSQL.EXE /S <Server> /U <User> /P &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />SWD> /I %1 /O %1.OUT SCRIPT1.SQL ———– CREATE TABLE…. SCRIPT2.SQL ———– CREATE INDEX…. EXECUTE.BAT ———– CALL MAIN.BAT SCRIPT1.SQL CALL MAIN.BAT SCRIPT2.SQL ….. Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here. <hr height="1" noshade id="quote"></blockquote id="quote">

That looks good too if we can keep the SQL scripts and just run them from DTS. I’ll learn some more about it.

quote:Originally posted by Lazy_DBA I recommend you look into DTS. From what you described in your steps it should meet your needs. I did a similiar task recently, I ran "one" script that creates tables, altered tables DRI, added Contraints, then loaded data, then ran another script that Added indexes. All in one simple DTS package.

OSQL replaces the very old ISQL from versions 6.5 and ealier. Very good tip from Gaurav but a suggestion if you dont know OSQL I wouldnt bother learning it now it is present only for backward compatibility. You could still call these scripts using xp_commandshell from QA. Even though MS doesnt plan to through away OSQL from new releases of SQL Server, I havent bothered with it since I can use alternate solutions.

I never heard that it is being phased out. AFAIK it is the only one utility which uses OLE DB connectivity to SQL Server and is one of the best ways to run scripts. What are the alternate solutions that you are looking at? I didn’t understand the xp_cmdshell implementation for running SQL Commands. Can you please elaborate on that as well? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

I Never said the OSQL was going to phase out, only that it’s there for backward compatibility. DTS doesn’t use OLE DB connectivity?? The description that simonbaker4 gave was similiar to a task I completed using DTS which met my needs. When looking at the whole picture I dont think simonbaker4 needs to be making Osql calls of anykind for a solution although I do believe Osql could be handy for other tasks. In respects to xp_commandshell calling SQL, let me clarify ment calling the bat files you see Osql is a client utility so if you plan on running OSQL from anywhere besides the server you will see a performance hit. Bottum line, given simonbaker4 problem I would say running these scripts from QA or using a DTS package would suffice, I dont think is should be that involved unless there is something missing here.

]]>