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
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