osql batch

Hi
I want to run a batch file containg osql commands but do not want the user
to be able to see the sa password. Is there any way of doing this?
Thanks
> I want to run a batch file containg osql commands but do not want the user
> to be able to see the sa password. Is there any way of doing this?
Use trusted connection (-E parameter) instead of sql login/password.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks
"Dejan Sarka" wrote:

> Use trusted connection (-E parameter) instead of sql login/password.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

osql batch

Hi
I want to run a batch file containg osql commands but do not want the user
to be able to see the sa password. Is there any way of doing this?
Thanks> I want to run a batch file containg osql commands but do not want the user
> to be able to see the sa password. Is there any way of doing this?
Use trusted connection (-E parameter) instead of sql login/password.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks
"Dejan Sarka" wrote:

> Use trusted connection (-E parameter) instead of sql login/password.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

osql -b question!

the help document said that

"osql -b will specifies that OSQL exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0."

my question is : how can I get the DOS ERRORLEVEL.

example:

C:\>isql -E -b -Q"backup log pubs to disk='C:\adsfasd.tmp'"

the result:

Msg 4208, Level 16, State 0, Server YANG, Line 1
当恢复模型为 SIMPLE 时，不允许使用 BACKUP LOG 语句。请使用 BACKUP DATABASE 或用
ALTER DATABASE
更改恢复模型。
Msg 3013, Level 16, State 1, Server YANG, Line 1
BACKUP LOG 操作异常终止。

It's in chinese language,means
"can not use BACKUP LOG when the restore model being SIMPLE,you can use ALTER DATABASE or BACKUP DATABASE to change the restore model.
Msg 3013, Level 16, State 1, Server YANG, Line 1
BACKUP LOG aborted"

where is DOS ERRORLEVEL?? How can I get it??

thankstake a look at the server errorlog or the backupserver errorlog for details|||See KB#39585 (http://support.microsoft.com/default.aspx?scid=kb;en-us;39585).

-PatP

osql and unicode

Hi,
Is anybody aware of any problems with inserting unicode chars via osql. I've
got some unicode text and when it gets inserted it inserts not the character
I am expecting. It works fine from query analyser etc. My collation is set t
o
the generic collation no problems there. Thanks.
Panos.Just some extra information.
The string I have in the input file is:
WIENER B?RSE
and it becomes
WIENER BíRSE
When the same script is run from query analyser then it insert WIENER B?RSE
which is correct. Thanks.
"Panos Stavroulis." wrote:

> Hi,
> Is anybody aware of any problems with inserting unicode chars via osql. I'
ve
> got some unicode text and when it gets inserted it inserts not the charact
er
> I am expecting. It works fine from query analyser etc. My collation is set
to
> the generic collation no problems there. Thanks.
> Panos.

OSQL and SQL 2005 Express

Hi All... I'm trying to upgrade one of our MSDE-based products to SQL 2005 Express. The installation of the product includes the use of a bat file that makes several OSQL calls. This bat file runs on the machine that has SQL 2005 Express installed and is failing - times out trying to connect. I've gotten away from the bat file and am now just trying to run OSQL against the db from a dos prompt. I'm getting the following:

C:\Documents and Settings\cdehaven>osql -E
[SQL Native Client]Named Pipes Provider: Could not open a connection to SQLServer [2].
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections.

Any thoughts? Thanks much!!

Curt

Answered my own question! I added a switch to the OSQL command line:

-S.\sqlexpress

This specifies the server and i suppose instance of sql server to connect to. I never needed to do this with MSDE, but I guess we need to now...

Curt

|||

MSDE installed as a default instance by default, SQL Server 2005 does not, it installs the Express instance by default in Machine\SQLExpress. if you are using a named instance you will either have to provide the following syntax (or use a SQLAlias on the machines)

1. Machine\InstanceName
2 Machinename,Portnumber (Which will redirect to the named instance, be aware that this is impratical for SQL Server Express named instances by default as the port is dynamically choosenupon startup of the service)

Jens K. Suessmeyer

http://www.sqlserver2005.de

osql and 'real' csv format

I want to use osql to generate the query report in comma separated format
(csv), but even though I specified output file as report.csv, the data in the
file is not comma separated, instead, it's just like what we see when we do
query with SQL QUERY ANALYZER with "results in text" menu seletect. for
example, suppose the query is select column1, column2 from table1, and I
expect the data in .csv file as
r11,r12
r21,r22
r31,r32
instead, I get:
header1 header2
-- --
r11 r12
r21 r22
r31 r32
my statement is:
"c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -o
c:\mydb\report.csv
what am i missing here and how to get report in 'real' csv format?
Appreciate your help and time on this, THANKS!
You may try something like
[script]
bcp "SELECT au_fname, au_lname FROM pubs..Authors ORDER BY au_lname"
queryout c:\Authors.txt -t , -r \n -c -SYourServerName -T
[/script]
And take a look at this article "Showdown-bcp vs. DTS"
http://www.windowsitpro.com/Articles...rticleID=19760
Cristian Lefter, SQL Server MVP
"gongzuo" <gongzuo@.discussions.microsoft.com> wrote in message
news:A7B5CE27-E2C1-4BCE-9B24-7EBF0588C921@.microsoft.com...
>I want to use osql to generate the query report in comma separated format
> (csv), but even though I specified output file as report.csv, the data in
> the
> file is not comma separated, instead, it's just like what we see when we
> do
> query with SQL QUERY ANALYZER with "results in text" menu seletect. for
> example, suppose the query is select column1, column2 from table1, and I
> expect the data in .csv file as
> r11,r12
> r21,r22
> r31,r32
> instead, I get:
> header1 header2
> -- --
> r11 r12
> r21 r22
> r31 r32
> my statement is:
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -o
> c:\mydb\report.csv
> what am i missing here and how to get report in 'real' csv format?
> Appreciate your help and time on this, THANKS!
|||Try this
"c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -o
c:\mydb\report.csv
"gongzuo" wrote:

> I want to use osql to generate the query report in comma separated format
> (csv), but even though I specified output file as report.csv, the data in the
> file is not comma separated, instead, it's just like what we see when we do
> query with SQL QUERY ANALYZER with "results in text" menu seletect. for
> example, suppose the query is select column1, column2 from table1, and I
> expect the data in .csv file as
> r11,r12
> r21,r22
> r31,r32
> instead, I get:
> header1 header2
> -- --
> r11 r12
> r21 r22
> r31 r32
> my statement is:
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -o
> c:\mydb\report.csv
> what am i missing here and how to get report in 'real' csv format?
> Appreciate your help and time on this, THANKS!
|||Try this
"c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -o
c:\mydb\report.csv
"gongzuo" wrote:

> I want to use osql to generate the query report in comma separated format
> (csv), but even though I specified output file as report.csv, the data in the
> file is not comma separated, instead, it's just like what we see when we do
> query with SQL QUERY ANALYZER with "results in text" menu seletect. for
> example, suppose the query is select column1, column2 from table1, and I
> expect the data in .csv file as
> r11,r12
> r21,r22
> r31,r32
> instead, I get:
> header1 header2
> -- --
> r11 r12
> r21 r22
> r31 r32
> my statement is:
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -o
> c:\mydb\report.csv
> what am i missing here and how to get report in 'real' csv format?
> Appreciate your help and time on this, THANKS!
|||it works, Thanks!
one more question:
how to generate a 'real' .xls format report using the same method?
or what's the col_separator character for an .xls format?
[i changed the generated .csv to .xls and the data are not align with
the columns in .xls file; I changed the '.csv' to '.xls' in the statement
only to generated the same in wrong format.
"msdnbuddy" wrote:
[vbcol=seagreen]
> Try this
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -o
> c:\mydb\report.csv
> "gongzuo" wrote:
|||it works, thanks!
one more: what's the colomn-separator character for an .xls format?
i changed the report generated by the above statement from 'report.csv'
to 'report.xls' and the data are not align with the columns in the file;
I changed the '.csv' to '.xls' in the statement and only get the similar
wrong .xls format]
"msdnbuddy" wrote:
[vbcol=seagreen]
> Try this
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -o
> c:\mydb\report.csv
> "gongzuo" wrote:
|||The problem that was causing the columns to not align was that the column
width of the output file was (by default) 80 cols. This can be corrected
with the -w switch
"c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -w
500 -o
c:\mydb\report.csv
Depending on how many columns are returned in your select statement, I would
suggest that you increase or decrease the width. I did a "select * from
orders" on the northwind db and widht of 500 resulted in a well formatted
..xls file. Give it a try.
Cheers.
"gongzuo" wrote:
[vbcol=seagreen]
> it works, thanks!
> one more: what's the colomn-separator character for an .xls format?
> i changed the report generated by the above statement from 'report.csv'
> to 'report.xls' and the data are not align with the columns in the file;
> I changed the '.csv' to '.xls' in the statement and only get the similar
> wrong .xls format]
> "msdnbuddy" wrote:
|||Thanks !
another problem :
using -s","
I couldn't get the whole date_time , for example, for the date_time is
2005-04-20 14:38:36.000, I only get 38:36:0
I did "select BirthDate from employees" on the nothwind db and get 00:00:0
for all the 9 records.
appreciate your help and time!
"msdnbuddy" wrote:
[vbcol=seagreen]
> The problem that was causing the columns to not align was that the column
> width of the output file was (by default) 80 cols. This can be corrected
> with the -w switch
> "c:\sql2ksp3\x86\binn\osql" -E -d mydb -Q "select * from table1" -s"," -w
> 500 -o
> c:\mydb\report.csv
> Depending on how many columns are returned in your select statement, I would
> suggest that you increase or decrease the width. I did a "select * from
> orders" on the northwind db and widht of 500 resulted in a well formatted
> .xls file. Give it a try.
> Cheers.
> "gongzuo" wrote:
|||I too am having this date problem. Also, can osql use tabs as delimiters? -s
"\t" doesn't work.
"gongzuo" wrote:
[vbcol=seagreen]
> Thanks !
> another problem :
> using -s","
> I couldn't get the whole date_time , for example, for the date_time is
> 2005-04-20 14:38:36.000, I only get 38:36:0
> I did "select BirthDate from employees" on the nothwind db and get 00:00:0
> for all the 9 records.
> appreciate your help and time!
> "msdnbuddy" wrote:

osql and line spacing

I am importing a query to a .txt file and for some reason my file is being
written double spaced! Is there a parameter I am overlooking that will
control this' I do not know how long my lines will be so I am setting them
to be arbitrarily long so that I know that the data will not force a carrage
return.
The statement I am using to excecute my query looks like this:
"osql -SServer -Uusername -Ppassword -h-1 -o C:\Test\test.txt -s","
-w"2000" -Q"Select ' + @.flds + ' from Mytable"
I build my query in the @.flds variable based on values in another table. I
know that that part works. I just can not figure out why my output ends up
double spaced.
Thanks in advance.swag: is word wrap on in your text viewer?
Rick wrote:
> I am importing a query to a .txt file and for some reason my file is being
> written double spaced! Is there a parameter I am overlooking that will
> control this' I do not know how long my lines will be so I am setting the
m
> to be arbitrarily long so that I know that the data will not force a carra
ge
> return.
> The statement I am using to excecute my query looks like this:
> "osql -SServer -Uusername -Ppassword -h-1 -o C:\Test\test.txt -s","
> -w"2000" -Q"Select ' + @.flds + ' from Mytable"
> I build my query in the @.flds variable based on values in another table.
I
> know that that part works. I just can not figure out why my output ends u
p
> double spaced.
> Thanks in advance.
>|||No, But I was using note pad. You made me curious so I tried word pad, all
was better.
THANKS!
"Trey Walpole" wrote:

> swag: is word wrap on in your text viewer?
> Rick wrote:
>

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 and carc.:

I run a script with Osql and got no error but the accent caracter (french message) are modified.

the script is
CREATE PROCEDURE dbo.AccentTest
AS
Print(' ')
GO
and come
CREATE PROCEDURE dbo.AccentTest
AS
Print('Θ α Φ ε ∩ ∙')
GO
:o
This is very bad! Can someone can help me?
Any suggestion?
PLease....

osql.exe -S MyServer -U username-P pwd-d TestDB -n -o "C:\Dev\osql_test.log" -i "C:\Dev\AccentTest.sql" :What's the default language of the database? I don't have a lot of experience here, but I would start there. Also take a look at varchar and nvarchar.

[Edit: I should note that I have a default install of SQL Server 2000 (SP4). I created the proc as shown above and executed it and it printed the characters correctly.]

Regards,

hmscott

OSQL + sql files with accent characters = problems!

Hi,
I've an SQL file which contain accent characters to fill my default values
like this:
insert into mtytable... values('Fvrier')
and also I create storedprocedures which execute some insert / update
command with the same values.
The SQL file is correct.
I've a batch file (.bat) which execute this command :
osql -E -i "MyScript.sql" -d MyDatabase
but the result is:
the accents disappeared !
Fvrier become Fvrier in the database!
My stored procedure has changed during the OSQL execution.
How can I solve the problem?
The storeprocedure (and other commands) are correct in the .sql file.
thanks.
Jerome.
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> I've an SQL file which contain accent characters to fill my default values
> like this:
> insert into mtytable... values('Fvrier')
> and also I create storedprocedures which execute some insert / update
> command with the same values.
> The SQL file is correct.
> I've a batch file (.bat) which execute this command :
> osql -E -i "MyScript.sql" -d MyDatabase
> but the result is:
> the accents disappeared !
> Fvrier become Fvrier in the database!
> My stored procedure has changed during the OSQL execution.
> How can I solve the problem?
> The storeprocedure (and other commands) are correct in the .sql file.
> thanks.
> Jerome.
>
osql can use Unicode input files, however, when you create the Unicode input
files,
you need to do so using UTF-16 rather than the default UTF-8.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||ok, I'll try this
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uYdrCK0yEHA.2676@.TK2MSFTNGP12.phx.gbl...
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> osql can use Unicode input files, however, when you create the Unicode
> input files,
> you need to do so using UTF-16 rather than the default UTF-8.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

OSQL + sql files with accent characters = problems!

Hi,
I've an SQL file which contain accent characters to fill my default values
like this:
insert into mtytable... values('Fvrier')
and also I create storedprocedures which execute some insert / update
command with the same values.
The SQL file is correct.
I've a batch file (.bat) which execute this command :
osql -E -i "MyScript.sql" -d MyDatabase
but the result is:
the accents disappeared !
Fvrier become Fvrier in the database!
My stored procedure has changed during the OSQL execution.
How can I solve the problem?
The storeprocedure (and other commands) are correct in the .sql file.
thanks.
Jerome.
"Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> I've an SQL file which contain accent characters to fill my default values
> like this:
> insert into mtytable... values('Fvrier')
> and also I create storedprocedures which execute some insert / update
> command with the same values.
> The SQL file is correct.
> I've a batch file (.bat) which execute this command :
> osql -E -i "MyScript.sql" -d MyDatabase
> but the result is:
> the accents disappeared !
> Fvrier become Fvrier in the database!
> My stored procedure has changed during the OSQL execution.
> How can I solve the problem?
> The storeprocedure (and other commands) are correct in the .sql file.
> thanks.
> Jerome.
>
osql can use Unicode input files, however, when you create the Unicode input
files,
you need to do so using UTF-16 rather than the default UTF-8.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||ok, I'll try this
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uYdrCK0yEHA.2676@.TK2MSFTNGP12.phx.gbl...
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> osql can use Unicode input files, however, when you create the Unicode
> input files,
> you need to do so using UTF-16 rather than the default UTF-8.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

OSQL + sql files with accent characters = problems!

Hi,
I've an SQL file which contain accent characters to fill my default values
like this:
insert into mtytable... values('Fvrier')
and also I create storedprocedures which execute some insert / update
command with the same values.
The SQL file is correct.
I've a batch file (.bat) which execute this command :
osql -E -i "MyScript.sql" -d MyDatabase
but the result is:
the accents disappeared !
Fvrier become Fvrier in the database!
My stored procedure has changed during the OSQL execution.
How can I solve the problem?
The storeprocedure (and other commands) are correct in the .sql file.
thanks.
Jerome."Jj" <willgart_A_@.hotmail_A_.com> wrote in message
news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> I've an SQL file which contain accent characters to fill my default values
> like this:
> insert into mtytable... values('Fvrier')
> and also I create storedprocedures which execute some insert / update
> command with the same values.
> The SQL file is correct.
> I've a batch file (.bat) which execute this command :
> osql -E -i "MyScript.sql" -d MyDatabase
> but the result is:
> the accents disappeared !
> Fvrier become Fvrier in the database!
> My stored procedure has changed during the OSQL execution.
> How can I solve the problem?
> The storeprocedure (and other commands) are correct in the .sql file.
> thanks.
> Jerome.
>
osql can use Unicode input files, however, when you create the Unicode input
files,
you need to do so using UTF-16 rather than the default UTF-8.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||ok, I'll try this
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uYdrCK0yEHA.2676@.TK2MSFTNGP12.phx.gbl...
> "Jj" <willgart_A_@.hotmail_A_.com> wrote in message
> news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> osql can use Unicode input files, however, when you create the Unicode
> input files,
> you need to do so using UTF-16 rather than the default UTF-8.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

OSQL + sql files with accent characters = problems!

Hi,
I've an SQL file which contain accent characters to fill my default values
like this:
insert into mtytable... values('Février')
and also I create storedprocedures which execute some insert / update
command with the same values.
The SQL file is correct.
I've a batch file (.bat) which execute this command :
osql -E -i "MyScript.sql" -d MyDatabase
but the result is:
the accents disappeared !
Février become FÚvrier in the database!
My stored procedure has changed during the OSQL execution.
How can I solve the problem?
The storeprocedure (and other commands) are correct in the .sql file.
thanks.
Jerome."Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> I've an SQL file which contain accent characters to fill my default values
> like this:
> insert into mtytable... values('Février')
> and also I create storedprocedures which execute some insert / update
> command with the same values.
> The SQL file is correct.
> I've a batch file (.bat) which execute this command :
> osql -E -i "MyScript.sql" -d MyDatabase
> but the result is:
> the accents disappeared !
> Février become FÚvrier in the database!
> My stored procedure has changed during the OSQL execution.
> How can I solve the problem?
> The storeprocedure (and other commands) are correct in the .sql file.
> thanks.
> Jerome.
>
osql can use Unicode input files, however, when you create the Unicode input
files,
you need to do so using UTF-16 rather than the default UTF-8.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||ok, I'll try this
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uYdrCK0yEHA.2676@.TK2MSFTNGP12.phx.gbl...
> "Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
> news:uIuFDPyyEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> I've an SQL file which contain accent characters to fill my default
>> values like this:
>> insert into mtytable... values('Février')
>> and also I create storedprocedures which execute some insert / update
>> command with the same values.
>> The SQL file is correct.
>> I've a batch file (.bat) which execute this command :
>> osql -E -i "MyScript.sql" -d MyDatabase
>> but the result is:
>> the accents disappeared !
>> Février become FÚvrier in the database!
>> My stored procedure has changed during the OSQL execution.
>> How can I solve the problem?
>> The storeprocedure (and other commands) are correct in the .sql file.
>> thanks.
>> Jerome.
>>
> osql can use Unicode input files, however, when you create the Unicode
> input files,
> you need to do so using UTF-16 rather than the default UTF-8.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

osql :r filenames with spaces

osql :r doesn't seem to like file names with spaces. Don't want to have to
use shortnames if I don't have to.
What's the fix?
Thanks
Martin
Hi,
Use double quotes (") while mentioning the file names with spaces (special
charecters)
Sample:-
osql -Usa -Sa266uss\harisql -Phari -Q"select * from
master..sysobjects" -o"c:\sys objects.txt"
Thanks
Hari
MCDBA
"Martin" <x@.y.z> wrote in message
news:e67N1YkaEHA.2340@.TK2MSFTNGP09.phx.gbl...
> osql :r doesn't seem to like file names with spaces. Don't want to have
to
> use shortnames if I don't have to.
> What's the fix?
> Thanks
> Martin
>

osql :r filename

Hello,
somebody have already used this option od osql ==> :r filename
Could you give me some example
thanks
Put below in a bat file. It will create an inner script file as sell as an outer script file and
then call OSQL with the outer script file as input file.
ECHO SELECT 'Inner script' > inner.sql
ECHO SELECT 'Outer Script before' > outer.sql
ECHO :r inner.sql >> outer.sql
ECHO SELECT 'Outer cript after' >> outer.sql
OSQL /E /i outer.sql
PAUSE
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ft" <ft@.discussions.microsoft.com> wrote in message
news:E3423CEB-A2B3-4140-9971-B6D31905BF15@.microsoft.com...
> Hello,
> somebody have already used this option od osql ==> :r filename
> Could you give me some example
> thanks
|||Thanks for your response.
I try to run the scrip "all.sql" which contains 2 other scripts . scr1.sql
and scr2.sql
these scripts contains "go" statement".
there is the contain of "all.sql"
:r scr1.sql
:r scr2.sql
when I run this command on line
osql -S Server -d my base -U my user -P my pass -i all.sql -o out.txt
I obtain the error message : "Incorrect syntax near 'GO'.
If I run the scr1.sql directly from a command line, that is ok. There is no
error message.
Do you have any idea about this problem.
Thanks
"Tibor Karaszi" wrote:

> Put below in a bat file. It will create an inner script file as sell as an outer script file and
> then call OSQL with the outer script file as input file.
> ECHO SELECT 'Inner script' > inner.sql
> ECHO SELECT 'Outer Script before' > outer.sql
> ECHO :r inner.sql >> outer.sql
> ECHO SELECT 'Outer cript after' >> outer.sql
> OSQL /E /i outer.sql
> PAUSE
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ft" <ft@.discussions.microsoft.com> wrote in message
> news:E3423CEB-A2B3-4140-9971-B6D31905BF15@.microsoft.com...
>
>
|||An inner script called by OSQL using :r cannot contain GO, I'm afraid...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ft" <ft@.discussions.microsoft.com> wrote in message
news:98DBCA95-E826-48D3-BE60-1D10EBFC8E79@.microsoft.com...[vbcol=seagreen]
> Thanks for your response.
> I try to run the scrip "all.sql" which contains 2 other scripts . scr1.sql
> and scr2.sql
> these scripts contains "go" statement".
> there is the contain of "all.sql"
> :r scr1.sql
> :r scr2.sql
> when I run this command on line
> osql -S Server -d my base -U my user -P my pass -i all.sql -o out.txt
> I obtain the error message : "Incorrect syntax near 'GO'.
> If I run the scr1.sql directly from a command line, that is ok. There is no
> error message.
> Do you have any idea about this problem.
> Thanks
>
> "Tibor Karaszi" wrote:

osql :r filename

Hello,
somebody have already used this option od osql ==> :r filename
Could you give me some example
thanksPut below in a bat file. It will create an inner script file as sell as an o
uter script file and
then call OSQL with the outer script file as input file.
ECHO SELECT 'Inner script' > inner.sql
ECHO SELECT 'Outer Script before' > outer.sql
ECHO :r inner.sql >> outer.sql
ECHO SELECT 'Outer cript after' >> outer.sql
OSQL /E /i outer.sql
PAUSE
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ft" <ft@.discussions.microsoft.com> wrote in message
news:E3423CEB-A2B3-4140-9971-B6D31905BF15@.microsoft.com...
> Hello,
> somebody have already used this option od osql ==> :r filename
> Could you give me some example
> thanks|||Thanks for your response.
I try to run the scrip "all.sql" which contains 2 other scripts . scr1.sql
and scr2.sql
these scripts contains "go" statement".
there is the contain of "all.sql"
:r scr1.sql
:r scr2.sql
when I run this command on line
osql -S Server -d my base -U my user -P my pass -i all.sql -o out.txt
I obtain the error message : "Incorrect syntax near 'GO'.
If I run the scr1.sql directly from a command line, that is ok. There is no
error message.
Do you have any idea about this problem.
Thanks
"Tibor Karaszi" wrote:

> Put below in a bat file. It will create an inner script file as sell as an
outer script file and
> then call OSQL with the outer script file as input file.
> ECHO SELECT 'Inner script' > inner.sql
> ECHO SELECT 'Outer Script before' > outer.sql
> ECHO :r inner.sql >> outer.sql
> ECHO SELECT 'Outer cript after' >> outer.sql
> OSQL /E /i outer.sql
> PAUSE
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ft" <ft@.discussions.microsoft.com> wrote in message
> news:E3423CEB-A2B3-4140-9971-B6D31905BF15@.microsoft.com...
>
>|||An inner script called by OSQL using :r cannot contain GO, I'm afraid...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ft" <ft@.discussions.microsoft.com> wrote in message
news:98DBCA95-E826-48D3-BE60-1D10EBFC8E79@.microsoft.com...[vbcol=seagreen]
> Thanks for your response.
> I try to run the scrip "all.sql" which contains 2 other scripts . scr1.sql
> and scr2.sql
> these scripts contains "go" statement".
> there is the contain of "all.sql"
> :r scr1.sql
> :r scr2.sql
> when I run this command on line
> osql -S Server -d my base -U my user -P my pass -i all.sql -o out.txt
> I obtain the error message : "Incorrect syntax near 'GO'.
> If I run the scr1.sql directly from a command line, that is ok. There is n
o
> error message.
> Do you have any idea about this problem.
> Thanks
>
> "Tibor Karaszi" wrote:
>

osql :r filename

Hello,
somebody have already used this option od osql ==> :r filename
Could you give me some example
thanksPut below in a bat file. It will create an inner script file as sell as an outer script file and
then call OSQL with the outer script file as input file.
ECHO SELECT 'Inner script' > inner.sql
ECHO SELECT 'Outer Script before' > outer.sql
ECHO :r inner.sql >> outer.sql
ECHO SELECT 'Outer cript after' >> outer.sql
OSQL /E /i outer.sql
PAUSE
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ft" <ft@.discussions.microsoft.com> wrote in message
news:E3423CEB-A2B3-4140-9971-B6D31905BF15@.microsoft.com...
> Hello,
> somebody have already used this option od osql ==> :r filename
> Could you give me some example
> thanks|||Thanks for your response.
I try to run the scrip "all.sql" which contains 2 other scripts . scr1.sql
and scr2.sql
these scripts contains "go" statement".
there is the contain of "all.sql"
:r scr1.sql
:r scr2.sql
when I run this command on line
osql -S Server -d my base -U my user -P my pass -i all.sql -o out.txt
I obtain the error message : "Incorrect syntax near 'GO'.
If I run the scr1.sql directly from a command line, that is ok. There is no
error message.
Do you have any idea about this problem.
Thanks
"Tibor Karaszi" wrote:
> Put below in a bat file. It will create an inner script file as sell as an outer script file and
> then call OSQL with the outer script file as input file.
> ECHO SELECT 'Inner script' > inner.sql
> ECHO SELECT 'Outer Script before' > outer.sql
> ECHO :r inner.sql >> outer.sql
> ECHO SELECT 'Outer cript after' >> outer.sql
> OSQL /E /i outer.sql
> PAUSE
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ft" <ft@.discussions.microsoft.com> wrote in message
> news:E3423CEB-A2B3-4140-9971-B6D31905BF15@.microsoft.com...
> > Hello,
> >
> > somebody have already used this option od osql ==> :r filename
> >
> > Could you give me some example
> >
> > thanks
>
>|||An inner script called by OSQL using :r cannot contain GO, I'm afraid...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ft" <ft@.discussions.microsoft.com> wrote in message
news:98DBCA95-E826-48D3-BE60-1D10EBFC8E79@.microsoft.com...
> Thanks for your response.
> I try to run the scrip "all.sql" which contains 2 other scripts . scr1.sql
> and scr2.sql
> these scripts contains "go" statement".
> there is the contain of "all.sql"
> :r scr1.sql
> :r scr2.sql
> when I run this command on line
> osql -S Server -d my base -U my user -P my pass -i all.sql -o out.txt
> I obtain the error message : "Incorrect syntax near 'GO'.
> If I run the scr1.sql directly from a command line, that is ok. There is no
> error message.
> Do you have any idea about this problem.
> Thanks
>
> "Tibor Karaszi" wrote:
> > Put below in a bat file. It will create an inner script file as sell as an outer script file and
> > then call OSQL with the outer script file as input file.
> >
> > ECHO SELECT 'Inner script' > inner.sql
> > ECHO SELECT 'Outer Script before' > outer.sql
> > ECHO :r inner.sql >> outer.sql
> > ECHO SELECT 'Outer cript after' >> outer.sql
> > OSQL /E /i outer.sql
> > PAUSE
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "ft" <ft@.discussions.microsoft.com> wrote in message
> > news:E3423CEB-A2B3-4140-9971-B6D31905BF15@.microsoft.com...
> > > Hello,
> > >
> > > somebody have already used this option od osql ==> :r filename
> > >
> > > Could you give me some example
> > >
> > > thanks
> >
> >
> >

osql - restore database

Please help,
Using OSQL
How can I restore into a database called ‘mydb1’ from a backup of database
‘mydb2’
Tanks
hi Carlos,
Carlos AZ wrote:
> Please help,
> Using OSQL
> How can I restore into a database called 'mydb1' from a backup of
> database 'mydb2'
>
you have to modify the database name to the new name..
and of course specify a different file system location for the physical
files or you will overwrite the original mydb1 files..
following you can see a statement that restores to pubs2 the pubs database
backup, moving the datafile and the transaction log file to new physical
files..
RESTORE DATABASE [pubs2] FROM DISK = N'C:\Programmi\Microsoft SQL
Server\MSSQL\BACKUP\pubs.bak'
WITH FILE = 1,
NOUNLOAD ,
STATS = 10,
RECOVERY ,
MOVE N'pubs' TO N'C:\Programmi\Microsoft SQL
Server\MSSQL\data\pubs_2.mdf',
MOVE N'pubs_log' TO N'C:\Programmi\Microsoft SQL
Server\MSSQL\data\pubs_log_2.ldf'
please have a look at
http://msdn.microsoft.com/library/de...ra-rz_25rm.asp
for RESTORE statement synopsis
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

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 - creating a DB - permission problem.

Hi;
I am trying to create a database from the command line using osql and a .sql
file. If I create the database first in the enterprise manager and then run
it - it works fine. But if the database does not exist I get:
C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
Server\90\Tools\Binn\OS
QL.EXE" -S ARIEL -d WindwardPortal -E -i
\src\RePortal\SqlScripts\SqlServer.sql
Login failed for user 'THIELEN\dave'.
[SQL Native Client]Shared Memory Provider: The system cannot open the file.
[SQL Native Client]Communication link failure
Cannot open database requested in login 'WindwardPortal'. Login fails.
The file SqlServer.sql starts with:
CREATE DATABASE [WindwardPortal]
GO
use [WindwardPortal]
GO
/****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006 2:06:26
PM ******/
CREATE TABLE [dbo].[Datasource] (
[datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
any ideas?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
Since the database has not yet been created. OSQL cannot set the database
context to that database with the "-d WindwardPortal" specification. You
can remove "-d WindwardPortal" since you have a USE following the CREATE
DATABASE.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:1D648FD7-2565-4DFD-B3AF-1867E6B3445C@.microsoft.com...
> Hi;
> I am trying to create a database from the command line using osql and a
> .sql
> file. If I create the database first in the enterprise manager and then
> run
> it - it works fine. But if the database does not exist I get:
> C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
> Server\90\Tools\Binn\OS
> QL.EXE" -S ARIEL -d WindwardPortal -E -i
> \src\RePortal\SqlScripts\SqlServer.sql
> Login failed for user 'THIELEN\dave'.
> [SQL Native Client]Shared Memory Provider: The system cannot open the
> file.
> [SQL Native Client]Communication link failure
> Cannot open database requested in login 'WindwardPortal'. Login fails.
> The file SqlServer.sql starts with:
> CREATE DATABASE [WindwardPortal]
> GO
> use [WindwardPortal]
> GO
> /****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006
> 2:06:26
> PM ******/
> CREATE TABLE [dbo].[Datasource] (
> [datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
> any ideas?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
|||Hello,
Looks like the path for SQLServer.SQL is wrong. As a first step try login
using OSQL with out giving the database name and script name.
OSQL -S Servername -E
Thanks
Hari
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:1D648FD7-2565-4DFD-B3AF-1867E6B3445C@.microsoft.com...
> Hi;
> I am trying to create a database from the command line using osql and a
> .sql
> file. If I create the database first in the enterprise manager and then
> run
> it - it works fine. But if the database does not exist I get:
> C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
> Server\90\Tools\Binn\OS
> QL.EXE" -S ARIEL -d WindwardPortal -E -i
> \src\RePortal\SqlScripts\SqlServer.sql
> Login failed for user 'THIELEN\dave'.
> [SQL Native Client]Shared Memory Provider: The system cannot open the
> file.
> [SQL Native Client]Communication link failure
> Cannot open database requested in login 'WindwardPortal'. Login fails.
> The file SqlServer.sql starts with:
> CREATE DATABASE [WindwardPortal]
> GO
> use [WindwardPortal]
> GO
> /****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006
> 2:06:26
> PM ******/
> CREATE TABLE [dbo].[Datasource] (
> [datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
> any ideas?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>

osql - creating a DB - permission problem.

Hi;
I am trying to create a database from the command line using osql and a .sql
file. If I create the database first in the enterprise manager and then run
it - it works fine. But if the database does not exist I get:
C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
Server\90\Tools\Binn\OS
QL.EXE" -S ARIEL -d WindwardPortal -E -i
\src\RePortal\SqlScripts\SqlServer.sql
Login failed for user 'THIELEN\dave'.
[SQL Native Client]Shared Memory Provider: The system cannot open the fi
le.
[SQL Native Client]Communication link failure
Cannot open database requested in login 'WindwardPortal'. Login fails.
The file SqlServer.sql starts with:
CREATE DATABASE [WindwardPortal]
GO
use [WindwardPortal]
GO
/****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006
2:06:26
PM ******/
CREATE TABLE [dbo].[Datasource] (
[datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
any ideas?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htmSince the database has not yet been created. OSQL cannot set the database
context to that database with the "-d WindwardPortal" specification. You
can remove "-d WindwardPortal" since you have a USE following the CREATE
DATABASE.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:1D648FD7-2565-4DFD-B3AF-1867E6B3445C@.microsoft.com...
> Hi;
> I am trying to create a database from the command line using osql and a
> .sql
> file. If I create the database first in the enterprise manager and then
> run
> it - it works fine. But if the database does not exist I get:
> C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
> Server\90\Tools\Binn\OS
> QL.EXE" -S ARIEL -d WindwardPortal -E -i
> \src\RePortal\SqlScripts\SqlServer.sql
> Login failed for user 'THIELEN\dave'.
> [SQL Native Client]Shared Memory Provider: The system cannot open the
> file.
> [SQL Native Client]Communication link failure
> Cannot open database requested in login 'WindwardPortal'. Login fails.
> The file SqlServer.sql starts with:
> CREATE DATABASE [WindwardPortal]
> GO
> use [WindwardPortal]
> GO
> /****** Object: Table [dbo].[Datasource] Script Date: 11/16/20
06
> 2:06:26
> PM ******/
> CREATE TABLE [dbo].[Datasource] (
> [datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
> any ideas?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hello,
Looks like the path for SQLServer.SQL is wrong. As a first step try login
using OSQL with out giving the database name and script name.
OSQL -S Servername -E
Thanks
Hari
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:1D648FD7-2565-4DFD-B3AF-1867E6B3445C@.microsoft.com...
> Hi;
> I am trying to create a database from the command line using osql and a
> .sql
> file. If I create the database first in the enterprise manager and then
> run
> it - it works fine. But if the database does not exist I get:
> C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
> Server\90\Tools\Binn\OS
> QL.EXE" -S ARIEL -d WindwardPortal -E -i
> \src\RePortal\SqlScripts\SqlServer.sql
> Login failed for user 'THIELEN\dave'.
> [SQL Native Client]Shared Memory Provider: The system cannot open the
> file.
> [SQL Native Client]Communication link failure
> Cannot open database requested in login 'WindwardPortal'. Login fails.
> The file SqlServer.sql starts with:
> CREATE DATABASE [WindwardPortal]
> GO
> use [WindwardPortal]
> GO
> /****** Object: Table [dbo].[Datasource] Script Date: 11/16/20
06
> 2:06:26
> PM ******/
> CREATE TABLE [dbo].[Datasource] (
> [datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
> any ideas?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>

osql - creating a DB - permission problem.

Hi;
I am trying to create a database from the command line using osql and a .sql
file. If I create the database first in the enterprise manager and then run
it - it works fine. But if the database does not exist I get:
C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
Server\90\Tools\Binn\OS
QL.EXE" -S ARIEL -d WindwardPortal -E -i
\src\RePortal\SqlScripts\SqlServer.sql
Login failed for user 'THIELEN\dave'.
[SQL Native Client]Shared Memory Provider: The system cannot open the file.
[SQL Native Client]Communication link failure
Cannot open database requested in login 'WindwardPortal'. Login fails.
The file SqlServer.sql starts with:
CREATE DATABASE [WindwardPortal]
GO
use [WindwardPortal]
GO
/****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006 2:06:26
PM ******/
CREATE TABLE [dbo].[Datasource] (
[datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
any ideas?
--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htmSince the database has not yet been created. OSQL cannot set the database
context to that database with the "-d WindwardPortal" specification. You
can remove "-d WindwardPortal" since you have a USE following the CREATE
DATABASE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:1D648FD7-2565-4DFD-B3AF-1867E6B3445C@.microsoft.com...
> Hi;
> I am trying to create a database from the command line using osql and a
> .sql
> file. If I create the database first in the enterprise manager and then
> run
> it - it works fine. But if the database does not exist I get:
> C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
> Server\90\Tools\Binn\OS
> QL.EXE" -S ARIEL -d WindwardPortal -E -i
> \src\RePortal\SqlScripts\SqlServer.sql
> Login failed for user 'THIELEN\dave'.
> [SQL Native Client]Shared Memory Provider: The system cannot open the
> file.
> [SQL Native Client]Communication link failure
> Cannot open database requested in login 'WindwardPortal'. Login fails.
> The file SqlServer.sql starts with:
> CREATE DATABASE [WindwardPortal]
> GO
> use [WindwardPortal]
> GO
> /****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006
> 2:06:26
> PM ******/
> CREATE TABLE [dbo].[Datasource] (
> [datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
> any ideas?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hello,
Looks like the path for SQLServer.SQL is wrong. As a first step try login
using OSQL with out giving the database name and script name.
OSQL -S Servername -E
Thanks
Hari
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:1D648FD7-2565-4DFD-B3AF-1867E6B3445C@.microsoft.com...
> Hi;
> I am trying to create a database from the command line using osql and a
> .sql
> file. If I create the database first in the enterprise manager and then
> run
> it - it works fine. But if the database does not exist I get:
> C:\src\RePortal\SqlScripts>"\Program Files\Microsoft SQL
> Server\90\Tools\Binn\OS
> QL.EXE" -S ARIEL -d WindwardPortal -E -i
> \src\RePortal\SqlScripts\SqlServer.sql
> Login failed for user 'THIELEN\dave'.
> [SQL Native Client]Shared Memory Provider: The system cannot open the
> file.
> [SQL Native Client]Communication link failure
> Cannot open database requested in login 'WindwardPortal'. Login fails.
> The file SqlServer.sql starts with:
> CREATE DATABASE [WindwardPortal]
> GO
> use [WindwardPortal]
> GO
> /****** Object: Table [dbo].[Datasource] Script Date: 11/16/2006
> 2:06:26
> PM ******/
> CREATE TABLE [dbo].[Datasource] (
> [datasourceId] [int] IDENTITY (1, 1) NOT NULL ,
> any ideas?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>

OSQL - appending output to a file

I want to run a job every hour which calls a stored proc using osql and apppends the results to a text file.
But it always overwrites the previous results. Is there a way of getting osql to append the results instead?Originally posted by mwilkes
I want to run a job every hour which calls a stored proc using osql and apppends the results to a text file.

But it always overwrites the previous results. Is there a way of getting osql to append the results instead?

Good old DOS, you should use batch file to append OSQL results into existing text file, named differently than the one used in OSQL output, something like:

osql ... > Osql.Txt

Type Osql.Txt >> FinalResult.Txt

Where FinalResult.Txt contains all, appended results.

Cheers, Marin

Osql

Hi all.

My first post here.

I want to distriburte a msde database. When doing so, I also want to make a new login, make a user, make the user the dbowner of the database i installed.

I found the Stored procedures I need to use, and I have tested that it works using OSQL.

What I want to do now is to make this automatic. After installing the database the OSQL commands should be executed and no user interference should be necessary. How can that be done ?

peetthrough a *.bat file?|||through a *.bat file?

Ok, how ?

From what I see I have to do the following :

'**************** sample ********************

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

D:\>osql -S PEETSPC\PEETSQL -U sa
Password:
1> sp_attach_single_file_db "TEST","D:\DB\TEST.MDF"
2> go
New log file 'D:\DB\TEST_log.LDF' was created.
1> exit

D:\>osql -S PEETSPC\PEETSQL -U sa -d TEST
Password:
1> sp_addlogin test, mypass, TEST
2> sp_adduser test, test, db_owner
3> go
1> sp_addlogin test, mypass, TEST
2> go
New login created.
1> sp_adduser test, test, db_owner
2> go
Granted database access to 'test'.
'test' added to role 'db_owner'.
1> exit

D:\>

How can I do this using a bat file ?
I know what a bat file is, but I do not see how I can get a bat file to do the above. Please help me out if you can :-)

peet|||*bump*

anyone ?|||You need two separate files. The first one I'll calll test.sql will contain:sp_attach_single_file_db "TEST","D:\DB\TEST.MDF"
go

USE TEST
GO

sp_addlogin test, mypass, TEST
sp_adduser test, test, db_owner
go

sp_addlogin test, mypass, TEST
go

sp_adduser test, test, db_owner
goThe second file I'll call test.bat:osql -S PEETSPC\PEETSQL -U sa - P myPass=123 -i test.sql -o test.rptWhen you run the batch file, it will create the database, add the logins and users, and put the output into the test.rpt file for your reading pleasure.

-PatP

OSQL

Creating a utility that will allow a user to run a query from a BAT file.
BUT the SQL client is not installed on all PCs. How can I modify this to
run, or what must I do in order for this to work from PCs with no SQL client
'
osql -E -S EXCEDE\EXCEDE -d excede_Reporting -q"EXIT(exec isp_SetCrPw %1)"Hi,
you would probably have to write your own application for this using
e.g. .NET. That should be quite easy, as the only thing would be to
have a argument parser in place, the rest is only some simply
SqlConnection and SqlCommand.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||That would be beyond my current abilities. I can write some very simple
scripts, and I have a mild understanding of some VB and .NET, But to write
my own code. Well I have no idea where to start. If you would like to help
and get me started I would greatly appreciate and enjoy the experience.
Thanks.
==============================================="Jens" <Jens@.sqlserver2005.de> wrote in message
news:1171392808.470036.289720@.j27g2000cwj.googlegroups.com...
> Hi,
> you would probably have to write your own application for this using
> e.g. .NET. That should be quite easy, as the only thing would be to
> have a argument parser in place, the rest is only some simply
> SqlConnection and SqlCommand.
>
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Lets take that offline and write me an Email.|||WANNABE;7269311 Wrote:
> That would be beyond my current abilities. I can write some very
> simple
> scripts, and I have a mild understanding of some VB and .NET, But to
> write
> my own code. Well I have no idea where to start. If you would like to
> help
> and get me started I would greatly appreciate and enjoy the
> experience.
> Thanks.
> ===============================================> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1171392808.470036.289720@.j27g2000cwj.googlegroups.com...
> > Hi,
> >
> > you would probably have to write your own application for this using
> > e.g. .NET. That should be quite easy, as the only thing would be to
> > have a argument parser in place, the rest is only some simply
> > SqlConnection and SqlCommand.
> >
> >
> > HTH, Jens K. Suessmeyer.
> >
> > --
> > http://www.sqlserver2005.de
> > --
> >
I have written an OSQL like replacement in C#/.Net 2.0. I am
having a wee bit of an issue though. If the SQL file to be run
has dynamic SQL in it (building SQL strings and then executing
them) then the command will fail - always. I think it has
something to do with the nesting of the quotes. Got any ideas
on what would be causing something like this?
Thanks
escher4096