SELECT*

The below link is describing about List of known issues when you install SQL Server 2005 on Windows server 2008 http://support.microsoft.com/kb/936302

CREATE TABLE dbo.testrun_reports ( RunID INT IDENTITY(1,1) PRIMARY KEY, RunDate SMALLDATETIME NOT NULL DEFAULT GETDATE(), db2dbreport VARCHAR(MAX), incexcreport VARCHAR(MAX) ); GO CREATE PROCEDURE dbo.Run_Start @RunID INT OUTPUT AS SET NOCOUNT ON; INSERT dbo.testrun_reports (db2dbreport,incexcreport) values('Started','Started'); SELECT @RunID = SCOPE_IDENTITY(); GO ----Execution of Procedure DECLARE @x INT EXEC Run_Start @x OUTPUT SELECT @x

I we have a primary key and Foreign key relations are there in the tables we can not delete the data from primary key value. Since that value used in the child tables. But this Possible if we create table with ON DELETE CASCADE option. Example is given below. CREATE TABLE Items (Sno INT PRIMARY KEY, [Name] VARCHAR(50)) INSERT INTO Items VALUES(1,'Pen') INSERT INTO Items VALUES(2,'Paper') INSERT INTO Items VALUES(3,'Pencil') INSERT INTO Items VALUES(4,'Sketches') SELECT * FROM Items GO CREATE TABLE Orders ( OrderID INT PRIMARY KEY , Sno INT CONSTRAINT FK_02345 FOREIGN KEY REFERENCES Items(Sno) , Qty INT , FOREIGN KEY (Sno) REFERENCES Items ON DELETE CASCADE ) INSERT INTO Orders VALUES(2,3,15) DELETE FROM Items--ParentTable WHERE Sno=3 SELECT * FROM Orders

CREATE TABLE EmpSalWithDeptWise (EmpID INT,EName CHAR(5),Salary MONEY,DeptID INT) GO empid ename sal deptid 101 A 23000 10 102 B 12000 10 103 C 8000 20 104 D 15000 30 105 E 32000 10 106 F 22000 20 107 G 5000 30 108 H 14000 30 109 I 16000 20 110 J 19000 10 111 K 7000 10 112 L 45000 20 113 M 22000 10 114 N 16000 10 115 O 11000 20 GO SELECT Q.empid,Q.ename, Q.Salary,Q.deptid ,Q.Salary FROM ( SELECT T.empid, T.ename, deptid ,Salary, Row_Order =( SELECT COUNT(T1.empid) + 1 FROM dbo.EmpSalWithDeptWise T1 WHERE T1.Salary < T.Salary AND t1.deptid = t.deptid ) FROM dbo.EmpSalWithDeptWise T)Q WHERE Q.Row_Order <= 2 ORDER BY Q.deptid

We wish to know what locks are being held by transaction. SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS LockedResource, L.request_mode AS LockType, ST.text AS SqlStatementText, ES.login_name AS LoginName, ES.host_name AS HostName, TST.is_user_transaction as IsUserTransaction, AT.name as TransactionName, CN.auth_scheme as AuthenticationMethod FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE

USE DatabaseName GO --Declaration of local variables Declare @CNT int , @ObjectName sysname , @IndexName sysname , @FagmentationPercentage int SET @FagmentationPercentage = 90 IF Object_id('##DBCCShowcontigAll') is not Null BEGIN DROP TABLE ##DBCCShowcontigAll End CREATE TABLE ##DBCCShowcontigAll ( RowNum Int Identity , ObjectName sysname , ObjectId int , IndexName sysname , IndexId smallint , Levle smallint , Pages int , TRows int , MinimumRecordSize int , MaximumRecordSize Int , AverageRecordSize decimal(9,5) , ForwardedRecords int , Extents int , ExtentSwitches int , AverageFreeBytes decimal(9,5) , AveragePageDensity float , ScanDensity float , BestCount int , ActualCount int , LogicalFragmentation float , ExtentFragmentation Float ) INSERT INTO ##DBCCShowcontigAll EXEC ('DBCC SHOWCONTIG WITH ALL_INDEXES ,TABLERESULTS ') PRINT 'Existing Indexes before process of Re-Indexing' PRINT 

To find out restore history of databases we can use RESTORE HISTORY Table in the msdb database. We can run the below query and find out when the latest restore has happend for the particular database. SELECT * FROM msdb.dbo.RestoreHistory WHERE Destination_Database_Name='DatabaseName' ORDER BY Restore_Date DESC SELECT [restore_date] ,[destination_database_name] ,[user_name] ,[backup_set_id] ,[restore_type] ,[ replace ] ,[recovery] ,[ restart ] FROM [msdb].[dbo].[restorehistory] WHERE destination_database_name = 'AdventureWorks2012'

While installing SQL Server 2005 setup in our box. REPORTING SERVICES will be in Disable mode. This is because IIS not installed in our system . IIS is compulsory for Reporting Services.

Step 1. Put SQL Server into Single User Mode The first thing you will want to do is stop the SQL Sever Sevicer (sqlservr.exe) and the associated services (Agent, Full Text, Etc). There are a few ways to do this, but the easiest way would be to use the SQL Configuration Manager (Start->All Programs->Microsoft Sql Server 2005->Configuration Tools->Sql Server Configuration Manager). From this console you can manage the various SQL server services running on the machine. Right Click on each service listed and stop the service. The services are stopped, you can proceed to Step 2. Step 2. Start the SQL server in Single User Mode Open a command window and navigate to the folder where Sqlservr.exe resides (generally :\Program Files\Microsoft Sql Server\MSSQL.1\MSSQL\Binn). Run “sqlserver.exe -m” to start the sql server from single user mode. Step 3. Rebuild the system databases In SQL 2005, the rebuildm.exe program is nto supported. To rebuild the master database you nee

While we are restoring database from prdouction server to our local server there may be some orphaned users in the database.So we can findout those users by running the below query. Use DatabaseName go sp_change_users_login 'report' You will get UserNames and their uniqueidentifier numbers.Then you can update the orphaned users. sp_change_users_login 'update_one','LoginName','loginName' Eg:sp_change_users_login 'update_one','ramesh','ramesh' use DatabaseNameHere GO EXEC sp_change_users_login 'report' go --Pass the values that comes as users from above 'report' list exec sp_change_users_login 'update_one' , 'UserName' , 'UserName' The below commands from dbatools cls Get-DbaDbRestoreHistory ` -SqlInstance SQLServerInstanceName ` -Database DatabaseName ` -Since '2022-10-17 00:00:00' ` -Verbose Get-DbaDbOrphanUser ` -SqlInstance SQLServerInstanceName ` -Database Databas

CREATE TABLE Alpha ( ID int IDENTITY(0,1), AlphaID as 'PY'+RIGHT('00'+CONVERT(VARCHAR,ID),3)PERSISTED PRIMARY KEY, [DESC] VARCHAR(64) DEFAULT(''), ModifiedDate DATETIME DEFAULT(GETDATE()) ) INSERT Alpha DEFAULT VALUES INSERT Alpha DEFAULT VALUES INSERT Alpha DEFAULT VALUES INSERT Alpha DEFAULT VALUES INSERT Alpha DEFAULT VALUES SELECT * FROM Alpha

SELECT a.execution_count , OBJECT_NAME(objectid) Name, query_text = SUBSTRING( b.text, a.statement_start_offset/2, ( CASE WHEN a.statement_end_offset = -1 THEN len(convert(nvarchar(max), b.text)) * 2 ELSE a.statement_end_offset END - a.statement_start_offset)/2 ) , b.dbid , dbname = db_name(b.dbid) , b.objectid , a.creation_time, a.last_execution_time, a.* FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b WHERE OBJECT_NAME(objectid) = 'ProcedureNameHere' ORDER BY a.last_execution_time DESC

TWEAKING SQLSERVER: Installing SQL Server 2008 On Window server 2008 : "http://www.windows-noob.com/forums/index.php?/topic/486-how-can-i-install-sql-server-2008-in-windows-server-2008/"

TWEAKING SQLSERVER: Installing SQL Server 2008 On Window server 2008

TWEAKING SQLSERVER: POINT IN TIME RECOVERY

http://www.windows-noob.com/forums/index.php?/topic/486-how-can-i-install-sql-server-2008-in-windows-server-2008/