access-SQL pass-through query (creating SP) error

Question

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)

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

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?

Any help is highly appreciated!

Edgaras

Answer 1

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.

Answer 2

How about setting up something like this:

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 

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

Tested in MS Access 2010