SQL Query Files with VBS variables

Question

I am converting a lot of batch scripts into VBS. In my VBS script I have the following code:

 cmdline1="sqlcmd -I -E -S " & CATALOG &" -i " & chr(34) & strPath & chr(34) & "\DatabaseProvision.sql -o Log_0_Catalog_" &  ymd & "_" & hms & ".txt"

Set objExecObject = objShell.Exec("%comspec% /k" & cmdline1 & " > C:\Database\Cmdline1.txt 2>&1")

This code works. The DatabaseProvision.sql file that is called in Cmdline1 is running the following code:

--Creating Tables
--Executed EACatalogTables
:r  $(currentdirectory)$(tablesdirectory)Tables.sql
GO

The issue is that the SQLCMD's output has an error stating that "'currentdirectory' scripting variable not defined."

In my VBS file I have a variable defined as currentdirectory.  I think the issue is, how can I have the variable defined in my VBS file work when calling the SQL script when using ":r  $(currentdirectory)$(tablesdirectory)Tables.sql" ??

Answer 1

Why? When you're already making the effort, why not converting it to PowerShell scripts?

But your error should be clear: The SQLCMD script contains two variables $(CURRENTDIRECTOR) and $(TABLESDIRECTORY). You need to specify both in your command line string. It's the -v option of SQLCMD (-v CURRENTDIRECTORY=".." -v TABLESDIRECTORY="..").

Answer 2

Why would VBS need it, but not batch?

I would convert it to powershell if I knew how:)

Answer 3

Variables can be passed into SQLCMD Scripts through

1. Environemnt Variables (System/User) or they might have used

2. A :setvar command to set the value of the variables prior to calling the sql file.

3. Simply using a SET X = Y command before calling the batch.

4. Using the -v command that Stefan suggested

Option 1-3 could have been used in the batch files you are converting and that's why you never needed a explicit passing of variables. Check this link for more information on how to pass in variables.

Answer 4

The parameters? Well, they are needed in the batch also.. Take a look at them again, there must be somewhere the -v option be lurking around :)

I would start reading here:

• Common PowerShell:  MSDN and TechNet
• SQL Server and PowerShell: MSDN and TechNet

Answer 5

Why should I convert to Powershell? What benefits does Powershell have over VBS?