December 22, 2008 at 10:22 pm
I have restore database of sql server 2000 to 2005, and set the compatibility to 80 for backward compatibility.
Noe i want to set database compatibility to 90.
So can anybody tell me , which change i have to do in stored procedure or any.
I want general problem which we can face during upgradation and how we can solve them.
December 23, 2008 at 8:04 am
You can find a complete list in the Books Online:
http://msdn.microsoft.com/en-us/library/ms178653(SQL.90).aspx
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
December 24, 2008 at 2:52 am
I did the same recently. The biggest issues for us was the *= joins.
I used this script to look for any *= in storedproc. All our sp are prefixed with 'up_' so this scripts limits it to this.
Change the sp.name LIKE N'up%' to adapt it to your needs.
--Look for *= in SP with name like up%
SELECT 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + name+ ']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1) drop procedure [dbo].[' + name+ ']'
,lower(name),ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE (sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC') and(sp.name LIKE N'up%' and SCHEMA_NAME(sp.schema_id)=N'dbo')
AND
( (smsp.definition IS NOT NULL AND smsp.definition LIKE N'%*=%')
OR
(sSmsp.definition IS NOT NULL AND sSmsp.definition LIKE N'%*=%')
)ORDER BY 1
And don't forget to rerun this for '=*'
JM.
December 24, 2008 at 8:29 am
JM,
Thanks for the script.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
December 24, 2008 at 10:12 pm
Can i run Upgrade Adviser on database which have in SQL server 2005 (which was restored from sql server 2000 ). but have compatibility is 80.
January 1, 2009 at 4:34 am
HI,
paresh i got that problem solution we can get procedure name through error_procedure()
now i want to make database trigger if you have any idea than please pm me or reply me
actually what i do is that make one procedure that will take error and insert into one table
Raj Acharya
January 1, 2009 at 4:46 am
you have to run upgrade advisor of sqlserver 2005 on sql server 2000
then you will find all the upgrade advice need to upgrade that and also solution for that
Raj Acharya
January 1, 2009 at 5:04 am
raj acharya (1/1/2009)
HI,paresh i got that problem solution we can get procedure name through error_procedure()
now i want to make database trigger if you have any idea than please pm me or reply me
actually what i do is that make one procedure that will take error and insert into one table
--------------------------
Code to handle Errorlog in table
---------------------------
BEGIN CATCH
ROLLBACK TRANSACTION
insert into Logtable
SELECT CONVERT(VARCHAR,GETDATE(),121) as LogDate ,error_procedure() as SPName, ERROR_MESSAGE() as ErrorMessage,
exec(@CEXPORTLOG)
END CATCH
-------------------------------------------------------
------------------------------------------------------
----------------------
To track object change using DDL Trigger
---------------------
CREATE TABLE [dbo].[AdministratorLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()),
[LoginName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_tbl_ChangeLog] PRIMARY KEY CLUSTERED
(
[LogId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TRIGGER [Admin_BackupObjects]
ON DATABASE
FOR
DDL_DATABASE_LEVEL_EVENTS
--create_trigger,alter_trigger,drop_trigger,
--create_procedure, alter_procedure, drop_procedure,
--create_table, alter_table, drop_table,
--create_function, alter_function, drop_function,
--create_view,alter_view,drop_view
AS
BEGIN
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO AdministratorLog(DatabaseName,EventType,ObjectName,ObjectType,SqlCommand,LoginName)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), -- value is case-sensitive
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_PADDING ON
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply