访问SQL传递查询（创建SP）错误 [英] access-SQL pass-through query (creating SP) error

问题描述

我想创建使用MS Access 2010中的存储过程使用传递查询在SQL Server 2012。

I am trying to create a stored procedure using pass-through query in SQL Server 2012, using MS Access 2010.

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'SCHtest') 
EXECUTE sp_executesql N'create schema SCHtest'
GO
CREATE PROCEDURE [SCHtest].[SQLLrtest_2]
AS
BEGIN
INSERT INTO [dbo].[UploadTest] (.....)

在访问我得到这个错误：

In Access I get this error:

Run time error '3146':
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax nera 'GO'. (#102)
[Microsoft][ODBC SQL Server Driver][SQL Server]'CREATE/ALTER PROCEDURE' must be 
the first statement in a query batch. (#111)

如果我复制粘贴code到SQL Server - ！一切都工作得很好

And if I copy-paste code to SQL Server - everything works just fine!

因为它是写错误，如果我删除IF语句和GO，工作在Access和SQL。如何使它不删除IF语句工作？

As it is written in the error, if I delete IF statement and GO, works in both Access and SQL. How to make it work without deleting IF statement?

任何帮助是非常AP preciated！

Any help is highly appreciated!

Edgaras

推荐答案

如何建立这样的事情：

IF NOT EXISTS (SELECT *
               FROM   sys.schemas
               WHERE  name = N'testx')
  EXECUTE Sp_executesql
    N'create schema testx'

DECLARE @sql VARCHAR(max)

SELECT @sql = '
CREATE PROCEDURE testx
AS
INSERT INTO [dbo].[table_1] (atext) values (''abc'') '

EXEC (@sql)
EXEC Testx 

参考：<一href="http://ask.sqlservercentral.com/questions/4420/alternative-to-go-for-batching-sql-statements.html" rel="nofollow">http://ask.sqlservercentral.com/questions/4420/alternative-to-go-for-batching-sql-statements.html

测试在MS Access 2010

Tested in MS Access 2010

这篇关于访问SQL传递查询（创建SP）错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！