[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to launch files from wsh / vb script

Posted on 2007-10-03
10
Medium Priority
?
11,126 Views
Last Modified: 2008-01-09
Hi

I have created a bat file that I use to start several scripts and sqlcmd commands, something like this:

CScript C:\MyTests\FS\Scripts\script01.vbs

sqlcmd -S SERV2K3STD\SQLEXPRESS -i C:\MyTests\FS\Scripts\sql_statement_01.sql

bcp [Mondial_LAM].[dbo].[FileTest] in C:\MyTests\FS\Scripts\some_file.txt -S SERV2K3STD\SQLEXPRESS -c -T
sqlcmd -S SERV2K3STD\SQLEXPRESS -i C:\MyTests\FS\Scripts\3sql_statement_02.sql


My question is how to create a WSH / vb script instead of bat file that would launch those files as a scheduled task. I mean, how do I create such vbs file to be able then launch it through Scheduled tasks?

Thanks!
0
Comment
Question by:Zaurb
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20005021
Do you want to integrate that into VBScript so that you just have one nice script that you can run?

I would actually suggest converting the script01.vbs to a batch script if possible.
0
 
LVL 7

Expert Comment

by:badbearontour
ID: 20005025
I was under the impression that the WHost ability to launch External programs was removed a long time ago, because of obvious reasons with vbs attachments...

that is my understanding anyway!!

BB
0
 
LVL 1

Author Comment

by:Zaurb
ID: 20005037
actually what I need is to schedule this task so that it won't open a command prompt window when run. I thought that creating and scheduling a vbs script will do.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 30

Accepted Solution

by:
nmcdermaid earned 400 total points
ID: 20005079
Here a sample of some VBScript that can do your SQLCMD operations and your BCP operations. You just need to enhance it to load sql_statement_01.sql into a string and run that instead of the hard coded statement shown below.


Dim oConn
Dim oCmd

Set oConn = Server.CreateObject("ADODB.Connection");

'Set the connection string
oConn.ConnectionString = "Provider=SQLOLEDB; Server=SERV2K3STD\SQLEXPRESS;Database=Mondial_LAM;Integrated Security=SSPI;"

' Connect to the db
oConn.Open

' Run some code
oConn.Execute "Your SQL Script Goes Here"

' Disconnect
oConn.Close

Dim WshShell, oExec
Set WshShell = CreateObject("WScript.Shell")

Set oExec = WshShell.Exec("bcp [Mondial_LAM].[dbo].[FileTest] in C:\MyTests\FS\Scripts\some_file.txt -S SERV2K3STD\SQLEXPRESS -c -T
")



But again I would advise converting your script01.vbs to a batch file, or leaving it the way it is. It doesn't hurt to have different scripts in different files.

As you can see, by converting it to VBScript, you have just gone from three lines of code to twenty.... usually thats not a good thing.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20005089
>> actually what I need is to schedule this task so that it won't open a command prompt window when run.

Why?

>> I thought that creating and scheduling a vbs script will do

Yep that will indeed stop a command prompt coming up.
0
 
LVL 1

Author Comment

by:Zaurb
ID: 20005366
Well, the reason is that we already have some scripts running on this machine, plus this particular script will be set to run every several minutes. Most of the work could have be done in SQL Server Agent on standard or enterpise versions of SQL Server, but the one we use right now is Express Edition. For that reason I have to automate many tasks using script files. By the way, the script execution time is about  seconds right now and I expect it will take more in the future as my database grows.

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20005398
Actually, looking at it, I suggest you move your entire script into a stored procedure, then use Windows Scheduler/VBScript to just run your stored procedure. I'm biased though because I know my way around stored procedures.

To put it another way it should be done in the language you are most comfortabe with -  VBScript or T-SQL.
0
 
LVL 85

Assisted Solution

by:oBdA
oBdA earned 300 total points
ID: 20005403
Create a dedicated account "SQLTasks" or whatever with the necessary permissions, and schedule the task to run under this account.
You'll only see the window of a scheduled task if you're logged on with the same account that is used to run the task.
This has the additional advantage that you can change your password without having to edit the task as well.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 20010167
One of the easiest ways to implement a VBS solution would be to create another VBS file, as well as this batch file, that simply calls the batch file in a hidden DOS window.

Create a RunBatch.vbs file, and paste this code:
'================
Set objShell = CreateObject("WScript.Shell")
strBatch = "C:\RunSQLScript.bat"
objShell.Run strBatch, 0, True
Set objShell = Nothing
'================

It is the zero in the objShell.Run command that runs the hidden window.

Then, when creating your sheduled task, make sure the command line it runs is
wscript C:\RunBatch.vbs

Regards,

Rob.
0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 300 total points
ID: 20010206
Or, seeing as you're already using a VBS (I missed that), you can just append the last three commands as objShell.Run commands to that Script01.vbs:

'===============
Set objShell = CreateObject("WScript.Shell")
strCommand = "sqlcmd -S SERV2K3STD\SQLEXPRESS -i C:\MyTests\FS\Scripts\sql_statement_01.sql"
objShell.Run strCommand, 0, True
strCommand = "bcp [Mondial_LAM].[dbo].[FileTest] in C:\MyTests\FS\Scripts\some_file.txt -S SERV2K3STD\SQLEXPRESS -c -T"
objShell.Run strCommand, 0, True
strCommand = "sqlcmd -S SERV2K3STD\SQLEXPRESS -i C:\MyTests\FS\Scripts\3sql_statement_02.sql"
objShell.Run strCommand, 0, True
Set objShell = Nothing
'===============

Then again, in a scheduled task, run
wscript c:\script01.vbs

Regards,

Rob.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question