Calling a T-SQL script from within a T-SQL script

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Calling a T-SQL script from within a T-SQL...

Calling a T-SQL script from within a T-SQL script

Rate Topic

Display Mode

Topic Options

Author

Message

Lee Morrison

Posted Friday, March 14, 2003 3:10 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 22, 2005 6:37 AM
Points: 3, Visits: 1

Is there any way to call a T-SQL script from within a T-SQL script?

Post #10622

Chris Hedgate

Posted Saturday, March 15, 2003 2:01 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7

You can use xp_cmdshell to run isql with the script. Don't really see why you would need that though, are you sure that is the right way of doing what you need?

--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Post #56324

Lee Morrison

Posted Monday, March 17, 2003 6:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 22, 2005 6:37 AM
Points: 3, Visits: 1

quote:

You can use xp_cmdshell to run isql with the script. Don't really see why you would need that though, are you sure that is the right way of doing what you need?

--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
http://www.sql.nu

Post #56325

Lee Morrison

Posted Monday, March 17, 2003 7:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 22, 2005 6:37 AM
Points: 3, Visits: 1

Thank's for the reply and I am familiar with calling the command procedure running the script in batch.
Perhaps there is a better way? We have templates defined to create a database and then scripts to create tables, procedures, views, triggers, etc. I am trying to create a script that would call each of these "sub scripts" rather than combine them all into one big script.

Post #56326

Chris Hedgate

Posted Monday, March 17, 2003 7:42 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7

To my knowledge there is no way to do this in T-SQL. I think Oracle has some syntax for it, that may be why some want this. I can't see any reason to include it in T-SQL. If anything, there could be some way to set it up in Query Analyzer 'automatically'. What you can do is create a command-line batch script that uses osql to run each script.

--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Post #56327

tcartwright

Posted Monday, March 17, 2003 9:21 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, March 12, 2007 10:17 AM
Points: 277, Visits: 1

I would actually suggest using VBScript or JScript via WSH to execute your
scripts. I used to do something similar to what you are talking about with batch
files, but the scripting languages provide so much more capabilities I rarely go
back to dos batch mode. Also with WSH 5.6 there is remote scripting, so that you
can run scripts remotely on other computers. I have written a script that lets
me remotely re-register my com plus objects on my middle tier server. I copy
them over then kick this script off remotely. There are other options : VNC,
Term Serve, Telnet, SSH, etc...., but I like this one as it does not even
require me to have the scripts I am running on the target server. Lets me do
some very inventive administrative work from my desk. And as I am lazy, that is
a good thing. I work really hard so I can take it easy...

Tim C.
//Will write code for food

Tim C //Will code for food

Post #56328

Chris Hedgate

Posted Monday, March 17, 2003 10:33 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, Visits: 7

That sounds like a great solution, and yes, WSH is probably better than batch script. Just me that's oldfashioned. :)

But still, neither of these solutions makes it possible to call a T-SQL script from within a T-SQL script. But as I said, I don't really see any reason to do that.

--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Post #56329

tcartwright

Posted Monday, March 17, 2003 10:53 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, March 12, 2007 10:17 AM
Points: 277, Visits: 1

Your right, without using xp_cmdshell I don't see another way either. I was trying to offer him an alternative. I really did not understand the need to have it run from TSQL to begin with either. He could use WSH to kick off i/osql, and capture the StdOut from it running.

Tim C.
//Will write code for food

Tim C //Will code for food

Post #56330

eugene_liu

Posted Thursday, March 20, 2003 9:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 24, 2012 10:08 AM
Points: 5, Visits: 51

Yes, You can. Make your scripts into store procedure then be called by your command T-SQL script. If this would work for you ?

Post #56331

tcartwright

Posted Thursday, March 20, 2003 8:35 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, March 12, 2007 10:17 AM
Points: 277, Visits: 1

eugene I think he was referring to being able to call .sql file(s) from within T-SQL file if I am not mistaken. Which is better left to batch files or script files.

Tim C.
//Will write code for food

Tim C //Will code for food

Post #56332

« Prev Topic | Next Topic »

Permissions