正确使用MSSQL“去”在VBA [英] The proper use of MSSQL "go" in VBA

问题描述

我将要运行一个使用MSSQL编程的批处理，如下所示：

I am about to run a batch what I programmed using MSSQL, like this:

create function xy ( 
.....
end
go

create function2 xy (
...
end
go

然后，我将它保存在一个文件中，我的宏读取到一个字符串，ADO试图运行，不幸的是，所有的时间我收到以下错误消息：在'go'附近的语法不正确我一直在阅读它，只找到分解批处理的解决方案，但我不是真的想要，因为我有一个字符串变量。 vba中的连接字符串如下（首先是公开声明的变量）：

Then, I saved it in a file, what my macro reads it into a string and with ADO trying to run. Unfortunately, all the time I get the following error message: Incorrect syntax near 'go'. I have been reading about it, and only found the solution to split the batch, but I don't really want since I have it in one string variable. My connection string in vba is the following (first the public declared variables):

Public conn        As ADODB.Connection
Public rs          As ADODB.Recordset
Public cmd         As ADODB.Command
Public sConnString As String

On Local Error GoTo err
    sConnString = "Provider=SQLOLEDB.1;Data Source=localhost;" & _
                  "Initial Catalog=" & database & ";" & _
                  "Integrated Security=SSPI;"
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command
    conn.Open sConnString
    conn.CursorLocation = adUseClient
    cmd.ActiveConnection = conn

set rs = conn.Execute(mysqlstring)

我还使用vba检查了我读入的文件，没有问题，如果我将它复制然后粘贴到microsoft sql，它的工作原理。没有，如果我使用ADO。

I have also checked the file what I read in with vba and no problem with it, if I copy it then paste to microsoft sql, it works. It does not, if I use ADO.

每个帮助都非常感激！

Marta

推荐答案

GO 是默认的批处理终止符，它是不正确的TSQL。

GO is default batch terminator, it is not correct TSQL.

将一批Transact-SQL语句的结尾发送到SQL Server实用程序。

"Signals the end of a batch of Transact-SQL statements to the SQL Server utilities."

GO不是Transact-SQL语句;它是由sqlcmd和osql实用程序和SQL Server Management Studio代码编辑器识别的命令。

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

对于您的示例，使2个数据库调用或通过SSMS创建这些功能。

For your example make 2 calls to database or create these functions via SSMS.

这篇关于正确使用MSSQL“去”在VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！