Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Lee Morrison
Lee Morrison
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
Is there any way to call a T-SQL script from within a T-SQL script?



Chris Hedgate
Chris Hedgate
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1497 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/
Lee Morrison
Lee Morrison
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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





Lee Morrison
Lee Morrison
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.



Chris Hedgate
Chris Hedgate
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1497 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/
tcartwright
tcartwright
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
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
Chris Hedgate
Chris Hedgate
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1497 Visits: 7
That sounds like a great solution, and yes, WSH is probably better than batch script. Just me that's oldfashioned. Smile

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/
tcartwright
tcartwright
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
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
eugene_liu
eugene_liu
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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 ?



tcartwright
tcartwright
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search