May 22, 2012 at 10:01 am
Hello,
I'm in the process of planning an upgrade from SQL 2000 and SQL 2005 to SQL 2008 R2. I was wondering if anyone has a good procedure for checking the existing SQL 2000 and SQL 2005 databases for this deprecated feature warning: Not ending Transact-SQL statements with a semicolon. We have tons of SQL code; does this mean we have to find every single statement in those tons of code and add a semicolon after each statement in it? Does that include SET and GO statements as well, in other words do they have to be modified to, say, SET ANSI_NULLS ON; and GO; ? If so, does anyone have any scripts or recommended tools to make this easier?
Thanks for any help!
-webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 22, 2012 at 10:14 am
As for the GO, no, do not put a ; after it as it will error.
May 22, 2012 at 10:14 am
Hmm... Why do you think that every statement needs to end with a semi-colon in 2008R2? Also, GO is a batch terminator and will never require a semi-colon.
Jared
CE - Microsoft
May 22, 2012 at 10:28 am
At some point in time, yes you may need to end every statement with a ;. Until then, I believe the only statements that require that the previous statement be terminated is the WITH (CTE) and MERGE statements. You should look at the new features added in SQL Server 2005, SQL Server 2008 to determine if any others may require this. If not, don't worry about. In new development, get the developers used to terminating the statements with a ;, it will make things easier going forward.
May 22, 2012 at 11:21 am
You don't currently have to terminate statements with a semicolon. However, you will have to at an as-yet-undetermined future date.
I don't know a tool that will automate adding them for you. Probably, once it's needed, someone (maybe RedGate) will create such a tool, but there's no market for it yet.
As already mentioned, GO won't need one. It's not an SQL statement, it's an inline command for Management Studio (and for old Query Analyzer). You can actually put a number after GO to get it to run the preceeding batch X number of times. Like "GO 10" will run the batch 10 times. Not many seem to know that. Putting a semicolon after GO makes the compiler think you are trying to do an SQL command, and will throw an error.
SET will need one, at whatever future date they become mandatory. So will BEGIN and END. (END makes sense to have one, but BEGIN doesn't. Not to me anyway.) There are some others that won't as well.
Example:
CREATE PROCEDURE dbo.MyProc
AS
SET NOCOUNT ON ;
SELECT *
FROM dbo.MyTable ;
Note that the CREATE PROC <name> AS statement doesn't have a semicolon after it, but SET NOCOUNT ON and the SELECT statement do.
None of that is needed, yet. But it will be soon, so it's a good idea to get in the habit now.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 22, 2012 at 11:25 am
Thanks, everyone, for your replies. Especially for the information about GO, which I didn't know.
I guess for now, I will make this a longer-term task with a checklist for modifying the code one database at a time. It would be nice to have this done before migrating but completing all of it may not be possible given my schedule.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 22, 2012 at 11:46 am
Just an FYI, I tend to use semi-colons separately. Just for ease of editing and working.
alter database MyDb set read_only
;
go
select name
from sys.databases
where name not in ('master', 'msdb')
;
That way if I need to edit things, it's easy with the semi-colon on the next line.
May 22, 2012 at 12:02 pm
Hi Steve,
Thanks for the tip! That's a great idea.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 22, 2012 at 12:07 pm
I've done that on many occasions, especially while writing a query. Makes sure that I have the final ;.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply