x
login about faq Site discussion (meta-askssc)

passing parameters to a vbs file in command line

Hi,

I have a VbScript file that has oSQLServer.Connect statement using windows authentication. I am trying to pass the servername as parameter from command line.

cscript ScriptJobs.vbs KIRAN\SECONDARY

this value KIRAN\SECONDARY should be passed as the servername. I have the following code

Dim ArgObj
Dim ServerName
Set ArgObj=Wscript.Arguments
Set ServerName=ArgObj.Item(0)

Dim oSQLServer
Dim oStream
Set oSQlServer = CreateObject("Wscript.SQLDMO.SQLServer")
Set oStream = CreateObject("ADODB.Stream")
oSQLServer.LoginSecure=True
oSQLServer.Connect "& ServerName"

Dim idStep
Dim ScriptJob
Dim CountJobs
Dim JobName
Dim ScriptAllJobs

For Each oJob In
oSQLServer.JobServer.Jobs
CountJobs = oSQLServer.JobServer.Jobs.Count
Next

For idStep = 1 To CountJobs

JobName = oSQLServer.JobServer.Jobs.Item(idStep).Name
ScriptJob = oSQLServer.JobServer.Jobs.Item(idStep).Script(4, "C:\" & JobName & ".sql")

ScriptAllJobs = ScriptAllJobs & ScriptJob

Next

oStream.Open oStream.WriteText (ScriptAllJobs) oStream.SaveToFile ("C:\SQLAllScripts.sql"), 2

oStream.Close oSQLServer.DisConnect

Set oStream = Nothing
Set oSQLServer = Nothing
Set ArgObj=Nothing

However, the parameter that i pass in command line is not being taken as input into the ServerName.

Please help me out with this

more ▼

asked Apr 20 '10 at 10:29 PM in Default

Kiran 1 gravatar image

Kiran 1
14 4 4 5

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Safe to say I'm not the greatest vbscript writer but on the line oSQLServer.Connect "& ServerName" should the & be in the quotes? and also what value are you passing to the script? If the server has a named SQL instance then you might need to wrap it in [] so it might need to be like [MyServer\SQLInstanceName].

Can you connect to the SQL Server by other means from the PC where this problem exists? You might try using a UDL file to test it.

more ▼

answered Apr 21 '10 at 04:53 AM

Fatherjack gravatar image

Fatherjack ♦♦
38.8k 55 69 104

Thanks for replying. Found the problem with the code. Have to remove set in SET SERVERNAME= statement. Take care

Apr 21 '10 at 05:36 PM Kiran 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x272
x7

asked: Apr 20 '10 at 10:29 PM

Seen: 1265 times

Last Updated: Apr 21 '10 at 04:35 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.