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

GO is for management studio only, its not a t-sql command (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 stackoverflow.com/questions/55506/… — Alex K.
– Alex K., Commented Aug 30, 2012 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
– mellamokb, Commented Aug 30, 2012 at 13:29

Fionnuala · Accepted Answer · 2012-08-30 14:27:58Z

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

edited Aug 30, 2012 at 14:27

answered Aug 30, 2012 at 14:09

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Igor Borisenko · Accepted Answer · 2012-08-30 23:46:04Z

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.

edited Aug 30, 2012 at 23:46

answered Aug 30, 2012 at 13:31

Igor Borisenko

3,8663 gold badges38 silver badges53 bronze badges

2 Comments

edgarasb Over a year ago

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

Igor Borisenko Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related