Please visit my New Blog http://www.sqlserverdba.co.cc/

The following are the steps to perform Side by Side upgrade from SQL Server 7 or SQL Server 2000 to SQL Server 2005.

1) Take full backup of all the databases in SQL 7 or SQL 2000 for precautionary purpose
2) Script all the logins using http://support.microsoft.com/kb/246133 in SQL 7 or SQL 2000
3) Script all the jobs in SQL 7 or SQL 2000 using the below link http://msdn2.microsoft.com/en-us/library/aa177024(SQL.80).aspx
4) Install SQL Server 2005
5) Detach all the user databases in SQL 7 or SQL 2000 and copy the files
6) Attach all the user databases in SQL 2005
7) Run the script taken in Step 2 to recreate the logins
8) Run the script taken in Step 3 to recreate the jobs
9) Inorder to migrate DTS packages to SSIS you can refer this link, http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration
10) Once everything is completed login and check the application functionality
The below link is helpful for getting started with upgrading to SQL Server 2005, http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en

Read Complete Article @ here….

Steve Jones is Maintaining the build list of SQL 2005. You can verify and validate your version by running @@Version Global variable and compare it with sqlservercentral.com’s build list @ http://www.sqlservercentral.com/articles/Administration/2960/

SQL Server 2005 offers an undocumented system stored procedure sp_readerrorlog which calls the extended stored procedure xp_readerrorlog.
This procedure takes four parameters:

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results

If you do not pass any parameters this will return the contents of the current error log.

Read more @ http://www.mssqltips.com/tip.asp?tip=1476

You can execute the SP by executing its name like

EXEC DBO.SPGetForeignKeyInfo
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N’[dbo].[SPGetForeignKeyInfo]‘)
AND OBJECTPROPERTY(id, N’IsProcedure’) = 1)
DROP PROCEDURE dbo.SPGetForeignKeyInfo
GO
CREATE PROCEDURE DBO.SPGetForeignKeyInfo
AS
/*
Author : Seenivasan
This procedure is used for Generating Foreign Key script.
*/
SET NOCOUNT ON
DECLARE @FKName NVARCHAR(128)
DECLARE @FKColumnName NVARCHAR(128)
DECLARE @PKColumnName NVARCHAR(128)
DECLARE @fTableName NVARCHAR(128)
DECLARE @fUpdateRule INT
DECLARE @fDeleteRule INT
DECLARE @FieldNames NVARCHAR(500)
CREATE TABLE #Temp(
PKTABLE_QUALIFIER NVARCHAR(128),
PKTABLE_OWNER NVARCHAR(128),
PKTABLE_NAME NVARCHAR(128),
PKCOLUMN_NAME NVARCHAR(128),
FKTABLE_QUALIFIER NVARCHAR(128),
FKTABLE_OWNER NVARCHAR(128),
FKTABLE_NAME NVARCHAR(128),
FKCOLUMN_NAME NVARCHAR(128),
KEY_SEQ INT,
UPDATE_RULE INT,
DELETE_RULE INT,
FK_NAME NVARCHAR(128),
PK_NAME NVARCHAR(128),
DEFERRABILITY INT)
DECLARE TTableNames CURSOR FOR
SELECT name
FROM sysobjects
WHERE xtype = ‘U’
OPEN TTableNames
FETCH NEXT
FROM TTableNames
INTO @fTableName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #Temp
EXEC dbo.sp_fkeys @fTableName
FETCH NEXT
FROM TTableNames
INTO @fTableName
END
CLOSE TTableNames
DEALLOCATE TTableNames
SET @FieldNames = ”
SET @fTableName = ”
SELECT DISTINCT FK_NAME AS FKName,FKTABLE_NAME AS FTName,
@FieldNames AS FTFields,PKTABLE_NAME AS STName,
@FieldNames AS STFields,@FieldNames AS FKType
INTO #Temp1
FROM #Temp
ORDER BY FK_NAME,FKTABLE_NAME,PKTABLE_NAME
DECLARE FK_CUSROR CURSOR FOR
SELECT FKName
FROM #Temp1
OPEN FK_CUSROR
FETCH
FROM FK_CUSROR INTO @FKName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE FK_FIELDS_CUSROR CURSOR FOR
SELECT FKCOLUMN_NAME,PKCOLUMN_NAME,UPDATE_RULE,DELETE_RULE
FROM #TEMP
WHERE FK_NAME = @FKName
ORDER BY KEY_SEQ
OPEN FK_FIELDS_CUSROR
FETCH
FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,
@fUpdateRule,@fDeleteRule
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #Temp1 SET FTFields = CASE WHEN LEN(FTFields)
= 0 THEN ‘['+@FKColumnName+']‘
ELSE FTFields
+‘,['+@FKColumnName+']‘ END
WHERE FKName = @FKName
UPDATE #Temp1 SET STFields = CASE WHEN LEN(STFields)
= 0 THEN ‘['+@PKColumnName+']‘
ELSE STFields
+‘,['+@PKColumnName+']‘ END
WHERE FKName = @FKName
FETCH NEXT
FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,
@fUpdateRule,@fDeleteRule
END
UPDATE #Temp1 SET FKType = CASE WHEN @fUpdateRule = 0
THEN FKType + ‘ ON UPDATE CASCADE’
ELSE FKType END
WHERE FKName = @FKName
UPDATE #Temp1 SET FKType = CASE WHEN @fDeleteRule = 0
THEN FKType + ‘ ON DELETE CASCADE’
ELSE FKType END
WHERE FKName = @FKName
CLOSE FK_FIELDS_CUSROR
DEALLOCATE FK_FIELDS_CUSROR
FETCH next
FROM FK_CUSROR INTO @FKName
END
CLOSE FK_CUSROR
DEALLOCATE FK_CUSROR
SELECT ‘ALTER TABLE [dbo].['+FTName+'] ADD
CONSTRAINT ['+FKName+'] FOREIGN KEY (’+FTFields+‘)
REFERENCES ['+STName+'] (’+STFields+‘) ’+FKType
FROM #Temp1
SET NOCOUNT OFF
RETURN
GO

Read more @ http://blog.sqlauthority.com/2008/04/18/sql-server-generate-foreign-key-scripts-for-database/

While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction. If the server is busy, the thread may not be immediately scheduled; therefore, the time delay may be longer than the specified time. WAITFOR can be used with query but not with UDF or cursors. WAITFOR wait till TIMEOUT is reached.

--Delay for 20 seconds
WAITFOR DELAY ‘000:00:20′
SELECT ‘20 Second Delay’
GO
—Delay till 7 AM
WAITFOR TIME ‘7:00:00′
SELECT ‘Good Morning’
GO

To Read more Click the below link
http://blog.sqlauthority.com/2007/06/18/sql-server-delay-function-waitfor-clause-delay-execution-of-commands/

Microsoft Says:

SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions.

So the options left out is to use DBCC INDEXDEFRAG which is applicable for online Re-indexing but the best solution is to use ALTER INDEX…

Simple Syntax:
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;