Friday, March 9, 2012

Open a script file for a script

Hi,
Is it possible to open a sql file a run the content from another file?
MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
refman/5.0/en/batch-commands.html).
I saw this news
http://groups.google.com.au/group/microsoft.public.sqlserver.server/browse_thread/thread/18aa1a599d5e4ec/7ea0f09197e729ee?lnk=gst&q=run+file+script&rnum=3&hl=en#7ea0f09197e729ee
but it is not exactly what I want to do.
Let me know if it's possible.
Thank you.
BenWhat tool are you using to run the script? Also, does the script file exist on the server machine or
the client machine?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benjamin.amiot@.gmail.com> wrote in message
news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
> Hi,
> Is it possible to open a sql file a run the content from another file?
> MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
> refman/5.0/en/batch-commands.html).
> I saw this news
> http://groups.google.com.au/group/microsoft.public.sqlserver.server/browse_thread/thread/18aa1a599d5e4ec/7ea0f09197e729ee?lnk=gst&q=run+file+script&rnum=3&hl=en#7ea0f09197e729ee
> but it is not exactly what I want to do.
> Let me know if it's possible.
> Thank you.
> Ben
>|||On May 21, 4:47 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> What tool are you using to run the script? Also, does the script file exist on the server machine or
> the client machine?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <benjamin.am...@.gmail.com> wrote in message
> news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
> > Hi,
> > Is it possible to open a sql file a run the content from another file?
> > MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
> > refman/5.0/en/batch-commands.html).
> > I saw this news
> >http://groups.google.com.au/group/microsoft.public.sqlserver.server/b...
> > but it is not exactly what I want to do.
> > Let me know if it's possible.
> > Thank you.
> > Ben
I'm using the SQL Server Management Studio.
The file will be run on the client machine.
The reason why I'm asking for this function is because we have 300+
SQL script files that need to be ran in a specific order. Opening them
1 by 1 and running them using the manager is *really* time consuming.
The final script that will run all the others will be used by my
client as a unique "run all" script.
Thank you for your answer.
Ben|||You can run SSMS in "SQLCMD mode" (the Query menu) or use SQLCMD.EXE. If you do this, you can use :r
to have the tool to read a file. The file cannot contain GO (batch separator), at lest this was a
restriction last time I tested (2000).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benjamin.amiot@.gmail.com> wrote in message
news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
> On May 21, 4:47 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> What tool are you using to run the script? Also, does the script file exist on the server machine
>> or
>> the client machine?
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> <benjamin.am...@.gmail.com> wrote in message
>> news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
>> > Hi,
>> > Is it possible to open a sql file a run the content from another file?
>> > MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
>> > refman/5.0/en/batch-commands.html).
>> > I saw this news
>> >http://groups.google.com.au/group/microsoft.public.sqlserver.server/b...
>> > but it is not exactly what I want to do.
>> > Let me know if it's possible.
>> > Thank you.
>> > Ben
> I'm using the SQL Server Management Studio.
> The file will be run on the client machine.
> The reason why I'm asking for this function is because we have 300+
> SQL script files that need to be ran in a specific order. Opening them
> 1 by 1 and running them using the manager is *really* time consuming.
> The final script that will run all the others will be used by my
> client as a unique "run all" script.
> Thank you for your answer.
> Ben
>|||Since you need to run the 300+ files in a certain order, I would use a
simple vbscript and SMO to do the executes. Use the FileSystemObject to
open each file in the correct order (dos dir /b command output to file will
get you the list of files, which you can then order as appropriate), and
execute against a smo connection to the appropriate server/db.
--
TheSQLGuru
President
Indicium Resources, Inc.
<benjamin.amiot@.gmail.com> wrote in message
news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
> On May 21, 4:47 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> What tool are you using to run the script? Also, does the script file
>> exist on the server machine or
>> the client machine?
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> <benjamin.am...@.gmail.com> wrote in message
>> news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
>> > Hi,
>> > Is it possible to open a sql file a run the content from another file?
>> > MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
>> > refman/5.0/en/batch-commands.html).
>> > I saw this news
>> >http://groups.google.com.au/group/microsoft.public.sqlserver.server/b...
>> > but it is not exactly what I want to do.
>> > Let me know if it's possible.
>> > Thank you.
>> > Ben
> I'm using the SQL Server Management Studio.
> The file will be run on the client machine.
> The reason why I'm asking for this function is because we have 300+
> SQL script files that need to be ran in a specific order. Opening them
> 1 by 1 and running them using the manager is *really* time consuming.
> The final script that will run all the others will be used by my
> client as a unique "run all" script.
> Thank you for your answer.
> Ben
>|||On May 22, 4:30 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Since you need to run the 300+ files in a certain order, I would use a
> simple vbscript and SMO to do the executes. Use the FileSystemObject to
> open each file in the correct order (dos dir /b command output to file will
> get you the list of files, which you can then order as appropriate), and
> execute against a smo connection to the appropriate server/db.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <benjamin.am...@.gmail.com> wrote in message
> news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
> > On May 21, 4:47 pm, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> What tool are you using to run the script? Also, does the script file
> >> exist on the server machine or
> >> the client machine?
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> <benjamin.am...@.gmail.com> wrote in message
> >>news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
> >> > Hi,
> >> > Is it possible to open a sql file a run the content from another file?
> >> > MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
> >> > refman/5.0/en/batch-commands.html).
> >> > I saw this news
> >> >http://groups.google.com.au/group/microsoft.public.sqlserver.server/b...
> >> > but it is not exactly what I want to do.
> >> > Let me know if it's possible.
> >> > Thank you.
> >> > Ben
> > I'm using the SQL Server Management Studio.
> > The file will be run on the client machine.
> > The reason why I'm asking for this function is because we have 300+
> > SQL script files that need to be ran in a specific order. Opening them
> > 1 by 1 and running them using the manager is *really* time consuming.
> > The final script that will run all the others will be used by my
> > client as a unique "run all" script.
> > Thank you for your answer.
> > Ben
Ok,
I'll try both method and see which one is the easiest to implement.
Thank you for your messages.
Ben|||On May 21, 6:43 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You can run SSMS in "SQLCMD mode" (the Query menu) or use SQLCMD.EXE. If you do this, you can use :r
> to have the tool to read a file. The file cannot contain GO (batch separator), at lest this was a
> restriction last time I tested (2000).
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> <benjamin.am...@.gmail.com> wrote in message
> news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
> > On May 21, 4:47 pm, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> What tool are you using to run the script? Also, does the script file exist on the server machine
> >> or
> >> the client machine?
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> <benjamin.am...@.gmail.com> wrote in message
> >>news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
> >> > Hi,
> >> > Is it possible to open a sql file a run the content from another file?
> >> > MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
> >> > refman/5.0/en/batch-commands.html).
> >> > I saw this news
> >> >http://groups.google.com.au/group/microsoft.public.sqlserver.server/b...
> >> > but it is not exactly what I want to do.
> >> > Let me know if it's possible.
> >> > Thank you.
> >> > Ben
> > I'm using the SQL Server Management Studio.
> > The file will be run on the client machine.
> > The reason why I'm asking for this function is because we have 300+
> > SQL script files that need to be ran in a specific order. Opening them
> > 1 by 1 and running them using the manager is *really* time consuming.
> > The final script that will run all the others will be used by my
> > client as a unique "run all" script.
> > Thank you for your answer.
> > Ben
Hi Tibor,
Thanks for your solution, but I won't be able to us it as most of the
files are using GO in them. I can't remove this command as the scripts
are generated by different teams in the company.
I'll try the solution of TheSQLGuru.
Ben|||> Thanks for your solution, but I won't be able to us it as most of the
> files are using GO in them. I can't remove this command as the scripts
> are generated by different teams in the company.
This is one of the reasons to always read BOL. Reading current BOL, it seems like you *can* have
batch terminator in the inner script. Quotes from BOL:
"If the file contains Transact-SQL statements that arenot followed by GO, you must enter GO on the
line that follows :r."
"The file will be read and executed after a batch terminator is encountered. You can issue multiple
:r commands. The file may include any sqlcmd command. This includes the batch terminator GO."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benjamin.amiot@.gmail.com> wrote in message
news:1179790434.126680.210820@.r3g2000prh.googlegroups.com...
> On May 21, 6:43 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> You can run SSMS in "SQLCMD mode" (the Query menu) or use SQLCMD.EXE. If you do this, you can use
>> :r
>> to have the tool to read a file. The file cannot contain GO (batch separator), at lest this was a
>> restriction last time I tested (2000).
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> <benjamin.am...@.gmail.com> wrote in message
>> news:1179731210.026500.253250@.r3g2000prh.googlegroups.com...
>> > On May 21, 4:47 pm, "Tibor Karaszi"
>> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> >> What tool are you using to run the script? Also, does the script file exist on the server
>> >> machine
>> >> or
>> >> the client machine?
>> >> --
>> >> Tibor Karaszi, SQL Server
>> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
>> >> <benjamin.am...@.gmail.com> wrote in message
>> >>news:1179729481.030659.246610@.n15g2000prd.googlegroups.com...
>> >> > Hi,
>> >> > Is it possible to open a sql file a run the content from another file?
>> >> > MySQL can do it using the "source" function. (http://dev.mysql.com/doc/
>> >> > refman/5.0/en/batch-commands.html).
>> >> > I saw this news
>> >> >http://groups.google.com.au/group/microsoft.public.sqlserver.server/b...
>> >> > but it is not exactly what I want to do.
>> >> > Let me know if it's possible.
>> >> > Thank you.
>> >> > Ben
>> > I'm using the SQL Server Management Studio.
>> > The file will be run on the client machine.
>> > The reason why I'm asking for this function is because we have 300+
>> > SQL script files that need to be ran in a specific order. Opening them
>> > 1 by 1 and running them using the manager is *really* time consuming.
>> > The final script that will run all the others will be used by my
>> > client as a unique "run all" script.
>> > Thank you for your answer.
>> > Ben
> Hi Tibor,
> Thanks for your solution, but I won't be able to us it as most of the
> files are using GO in them. I can't remove this command as the scripts
> are generated by different teams in the company.
> I'll try the solution of TheSQLGuru.
> Ben
>

No comments:

Post a Comment