RSS Text Data Mining: scripts

Showing posts with label scripts. Show all posts

Tuesday, March 20, 2012

Run SQL Script from Web App

Hi,

I have a asp.net 2.0 web app that needs to allow the user to run sql scripts on a database. I provided the user with a large text box which allows them to cut and past their script in. However the script contains complex SQL which cannot be handeled in the normal way of passing a string to a connection object and doing a ExecuteNonQuery.

The scripts contains words such as "GO" which makes teh script fail. Is there a wrapper or method for sending cuch SQL statment. Is there a stored procedures I can call or something that will allow me to excute the TSQL exactly in teh same way Query Analyser does?

Many thanks in advance

PS. yes im aware of the security implications of doing this, I have put the relevant security measures in place

check out the OSQL utility.|||

I am not sure but I think StoredProcedure can not do this because it executes SQL querys. I say I am not sure but if there is some body who is sure 100%, he should let us know:-)

|||

GO is not a valid T-SQL command, so no, you can't send it to the SQL Server and expect it to work. GO is a command to Query Analyzer/osql/sqlcmd/Management Studio, and if you want to emulate it's functionality, your program will have to handle it or call one of the above utilities to do it on your behalf.

Something like (pseudo code here):

dim cmds as string() =string.split({Your query},vbCRLf & "GO" & vbcrlf)

for each cmd in cmds

sqlcmd.commandtext=cmd

sqlcmd.executenonquery

|||

Thanks to everybody for their replies, I have been looking into OSQL Utility approach.

From my basic understanding of OSQL it can only be run from the Command Prompt so their for I would need to create a batch file. Will ASP.NET 2.0 let me call a batch file from within my web application?

Or is there another approach apart from using a batch file?

Many thanks in advanve

Run scripts on multiple servers in SQL Management Studio

I have a number of scripts that I need to run on multiple servers and databases. Once I have created a Solution with them all in, how do I make them run in a specific sequence, and how can I switch the focus of the entire set of scripts to the other databases I want to run them on?

Thanks,

-Rob

There is no way to do this included in SQL Management Studio. Solutions in SMS aren't like VS solutions, in that you can execute code pages and objects. In SMS the solution is just a convenient place to store and organize the script files you are working with.

You would have to open each script seperately and execute it against the server and database you wanted in the order you wanted.

Sorry, it's going to be a manual process. There are some 3rd party tools that might help some. Check Red Gate and APEX products.

|||

You can do this by using a batch file to execute either OSQL or SQLCMD (preferred) - both of which can be used to execute script files. If you pass server and database names as parameters into your batch file you can then pass the parameter values to OSQL or SQLCMD.

I don't have any examples to hand, but the following links should set you on the right track:

http://msdn2.microsoft.com/en-us/library/ms162806.aspx

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/percent.mspx?mfr=true

This is how we deploy changes to our databases in each of our environments - the only thing that changes between each environment is the server name parameter. We generally have one batch file per database and one SQL script per database object - one batch file can be used to execute hundreds of SQL script files if need be.

Chris

|||

Dan and Chris,

Thanks for your response on my question. Not the answers I was hoping to get, but thanks anyway!! I have used the DOS bat file method successfully in the the past, but it is a bit clunky and was hoping against hope that there would be a better method in the SQL management Studio. DO you think I may be looking in the wrong tool?

I can't believe that I am the only one who thinks that the lack of a tool or process to set up implementation of scripts is a serious gap in the SQL Management Studio.

Thanks again, Rob

|||

You have to remember that SQL Server is an RDBMS. Tools provide for development are extras that I find invaluable. Just look at how clunky the management and development tools that come with Oracle have been over the years.

On a good note, you may be able to make some progress by developing your procedures and sql code in Visual Studio instead of SMS. If you have VS 2005 professional edition you can create a sql server database project that will allow you to write t-sql code and execute it. You should be able to write a minor application that would execute the scripts for you against a list of databases and/or servers.

Sorry that there isn't an easier way to do it through SMS. Perhaps it will be a forthcoming feature. I'll suggest it on the product feedback group.

Run scripts on multiple servers in SQL Management Studio

Thanks,

-Rob

You would have to open each script seperately and execute it against the server and database you wanted in the order you wanted.

Sorry, it's going to be a manual process. There are some 3rd party tools that might help some. Check Red Gate and APEX products.

|||

I don't have any examples to hand, but the following links should set you on the right track:

http://msdn2.microsoft.com/en-us/library/ms162806.aspx

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/percent.mspx?mfr=true

Chris

|||

Dan and Chris,

I can't believe that I am the only one who thinks that the lack of a tool or process to set up implementation of scripts is a serious gap in the SQL Management Studio.

Thanks again, Rob

|||

Sorry that there isn't an easier way to do it through SMS. Perhaps it will be a forthcoming feature. I'll suggest it on the product feedback group.

Monday, March 12, 2012

Run Multiple Stored Procedures in order with a script?

Hello. I have to run many sql scripts each day. Sometimes I'll have 100+
scripts to run. The scripts always come in a folder numbered in the order
they need to be run (001 - ScriptA, 002 - ScriptB, etc.).
I've been running them all manaully one by one so that I can look for errors
in the results. I'd love to be able to point a batch file to the folder
with all the scripts, have them run one by one, and have the results output
in one file with:
Results.txt:
001 - ScriptA.sql
Command Completed Successfully
002 - ScriptB.sql
Command Completed Successfully
003 - ScriptC.sql
Error in line 1...etc
004 - ScriptD.sql
Command Completed Successfully
etc.
I have tried piping them using c:\Scipts\*.sql > c:\AllScripts.sql
However, this is often causes problems as sometimes the end of one script
will join directly to the start of the first script making statements that
don't exist (EndscriptGo).. two statements joint into one. Or other times
scripts will be left out for some reason. Piping has caused many problems
and isn't trusted anymore.
I tried using osql
OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo
g
but I don't know how to run scripts 001 through say 108 in order, or how to
have them output into one file.
Is there a way to use this or something else (like run 001*.sql, then run
001+1*.sql [I have no idea how to program!) so that I can just point it to
the folder with the numbered scripts and have them run one by one in order?
Any help is REALLY REALLY appreciated!!!!
Thank you
Why not put the names of the scripts in a table and loop thru with a cursor
and call oSql one script at a time?
Andrew J. Kelly SQL MVP
"Tom" <none@.none.com> wrote in message
news:eDGJSmA0EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hello. I have to run many sql scripts each day. Sometimes I'll have 100+
> scripts to run. The scripts always come in a folder numbered in the order
> they need to be run (001 - ScriptA, 002 - ScriptB, etc.).
> I've been running them all manaully one by one so that I can look for
> errors
> in the results. I'd love to be able to point a batch file to the folder
> with all the scripts, have them run one by one, and have the results
> output
> in one file with:
> Results.txt:
> 001 - ScriptA.sql
> Command Completed Successfully
> --
> 002 - ScriptB.sql
> Command Completed Successfully
> --
> 003 - ScriptC.sql
> Error in line 1...etc
> --
> 004 - ScriptD.sql
> Command Completed Successfully
> --
> etc.
>
> I have tried piping them using c:\Scipts\*.sql > c:\AllScripts.sql
> However, this is often causes problems as sometimes the end of one script
> will join directly to the start of the first script making statements that
> don't exist (EndscriptGo).. two statements joint into one. Or other times
> scripts will be left out for some reason. Piping has caused many problems
> and isn't trusted anymore.
> I tried using osql
> OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo
> g
> but I don't know how to run scripts 001 through say 108 in order, or how
> to
> have them output into one file.
> Is there a way to use this or something else (like run 001*.sql, then run
> 001+1*.sql [I have no idea how to program!) so that I can just point it to
> the folder with the numbered scripts and have them run one by one in
> order?
>
> Any help is REALLY REALLY appreciated!!!!
>
> Thank you
>
>
|||Okay, that is way over my head!
I would have to create a new DB for it because I can't add a new table to
the live DB.
If I tried adding all the script names to a table wouldn't I have to type
all the names of the scritps in the table? If there's a way to automate
that I could try it, but it sounds like it could end up being as manual a
process as running them one by one.
What do you think?
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OeJdMvA0EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Why not put the names of the scripts in a table and loop thru with a
cursor[vbcol=seagreen]
> and call oSql one script at a time?
> --
> Andrew J. Kelly SQL MVP
>
> "Tom" <none@.none.com> wrote in message
> news:eDGJSmA0EHA.4028@.TK2MSFTNGP15.phx.gbl...
100+[vbcol=seagreen]
order[vbcol=seagreen]
script[vbcol=seagreen]
that[vbcol=seagreen]
times[vbcol=seagreen]
problems[vbcol=seagreen]
OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo[vbcol=seagreen]
run[vbcol=seagreen]
to
>
|||See
http://www.nigelrivett.net/s_ProcessAllFilesInDir.html
It will execute a proc for each file in a directory in order of the file name.
Create a proc which takes two parameters, file path and file name and
executes a osql command to run that file with output to a file.
Set @.ProcSp to be this proc and you are done.
"Tom" wrote:

> Hello. I have to run many sql scripts each day. Sometimes I'll have 100+
> scripts to run. The scripts always come in a folder numbered in the order
> they need to be run (001 - ScriptA, 002 - ScriptB, etc.).
> I've been running them all manaully one by one so that I can look for errors
> in the results. I'd love to be able to point a batch file to the folder
> with all the scripts, have them run one by one, and have the results output
> in one file with:
> Results.txt:
> 001 - ScriptA.sql
> Command Completed Successfully
> --
> 002 - ScriptB.sql
> Command Completed Successfully
> --
> 003 - ScriptC.sql
> Error in line 1...etc
> --
> 004 - ScriptD.sql
> Command Completed Successfully
> --
> etc.
>
> I have tried piping them using c:\Scipts\*.sql > c:\AllScripts.sql
> However, this is often causes problems as sometimes the end of one script
> will join directly to the start of the first script making statements that
> don't exist (EndscriptGo).. two statements joint into one. Or other times
> scripts will be left out for some reason. Piping has caused many problems
> and isn't trusted anymore.
> I tried using osql
> OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo
> g
> but I don't know how to run scripts 001 through say 108 in order, or how to
> have them output into one file.
> Is there a way to use this or something else (like run 001*.sql, then run
> 001+1*.sql [I have no idea how to program!) so that I can just point it to
> the folder with the numbered scripts and have them run one by one in order?
>
> Any help is REALLY REALLY appreciated!!!!
>
> Thank you
>
>
|||Awesome, I'll check it out!
Thanks so much Nigel!
Tom
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:3FD23957-DF3C-4EEB-9987-869B69B6C195@.microsoft.com...
> See
> http://www.nigelrivett.net/s_ProcessAllFilesInDir.html
> It will execute a proc for each file in a directory in order of the file
name.[vbcol=seagreen]
> Create a proc which takes two parameters, file path and file name and
> executes a osql command to run that file with output to a file.
> Set @.ProcSp to be this proc and you are done.
>
> "Tom" wrote:
100+[vbcol=seagreen]
order[vbcol=seagreen]
errors[vbcol=seagreen]
output[vbcol=seagreen]
script[vbcol=seagreen]
that[vbcol=seagreen]
times[vbcol=seagreen]
problems[vbcol=seagreen]
OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo[vbcol=seagreen]
to[vbcol=seagreen]
run[vbcol=seagreen]
to[vbcol=seagreen]
order?[vbcol=seagreen]
|||Nigel, is there an output file in this?
Thanks,
Tom
"Tom" <none@.none.com> wrote in message
news:uJKqlyC0EHA.2600@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Awesome, I'll check it out!
> Thanks so much Nigel!
>
> Tom
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:3FD23957-DF3C-4EEB-9987-869B69B6C195@.microsoft.com...
> name.
> 100+
> order
> errors
folder
> output
> script
> that
> times
> problems
>
OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo[vbcol=seagreen]
how[vbcol=seagreen]
> to
> run
it
> to
> order?
>
|||Nigel,
How do I create the @.procSP and what exactly do I put in it in this case?
Thanks,
Tom
"Tom" <none@.none.com> wrote in message
news:uJKqlyC0EHA.2600@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Awesome, I'll check it out!
> Thanks so much Nigel!
>
> Tom
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:3FD23957-DF3C-4EEB-9987-869B69B6C195@.microsoft.com...
> name.
> 100+
> order
> errors
folder
> output
> script
> that
> times
> problems
>
OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo[vbcol=seagreen]
how[vbcol=seagreen]
> to
> run
it
> to
> order?
>
|||Tom wrote:
> but it sounds like it could end up
> being as manual a process as running them one by one.
> What do you think?
Yes, but only the first time...
David Gugick
Imceda Software
www.imceda.com
|||Consider this:
1. Create a text file with the .sql files listed one per line.
2. Create a command/batch file to submit each to osql.exe
3. Have osql send results to the screen, but instead redirect results to a
logfile
For example:
text.txt contents
sqlscript1.sql
sqlscript2.sql
sqlscript3.sql
sqlcommand.cmd contents
for /F %%i IN (text.txt) DO osql -Usa -Ppass -Sdb123 -i%%i >> logfile.txt
So, for each file in text.txt, submit the osql script file and redirect to
logfile.
Check out Windows Help on the For command, it's quite handy. I suggest using
the text.txt file so you can edit which scripts run and in which order, in
case your numeric sequence needs to be reordered.
Larry
"Tom" <none@.none.com> wrote in message
news:eDGJSmA0EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hello. I have to run many sql scripts each day. Sometimes I'll have 100+
> scripts to run. The scripts always come in a folder numbered in the order
> they need to be run (001 - ScriptA, 002 - ScriptB, etc.).
> I've been running them all manaully one by one so that I can look for
errors
> in the results. I'd love to be able to point a batch file to the folder
> with all the scripts, have them run one by one, and have the results
output
> in one file with:
> Results.txt:
> 001 - ScriptA.sql
> Command Completed Successfully
> --
> 002 - ScriptB.sql
> Command Completed Successfully
> --
> 003 - ScriptC.sql
> Error in line 1...etc
> --
> 004 - ScriptD.sql
> Command Completed Successfully
> --
> etc.
>
> I have tried piping them using c:\Scipts\*.sql > c:\AllScripts.sql
> However, this is often causes problems as sometimes the end of one script
> will join directly to the start of the first script making statements that
> don't exist (EndscriptGo).. two statements joint into one. Or other times
> scripts will be left out for some reason. Piping has caused many problems
> and isn't trusted anymore.
> I tried using osql
>
OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo
> g
> but I don't know how to run scripts 001 through say 108 in order, or how
to
> have them output into one file.
> Is there a way to use this or something else (like run 001*.sql, then run
> 001+1*.sql [I have no idea how to program!) so that I can just point it to
> the folder with the numbered scripts and have them run one by one in
order?
>
> Any help is REALLY REALLY appreciated!!!!
>
> Thank you
>
>

Run Multiple Stored Procedures in order with a script?

Hello. I have to run many sql scripts each day. Sometimes I'll have 100+
scripts to run. The scripts always come in a folder numbered in the order
they need to be run (001 - ScriptA, 002 - ScriptB, etc.).
I've been running them all manaully one by one so that I can look for errors
in the results. I'd love to be able to point a batch file to the folder
with all the scripts, have them run one by one, and have the results output
in one file with:
Results.txt:
001 - ScriptA.sql
Command Completed Successfully
--
002 - ScriptB.sql
Command Completed Successfully
--
003 - ScriptC.sql
Error in line 1...etc
--
004 - ScriptD.sql
Command Completed Successfully
--
etc.
I have tried piping them using c:\Scipts\*.sql > c:\AllScripts.sql
However, this is often causes problems as sometimes the end of one script
will join directly to the start of the first script making statements that
don't exist (EndscriptGo).. two statements joint into one. Or other times
scripts will be left out for some reason. Piping has caused many problems
and isn't trusted anymore.
I tried using osql
OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo
g
but I don't know how to run scripts 001 through say 108 in order, or how to
have them output into one file.
Is there a way to use this or something else (like run 001*.sql, then run
001+1*.sql [I have no idea how to program!) so that I can just point it
to
the folder with the numbered scripts and have them run one by one in order'
Any help is REALLY REALLY appreciated!!!!
Thank youWhy not put the names of the scripts in a table and loop thru with a cursor
and call oSql one script at a time?
Andrew J. Kelly SQL MVP
"Tom" <none@.none.com> wrote in message
news:eDGJSmA0EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hello. I have to run many sql scripts each day. Sometimes I'll have 100+
> scripts to run. The scripts always come in a folder numbered in the order
> they need to be run (001 - ScriptA, 002 - ScriptB, etc.).
> I've been running them all manaully one by one so that I can look for
> errors
> in the results. I'd love to be able to point a batch file to the folder
> with all the scripts, have them run one by one, and have the results
> output
> in one file with:
> Results.txt:
> 001 - ScriptA.sql
> Command Completed Successfully
> --
> 002 - ScriptB.sql
> Command Completed Successfully
> --
> 003 - ScriptC.sql
> Error in line 1...etc
> --
> 004 - ScriptD.sql
> Command Completed Successfully
> --
> etc.
>
> I have tried piping them using c:\Scipts\*.sql > c:\AllScripts.sql
> However, this is often causes problems as sometimes the end of one script
> will join directly to the start of the first script making statements that
> don't exist (EndscriptGo).. two statements joint into one. Or other times
> scripts will be left out for some reason. Piping has caused many problems
> and isn't trusted anymore.
> I tried using osql
> OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.
lo
> g
> but I don't know how to run scripts 001 through say 108 in order, or how
> to
> have them output into one file.
> Is there a way to use this or something else (like run 001*.sql, then run
> 001+1*.sql [I have no idea how to program!) so that I can just point i
t to
> the folder with the numbered scripts and have them run one by one in
> order'
>
> Any help is REALLY REALLY appreciated!!!!
>
> Thank you
>
>|||Okay, that is way over my head!
I would have to create a new DB for it because I can't add a new table to
the live DB.
If I tried adding all the script names to a table wouldn't I have to type
all the names of the scritps in the table? If there's a way to automate
that I could try it, but it sounds like it could end up being as manual a
process as running them one by one.
What do you think?
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OeJdMvA0EHA.3908@.TK2MSFTNGP12.phx.gbl...
> Why not put the names of the scripts in a table and loop thru with a
cursor
> and call oSql one script at a time?
> --
> Andrew J. Kelly SQL MVP
>
> "Tom" <none@.none.com> wrote in message
> news:eDGJSmA0EHA.4028@.TK2MSFTNGP15.phx.gbl...
100+[vbcol=seagreen]
order[vbcol=seagreen]
script[vbcol=seagreen]
that[vbcol=seagreen]
times[vbcol=seagreen]
problems[vbcol=seagreen]
OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo[vbcol=seagreen]
run[vbcol=seagreen]
to[vbcol=seagreen]
>|||See
http://www.nigelrivett.net/s_ProcessAllFilesInDir.html
It will execute a proc for each file in a directory in order of the file nam
e.
Create a proc which takes two parameters, file path and file name and
executes a osql command to run that file with output to a file.
Set @.ProcSp to be this proc and you are done.
"Tom" wrote:

> Hello. I have to run many sql scripts each day. Sometimes I'll have 100+
> scripts to run. The scripts always come in a folder numbered in the order
> they need to be run (001 - ScriptA, 002 - ScriptB, etc.).
> I've been running them all manaully one by one so that I can look for erro
rs
> in the results. I'd love to be able to point a batch file to the folder
> with all the scripts, have them run one by one, and have the results outpu
t
> in one file with:
> Results.txt:
> 001 - ScriptA.sql
> Command Completed Successfully
> --
> 002 - ScriptB.sql
> Command Completed Successfully
> --
> 003 - ScriptC.sql
> Error in line 1...etc
> --
> 004 - ScriptD.sql
> Command Completed Successfully
> --
> etc.
>
> I have tried piping them using c:\Scipts\*.sql > c:\AllScripts.sql
> However, this is often causes problems as sometimes the end of one script
> will join directly to the start of the first script making statements that
> don't exist (EndscriptGo).. two statements joint into one. Or other times
> scripts will be left out for some reason. Piping has caused many problems
> and isn't trusted anymore.
> I tried using osql
> OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.
lo
> g
> but I don't know how to run scripts 001 through say 108 in order, or how t
o
> have them output into one file.
> Is there a way to use this or something else (like run 001*.sql, then run
> 001+1*.sql [I have no idea how to program!) so that I can just point i
t to
> the folder with the numbered scripts and have them run one by one in order
'
>
> Any help is REALLY REALLY appreciated!!!!
>
> Thank you
>
>|||Awesome, I'll check it out!
Thanks so much Nigel!
Tom
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:3FD23957-DF3C-4EEB-9987-869B69B6C195@.microsoft.com...
> See
> http://www.nigelrivett.net/s_ProcessAllFilesInDir.html
> It will execute a proc for each file in a directory in order of the file
name.[vbcol=seagreen]
> Create a proc which takes two parameters, file path and file name and
> executes a osql command to run that file with output to a file.
> Set @.ProcSp to be this proc and you are done.
>
> "Tom" wrote:
>
100+[vbcol=seagreen]
order[vbcol=seagreen]
errors[vbcol=seagreen]
output[vbcol=seagreen]
script[vbcol=seagreen]
that[vbcol=seagreen]
times[vbcol=seagreen]
problems[vbcol=seagreen]
OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo[vbcol=seagreen]
to[vbcol=seagreen]
run[vbcol=seagreen]
to[vbcol=seagreen]
order'[vbcol=seagreen]|||Nigel, is there an output file in this?
Thanks,
Tom
"Tom" <none@.none.com> wrote in message
news:uJKqlyC0EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Awesome, I'll check it out!
> Thanks so much Nigel!
>
> Tom
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:3FD23957-DF3C-4EEB-9987-869B69B6C195@.microsoft.com...
> name.
> 100+
> order
> errors
folder[vbcol=seagreen]
> output
> script
> that
> times
> problems
>
OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo
how[vbcol=seagreen]
> to
> run
it[vbcol=seagreen]
> to
> order'
>|||Nigel,
How do I create the @.procSP and what exactly do I put in it in this case?
Thanks,
Tom
"Tom" <none@.none.com> wrote in message
news:uJKqlyC0EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Awesome, I'll check it out!
> Thanks so much Nigel!
>
> Tom
> "Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
> news:3FD23957-DF3C-4EEB-9987-869B69B6C195@.microsoft.com...
> name.
> 100+
> order
> errors
folder[vbcol=seagreen]
> output
> script
> that
> times
> problems
>
OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo
how[vbcol=seagreen]
> to
> run
it[vbcol=seagreen]
> to
> order'
>|||Tom wrote:
> but it sounds like it could end up
> being as manual a process as running them one by one.
> What do you think?
Yes, but only the first time...
David Gugick
Imceda Software
www.imceda.com|||Consider this:
1. Create a text file with the .sql files listed one per line.
2. Create a command/batch file to submit each to osql.exe
3. Have osql send results to the screen, but instead redirect results to a
logfile
For example:
text.txt contents
--
sqlscript1.sql
sqlscript2.sql
sqlscript3.sql
sqlcommand.cmd contents
--
for /F %%i IN (text.txt) DO osql -Usa -Ppass -Sdb123 -i%%i >> logfile.txt
So, for each file in text.txt, submit the osql script file and redirect to
logfile.
Check out Windows Help on the For command, it's quite handy. I suggest using
the text.txt file so you can edit which scripts run and in which order, in
case your numeric sequence needs to be reordered.
Larry
"Tom" <none@.none.com> wrote in message
news:eDGJSmA0EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hello. I have to run many sql scripts each day. Sometimes I'll have 100+
> scripts to run. The scripts always come in a folder numbered in the order
> they need to be run (001 - ScriptA, 002 - ScriptB, etc.).
> I've been running them all manaully one by one so that I can look for
errors
> in the results. I'd love to be able to point a batch file to the folder
> with all the scripts, have them run one by one, and have the results
output
> in one file with:
> Results.txt:
> 001 - ScriptA.sql
> Command Completed Successfully
> --
> 002 - ScriptB.sql
> Command Completed Successfully
> --
> 003 - ScriptC.sql
> Error in line 1...etc
> --
> 004 - ScriptD.sql
> Command Completed Successfully
> --
> etc.
>
> I have tried piping them using c:\Scipts\*.sql > c:\AllScripts.sql
> However, this is often causes problems as sometimes the end of one script
> will join directly to the start of the first script making statements that
> don't exist (EndscriptGo).. two statements joint into one. Or other times
> scripts will be left out for some reason. Piping has caused many problems
> and isn't trusted anymore.
> I tried using osql
>
OSQL -Usa -Ppass -Sdb123 -iC:\scripts\001scriptA.sql -oC:\logs\001scriptA.lo
> g
> but I don't know how to run scripts 001 through say 108 in order, or how
to
> have them output into one file.
> Is there a way to use this or something else (like run 001*.sql, then run
> 001+1*.sql [I have no idea how to program!) so that I can just point i
t to
> the folder with the numbered scripts and have them run one by one in
order'
>
> Any help is REALLY REALLY appreciated!!!!
>
> Thank you
>
>

Wednesday, March 7, 2012

Run all .sql files in a folder

I have created scripts to create approximately 200 tables. I have all of
these .sql files saved in a folder on a server. Is there a way to execute o
r
run all of the files at once in this folder instead of running them one at a
time?
ThanksYou could write a batch file - use a 'for' loop and call osql passing the
file path and other appropriate parameters.
"bbasile" wrote:

> I have created scripts to create approximately 200 tables. I have all of
> these .sql files saved in a folder on a server. Is there a way to execute
or
> run all of the files at once in this folder instead of running them one at
a
> time?
> Thanks|||Sounds good. Are there any examples out there I could take a look at?
"KH" wrote:
> You could write a batch file - use a 'for' loop and call osql passing the
> file path and other appropriate parameters.
>
> "bbasile" wrote:
>|||that's exactly what I was going to suggest.
another way would be to use the OA methods and FileSystemObjects to load
each of the files and then execute them.
> You could write a batch file - use a 'for' loop and call osql passing the
> file path and other appropriate parameters.
>
> "bbasile" wrote:
>
new

Run all .sql files (scripts) in project (SQL Server 2005 SP2)

Hello,

I've run as many keyword combinations searching for this as I can
think of, but cannot find a simple answer.

The situation: I have a database that is mid-conversion/redevelopment.
I frequently rebuild my dev copy from the production version, which
requires a bunch of DDL script, and then adding in all the stored
procedures necessary to the new version only. Each sp may have changed
since the last time it was run, so I can't exactly keep them all
concatenated in a single file or anything like that.

So the only way I can see to execute each and every one of these saved
script files is to open it up in SSMS and hit F5 for execute. This is
a silly waste of time, it seems to me, and hardly foolproof. Is there
not some simple way to run every script in a project? What purpose
does a project serve, otherwise?

(Worth mentioning that the tool I found here
http://www.codeproject.com/useritem...criptRunner.asp does not
seem to work.)

Thanks in advance.downwitch (downwitch@.gmail.com) writes:

Quote:

Originally Posted by

I've run as many keyword combinations searching for this as I can
think of, but cannot find a simple answer.
>
The situation: I have a database that is mid-conversion/redevelopment.
I frequently rebuild my dev copy from the production version, which
requires a bunch of DDL script, and then adding in all the stored
procedures necessary to the new version only. Each sp may have changed
since the last time it was run, so I can't exactly keep them all
concatenated in a single file or anything like that.
>
So the only way I can see to execute each and every one of these saved
script files is to open it up in SSMS and hit F5 for execute. This is
a silly waste of time, it seems to me, and hardly foolproof. Is there
not some simple way to run every script in a project? What purpose
does a project serve, otherwise?

SQL code should be kept under source control, just likely any other code,
and deployment should be done from the version-control system.

Having said that, there are a couple of options you consider. One is
SQL Compare from Red Gate, although they currently have no direct support
for Source Control. The latest version supports comparing a database to
folder structure on disk, though.

A much more simple-minded way is to get a list of all stored procedures
you need to load. Put that in a text file, and then use a text editor
with good find-replace capabilities to transform the list to a BAT
file that runs SQLCMD to load the files. Just make sure that you run
SQLCMD with the -I option, so that you run with QUOTED_IDENTIFIER ON.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Aug 22, 5:52 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

downwitch (downwi...@.gmail.com) writes:

Quote:

Originally Posted by

I've run as many keyword combinations searching for this as I can
think of, but cannot find a simple answer.

Quote:

Originally Posted by

Quote:

Originally Posted by

>
SQLcode should be kept under source control, just likely any other code,
and deployment should be done from the version-control system.
>
Having said that, there are a couple of options you consider. One isSQLComparefrom Red Gate, although they currently have no direct support
for Source Control. The latest version supports comparing a database to
folder structure on disk, though.
>
A much more simple-minded way is to get a list of all stored procedures
you need to load. Put that in a text file, and then use a text editor
with good find-replace capabilities to transform the list to a BAT
file that runs SQLCMD to load the files. Just make sure that you run
SQLCMD with the -I option, so that you run with QUOTED_IDENTIFIER ON.
>
--
Erland Sommarskog,SQLServerMVP, esq...@.sommarskog.se
>
Books Online forSQLServer2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -

downwitch - we have a new product that will be released in the next
few days, xSQL Builder that allows you to create a database deployment
package which between other things allows you to specify a folder
where you can have all the scripts you want to run before the database
synchronization takes place and also you can specify a folder where
all your post synchronization scripts are. The application will run
all those scripts on one big transaction and log everything.

Email us at our support address @. xsqlsoftware.com and we will send
you the pre-release build so you can give it a try.

Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com|||On Aug 22, 11:24 am, downwitch <downwi...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
I've run as many keyword combinations searching for this as I can
think of, but cannot find a simple answer.
>
The situation: I have a database that is mid-conversion/redevelopment.
I frequently rebuild my dev copy from the production version, which
requires a bunch of DDL script, and then adding in all the stored
procedures necessary to the new version only. Each sp may have changed
since the last time it was run, so I can't exactly keep them all
concatenated in a single file or anything like that.
>
So the only way I can see to execute each and every one of these saved
script files is to open it up in SSMS and hit F5 for execute. This is
a silly waste of time, it seems to me, and hardly foolproof. Is there
not some simple way to run every script in a project? What purpose
does a project serve, otherwise?
>
(Worth mentioning that the tool I found herehttp://www.codeproject.com/useritems/SSMSScriptRunner.aspdoes not
seem to work.)
>
Thanks in advance.

Hello downwitch,

xSQL Software has just released a free utility, xSQL Script Executor,
that allows you to run multiple T-SQL scripts at once - you can choose
individual scripts or whole folders and run them all. You can choose
to wrap all scripts in one big transaction or not, you can call it
from the command line etc. Check it out at:
http://www.xsqlsoftware.com/Product...torDetails.aspx
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com

run a sql-sript from interface

Dear All,

I'm looking for a VBA-code to run a sql-sript from the Access.adp-interface.
I want to send scripts so my client can add sql-server-objects from the
interface.
Thanks,
Filip"Filips Benoit" <benoit.filips@.pandora.be> wrote in message news:<eEGsb.20778$7o6.640107@.phobos.telenet-ops.be>...
> Dear All,
> I'm looking for a VBA-code to run a sql-sript from the Access.adp-interface.
> I want to send scripts so my client can add sql-server-objects from the
> interface.
> Thanks,
> Filip

There are several options - SQLDMO, SQL commands embedded in your
code, reading in and then executing an external file etc. I suggest
you post this to an Access or VBA newsgroup, as you'll probably get a
better response on which is the best approach.

Simon

Tuesday, March 20, 2012

Run SQL Script from Web App

Run scripts on multiple servers in SQL Management Studio

Run scripts on multiple servers in SQL Management Studio

Monday, March 12, 2012

Run Multiple Stored Procedures in order with a script?

Run Multiple Stored Procedures in order with a script?

Run Multiple Stored Procedures in order with a script?

Run Multiple Stored Procedures in order with a script?

Wednesday, March 7, 2012

Run all .sql files in a folder

Run all .sql files (scripts) in project (SQL Server 2005 SP2)

run a sql-sript from interface

RSS Text Data Mining

Blog Archive

About Me