How can I call a script from a script

Question

How can I call a script from a script

Bob Boykin

SSC Rookie

Points: 42
More actions
September 13, 2005 at 2:16 am

#91078

How can I call a script from a script:
I inherited a 1600 line script, and I would really like to brake it into smaller chunks and execute them from a master script.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Hi Bob

Can you break the script into several stored procedures and then bring these all together in a master stored procedure? Just use

exec [procname]

within a stored proc to execute another stored proc - see BOL for the full syntax.

Regards

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bob Boykin SSC Rookie Points: 42 More actions · Answer 2

Thanks, but no; it's an installation / configuration thing, and I really need to execute one .SQL from another

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

You might be able to use xp_cmdshell to execute osql commands for each of the separate script files.

This assumes that your scriptlets do not have to share any variables or temp tables with the main script or each other. You could make temp tables global (use ## prefix) if necessary, and create/drop them in the main script to control their lifetime.

Bob Boykin SSC Rookie Points: 42 More actions · Answer 4

Thanks, Scott.

That was my backup plan.

I guess I’m just surprised that there is not some form of the execute command or some other method to execute the script file ( by that, I mean a text file with a .sql extension)!

Again, thanks for your suggestion.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

There is no easy way to tell SQL Server to go off and execute commands in an arbitrary file, and probably for good reasons from a security perspective.

Find the person(s) responsible for not letting you put the script into stored procedures and bribe them or beat them into submission. Failing that, give their names to all the headhunters you can find until they all leave for better jobs. (Refer to BOFH stories on http://www.theregister.co.uk for more inspiration.) Then put the code into stored procedures the way God intended it to be.

If you need alternatives, try loading the .sql files into text fields and running them with sp_executeSQL.

You could use OPENROWSET with the flat file provider, or BULK INSERT and SELECT, to read a file into a text variable and again use sp_executeSQL.

Or even use exec xp_cmdshell 'isqlw.exe -1 ...' to load the scripts into new windows in the same instance of Query Analyzer. (Assumes connection to a local server.) (I have no idea if this would work, or if all the windows would be executing in parallel.)

You could create a COM object in Visual Basic that uses SENDKEYS operations to hit the Query Analyzer file menu to open a new window, load a file, and run it. This could be called from a main script using object automation methods. If you get it to work, your colleages will be amazed because it will look like magic. Or you'll get fired, because if they don't like stored procedures they'll hate this idea.

You are insisting on running code stored in script files, which is a function of Query Analyzer. (Or Apex SQL Edit, or Toad, or some other tool.) You have to remember that Query Analyzer and SQL Server are two different things. SQL Server receives queries over connections and returns results. Query Analyzer is a tool that maintains text windows, loads and saves .sql files, and submits queries to SQL Server over a connection. There are no methods for SQL Server to reach back through the connection and control Query Analyzer. There are no T-SQL commands that will switch QA between grid mode and text mode, for instance. And there is no good way to tell it (through T-SQL) to load and submit another file.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

Wow, comprehensive answer! So there is a God and he understands SQL Server ... all is well

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin