locked
SQL Query Files with VBS variables RRS feed

  • 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" ??

    Friday, January 4, 2013 3:52 PM

Answers

  • 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.

    • Proposed as answer by Naomi N Friday, January 4, 2013 6:06 PM
    • Marked as answer by Kalman Toth Wednesday, January 9, 2013 6:18 PM
    Friday, January 4, 2013 6:05 PM
  • 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:

    • Marked as answer by Kalman Toth Wednesday, January 9, 2013 6:18 PM
    Friday, January 4, 2013 6:08 PM

All replies

  • 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="..").

    • Proposed as answer by Naomi N Friday, January 4, 2013 5:27 PM
    Friday, January 4, 2013 5:08 PM
  • Why would VBS need it, but not batch?

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

    Friday, January 4, 2013 5:12 PM
  • 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.

    • Proposed as answer by Naomi N Friday, January 4, 2013 6:06 PM
    • Marked as answer by Kalman Toth Wednesday, January 9, 2013 6:18 PM
    Friday, January 4, 2013 6:05 PM
  • 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:

    • Marked as answer by Kalman Toth Wednesday, January 9, 2013 6:18 PM
    Friday, January 4, 2013 6:08 PM
  • Why should I convert to Powershell? What benefits does Powershell have over VBS?
    Wednesday, January 9, 2013 7:10 PM