passing parameters from vbscript to sql server osql

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

passing parameters from vbscript to sql server osql

Logged in as: Guest

arrSession:exec spGetSession 2,2,24429

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> passing parameters from vbscript to sql server osql
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

passing parameters from vbscript to sql server osql - 8/4/2005 4:09:55 AM

sambaram

Posts: 3
Score: 0
Joined: 8/2/2005
Status: offline

Is there anyway to pass parameters from vbscript to SQL server osql utility.
eg. i have a vbscript which has an inputbox to allow the username to enter the name of the database to restore. The reason we are developing an application so require customised SQL and oracle backup restore tools.

I am using osql for that in my vbscript.

Now the issue is I want osql to execute the following statement in vbscript.

select name from sysdatabases where name = 'MYDB' (this has to be a variable)
if @@ROWCOUNT = 0
    print 'DB DOES NOT EXISTENT'
ELSE
    print 'DB EXISTS'

Secondly, can i pass statements to sql files...i.e. if i save the above to a .sql file then can i pass the variable MYDB from osql to that .sql file?

eg.   osql -E -i query.sql ..........     how can i pass MYDB variable to it??

Post #: 1

RE: passing parameters from vbscript to sql server osql - 8/4/2005 5:18:32 AM

ehvbs

Posts: 2078
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

I'm not sure, whether I understand you correctly, but ...

To pass parameters to another process like osql.exe you may use
WshShell.Run
or
WshShell.Exec
specifying a suitable command string. Putting content of variables into
the command string would lok like:

sCmd = "osql -E -Q -q ""select name from sysdatabases where name = '" + sUsrInput + " ' """

Obviously, getting the quotes right will be a problem (my sample isn't tested!).

I *think* your statement

select name from sysdatabases where name = 'MYDB'
if @@ROWCOUNT = 0
print 'DB DOES NOT EXISTENT'
ELSE
print 'DB EXISTS'

will work if saved to xxx.sql and send to osql.exe with "osql -E -i xxx.sql". To create
such .sql files from VBScript 'on the fly' you may use code like the sample proposed by
Zifter for batch files
http://www.visualbasicscript.com/tm.aspx?m=24422

The next problem is to get output/results from osql.exe. Using WshShell.Exec you can
access the StdOut and Status property of the WshScriptExec object. For WshShell.Run
you have to use the -o option of osql.exe. Either way - it will be a lot of complicated
work.

Do you really have so many / changing databases that you can't write some 5 or six
scripts with hardcoded database names?

If you need the flexibility / a lot of sql statements against different databases you
should consider using ADO to access your server.

(in reply to sambaram)

Post #: 2

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> passing parameters from vbscript to sql server osql

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts