Access-sql Pass-through Query (creating Sp) Error

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') EXECU

Solution 1:

How about setting up something like this:

IF NOTEXISTS (SELECT*FROM   sys.schemas
               WHERE  name = N'testx')
  EXECUTE Sp_executesql
    N'create schema testx'DECLARE@sqlVARCHAR(max)

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

Reference: http://ask.sqlservercentral.com/questions/4420/alternative-to-go-for-batching-sql-statements.html

Tested in MS Access 2010

Solution 2:

GO is not a TSQL statement it is command of SSMS. As long as you execute your query from MSAccess you cannot use GO.

So, the only option for you is to split your query and execute them separately. EDITED Actualy, not the only option, see the Remou's answer.

Also, I don't think that this is a good idea to create procedures through MSAccess. At least it is not convenient. Maybe, you'd better ask how to solve your original problem which forced you to create procedures through MSAccess?

Hope this helps.