Database Explorer

Findings Words in a File from Any Directory

2012-01-24T03:01:00.001-08:00

Go to the particular directory and search using the below syntax:
FINDSTR /S /I write_name_you_are_searching *.* >C:\TMP\Results.txt
FINDSTR /S /I "deleted" *.* >C:\TMP\Results.txt

Reference: http://www.computerhope.com/findstr.htm

DOS Command to OutPut File Names Only

2012-01-17T03:55:00.000-08:00

To display only the procedures starting with dbo.Admin. The below syntax won't provide other informations like date, file size etcC:\backup>dir dbo.Admin* /B > C:\TMP\Procedureslist.txt

SSRS Uninstallation Error

2012-01-05T09:12:00.001-08:00

Reporting Services: Can’t Uninstall – The setup failed to read IIsMimeMap table. The error code is -2147024893Solution:stop IIS and World wide web Publishing service through service.msc and then re-run the Uninstall.

ASP.Net 32 Bit Enable in 64 Bit System

2012-01-05T07:51:00.000-08:00

cscript %SystemDrive%\inetpub\AdminScripts\adsutil.vbs set w3svc/AppPools/Enable32bitAppOnWin64 1

Procedure Parameter Retrieval

2011-12-02T09:22:00.000-08:00

SELECT name ,
TYPE_NAME(user_type_id )TYPE ,
max_length ,
parameter_id param_order
FROM sys.all_parameters
WHERE object_id = OBJECT_ID('[dbo].[sp_name_p]' )
AND is_output<>1 -- Excluding the OUTPUT parameter

PowerShell Script to find File Size

2011-12-01T06:03:00.000-08:00

cd/
powershell
cd\

clear-Host
$FolderPathToVerify="c:\backup\TestTools"
$FilePathToWriteReport="C:\backup\TestTools\Tested.csv"
Get-ChildItem $FolderPathToVerify -force -Recurse -ErrorAction "SilentlyContinue" |Select-Object Name,Length,Directory,CreationTime,LastAccessTime,LastWriteTime |Where-Object {$_.Length -ge 1} |Export-CSV -NoTypeInformation $FilePathToWriteReport

clear-Host
$FolderPathToVerify="c:\backup\TestTools"
$FilePathToWriteReport="C:\backup\TestTools\Tested.csv"
Get-ChildItem $FolderPathToVerify -force -Recurse -ErrorAction "SilentlyContinue" |Select-Object Name,Length,Directory,CreationTime,LastAccessTime,LastWriteTime |Export-CSV -NoTypeInformation $FilePathToWriteReport

cd/
powershell
cd\
Get-ChildItem c:\backup\TestTools -force -Recurse -ErrorAction "SilentlyContinue" |Where-Object {$_.Length -ge 0} |Export-Csv -Delimiter ',' -NoTypeInformation -Path C:\backup\DriveVersion.csv
pause

IIS 6 - Backup and Restore

2011-11-21T06:02:00.000-08:00

All below steps involved in the command line:

/sp = source path

/lm = local machine

/dp = destination path

/d = password == /d passwordneedstowrite

/f c:\path = Pathname

Go to command prompt and type:

iiscnfg

Enter

The script does not work with WScript

Would you like to register CScript as your default host for VBScript

Click Yes to register CScript iiscnfg /export /sp /lm /children /inherited /d password /f c:\IISConfig.bak

Go to the directory where you backup the configuration: cd\ dir *.bak

To see the backup file use the below command: more IISConfig.bak iiscnfg /import /dp /lm /sp /lm /children /inherited /d password /f c:\IISConfig.bak

Rename the SQL Server Instance

2011-11-18T04:17:00.000-08:00

Get the current name of the SQL Server instance for later comparison.
SELECT @@servername
Remove server from the list of known remote and linked servers on the local instance of SQL Server.
EXEC master.dbo.sp_dropserver 'OLDSERVRNAME'
Define the name of the local instance of SQL Server.
EXEC master.dbo.sp_addserver NEWSERVERNAME, 'local'
Get the new name of the SQL Server instance for comparison.
SELECT @@servername

SQL Management Studio

2011-10-20T04:32:00.000-07:00

Type SqlWb command in run window to open sql server (for sql server 2005).
Type ssms command in run window to open sql server (for sql server 2008).

MULTIPLE ROWS IN ONE COLUMN TO SINGLE ROWS WITH COMMA SEPARATION

2011-10-18T06:46:00.000-07:00

CREATE TABLE TESTCOLROWS (ID INT IDENTITY(1,1), NAME VARCHAR(10))
GO
GO
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST1')
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST2')
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST3')
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST4')
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST5')
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST6')
INSERT INTO TESTCOLROWS (NAME) VALUES ('')
INSERT INTO TESTCOLROWS (NAME) VALUES ('TEST7')
INSERT INTO TESTCOLROWS (NAME) VALUES (NULL)
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + ISNULL(NAME,'')
FROM TESTCOLROWS
SELECT @listStr
GO
DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + NAME + ','
FROM TESTCOLROWS
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)
GO
CREATE FUNCTION [dbo].[UDF_DOCUMENTIDCHILD] ( @ID INT )
RETURNS VARCHAR(100)
AS BEGIN
--DECLARE @ID INT
DECLARE @listStr VARCHAR(100)
SELECT @listStr = COALESCE(@listStr+',' , '') + ISNULL(NAME,'')
FROM TESTCOLROWS WHERE ID=@ID;
RETURN @listStr
END
GO
SELECT DBO.[UDF_DOCUMENTIDCHILD] (2) AS NAME

Generate Sequential Numbers

2011-06-15T07:57:00.000-07:00

CREATE PROCEDURE GENERATEROWNUMBERS(@NUMBEROFROWS INT, @STARTNUMBER INT)AS BEGIN
DECLARE @NUMGEN TABLE (NUM INT)
DECLARE @CNT INT SET @CNT = 1
WHILE @CNT <= 100 BEGIN
INSERT INTO @NUMGEN
SELECT @CNT SET @CNT = @CNT + 1
END
SELECT @STARTNUMBER + ROWNUM FROM ( SELECT ROW_NUMBER() OVER (ORDER BY N1.NUM) AS ROWNUM FROM @NUMGEN N1, @NUMGEN N2, @NUMGEN N3, @NUMGEN N4 ) ROWNUMS WHERE ROWNUM <= @NUMBEROFROWS
END

Hour Based - Group by SQL Query

2011-06-10T09:52:00.000-07:00

The below query can be useful if you want to see the result with hour based information:

select convert(varchar(13), datetime, 120), count(datetime), sum(connectionduration/60.0) from T_tablename(nolock) where datetime between '2011-06-07' and '2011-06-08'
group by convert(varchar(13), datetime, 120) order by 1

SQL Server 2008 Expiry Date Find

2011-04-04T15:53:00.000-07:00

There are three ways to get this information,

Solution 1

Open the Management Studio, click on the "Help" of Menu Bar and then on "About".

Solution 2

You can check the windows installer for the SQL Product code to pull out the install date.

In registry look under,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\\InstallProperties

Notes :

•The "InstallDate" key will have the date stored in YYYYMMDD
•For SQL 2008 you can search for "Microsoft SQL Server 2008 Database Engine Services" to get the correct GUID.
•For SQL 2005 you can search for "Microsoft SQL Server 2005" only to get the correct GUID

Solution 3

Get this via following TSQL Script

SELECT create_date as 'SQL Server Install Date',
DATEADD(dd,180,create_date) as 'Will Stop Working ON'
FROM sys.server_principals WHERE name='NT AUTHORITY

Top and Set Row Count - SQL Server

2011-04-03T15:23:00.000-07:00

http://www.databasejournal.com/features/mssql/article.php/3532226/TOP-Clause-in-SQL-Server-2005.htm

How do I Transfer logins from one SQL Server 2005 instance to another?

2011-03-01T18:02:00.000-08:00

http://support.microsoft.com/kb/918992/ - Folllow the procedure from microsoft.

USE master
GO
CREATE PROCEDURE sp_hexadecimal, sp_help_revlogin and get the output script from EXEC sp_help_revlogin in the primary server or old server or source server.

Once done, you have to synchronise the database users

Run the below script under the database where you want the users to synchronise:

DECLARE @UserName nvarchar(255)
DECLARE Cursor_OrphanedUser cursor for
SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name
OPEN Cursor_OrphanedUser
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' Synchronization of Logins in Progress'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
END
CLOSE Cursor_OrphanedUser
DEALLOCATE Cursor_OrphanedUser

Refreshing Website using Meta Tag

2011-03-01T01:19:00.000-08:00

Objective: Refreshing Website using Meta Tag:

Please keep the meta refresh tag in the sitemanager.master.vb

TEMPDB Usage

2011-02-20T11:52:00.001-08:00

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/12/tempdb-monitoring-and-troubleshooting-out-of-space.aspx

select top 10
t1.session_id,
t1.request_id,
t1.task_alloc,
t1.task_dealloc,
(SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE statement_end_offset
END - t2.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan

from (Select session_id, request_id,
sum(internal_objects_alloc_page_count + user_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1,
sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and
(t1.request_id = t2.request_id) and
t1.session_id > 50
order by t1.task_alloc DESC

Finding Filegroup - Objects

2011-02-04T07:37:00.001-08:00

select distinct(object_name(id)) from sysindexes
where groupid=filegroup_id('PRIMARY')

Adding Pipe Delimited Option for Report Rendering

2011-01-11T04:11:00.000-08:00

One of my customers created a report used to send data to another system via a file transfer. The problem is the transfer is not direct and requires a process to convert the CSV to pipe delimited. The system receiving the data has a format it expects that isn’t configurable and must be pipe delimited. Reporting Services comes with the ability to export a comma delimited out of the box, but that’s the only text file option by default.

After a little chat with Mr. Google I found that adding a new data rendering extension in SSRS is quite simple and any text file output you may want is just a change to the reportserver.config file away.

It can be used in file share delivery for your subscriptionsThe Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport has numerous configuration options, pipe delimited is just one

Job Owner, Enabled Status, Job Name - Findings

2011-01-01T05:11:00.000-08:00

USE MSDB
GO
SELECT GETDATE() AS 'ExecutionTime'
GO
SELECT @@SERVERNAME AS 'SQLServerInstance'
GO
SELECT j.[name] AS 'JobName',
Enabled = CASE WHEN j.Enabled = 0 THEN 'No'
ELSE 'Yes'
END,
l.[name] AS 'OwnerName'
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY l.[name]

BCP QUERYOUT WITH FIRSTROW, LASTROW, BATCHSIZE ETC

2010-12-22T01:52:00.000-08:00

BCP QUERYOUT WITH FIRSTROW, LASTROW, BATCHSIZE ETC

BCP "SELECT COLUMNNAME from DBNAME.dbo.TABLENAME (nolock)" queryOut "c:\backup\TABLENAME.txt" -c -T -F 1 -L 57318503 -b 50000 -S "PC123"

BCP "SELECT COLUMNNAME from DBNAME.dbo.TABLENAME (nolock)" queryOut "c:\backup\TABLENAME.txt" -c -T -F 57318504 -L 114637007 -b 50000 -S "PC123"

Login Transfer

2010-12-13T07:41:00.000-08:00

How to transfer logins and passwords between instances of SQL Server (2000-2005 or 2000-2000 sql server)

Refer: http://support.microsoft.com/kb/246133

How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

Refer: http://support.microsoft.com/kb/918992

Move TempDB to New Location

2010-09-03T11:51:00.000-07:00

1. Open SQL Server Management Studio.

2. Connect to the desired server.

3. Click the New Query button.

4. Copy and paste the following into the query pane:
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '{new location}\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '{new location}\templog.ldf');
GO

5. Change {new location} in the pasted code (for both the tempdb.mdf and templog.ldf files) to the path of the new location.

6. Click Execute.

7. Go to the Control Panel and then Administrative Tools. Select Services.

8. Stop and Start SQL Server (MSSQLSERVER).

9. Go back to SQL Server Management Studio and open a new query pane.

10. Copy and paste the following to verify that tempdb has moved to the new location:
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

11. Click Execute.

12. In the physical_name column, you should see the path to the new location.

MS DOS USEFUL COMMANDS

2010-09-03T09:41:00.000-07:00

MS DOS USEFUL COMMANDS

To Access… Run Command
Accessibility Controls access.cpl
Accessibility Wizard accwiz
Add Hardware Wizard hdwwiz.cpl
Add/Remove Programs appwiz.cpl
Administrative Tools control admintools
Adobe Acrobat (if installed) acrobat
Adobe Designer (if installed) formdesigner
Adobe Distiller (if installed) acrodist
Adobe ImageReady (if installed) imageready
Adobe Photoshop (if installed) photoshop
Automatic Updates wuaucpl.cpl
Bluetooth Transfer Wizard fsquirt
Calculator calc
Certificate Manager certmgr.msc
Character Map charmap
Check Disk Utility chkdsk
Clipboard Viewer clipbrd
Command Prompt cmd
Component Services dcomcnfg
Computer Management compmgmt.msc
Control Panel control
Date and Time Properties timedate.cpl
DDE Shares ddeshare
Device Manager devmgmt.msc
Direct X Control Panel (if installed)* directx.cpl
Direct X Troubleshooter dxdiag
Disk Cleanup Utility cleanmgr
Disk Defragment dfrg.msc
Disk Management diskmgmt.msc
Disk Partition Manager diskpart
Display Properties control desktop
Display Properties desk.cpl
Display Properties (w/Appearance Tab Preselected) control color
Dr. Watson System Troubleshooting Utility drwtsn32
Driver Verifier Utility verifier
Event Viewer eventvwr.msc
Files and Settings Transfer Tool migwiz
File Signature Verification Tool sigverif
Findfast findfast.cpl
Firefox (if installed) firefox
Folders Properties folders
Fonts control fonts
Fonts Folder fonts
Free Cell Card Game freecell
Game Controllers joy.cpl
Group Policy Editor (XP Prof) gpedit.msc
Hearts Card Game mshearts
Help and Support helpctr
HyperTerminal hypertrm
Iexpress Wizard iexpress
Indexing Service ciadv.msc
Internet Connection Wizard icwconn1
Internet Explorer iexplore
Internet Properties inetcpl.cpl
Internet Setup Wizard inetwiz
IP Configuration (Display Connection Configuration) ipconfig /all
IP Configuration (Display DNS Cache Contents) ipconfig /displaydns
IP Configuration (Delete DNS Cache Contents) ipconfig /flushdns
IP Configuration (Release All Connections) ipconfig /release
IP Configuration (Renew All Connections) ipconfig /renew
IP Configuration (Refreshes DHCP & Re-Registers DNS) ipconfig /registerdns
IP Configuration (Display DHCP Class ID) ipconfig /showclassid
IP Configuration (Modifies DHCP Class ID) ipconfig /setclassid
Java Control Panel (if installed) jpicpl32.cpl
Java Control Panel (if installed) javaws
Keyboard Properties control keyboard
Local Security Settings secpol.msc
Local Users and Groups lusrmgr.msc
Logs You Out Of Windows logoff
Malicious Software Removal Tool mrt
Microsoft Access (if installed) msaccess
Microsoft Chat winchat
Microsoft Excel (if installed) excel
Microsoft Frontpage (if installed) frontpg
Microsoft Movie Maker moviemk
Microsoft Paint mspaint
Microsoft Powerpoint (if installed) powerpnt
Microsoft Word (if installed) winword
Microsoft Syncronization Tool mobsync
Minesweeper Game winmine
Mouse Properties control mouse
Mouse Properties main.cpl
Nero (if installed) nero
Netmeeting conf
Network Connections control netconnections
Network Connections ncpa.cpl
Network Setup Wizard netsetup.cpl
Notepad notepad
Nview Desktop Manager (if installed) nvtuicpl.cpl
Object Packager packager
ODBC Data Source Administrator odbccp32.cpl
On Screen Keyboard osk
Opens AC3 Filter (if installed) ac3filter.cpl
Outlook Express msimn
Paint pbrush
Password Properties password.cpl
Performance Monitor perfmon.msc
Performance Monitor perfmon
Phone and Modem Options telephon.cpl
Phone Dialer dialer
Pinball Game pinball
Power Configuration powercfg.cpl
Printers and Faxes control printers
Printers Folder printers
Private Character Editor eudcedit
Quicktime (If Installed) QuickTime.cpl
Quicktime Player (if installed) quicktimeplayer
Real Player (if installed) realplay
Regional Settings intl.cpl
Registry Editor regedit
Registry Editor regedit32
Remote Access Phonebook rasphone
Remote Desktop mstsc
Removable Storage ntmsmgr.msc
Removable Storage Operator Requests ntmsoprq.msc
Resultant Set of Policy (XP Prof) rsop.msc
Scanners and Cameras sticpl.cpl
Scheduled Tasks control schedtasks
Security Center wscui.cpl
Services services.msc
Shared Folders fsmgmt.msc
Shuts Down Windows shutdown
Sounds and Audio mmsys.cpl
Spider Solitare Card Game spider
SQL Client Configuration cliconfg
System Configuration Editor sysedit
System Configuration Utility msconfig
System File Checker Utility (Scan Immediately) sfc /scannow
System File Checker Utility (Scan Once At The Next Boot) sfc /scanonce
System File Checker Utility (Scan On Every Boot) sfc /scanboot
System File Checker Utility (Return Scan Setting To Default) sfc /revert
System File Checker Utility (Purge File Cache) sfc /purgecache
System File Checker Utility (Sets Cache Size to size x) sfc /cachesize=x
System Information msinfo32
System Properties sysdm.cpl
Task Manager taskmgr
TCP Tester tcptest
Telnet Client telnet
Tweak UI (if installed) tweakui
User Account Management nusrmgr.cpl
Utility Manager utilman
Windows Address Book wab
Windows Address Book Import Utility wabmig
Windows Backup Utility (if installed) ntbackup
Windows Explorer explorer
Windows Firewall firewall.cpl
Windows Magnifier magnify
Windows Management Infrastructure wmimgmt.msc
Windows Media Player wmplayer
Windows Messenger msmsgs
Windows Picture Import Wizard (need camera connected) wiaacmgr
Windows System Security Tool syskey
Windows Update Launches wupdmgr
Windows Version (to show which version of windows) winver
Windows XP Tour Wizard tourstart
Wordpad write

Truncate tables monthly after backup

2010-09-03T04:48:00.000-07:00

Objective: Truncate tables monthly after backup

DECLARE @yearmonth char(6)
DECLARE @DBNAME VARCHAR(11)
DECLARE @SQL VARCHAR(8000)
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

SET @yearmonth=CAST(CONVERT(VARCHAR(4),GETDATE(),112)+ right('00' + convert(varchar,(month(getdate())-2)),2) AS INT)
SET @DBNAME='dbanameprefix'+ @yearmonth
SET @path = '\\ipaddress\Backup\servername\BACKUPBEFORETRUNCATE\'

SET @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SET @FILENAME = @path + @DBNAME + '_' + @fileDate + '.BAK'

PRINT @FILENAME

SET @SQL=
'
USE [master]
GO
BACKUP DATABASE ' + @DBNAME + ' TO DISK='''+@FILENAME+''';
exec master.dbo.xp_cmdshell ''c:\tools\gzip -r '+cast(@PATH as varchar(256))+'*.BAK'';
TRUNCATE TABLE ' + @DBNAME + '.DBO.ARTA_CALLRECORDS;
TRUNCATE TABLE ' + @DBNAME + '.DBO.ARTA_CALLRECORDS_DETAIL;
TRUNCATE TABLE ' + @DBNAME + '.DBO.GR_MTG_CALLRECORDS;
TRUNCATE TABLE ' + @DBNAME + '.DBO.MTG_CALLRECORDS;
USE ' + @DBNAME + '
GO
DBCC SHRINKDATABASE(N'''+ cast(@DBNAME as varchar) + ''' )
'
PRINT @SQL
SET @yearmonth=CAST(CONVERT(VARCHAR(4),GETDATE(),112)+ right('00' + convert(varchar,(month(getdate())-1)),2) AS INT)
SET @DBNAME='dbanameprefix'+ @yearmonth
SET @FILENAME = @path + @DBNAME + '_' + @fileDate + '.BAK'
SET @SQL=
'
USE [master]
GO
BACKUP DATABASE ' + @DBNAME + ' TO DISK='''+@FILENAME+''';
exec master.dbo.xp_cmdshell ''c:\tools\gzip -r '+cast(@PATH as varchar(256))+'*.BAK'';
TRUNCATE TABLE ' + @DBNAME + '.DBO.ARTA_CALLRECORDS_DETAIL;
USE ' + @DBNAME + '
GO
DBCC SHRINKDATABASE(N'''+ cast(@DBNAME as varchar) + ''' )
'
PRINT @SQL
SET @yearmonth=CAST(CONVERT(VARCHAR(4),GETDATE(),112)+ right('00' + convert(varchar,(month(getdate())-4)),2) AS INT)
SET @DBNAME='dbanameprefix'+ @yearmonth
SET @SQL=
'
USE [master]
GO
ALTER DATABASE ' + @DBNAME + ' SET READ_ONLY WITH NO_WAIT
GO
ALTER DATABASE ' + @DBNAME + ' SET READ_ONLY
'
PRINT @SQL

CHECKIDENT

2010-08-21T16:16:00.001-07:00

OBJECTIVE: To find out the current identity value in the current table, use the below syntax to find out.
USE DATABASENAME
go
DBCC CHECKIDENT ("DBO.TABLENAME", NORESEED);

For further scripts documentation: Microsoft library

Reindexing - Update Statistics - Based on Fragmentation Level

2010-08-16T15:41:00.000-07:00

OBJECTIVE: Reindexing based on Fragmentation Level:

CREATE PROCEDURE [dbo].[P_REINDEXING_REBUILD_STATSUPDATE]
AS
BEGIN
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T_Fragmentation_Info]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].T_Fragmentation_Info(
[ID] int identity(1,1) NOT NULL,
[Frag_Date] [datetime] NULL,
[Table_Name] [varchar](100) NULL,
[Databasename] [varchar](100) NULL,
[Index_ID] [int] NULL,
[Index_Name] [varchar](100) NULL,
[Avg_Frag_Percentage] [numeric](15, 12) NULL,
[Action_Take] [varchar](30) NULL,
[Index_Type] [varchar](20) NULL,
[INDEXSTATUS] INT NOT NULL DEFAULT(1),
[STATSSTATUS] INT NOT NULL DEFAULT(1),
[INDEXQUERY] NVARCHAR(4000) NULL,
[STATSQUERY] NVARCHAR(4000) NULL,
[IX_START_DATE] DATETIME NULL,
[IX_END_DATE] DATETIME NULL,
[STATS_START_DATE] DATETIME NULL,
[STATS_END_DATE] DATETIME NULL
) ON [PRIMARY]
END
SET ANSI_PADDING OFF;

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].T_objectsinfo') AND type in (N'U'))
BEGIN
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
create table [dbo].T_objectsinfo (ServerName VARCHAR(100), DatabaseName VARCHAR(100),Name VARCHAR(100), ID VARCHAR(100))
END
SET ANSI_PADDING OFF;
--Select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,* from INFORMATION_SCHEMA.TABLES Where table_type='Base Table'

Truncate table T_objectsinfo
Truncate table [DBMONITOR].[dbo].T_Fragmentation_Info

DECLARE @TableInfo TABLE ( ServerName VARCHAR(100), DatabaseName VARCHAR(100),Name VARCHAR(100), ID VARCHAR(100))
DECLARE @command VARCHAR(5000)
SELECT @command = 'Use [' + '?' + '] Select @@servername as ServerName, ' + '''' + '?' + '''' + ' AS DatabaseName,Name,ID from sys.sysobjects (nolock) where xtype=''u'' and (name NOT LIKE ''%TEMP%'' AND NAME NOT LIKE ''%DELETE%'' AND
NAME NOT LIKE ''%TEST%'' AND name NOT LIKE ''%2010%'' AND name NOT LIKE ''%2009%'' AND name NOT LIKE ''%2008%''
AND name NOT LIKE ''%2007%'' )'
--Select @command
INSERT INTO T_objectsinfo (ServerName, DatabaseName,Name, ID)
EXEC sp_MSForEachDB @command SELECT ServerName, DatabaseName,Name,ID from @TableInfo
select * from T_objectsinfo

SET nocount ON
DECLARE @strDatabaseName Varchar (50)
Declare @strName Varchar (200)
Declare @intID int
Declare curTables Cursor For Select DatabaseName,Name,ID from T_objectsinfo (nolock)
Open curTables
Fetch Next From curTables Into @strDatabaseName,@strName,@intID
While @@FETCH_STATUS = 0
Begin

Declare @SQL nVarchar (4000)
BEGIN
SET @SQL='
USE ' + @strDatabaseName + '
INSERT INTO [DBMONITOR].[dbo].T_Fragmentation_Info
([Databasename],[Frag_Date],[Table_Name],[Index_ID],[Index_Name],[Avg_Frag_Percentage])
SELECT '''+@strDatabaseName+''' AS [Databasename] ,Getdate() as [Frag_Date],'''+ @strName+''' as [Table_Name],a.index_id as [Index_ID], name as [Index_Name] , round(avg_fragmentation_in_percent,2) as [Avg_Frag_Percentage]
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('''+ @strName+'''),NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
'
PRINT @SQL
EXEC (@SQL)

update [DBMONITOR].[dbo].T_Fragmentation_Info set Action_Take='REORGANIZE' where Avg_Frag_Percentage between 5 and 30 and Action_Take is null and databasename=@strDatabaseName
update [DBMONITOR].[dbo].T_Fragmentation_Info set Action_Take='REBUILD' where Avg_Frag_Percentage>30 and Action_Take is null and databasename=@strDatabaseName
update [DBMONITOR].[dbo].T_Fragmentation_Info set Action_Take='None' where Avg_Frag_Percentage<5 databasename="@strDatabaseName" index_type="'CLUSTERED'" index_id="1" databasename="@strDatabaseName" index_type="'NONCLUSTERED'">1 and Index_Type is null and databasename=@strDatabaseName

End

Fetch Next From curTables Into @strDatabaseName,@strName,@intID
End
Close curTables
Deallocate curTables
SET nocount OFF

Declare @strID Varchar (20)
Declare @strAction Varchar (20)
Declare @strTableName Varchar (100)
Declare @strIndexName Varchar (100)
Declare @strQuery Varchar (1000)
Declare curTablesIndexs Cursor For Select ID,[Databasename],Table_Name,Index_Name,ACTION_TAKE from [DBMONITOR].[dbo].T_Fragmentation_Info WHERE (INDEXSTATUS=1 OR STATSSTATUS=1)
Open curTablesIndexs
Fetch Next From curTablesIndexs Into @strID,@strDatabaseName,@strTableName,@strIndexName,@strAction
While @@FETCH_STATUS = 0
Begin
IF @STRACTION='None'
BEGIN
PRINT 'NO REINDEX OR REORGANIZE NECESSARY'
UPDATE T_Fragmentation_Info SET INDEXSTATUS=0,STATSSTATUS=0,IX_START_DATE=GETDATE(),IX_END_DATE=GETDATE(),STATS_START_DATE=GETDATE(),STATS_END_DATE=GETDATE(),INDEXQUERY='NO ACTION NECESSARY',STATSQUERY='NO ACTION NECESSARY' WHERE ID=@STRID
END
ELSE
BEGIN
UPDATE T_Fragmentation_Info SET IX_START_DATE=GETDATE() WHERE ID=@STRID
SET @STRQUERY='USE ' + @strDatabaseName + '
ALTER INDEX ' + @strIndexName + ' on ' + @strTableName + ' '+@strAction+';
'
PRINT (@STRQUERY )

UPDATE T_Fragmentation_Info SET INDEXSTATUS=0,IX_END_DATE=GETDATE(),STATS_START_DATE=GETDATE(),INDEXQUERY=@STRQUERY WHERE ID=@STRID
SET @STRQUERY='USE ' + @strDatabaseName + '
UPDATE STATISTICS ' + @strTableName +' '+ @strIndexName + ';
'
PRINT (@STRQUERY )
UPDATE T_Fragmentation_Info SET STATSSTATUS=0,STATS_END_DATE=GETDATE(),STATSQUERY=@STRQUERY WHERE ID=@STRID

END

Fetch Next From curTablesIndexs Into @strID,@strDatabaseName,@strTableName,@strIndexName,@strAction
End
Close curTablesIndexs
Deallocate curTablesIndexs

END

SQLCMD - Replacement solution for BCP

2010-07-29T00:15:00.000-07:00

Objective: The below script is used to extract data from sql table in the file and transfer to the remote location.
use DBMONITOR
go
DECLARE @DBNAME VARCHAR(50)
DECLARE @SELECTQUERY VARCHAR(MAX)
DECLARE @SERVERNAME VARCHAR(50)
DECLARE @return_value INT
Declare @strFileName Varchar(MAX)
DECLARE @SQL VARCHAR(8000)
DECLARE @idInc INT
Set @strFileName = '\\RemoteLocation\backup\TEST\TEST.DAT'
Set @Servername='IP Address or Servername' -- To get servername - Go to SQL Server - Query - Select @@servername
Set @DBNAME='Databasename'
set @SELECTQUERY='select top 10 * from Tablename'
SET @SQL ='Sqlcmd -S '+ cast(@SERVERNAME as varchar(50)) +' -E -d '+ cast(@DBNAME as varchar(50)) +' -Q "'+CAST(@SELECTQUERY AS nVARCHAR(MAX))+' " -o '+ cast(@strFileName as varchar(MAX))+' -s"," -W'
Print @SQL
EXECUTE @return_value = master..xp_cmdshell @SQL

Database Mail XPs

2010-07-06T02:59:00.000-07:00

To enable 'Database Mail XPs'
Scripts:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

REINDEXING - UPDATE STATS

2010-07-05T11:25:00.000-07:00

ALTER PROCEDURE P_ReIndexDatabases_UpdateStats
AS
Declare @sql varchar(max)
DECLARE @MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT TABLE_NAME
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @MyTable
SET @sql='ALTER INDEX ALL ON ['+ @MyTable +'] REBUILD WITH (ONLINE=ON)'
--PRINT @SQL
EXEC (@SQL)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats

XP Command Shell

2010-07-02T01:24:00.001-07:00

-- To allow advanced options to be changed.
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Collation - Identification

2010-06-24T02:19:00.000-07:00

Objective: This script used to identify the collation, recovery model and status.

SELECT name,
DATABASEPROPERTYEX(name, 'Recovery'),
DATABASEPROPERTYEX(name, 'Status'),
DATABASEPROPERTYEX(name, 'collation'),
DATABASEPROPERTYEX(name, 'UserAccess'),
DATABASEPROPERTYEX(name, 'VERSION')
FROM master.dbo.sysdatabases
ORDER BY 1

Website:

http://www.mssqltips.com/tip.asp?tip=1033

SQL Server Date and Time Format

2010-06-18T07:36:00.000-07:00

Objective: Date Time Format

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

http://www.mssqltips.com/tip.asp?tip=1145

select *, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events

EventID StartDate EndDate TotalSeconds
----------- ------------------------- ------------------------- -----------------
1 2007-01-01 06:34:12.000 2007-01-01 12:45:34.000 22282
2 2007-01-02 09:23:08.000 2007-01-02 17:05:37.000 27749
3 2007-01-03 16:34:12.000 2007-01-03 16:55:18.000 1266
4 2007-01-04 11:02:00.000 2007-01-04 14:53:21.000 13881
5 2007-01-05 07:52:55.000 2007-01-05 09:08:48.000 4553
6 2007-01-06 19:59:11.000 2007-01-07 01:23:11.000 19440
7 2007-01-07 03:12:23.000 2007-01-07 20:02:25.000 60602

(7 row(s) affected)

Converting Time Units With Math
I'm sure that (hopefully?) we all know that:

•To convert seconds to hours, simply divide by 3600 (since each hour has 60 seconds * 60 minutes). The remainder is the remaining seconds.
•To convert seconds to minutes, simply divide by 60. The remainder is the remaining seconds.
Nothing too shocking there, right? So, let's do some math. If we have a TotalSeconds, we can get:

•Hours = (TotalSeconds / 3600)
•Remaining Minutes = (TotalSeconds % 3600) / 60
•Remaining Seconds = (TotalSeconds % 60)
(The % is the modulo operator in T-SQL, which returns the remainder when dividing two integers.)

Thus, we can write our SQL like this to return 3 integer columns (Hours, Minutes, Seconds) for each event:

select
EventID,
TotalSeconds / 3600 as Hours,
(TotalSeconds % 3600) / 60 as Minutes,
TotalSeconds % 60 as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x

EventID Hours Minutes Seconds
----------- ----------- ----------- -----------
1 6 11 22
2 7 42 29
3 0 21 6
4 3 51 21
5 1 15 53
6 5 24 0
7 16 50 2

(7 row(s) affected)
Now our results like a lot nicer! We can easily see how long each event is in units that we can quickly identify and work with. Our clients can also easily format the Hours/Minutes/Seconds values into whatever format they need since we are returning clean integer values; no string parsing or converting is required.

Calculating Duration Totals
Now that we have the duration for each event, how do we get the grand totals?

At first glance, it may appear that we could simply sum up our Hours/Minutes/Seconds calculations by wrapping them in SUM() expressions:

select
sum(TotalSeconds / 3600) as Hours,
sum((TotalSeconds % 3600) / 60) as Minutes,
sum(TotalSeconds % 60) as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x

Hours Minutes Seconds
----------- ----------- -----------
38 214 133

(1 row(s) affected)
However, look at those results -- they do not make much sense, we have Seconds and Minutes greater than 59 returned. We'd need to do further math to carry all seconds over 59 to the Minutes column, and then carry Minutes over 59 to the Hours column, and we may wish to return the total number of days as well for hours over 23. It sure seems like this just got very complicated!

The answer, of course, is to keep it simple -- just add up the total seconds for all events first, and then calculate the resulting Hours/Minutes/Seconds from that total:

select
sum(TotalSeconds) / 3600 as Hours,
(sum(TotalSeconds) % 3600) / 60 as Minutes,
sum(TotalSeconds) % 60 as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x

Hours Minutes Seconds
----------- ----------- -----------
41 36 13

(1 row(s) affected)
Now that looks a lot better. The last SELECT may look the same as the previous one, but examine it closely: You will see that we are now taking the SUM(TotalSeconds) first, and then using our formulas on those values. Previously, we did the math first and then added up the results. This causes a very big difference in the results!

If we'd like to return the total days as well for hours over 24, again we just use some basic algebra to get what we need. Each day has 60 seconds * 60 minutes * 24 hours = 86,400 seconds in it, so we just write:

select
sum(TotalSeconds) / 86400 as Days,
(sum(TotalSeconds) % 86400) / 3600 as Hours,
(sum(TotalSeconds) % 3600) / 60 as Minutes,
sum(TotalSeconds) % 60 as Seconds
from
(
select EventID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
) x

Days Hours Minutes Seconds
----------- ----------- ----------- -----------
1 17 36 13

(1 row(s) affected)
Once again, the client can easily format these 4 values any way necessary since we are returning this nice raw data.

Representing TimeSpans and Durations with DateTime
Finally, there is another option instead of breaking the values down into integer units; we could just return regular DateTime data, offset from the "base date" of 1900-01-01 at 12:00:00 AM. That 1900-01-01 date is the mathematical equivalent of a "0", in that it is always the result of subtracting any date from itself, and adding it to any other date will have no affect:

declare @d datetime
declare @BaseDate datetime

set @d= '2005-02-05 6:23:51 PM'

set @BaseDate = @d - @d

select @BaseDate as BaseDate, cast(0 as datetime) as BaseDateAsZero

select @d as Date, @d + @BaseDate as DatePlusBaseDate, @d + 0 DatePlusZero

BaseDate BaseDateAsZero
-------------------------- --------------------------
1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

(1 row(s) affected)

Date DatePlusBaseDate DatePlusZero
-------------------------- ------------------------- -------------------------
2005-02-05 18:23:51.000 2005-02-05 18:23:51.000 2005-02-05 18:23:51.000

(1 row(s) affected)
Take a few minutes to really examine the above code, and play with it yourself as well if necessary to get a feel for what happens when you add an subtract DateTimes. Thus, we could calculate our Duration column using standard DateTime data like this:

select *, EndDate-StartDate as Duration
from Events

EventID StartDate EndDate Duration
----------- ------------------------- ------------------------- ----------------------------
1 2007-01-01 06:34:12.000 2007-01-01 12:45:34.000 1900-01-01 06:11:22.000
2 2007-01-02 09:23:08.000 2007-01-02 17:05:37.000 1900-01-01 07:42:29.000
3 2007-01-03 16:34:12.000 2007-01-03 16:55:18.000 1900-01-01 00:21:06.000
4 2007-01-04 11:02:00.000 2007-01-04 14:53:21.000 1900-01-01 03:51:21.000
5 2007-01-05 07:52:55.000 2007-01-05 09:08:48.000 1900-01-01 01:15:53.000
6 2007-01-06 19:59:11.000 2007-01-07 01:23:11.000 1900-01-01 05:24:00.000
7 2007-01-07 03:12:23.000 2007-01-07 20:02:25.000 1900-01-01 16:50:02.000

(7 row(s) affected)

Compare those results with when we returned Hours/Minutes/Seconds as integers -- if you ignore the date portion, you will see they are the same values, just formatted differently. Thus, our client applications could just omit the Date part of these DateTime durations, and we'd be good to go. There are two issues with this, however:

1.We cannot use SUM() on DateTime data to get totals
2.We cannot completely ignore the Date portion -- what if it overflows to 1900-01-02 ? (i.e., the duration is greater than 24 hours)
Calculating the SUM() of DateTime Values
Let's start with the first point. If we try to write:

select sum(EndDate-StartDate) as Duration
from Events
we get:

Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a datetime data type as an argument.

To work around this, we could convert our DateTime values to float or decimal or some other data type that can be used with SUM(), add up that converted data, and then convert back to a DateTime:

select cast(sum(cast(EndDate - StartDate as float)) as DateTime) as TotalDuration
from Events

TotalDuration
------------------------------------------------------
1900-01-02 17:36:13.000

(1 row(s) affected)
Or, we could simply add up the number of seconds for each duration as we did before, and then add the TotalSeconds returned to the "BaseDate":

select dateadd(second, sum(datediff(second,startDate,EndDate)),0) as TotalDuration
from Events

TotalDuration
------------------------------------------------------
1900-01-02 17:36:13.000

(1 row(s) affected)
That works equally well. Break down the two formulas if necessary to see how they are working; or if you like, re-write them using Derived tables to make it more clear. As always, I encourage readers to stop and experiment with simple little scripts like these to test out new concepts to see exactly how things work -- it is much easier and quicker than doing it on your live data, right?

DateTime Durations That Span Multiple Days
Finally, addressing point #2, if we return our duration using the DateTime data type, we need to be sure that our client does not ignore dates other than 1900-01-01. In this case, our TotalDuration value has a date of 1900-01-02, meaning that the duration is not just 17 hours but one day plus 17 hours. There is really no way of ensuring or guaranteeing that the clients will do this, especially if they are written to simply use a format that hides the datetime portion, so what you could do is either a) return the values broken out into days/hours/minutes/seconds as shown previously or b) always return the time at the base date (1900-01-01) and return an additional "Days" column.

To return each unit broken down into integers, we can just use the previous calculations shown on TotalSeconds. However, if we are starting with a DateTime offset from 1900-01-01, we can use the following simple T-SQL Date formulas to break it down:

declare @Duration as DateTime
set @Duration = '1900-01-02 17:36:13.000'

select
DateDiff(day, 0, @Duration) as Days, -- note that 0 equals 1900-01-01, the "base date"
DatePart(Hour, @Duration) as Hours,
DatePart(Minute, @Duration) as Minutes,
DatePart(Second, @Duration) as Seconds

Days Hours Minutes Seconds
----------- ----------- ----------- -----------
1 17 36 13

(1 row(s) affected)
You will see that we just got back to our original 4 integers from the DateTime value, but this time we used the Date functions provided by T-SQL.

The other option, as mentioned, is to return our results in a DateTime format that always uses 1900-01-01 but also includes a separate integer Days column. We can simply calculate that results like this:

declare @Duration as DateTime
set @Duration = '1900-01-02 17:36:13.000'

select
DateDiff(day, 0, @Duration) as DurationDays, -- note that 0 equals 1900-01-01, the "base date"
@Duration - DateDiff(day, 0, @Duration) as DurationTime

DurationDays DurationTime
-------------- -------------------------
1 1900-01-01 17:36:13.000

(1 row(s) affected)
In the above, we are calculating the days as in our previous formula, and then simply subtracting that many days back out from our original Duration ensuring that the DateTime component always returns the time on 1900-01-01. Thus, clients can simply ignore the Date portion when outputting the results, and the additional Days column makes it easy and clear that they need to output and/or handle the Days component as well.

The advantage of returning a DateTime value over Hours/Minutes/Seconds is that clients can easily ignore the date, as mentioned, and format the time using flexible options such as leading zeroes, adding AM or PM versus military time, and so on. It is quite easy to construct a Time format at most clients using integer Hour/Minute/Second values as well, but sometimes it may take more work. Either way, the key is that we are returning properly typed data from our database and not pre-formatted strings that cause more work for both the database and the client.

What About Months and Years?
On a final note, what if we wish to break down our Durations or TimeSpans into Months or Years? The answer is that you cannot -- those units are not precise and vary from year to year and month to month, so the largest unit you can calculate with a TimeSpan is Days. After all, how many months is 29 days? It could be one, it could be zero. And you could break 35 days down into 1 month/4 days (for months with 31 days), or 1 month/5 days (30 day months), and so on. Even years vary between 365 and 366 days. You can always estimate or round TimeSpans to these units (i.e., 360 days is approximately 12 months, or 731 days is approximately 2 years) but you cannot return and calculate precise results in those units.

Summary
I hope this has given you some guidance and ideas on how to effectively work with Durations and TimeSpans in SQL Server using the DateTime data type as well as units broken down into integers. The approach to take varies depending on your needs, but as always: Keep it simple, keep it accurate, prefer math over string parsing, return clean data to your clients, and don't try to format things at the database layer.

BCP - OUT - IN - Bulk Insert

2010-06-17T01:38:00.000-07:00

BULK insert t_Export from 'c:\t_Export.txt' with (batchsize=10000,tablock,FIELDTERMINATOR='\t',keepidentity)
GO
BCP "select * from dbname.dbo.TableName_0 (nolock) where [Date] between '2010-05-01' and '2010-05-31 23:59:59.997' " queryOut D:\dbname_TableName201005.txt -c -T
pause
Exit

BCP dbname.dbo.TableName_0 Out D:\dbname_TableName200912.txt -c -T
pause
Exit

BCP dbname.dbo.TableName_0 in D:\dbname_TableName201005.txt -c -T -E -- E Stand for Identity_Insert ON
Pause
Exit

SET identity_insert TableName_0 on
bulk insert dbname.dbo.TableName_0 from 'D:\dbname_TableName201005.txt' with (FIELDTERMINATOR = ',')
SET identity_insert TableName_0 off

GO

SET identity_insert TableName_0 on
bulk insert dbname.dbo.TableName_0 from 'D:\dbname_TableName201005.txt' with (FIELDTERMINATOR = '\t')
SET identity_insert TableName_0 off

GO

SET identity_insert TableName_0 on
bulk insert dbname.dbo.TableName_0 from 'D:\dbname_TableName201005.txt' with (batchsize=10000,tablock,FIELDTERMINATOR='\t')
SET identity_insert TableName_0 off

GO
SET identity_insert TableName_0 on
bulk insert TableName_0 from 'D:\dbname_TableName201005.txt' with (batchsize=10000,tablock,FIELDTERMINATOR='\t',Firstrow=2)
SET identity_insert TableName_0 off

Truncate Log in SQL Server 2008

2010-06-17T01:19:00.000-07:00

Truncate Log Files:

USE dbname;
GO
SELECT file_id, name FROM sys.database_files; -- Take the file ID from the log file name
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

Article from the below blog is quite good:

http://technet.microsoft.com/en-us/library/ms189493.aspx

http://madhuottapalam.blogspot.com/2008/05/faq-how-to-truncate-and-shrink.html

First of all truncation of transaction log is not a recommended practice. But it is unavoidable if you have not kept proper backup policy and recovery model for your database. Its always better to know the cause and prevention for the transaction log size issue. Refer the following KB for more info

Managing the Size of the Transaction Log File
ahttp://msdn.microsoft.com/en-us/library/ms365418(SQL.100).aspx
Transaction Log Physical Architecture
http://msdn.microsoft.com/en-us/library/ms179355(SQL.100).aspx
Factors That Can Delay Log Truncation
http://msdn.microsoft.com/en-us/library/ms345414(SQL.100).aspx

Now coming to the point. If you have no space left with the drive where the Log file is kept and the size of the Transaction Log file is not manageable then its better to shrink the log.

Broadly , you have two steps here.
(a) Mark the inactive part of Trasaction log to release.
(b) Release the marked release portion of Transaction log to OS.

SQL Server 2005

-- Step 1 – Mark the inactive part of the log for release

Use YourDatabaseName
Go
Backup Log YourDatabaseName With Truncate_Only
GO

-- Step 2 - Release the marked space to OS

Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

Note : If you have single log file the above mentioned script will work. IF you have multiple log file the change the script accordingly

SQl Server 2000

-- Step 1 – Mark the inactive part of the log for release

Use YourDatabaseName
Go
Backup Log YourDatabaseName With Truncate_Only
GO

-- Step 2 - Release the marked space to OS

Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sysfiles where filename like '%.ldf'
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

Note : If you have single log file and the extension of the log file is .LDF the above mentioned script will work. IF you have multiple log file the change the script accordingly

SQL Server 2008

In SQL Server this process have been changed. In 20008, just change the recovery model to simple and then use DBCC SHrinkfile command.

select name,recovery_model_desc from sys.databases
GO
Alter database YourDatabaseName Recovery simple
GO
Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,100)

Trustedproduct

2010-06-06T11:50:00.000-07:00

Satellite- Direct

Restore Database Script

2010-05-20T08:27:00.001-07:00

restore headeronly FROM disk = '\\192.168.0.10\Public\BACKUP\dbname.bak' with nounload

restore filelistonly FROM disk = '\\192.168.0.10\Public\BACKUP\dbname.bak' with file=1,nounload

RESTORE DATABASE dbname FROM disk = '\\192.168.0.10\Public\BACKUP\dbname.bak'
WITH FILE = 1,
Move 'primary_data' To 'H:\dbname\primary_data.mdf',
Move 'Data1_data' To 'H:\dbname\Data1_data.ndf',
Move 'balance_data' To 'H:\dbname\balance_data.ndf',
Move 'topups_data' To 'H:\dbname\topups_data.ndf',
Move 'sim_data' To 'H:\dbname\sim_data.ndf',
Move 'indx_data' To 'H:\dbname\indx_data.ndf',
Move 'dbname_indx_1' To 'H:\dbname\dbname_indx_1.ndf',
Move 'Data2_data' To 'H:\dbname\Data2_data.ndf',
Move 'dbname_indx_2' To 'H:\dbname\dbname_indx_2.ndf',
Move 'dbnamelog' To 'F:\dbname_Log\dbname_log.ldf',
RECOVERY, REWIND, NOUNLOAD

Excel Solution for Breaking Next Line

2010-05-18T05:00:00.000-07:00

Objective: How to bring the sentence to the next line in the same cell of excel sheet

Solution:

Alt+Enter to bring the next line in excel

Useful Websites for SQL Server DBA

2010-05-10T01:37:00.001-07:00

The DBA Script Thumb:
http://www.simple-talk.com/sql/database-administration/the-dba-script-thumb/

Random Number Generation

2010-04-28T08:00:00.000-07:00

Objective: Random number generation from 1 to any number using ROWID

//SQL SERVER 2005 - MAKE SURE THE COLUMN NAME MUST BE UNIQUE

SELECT ROW_NUMBER() OVER(ORDER BY DATABASENAME) AS SERIALNUMBER,databasename from DBMONITOR.dbo.T_SHRINKLOGHISTORY

GO

IN SQL SERVER 2000

WE CAN USE THE FOLLOWING METHOD:

DROP TABLE #A
SELECT IDENTITY(INT, 0,1) AS Rank ,databasename INTO
#A from DBMONITOR.dbo.T_SHRINKLOGHISTORY
SELECT * FROM #A

//SQL SERVER 2005

Declare @table table(id int identity(1,1),databasename varchar(20))
insert into @table
select databasename from T_SHRINKLOGHISTORY
select * from @table

Also refer http://www.sqlteam.com/article/returning-a-row-number-in-a-query

Filegroups and Files

2010-04-28T05:39:00.001-07:00

Objective:
To add, remove filegroups and files - To find objects in filegroup
Solution:
//**Listing 1: T-SQL to Display objects and filegroups not on Primary OR Any other Filegroup**//

select TableNAME = o.name, ObjectName = i.name, i.indid, s.groupname
from sysfilegroups s, sysindexes i, sysobjects o
where i.id = o.id
and o.type in ('S ','U ') --system or user table
and i.groupid = s.groupid
AND s.groupname <> 'PRIMARY'
go

select TableNAME = o.name, ObjectName = i.name, i.indid, s.groupname
from sysfilegroups s, sysindexes i, sysobjects o
where i.id = o.id
and o.type in ('S ','U ') --system or user table
and i.groupid = s.groupid
AND s.groupname = 'PRIMARY'
GO
select TableNAME = o.name, ObjectName = i.name, i.indid, s.groupname
from sysfilegroups s, sysindexes i, sysobjects o
where i.id = o.id
--and o.type in ('S ','U ') --system or user table
--and i.groupid = s.groupid
AND s.groupname = 'PRIMARY'
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = 2 --* New FileGroup*
GO
CREATE UNIQUE CLUSTERED
INDEX PK_TblWithClustIDXOnly ON dbo.TblWithClustIDXOnly (NonClustCol)
WITH
DROP_EXISTING
ON PRIMARY
GO
USE DBMONITOR
GO
EXEC sp_helpfilegroup
GO
EXEC sp_helpfilegroup @filegroupname= 'PRIMARY'
GO
USE DBMONITOR
GO
EXEC sp_helpfile
GO
EXEC sp_helpfile @filename= 'dbmonitor'
GO
//**ADDING A FILE **//
ALTER DATABASE Test1
ADD FILE
(
NAME = Test1dat2,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
) on filegroupname
GO

//***Remove a file from a database
This example removes one of the files added to the Test1 database.
***//
USE master
GO
ALTER DATABASE Test1 REMOVE FILE test1dat4
GO
//***Modify a file
This example increases the size of one of the files added to the Test1 database.
**//
USE master
GO
ALTER DATABASE Test1 MODIFY FILE (NAME = TEST1DATA3,SIZE = 20MB)
GO
ALTER DATABASE MSDB MODIFY FILE ( NAME = N'MSDBData', FILEGROWTH = 5%)
go
ALTER DATABASE [Logging] MODIFY FILE ( NAME = N'Logging', FILEGROWTH = 9%)

REMOVE FILEGROUPS

ALTER DATABASE MyDBName
REMOVE FILEGROUP MyFileGroupName

Actually sp_help calls for this resultset a nondocumented stored procedure:

sp_objectfilegroup OBJECT_ID('')

In order to display the table names and the filegroups in which they are allocated, you can execute:

select o.name, s.groupname
from sysobjects o
join sysindexes i on o.id = i.id
join sysfilegroups s on i.groupid = s.groupid
where o.type in ('U', 'S')
and i.indid < 2

Refer: http://www.mssqltips.com/tip.asp?tip=1112

Recovery for SQL Server repairs corrupted SQL Server databases, backups and logs (.mdf, .ndf, .bak, .ldf)

2010-04-28T04:49:00.000-07:00

Objective:
To recover the SQL Server Database if any damage or corrupt to the files
Solution from Different Vendor:
http://www.recoverytoolbox.com/sql.html
http://www.officerecovery.com/mssql/index.htm

Dead Locks

2010-04-14T10:15:00.000-07:00

DeadLocks Information:
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Troubleshoot%20Deadlocking%20in%20SQL%20Server

Remote Server Administration Tools for Windows 7

2010-04-14T10:00:00.000-07:00

Remote Server Administration Tools for Windows 7 Installation:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7d2f6ad7-656b-4313-a005-4e344e43997d&displaylang=en#filelist

Once Installed the Remote Server Administration Tools for Windows 7, then

Follow below steps

For Window7 64 you have to install the amd64fre_GRMRSATX_MSU.msu

Start menu->Control Panel-> Programs -> Turn Windows Features On and Off -> Check all the Administrative tools Options or Remote Desktop Options

SQL Server Product Information

2010-04-14T02:16:00.001-07:00

Objective:
The below query information retrieve your servername, sql server edition, Product Level like Service Pack version and Product Version number as well

Query:
select @@servername as ServerName,serverproperty('edition') as Edition,serverproperty('productlevel') as ProductLevel,
serverproperty('productversion') as ProductVersion

Msg 7411, Level 16, State 1, Line 1

2010-04-06T03:42:00.000-07:00

Problem or Error Messages in SQL Server 2008:
Msg 7411, Level 16, State 1, Line 1
Server 'ServerName' is not configured for DATA ACCESS.

Executing the below message gives the below error and followed by solution:
select * from openquery ([ServerName],'set fmtonly off; exec master..xp_fixeddrives')
GO
Msg 7411, Level 16, State 1, Line 1
Server 'ServerName' is not configured for DATA ACCESS
GO

SELECT @@SERVERNAME -- To retrieve your server name
GO
-- Executing this below query will sort it out the problem.
Exec sp_serveroption @server = 'ServerName'
,@optname = 'DATA ACCESS'
,@optvalue = 'TRUE'

EXTRACT - ZIP - FTP

2010-03-16T09:50:00.000-07:00

Objective: How to extract data in csv and then FTP:

First extract the data from stored procedure or sql query using bcp, osql or sqlcmd

Then Zip it

Then finally FTP

Procedures are as follows:

SET QUOTED_IDENTIFIER OFF
go
Create Procedure P_EXTRACT_ZIP_FTP
as
Begin
DECLARE @return_value INT
Declare @FilePath Varchar(MAX)
DECLARE @SQL VARCHAR(8000)
Declare @pname varchar(75)
DECLARE @filename varchar(75)
DECLARE CURSOR_NAME CURSOR for select [pname],[filename] from TABLENAME(NOLOCK)
OPEN CURSOR_NAME
FETCH NEXT FROM CURSOR_NAME INTO @pname, @filename
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @Query VARCHAR(255)
SET @Query=@pname
DECLARE @dbname VARCHAR(255)
SET @dbname='dbname'
DECLARE @Headers int
Set @Headers=999999
SET @FilePath = 'D:\Reports\foldername\send\'+rtrim(ltrim(right(convert(varchar(11),getdate()-16, 120),11)))+rtrim(ltrim(cast(@Filename as varchar(MAX))))+'.csv'
/** This code is just for examples
--SET @SQL = 'bcp "' +cast(@pname as varchar(max))+'" queryout "'+ cast(@FilePath as varchar(MAX))+'" -c -r\n -t\, -T -w -V 90 -eerr'+rtrim(ltrim(cast(@Filename as varchar(MAX))))+'.txt >> errors-'+rtrim(ltrim(cast(@Filename as varchar(MAX))))+'.txt'
--SET @SQL Sqlcmd -S Servername -E -d northwind -Q "Select * from employees" -o C:\output_file.csv -s ,
--SET @SQL = 'OSQL -E' + ' -d' + @dbname + ' -w8000 -u -s"," -h' + convert(varchar(10),@Headers) + ' -Q"set nocount on;' + @Query + '" -o' + @FilePath
SET @SQL = 'OSQL -E' + ' -d' + @dbname + ' -w8000 -u -s"," -h' + convert(varchar(10),@Headers) + ' -Q"set nocount on;' + @Query + '" -o' + @FilePath
**/
SET @SQL = 'OSQL -E' + ' -d' + @dbname + ' -w8000 -u -s, -h' + convert(varchar(10),@Headers) + ' -Q"set nocount on;' + @Query + '" -o' + @FilePath
print @SQL
EXECUTE @return_value = master..xp_cmdshell @SQL
SET QUOTED_IDENTIFIER off
DECLARE @cmd AS VARCHAR(128)
set @cmd = "master..xp_cmdshell 'D:\Reports\foldername\GZIP D:\Reports\foldername\send\*.csv'"
EXEC(@cmd)
EXEC MONITOR.dbo.[sp_FtpPutFile_ReportServer]
@FTPServer = 'server IP'
,@FTPUser = 'username'
,@FTPPWD = 'password'
,@FTPPath = '/'
,@SourcePath = 'D:\Reports\foldername\send\'
,@DestPath = 'D:\Reports\foldername\moved\'
,@SourceFile = '*.CSV.GZ'
,@workdir = 'D:\Reports\foldername\'

FETCH NEXT FROM CURSOR_NAME INTO @pname, @filename

END
CLOSE CURSOR_NAME
DEALLOCATE CURSOR_NAME
End

TABLE STRUCTURE:

create table TABLENAME
(id int identity(1,1),pname varchar(75), filename varchar(75))
go
insert into TABLENAME (pname, filename) values ('EXEC DBNAME.DBO.PROCEDURENAME','_FILENAME')

FTP MOVING PROCEDURE:

CREATE PROCEDURE [dbo].[sp_FtpPutFile_ReportServer]
(
@FTPServer varchar(128)
,@FTPUser nvarchar(128)
,@FTPPWD nvarchar(128)
,@FTPPath nvarchar(128)
,@SourcePath nvarchar(128)
,@SourceFile nvarchar(128)
,@workdir nvarchar(128)
,@DestPath nvarchar(128)
,@binaryFile bit = NULL
)
AS
BEGIN
SET NOCOUNT ON;

IF(@binaryFile IS NULL)
begin
SET @binaryFile = 1
end

DECLARE @cmd varchar(1000) -- command to execute
DECLARE @workfilename varchar(20) -- file to store ftp command batch

SET @workfilename = 'ftpcmd.txt'

-- deal with special characters for echo commands
SET @FTPServer = REPLACE(REPLACE(REPLACE(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
SET @FTPUser = REPLACE(REPLACE(REPLACE(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
SET @FTPPWD = REPLACE(REPLACE(REPLACE(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
SET @FTPPath = REPLACE(REPLACE(REPLACE(@FTPPath, '|', '^|'),'<','^<'),'>','^>')

-- insert FTP servername to @workfilename
SET @cmd = 'echo open ' + @FTPServer + ' > ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- insert FTP username to @workfilename
SET @cmd = 'echo ' + @FTPUser + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- insert FTP password to @workfilename
SET @cmd = 'echo ' + @FTPPWD + '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

IF @binaryFile = 1
begin
-- set the file transfer type to binary
SET @cmd = 'echo bin' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output
end

-- change the working directory on the remote computer
SET @cmd = 'echo cd ' + @FTPPath + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- copy all files of a type @SourceFile from @SourcePath into @FTPPath
SET @cmd = 'echo mput ' + @SourcePath + @SourceFile + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- end the FTP session with the remote computer and exit ftp
SET @cmd = 'echo quit' + ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd, no_output

-- execute the @workfilename (i - turn off interactive prompting for the mput command)
SET @cmd = 'ftp -i -s:' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd--, no_output

-- move all files of a type @SourceFile from @SourcePath into @DestPath (Y - suppress prompting to overwrite)
SET @cmd = 'move /Y ' + @SourcePath + @SourceFile + ' ' + @DestPath
EXEC master..xp_cmdshell @cmd, no_output

END

Excel Functions

2010-03-12T05:18:00.000-08:00

Objective: This article gives some day to day usage of excel functions.

The below excel function will return the output in other column based on the requested excel column. In this case, A1, A2 are the requested column information as mentioned below.

=If(A1>10, "Larger", "Smaller") would return "Larger".

=If(A1=20, "Equal", "Not Equal") would return "Not Equal".

=If(A2="Tech on the Net", 12, 0) would return 12.

Reference: http://www.techonthenet.com/excel/formulas/if.php

Make database to be read only

2010-02-24T08:38:00.000-08:00

How can I make database to be read only?
Answer:
To make database to be read only, you can use the Enterprise Manager or the sp_dboption system stored procedure.For example, to make the pubs database to be read only, you can use the following statement:
Scripts:
EXEC sp_dboption 'pubs', 'read only', 'TRUE'
To make it online again, follow the below script
EXEC sp_dboption 'pubs', 'read only', 'FALSE'

Encryption and Decryption of SQL Server Data

2010-02-23T07:47:00.000-08:00

Subject: Built in Encryption in SQL Server 2005:

In SQL Server 2005, T-SQL support for symmetric encryption and asymmetric encryption using keys, certificates and passwords. This article describes how to create, manage and use symmetric keys and certificates.

Because of the amount of information involved, I've divided this article into three sections:

Service Master Key

Each SQL Server 2005 installation has exactly one Service Master Key (SMK), which is generated at install time. The SMK directly or indirectly secures all other keys on the server, making it the "mother of all SQL Server encryption keys." The Windows Data Protection API (DPAPI), at the higher O/S level, uses the SQL Server service account credentials to automatically encrypt and secure the SMK.

Because it is automatically created and managed by the server, Service Master Keys require only a few administrative tools. The SMK can be backed up via the BACKUP SERVICE MASTER KEY T-SQL statement. This statement has the following format:

BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'

Should you ever need to restore the Service Master Key from the backup copy, you can use the RESTORE SERVICE MASTER KEY statement:

RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD = 'password' [FORCE]

Database Master Keys

While each SQL Server has a single Service Master Key, each SQL database can have its own Database Master Key (DMK). The DMK is created using the CREATE MASTER KEY statement:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

This statement creates the DMK, encrypts it using the supplied password, and stores it in the database. In addition, the DMK is encrypted using the Service Master Key and stored in the master database; a feature known as "automatic key management." We'll talk more about this feature later.

Like the Service Master Key, you can backup and restore Database Master Keys. To backup a DMK, use the BACKUP MASTER KEY statement. The syntax is analogous to backing up a Service Master Key.

BACKUP MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'

Restoring the Database Master Key requires that you use the DECRYPTION BY PASSWORD clause, which specifies the password previously used to encrypt the backup file. In addition you must use the ENCRYPTION BY PASSWORD clause, which gives SQL Server a password to encrypt the DMK after it is loaded in the database.

RESTORE MASTER KEY FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD = 'password'
ENCRYPTION BY PASSWORD = 'password'
[ FORCE ]

To drop a DMK, use the DROP MASTER KEY statement:

DROP MASTER KEY

Part 2: Certificates
he following sample script creates a Database Master Key, a test certificate and demonstrates how to encrypt/decrypt
data using the certificate.

-- Sample T-SQL Script to demonstrate Certificate Encryption

-- Use the AdventureWorks database
USE AdventureWorks;

-- Create a Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

-- Create a Temp Table
CREATE TABLE Person.#Temp
(ContactID   INT PRIMARY KEY,
FirstName   NVARCHAR(200),
MiddleName  NVARCHAR(200),
LastName    NVARCHAR(200),
eFirstName  VARBINARY(200),
eMiddleName VARBINARY(200),
eLastName   VARBINARY(200));

-- Create a Test Certificate, encrypted by the DMK
CREATE CERTIFICATE TestCertificate
  WITH SUBJECT = 'Adventureworks Test Certificate',
  EXPIRY_DATE = '10/31/2009';

-- EncryptByCert demonstration encrypts 100 names from the Person.Contact table
INSERT
INTO Person.#Temp (ContactID, eFirstName, eMiddleName, eLastName)
SELECT ContactID, EncryptByCert(Cert_ID('TestCertificate'), FirstName),
 EncryptByCert(Cert_ID('TestCertificate'), MiddleName),
 EncryptByCert(Cert_ID('TestCertificate'), LastName)
FROM Person.Contact
WHERE ContactID <= 100;

-- DecryptByCert demonstration decrypts the previously encrypted data
UPDATE Person.#Temp
SET FirstName = DecryptByCert(Cert_ID('TestCertificate'), eFirstName),
 MiddleName = DecryptByCert(Cert_ID('TestCertificate'), eMiddleName),
 LastName = DecryptByCert(Cert_ID('TestCertificate'), eLastName);

-- View the results
SELECT *
FROM Person.#Temp;

-- Clean up work:  drop temp table, test certificate and master key
DROP TABLE Person.#Temp;
DROP CERTIFICATE TestCertificate;
DROP MASTER KEY;

Part 3: Symmetric Keys

Here is a sample T-SQL script demonstrating encryption and decryption by symmetric key:

-- Use the AdventureWorks database
USE AdventureWorks;

-- Create a Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

-- Create a Temp Table
CREATE TABLE Person.#Temp
(ContactID   INT PRIMARY KEY,
FirstName   NVARCHAR(200),
MiddleName  NVARCHAR(200),
LastName    NVARCHAR(200),
eFirstName  VARBINARY(200),
eMiddleName VARBINARY(200),
eLastName   VARBINARY(200));

-- Create a Test Certificate
CREATE CERTIFICATE TestCertificate
  WITH SUBJECT = 'Adventureworks Test Certificate',
  EXPIRY_DATE = '10/31/2009';

-- Create a Symmetric Key
CREATE SYMMETRIC KEY TestSymmetricKey
 WITH ALGORITHM = TRIPLE_DES
   ENCRYPTION BY CERTIFICATE TestCertificate;
OPEN SYMMETRIC KEY TestSymmetricKey
  DECRYPTION BY CERTIFICATE TestCertificate;

-- EncryptByKey demonstration encrypts 100 names from the Person.Contact table
INSERT
INTO Person.#Temp (ContactID, eFirstName, eMiddleName, eLastName)
SELECT ContactID,
 EncryptByKey(Key_GUID('TestSymmetricKey'), FirstName),
 EncryptByKey(Key_GUID('TestSymmetricKey'), MiddleName),
 EncryptByKey(Key_GUID('TestSymmetricKey'), LastName)
FROM Person.Contact
WHERE ContactID <= 100;

-- DecryptByKey demonstration decrypts the previously encrypted data
UPDATE Person.#Temp
SET FirstName = DecryptByKey(eFirstName),
 MiddleName = DecryptByKey(eMiddleName),
 LastName = DecryptByKey(eLastName);

-- View the results
SELECT *
FROM Person.#Temp;

-- Clean up work:  drop temp table, symmetric key, test certificate and master key
DROP TABLE Person.#Temp;
CLOSE SYMMETRIC KEY TestSymmetricKey;
DROP SYMMETRIC KEY TestSymmetricKey;
DROP CERTIFICATE TestCertificate;
DROP MASTER KEY;

Replication Subscription Reinitialise from Backup

2010-02-19T01:55:00.000-08:00

Objective:

This simple stored procedure generates the initialise from backup script text

Procedure:

use monitoringdb
go
Create procedure spGenerateInitialzeFromBackup
@publicationName nvarchar (50),
@destinationdb nvarchar (50)
as
/*
This simple stored procedure generates the initialise from backup script text,

*/
declare @sql nvarchar (4000)
select @sql = '
EXEC SP_CHANGEPUBLICATION @PUBLICATION = '''+@publicationName+''', --publication name
@PROPERTY = ''allow_initialize_from_backup'', --property
@value = ''true'' --set the value to true
go

exec sp_addsubscription
@publication = '''+@publicationName+''',
@article = ''All'',
@subscriber = ''ServerName'',
@destination_db = '''+@destinationdb+''',
@sync_type = ''initialize with backup'',
@backupdevicetype = ''disk'',
@backupdevicename = ''F:\backup\dbnameInitialize.bak''
go
--Then add the push subscription agent to synchronise a push subscription
exec sp_addpushsubscription_agent @publication = '''+@publicationName+''',
@subscriber = ''ServerName'',
@subscriber_db = '''+@destinationdb+''',
@subscriber_security_mode = 0,
@subscriber_login = ''replicationuser'',
@subscriber_password = ''hereisthepassword''
go'

print @Sql

Reference: http://msdn.microsoft.com/en-us/library/ms147897.aspx

Charindex to get Specific Domain Name from Email Address

2010-02-04T02:04:00.001-08:00

Objective: Get the specific domain name from emailaddress column in table and group it. Also to know the distinct domain name:
Below is the SQL Query to extract the domain name from emailaddress column.
select substring(emailaddress,charindex('@',emailaddress)+1 ,len(emailaddress)) as [Emaildomain], count(*) as Counting from Tablename(nolock)
where datediff(mm,date,getdate())=1
group by substring(emailaddress,charindex('@',emailaddress)+1 ,len(emailaddress))
order by counting desc
go
select distinct(substring(emailaddress,charindex('@',emailaddress)+1 ,len(emailaddress))) as [Emaildomain] from Tablename(nolock) where datediff(mm,date,getdate())=1

Exchange ActiveSync and Outlook Mobile Access

2009-12-18T06:11:00.000-08:00

Objective: To send and receive email from Exchange server to Mobile - Configuration
Exchange ActiveSync and Outlook Mobile Access errors occur when SSL or forms-based authentication is required for Exchange Server 2003
Please refer the below website:
http://support.microsoft.com/kb/817379

Index On View

2009-12-18T02:44:00.000-08:00

Objective: To create index on View

Follow the steps to create index on view. Make sure you always use schemabinding for the view in order to create index.

Create table T_TableName (ID int identity (1,1),ColumnName varchar(50),Telephone Varchar(50))
GO
Create view V_T_TableName with schemabinding
as
Select ID,Columnname,Telephone from dbo.T_TableName(nolock)
GO
Create unique clustered index IX_ID_T_TableName on v_register (id)
GO
Create index IX_Telephone_T_TableName on T_TableName (Telephone) with (online=on)

User Permissions Script from Database

2009-12-16T08:46:00.001-08:00

Objective: To see specific user permissions with script:
Explanations:
Once created the below procedure in the database, you can view the permissions script by executing the script like below:
Eg:
exec loginscript_Database 'username'
SQL Procedures Script:
Create procedure loginscript_Database (@username varchar(70))
as
begin
DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName = @username
SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)
SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
@username + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '--Security creation script for user ' + @ServerUserName + CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +
'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END
end

Create and Drop Constraints (Primary Key and Foreign Key Constraints)

2009-12-16T04:17:00.000-08:00

Objective: Create and Drop Constraints (Primary Key and Foreign Key Constraints)
sp_pkeys -- To see primary key
GO
sp_fkeys -- To see foriegn key
USE [DatabaseName]
GO
ALTER TABLE [dbo].[TableName] WITH NOCHECK ADD CONSTRAINT [ForiegnKeyConstraintName] FOREIGN KEY([AffiliateID])
REFERENCES [dbo].[ReferencedTableName] ([ColumnName])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[TableName] CHECK CONSTRAINT [ForiegnKeyConstraintName]
GO
USE [DatabaseName]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[ForiegnKeyConstraintName]') AND parent_object_id = OBJECT_ID(N'[dbo].[TableName]'))
ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [ForiegnKeyConstraintName]

IO Reads and Writes in SQL Server

2009-12-09T07:39:00.000-08:00

Objective: To find IO reads and writes from SQL Server 2005
select *
from sys.dm_io_virtual_file_stats(NULL, NULL)
/*--what files are taking up the most IO*/
select db_name(mf.database_id)as database_name,mf.physical_name,left(mf.physical_name, 1) as drive_letter,
vfs.num_of_writes,vfs.num_of_bytes_written,vfs.io_stall_write_ms,mf.type_desc, vfs.num_of_reads,vfs.num_of_bytes_read,
vfs.io_stall_read_ms,vfs.io_stall,vfs.size_on_disk_bytes
from sys.master_files mf
join sys.dm_io_virtual_file_stats(NULL,NULL)vfs
on mf.database_id = vfs.database_id and mf.file_id=vfs.file_id
order by vfs.num_of_bytes_written desc

Public DNS or Open DNS

2009-12-04T04:06:00.000-08:00

http://code.google.com/speed/public-dns/
What is Google Public DNS?
Google Public DNS is a free, global Domain Name System (DNS) resolution service, that you can use as an alternative to your current DNS provider.
To try it out:
Configure your network settings to use the IP addresses 8.8.8.8 and 8.8.4.4 as your DNS servers or Read our configuration instructions.If you decide to try Google Public DNS, your client programs will perform all DNS lookups using Google Public DNS.

Why does DNS matter?
The DNS protocol is an important part of the web's infrastructure, serving as the Internet's phone book: every time you visit a website, your computer performs a DNS lookup. Complex pages often require multiple DNS lookups before they start loading, so your computer may be performing hundreds of lookups a day.

Why should you try Google Public DNS? By using Google Public DNS you can:
Speed up your browsing experience. Improve your security. Get the results you expect with absolutely no redirection.
Or
http://www.opendns.com/ provides the below IP's
208.67.222.222
208.67.202.202

Email Validation Function

2009-12-03T10:04:00.000-08:00

Email Validation Function:
CREATE FUNCTION dbo.IsEmailValid (@Email varchar (100))
RETURNS BIT
AS
BEGIN
/*
One instance of @, multiple dots, atleast one after @
no space
after the last dot minimum 2 chars, max 3 chars
some chars between @ and .
No Special Chars
*/
DECLARE @atpos int, @dotpos int
SET @Email = LTRIM(RTRIM(@Email)) -- remove leading and trailing blanks
IF LEN(@Email) = 0 RETURN(0) -- nothing to validate
SET @atpos = charindex('@',@Email) -- position of first (hopefully only) @
IF @atpos <= 1 OR @atpos = LEN(@Email) RETURN(0) -- @ is neither 1st or last or missing
IF CHARINDEX('@', @email, @atpos+1) > 0 RETURN(0) -- Two @s are illegal
IF CHARINDEX(' ',@Email) > 0 RETURN(0) -- Embedded blanks are illegal
SET @dotpos = CHARINDEX('.',Reverse(@Email)) -- location (from rear) of last dot
IF (@dotpos <> 4) or (LEN(@Email) - @dotpos) < @atpos RETURN (0) -- dot / 2 or 3 char, after @
if (@atpos + @dotpos = len(@email)) Return (0) -- Nothing between @ and .
-- Special Characters Scan
Declare @emaillen int, @currpos int, @currascii int
SET @emaillen = LEN(@email)
SET @currpos = 0
WHILE @emaillen != @currpos
BEGIN
SET @currpos = @currpos + 1
SET @currascii = ASCII(substring(@email, @currpos, 1))
if @currascii <= 44 Return (0)
if @currascii = 47 Return (0)
if @currascii >= 58 and @currascii <= 63 Return (0)
if @currascii >= 91 and @currascii <= 94 Return (0)
if @currascii = 96 Return (0)
if @currascii >= 123 Return (0)
END
RETURN(1) -- Hope this is a valid email
END

Database Size Report

2009-12-03T10:00:00.000-08:00

Database Size Reporting:
Option 1:
Exec sp_databases
Option 2:
Exec sp_helpdb
Option 3:
This below procedure also gives information about each databases:
create procedure spReportDatabaseSizes as /* Purpose: Stored procedure to give at a glance information of database size,free space, file locations and file sizes */ DECLARE @DBInfo TABLE ( ServerName VARCHAR(100), DatabaseName VARCHAR(100), FileSizeMB INT, LogicalFileName sysname, PhysicalFileName NVARCHAR(520), Status sysname, Updateability sysname, RecoveryMode sysname, FreeSpaceMB INT, FreeSpacePct VARCHAR(7), FreeSpacePages INT, PollDate datetime) DECLARE @command VARCHAR(5000) SELECT @command = 'Use [' + '?' + '] SELECT @@servername as ServerName, ' + '''' + '?' + '''' + ' AS DatabaseName, CAST(sysfiles.size/128.0 AS int) AS FileSize, sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName, CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status, CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability, CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode, CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB, CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct, GETDATE() as PollDate FROM dbo.sysfiles' INSERT INTO @DBInfo (ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, PollDate) EXEC sp_MSForEachDB @command SELECT ServerName, DatabaseName, LogicalFileName, PhysicalFileName, FileSizeMB, FreeSpaceMB, FreeSpacePct, Status, Updateability, RecoveryMode, PollDate as DateInfoGenerated FROM @DBInfo ORDER BY ServerName, DatabaseName

How to Find Worst SQL Queries by Dynamic Management View

2009-12-03T09:58:00.000-08:00

Worst SQL Queries:
Eg: exec usp_Worst_TSQL @orderby='TCPU'
CREATE PROC [dbo].[usp_Worst_TSQL]
/*
Name: usp_Worst_TSQL
Description: This stored procedure displays the top worst performing queries based on CPU, Execution Count,
I/O and Elapsed_Time as identified using DMV information. This can be display the worst
performing queries from an instance, or database perspective. The number of records shown,
the database, and the sort order are identified by passing pararmeters.
Parameters: There are three different parameters that can be passed to this procedures: @DBNAME, @COUNT
and @ORDERBY. The @DBNAME is used to constraint the output to a specific database. If
when calling this SP this parameter is set to a specific database name then only statements
that are associated with that database will be displayed. If the @DBNAME parameter is not set
then this SP will return rows associated with any database. The @COUNT parameter allows you
to control the number of rows returned by this SP. If this parameter is used then only the
TOP x rows, where x is equal to @COUNT will be returned, based on the @ORDERBY parameter.
The @ORDERBY parameter identifies the sort order of the rows returned in descending order.
This @ORDERBY parameters supports the following type: CPU, AE, TE, EC or AIO, TIO, ALR, TLR, ALW, TLW, APR, and TPR
where "ACPU" represents Average CPU Usage
"TCPU" represents Total CPU usage
"AE" represents Average Elapsed Time
"TE" represents Total Elapsed Time
"EC" represents Execution Count
"AIO" represents Average IOs
"TIO" represents Total IOs
"ALR" represents Average Logical Reads
"TLR" represents Total Logical Reads
"ALW" represents Average Logical Writes
"TLW" represents Total Logical Writes
"APR" represents Average Physical Reads
"TPR" represents Total Physical Read
Typical execution calls
Top 6 statements in the AdventureWorks database base on Average CPU Usage:
EXEC usp_Worst_TSQL @DBNAME='AdventureWorks',@COUNT=6,@ORDERBY='ACPU';
Top 100 statements order by Average IO
EXEC usp_Worst_TSQL @COUNT=100,@ORDERBY='ALR';
Show top all statements by Average IO
EXEC usp_Worst_TSQL;
*/
(@DBNAME VARCHAR(128) = ''
,@COUNT INT = 999999999
,@ORDERBY VARCHAR(4) = 'AIO')
AS
-- Check for valid @ORDERBY parameter
IF ((SELECT CASE WHEN
@ORDERBY in ('ACPU','TCPU','AE','TE','EC','AIO','TIO','ALR','TLR','ALW','TLW','APR','TPR')
THEN 1 ELSE 0 END) = 0)
BEGIN
-- abort if invalid @ORDERBY parameter entered
RAISERROR('@ORDERBY parameter not APCU, TCPU, AE, TE, EC, AIO, TIO, ALR, TLR, ALW, TLW, APR or TPR',11,1)
RETURN
END
SELECT TOP (@COUNT)
COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT))+'*',
'Resource') AS [Database Name]
-- find the offset of the actual statement being executed
,SUBSTRING(text,
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
CASE WHEN statement_end_offset = 0
OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END + 1
) AS [Statement]
,OBJECT_SCHEMA_NAME(st.objectid,dbid) [Schema Name]
,OBJECT_NAME(st.objectid,dbid) [Object Name]
,objtype [Cached Plan objtype]
,execution_count [Execution Count]
,(total_logical_reads + total_logical_writes + total_physical_reads )/execution_count [Average IOs]
,total_logical_reads + total_logical_writes + total_physical_reads [Total IOs]
,total_logical_reads/execution_count [Avg Logical Reads]
,total_logical_reads [Total Logical Reads]
,total_logical_writes/execution_count [Avg Logical Writes]
,total_logical_writes [Total Logical Writes]
,total_physical_reads/execution_count [Avg Physical Reads]
,total_physical_reads [Total Physical Reads]
,total_worker_time / execution_count [Avg CPU]
,total_worker_time [Total CPU]
,total_elapsed_time / execution_count [Avg Elapsed Time]
,total_elapsed_time [Total Elasped Time]
,last_execution_time [Last Execution Time]
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE attribute = 'dbid' AND
CASE when @DBNAME = '' THEN ''
ELSE COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT)) + '*',
'Resource') END
IN (RTRIM(@DBNAME),RTRIM(@DBNAME) + '*')
ORDER BY CASE
WHEN @ORDERBY = 'ACPU' THEN total_worker_time / execution_count
WHEN @ORDERBY = 'TCPU' THEN total_worker_time
WHEN @ORDERBY = 'AE' THEN total_elapsed_time / execution_count
WHEN @ORDERBY = 'TE' THEN total_elapsed_time
WHEN @ORDERBY = 'EC' THEN execution_count
WHEN @ORDERBY = 'AIO' THEN (total_logical_reads + total_logical_writes + total_physical_reads) / execution_count
WHEN @ORDERBY = 'TIO' THEN total_logical_reads + total_logical_writes + total_physical_reads
WHEN @ORDERBY = 'ALR' THEN total_logical_reads / execution_count
WHEN @ORDERBY = 'TLR' THEN total_logical_reads
WHEN @ORDERBY = 'ALW' THEN total_logical_writes / execution_count
WHEN @ORDERBY = 'TLW' THEN total_logical_writes
WHEN @ORDERBY = 'APR' THEN total_physical_reads / execution_count
WHEN @ORDERBY = 'TPR' THEN total_physical_reads
END DESC

Top 100 Queries from particular instance of server

2009-12-03T09:56:00.000-08:00

Top 100 Queries from particular instance of server:
create procedure usp_top100_queries as select top 100 a.total_worker_time,b.text from sys.dm_exec_query_stats a cross apply sys.dm_exec_sql_text(a.plan_handle) as b order by 1 desc

Replication Troubleshooting Information

2009-12-03T09:54:00.000-08:00

Replication Troubleshoot Information Table:

http://vyaskn.tripod.com/repl_ans.htm

use distribution
go
select * from MSlogreader_history order by agent_id desc
select * from MSsnapshot_history order by agent_id desc
select * from MSdistribution_history order by agent_id desc
select * from MSmerge_history

XML File Read through OPENROWSET

2009-12-03T09:39:00.000-08:00

OBJECTIVE: To read a xml file in sql server 2005

FILE CONTENT STARTED:

- 2009-11-25 04:26:00 - 18B15GD022 Mobile Broadband ZTE MF627 USB Modem Broadband (15GB) - 18 months contract from 3 Mobile Mobile Phone Contract n/a n/a ZTE MF627 USB Modem Mobile Phones Contract 372 Broadband (15GB) - 15 GB of data allowance every month. Get Broadband Anywhere with Speeds upto 3.6Mbps http://3mobileshop.at/adsvine/18B15GD022.html http://3mobileshop.at/adsvine?CTY=9&DURL=http://threestore.three.co.uk/DealSummary.aspx?tariffid=1239&offerCode=18B15GD022&id=1183 http://threestore.three.co.uk/images/mixnmatch/ZTE-modem(38x80).jpg GBP 1 in stock Broadband (15GB) - With 0 included minutes and 0 texts; 18 months mobile broadband contract from 3 Mobile. Plus Contract Length; Get Broadband Anywhere with Speeds upto 3.6Mbps Broadband (15GB) - 15 GB of data allowance every month. Get Broadband Anywh ZTE MF627 USB Modem 0.00 0 20.00 3 Broadband (15GB)

FILE CONTENT FINISHED:

DECLARE @xml as XML
SELECT @xml=CONVERT(xml, BulkColumn, 2)
FROM
OPENROWSET(Bulk 'D:\DailyScripts\xml\Mobiles.xml', SINGLE_BLOB) [rowsetresults]
SELECT
cast(T.Item.query('../last_updated/text()') as varchar(20)) lastUpdate,
cast(T.Item.query('product_code/text()') as varchar(20)) Product_Code ,
cast(T.Item.query('product_name/text()') as nvarchar(100)) Product_Name
FROM @xml.nodes('/products/product') AS T(Item)

Table Size Information

2009-12-03T06:16:00.000-08:00

Objecive: To find table size information for particular database
USE DBNAME
go
SET NOCOUNT ON
/*DATABASE TABLE SPY SCRIPT
DESCRIPTION
Returns TABLE Size Information
SORTING USAGE
@Sort bit VALUES
0 = Alphabetically BY TABLE name
1 = Sorted BY total space used by TABLE
*/
DECLARE @cmdstr varchar(100)
DECLARE @Sort bit
SELECT @Sort = 1 /* Edit this value FOR sorting options */
/* DO NOT EDIT ANY CODE BELOW THIS LINE */
--- =--Create temporary table
CREATE TABLE #TempTable
( [Table_Name] varchar(150),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
--- =--Create Stored Procedure String
SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
--Populate Tempoary table
INSERT INTO #TempTable EXEC(@cmdstr)
--Determine sorting method
IF @Sort = 0
BEGIN
--Retrieve Table Data and Sort Alphabetically
SELECT * FROM #TempTable ORDER BY Table_Name
END
ELSE
BEGIN
/*Retrieve TABLE Data AND Sort BY the size OF the Table*/
SELECT *, cast(replace(table_size, 'kb', '') as int) ts FROM #TempTable ORDER BY ts DESC
END
DROP TABLE #TempTable

How to Read XML file

2009-11-26T06:40:00.000-08:00

Objective: To read xml in sql server.
Assume file is stored in D:\DailyScripts\xml\customer.xml
File Name: Customer.xml
Reference: http://msdn.microsoft.com/en-us/library/ms186918.aspx
Eg: http://blog.sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql/
File Start:

File End

DECLARE @FileName varchar(255)
DECLARE @ExecCmd VARCHAR(255)
DECLARE @y INT
DECLARE @x INT
DECLARE @FileContents VARCHAR(8000)
CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))
SET @FileName = 'D:\DailyScripts\xml\customer.xml'
SET @ExecCmd = 'type ' + @FileName
SET @FileContents = ''
INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
SELECT @y = count(*) from #tempXML
SET @x = 0
WHILE @x <> @y
BEGIN
SET @x = @x + 1
SELECT @FileContents = @FileContents + ThisLine from #tempXML WHERE PK = @x
END
SELECT @FileContents as FileContents
DROP TABLE #tempXML
DECLARE @idoc int
DECLARE @doc varchar(max)
SET @doc =@FileContents
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')

SQL Server Service Pack Version Finder

2009-11-26T04:10:00.000-08:00

Objective: To find out the SQL server Service Pack Version
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
go
select @@version

Way to find Blocking Information - SQL Server

2009-11-26T02:58:00.000-08:00

Objective: Below script is to find the blocking information in sql server
Declare @wTable Table (spid int, blocked int, loginame varchar(100), dbName varchar(100), last_batch dateTime)
Insert into @wTable
select spid, blocked, loginame, db_name(dbid), last_batch from sys.sysprocesses (nolock)--where blocked <> 0
select spid,blocked,loginame,dbName,last_batch from @wtable where blocked <> 0
Union all
select spid,blocked,loginame,dbName,last_batch from @wtable where spId in(select blocked from @wtable where blocked <> 0)
order by blocked

Server Roles - SQL Server 2005

2009-11-26T02:42:00.000-08:00

Script for Server Roles in SQL Server 2005:
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'bulkadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'dbcreator'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'diskadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'processadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'securityadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'serveradmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'setupadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'username', @rolename = N'sysadmin'
GO

Installing Oracle Database 11g on Windows

2009-11-14T14:01:00.000-08:00

Download Website Link:
http://www.oracle.com/technology/software/products/database/index.html
Installation instructions:
http://www.oracle.com/technology/obe/11gr1_db/install/dbinst/windbinst2.htm#i

Rapidshare Download Tools

2009-11-10T04:26:00.001-08:00

Rapidshare Download Tools:

This is the best website to have some idea for rapidshare download tools.

http://www.megaleecher.net/Rapidshare_Plus

Clean Buffers and Free Cache

2009-11-09T09:09:00.000-08:00

DBCC DROPCLEANBUFFERS: Use this command to remove all the data from SQL Server’s data cache (buffer) between performance tests to ensure fair testing. Keep in mind that this command only removes clean buffers, not dirty buffers. Because of this, before running the DBCC DROPCLEANBUFFERS command, you may first want to run the CHECKPOINT command first. Running CHECKPOINT will write all dirty buffers to disk. And then when you run DBCC DROPCLEANBUFFERS, you can be assured that all data buffers are cleaned out, not just the clean ones.

DBCC FREEPROCCACHE: Used to clear out the stored procedure cache for all SQL Server databases. You may want to use this command before testing to ensure that previous stored procedure plans won’t negatively affect testing results.

The cache can be cleared with the following script:
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DBCC FREEPROCCACHE;
GO

Way to find the database table that is accessed the most often?

2009-11-05T07:37:00.000-08:00

A way to find the database table that is accessed the most often?

You can query the V$SEGMENT_STATISTICS view for this information. Look for tables that have a high number of physical reads and logical reads.

The below query will give the following might provide what you are looking for:

SELECT t.owner,t.table_name,lr.value+pr.value AS total_reads
FROM (SELECT owner,object_name,value FROM v$segment_statistics
WHERE statistic_name='logical reads') lr,
SELECT owner,object_name,value FROM v$segment_statistics
WHERE statistic_name='logical reads') pr,
dba_tables t
WHERE lr.owner=pr.owner AND lr.object_name=pr.object_name
AND lr.owner=t.owner AND lr.object_name=t.table_name
ORDER BY 3;

Putting Database in to a single user mode

2009-10-30T02:30:00.000-07:00

Objective: Bring the database to single user mode to do some maintenance.

You can use this sp_dboption in previous version like SQL 2000 and earlier.

sp_dboption 'dbname', 'single user', true

You can use this Alter syntax for versions like SQL 2005 and later

ALTER DATABASE DBNAME SET SINGLE_USER

ALTER DATABASE DBNAME SET SINGLE_USER with no_wait

ALTER DATABASE DBNAME SET SINGLE_USER with rollback after 5

ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATELY

GUI mode:

Also you can do from Database GUI mode. Highlight database and righ click to get the Properties - Options - State - There you will find option.

-----------

Drop database connections:

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'test'
--SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)
SET @SQL = ''

SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

SELECT @SQL
EXEC(@SQL)
-------------------OR--------------------------------
DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'test'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

SELECT @SQL
EXEC(@SQL)

------------------------------

How to Alter a SQL Server Database as Single User Mode and as Multi User Mode

You can use the SQL Server Enterprise Manager for SQL Server 2000 to set a database to a single user mode or to multi user mode. Similarly, SQL Server Management Studio can be used for SQL Server 2005 for changing a database to single user mode or to multi user mode. Also you can alter the database access mode by using sql commands like ALTER DATABASE and sp_dboption.

ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [Works] SET SINGLE_USER WITH NO_WAIT

or

EXEC sp_dboption 'Works', 'single user', 'false'

EXEC sp_dboption 'Works', 'single user', 'true'

Table to Insert Incremental Values

2009-10-22T09:40:00.000-07:00

Objective: To populate multiple incremental values in table.

Create Table test (val float)
insert into test values (0.001)
declare @t float
select @t = max(val) from test
Insert into test
select val + @t from test

Or
DECLARE @idt float
SET @idt = 0
WHILE (@idt < 10)
BEGIN
SELECT @idt = @idt + 0.001
insert into tablename (columnname)
select @idt
END

or

DECLARE @idt int
SET @idt = 0
WHILE (@idt < 100)
BEGIN
SELECT @idt = @idt + 1
insert into tablename (columnname)
select @idt
END

Export Import Tool - Microsoft VSS to Vault Source Gear

2009-09-22T09:12:00.001-07:00

Objective: To import all files from Microsoft to Source Gear

Install tools from source gear website

http://www.sourcegear.com/vault/downloads2.html

Installation will carry forward like below from VSS Import Tool

Give the path where the file comes from. For example the network path from microsoft Visual source safe and their credentials

The below screen shot is vault admin username and password

click on Continue when the error raises something like below:

once examining the project, all microsoft VSS will be imported to Source Gear.

Vault Source Gear Installation

2009-09-22T09:02:00.000-07:00

Objective: To install source gear in windows 2003 server

http://www.sourcegear.com/vault/support/install/

Source Gear File for installing ver 4

Once installed, you can access through Program Files from Windows OS

The below screen shot is the admin page where you can manage everything (For eg. Add users, import, export etc)

SQL Agent Jobs - Permission

2009-09-14T09:07:00.001-07:00

SQLAgentReaderRole

The SQLAgentReaderRole is a database role located in the msdb database. It is one of three new roles in this database aimed at allowing the database administrator the ability to assign more granular permissions when it comes to the administration of SQL Agent jobs. Assigning a user or group to be a member of this role allows the user to see any SQL Agent job located on the server instance, even the jobs in which that user does not own. The user can see the job, along with any history saved to the job. However, the group is not allowed to execute the jobs. To add a user as a member of the SQLAgentReaderRole, you can execute the following command:

use msdb

EXECUTE sp_addrolemember

@rolename = 'SQLAgentReaderRole',

@membername = 'username'

It is also worth mentioning the other two SQL Agent roles available in SQL Server 2005. The SQLAgentUserRole allows users to create jobs and to manage the jobs that they create. The SQLAgentOperatorRole allows users all of the rights assigned to the SQLAgentReaderRole along with the permissions necessary to execute local jobs that they do not own.

Further you can view some more details in this below blog:

http://blogs.techrepublic.com.com/datacenter/?p=428

Route Add

2009-09-10T08:10:00.000-07:00

Static Route Add in the server:

If you cannot ping or net use, then you may manually add the route in the server where you are targeting

route ADD 10.1.1.0 MASK 255.255.255.0 192.168.1.8

10.1.1.0 or 10.1.1.12 - Destination IP or Destination Subnet or Target Server IP

255.255.255.0 - Target or Destination Server subnet mask

192.168.1.8 - Gateway IP Address - Target Server Gateway

Adding a TCP/IP Route to the Windows Routing Table

Adding routes to your machine is a useful testing tool for some of these situations.

Or another explanation is:

Syntax:

route ADD “network” MASK “subnet mask” “gateway ip”

For example, if you were on the 192.168.1.0 network, and you had a gateway on 192.168.1.12 configured to access the 10.10.10.0/24 network, you would use a route add statement like this:

route ADD 10.10.10.0 MASK 255.255.255.0 192.168.1.12

Your routing table should now reflect that change, and all traffic to the 10.10.10.x range will now be sent over to the gateway machine.

The route add change will only stick across reboots if you add it with the -p flag, as in the following:

route -p ADD 10.10.10.0 MASK 255.255.255.0 192.168.1.12
route -p add 10.0.1.0 mask 255.255.255.0 10.0.0.2

Please see below link as well

http://www.mydigitallife.info/2008/12/25/how-to-add-route-to-tcpip-routing-table-with-windows-routing-and-remote-access-console-or-dos-prompt/

How to fix orphaned SQL Server users

2009-09-10T06:42:00.000-07:00

--------------------------------------------------------------------------------

Brief:

When you restore a MS SQL Server database on a different machine, you cannot access the database until you fix the permissions.

--------------------------------------------------------------------------------

Detail

The problem is that the user in the database is an "orphan". This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a GUID (called a SID in Microsoft-speak) that has to match as well.

All of these should be done as a database admin, with the restored database selected.

Firstly, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

SQL Tips - Available Sites

2009-08-19T01:55:00.000-07:00

Objective: Updating information for the users to get some benefits from other site for SQL Tips

Please visit the following sites:

http://dotnet-explorer.blogspot.com

IIS 7 Installation and Configuration for SQL Server 2005 Installation

2009-08-14T15:45:00.000-07:00

IIS 7 Installation Prior to SQL Server 2005 installation:

1. You can install IIS in Windows Vista by following the below steps

2. Go to Control Panel and Click Turn Windows Features on or off to install relevant IIS Componenets as discussed below.

-Internet Information Services - -Web Management Tools - - -IIS 6 Management Compatibility - - - -IIS 6 WMI Compatibility - - - -IIS Metabase and IIS 6 configuration compatibility - -World WIde Web Services - - -Application Development Features - - - -ASP.NET - - - -ISAPI Extensions - - - -ISAPI Filters - - -Common Http Features - - - -Default Document - - - -Directory Browsing - - - -HTTP Redirection - - - -Static Content - - -Security - - - -Windows Authentication

AS soon as the above installed, you should be able to install SQL Server 2005 with Reporting Services while installing SQL Server 2005 Developer or Enterprise Edition.

Who is Blocking Your Database - Simple Query

2009-08-11T06:12:00.000-07:00

Simple Script to get Blocking Info in SQL Server Database:

select * from

(select spID, blocked, program_name, loginame from sys.sysprocesses (nolock) where blocked <> 0) locks,

(select spID, blocked, program_name, loginame from sys.sysprocesses (nolock)) allprocess

where locks.blocked = allprocess.spId

Also you may refer Microsoft articles for the same

http://support.microsoft.com/kb/271509

The above articles also provides detailed blocking details:

Procedure run to check:

WHILE 1=1

BEGIN

EXEC tempdb.dbo.sp_blocker_pss08

-- Or for fast mode

-- EXEC master.dbo.sp_blocker_pss08 @fast=1

-- Or for latch mode

-- EXEC master.dbo.sp_blocker_pss08 @latch=1

WAITFOR DELAY '00:00:15'

END

Script to create the procedure from the above Microsoft website is:

Note: This script is meant to have 3 creations of the same stored procedure and two of them will fail

with either 207 errors or a 2714 error.

use tempdb

if exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss08') and sysstat & 0xf = 4)

drop procedure dbo.sp_blocker_pss08

create procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')

--version 19.2005 - 2005 or Later

if is_member('sysadmin')=0

begin

print 'Must be a member of the sysadmin group in order to run this procedure'

return

end

set nocount on

SET LANGUAGE 'us_english'

declare @spid varchar(6)

declare @blocked varchar(6)

declare @time datetime

declare @time2 datetime

declare @dbname nvarchar(128)

declare @status sql_variant

declare @useraccess sql_variant

declare @request varchar(12)

set @time = getdate()

declare @probclients table(spid smallint, request_id int, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,

ignore_app tinyint, primary key (blocked, spid, request_id, ecid))

insert @probclients select spid, request_id, ecid, blocked, waittype, dbid,

case when convert(varchar(128),hostname) = @appname then 1 else 0 end

from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1)

begin

set @time2 = getdate()

print ''

print '9.0 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.2005 '+ltrim(str(@latch))+' '+ltrim(str(@fast))

insert @probclients select distinct blocked, 0, 0, 0, 0x0000, 0, 0 from @probclients

where blocked not in (select spid from @probclients) and blocked != 0

if (@fast = 1)

begin

print ''

print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

select spid, status, blocked, open_tran, waitresource, waittype,

waittime, cmd, lastwaittype, cpu, physical_io,

memusage, last_batch=convert(varchar(26), last_batch,121),

login_time=convert(varchar(26), login_time,121),net_address,

net_library, dbid, ecid, kpid, hostname, hostprocess,

loginame, program_name, nt_domain, nt_username, uid, sid,

sql_handle, stmt_start, stmt_end, request_id

from master.dbo.sysprocesses

where blocked!=0 or waittype != 0x0000

or spid in (select blocked from @probclients where blocked != 0)

or spid in (select spid from @probclients where blocked != 0)

print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))

print ''

print 'SYSPROC FIRST PASS'

select spid, request_id, ecid, waittype from @probclients where waittype != 0x0000

if exists(select blocked from @probclients where blocked != 0)

begin

print 'Blocking via locks at ' + convert(varchar(26), @time, 121)

print ''

print 'SPIDs at the head of blocking chains'

select distinct spid from @probclients -- change: added distinct

where blocked = 0 and spid in (select blocked from @probclients where spid != 0)

if @latch = 0 and exists (select spid from @probclients where waittype between 0x0001 and 0x0017) -- Change: exists

begin

print 'SYSLOCKINFO'

select @time2 = getdate()

select spid = convert (smallint, req_spid),

ecid = convert (smallint, req_ecid),

rsc_dbid As dbid,

rsc_objid As ObjId,

rsc_indid As IndId,

Type = case rsc_type when 1 then 'NUL'

when 2 then 'DB'

when 3 then 'FIL'

when 4 then 'IDX'

when 5 then 'TAB'

when 6 then 'PAG'

when 7 then 'KEY'

when 8 then 'EXT'

when 9 then 'RID'

when 10 then 'APP' end,

Resource = substring (rsc_text, 1, 16),

Mode = case req_mode + 1 when 1 then NULL

when 2 then 'Sch-S'

when 3 then 'Sch-M'

when 4 then 'S'

when 5 then 'U'

when 6 then 'X'

when 7 then 'IS'

when 8 then 'IU'

when 9 then 'IX'

when 10 then 'SIU'

when 11 then 'SIX'

when 12 then 'UIX'

when 13 then 'BU'

when 14 then 'RangeS-S'

when 15 then 'RangeS-U'

when 16 then 'RangeIn-Null'

when 17 then 'RangeIn-S'

when 18 then 'RangeIn-U'

when 19 then 'RangeIn-X'

when 20 then 'RangeX-S'

when 21 then 'RangeX-U'

when 22 then 'RangeX-X'end,

Status = case req_status when 1 then 'GRANT'

when 2 then 'CNVT'

when 3 then 'WAIT' end,

req_transactionID As TransID, req_transactionUOW As TransUOW

from master.dbo.syslockinfo s,

@probclients p

where p.spid = s.req_spid

--and ((p.waittype between 0x0001 and 0x0017) or ()) --change: added line

print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))

end -- latch not set

end

else

print 'No blocking via locks at ' + convert(varchar(26), @time, 121)

print ''

end -- fast set

else

begin -- Fast not set

print ''

print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

select spid, status, blocked, open_tran, waitresource, waittype,

waittime, cmd, lastwaittype, cpu, physical_io,

memusage, last_batch=convert(varchar(26), last_batch,121),

login_time=convert(varchar(26), login_time,121),net_address,

net_library, dbid, ecid, kpid, hostname, hostprocess,

loginame, program_name, nt_domain, nt_username, uid, sid,

sql_handle, stmt_start, stmt_end, request_id

from master.dbo.sysprocesses

print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))

print ''

print 'SYSPROC FIRST PASS'

select spid, request_id, ecid, waittype from @probclients where waittype != 0x0000

if exists(select blocked from @probclients where blocked != 0)

begin

print 'Blocking via locks at ' + convert(varchar(26), @time, 121)

print ''

print 'SPIDs at the head of blocking chains'

select spid from @probclients

where blocked = 0 and spid in (select blocked from @probclients where spid != 0)

if @latch = 0

begin

print 'SYSLOCKINFO'

select @time2 = getdate()

select spid = convert (smallint, req_spid),

ecid = convert (smallint, req_ecid),

rsc_dbid As dbid,

rsc_objid As ObjId,

rsc_indid As IndId,

Type = case rsc_type when 1 then 'NUL'

when 2 then 'DB'

when 3 then 'FIL'

when 4 then 'IDX'

when 5 then 'TAB'

when 6 then 'PAG'

when 7 then 'KEY'

when 8 then 'EXT'

when 9 then 'RID'

when 10 then 'APP' end,

Resource = substring (rsc_text, 1, 16),

Mode = case req_mode + 1 when 1 then NULL

when 2 then 'Sch-S'

when 3 then 'Sch-M'

when 4 then 'S'

when 5 then 'U'

when 6 then 'X'

when 7 then 'IS'

when 8 then 'IU'

when 9 then 'IX'

when 10 then 'SIU'

when 11 then 'SIX'

when 12 then 'UIX'

when 13 then 'BU'

when 14 then 'RangeS-S'

when 15 then 'RangeS-U'

when 16 then 'RangeIn-Null'

when 17 then 'RangeIn-S'

when 18 then 'RangeIn-U'

when 19 then 'RangeIn-X'

when 20 then 'RangeX-S'

when 21 then 'RangeX-U'

when 22 then 'RangeX-X'end,

Status = case req_status when 1 then 'GRANT'

when 2 then 'CNVT'

when 3 then 'WAIT' end,

req_transactionID As TransID, req_transactionUOW As TransUOW

from master.dbo.syslockinfo

print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))

end -- latch not set

end

else

print 'No blocking via locks at ' + convert(varchar(26), @time, 121)

print ''

end -- Fast not set

print 'sys.dm_os_wait_stats'

select * from sys.dm_os_wait_stats where waiting_tasks_count > 0

print 'OWS'

Print ''

Print '*********************************************************************'

Print 'Print out DBCC Input buffer for all blocked or blocking spids.'

Print '*********************************************************************'

declare ibuffer cursor fast_forward for

select distinct cast (spid as varchar(6)) as spid, cast (request_id as varchar(12)) as request_id

from @probclients

where (spid <> @@spid) and (spid > 50) and

((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))

or spid in (select blocked from @probclients where blocked != 0))

open ibuffer

fetch next from ibuffer into @spid, @request

while (@@fetch_status != -1)

begin

print ''

print 'DBCC INPUTBUFFER FOR SPID ' + @spid +'('+@request+')'

exec ('dbcc inputbuffer (' + @spid + ',' + @request +')')

fetch next from ibuffer into @spid, @request

end

deallocate ibuffer

Print ''

Print '*******************************************************************************'

Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'

Print '*******************************************************************************'

declare ibuffer cursor fast_forward for

select distinct cast (dbid as varchar(6)) from @probclients

where dbid != 0

open ibuffer

fetch next from ibuffer into @spid

while (@@fetch_status != -1)

begin

print ''

set @dbname = db_name(@spid)

set @status = DATABASEPROPERTYEX(@dbname,'Status')

set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')

print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'

if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'

dbcc opentran(@dbname)

else

print 'Skipped: Status=' + convert(nvarchar(128),@status)

+ ' UserAccess=' + convert(nvarchar(128),@useraccess)

print ''

if @spid = '2' select @blocked = 'Y'

fetch next from ibuffer into @spid

end

deallocate ibuffer

if @blocked != 'Y'

begin

print ''

print 'DBCC OPENTRAN FOR DBID 2 [tempdb]'

dbcc opentran ('tempdb')

end

print 'End time: ' + convert(varchar(26), getdate(), 121)

end -- All

else

print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '

+ convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.2005'

create procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')

--version 19.20003 --2000 SP3 or Later

if is_member('sysadmin')=0

begin

print 'Must be a member of the sysadmin group in order to run this procedure'

return

end

set nocount on

SET LANGUAGE 'us_english'

declare @spid varchar(6)

declare @blocked varchar(6)

declare @time datetime

declare @time2 datetime

declare @dbname nvarchar(128)

declare @status sql_variant

declare @useraccess sql_variant

set @time = getdate()

declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,

ignore_app tinyint, primary key (blocked, spid, ecid))

insert @probclients select spid, ecid, blocked, waittype, dbid,

case when convert(varchar(128),hostname) = @appname then 1 else 0 end

from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)

begin

set @time2 = getdate()

print ''

print '8.2 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.20003 ' +ltrim(str(@latch))+' '+ltrim(str(@fast))

insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients

where blocked not in (select spid from @probclients) and blocked != 0

if (@fast = 1)

begin

print ''

print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

select spid, status, blocked, open_tran, waitresource, waittype,

waittime, cmd, lastwaittype, cpu, physical_io,

memusage, last_batch=convert(varchar(26), last_batch,121),

login_time=convert(varchar(26), login_time,121),net_address,

net_library, dbid, ecid, kpid, hostname, hostprocess,

loginame, program_name, nt_domain, nt_username, uid, sid,

sql_handle, stmt_start, stmt_end

from master.dbo.sysprocesses

where blocked!=0 or waittype != 0x0000

or spid in (select blocked from @probclients where blocked != 0)

or spid in (select spid from @probclients where blocked != 0)

print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))

print ''

print 'SYSPROC FIRST PASS'

select spid, ecid, waittype from @probclients where waittype != 0x0000

if exists(select blocked from @probclients where blocked != 0)

begin

print 'Blocking via locks at ' + convert(varchar(26), @time, 121)

print ''

print 'SPIDs at the head of blocking chains'

select spid from @probclients

where blocked = 0 and spid in (select blocked from @probclients where spid != 0)

if @latch = 0

begin

print 'SYSLOCKINFO'

select @time2 = getdate()

select spid = convert (smallint, req_spid),

ecid = convert (smallint, req_ecid),

rsc_dbid As dbid,

rsc_objid As ObjId,

rsc_indid As IndId,

Type = case rsc_type when 1 then 'NUL'

when 2 then 'DB'

when 3 then 'FIL'

when 4 then 'IDX'

when 5 then 'TAB'

when 6 then 'PAG'

when 7 then 'KEY'

when 8 then 'EXT'

when 9 then 'RID'

when 10 then 'APP' end,

Resource = substring (rsc_text, 1, 16),

Mode = case req_mode + 1 when 1 then NULL

when 2 then 'Sch-S'

when 3 then 'Sch-M'

when 4 then 'S'

when 5 then 'U'

when 6 then 'X'

when 7 then 'IS'

when 8 then 'IU'

when 9 then 'IX'

when 10 then 'SIU'

when 11 then 'SIX'

when 12 then 'UIX'

when 13 then 'BU'

when 14 then 'RangeS-S'

when 15 then 'RangeS-U'

when 16 then 'RangeIn-Null'

when 17 then 'RangeIn-S'

when 18 then 'RangeIn-U'

when 19 then 'RangeIn-X'

when 20 then 'RangeX-S'

when 21 then 'RangeX-U'

when 22 then 'RangeX-X'end,

Status = case req_status when 1 then 'GRANT'

when 2 then 'CNVT'

when 3 then 'WAIT' end,

req_transactionID As TransID, req_transactionUOW As TransUOW

from master.dbo.syslockinfo s,

@probclients p

where p.spid = s.req_spid

print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))

end -- latch not set

end

else

print 'No blocking via locks at ' + convert(varchar(26), @time, 121)

print ''

end -- fast set

else

begin -- Fast not set

print ''

print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

select spid, status, blocked, open_tran, waitresource, waittype,

waittime, cmd, lastwaittype, cpu, physical_io,

memusage, last_batch=convert(varchar(26), last_batch,121),

login_time=convert(varchar(26), login_time,121),net_address,

net_library, dbid, ecid, kpid, hostname, hostprocess,

loginame, program_name, nt_domain, nt_username, uid, sid,

sql_handle, stmt_start, stmt_end

from master.dbo.sysprocesses

print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))

print ''

print 'SYSPROC FIRST PASS'

select spid, ecid, waittype from @probclients where waittype != 0x0000

if exists(select blocked from @probclients where blocked != 0)

begin

print 'Blocking via locks at ' + convert(varchar(26), @time, 121)

print ''

print 'SPIDs at the head of blocking chains'

select spid from @probclients

where blocked = 0 and spid in (select blocked from @probclients where spid != 0)

if @latch = 0

begin

print 'SYSLOCKINFO'

select @time2 = getdate()

select spid = convert (smallint, req_spid),

ecid = convert (smallint, req_ecid),

rsc_dbid As dbid,

rsc_objid As ObjId,

rsc_indid As IndId,

Type = case rsc_type when 1 then 'NUL'

when 2 then 'DB'

when 3 then 'FIL'

when 4 then 'IDX'

when 5 then 'TAB'

when 6 then 'PAG'

when 7 then 'KEY'

when 8 then 'EXT'

when 9 then 'RID'

when 10 then 'APP' end,

Resource = substring (rsc_text, 1, 16),

Mode = case req_mode + 1 when 1 then NULL

when 2 then 'Sch-S'

when 3 then 'Sch-M'

when 4 then 'S'

when 5 then 'U'

when 6 then 'X'

when 7 then 'IS'

when 8 then 'IU'

when 9 then 'IX'

when 10 then 'SIU'

when 11 then 'SIX'

when 12 then 'UIX'

when 13 then 'BU'

when 14 then 'RangeS-S'

when 15 then 'RangeS-U'

when 16 then 'RangeIn-Null'

when 17 then 'RangeIn-S'

when 18 then 'RangeIn-U'

when 19 then 'RangeIn-X'

when 20 then 'RangeX-S'

when 21 then 'RangeX-U'

when 22 then 'RangeX-X'end,

Status = case req_status when 1 then 'GRANT'

when 2 then 'CNVT'

when 3 then 'WAIT' end,

req_transactionID As TransID, req_transactionUOW As TransUOW

from master.dbo.syslockinfo

print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))

end -- latch not set

end

else

print 'No blocking via locks at ' + convert(varchar(26), @time, 121)

print ''

end -- Fast not set

print 'DBCC SQLPERF(WAITSTATS)'

dbcc sqlperf(waitstats)

Print ''

Print '*********************************************************************'

Print 'Print out DBCC Input buffer for all blocked or blocking spids.'

Print '*********************************************************************'

declare ibuffer cursor fast_forward for

select distinct cast (spid as varchar(6)) as spid

from @probclients

where (spid <> @@spid) and

((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))

or spid in (select blocked from @probclients where blocked != 0))

open ibuffer

fetch next from ibuffer into @spid

while (@@fetch_status != -1)

begin

print ''

print 'DBCC INPUTBUFFER FOR SPID ' + @spid

exec ('dbcc inputbuffer (' + @spid + ')')

fetch next from ibuffer into @spid

end

deallocate ibuffer

Print ''

Print '*******************************************************************************'

Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'

Print '*******************************************************************************'

declare ibuffer cursor fast_forward for

select distinct cast (dbid as varchar(6)) from @probclients

where dbid != 0

open ibuffer

fetch next from ibuffer into @spid

while (@@fetch_status != -1)

begin

print ''

set @dbname = db_name(@spid)

set @status = DATABASEPROPERTYEX(@dbname,'Status')

set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')

print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'

if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'

dbcc opentran(@dbname)

else

print 'Skipped: Status=' + convert(nvarchar(128),@status)

+ ' UserAccess=' + convert(nvarchar(128),@useraccess)

print ''

if @spid = '2' select @blocked = 'Y'

fetch next from ibuffer into @spid

end

deallocate ibuffer

if @blocked != 'Y'

begin

print ''

print 'DBCC OPENTRAN FOR DBID 2 [tempdb]'

dbcc opentran ('tempdb')

end

print 'End time: ' + convert(varchar(26), getdate(), 121)

end -- All

else

print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '

+ convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.20003'

create procedure dbo.sp_blocker_pss08 (@latch int = 1, @fast int = 1, @appname sysname='PSSDIAG')

--version 19.2000 -- 2000 before SP3

if is_member('sysadmin')=0

begin

print 'Must be a member of the sysadmin group in order to run this procedure'

return

end

set nocount on

declare @spid varchar(6)

declare @blocked varchar(6)

declare @time datetime

declare @time2 datetime

declare @dbname nvarchar(128)

declare @status sql_variant

declare @useraccess sql_variant

set @time = getdate()

declare @probclients table(spid smallint, ecid smallint, blocked smallint, waittype binary(2), dbid smallint,

ignore_app tinyint, primary key (blocked, spid, ecid))

insert @probclients select spid, ecid, blocked, waittype, dbid,

case when convert(varchar(128),hostname) = @appname then 1 else 0 end

from master.dbo.sysprocesses where blocked!=0 or waittype != 0x0000

if exists (select spid from @probclients where ignore_app != 1 or waittype != 0x020B)

begin

set @time2 = getdate()

print ''

print '8 Start time: ' + convert(varchar(26), @time, 121) + ' ' + convert(varchar(12), datediff(ms,@time,@time2)) + ' 19.2000 '+ltrim(str(@latch))+' '+ltrim(str(@fast))

insert @probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from @probclients

where blocked not in (select spid from @probclients) and blocked != 0

if (@fast = 1)

begin

print ''

print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

select spid, status, blocked, open_tran, waitresource, waittype,

waittime, cmd, lastwaittype, cpu, physical_io,

memusage,last_batch=convert(varchar(26), last_batch,121),

login_time=convert(varchar(26), login_time,121), net_address,

net_library, dbid, ecid, kpid, hostname, hostprocess,

loginame, program_name, nt_domain, nt_username, uid, sid

from master.dbo.sysprocesses

where blocked!=0 or waittype != 0x0000

or spid in (select blocked from @probclients where blocked != 0)

or spid in (select spid from @probclients where waittype != 0x0000)

print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))

print ''

print 'SYSPROC FIRST PASS'

select spid, ecid, waittype from @probclients where waittype != 0x0000

if exists(select blocked from @probclients where blocked != 0)

begin

print 'Blocking via locks at ' + convert(varchar(26), @time, 121)

print ''

print 'SPIDs at the head of blocking chains'

select spid from @probclients

where blocked = 0 and spid in (select blocked from @probclients where spid != 0)

if @latch = 0

begin

print 'SYSLOCKINFO'

select @time2 = getdate()

select spid = convert (smallint, req_spid),

ecid = convert (smallint, req_ecid),

rsc_dbid As dbid,

rsc_objid As ObjId,

rsc_indid As IndId,

Type = case rsc_type when 1 then 'NUL'

when 2 then 'DB'

when 3 then 'FIL'

when 4 then 'IDX'

when 5 then 'TAB'

when 6 then 'PAG'

when 7 then 'KEY'

when 8 then 'EXT'

when 9 then 'RID'

when 10 then 'APP' end,

Resource = substring (rsc_text, 1, 16),

Mode = case req_mode + 1 when 1 then NULL

when 2 then 'Sch-S'

when 3 then 'Sch-M'

when 4 then 'S'

when 5 then 'U'

when 6 then 'X'

when 7 then 'IS'

when 8 then 'IU'

when 9 then 'IX'

when 10 then 'SIU'

when 11 then 'SIX'

when 12 then 'UIX'

when 13 then 'BU'

when 14 then 'RangeS-S'

when 15 then 'RangeS-U'

when 16 then 'RangeIn-Null'

when 17 then 'RangeIn-S'

when 18 then 'RangeIn-U'

when 19 then 'RangeIn-X'

when 20 then 'RangeX-S'

when 21 then 'RangeX-U'

when 22 then 'RangeX-X'end,

Status = case req_status when 1 then 'GRANT'

when 2 then 'CNVT'

when 3 then 'WAIT' end,

req_transactionID As TransID, req_transactionUOW As TransUOW

from master.dbo.syslockinfo s,

@probclients p

where p.spid = s.req_spid

print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))

end -- latch not set

end

else

print 'No blocking via locks at ' + convert(varchar(26), @time, 121)

print ''

end -- fast set

else

begin -- Fast not set

print ''

print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)

select spid, status, blocked, open_tran, waitresource, waittype,

waittime, cmd, lastwaittype, cpu, physical_io,

memusage,last_batch=convert(varchar(26), last_batch,121),

login_time=convert(varchar(26), login_time,121), net_address,

net_library, dbid, ecid, kpid, hostname, hostprocess,

loginame, program_name, nt_domain, nt_username, uid, sid

from master.dbo.sysprocesses

print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))

print ''

print 'SYSPROC FIRST PASS'

select spid, ecid, waittype from @probclients where waittype != 0x0000

if exists(select blocked from @probclients where blocked != 0)

begin

print 'Blocking via locks at ' + convert(varchar(26), @time, 121)

print ''

print 'SPIDs at the head of blocking chains'

select spid from @probclients

where blocked = 0 and spid in (select blocked from @probclients where spid != 0)

if @latch = 0

begin

print 'SYSLOCKINFO'

select @time2 = getdate()

select spid = convert (smallint, req_spid),

ecid = convert (smallint, req_ecid),

rsc_dbid As dbid,

rsc_objid As ObjId,

rsc_indid As IndId,

Type = case rsc_type when 1 then 'NUL'

when 2 then 'DB'

when 3 then 'FIL'

when 4 then 'IDX'

when 5 then 'TAB'

when 6 then 'PAG'

when 7 then 'KEY'

when 8 then 'EXT'

when 9 then 'RID'

when 10 then 'APP' end,

Resource = substring (rsc_text, 1, 16),

Mode = case req_mode + 1 when 1 then NULL

when 2 then 'Sch-S'

when 3 then 'Sch-M'

when 4 then 'S'

when 5 then 'U'

when 6 then 'X'

when 7 then 'IS'

when 8 then 'IU'

when 9 then 'IX'

when 10 then 'SIU'

when 11 then 'SIX'

when 12 then 'UIX'

when 13 then 'BU'

when 14 then 'RangeS-S'

when 15 then 'RangeS-U'

when 16 then 'RangeIn-Null'

when 17 then 'RangeIn-S'

when 18 then 'RangeIn-U'

when 19 then 'RangeIn-X'

when 20 then 'RangeX-S'

when 21 then 'RangeX-U'

when 22 then 'RangeX-X'end,

Status = case req_status when 1 then 'GRANT'

when 2 then 'CNVT'

when 3 then 'WAIT' end,

req_transactionID As TransID, req_transactionUOW As TransUOW

from master.dbo.syslockinfo

print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))

end -- latch not set

end

else

print 'No blocking via locks at ' + convert(varchar(26), @time, 121)

print ''

end -- Fast not set

print 'DBCC SQLPERF(WAITSTATS)'

dbcc sqlperf(waitstats)

Print ''

Print '*********************************************************************'

Print 'Print out DBCC Input buffer for all blocked or blocking spids.'

Print '*********************************************************************'

declare ibuffer cursor fast_forward for

select distinct cast (spid as varchar(6)) as spid

from @probclients

where (spid <> @@spid) and

((blocked!=0 or (waittype != 0x0000 and ignore_app = 0))

or spid in (select blocked from @probclients where blocked != 0))

open ibuffer

fetch next from ibuffer into @spid

while (@@fetch_status != -1)

begin

print ''

print 'DBCC INPUTBUFFER FOR SPID ' + @spid

exec ('dbcc inputbuffer (' + @spid + ')')

fetch next from ibuffer into @spid

end

deallocate ibuffer

Print ''

Print '*******************************************************************************'

Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'

Print '*******************************************************************************'

declare ibuffer cursor fast_forward for

select distinct cast (dbid as varchar(6)) from @probclients

where dbid != 0

open ibuffer

fetch next from ibuffer into @spid

while (@@fetch_status != -1)

begin

print ''

set @dbname = db_name(@spid)

set @status = DATABASEPROPERTYEX(@dbname,'Status')

set @useraccess = DATABASEPROPERTYEX(@dbname,'UserAccess')

print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'

if @status = N'ONLINE' and @useraccess != N'SINGLE_USER'

dbcc opentran(@dbname)

else

print 'Skipped: Status=' + convert(nvarchar(128),@status)

+ ' UserAccess=' + convert(nvarchar(128),@useraccess)

print ''

if @spid = '2' select @blocked = 'Y'

fetch next from ibuffer into @spid

end

deallocate ibuffer

if @blocked != 'Y'

begin

print ''

print 'DBCC OPENTRAN FOR DBID 2 [tempdb]'

dbcc opentran ('tempdb')

end

print 'End time: ' + convert(varchar(26), getdate(), 121)

end -- All

else

print '8 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '

+ convert(varchar(12), datediff(ms,@time,getdate())) + ' ' + ISNULL (@@servername,'(null)') + ' 19.2000'

HTTP Error 401.2 - Unauthorized: Access is denied due to server configuration

2009-08-04T07:07:00.000-07:00

Objective: Solution for HTTP Error 401.2 - Unauthorized: Access is denied

Error in Web Page:

You do not have permission to view this directory or page using the credentials that you supplied because your Web browser is sending a WWW-Authenticate header field that the Web server is not configured to accept.
Please try the following:

Contact the Web site administrator if you believe you should be able to view this directory or page. Click the Refresh button to try again with different credentials.

HTTP Error 401.2 - Unauthorized: Access is denied due to server configuration.
Internet Information Services (IIS)

Technical Information (for support personnel)

Go to Microsoft Product Support Services and perform a title search for the words HTTP and 401.
Open IIS Help, which is accessible in IIS Manager (inetmgr), and search for topics titled About Security, Authentication, and About Custom Error Messages.

Solution:

Give the permissions in the child folder where the website is accessing for the relevant user like IUSR_MGMTEXDR

Please see the error in internet explorer by deselecting the friendly HTTP error messages from Internet Explorer.

To make the error to appear in web page, Please go to Internet Explorer - Tools - Options - Advanced - Deselect the Friendly HTTP error

Go to IIS and Select the Particular folder which you are accessing through website and right click to see the properties.

Go to Directory Security Tab and click the Edit button in the Authentication and click Enable Anonymous Access check box and Apply for the user IUSR_MGMTEXDR.

MSN Or Windows Messenger Problem

2009-08-03T15:13:00.001-07:00

Objective: To rectify the error: 'Windows Live Messenger Can't Sign' - Messenger error code: error code-80072ee7
http://webmessenger.msn.com
Follow the below steps :
1. The date on your computer needs to be set properly -- double click the clock verify that the time and date are set.

2. If your password information is not saved, verify that you are typing it in with the correct case (uppercase or lowercase).

3. Change your password at http://memberservicesnet.passport.com/memberservice.srf to something smaller (under 10-12 characters), then try signing in again.

4. If you use a firewall (like ZoneAlarm, Norton Internet Security etc. ), it's possible that Messenger doesn't have the correct rights to access the Internet, especially since you upgraded. You may need to re/add Messenger to the allowed list of programs in your firewall if this is the case.

5. If you disabled your firewall in the past, it still may be blocking Messenger -- try restarting it and see if that helps the situation. You also might try uninstalling an installed firewall, to verify that it isn't causing a problem (even if it is disabled).

6. Clear your IE cache and cookies -- open Internet Explorer, click the Tools menu, then Internet Options, then click the Delete Files button, and when that's complete click the Delete Cookies button.

7. Check your IE Security settings -- open Internet Explorer, click the Tools menu, then Internet Options, then Advanced tab, scroll to the Security section, and verify that "Check for server certificate revocation" is unchecked. Also verify that 'Use SSL 2.0' and Use SSL 3.0' is checked, then click OK.

8. Click Start, then Run, and enter the following:
regsvr32 softpub.dll
then click OK and do the same for the following: regsvr32 wintrust.dll regsvr32 initpki.dll

9. If using MSN Messenger 7, click the Tools menu, then Options, then Connection category, click the Advanced Settings button, uncheck all the options, click OK twice and then try

View Permissions in SQL Server

2009-07-30T03:33:00.001-07:00

Step I: List all dbo Grantor Permissions

EXEC sp_helprotect NULL, NULL, dbo
EXEC sp_helprotect @grantorname = 'dbo'

Step II: Listing the permissions for a table

EXEC sp_helprotect 't_register'

Step III: Listing the permissions granted by a specific user

EXEC sp_helprotect NULL, 'Linda'

Step IV: Listing the statement permissions only

EXEC sp_helprotect NULL, NULL, NULL, 's'

Log Truncate for SQL Server Database

2009-07-29T02:34:00.000-07:00

Objective: Truncate log in the SQL Server Database to provide more space for the Server:

Please use the below procedure to get script to apply for Truncating the Log in the SQL Server.

CREATE procedure P_LogTruncate
AS
Begin
DECLARE @DBNAME varchar(50)
DECLARE @LOGICALFILENAME varchar(50)
DECLARE CURSOR_LOGDB CURSOR for select db_name(database_id) as DBNAME,NAME AS LOGICALFILENAME from sys.master_files(NOLOCK) WHERE TYPE_DESC='LOG'
OPEN CURSOR_LOGDB
FETCH NEXT FROM CURSOR_LOGDB INTO @DBNAME, @LOGICALFILENAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
Declare @SQL1 Varchar(MAX)
Declare @SQL2 Varchar(MAX)
SET @SQL1 = 'BACKUP LOG '+@DBNAME+' WITH TRUNCATE_ONLY'
PRINT @SQL1
--EXEC (@SQL1)

SET @SQL2 = 'use ' + @Dbname + ' DBCC SHRINKFILE(' + @LOGICALFILENAME + ',0)'
PRINT @SQL2
--EXEC (@SQL2)

FETCH NEXT FROM CURSOR_LOGDB INTO @Dbname, @LOGICALFILENAME
END
close CURSOR_LOGDB
Deallocate CURSOR_LOGDB
END

Change compatibility level of SQL Server Database

2009-07-28T06:42:00.000-07:00

Objective: Change compatibility level of database

Step I: Change database to single user before changing the db compatibility

ALTER DATABASE [DBNAME] SET MULTI_USER WITH NO_WAIT

----

ALTER DATABASE DBNAME SET SINGLE_USER WITH NO_WAIT

------

EXEC sp_dboption 'DBNAME', 'single user', 'true'

Step II: Kill all spids for that database except your spid

Select spid from master..sysprocesses where dbid = db_id('DBNAME') and spid <> @@spid

--------

Kill those spids using kill command:

For eg: kill 94

--------

Or you can close all connections using the below script:

/**

DECLARE @DatabaseName nvarchar(50)

DECLARE @SPId int

SET @DatabaseName = N'DBNAME'

DECLARE my_cursor CURSOR FAST_FORWARD FOR

SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DBNAME) AND SPId <> @@SPId

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @SPId

WHILE @@FETCH_STATUS = 0

BEGIN

KILL @SPId

FETCH NEXT FROM my_cursor INTO @SPId

END

CLOSE my_cursor

DEALLOCATE my_cursor

********/

Step III Change compatibility level:

EXEC sp_dbcmptlevel AdventureWorks, 80;

----SQL Server 2000 database compatible level to SQL Server 2005

EXEC sp_dbcmptlevel AdventureWorks, 90;

Step IV: Change single user to multi user:

EXEC sp_dboption 'c_data1dk', 'single user', 'false'

Grant Permissions to All Stored Procedures

2009-07-28T02:29:00.000-07:00

Objective: To grant permissions for specific user to all stored procedures in the specific Database.

use dbname
go
CREATE PROCEDURE P_GrantExectoAllSP @user sysname
AS
SET NOCOUNT ON

--Step I: Variable declarations
DECLARE @CMD1 varchar(8000)
DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)

--Step 2 - Create temporary table
CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)

--Step 3 - Populate temporary table
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME NOT LIKE 'dt_%' and ROUTINE_NAME NOT LIKE 'sp_MS%'
AND ROUTINE_TYPE = 'PROCEDURE'

--Step 4 - Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures

-- 5 - WHILE loop
WHILE @MAXOID > 0
BEGIN

-- 6 - Initialize the variables
SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @MAXOID

-- 7 - Build the string
SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user

-- 8 - Execute the string
-- SELECT @CMD1
EXEC(@CMD1)

-- 9 - Decrement @MAXOID
SET @MAXOID = @MAXOID - 1
END

-- 10 - Drop the temporary table
DROP TABLE #StoredProcedures

SET NOCOUNT OFF
GO

SQL Server Objects Permissions Explore

2009-07-24T03:05:00.000-07:00

Objective: To explore SQL Server Object Permission:

DATABASE PERMISSIONS FOR USER:

USE [DB NAME]
GO
EXEC sp_addrolemember N'db_accessadmin', N'USERNAME'

EXEC sp_addrolemember N'db_ddladmin', N'USERNAME'
GO
USE [DB NAME]
GO
EXEC sp_addrolemember N'db_backupoperator', N'USERNAME'
GO
USE [DB NAME]
GO
EXEC sp_addrolemember N'db_denydatareader', N'USERNAME'
GO
USE [DB NAME]
GO
EXEC sp_addrolemember N'db_denydatawriter', N'USERNAME'
GO
USE [DB NAME]
GO
EXEC sp_addrolemember N'db_securityadmin', N'USERNAME'
GO

STORED PROCEDURE PERMISSION:

GRANT ALTER ON [dbo].[SP_NAME] TO [USER NAME]
GO
GRANT CONTROL ON [dbo].[SP_NAME] TO [USER NAME]
GO
GRANT TAKE OWNERSHIP ON [dbo].[SP_NAME] TO [USER NAME]
GO
GRANT EXECUTE ON [dbo].[SP_NAME] TO ANSON
GO
GRANT VIEW DEFINITION ON [dbo].[SP_NAME] TO [USER NAME]
GO
GRANT EXECUTE ON employee_proc TO public

TABLE LEVEL PERMISSIONS:

GRANT SELECT ON TableName TO Alex
DENY SELECT ON TableName TO Alex
REVOKE SELECT ON TableName TO Alex
DENY SELECT ON TableName TO Sales, Tech - HERE WE CAN DENY FOR TWO ROLES
DENY UPDATE, DELETE, INSERT ON TableName TO Accounting

Monitoring Tool for SQL Server

2009-07-24T03:01:00.000-07:00

Objective: To explore the monitoring Tools for SQL Server.

Please use the below link to explore yourself in monitoring.

SQL Server Monitoring Tool Reviews

Object Permissions in SQL Server

2009-07-24T02:43:00.000-07:00

Objective: To explore certain syntax for giving permissions in SQL Server Objects:

DATABASE PERMISSIONS FOR USER:

USE [DB NAME]

EXEC sp_addrolemember N'db_accessadmin', N'USERNAME'

USE [DB NAME]

EXEC sp_addrolemember N'db_backupoperator', N'USERNAME'

USE [DB NAME]

EXEC sp_addrolemember N'db_denydatareader', N'USERNAME'

USE [DB NAME]

EXEC sp_addrolemember N'db_denydatawriter', N'USERNAME'

USE [DB NAME]

EXEC sp_addrolemember N'db_securityadmin', N'USERNAME'

STORED PROCEDURE PERMISSION:

GRANT ALTER ON [dbo].[SP_NAME] TO [USER NAME]

GRANT CONTROL ON [dbo].[SP_NAME] TO [USER NAME]

GRANT TAKE OWNERSHIP ON [dbo].[SP_NAME] TO [USER NAME]

GRANT EXECUTE ON [dbo].[SP_NAME] TO ANSON

GRANT VIEW DEFINITION ON [dbo].[SP_NAME] TO [USER NAME]

GRANT EXECUTE ON employee_proc TO public

TABLE LEVEL PERMISSIONS:

GRANT SELECT ON TableName TO Alex

DENY SELECT ON TableName TO Alex

REVOKE SELECT ON TableName TO Alex

DENY SELECT ON TableName TO Sales, Tech - HERE WE CAN DENY FOR TWO ROLES

DENY UPDATE, DELETE, INSERT ON TableName TO Accounting

Also refer the below articles:

1. Database Journal

2. searchsqlserver.techtarget.com

3. MSSQL Tips

Detach and Attach DB

2009-07-23T09:18:00.000-07:00

How to Detach and Attach DB:

1. Location of files are in : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Our purpose is to move the database from C: Drive to D: Drive

3. Execute the below Procedure:

USE [master]

EXEC master.dbo.sp_detach_db @dbname = N'Reporting', @keepfulltextindexfile=N'true'

4. Move the mdf and ldf files to the D: Drive Now:

5. Then Execute the below procedure to attach db:

EXEC sp_attach_db @dbname = 'Reporting',

@filename1 = 'D:\data\Reporting\Reporting.mdf',

@filename2 = 'D:\data\Reporting\Reporting_log.ldf'

Adding, Dropping Column in SQL Server Replication Table

2009-07-15T05:59:00.000-07:00

Objective: To modify the existing table structure in the replicated table

ALTER TABLE [dbo].[T_TableName] drop CONSTRAINT [Constraintname] DEFAULT ((0)) FOR [ColumnName]

Exec sp_repldropcolumn @source_object = 'T_TableName'

, @column = 'ColumnName'

Exec sp_repladdcolumn @source_object = 'T_TableName'

, @column = 'ColumnName'

, @typetext = 'Bit Not Null CONSTRAINT constraintName DEFAULT (0)'

, @publication_to_add = 'All' -- This indicates whichever publication holds this table, will modify.

Disabling - Shut Down Option

2009-07-15T05:57:00.000-07:00

Objective: To avoid shut down from the Start Menu

Hive: HKEY_LOCAL_MACHINE

Key: Software\Microsoft\Windows\CurrentVersion\Policies\Explorer

Name: NoClose

Type: REG_DWORD

Value: 1

You could still shutdown using the command line tool. This is just to avoid clicking shutdown, instead of Logoff.

Process Monitor - Check All Application Activities - Including SQL Server

2009-07-08T03:57:00.000-07:00

Process Monitor v2.04

Download Process Monitor

Process Monitor is an advanced monitoring tool for Windows that shows real-time file system, Registry and process/thread activity. It combines the features of two legacy Sysinternals utilities, Filemon and Regmon, and adds an extensive list of enhancements including rich and non-destructive filtering, comprehensive event properties such session IDs and user names, reliable process information, full thread stacks with integrated symbol support for each operation, simultaneous logging to a file, and much more. Its uniquely powerful features will make Process Monitor a core utility in your system troubleshooting and malware hunting toolkit.

http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

Process Explorer - System Problems Detective Tool

2009-07-08T03:14:00.000-07:00

Process Explorer v11.33

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

Download Link:

Download Process Explorer

Grab Images From Website Software

2009-07-06T13:21:00.000-07:00

The below tool is very good for the purpose of downloading the images from particular website:

Adsen Image Grab 1.2

Create Date in Different Format - In SQL Server

2009-06-29T03:21:00.000-07:00

With DatesCreation as
(
select cast('2006-01-01' as datetime) DateValue
union all
select DateValue + 1
from DatesCreation
where DateValue + 1 <= '2008-12-31'

)

select DateValue,Year(DateValue) as Year,Month(DateValue) as Month,day(DateValue) as Day ,

DateName(dw,DateValue) as Day_Name, DateName(Month,DateValue) as Month_Name,

DateName(Year,DateValue) as Year_Name,

Datepart(weekday,DateValue) as [WeekDay],

Datepart(week,DateValue) as Week_In_Year,

DatePart(dayofyear,DateValue) as DayNumber_In_Year,DatePart(quarter,DateValue) as [Quarter],

REPLACE(CONVERT(VARCHAR(10), DateValue, 1), '/', '') as MMDDYY, REPLACE(CONVERT(VARCHAR(10), DateValue, 3), '/', '') as DDMMYY, REPLACE(CONVERT(VARCHAR(10), DateValue, 103), '/', '') AS [DDMMYYYY], CONVERT(VARCHAR(26), DateValue, 109) as [Mon DD YYYY HH:MI:SS:MMMAM (or PM)],

CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY],

CONVERT(VARCHAR(8), DateValue, 3) AS [DD/MM/YY],

CONVERT(VARCHAR(10), DateValue, 103) AS [DD/MM/YYYY],

CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY],

CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY],

CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY],

CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY],

CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD],

CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] from DatesCreation

OPTION (MAXRECURSION 0)

You can also create function something like below to populate dates from One Date to Another Date:

Create function fnDatesCreation (@StartDate datetime, @EndDate datetime)
Returns @Series table ([Day] datetime)
-- select * from dbo.fnDatesCreation ('2008-01-01','2008-01-31')
as
begin
declare @StartingDate datetime
set @StartingDate = @StartDate
while (datediff(day, @StartingDate, @EndDate) >= 0)
begin
insert @Series select @StartingDate
set @StartingDate = dateadd(day,1,@StartingDate)
end
return
end

SQL Server Datawarehouse Lesson 1

2009-06-26T03:55:00.000-07:00

First Install SQL Server 2008, Visual Studio 2008 and Adventure Works Database:
1. SQL Server Server Installations Files:
http://www.microsoft.com/downloads/details.aspx?FamilyId=265F08BC-1874-4C81-83D8-0D48DBCE6297&displaylang=en
2. Visual Studio 2008 Installations Files:
http://www.microsoft.com/visualstudio/en-us/downloads/microsoft-downloads.mspx
3. Adventure Works Database Installation:
http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18407
Complete the above 3 installation and proceed for making Cubes in Data Warehousing.

=If(A1>10, "Larger", "Smaller")	would return "Larger".
=If(A1=20, "Equal", "Not Equal")	would return "Not Equal".
=If(A2="Tech on the Net", 12, 0)	would return 12.