We are using the bulk set of sql script in a sql file and load that file during runtime to text variable in C#. Hence I execute the following query through Oledb Connection and Command type as "Text". Unfortunatly It was given the following error.
我们正在sql文件中使用大量的sql脚本,并在运行时将该文件加载到c#中的文本变量中。因此,我通过Oledb连接和命令类型作为“文本”执行以下查询。不幸的是,它犯了以下错误。
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near 'GO'.
SQL Query Syntax
SQL查询语法
IF exists (
SELECT '1'
FROM sysobjects
WHERE id = object_id(N'SP_Name')
and objectproperty(id, N'isprocedure') = 1
)
BEGIN
DROP PROC SP_Name
END
GO
Create PROCEDURE SP_Name
(
@var_id NUMERIC(10),
@var_id1 NVARCHAR(100),
@var_id_Out1 numeric(10) output,
@var_id_Out2 integer output,
@var_id_Out3 nvarchar(2000) output
)
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
select * from demotable
SET NOCOUNT OFF
END
GO
GRANT EXEC ON SP_Name TO PUBLIC
GO
C# Code snippet
c#代码片段
OleDbCommand cmd;
OleDbConnection co = new OleDbConnection("Provider=SQLOLEDB;Data Source=servername;Database=DB_Name;User Id=sa;Password=mypass;");
co.Open();
String[] strFileList = Directory.GetFiles(@"D:\Procedure");
String conntent = null;
foreach (string commandSP in strFileList)
{
conntent = File.ReadAllText(commandSP);
cmd = co.CreateCommand();
cmd.CommandText = conntent;
cmd.ExecuteNonQuery();
}
co.Close();
2 个解决方案
#1
2
the GO command is T-SQL syntax ... that is interpreted by the frontend to separate batches ... neither OLEDB nor the server will understand that statement ...
GO命令是T-SQL语法…这是由前端分离批次来解释的。OLEDB和服务器都不理解这个语句…
for getting this to work with OLEDB you can split the batches yourself and run each batch individualy
要让它与OLEDB一起工作,你可以自己分割这些批次,每个批次单独运行
#2
2
GO isn't a T-SQL keyword i.e. it's not part of the language, it's simply a token used by some clients (e.g. Management Studio) to separate batches
GO不是T-SQL关键字,也就是说,它不是语言的一部分,它只是一些客户端(例如Management Studio)用于分离批的标记
#1
2
the GO command is T-SQL syntax ... that is interpreted by the frontend to separate batches ... neither OLEDB nor the server will understand that statement ...
GO命令是T-SQL语法…这是由前端分离批次来解释的。OLEDB和服务器都不理解这个语句…
for getting this to work with OLEDB you can split the batches yourself and run each batch individualy
要让它与OLEDB一起工作,你可以自己分割这些批次,每个批次单独运行
#2
2
GO isn't a T-SQL keyword i.e. it's not part of the language, it's simply a token used by some clients (e.g. Management Studio) to separate batches
GO不是T-SQL关键字,也就是说,它不是语言的一部分,它只是一些客户端(例如Management Studio)用于分离批的标记