3

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

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
edgarasb
  • 45
  • 6
  • 3
    `GO` is for management studio only, its not a t-sql command (http://stackoverflow.com/questions/2668529/t-sql-go-statement) so remove it, then you will get another error when you try to create the sp, see here http://stackoverflow.com/questions/55506/how-do-i-conditionally-create-a-stored-procedure-in-sql-server – Alex K. Aug 30 '12 at 13:28
  • @AlexK.: That should be the answer :) As a side note, your profile website (endjinn.com) gives a Virtual Directory listing denied as the result. – mellamokb Aug 30 '12 at 13:29

2 Answers2

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

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
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.

Igor Borisenko
  • 3,806
  • 3
  • 34
  • 49
  • Ok, so I have a task to make some calculations in SQL server and to also make an interface, where a person can enter some formulas by which these calculations will be made (earlier, everything was in Access). Therefore, as SQL does not have any forms (right?), I am linking Access with SQL with a form in which I send a pass-through query to SQL in order to create a stored procedure for every formula (which also should be in several groups - this is why I need this schema). Thank you so much!!! – edgarasb Aug 30 '12 at 18:30
  • @edgarasb Your approach can be used though I don' see how you will store procedure names for reuse and why you could not store queries with this formulas. Though I haven' worked with access for two years. Glad we could help you. – Igor Borisenko Aug 31 '12 at 00:11