Simple query failing

Question

Post reply

Simple query failing

ElijahE

Old Hand

Points: 310
More actions
February 19, 2014 at 7:51 am

#285807

I have SQL 2008r2 installed on my laptop and yesterday I installed SQL 2012. Since doing that the following simple query in SQL 2008r2 fails:
IF OBJECT_ID('usp_test','P') IS NULL
EXEC ('CREATE PROCEDURE usp_test AS select 1 as row1')
GO
ALTER PROCEDURE usp_test
AS
select 2 as row2
GO
I've run code like this hundreds of times in the past so I know it works. If you highlight just the first two lines (the IF statement) it runs correctly. If I then highlight the alter procedure statements (lines 4-6) that runs correctly as well. If I include the GO keyword with either statement it tells me I have incorrect syntax and refuses to run.
So this works:
IF OBJECT_ID('usp_test','P') IS NULL
EXEC ('CREATE PROCEDURE usp_test AS select 1 as row1')
And this fails:
IF OBJECT_ID('usp_test','P') IS NULL
EXEC ('CREATE PROCEDURE usp_test AS select 1 as row1')
GO
This only started happening after I installed SQL 2012. Any ideas?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

What is the error message? Why are you using dynamic sql to create stored procs?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

ElijahE (2/19/2014)
I have SQL 2008r2 installed on my laptop and yesterday I installed SQL 2012. Since doing that the following simple query in SQL 2008r2 fails:
IF OBJECT_ID('usp_test','P') IS NULL
EXEC ('CREATE PROCEDURE usp_test AS select 1 as row1')
GO
ALTER PROCEDURE usp_test
AS
select 2 as row2
GO
I've run code like this hundreds of times in the past so I know it works. If you highlight just the first two lines (the IF statement) it runs correctly. If I then highlight the alter procedure statements (lines 4-6) that runs correctly as well. If I include the GO keyword with either statement it tells me I have incorrect syntax and refuses to run.
So this works:
IF OBJECT_ID('usp_test','P') IS NULL
EXEC ('CREATE PROCEDURE usp_test AS select 1 as row1')
And this fails:
IF OBJECT_ID('usp_test','P') IS NULL
EXEC ('CREATE PROCEDURE usp_test AS select 1 as row1')
GO
This only started happening after I installed SQL 2012. Any ideas?

Have you tried a proper simple query to see if it's the batch separator causing the problem?

SELECT 1

GO

SELECT 1

GO

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 3

Strange. I run all of yours t-sql statements on a sql 2012 and sql 2008 r2 instances and they worked all.

I don't think it's the new installed instance of 2012.

You can send the full error message here.

Regards,

Igor

Igor Micev,
My blog: www.igormicev.com

ElijahE Old Hand Points: 310 More actions · Answer 4

Here's the error message:

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'ALTER'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'GO'.

I use this syntax when scripting deployments of objects which may or may not exist in production yet. If the object exists then only the alter is run, if it doesn't yet exist then a placeholder procedure is created and then replaced by the real procedure code.

Luis Cazares SSC Guru Points: 183686 More actions · Answer 5

Has anyone played with your batch separator settings?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ElijahE Old Hand Points: 310 More actions · Answer 6

ElijahE

Old Hand

Points: 310

February 19, 2014 at 8:22 am

#1690317

Yea, I checked that, and it's still set as GO

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

ElijahE (2/19/2014)
Yea, I checked that, and it's still set as GO

ChrisM@Work (2/19/2014)
Have you tried a proper simple query to see if it's the batch separator causing the problem?
SELECT 1
GO
SELECT 1
GO

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ElijahE Old Hand Points: 310 More actions · Answer 8

ElijahE

Old Hand

Points: 310

February 19, 2014 at 8:57 am

#1690338

This code works:

SELECT 1

GO

SELECT 1

GO

Sean Lange SSC Guru Points: 286562 More actions · Answer 9

Maybe you should just create your scripts with a more standard approach?

IF OBJECT_ID('usp_test','P') IS NOT NULL

drop PROCEDURE usp_test

GO

create PROCEDURE usp_test

AS

select 2 as row2

GO

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ElijahE Old Hand Points: 310 More actions · Answer 10

If you drop and recreate then you lose all permissions settings. If you ALTER then the permissions are kept.

Interestingly enough, that query does work though.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 11

What about trying by using a ";" as the statement terminator?

Igor Micev,
My blog: www.igormicev.com

ElijahE Old Hand Points: 310 More actions · Answer 12

When I run the following code (includes statement terminators) I get a different error message:

IF OBJECT_ID('usp_test','P') IS NULL

EXEC ('CREATE PROCEDURE usp_test AS select 1 as row1');

GO

ALTER PROCEDURE usp_test

AS

select 2 as row2;

GO

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'GO'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'GO'.

KS Hall of Fame Points: 3323 More actions · Answer 13

May be there are non-printable characters in the code you might have copied from your script files that are not visible in SSMS. What happens if you open a new window and type the code?

ElijahE Old Hand Points: 310 More actions · Answer 14

That's it. There was an issue with the line feed character which was causing the issue. I typed out the same code in a new window and everything worked correctly.

A little background, I'm writing a stored procedure which will generate other stored procedures. So this code is being generated and I had an issue with the CRLF character. I only included the CHAR(13) instead of both CHAR(13) and CHAR(10). After that was fixed everything works as it should.

Thank you all for looking into this! I really appreciate it.