VBS Script to Execute SQL statement?

Question

To open, I have never written a vbs script. I have written many SQL scripts, views, developed databases. I have written plenty of VBA in Access applications.

For this, I am just trying to set up a SQL script as a VBS script, so the users don't have to go into SSMS to run it. They can just double-click the VBS script, specify the server and database when prompted, and the quick script will run for them.

This is what I have gotten so far, but I keep getting Microsoft VBScript compilation errors. The latest one is line 3 char 17, which is on a Dim statement. Just wanted to see if anyone can tell if I am missing something fundamental to this script, that is preventing it from compiling or processing correctly.

This is the very short script:

Dim conn 
Set conn = createobject("Adodb.Connection")
Dim sConnString As String
Dim SqlStatement As String

sSourceServer = InputBox ("Enter the name of the SQL Server","Enter SQL Server Name","")
    If Len(sSourceServer) = 0 Then
        MsgBox "No SQL Server was specified.", , "Unable to Continue"
        Exit Sub
    End if

sSourceDB = InputBox ("Enter the name of the Law SQL Database","Enter Law SQL DB Name","")
    If Len(sSourceDB) = 0 Then
        MsgBox "No SQL DB was specified.", , "Unable to Continue"
        Exit Sub
    End if

' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=" & sSourceServer & "; Initial Catalog=" & sSourceDB & "; Integrated Security=SSPI;"

MsgBox sConnString

' Open the connection and execute.
conn.Open sConnString
conn.CommandTimeout = 900

SqlStatement = "UPDATE [tablename] " & _
                                "SET UUID = CASE WHEN CHARINDEX('.',[Filename]) > 1 THEN LEFT(CAST([Filename] AS VARCHAR),CHARINDEX('.',[Filename])-1) ELSE [Filename] END " & _
                                "WHERE [Filename] IS NOT NULL"

conn.Execute(SqlStatement)

conn.Close
Set rs = Nothing
SqlStatement = vbNullString

MsgBox "All Done! Go Check your results!"

If anyone can help, I'd greatly appreciate it.

Thank you

Answer 1

nevermind. I kept troubleshooting and finally got it to work. For those that this might help, unlike VBA, it's easier not to declare variables as a type. just Dim them and move on. see below:

Dim conn 
Set conn = createobject("Adodb.Connection")
Dim sConnString
Dim SqlStatement

StartScript

Sub StartScript()

sSourceServer = InputBox ("Enter the name of the SQL Server","Enter SQL Server Name","")
    If Len(sSourceServer) = 0 Then
        MsgBox "No SQL Server was specified.", , "Unable to Continue"
        Exit Sub
    End if

sSourceDB = InputBox ("Enter the name of the Law SQL Database","Enter Law SQL DB Name","")
    If Len(sSourceDB) = 0 Then
        MsgBox "No SQL DB was specified.", , "Unable to Continue"
        Exit Sub
    End if

' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=" & sSourceServer & "; Initial Catalog=" & sSourceDB & "; Integrated Security=SSPI;"

' Open the connection and execute.
conn.Open sConnString
conn.CommandTimeout = 900

SqlStatement = "UPDATE [tablename] " & _
                                "SET UUID = CASE WHEN CHARINDEX('.',[Filename]) > 1 THEN LEFT(CAST([Filename] AS VARCHAR),CHARINDEX('.',[Filename])-1) ELSE [Filename] END " & _
                                "WHERE [Filename] IS NOT NULL"

conn.Execute(SqlStatement)

conn.Close
Set rs = Nothing
SqlStatement = vbNullString

End Sub

MsgBox "All Done! Go Check your results!"

Remember, for those looking to use this as a basis for a script - I'm not doing any checks, so if you don't know your data, this is a dangerous thing to run.

Know your data, backup your data, and if you can, add in some checks, to make sure anything that isn't a select statement, is checked and re-checked before it is run.