sql - VBS脚本执行SQL语句？

Question

要打开，我从未写过vbs脚本。我写了许多SQL脚本， View ，开发的数据库。我已经在Access应用程序中编写了大量VBA。

为此，我只是尝试将SQL脚本设置为VBS脚本，因此用户不必进入SSMS即可运行它。他们只需双击VBS脚本，在出现提示时指定服务器和数据库，即可为其运行快速脚本。

到目前为止，这就是我所得到的，但是我一直在收到Microsoft VBScript编译错误。最新的是第3行char 17，它在Dim语句上。只是想看看是否有人可以告诉我是否缺少此脚本的基本内容，从而阻止了该脚本的正确编译或处理。

这是非常简短的脚本:

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!"

如果有人可以提供帮助，我将不胜感激。

谢谢

Answer 1

没关系。我一直进行故障排除，最后使其正常工作。对于那些可能会有所帮助的人，与VBA不同，不将变量声明为类型会更容易。只是将它们调暗并继续前进。见下文:

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!"

记住，对于那些希望以此为脚本基础的人来说，我不会做任何检查，因此，如果您不知道自己的数据，这是很危险的事情。

了解您的数据，备份数据，如果可以，请添加一些检查，以确保在运行之前检查并重新检查不是select语句的所有内容。