April 22, 2010 at 9:30 am
Any tips would be appreciated. Thank you in advance!
Currently, or databases are set to a compatibility level of 80, which is keeping me from taking advantage of some of the great new options in SQL Server 2005 or even SQL Server 2008. I changed one database to level 90 (sql server 2005) to see what the implications would be and one of the errors that were raised was incompatible code in a index. I see that the issue is with the old syntax on the joins where they are using *=, =*, etc...
Rather than attempting to go through every single Proc, View, etc... to find these syntax incompatibility issues, I decided to change it back to 80. I would really like to get all databases up to 100 (sql server 2008). Is there something I can run to update all the sql code to be compatible with SQL 2008 or SQL 2005? Or is there something I can run to identify all the objects that have this type syntax so I don't have to look at every single object?
Thanks,
Mark
April 22, 2010 at 2:50 pm
You can download the Microsoft SQL Server Upgrade Adviser. It will identify all of the objects, but you have to change them yourself.
April 22, 2010 at 2:56 pm
Thanks Toby, I'll check it out. I shouldn't have a problem converting them, but didn't want to have to find a needle in a haystack.
Thanks,
Mark
April 22, 2010 at 3:20 pm
No problem. My experience using the tool has been positive. It identifies the objects and the problem in the specific object. However there is one caveate, the database must be in an earlier instance for the tool to work with it. In other words if the database in compatibility mode 8 on a 2008 server the tool won't work on it. At least this is how it was the last time I used it. I had to use a custom script that rebuilt every object on a dumby database and stored the error messages.
April 23, 2010 at 1:32 am
This was removed by the editor as SPAM
April 23, 2010 at 8:57 am
Thank you all for your help and tips.
Check this out my DBA-Friends. I wrote this today and it returns the objects that are creating problems for me. Let me know what you think.
--Created by Mark Cusano on 4/23/2010.
--The following can be used to identify objects containing specific text.
--Two tables are created:
--One to analyze the script.
--And one to store the object names that contain the text you are looking for.
--Then a cursor is populated with the names of the objects and looped through to find the ones that meet the constraint we are looking for.
--Create a table to dump the script into.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[xScriptAnalysis](
[ScriptText] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--Create a table to keep track of the names of the scripts that will need updated.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[xScriptNeedsUpdated](
[ScriptNeedsUpdated] [varchar](60) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
DECLARE @ScriptName varchar(60) --Variable used to store the name of the script
DECLARE MyCursor CURSOR FOR --Cursor used to populate all the names of the Stored Procs and Views.
SELECT [name] FROM sys.objects WHERE [TYPE] IN ('P','V') --Looking at Stored Procs and Views only here.
OPEN MyCursor
FETCH NEXT FROM MyCursor
INTO @ScriptName
WHILE @@FETCH_STATUS = 0
BEGIN
--Clear out the table for every script that is being analyzed.
IF EXISTS (SELECT * FROM xScriptAnalysis)
BEGIN
DELETE FROM xScriptAnalysis
END
--Dump the script in the table so we can search for the text we are looking for.
ELSE
INSERT INTO xScriptAnalysis
(scripttext)
exec sp_helptext @ScriptName
--Check the script to see if it contains the text we are looking for.
IF EXISTS (SELECT * FROM xScriptAnalysis WHERE ScriptText LIKE '%*=%' OR ScriptText LIKE '%=*%')
BEGIN
--Populate the table with the name of the script to be updated, only if it is not already in the table.
IF NOT EXISTS (SELECT ScriptNeedsUpdated FROM xScriptNeedsUpdated WHERE ScriptNeedsUpdated = @ScriptName)
BEGIN
--Populate the table with the name of the object that will need updated.
INSERT INTO xScriptNeedsUpdated
(ScriptNeedsUpdated)
VALUES (@ScriptName)
END
END
FETCH NEXT FROM MyCursor
INTO @ScriptName
END
CLOSE MyCursor
DEALLOCATE MyCursor
--Check the table to see if any objects need updated.
SELECT * FROM xScriptNeedsUpdated
April 23, 2010 at 9:02 am
This was removed by the editor as SPAM
April 23, 2010 at 9:16 am
Wow! Yours is much shorter and much more efficiant. Thanks for the lesson. 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply