Re: tsql script to invoke another script

From: Wayne Snyder (wsnyder_at_computeredservices.com)
Date: 05/18/04 

Date: Tue, 18 May 2004 08:11:32 -0400

Here is a script I use, It executes(using OSQL) all of the *.sql files in
the given directory..But it in a BAT or CMD file and run it..It uses a
trusted connection, but you could change it to pass in a login and
password...

FOR %%S IN (C:\SCRIPTS\*.SQL) DO osql -i"%%S" -S"SERVERNAME" -E >>
C:\SCRIPTS\RESULTS.TXT 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:O%23KgNdIPEHA.2580@TK2MSFTNGP09.phx.gbl...
> > Where is that :r bit documented?  I don't see anything about
> > it in the Sql Server Books Online.
>
> From the OSQL Utility topic in the latest SQL 2000 Books Online:
>
> <Excerpt href="coprompt.chm::/cp_osql_1wxl.htm">
> When using osql interactively, you can read an operating-system file into
> the command buffer with :r file_name. Do not include a command terminator
in
> the file; enter the terminator interactively after you have finished
> editing.
> </Excerpt>
>
> You'll need to use a different approach if you have command terminators in
> your files, .  Another method to run multiple script files is with a FOR
> command.  The following will execute all *.sql files in the current
folder:
>
> FOR %f IN (*.sql) DO OSQL -S MyServer -U MyLogin -P MyPassword -i %f
>
> -- 
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "roger" <xrsr@rogerware.com> wrote in message
> news:Xns94ECC57E74F9Ersrrogerwarecom@204.127.199.17...
> > Thanks, that almost gets me there...
> >
> > The problem I see now is that, supposing my secondary scripts
> > have more than one batch in them, e.g.
> >   -- foo.sql
> >   drop procedure foo
> >   go
> >
> >   create procedure foo as
> >      select user
> >   go
> >
> > and then from my "main"  script
> >
> >   :r foo.sql
> >   go
> >
> > I get an error about incorrect syntax near 'go', at line 2.
> >
> >
> > Whereas, if I use
> >
> >    isql [connection options] -i foo.sql
> >
> > the script executes fine.
> >
> > Am I doing something wrong?
> > Where is that :r bit documented?  I don't see anything about
> > it in the Sql Server Books Online.
> >
> > Thanks again.
> >
> >
> >
> > "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in
> > news:uZLhJIHPEHA.640@TK2MSFTNGP12.phx.gbl:
> >
> > > You can use ':r' to read the contents of another script file in OSQL.
> > > For example, the following script file
> > >
> > >     --script.sql
> > >     :r script1.sql
> > >     GO
> > >     :r script2.sql
> > >     GO
> > >
> > > can be invoked with:
> > >
> > >     OSQL -S MyServer -U MyLogin -P MyPassword -i script.sql
> > >
> > > Note that all the scripts will run on the same connection.
> > >
> >
>
>

Relevant Pages

  • Re: ActiveX script works in DTS but not in job, why?
    ... Peter DeBetta, MVP - SQL Server ... As another test I saved the script as a .vbs file and tried running it, ... Microsoft OLE DB Provider for SQL Server ... 2000 databases including DTS packages. ...
    (microsoft.public.sqlserver.dts)
  • Re: Maintain production DB with changes from development DB
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... Or will I have to write a script to update all 4 servers? ...
    (microsoft.public.sqlserver.dts)
  • Re: Vista hosting XPe tools/db
    ... Are you running this script on the machine that actually has the SQL server ... machine hosting the database. ... i don't know if you can install a second instance of SQL ...
    (microsoft.public.windowsxp.embedded)
  • Re: ActiveX script works in DTS but not in job, why?
    ... Account that SQL Agent runs under and it is a Local Admin on the box. ... As another test I saved the script as a .vbs file and tried running it, ... Microsoft OLE DB Provider for SQL Server ... that would be since I need to be able to load DTS packages from either a SQL ...
    (microsoft.public.sqlserver.dts)
  • Re: Enterprise Manager Newbie Question
    ... a SQL backup is not a simple copy of the database files. ... Is it possible to write a script that one could run from a workstation and ...
    (microsoft.public.sqlserver.tools)
Loading