OSQL.exe question

Is there a way to process multiple SQL scripts in a single transaction using
osql.exe ?
Ideally, I'd like to be able to start transaction, execute multiple sql
scripts and commit transaction when all of them succeeded or rollback when
there was a failure.
I appreciate your help.
I would copy and paste them together.
-jens S=FC=DFmeyer.
|||"Marek" <nospam@.nowhere.com> wrote in
news:#BmMdSD8FHA.2676@.TK2MSFTNGP15.phx.gbl:

> Is there a way to process multiple SQL scripts in a single transaction
> using osql.exe ?
> Ideally, I'd like to be able to start transaction, execute multiple
> sql scripts and commit transaction when all of them succeeded or
> rollback when there was a failure.
> I appreciate your help.
As far as I know this is not possible in osql, but if you try sqlcmd from
SQL Server 2005, I do believe that you can. However, I have not tried this.
SqlCmd is also available in SQL Server 2005 Express, which is free.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in
news:Xns9717A547BD7D7olekristianbangaas@.207.46.248 .16:

> "Marek" <nospam@.nowhere.com> wrote in
> news:#BmMdSD8FHA.2676@.TK2MSFTNGP15.phx.gbl:
>
> As far as I know this is not possible in osql, but if you try sqlcmd
> from SQL Server 2005, I do believe that you can. However, I have not
> tried this. SqlCmd is also available in SQL Server 2005 Express, which
> is free.
And as Tibor so kindly answered in another group: You can download SqlCmd
here:
http://www.microsoft.com/downloads/d...D09C1D60-A13C-
4479-9B91-9E8B9D835CDC&displaylang=en
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||> As far as I know this is not possible in osql
I guess you could try the :r option to have OSQL read sub-scripts from a mother script. The
sub-scripts cannot have GO. A test is needed to verify that new connections will not be opened for
subscripts, use Profiler for that. Also, :r is not documented for OSQL, but I think it is for
SQLCMD. However, I don't think that error handling will be the easiest thing to accomplish.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns9717A547BD7D7olekristianbangaas@.207.46.248 .16...
> "Marek" <nospam@.nowhere.com> wrote in
> news:#BmMdSD8FHA.2676@.TK2MSFTNGP15.phx.gbl:
>
> As far as I know this is not possible in osql, but if you try sqlcmd from
> SQL Server 2005, I do believe that you can. However, I have not tried this.
> SqlCmd is also available in SQL Server 2005 Express, which is free.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging

osql vs. QA and extended characters

Hi, I have some sql commands I incorporate into an install and I've been
using scripts and osql.exe to perform these with generally good results.
A recent addition to these installs is a script that populates a table
of usernames and encryped passwords, and this is where I'm having
trouble. If I run a script in Query Analyzer that contains this line:
insert into operator (op_code, password, first_name, last_name,
security, operator, salespoint) values ('ADMIN', '??', 'ADMIN',
'ADMIN', 9, 'SYSTEM', 'SYSTEM')
I get predictable results and the encrypted password gets correctly
decrypted in our application.
If I run the very same script using osql like this:
osql.exe -E -i MyScript.sql -o "Output.txt"
The password doesn't end up in the database as '??', it seems to get
changed to '?+-' which obviously wouldn't decrypt correctly in the
application.
So this makes me think there's some kind of translation going on with
these extended characters when running the script via osql. Anyone know
how I can achieve the same results with osql as QA? I'm hoping there's a
switch or something that will do the trick. Thanks!
Matt
MattB wrote:
> Hi, I have some sql commands I incorporate into an install and I've
> been using scripts and osql.exe to perform these with generally good
> results.
> A recent addition to these installs is a script that populates a table
> of usernames and encryped passwords, and this is where I'm having
> trouble. If I run a script in Query Analyzer that contains this line:
> insert into operator (op_code, password, first_name, last_name,
> security, operator, salespoint) values ('ADMIN', '??', 'ADMIN',
> 'ADMIN', 9, 'SYSTEM', 'SYSTEM')
> I get predictable results and the encrypted password gets correctly
> decrypted in our application.
> If I run the very same script using osql like this:
> osql.exe -E -i MyScript.sql -o "Output.txt"
> The password doesn't end up in the database as '??', it seems to
> get changed to '?+-' which obviously wouldn't decrypt correctly in
> the application.
> So this makes me think there's some kind of translation going on with
> these extended characters when running the script via osql. Anyone
> know how I can achieve the same results with osql as QA? I'm hoping
> there's a switch or something that will do the trick. Thanks!
> Matt
See this page:
http://www.windowsitpro.com/SQLServe...ver_37470.html
David Gugick
Imceda Software
www.imceda.com
|||David Gugick wrote:
> MattB wrote:
>
> See this page:
> http://www.windowsitpro.com/SQLServe...ver_37470.html
>
Great. Thanks!
Matt

osql using :r and GO

I have a script that calls other scripts using :r. I am using osql to run th
e
script. I am getting an error that seems to indicate GO is not allowed in a
:r script, yet it is required to create a table and trigger in the same
script. How do I do this?
I have greatly simplified the scripts, and :r files must be used. Here are
the details:
File releaseA.sql:
:r code/releaseA1.SQL
File releaseA1.SQL
CREATE TABLE A1 (
LOG_ID BIGINT IDENTITY NOT NULL,
CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
)
GO
CREATE TRIGGER A1_RBUI
ON A1 FOR INSERT, UPDATE
AS
BEGIN
UPDATE A1
SET CTRL_UPDATE_DT = GETDATE(),
CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
FROM INSERTED I
INNER JOIN A1
ON I.LOG_ID = A1.LOG_ID
END
GO
COMMAND:
osql -i releaseA.sql
OUTPUT:
Incorrect syntax near the keyword 'CREATE'.
Msg 111, Level 15, State 1, Server MIRAGE, Line 35
'CREATE TRIGGER' must be the first statement in a query batch
Msg 170, Level 15, State 1, Server MIRAGE, Line 49
Line 49: Incorrect syntax near 'GO'.Hi
The following will work!
File f.sql
:r F1.sql
GO
:r F2.sql
File F1.sql
CREATE TABLE A1 (
LOG_ID BIGINT IDENTITY NOT NULL,
CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
)
File F2.sql
CREATE TRIGGER A1_RBUI ON A1 FOR INSERT, UPDATE
AS
BEGIN
IF @.@.ROWCOUNT = 0 RETURN
SET NOCOUNT ON
UPDATE A1
SET CTRL_UPDATE_DT = GETDATE(),
CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
FROM INSERTED I
INNER JOIN A1
ON I.LOG_ID = A1.LOG_ID
END
John
"Bevo" wrote:

> I have a script that calls other scripts using :r. I am using osql to run
the
> script. I am getting an error that seems to indicate GO is not allowed in
a
> :r script, yet it is required to create a table and trigger in the same
> script. How do I do this?
> I have greatly simplified the scripts, and :r files must be used. Here are
> the details:
> File releaseA.sql:
> :r code/releaseA1.SQL
>
> File releaseA1.SQL
> CREATE TABLE A1 (
> LOG_ID BIGINT IDENTITY NOT NULL,
> CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
> CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
> )
> GO
> CREATE TRIGGER A1_RBUI
> ON A1 FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE A1
> SET CTRL_UPDATE_DT = GETDATE(),
> CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
> FROM INSERTED I
> INNER JOIN A1
> ON I.LOG_ID = A1.LOG_ID
> END
> GO
> COMMAND:
> osql -i releaseA.sql
> OUTPUT:
> Incorrect syntax near the keyword 'CREATE'.
> Msg 111, Level 15, State 1, Server MIRAGE, Line 35
> 'CREATE TRIGGER' must be the first statement in a query batch
> Msg 170, Level 15, State 1, Server MIRAGE, Line 49
> Line 49: Incorrect syntax near 'GO'.|||Thank you for the solution John, yet I was hoping there would be another way
.
This approach would require a seperate file for each trigger. Considering ou
r
database may have at least one trigger on each of our 100+ tables, this
results in alot of trigger files...
"John Bell" wrote:
> Hi
> The following will work!
> File f.sql
> :r F1.sql
> GO
> :r F2.sql
>
> File F1.sql
> CREATE TABLE A1 (
> LOG_ID BIGINT IDENTITY NOT NULL,
> CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
> CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
> )
>
> File F2.sql
> CREATE TRIGGER A1_RBUI ON A1 FOR INSERT, UPDATE
> AS
> BEGIN
> IF @.@.ROWCOUNT = 0 RETURN
> SET NOCOUNT ON
> UPDATE A1
> SET CTRL_UPDATE_DT = GETDATE(),
> CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
> FROM INSERTED I
> INNER JOIN A1
> ON I.LOG_ID = A1.LOG_ID
> END
> John
>
> "Bevo" wrote:
>|||Hi
In general I would consider a trigger as a separate object to the table
anyhow, therefore they will be kept separately in version control. With
a decent file naming convention it is not difficult to know which
tables have triggers (something that you can not currently tell from
your files!), and the fact that are in different files make no
difference once you have a scripted build process.
John|||The file names provided were for demostrative purposes only.
Another limitation I have found is that osql will not allow nested :r files.
This is not allowed:
F1.sql
:r F2.sql
F2.sql
:r F2_1.sql
This limits the source directory structure to just one tier, so one cannot
group functionality into tiered directories.
"John Bell" wrote:

> Hi
> In general I would consider a trigger as a separate object to the table
> anyhow, therefore they will be kept separately in version control. With
> a decent file naming convention it is not difficult to know which
> tables have triggers (something that you can not currently tell from
> your files!), and the fact that are in different files make no
> difference once you have a scripted build process.
> John
>|||Hi
I think that you may be trying to make this overly complicated.
http://tinyurl.com/5299q
John

osql using :r and GO

I have a script that calls other scripts using :r. I am using osql to run th
e
script. I am getting an error that seems to indicate GO is not allowed in a
:r script, yet it is required to create a table and trigger in the same
script. How do I do this?
I have greatly simplified the scripts, and :r files must be used. Here are
the details:
File releaseA.sql:
:r code/releaseA1.SQL
File releaseA1.SQL
CREATE TABLE A1 (
LOG_ID BIGINT IDENTITY NOT NULL,
CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
)
GO
CREATE TRIGGER A1_RBUI
ON A1 FOR INSERT, UPDATE
AS
BEGIN
UPDATE A1
SET CTRL_UPDATE_DT = GETDATE(),
CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
FROM INSERTED I
INNER JOIN A1
ON I.LOG_ID = A1.LOG_ID
END
GO
COMMAND:
osql -i releaseA.sql
OUTPUT:
Incorrect syntax near the keyword 'CREATE'.
Msg 111, Level 15, State 1, Server MIRAGE, Line 35
'CREATE TRIGGER' must be the first statement in a query batch
Msg 170, Level 15, State 1, Server MIRAGE, Line 49
Line 49: Incorrect syntax near 'GO'.As you have noticed, the GO cannot be included in the :r script, because the
osql tool does not try to examine or parse the contents of the file, but
sends it directly to SQL Server for parsing, and the SQL Server doesn't like
GO.
Normally, GO is caught by osql itself, and never makes it to the back end.
Do you have to do this from within an osql session? A file with embedded GO
can be specified as a command line parameter to osql.
If the scripts have to call other scripts, can those other scripts be
editted at all? Instead of having a script with :r, maybe the script could
call xp_cmdshell which would call osql with the embedded script as a
parameter.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:B2348B52-4A37-4330-88E4-2837200E3036@.microsoft.com...
>I have a script that calls other scripts using :r. I am using osql to run
>the
> script. I am getting an error that seems to indicate GO is not allowed in
> a
> :r script, yet it is required to create a table and trigger in the same
> script. How do I do this?
> I have greatly simplified the scripts, and :r files must be used. Here are
> the details:
> File releaseA.sql:
> :r code/releaseA1.SQL
>
> File releaseA1.SQL
> CREATE TABLE A1 (
> LOG_ID BIGINT IDENTITY NOT NULL,
> CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
> CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
> )
> GO
> CREATE TRIGGER A1_RBUI
> ON A1 FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE A1
> SET CTRL_UPDATE_DT = GETDATE(),
> CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
> FROM INSERTED I
> INNER JOIN A1
> ON I.LOG_ID = A1.LOG_ID
> END
> GO
> COMMAND:
> osql -i releaseA.sql
> OUTPUT:
> Incorrect syntax near the keyword 'CREATE'.
> Msg 111, Level 15, State 1, Server MIRAGE, Line 35
> 'CREATE TRIGGER' must be the first statement in a query batch
> Msg 170, Level 15, State 1, Server MIRAGE, Line 49
> Line 49: Incorrect syntax near 'GO'.|||It seems :r files cannot be tiered, is this not allowed:
F1.sql
:r F2.sql
F2.sql
:r F2_1.sql
"Kalen Delaney" wrote:

> As you have noticed, the GO cannot be included in the :r script, because t
he
> osql tool does not try to examine or parse the contents of the file, but
> sends it directly to SQL Server for parsing, and the SQL Server doesn't li
ke
> GO.
> Normally, GO is caught by osql itself, and never makes it to the back end.
> Do you have to do this from within an osql session? A file with embedded G
O
> can be specified as a command line parameter to osql.
> If the scripts have to call other scripts, can those other scripts be
> editted at all? Instead of having a script with :r, maybe the script could
> call xp_cmdshell which would call osql with the embedded script as a
> parameter.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:B2348B52-4A37-4330-88E4-2837200E3036@.microsoft.com...
>
>|||So use xp_cmdshell and don't use :r at all.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:170F0DC1-B0B1-4183-8540-34509092778D@.microsoft.com...
> It seems :r files cannot be tiered, is this not allowed:
> F1.sql
> :r F2.sql
> F2.sql
> :r F2_1.sql
>
> "Kalen Delaney" wrote:
>

osql question

Re:
osql -i test.sql -e -b -r 1 > temp.log
I intend to use osql to submit a bunch of SQL scripts from
a batch file.
If the SQL script has an error, the message is logged with
a line number. But the echoed input does not have line
numbers. Instead, the SQL text is preceded by a line that
looks like:
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>
18> 19> 20> 21> 22> 23>
The above looks to me like the line numbers without the
text. The text is successfully echoed after that, but
with out line numbers.
Is there any way to get the text echoed to the log file
with line numbers?
Hi
I don't think you can do exactly what you want. -n will turn of the N>
prompts and -m can set the error level to display.
John
"Randy" <anonymous@.discussions.microsoft.com> wrote in message
news:1c7c801c4524c$36dbc0a0$a001280a@.phx.gbl...
> Re:
> osql -i test.sql -e -b -r 1 > temp.log
> I intend to use osql to submit a bunch of SQL scripts from
> a batch file.
> If the SQL script has an error, the message is logged with
> a line number. But the echoed input does not have line
> numbers. Instead, the SQL text is preceded by a line that
> looks like:
> 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>
> 18> 19> 20> 21> 22> 23>
> The above looks to me like the line numbers without the
> text. The text is successfully echoed after that, but
> with out line numbers.
> Is there any way to get the text echoed to the log file
> with line numbers?
>
>

osql or isql to run a batch remotely

Hello,
I have a collection of SQL Scripts that I would like to execute using a
batch file from a remote machine. Does anyone have any suggestions? I
think that I could use osql or isql, but I have never done that before. I
would appreciate any assistance.
Stewart
Stewart Saathoff wrote:
> Hello,
> I have a collection of SQL Scripts that I would like to execute using
> a batch file from a remote machine. Does anyone have any
> suggestions? I think that I could use osql or isql, but I have never
> done that before. I would appreciate any assistance.
> Stewart
I'm unclear about what is where...
Where is the file you want to execute located?
Do you want to run this file from a remote client or do you want to run
the file as though it were running directly on the server?
You can kick off OSQL from a stored procedure or a SQL Agent job on the
server. You could kick off OSQL from a client PC and execute a script
file that resides on the client PC. I would say that if the script file
is large, you'll get better performance running it from the server.
David Gugick
Imceda Software
www.imceda.com
|||ok, Let me be more clear.
I have a very large SQL Statement that needs to be run on the server.
Ideally the client will sit on their workstation and double-click on a file.
That file should execute the SQL statement on the server. I am pretty
positive that I can do all of this using osql, however I am unclear how I
can do this using a batch file. I don't want the users to have to type a
considerable amount and I definately do not want them to have to learn osql
or sql commands to do this...
Stewart
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%237j3Oby1EHA.524@.TK2MSFTNGP09.phx.gbl...
> Stewart Saathoff wrote:
> I'm unclear about what is where...
> Where is the file you want to execute located?
> Do you want to run this file from a remote client or do you want to run
> the file as though it were running directly on the server?
> You can kick off OSQL from a stored procedure or a SQL Agent job on the
> server. You could kick off OSQL from a client PC and execute a script file
> that resides on the client PC. I would say that if the script file is
> large, you'll get better performance running it from the server.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Stewart Saathoff wrote:
> ok, Let me be more clear.
> I have a very large SQL Statement that needs to be run on the server.
> Ideally the client will sit on their workstation and double-click on
> a file. That file should execute the SQL statement on the server. I
> am pretty positive that I can do all of this using osql, however I am
> unclear how I can do this using a batch file. I don't want the users
> to have to type a considerable amount and I definately do not want
> them to have to learn osql or sql commands to do this...
> Stewart
>
If you have to run the SQL batch from a client PC, you can use OSQL.
There's an option for submitting a file to run. The command-line
interface is fairly straightforward and documented in BOL.
David Gugick
Imceda Software
www.imceda.com
|||<snip>

> I am pretty positive that I can do all of this using
> osql, however I am unclear how I can do this using a
> batch file.
<snip>
Stewart,
Assuming you get this to work at the command prompt...
osql -SBigBox -Uuser -Ppwd -ddbname -i somefile.sql -o somefile.log
You can just paste it into a BAT file: that's it. You can use @.ECHO OFF,
print out message, etc to pretty it up but that's pretty much all you need.
You would need a file with your SQL statement where the BAT file runs
(ideally the same folder). If that's not acceptable, then you could put the
statement in a stored proc and call the proc by name instead of using the
input file.
Craig
|||Thank you all for your help. Everything works great.
"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message
news:yY9rd.1007069$Gx4.741632@.bgtnsc04-news.ops.worldnet.att.net...
> <snip>
>
> <snip>
> Stewart,
> Assuming you get this to work at the command prompt...
> osql -SBigBox -Uuser -Ppwd -ddbname -i somefile.sql -o somefile.log
> You can just paste it into a BAT file: that's it. You can use @.ECHO OFF,
> print out message, etc to pretty it up but that's pretty much all you
> need.
> You would need a file with your SQL statement where the BAT file runs
> (ideally the same folder). If that's not acceptable, then you could put
> the statement in a stored proc and call the proc by name instead of using
> the input file.
> Craig
>

osql feedback

I have some long running scripts which I fire at my database using osql.
(These are big files and mostly doing inserts but some also do a few other
things.) It would be nice to have some activity indication (other than the
disk activity light) that these are running. When I used to use Oracle,
their equivalent to osql had an option to print a dot (without a carriage
return) for every "n" statements. This gave a nice "I'm alive" indicator. I
can simulate this by adding a few "print" statements in my sql, but print
always adds a carriage return. Does anyone know a way of doing a print but
without the addition of a CR (or CR/LF)? So that a second "print" sends its
output to the same line as the first?

I know this is a nicety and I can live without it, but it would be nice.

thanks in advance,

Brian

www.cryer.co.uk/brianSend your feature request to sqlwish@.microsoft.com.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Brian Cryer" <brianc@.127.0.0.1.activesol.co.uk> wrote in message
news:1108636507.5572.0@.ersa.uk.clara.net...
>I have some long running scripts which I fire at my database using osql.
>(These are big files and mostly doing inserts but some also do a few other
>things.) It would be nice to have some activity indication (other than the
>disk activity light) that these are running. When I used to use Oracle,
>their equivalent to osql had an option to print a dot (without a carriage
>return) for every "n" statements. This gave a nice "I'm alive" indicator. I
>can simulate this by adding a few "print" statements in my sql, but print
>always adds a carriage return. Does anyone know a way of doing a print but
>without the addition of a CR (or CR/LF)? So that a second "print" sends its
>output to the same line as the first?
> I know this is a nicety and I can live without it, but it would be nice.
> thanks in advance,
> Brian
> www.cryer.co.uk/brian|||Create a small table, then from your script insert/update it with
information that will help you understand where the code is. From
another window you can query this table to see what's going on.

Dave|||On Thu, 17 Feb 2005 10:34:51 -0000, Brian Cryer wrote:

> I have some long running scripts which I fire at my database using osql.
> (These are big files and mostly doing inserts but some also do a few other
> things.) It would be nice to have some activity indication (other than the
> disk activity light) that these are running. When I used to use Oracle,
> their equivalent to osql had an option to print a dot (without a carriage
> return) for every "n" statements. This gave a nice "I'm alive" indicator. I
> can simulate this by adding a few "print" statements in my sql, but print
> always adds a carriage return. Does anyone know a way of doing a print but
> without the addition of a CR (or CR/LF)? So that a second "print" sends its
> output to the same line as the first?
> I know this is a nicety and I can live without it, but it would be nice.

You could wrap your osql call in a vbscript that captures the output, waits
for newlines, and prints dots to the console.|||"Brian Cryer" <brianc@.127.0.0.1.activesol.co.uk> wrote in message
news:1108636507.5572.0@.ersa.uk.clara.net...
>I have some long running scripts which I fire at my database using osql.
>(These are big files and mostly doing inserts but some also do a few other
>things.) It would be nice to have some activity indication (other than the
>disk activity light) that these are running. When I used to use Oracle,
>their equivalent to osql had an option to print a dot (without a carriage
>return) for every "n" statements. This gave a nice "I'm alive" indicator. I
>can simulate this by adding a few "print" statements in my sql, but print
>always adds a carriage return. Does anyone know a way of doing a print but
>without the addition of a CR (or CR/LF)? So that a second "print" sends its
>output to the same line as the first?
> I know this is a nicety and I can live without it, but it would be nice.
> thanks in advance,
> Brian
> www.cryer.co.uk/brian

Thank you for all of your comments.

Brian.

OSQL command - Execute all files in a folder;

Hi,
How do I execute all the .sql scripts in a folder with OSQL command?
Thanks in advance,
Hari Haran ArulmozhiCheck out a little free tool called Nant.

http://nant.sourceforge.net/

We use it for all of our product deployments. My Nant scripts go through all of the sql files in a directory and executes them using osql. The one little issue I have with it has to do with nested views because Nant will execute all of the files alphabetically, but there ways around this.|||You could cheat and use the FORFILES (http://technet2.microsoft.com/WindowsServer/en/library/9660fea1-65c7-48cf-b466-204ba159381e1033.mspx?mfr=true) command.

-PatP|||you could use xp_cmdshell and file system objects with sp_OACreate.|||Thanks for your responses !!

Osql and CommandLine stored procedures

I'm currently developing a number of SQL scripts to handle a major upgrade of our database from a development server to a production server using Osql.

Everything is working fine, but I have each of the scripts generating text-output and am hoping someone may know how to suppress some the Caution text (specifically triggered when a column has been renamed).

Can anyone provide a possible solution? (there's so many notes being written to the console its easy to miss the errors I'm concerned about.

Thanks!

TedNot that I am aware of. You might try SET ANSI_WARNINGS OFF but that will not suppress the types of warnings to which you are referring.

Terri

oSQL - How do I run all .SQL files in a folder by running one batch

Hello. I run into many instances where I have 50+ .sql scripts that I need
to run on multiple SQL installs. I want to automate this process some by
being able to drop all my .sql scripts into a folder and then click on my
batch file that will run all scripts in that folder, and create a log file
with the results.
For Example, my Folder has:
01 Sproc1.sql
02 Sproc2.sql
03 Sproc3.sql
etc...
The batch should be able to go through each file and run them one by one.
I know I can 'pipe' all scripts into one file and just run that one file,
but I've run into some issues with that method.
Any help is greatly appreciated!
Thanks,
TomI would use "xp_cmdshell" to get the name of all the SQL scripts into a
temporary table using the DOS 'dir /b <your directory>' command, then
process through each file in the temporary table one file at time and again
use "xp_cmdshell" to issue an "OSQL -E -S<yourserver> -i <your file> -o
<outputfile>" for each file. Hope this is enough to give you clues on how
to do this.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tom" <none@.none.com> wrote in message
news:ublnUExUEHA.2988@.TK2MSFTNGP10.phx.gbl...
> Hello. I run into many instances where I have 50+ .sql scripts that I
need
> to run on multiple SQL installs. I want to automate this process some by
> being able to drop all my .sql scripts into a folder and then click on my
> batch file that will run all scripts in that folder, and create a log file
> with the results.
>
> For Example, my Folder has:
> 01 Sproc1.sql
> 02 Sproc2.sql
> 03 Sproc3.sql
> etc...
> The batch should be able to go through each file and run them one by one.
>
> I know I can 'pipe' all scripts into one file and just run that one file,
> but I've run into some issues with that method.
> Any help is greatly appreciated!
> Thanks,
>
> Tom
>|||> I want to automate this process some by
> being able to drop all my .sql scripts into a folder and then click on my
> batch file that will run all scripts in that folder, and create a log file
> with the results.
The MS-DOS FOR command is pretty powerful
RunScripts.bat
@.ECHO OFF
IF "%1"=="" GOTO Syntax
FOR %f IN (%1\*.sql) DO osql -i %f <-- also add other switches for
osql.exe
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
Peace & happy computing,
Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"|||> FOR %f IN (%1\*.sql) DO osql -i %f <- add other switches for
> osql.exe
> GOTO End
Sorry about the wrap, just wanted to clear this up, that line that just says
osql.exe is part of my comment on the line above it. It is not a command in
the batch file. The line above it is supposed to end with this:
<-- also add other switches for osql.exe
Peace & happy computing,
Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"|||Hi
If you require the output from each file to be kept separate then your
method is an easy way to differentiate the output (check for empty files an
easy way to test it worked!).
If you don't require separate files my preference is to do something like:
for %f in (*.sql) do echo :r %f >> files.msql
Then run osql -E -i files.msql this will run multiple files on one
connection which should be alot faster.
John
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eiJT4TxUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Sorry about the wrap, just wanted to clear this up, that line that just
says
> osql.exe is part of my comment on the line above it. It is not a command
in
> the batch file. The line above it is supposed to end with this:
> <-- also add other switches for osql.exe
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "SELECT * FROM Users WHERE Clue > 0"
>|||Thanks. The script is not running though... I'm getting:
c:\TEMP\runscripts c:\scripts
1\*.sql was not expected at this time
I'm not the best with %"" stuff! Any ideas?
Thanks much!
Tom
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eiJT4TxUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Sorry about the wrap, just wanted to clear this up, that line that just
says
> osql.exe is part of my comment on the line above it. It is not a command
in
> the batch file. The line above it is supposed to end with this:
> <-- also add other switches for osql.exe
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "SELECT * FROM Users WHERE Clue > 0"
>|||> c:\TEMP\runscripts c:\scripts
> 1\*.sql was not expected at this time
OOPS! That's what I get for typing it in here and not playing with it
first. Sorry!
Here is the working version of runscripts.bat:
@.ECHO OFF
IF "%1"=="" GOTO Syntax
FOR %%f IN (%1\*.sql) DO osql -S server -E -d database -i %%f
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
Note that %1 always has one % sign, and %%f always has two. if you look at
the first page of the help for the FOR command (C:\> HELP FOR) you will note
a statement that says if you use the FOR command in a batch file, that
%variable should be %%variable. %1, however is a parameter, not a variable.
Also, you might look at John Bell's remarks. Concatenating the scripts into
one file will reduce all the connection opening times, if that's an issue
for you. In fact, you can get the batch file to do that for you:
Contents of runscripts2.bat (tested and verified this time):
@.ECHO OFF
IF "%1"=="" GOTO Syntax
IF EXIST %TEMP%\BigScript.sql DEL %TEMP%\BigScript.sql
FOR %%f IN (%1\*.sql) DO TYPE %%f >> %TEMP%\BigScript.sql
osql -S pc5055\MLabosh -E -d Northwind -i %TEMP%\BigScript.sql
DEL %TEMP%\BigScript.sql
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
As an option, you might insert a PAUSE right before the call to osql in case
you'd like to review bigscript.sql before running it. You could also remove
the second DEL command if you want to keep the bigscript.sql
Peace & happy computing,
Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"|||Hi
Just to add!!
I use ~ (tilda) to remove quotes around a variable (if they exist) and then
add them manually so that spaces in file/directory names can be used.
e.g ("%~1\*.sql")
John
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OnZwKg0UEHA.3016@.tk2msftngp13.phx.gbl...
> OOPS! That's what I get for typing it in here and not playing with it
> first. Sorry!
> Here is the working version of runscripts.bat:
> @.ECHO OFF
> IF "%1"=="" GOTO Syntax
> FOR %%f IN (%1\*.sql) DO osql -S server -E -d database -i %%f
> GOTO End
> :Syntax
> ECHO Please specify a folder like this:
> ECHO RunScripts c:\scripts
> ECHO to run all the SQL scripts in that folder
> :End
> ECHO.
> Note that %1 always has one % sign, and %%f always has two. if you look
at
> the first page of the help for the FOR command (C:\> HELP FOR) you will
note
> a statement that says if you use the FOR command in a batch file, that
> %variable should be %%variable. %1, however is a parameter, not a
variable.
> Also, you might look at John Bell's remarks. Concatenating the scripts
into
> one file will reduce all the connection opening times, if that's an issue
> for you. In fact, you can get the batch file to do that for you:
> Contents of runscripts2.bat (tested and verified this time):
> @.ECHO OFF
> IF "%1"=="" GOTO Syntax
> IF EXIST %TEMP%\BigScript.sql DEL %TEMP%\BigScript.sql
> FOR %%f IN (%1\*.sql) DO TYPE %%f >> %TEMP%\BigScript.sql
> osql -S pc5055\MLabosh -E -d Northwind -i %TEMP%\BigScript.sql
> DEL %TEMP%\BigScript.sql
> GOTO End
> :Syntax
> ECHO Please specify a folder like this:
> ECHO RunScripts c:\scripts
> ECHO to run all the SQL scripts in that folder
> :End
> ECHO.
> As an option, you might insert a PAUSE right before the call to osql in
case
> you'd like to review bigscript.sql before running it. You could also
remove
> the second DEL command if you want to keep the bigscript.sql
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "SELECT * FROM Users WHERE Clue > 0"
>|||Thank you guys! Getting closer in that the error is gone and it is running
(some of) the scripts in the folder. In the folder c:\scripts I created two
test scripts:
Script1.sql (Use Northwind select * from orders where orderID = 10317 )
Script2.sql (Use Northwind select * from orders where employeeID = 5 )
They are both just lookups for Northwind. Both of them run fine
individually and return results, but when using the batch only one was being
returned (scripts2.sql). So, I tried swapping the names of the files to see
if only the 2nd one would run.. and this time the log showed that they both
ran.
Here's what I used:
--
@.ECHO OFF
IF "%1"=="" GOTO Syntax
FOR %%f IN (%1\*.sql) DO osql -Usa -Ppasswordhere -Spryancompaq -i
%%f -oC:\log.txt
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
--
Could it be that it is actually running both, but that it overwrites the Log
file after the first one? If so, I wonder why it displayed both results the
2nd time. Perhaps the order of the scripts has something to do with it?
(Of course, the order they are run is important in many cases... we usually
name our scripts starting with 01script, 02script, etc.. I wonder if this
batch would run them but that order.. will try some tests..)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uzoT5D5UEHA.412@.TK2MSFTNGP10.phx.gbl...
> Hi
> Just to add!!
> I use ~ (tilda) to remove quotes around a variable (if they exist) and
then
> add them manually so that spaces in file/directory names can be used.
> e.g ("%~1\*.sql")
> John
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:OnZwKg0UEHA.3016@.tk2msftngp13.phx.gbl...
> at
> note
> variable.
> into
issue[vbcol=seagreen]
> case
> remove
>|||Hi
With the fixed log file you will overwrite it for each file, therefore only
the last output will be given
If you require one output file see my previous post, if infividual files are
required try:
FOR %%f IN ("%~1\*.sql") DO osql -Usa -Ppasswordhere -Spryancompaq -i %%f -o
%%~nf.txt
John
"Tom" <none@.none.com> wrote in message
news:uTBBqP6UEHA.3476@.tk2msftngp13.phx.gbl...
> Thank you guys! Getting closer in that the error is gone and it is
running
> (some of) the scripts in the folder. In the folder c:\scripts I created
two
> test scripts:
> Script1.sql (Use Northwind select * from orders where orderID = 10317 )
> Script2.sql (Use Northwind select * from orders where employeeID = 5 )
> They are both just lookups for Northwind. Both of them run fine
> individually and return results, but when using the batch only one was
being
> returned (scripts2.sql). So, I tried swapping the names of the files to
see
> if only the 2nd one would run.. and this time the log showed that they
both
> ran.
> Here's what I used:
> --
> @.ECHO OFF
> IF "%1"=="" GOTO Syntax
> FOR %%f IN (%1\*.sql) DO osql -Usa -Ppasswordhere -Spryancompaq -i
> %%f -oC:\log.txt
> GOTO End
> :Syntax
> ECHO Please specify a folder like this:
> ECHO RunScripts c:\scripts
> ECHO to run all the SQL scripts in that folder
> :End
> ECHO.
> --
> Could it be that it is actually running both, but that it overwrites the
Log
> file after the first one? If so, I wonder why it displayed both results
the
> 2nd time. Perhaps the order of the scripts has something to do with it?
> (Of course, the order they are run is important in many cases... we
usually
> name our scripts starting with 01script, 02script, etc.. I wonder if this
> batch would run them but that order.. will try some tests..)
>
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uzoT5D5UEHA.412@.TK2MSFTNGP10.phx.gbl...
> then
look[vbcol=seagreen]
will[vbcol=seagreen]
scripts[vbcol=seagreen]
> issue
in[vbcol=seagreen]
>

oSQL - How do I run all .SQL files in a folder by running one batch

Hello. I run into many instances where I have 50+ .sql scripts that I need
to run on multiple SQL installs. I want to automate this process some by
being able to drop all my .sql scripts into a folder and then click on my
batch file that will run all scripts in that folder, and create a log file
with the results.
For Example, my Folder has:
01 Sproc1.sql
02 Sproc2.sql
03 Sproc3.sql
etc...
The batch should be able to go through each file and run them one by one.
I know I can 'pipe' all scripts into one file and just run that one file,
but I've run into some issues with that method.
Any help is greatly appreciated!
Thanks,
Tom
I would use "xp_cmdshell" to get the name of all the SQL scripts into a
temporary table using the DOS 'dir /b <your directory>' command, then
process through each file in the temporary table one file at time and again
use "xp_cmdshell" to issue an "OSQL -E -S<yourserver> -i <your file> -o
<outputfile>" for each file. Hope this is enough to give you clues on how
to do this.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tom" <none@.none.com> wrote in message
news:ublnUExUEHA.2988@.TK2MSFTNGP10.phx.gbl...
> Hello. I run into many instances where I have 50+ .sql scripts that I
need
> to run on multiple SQL installs. I want to automate this process some by
> being able to drop all my .sql scripts into a folder and then click on my
> batch file that will run all scripts in that folder, and create a log file
> with the results.
>
> For Example, my Folder has:
> 01 Sproc1.sql
> 02 Sproc2.sql
> 03 Sproc3.sql
> etc...
> The batch should be able to go through each file and run them one by one.
>
> I know I can 'pipe' all scripts into one file and just run that one file,
> but I've run into some issues with that method.
> Any help is greatly appreciated!
> Thanks,
>
> Tom
>
|||> I want to automate this process some by
> being able to drop all my .sql scripts into a folder and then click on my
> batch file that will run all scripts in that folder, and create a log file
> with the results.
The MS-DOS FOR command is pretty powerful
RunScripts.bat
@.ECHO OFF
IF "%1"=="" GOTO Syntax
FOR %f IN (%1\*.sql) DO osql -i %f <-- also add other switches for
osql.exe
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
Peace & happy computing,
Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"
|||> FOR %f IN (%1\*.sql) DO osql -i %f <- add other switches for
> osql.exe
> GOTO End
Sorry about the wrap, just wanted to clear this up, that line that just says
osql.exe is part of my comment on the line above it. It is not a command in
the batch file. The line above it is supposed to end with this:
<-- also add other switches for osql.exe
Peace & happy computing,
Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"
|||Hi
If you require the output from each file to be kept separate then your
method is an easy way to differentiate the output (check for empty files an
easy way to test it worked!).
If you don't require separate files my preference is to do something like:
for %f in (*.sql) do echo :r %f >> files.msql
Then run osql -E -i files.msql this will run multiple files on one
connection which should be alot faster.
John
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eiJT4TxUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Sorry about the wrap, just wanted to clear this up, that line that just
says
> osql.exe is part of my comment on the line above it. It is not a command
in
> the batch file. The line above it is supposed to end with this:
> <-- also add other switches for osql.exe
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "SELECT * FROM Users WHERE Clue > 0"
>
|||Thanks. The script is not running though... I'm getting:
c:\TEMP\runscripts c:\scripts
1\*.sql was not expected at this time
I'm not the best with %"" stuff! Any ideas?
Thanks much!
Tom
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eiJT4TxUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Sorry about the wrap, just wanted to clear this up, that line that just
says
> osql.exe is part of my comment on the line above it. It is not a command
in
> the batch file. The line above it is supposed to end with this:
> <-- also add other switches for osql.exe
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "SELECT * FROM Users WHERE Clue > 0"
>
|||> c:\TEMP\runscripts c:\scripts
> 1\*.sql was not expected at this time
OOPS! That's what I get for typing it in here and not playing with it
first. Sorry!
Here is the working version of runscripts.bat:
@.ECHO OFF
IF "%1"=="" GOTO Syntax
FOR %%f IN (%1\*.sql) DO osql -S server -E -d database -i %%f
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
Note that %1 always has one % sign, and %%f always has two. if you look at
the first page of the help for the FOR command (C:\> HELP FOR) you will note
a statement that says if you use the FOR command in a batch file, that
%variable should be %%variable. %1, however is a parameter, not a variable.
Also, you might look at John Bell's remarks. Concatenating the scripts into
one file will reduce all the connection opening times, if that's an issue
for you. In fact, you can get the batch file to do that for you:
Contents of runscripts2.bat (tested and verified this time):
@.ECHO OFF
IF "%1"=="" GOTO Syntax
IF EXIST %TEMP%\BigScript.sql DEL %TEMP%\BigScript.sql
FOR %%f IN (%1\*.sql) DO TYPE %%f >> %TEMP%\BigScript.sql
osql -S pc5055\MLabosh -E -d Northwind -i %TEMP%\BigScript.sql
DEL %TEMP%\BigScript.sql
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
As an option, you might insert a PAUSE right before the call to osql in case
you'd like to review bigscript.sql before running it. You could also remove
the second DEL command if you want to keep the bigscript.sql
Peace & happy computing,
Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"
|||Hi
Just to add!!
I use ~ (tilda) to remove quotes around a variable (if they exist) and then
add them manually so that spaces in file/directory names can be used.
e.g ("%~1\*.sql")
John
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OnZwKg0UEHA.3016@.tk2msftngp13.phx.gbl...
> OOPS! That's what I get for typing it in here and not playing with it
> first. Sorry!
> Here is the working version of runscripts.bat:
> @.ECHO OFF
> IF "%1"=="" GOTO Syntax
> FOR %%f IN (%1\*.sql) DO osql -S server -E -d database -i %%f
> GOTO End
> :Syntax
> ECHO Please specify a folder like this:
> ECHO RunScripts c:\scripts
> ECHO to run all the SQL scripts in that folder
> :End
> ECHO.
> Note that %1 always has one % sign, and %%f always has two. if you look
at
> the first page of the help for the FOR command (C:\> HELP FOR) you will
note
> a statement that says if you use the FOR command in a batch file, that
> %variable should be %%variable. %1, however is a parameter, not a
variable.
> Also, you might look at John Bell's remarks. Concatenating the scripts
into
> one file will reduce all the connection opening times, if that's an issue
> for you. In fact, you can get the batch file to do that for you:
> Contents of runscripts2.bat (tested and verified this time):
> @.ECHO OFF
> IF "%1"=="" GOTO Syntax
> IF EXIST %TEMP%\BigScript.sql DEL %TEMP%\BigScript.sql
> FOR %%f IN (%1\*.sql) DO TYPE %%f >> %TEMP%\BigScript.sql
> osql -S pc5055\MLabosh -E -d Northwind -i %TEMP%\BigScript.sql
> DEL %TEMP%\BigScript.sql
> GOTO End
> :Syntax
> ECHO Please specify a folder like this:
> ECHO RunScripts c:\scripts
> ECHO to run all the SQL scripts in that folder
> :End
> ECHO.
> As an option, you might insert a PAUSE right before the call to osql in
case
> you'd like to review bigscript.sql before running it. You could also
remove
> the second DEL command if you want to keep the bigscript.sql
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "SELECT * FROM Users WHERE Clue > 0"
>
|||Thank you guys! Getting closer in that the error is gone and it is running
(some of) the scripts in the folder. In the folder c:\scripts I created two
test scripts:
Script1.sql (Use Northwind select * from orders where orderID = 10317 )
Script2.sql (Use Northwind select * from orders where employeeID = 5 )
They are both just lookups for Northwind. Both of them run fine
individually and return results, but when using the batch only one was being
returned (scripts2.sql). So, I tried swapping the names of the files to see
if only the 2nd one would run.. and this time the log showed that they both
ran.
Here's what I used:
@.ECHO OFF
IF "%1"=="" GOTO Syntax
FOR %%f IN (%1\*.sql) DO osql -Usa -Ppasswordhere -Spryancompaq -i
%%f -oC:\log.txt
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
Could it be that it is actually running both, but that it overwrites the Log
file after the first one? If so, I wonder why it displayed both results the
2nd time. Perhaps the order of the scripts has something to do with it?
(Of course, the order they are run is important in many cases... we usually
name our scripts starting with 01script, 02script, etc.. I wonder if this
batch would run them but that order.. will try some tests..)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uzoT5D5UEHA.412@.TK2MSFTNGP10.phx.gbl...
> Hi
> Just to add!!
> I use ~ (tilda) to remove quotes around a variable (if they exist) and
then[vbcol=seagreen]
> add them manually so that spaces in file/directory names can be used.
> e.g ("%~1\*.sql")
> John
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:OnZwKg0UEHA.3016@.tk2msftngp13.phx.gbl...
> at
> note
> variable.
> into
issue
> case
> remove
>
|||Hi
With the fixed log file you will overwrite it for each file, therefore only
the last output will be given
If you require one output file see my previous post, if infividual files are
required try:
FOR %%f IN ("%~1\*.sql") DO osql -Usa -Ppasswordhere -Spryancompaq -i %%f -o
%%~nf.txt
John
"Tom" <none@.none.com> wrote in message
news:uTBBqP6UEHA.3476@.tk2msftngp13.phx.gbl...
> Thank you guys! Getting closer in that the error is gone and it is
running
> (some of) the scripts in the folder. In the folder c:\scripts I created
two
> test scripts:
> Script1.sql (Use Northwind select * from orders where orderID = 10317 )
> Script2.sql (Use Northwind select * from orders where employeeID = 5 )
> They are both just lookups for Northwind. Both of them run fine
> individually and return results, but when using the batch only one was
being
> returned (scripts2.sql). So, I tried swapping the names of the files to
see
> if only the 2nd one would run.. and this time the log showed that they
both
> ran.
> Here's what I used:
> --
> @.ECHO OFF
> IF "%1"=="" GOTO Syntax
> FOR %%f IN (%1\*.sql) DO osql -Usa -Ppasswordhere -Spryancompaq -i
> %%f -oC:\log.txt
> GOTO End
> :Syntax
> ECHO Please specify a folder like this:
> ECHO RunScripts c:\scripts
> ECHO to run all the SQL scripts in that folder
> :End
> ECHO.
> --
> Could it be that it is actually running both, but that it overwrites the
Log
> file after the first one? If so, I wonder why it displayed both results
the
> 2nd time. Perhaps the order of the scripts has something to do with it?
> (Of course, the order they are run is important in many cases... we
usually[vbcol=seagreen]
> name our scripts starting with 01script, 02script, etc.. I wonder if this
> batch would run them but that order.. will try some tests..)
>
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uzoT5D5UEHA.412@.TK2MSFTNGP10.phx.gbl...
> then
look[vbcol=seagreen]
will[vbcol=seagreen]
scripts[vbcol=seagreen]
> issue
in
>

oSQL - How do I run all .SQL files in a folder by running one batch

Hello. I run into many instances where I have 50+ .sql scripts that I need
to run on multiple SQL installs. I want to automate this process some by
being able to drop all my .sql scripts into a folder and then click on my
batch file that will run all scripts in that folder, and create a log file
with the results.
For Example, my Folder has:
01 Sproc1.sql
02 Sproc2.sql
03 Sproc3.sql
etc...
The batch should be able to go through each file and run them one by one.
I know I can 'pipe' all scripts into one file and just run that one file,
but I've run into some issues with that method.
Any help is greatly appreciated!
Thanks,
Tom
I would use "xp_cmdshell" to get the name of all the SQL scripts into a
temporary table using the DOS 'dir /b <your directory>' command, then
process through each file in the temporary table one file at time and again
use "xp_cmdshell" to issue an "OSQL -E -S<yourserver> -i <your file> -o
<outputfile>" for each file. Hope this is enough to give you clues on how
to do this.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Tom" <none@.none.com> wrote in message
news:ublnUExUEHA.2988@.TK2MSFTNGP10.phx.gbl...
> Hello. I run into many instances where I have 50+ .sql scripts that I
need
> to run on multiple SQL installs. I want to automate this process some by
> being able to drop all my .sql scripts into a folder and then click on my
> batch file that will run all scripts in that folder, and create a log file
> with the results.
>
> For Example, my Folder has:
> 01 Sproc1.sql
> 02 Sproc2.sql
> 03 Sproc3.sql
> etc...
> The batch should be able to go through each file and run them one by one.
>
> I know I can 'pipe' all scripts into one file and just run that one file,
> but I've run into some issues with that method.
> Any help is greatly appreciated!
> Thanks,
>
> Tom
>
|||> I want to automate this process some by
> being able to drop all my .sql scripts into a folder and then click on my
> batch file that will run all scripts in that folder, and create a log file
> with the results.
The MS-DOS FOR command is pretty powerful
RunScripts.bat
@.ECHO OFF
IF "%1"=="" GOTO Syntax
FOR %f IN (%1\*.sql) DO osql -i %f <-- also add other switches for
osql.exe
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
Peace & happy computing,
Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"
|||> FOR %f IN (%1\*.sql) DO osql -i %f <- add other switches for
> osql.exe
> GOTO End
Sorry about the wrap, just wanted to clear this up, that line that just says
osql.exe is part of my comment on the line above it. It is not a command in
the batch file. The line above it is supposed to end with this:
<-- also add other switches for osql.exe
Peace & happy computing,
Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"
|||Hi
If you require the output from each file to be kept separate then your
method is an easy way to differentiate the output (check for empty files an
easy way to test it worked!).
If you don't require separate files my preference is to do something like:
for %f in (*.sql) do echo :r %f >> files.msql
Then run osql -E -i files.msql this will run multiple files on one
connection which should be alot faster.
John
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eiJT4TxUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Sorry about the wrap, just wanted to clear this up, that line that just
says
> osql.exe is part of my comment on the line above it. It is not a command
in
> the batch file. The line above it is supposed to end with this:
> <-- also add other switches for osql.exe
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "SELECT * FROM Users WHERE Clue > 0"
>
|||Thanks. The script is not running though... I'm getting:
c:\TEMP\runscripts c:\scripts
1\*.sql was not expected at this time
I'm not the best with %"" stuff! Any ideas?
Thanks much!
Tom
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eiJT4TxUEHA.2028@.TK2MSFTNGP11.phx.gbl...
> Sorry about the wrap, just wanted to clear this up, that line that just
says
> osql.exe is part of my comment on the line above it. It is not a command
in
> the batch file. The line above it is supposed to end with this:
> <-- also add other switches for osql.exe
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "SELECT * FROM Users WHERE Clue > 0"
>
|||> c:\TEMP\runscripts c:\scripts
> 1\*.sql was not expected at this time
OOPS! That's what I get for typing it in here and not playing with it
first. Sorry!
Here is the working version of runscripts.bat:
@.ECHO OFF
IF "%1"=="" GOTO Syntax
FOR %%f IN (%1\*.sql) DO osql -S server -E -d database -i %%f
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
Note that %1 always has one % sign, and %%f always has two. if you look at
the first page of the help for the FOR command (C:\> HELP FOR) you will note
a statement that says if you use the FOR command in a batch file, that
%variable should be %%variable. %1, however is a parameter, not a variable.
Also, you might look at John Bell's remarks. Concatenating the scripts into
one file will reduce all the connection opening times, if that's an issue
for you. In fact, you can get the batch file to do that for you:
Contents of runscripts2.bat (tested and verified this time):
@.ECHO OFF
IF "%1"=="" GOTO Syntax
IF EXIST %TEMP%\BigScript.sql DEL %TEMP%\BigScript.sql
FOR %%f IN (%1\*.sql) DO TYPE %%f >> %TEMP%\BigScript.sql
osql -S pc5055\MLabosh -E -d Northwind -i %TEMP%\BigScript.sql
DEL %TEMP%\BigScript.sql
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
As an option, you might insert a PAUSE right before the call to osql in case
you'd like to review bigscript.sql before running it. You could also remove
the second DEL command if you want to keep the bigscript.sql
Peace & happy computing,
Mike Labosh, MCSD
"SELECT * FROM Users WHERE Clue > 0"
|||Hi
Just to add!!
I use ~ (tilda) to remove quotes around a variable (if they exist) and then
add them manually so that spaces in file/directory names can be used.
e.g ("%~1\*.sql")
John
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OnZwKg0UEHA.3016@.tk2msftngp13.phx.gbl...
> OOPS! That's what I get for typing it in here and not playing with it
> first. Sorry!
> Here is the working version of runscripts.bat:
> @.ECHO OFF
> IF "%1"=="" GOTO Syntax
> FOR %%f IN (%1\*.sql) DO osql -S server -E -d database -i %%f
> GOTO End
> :Syntax
> ECHO Please specify a folder like this:
> ECHO RunScripts c:\scripts
> ECHO to run all the SQL scripts in that folder
> :End
> ECHO.
> Note that %1 always has one % sign, and %%f always has two. if you look
at
> the first page of the help for the FOR command (C:\> HELP FOR) you will
note
> a statement that says if you use the FOR command in a batch file, that
> %variable should be %%variable. %1, however is a parameter, not a
variable.
> Also, you might look at John Bell's remarks. Concatenating the scripts
into
> one file will reduce all the connection opening times, if that's an issue
> for you. In fact, you can get the batch file to do that for you:
> Contents of runscripts2.bat (tested and verified this time):
> @.ECHO OFF
> IF "%1"=="" GOTO Syntax
> IF EXIST %TEMP%\BigScript.sql DEL %TEMP%\BigScript.sql
> FOR %%f IN (%1\*.sql) DO TYPE %%f >> %TEMP%\BigScript.sql
> osql -S pc5055\MLabosh -E -d Northwind -i %TEMP%\BigScript.sql
> DEL %TEMP%\BigScript.sql
> GOTO End
> :Syntax
> ECHO Please specify a folder like this:
> ECHO RunScripts c:\scripts
> ECHO to run all the SQL scripts in that folder
> :End
> ECHO.
> As an option, you might insert a PAUSE right before the call to osql in
case
> you'd like to review bigscript.sql before running it. You could also
remove
> the second DEL command if you want to keep the bigscript.sql
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "SELECT * FROM Users WHERE Clue > 0"
>
|||Thank you guys! Getting closer in that the error is gone and it is running
(some of) the scripts in the folder. In the folder c:\scripts I created two
test scripts:
Script1.sql (Use Northwind select * from orders where orderID = 10317 )
Script2.sql (Use Northwind select * from orders where employeeID = 5 )
They are both just lookups for Northwind. Both of them run fine
individually and return results, but when using the batch only one was being
returned (scripts2.sql). So, I tried swapping the names of the files to see
if only the 2nd one would run.. and this time the log showed that they both
ran.
Here's what I used:
@.ECHO OFF
IF "%1"=="" GOTO Syntax
FOR %%f IN (%1\*.sql) DO osql -Usa -Ppasswordhere -Spryancompaq -i
%%f -oC:\log.txt
GOTO End
:Syntax
ECHO Please specify a folder like this:
ECHO RunScripts c:\scripts
ECHO to run all the SQL scripts in that folder
:End
ECHO.
Could it be that it is actually running both, but that it overwrites the Log
file after the first one? If so, I wonder why it displayed both results the
2nd time. Perhaps the order of the scripts has something to do with it?
(Of course, the order they are run is important in many cases... we usually
name our scripts starting with 01script, 02script, etc.. I wonder if this
batch would run them but that order.. will try some tests..)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uzoT5D5UEHA.412@.TK2MSFTNGP10.phx.gbl...
> Hi
> Just to add!!
> I use ~ (tilda) to remove quotes around a variable (if they exist) and
then[vbcol=seagreen]
> add them manually so that spaces in file/directory names can be used.
> e.g ("%~1\*.sql")
> John
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:OnZwKg0UEHA.3016@.tk2msftngp13.phx.gbl...
> at
> note
> variable.
> into
issue
> case
> remove
>
|||Hi
With the fixed log file you will overwrite it for each file, therefore only
the last output will be given
If you require one output file see my previous post, if infividual files are
required try:
FOR %%f IN ("%~1\*.sql") DO osql -Usa -Ppasswordhere -Spryancompaq -i %%f -o
%%~nf.txt
John
"Tom" <none@.none.com> wrote in message
news:uTBBqP6UEHA.3476@.tk2msftngp13.phx.gbl...
> Thank you guys! Getting closer in that the error is gone and it is
running
> (some of) the scripts in the folder. In the folder c:\scripts I created
two
> test scripts:
> Script1.sql (Use Northwind select * from orders where orderID = 10317 )
> Script2.sql (Use Northwind select * from orders where employeeID = 5 )
> They are both just lookups for Northwind. Both of them run fine
> individually and return results, but when using the batch only one was
being
> returned (scripts2.sql). So, I tried swapping the names of the files to
see
> if only the 2nd one would run.. and this time the log showed that they
both
> ran.
> Here's what I used:
> --
> @.ECHO OFF
> IF "%1"=="" GOTO Syntax
> FOR %%f IN (%1\*.sql) DO osql -Usa -Ppasswordhere -Spryancompaq -i
> %%f -oC:\log.txt
> GOTO End
> :Syntax
> ECHO Please specify a folder like this:
> ECHO RunScripts c:\scripts
> ECHO to run all the SQL scripts in that folder
> :End
> ECHO.
> --
> Could it be that it is actually running both, but that it overwrites the
Log
> file after the first one? If so, I wonder why it displayed both results
the
> 2nd time. Perhaps the order of the scripts has something to do with it?
> (Of course, the order they are run is important in many cases... we
usually[vbcol=seagreen]
> name our scripts starting with 01script, 02script, etc.. I wonder if this
> batch would run them but that order.. will try some tests..)
>
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uzoT5D5UEHA.412@.TK2MSFTNGP10.phx.gbl...
> then
look[vbcol=seagreen]
will[vbcol=seagreen]
scripts[vbcol=seagreen]
> issue
in
>

osql

i'm trying to execute some scripts created by the express studio script wizard. i can connect with the studio, the website (asp worker) but i can't create the right cmdline for osql ..... this is my osql line ......

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>osql -S (local)\\SQLEXPRESS -U sa -P sablah -i run.sql.......

this is the error i'm getting in my logs

Error: 18456, Severity: 14, State: 16.
2006-12-22 15:30:45.11 Logon Login failed for user 'sa'. [CLIENT: <local machine>]

"Server=(local)\\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";

the following is the working connection string for my aspworker.

"Server=(local)\\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";

when i try to put in the trust conenction parameter is says that it conflicts with the user flag ,. probably because its a differant type of login process. any ideas?

First, you only use EITHER username/password OR Trusted_Connection -NOT both. So the second connection string should fail.

Second, in the connection string, the server 'should' have only one [\] between the machinename and the instancename.

|||Yah, i believe i posted that. for asp.net the string actually works. i know why its not supposed to be there. and thats obivously why i can't add that argument to the osql statement. but i'm still left with the original posting of my error. i'm not missing anything in my osql statement?|||

Reading your post again, it seems that you indicated that your ASP connection string failed.

is98 wrote:

the following is the working connection string for my aspworker.

"Server=(local)\\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";

when i try to put in the trust conenction parameter is says that it conflicts with the user flag ,. probably because its a differant type of login process. any ideas?

ASP may be able to handle the two backslashes in the servername, but OSQL cannot. And if I recall, doesn't ASP use (localhost) instead of (local)?

Try: "server=(local)\SQLExpress" INSTEAD OF "Server=(local)\\SQLEXPRESS"

|||who the man? you the man. the localhost part works still ok. i just took out the extra slash which obiviously now explains why it couldn't find the server. thanks muchly.|||

The double back-slash is an escape character for C based languages, such as C#. When ever you want to pass a backslash in a C based language, you either need to use the "\\" escape sequence or use the @. to indicate a litteral string. You are passing literal strings to oSQL, not C#, so the "\\" is not needed, as you've found.

Just thought you'd be interested in understanding that this wasn't just random behaivor.

Mike

OSQL

I have a question with using osql. I'vce got a batch file that executes a
series of SQL scripts. These SQL scripts each will take between one and two
hours to execute. What I think is happening is that when one script fires
off, the next in line immediately fires.
How can I tell the bach file (osql) to wait until the script being executed
is complete before moving onto the next one?
Thanks in advance!
rem THIS BATCH FILE GENERATES THE SCRIPTS FOR REPLICATION
rem ONCE SCRIPTS ARE GENERATED THE BATCH FILE EXECUTES THEM IN THE PROPER
ORDER
echo GENERATING DROPSTATEMENTS
osql -Usa -Pvirtual -SACHIEVEREPL -dzion /Q "EXEC
dbo.GenerateReplicationDropStatements '5.0.4'" -o"E:\Documentation\CONVERSIO
N
SCRIPTS\droparticles2.sql"
echo GENERATING ADDSTATEMENTS
osql -Usa -Pvirtual -SACHIEVEREPL -dzion /Q "EXEC
dbo.GenerateReplicationAddStatements '5.0.4'" -o"E:\Documentation\CONVERSION
SCRIPTS\addarticles2.sql"
echo DROPPING ARTICLES
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\droparticles2.sql" -o"E:\Documentation\Drop.log"
echo EXECUTING RELEASE SCRIPTS
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script1-Cleanup.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script1-Cleanup.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script2-TableChanges.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script2-TableChanges.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script3-DropUnusedObjects.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script3-DropUnusedObjects.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script4-ChangedObjects.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script4-ChangedObjects.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script5-SynchEnvironments.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script5-SynchEnvironments.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script6-NewObj.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script6-NewObj.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script7- Conversion_Script_AR_ChargeRecord_PaidDa
te_Set.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script7- Conversion_Script_AR_ChargeRecord_PaidDa
te_Set.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script8- Conversion_Script_AR_ChargeRecords_Mark_
Prebills_Posted.sql"
-o"E:\Documentation\CONVERSION
SCRIPTS\Script8- Conversion_Script_AR_ChargeRecords_Mark_
Prebills_Posted.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script9-DBData.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script9-DBData.log"
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\Script10-SecurityTokenSetup.SQL" -o"E:\Documentation\CONVERSION
SCRIPTS\Script10-SecurityTokenSetup.log"
echo COMPLETED CONVERSION SCRIPTS
echo ADDING ARTICLES
osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
SCRIPTS\addarticles2.sql" -o"E:\Documentation\ADD.log"
echo STATRING SNAPSHOT AGENT
osql -Usa -Pvirtual -SACHIEVEREPL -dmsdb /Q "EXEC sp_start_job
'ACHIEVEREPL-zion-zion-6'" -o"E:\Documentation\CONVERSION
SCRIPTS\startjob.log"
I've included the actual batch file:YOu could use the Start [Command] /wait for it.
HTH, Jens Suessmeyer.
http://www-sqlserver2005.de
--
"A. Robinson" <ARobinson@.discussions.microsoft.com> schrieb im Newsbeitrag
news:69672621-DF68-442E-A3D8-B914FDA88BFB@.microsoft.com...
>I have a question with using osql. I'vce got a batch file that executes a
> series of SQL scripts. These SQL scripts each will take between one and
> two
> hours to execute. What I think is happening is that when one script fires
> off, the next in line immediately fires.
> How can I tell the bach file (osql) to wait until the script being
> executed
> is complete before moving onto the next one?
> Thanks in advance!
> rem THIS BATCH FILE GENERATES THE SCRIPTS FOR REPLICATION
> rem ONCE SCRIPTS ARE GENERATED THE BATCH FILE EXECUTES THEM IN THE PROPER
> ORDER
> echo GENERATING DROPSTATEMENTS
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion /Q "EXEC
> dbo.GenerateReplicationDropStatements
> '5.0.4'" -o"E:\Documentation\CONVERSION
> SCRIPTS\droparticles2.sql"
> echo GENERATING ADDSTATEMENTS
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion /Q "EXEC
> dbo.GenerateReplicationAddStatements
> '5.0.4'" -o"E:\Documentation\CONVERSION
> SCRIPTS\addarticles2.sql"
> echo DROPPING ARTICLES
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\droparticles2.sql" -o"E:\Documentation\Drop.log"
> echo EXECUTING RELEASE SCRIPTS
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script1-Cleanup.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script1-Cleanup.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script2-TableChanges.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script2-TableChanges.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script3-DropUnusedObjects.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script3-DropUnusedObjects.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script4-ChangedObjects.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script4-ChangedObjects.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script5-SynchEnvironments.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script5-SynchEnvironments.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script6-NewObj.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script6-NewObj.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script7- Conversion_Script_AR_ChargeRecord_PaidDa
te_Set.sql"
> -o"E:\Documentation\CONVERSION
> SCRIPTS\Script7- Conversion_Script_AR_ChargeRecord_PaidDa
te_Set.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script8- Conversion_Script_AR_ChargeRecords_Mark_
Prebills_Posted.sq
l"
> -o"E:\Documentation\CONVERSION
> SCRIPTS\Script8- Conversion_Script_AR_ChargeRecords_Mark_
Prebills_Posted.lo
g"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script9-DBData.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script9-DBData.log"
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\Script10-SecurityTokenSetup.SQL" -o"E:\Documentation\CONVERSION
> SCRIPTS\Script10-SecurityTokenSetup.log"
> echo COMPLETED CONVERSION SCRIPTS
> echo ADDING ARTICLES
> osql -Usa -Pvirtual -SACHIEVEREPL -dzion -i"E:\Documentation\CONVERSION
> SCRIPTS\addarticles2.sql" -o"E:\Documentation\ADD.log"
> echo STATRING SNAPSHOT AGENT
> osql -Usa -Pvirtual -SACHIEVEREPL -dmsdb /Q "EXEC sp_start_job
> 'ACHIEVEREPL-zion-zion-6'" -o"E:\Documentation\CONVERSION
> SCRIPTS\startjob.log"
>
> I've included the actual batch file:
>|||I don't see START as being an option for osql...
Could you give me an example?
Thanks!
"Jens Sü?meyer" wrote:

> YOu could use the Start [Command] /wait for it.
> HTH, Jens Suessmeyer.
> --
> http://www-sqlserver2005.de
> --
> "A. Robinson" <ARobinson@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:69672621-DF68-442E-A3D8-B914FDA88BFB@.microsoft.com...
>
>|||STart is a commandline util from DOS:
START OSQL -E -Q"Select GETDATE()" / wait
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"A. Robinson" <ARobinson@.discussions.microsoft.com> schrieb im Newsbeitrag
news:02BEFE97-165D-4D4C-B2F0-1939F6F9A786@.microsoft.com...
>I don't see START as being an option for osql...
> Could you give me an example?
> Thanks!
> "Jens Smeyer" wrote:
>|||I put START's in the script and the individual scripts did not wait for the
next ones to finish...it opens multiple osql windows and they are running
simultaneously.
I need for the scripts to run one at a time and wait for the command to
finish without opening up multiple sessions
"Jens Sü?meyer" wrote:

> STart is a commandline util from DOS:
> START OSQL -E -Q"Select GETDATE()" / wait
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "A. Robinson" <ARobinson@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:02BEFE97-165D-4D4C-B2F0-1939F6F9A786@.microsoft.com...
>
>|||Wouldn't it be easier to just combine all your scripts (seperated by go's )
and send them to OSQL once...
THen OSQL would run each statement in turn...
It seems to me that you are working too hard..
:)
"A. Robinson" wrote:
> I put START's in the script and the individual scripts did not wait for th
e
> next ones to finish...it opens multiple osql windows and they are running
> simultaneously.
> I need for the scripts to run one at a time and wait for the command to
> finish without opening up multiple sessions
> "Jens Sü?meyer" wrote:
>|||Rob:
I'd love to, but unfortunately Development isn't going to afford me such a
luxury...
"rob lynch" wrote:
> Wouldn't it be easier to just combine all your scripts (seperated by go's
)
> and send them to OSQL once...
> THen OSQL would run each statement in turn...
> It seems to me that you are working too hard..
> :)
>
> "A. Robinson" wrote:
>|||Is it the need for the Scripts that you generate in different scripts for th
e
purpose of debugging install issues?
If so, what about buffering the results in a temptable and then building the
textfiles using the output..
Another option might be to have OSQL call itself :)
I.e.
In you script and after completion...
exec master..xp_cmdshell 'osql blabalabal'
exec master..xp_cmdshell 'nextstep'
That might give you better control of the execution...
I find OSQL to be a very usefull tool and xp_cmdshell to be even more so!!!
Rob
"A. Robinson" wrote:
> Rob:
> I'd love to, but unfortunately Development isn't going to afford me such a
> luxury...
> "rob lynch" wrote:
>|||THe first two scripts that generate SQL files aren't a problem. It's the
scripts that perform DML. There are UPDATES and DELETES that take place and
each script performs a ton of operations.
The scripts end up stepping all over each other. There isn't a whole lot of
need for debugging.
Example: in the first script there is an ALTER done to a bunch of tables. In
the second script there is an ALTER done to a bunch of stored procedures tha
t
are dependent on the table changes. If the second script runs before the
first is complete, the second script will blow up because the table
definitions will not match...
"rob lynch" wrote:
> Is it the need for the Scripts that you generate in different scripts for
the
> purpose of debugging install issues?
> If so, what about buffering the results in a temptable and then building t
he
> textfiles using the output..
> Another option might be to have OSQL call itself :)
> I.e.
> In you script and after completion...
> exec master..xp_cmdshell 'osql blabalabal'
> exec master..xp_cmdshell 'nextstep'
> That might give you better control of the execution...
> I find OSQL to be a very usefull tool and xp_cmdshell to be even more so!!
!
> Rob
> "A. Robinson" wrote:
>|||Ahh yes I see the flaw in my thinking.. The issue is that the batch or scrip
t
will treat the starting of the OSQL tool as being success and allow it to
move on to the next part of the script..
It doesn't matter if it call's itself via xp_cmdshell or if it is done
directly via the batch file.
hhhmmmmm
How about rewriting this as a vbscript?
You could use the scripts much the same way (build them and then open the
file and read each command in much the same way as the osql tool does now)
It might take a bit more work, but I think you can ensure that you get what
you want.
If you need an example of a script for such a purpose let me know (by
posting back)
Rob
"A. Robinson" wrote:
> THe first two scripts that generate SQL files aren't a problem. It's the
> scripts that perform DML. There are UPDATES and DELETES that take place an
d
> each script performs a ton of operations.
> The scripts end up stepping all over each other. There isn't a whole lot o
f
> need for debugging.
> Example: in the first script there is an ALTER done to a bunch of tables.
In
> the second script there is an ALTER done to a bunch of stored procedures t
hat
> are dependent on the table changes. If the second script runs before the
> first is complete, the second script will blow up because the table
> definitions will not match...
> "rob lynch" wrote:
>