Stored procedure for backing up all databases

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:

-- Company: Manatix Technologies B.V.

-- Website: http://www.manatix.com

-- Create date: <14-09-2008>

-- Description:

-- =============================================

create PROCEDURE BackupSQLDatabases

AS

BEGIN



/* Declarations & Variables */

DECLARE @path VARCHAR (500) /* Path of the Backup Files */

DECLARE @folderdate VARCHAR (75) /* The subdir for my backups with Format YYYYMMDD */

DECLARE @cmd VARCHAR (4000) /* The command to create Subdir */

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE @PreciceDateTime varchar(255)

DECLARE @ServerName varchar(50)

DECLARE @NetworkBackupShare varchar(75)



--Network or local disk path you wish to use, such as D:\Backup

SET @NetworkBackupShare = N'D:\Backup'



--Create a dynamic path for the backup of the databases based on datetime



EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE;

/* gives us YYYYMMDD

hour hh

minute mi, n

second ss, s

*/

SELECT @folderdate = CONVERT(VARCHAR(20), GETDATE(), 112) + N'-' + N'H' + CONVERT(VARCHAR(20), DATEPART(hour, GETDATE()), 112) + N'M' + CONVERT(VARCHAR(20), DATEPART(minute, GETDATE()), 112) + N'S' + CONVERT(VARCHAR(20), DATEPART(second, GETDATE()), 112)

--PRINT @folderdate



SET @ServerName = (SELECT @@servername)

/* Path as C:\Backup\YYYYMMDD */

SET @path = @NetworkBackupShare + N'\' + @ServerName + N'\' + @folderdate + N'\'

/* Create the command that will do the creation of the folder*/

SET @cmd = N'mkdir ' + @path

--PRINT @cmd

/* Create the new directory */

EXEC master.dbo.xp_cmdshell @cmd , no_output

/* now I can direct all the backup file to the created subdirectory like,

SET filename = path [other_variable/s] ‘.BAK‘ */

/*******************************************/

--Now let's actually do the backups to the path created above

DECLARE DATABASES_CURSOR CURSOR FOR
select

DATABASE_NAME = db_name(s_mf.database_id)

from

sys.master_files s_mf

where

-- ONLINE

s_mf.state = 0



-- Only look at databases to which we have access

and has_dbaccess(db_name(s_mf.database_id)) = 1



-- Not master, tempdb or model

and db_name(s_mf.database_id) not in ('Master','tempdb','model')

group by s_mf.database_id

order by 1


OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN
declare @DBFileName varchar(256)

set @DBFileName = --datename(dw, getdate()) + ' - ' +

replace(replace(@DBName,':','_'),'\','_') + '.BAK'

PRINT @path

exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = ''' + @path +

@DBFileName + ''' WITH NOFORMAT, INIT, NAME = N''' +

@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100')



FETCH NEXT FROM DATABASES_CURSOR INTO @DBName


END

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR

/*******************************************/

EXEC sp_configure 'xp_cmdshell', 0;

EXEC sp_configure 'show advanced options', 0;

RECONFIGURE

END







---- USAGE
--USE [master]
--GO
--
--DECLARE @return_value int
--
--EXEC @return_value = [dbo].[BackupSQLDatabases]
--
--SELECT 'Return Value' = @return_value
--
--GO

how-to backup all my sql server databases via command line

::BackUpAllMyDatabases.cmd
:: COMMAND LINE BATCH SCRIPT FOR TAKING BACKUP OF ALL DATABASES

::RUN THE SQL SCRIPT VIA THE COMMAND LINE WITH LOGGING
sqlcmd -S localhost -e -i "BackUpAllMyDatabases.sql" -o Result_Of_BackUpAllMyDatabases.log

::VIEW THE RESULTS
Result_Of_BackUpAllMyDatabases.log

::pause

how-to back up all my sql server 2005 databases via sql script

--COURTESY OF Paul Hayman
-- SEE http://www.geekzilla.co.uk/View487F82A5-C96B-4660-A070-F7C8B7FC4431.htm
DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR
select
DATABASE_NAME = db_name(s_mf.database_id)
from
sys.master_files s_mf
where
-- ONLINE
s_mf.state = 0

-- Only look at databases to which we have access
and has_dbaccess(db_name(s_mf.database_id)) = 1

-- Not master, tempdb or model
and db_name(s_mf.database_id) not in ('Master','tempdb','model')
group by s_mf.database_id
order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
declare @DBFileName varchar(256)
set @DBFileName = @DbName + '_' + replace(convert(varchar, getdate(), 112), '-', '.') + '.bak'
--REMEMBER TO PUT HERE THE TRAILING \ FOR THE DIRECTORY !!!
exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = N''D:\libs\sql\PRACTICE_DB_INSTALLER\' +
@DBFileName + ''' WITH NOFORMAT, INIT, NAME = N''' +
@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100')

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

advanced trimming functions in PL /TSQL

SOURCES:
<a href="">Pinal Dave's blog</a>
use poc 
go
drop FUNCTION dbo.LTrimX
go
CREATE FUNCTION dbo.LTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE 'tableName[' + @trimchars + ']%' SET @str = SUBSTRING(@str, PATINDEX('tableName%[^' + @trimchars + ']%', @str), 8000)
RETURN @str
END
GO
drop FUNCTION dbo.RTrimX
go
CREATE FUNCTION dbo.RTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE 'tableName%[' + @trimchars + ']tableName'
SET @str = REVERSE(dbo.LTrimX(REVERSE(@str)))
RETURN @str
END
GO
drop FUNCTION dbo.TrimX
go
CREATE FUNCTION dbo.TrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN dbo.LTrimX(dbo.RTrimX(@str))
END
GO
/* Run the created function */
SELECT dbo.TRIMX('

tableName         word leading trailing spaces           ') AS tableNameTrimmedWord
GO

run stored procedure on sql server 2005 start-up

-- courtesy of John Sheesley

use master
GO
CREATE PROCEDURE usp_StartTrace
AS
BEGIN
DECLARE @TraceID INT
DECLARE @MaxSize BIGINT
SET @MaxSize = 15
EXECUTE sp_trace_create @TraceID output,
@options = 2, --//rollover the file when max size is reached
@tracefile = N'C:Tracefile.trc', --//trace file name
@maxfilesize = @MaxSize --//maximum file size
SELECT @TraceID --//output the traceid create for viewing
DECLARE @On BIT
SET @On = 1
EXECUTE sp_trace_setevent @TraceID, 10, 15, @On
EXECUTE sp_trace_setevent @TraceID, 10, 16, @On
EXECUTE sp_trace_setevent @TraceID, 10, 1, @On
EXECUTE sp_trace_setevent @TraceID, 10, 9, @On
EXECUTE sp_trace_setevent @TraceID, 10, 17, @On
EXECUTE sp_trace_setevent @TraceID, 10, 6, @On
EXECUTE sp_trace_setevent @TraceID, 10, 10, @On
EXECUTE sp_trace_setevent @TraceID, 10, 14, @On
EXECUTE sp_trace_setevent @TraceID, 10, 18, @On
EXECUTE sp_trace_setevent @TraceID, 10, 11, @On
EXECUTE sp_trace_setevent @TraceID, 10, 12, @On
EXECUTE sp_trace_setevent @TraceID, 10, 13, @On
EXECUTE sp_trace_setevent @TraceID, 12, 15, @On
EXECUTE sp_trace_setevent @TraceID, 12, 16, @On
EXECUTE sp_trace_setevent @TraceID, 12, 1, @On
EXECUTE sp_trace_setevent @TraceID, 12, 9, @On
EXECUTE sp_trace_setevent @TraceID, 12, 17, @On
EXECUTE sp_trace_setevent @TraceID, 12, 6, @On
EXECUTE sp_trace_setevent @TraceID, 12, 10, @On
EXECUTE sp_trace_setevent @TraceID, 12, 14, @On
EXECUTE sp_trace_setevent @TraceID, 12, 18, @On
EXECUTE sp_trace_setevent @TraceID, 12, 11, @On
EXECUTE sp_trace_setevent @TraceID, 12, 12, @On
EXECUTE sp_trace_setevent @TraceID, 12, 13, @On
EXECUTE sp_trace_setstatus @TraceID, 1
END
Go

sql server 2005 security who does what ?

SELECT
p.name, p.type_desc, pp.name, pp.type_desc, pp.is_fixed_role
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

SELECT
UserName = dp.name, UserType = dp.type_desc, LoginName = sp.name, LoginType = sp.type_desc
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id


SELECT
dp.class_desc, dp.permission_name, dp.state_desc,
ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name
FROM sys.database_permissions dp
JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id

How-to create custom formatting for each result set returned by a stored procedure in the whole sql server 2005 database


-- So the idea is that you refer to each column and than parse the formatting elements
-- you reffer to each column by Col + ColNumber + TypeOfFormatting notation
-- to the the data you would have the query the extended properties for that stored procedure




DECLARE @procName varchar(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
select s.name from sysobjects s where type = 'P' OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
print
'

EXEC sys.sp_addextendedproperty @name=N''Col1NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col2NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col3NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col4NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col5NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col6NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col7NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col8NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col9NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col10NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col11NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col12NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col13NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col14NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col15NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col16NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col17NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col18NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col19NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''
EXEC sys.sp_addextendedproperty @name=N''Col20NumberFormat'', @value=N''culturezInfo="fi-FI",currencyDecimalDigits="0",numberDecimalDigits="0",numberGroupSeparator=" ",numberDecimalSeparator=","'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''PROCEDURE'',@level1name=N''' + @procName + '''

'


FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName



how-to create installer for asp.net site

; SITENAMESITE_INSTALL.nsi
; THIS INSTALLER INSTALLS THE APPLICATION LAYER OF THE SITENAME-Tool
;2008.11.19 Yordan Georgiev
;Purpose: This NSI script packs all the nessessary source files for running the SITENAME site

;--------------------------------
!include "FileFunc.nsh"
!include "MUI2.nsh"
!include "WordFunc.nsh"
!include "Time.nsh"
!include "Sections.nsh"
!insertmacro WordFind



!insertmacro MUI_LANGUAGE "English"
!define /date MYDATE "%Y%b%d.%H%M%S" ;not really needed but yet
!define SERVER $R0 ;Temp variable for holding the option chosen for server
!define CHOSENSERVER $R9 ;Temp variable for holding the NOTE :


LangString MUI_TEXT_INSTALLING_TITLE ${LANG_ENGLISH} "SITENAME SITE Installation"
LangString MUI_TEXT_INSTALLING_SUBTITLE ${LANG_ENGLISH} "Installing SITENAME SITE"
LangString MUI_TEXT_FINISH_TITLE ${LANG_ENGLISH} "SITENAME SITE Ready"
LangString MUI_TEXT_FINISH_SUBTITLE ${LANG_ENGLISH} "SITENAME SITE Installed"
LangString MUI_TEXT_ABORT_TITLE ${LANG_ENGLISH} "Abort SITENAME SITE Installation ?!"
LangString MUI_TEXT_ABORT_SUBTITLE ${LANG_ENGLISH} "Abort SITENAME SITE Installation ?!"

XPStyle On
AutoCloseWindow false
ShowInstDetails show

;Things that need to be extracted on startup (keep these lines before any File command!)
;Only useful for BZIP2 compression
;Use ReserveFile for your own InstallOptions INI files too!

ReserveFile "${NSISDIR}\Plugins\InstallOptions.dll"
ReserveFile "SITENAMEsite_conf.ini"


Name "SITENAME-Tool Application Layer installer for Any Environment"
; THIS IS THE SETUP.EXE FILE PRODUCED BY THE SCRIPT
OutFile "G:\Setup_SITENAME.exe"


InstallDir "D:\SITENAME" ; CHANGE THIS PATH AS THE DEFAULT ONE TO PROVIDE THE USER TO INSTALL
;Prompt for Vista Admin Rights
RequestExecutionLevel admin

;--------------------------------

;Pages
;Order of pages
Page custom SetCustom ValidateCustom "Choose the right server to install t" ;Custom page.
Page directory
Page instfiles
Page custom CustomPage




UninstPage uninstConfirm
UninstPage instfiles

;--------------------------------
; Declaration of user variables (Var command), allowed charaters for variables names : [a-z][A-Z][0-9] and copy_'
Var "Info"
Var "InfoOK"

;--------------------------------
; Installer



;**************************************************************************************************
Section "Components"

;Get Install Options dialog user input

ReadINIStr ${SERVER} "$PLUGINSDIR\SITENAMEsite_conf.ini" "Field 2" "State"
DetailPrint "Install option=${SERVER}"
ReadINIStr ${SERVER} "$PLUGINSDIR\SITENAMEsite_conf.ini" "Field 3" "State"
DetailPrint "Install option=${SERVER}"
ReadINIStr ${SERVER} "$PLUGINSDIR\SITENAMEsite_conf.ini" "Field 4" "State"
DetailPrint "Install option=${SERVER}"
ReadINIStr ${SERVER} "$PLUGINSDIR\SITENAMEsite_conf.ini" "Field 5" "Text"
DetailPrint "Info=${SERVER}"

DetailPrint "CHOSENSERVER=${CHOSENSERVER}"
SectionEnd
;**************************************************************************************************


Section
;CREATE BACKUP OF THE EXISTING SITE IF ANY

${time::GetLocalTime} $R0
${time::TimeString} "$R0" $0 $1 $2 $3 $4 $5
/*
0 - date
1 - month
2 - year
3 - hour
4 - minute
5 - seconds
*/

;CREATE THE BACKUP DIRECTORY BASED ON THE TIMESTAMP
CreateDirectory "$INSTDIR.$2$1$0.$3$4$5"
Rename $INSTDIR "$INSTDIR.$2$1$0.$3$4$5"
;CopyFiles "$INSTDIR\*.*" "$INSTDIR.$2$1$0.$3$4$5" 0


SectionEnd
;**************************************************************************************************
;**************************************************************************************************

Section "Unpacking" SecDummy

; MessageBox MB_OK "The installation directory you choosed is $INSTDIR . Press OK to proceed"



CreateDirectory $INSTDIR
SetOutPath $INSTDIR
; HERE UNZIP ACTUALLY THE FILES
; DO NOT INCLUDE FILES CONTAINING "test" *.MDF *.ldf
File /r /x *.MDF /x *.exclude /x *.ldf /x *.pl /x *.nsis /x *.cmd *.cs *.aspx *.aspx.cs *.dll *.css *.resx *.config *.dll *.pdb *.js *.bmp *.gif *.jpg *.png *.master *.master.cs

SectionEnd
;**************************************************************************************************




;**************************************************************************************************
Section "Unpacking bin folder" SecUnpackBin

; HERE WE UNZIP THE FILES FOR THE BIN DIRECTOR
;CreateDirectory "D:\bin\SITENAME_TEST\"
;SetOutPath "D:\bin\SITENAME_TEST\"
;File /r *.cmd *.dll *.exe
;DELETE ALL EXE FILES

SectionEnd
;**************************************************************************************************



;**************************************************************************************************
Section
;FIND OUT WHICH APPLICATION ARE WE INSTALLING
;NOTE THIS ASSUMES INSTALLATION IN THE D:\ DRIVE
;SO LETS ASSUME INSTALLING D:\SITENAME

StrCpy $R0 "$INSTDIR"
${WordFind} $R0 "\" "-02" $R1
DetailPrint "I saw $R1"

WriteUninstaller "$R1\Uninstall_SITENAMESITE.exe"
SectionEnd
;$R1=D:

Section
StrCpy $R2 "$INSTDIR"
${WordFind} $R2 "\" "-01" $R3
DetailPrint "I saw $R3"
SectionEnd
;$R3=SITENAME

Section "SETUPCONNECTION ENVIRONMENT"

;INFORM THE STARTING OF THE COPY
DetailPrint "Setting up the resource file for the server "
;COPY THE SERVER CONFIG FILES FOR THIS APPLICATION
;COPY THE SERVER FILE AS FROM SERVER.FILE TO FILE
;Delete the Web.config file from the development
Delete "$INSTDIR\Web.config"
;server = heitv156lab.devlab.nokia.com
DetailPrint "I saw ${CHOSENSERVER} Followed by space"
CopyFiles "$INSTDIR\INSTALL\${CHOSENSERVER}.$R3.Web.config" "$INSTDIR\Web.config"
;${CHOSENSERVER}
;COPY THE App_GlobalResources folder
;Delete the App_GlobalResources from the development
Delete "$INSTDIR\App_GlobalResources\*.*"
;Copy the server.app.App_GlobalResources to it's correct place
CopyFiles "$INSTDIR\INSTALL\${CHOSENSERVER}.$R3.App_GlobalResources\*.*" "$INSTDIR\App_GlobalResources\"
;NOW DELETE THE INSTALL DIRECTORY CONTAINING THE DIFFERENT SETUP FILES



; Var /GLOBAL "AnotherVar"

; StrCpy $AnotherVar "test"

SectionEnd




;--------------------------------
; Uninstaller

Section "Uninstall"

StrCpy $Info "SITENAME SITE uninstalled successfully."
Delete "$R1\Uninst.exe"
RmDir $INSTDIR

SectionEnd

Function un.OnUninstSuccess

HideWindow
MessageBox MB_OK "$Info"

FunctionEnd

Function OnInstSuccess

StrCpy $Info "SITENAME SITE installed successfully !!!"
HideWindow
MessageBox MB_OK "$InfoOK"

FunctionEnd


;------------------------------------------------------------------------------------
Function CustomPage

SectionGetFlags ${Section_Name} $R0
IntOp $R0 $R0 & ${SF_SELECTED}
IntCmp $R0 ${SF_SELECTED} show

Abort
show:
FunctionEnd

Function .onInit

;Extract InstallOptions files
;$PLUGINSDIR will automatically be removed when the installer closes

InitPluginsDir
File /oname=$PLUGINSDIR\SITENAMEsite_conf.ini "SITENAMEsite_conf.ini"

FunctionEnd

Function SetCustom

;Display the InstallOptions dialog

Push ${SERVER}

InstallOptions::dialog "$PLUGINSDIR\SITENAMEsite_conf.ini"
Pop ${SERVER}

Pop ${SERVER}

FunctionEnd

Function ValidateCustom

ReadINIStr ${SERVER} "$PLUGINSDIR\SITENAMEsite_conf.ini" "Field 2" "State"
ReadINIStr ${CHOSENSERVER} "$PLUGINSDIR\SITENAMEsite_conf.ini" "Field 2" "Text"
StrCmp ${SERVER} 1 done

ReadINIStr ${SERVER} "$PLUGINSDIR\SITENAMEsite_conf.ini" "Field 3" "State"
ReadINIStr ${CHOSENSERVER} "$PLUGINSDIR\SITENAMEsite_conf.ini" "Field 3" "Text"
StrCmp ${SERVER} 1 done

ReadINIStr ${SERVER} "$PLUGINSDIR\SITENAMEsite_conf.ini" "Field 4" "State"
ReadINIStr ${CHOSENSERVER} "$PLUGINSDIR\SITENAMEsite_conf.ini" "Field 4" "Text"
StrCmp ${SERVER} 1 done
MessageBox MB_ICONEXCLAMATION|MB_OK "You must select at least one install option!"
Abort

done:

FunctionEnd

Function .onInstSuccess
MessageBox MB_OK "SITENAME site installed correctly"
FunctionEnd


Function un.onUserAbort
MessageBox MB_YESNO "Abort uninstall?" IDYES NoCancelAbort
Abort ; causes uninstaller to not quit.
NoCancelAbort:
FunctionEnd

Function un.onInit
MessageBox MB_YESNO "This will uninstall. Continue?" IDYES NoAbort
Abort ; causes uninstaller to quit.
NoAbort:
FunctionEnd

How-to generate insert statements from a table without including the Primary Key's

-- ORIGINAL SOURCE:
-- Generating INSERT statements in SQL Server - BY Sumit Amar
- I just turned it into stored procedure and added the meta data query to not include the PK's
-- Thanks to Sumit Amar and Enjoy !!!

create PROCEDURE [dbo].[procUtils_InsertGenerator]
(
@domain_user varchar(50),
@tableName varchar(100)
)


as

--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR


--old takes the PK's SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName

/* NEW without PK's */
select object_name(c.object_id) "TABLE_NAME", c.name "COLUMN_NAME", s.name "DATA_TYPE"
from sys.columns c
join sys.systypes s on (s.xtype = c.system_type_id)
where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')
AND object_name(c.object_id) in (select name from sys.tables where [name]=@tableName ) and c.is_identity=0




OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
DECLARE @IDENTITY_STRING nvarchar ( 100 )
SET @IDENTITY_STRING = ' '
select @IDENTITY_STRING
SET @string='INSERT '+@tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType

IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END

WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
--SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
--SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
--SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
-- 'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
--SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType
END
DECLARE @Query nvarchar(4000)

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
exec sp_executesql @query
--select @query

CLOSE cursCol
DEALLOCATE cursCol


/*

use MyDataBase
go

DECLARE @RC int
DECLARE @domain_user varchar(50)
DECLARE @tableName varchar(100)

-- TODO: Set parameter values here.
set @domain_user='myDomainUser'
set @tableName = 'MyTableName'

EXECUTE @RC = [POC].[dbo].[procUtils_InsertGenerator]
@domain_user
,@tableName

*/

how-to print all user defined procedures in sql server 2005 - simple use of cursor

SOURCES:
Pinal Dave's blog post
DECLARE @procName varchar(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
select s.name from sysobjects s where type = 'P' OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
exec sp_HelpText @procName
FETCH NEXT
FROM @getprocName INTO @procName
END
CLOSE @getprocName
DEALLOCATE @getprocName

Six Ways To Embed Click To Communicate in your Application

UC platform
UC API ...
Web apps
WinForms
WPF
Win32

How-to embed presense in the applications ...
3 main pillars of functionality of UC for Microsoft :
- Contextual Collaboration (communication of the people in the context of the application they are using )

- Business Process Communication
- Anywhare Information Access ( making the data more accessible trough different communication media )

The Microsoft UC Platform

The intent of the platform is to provide the developers the possibility for their own applications ...

Canonical samples
Comprehensive APIs
Robust platform ...
Great Tools ...

Office Communicator API

- provides automation interface by itself
- SharePoint uses this interface ..
- provides collaboration capabilities .. .

Contextrual Collaboration

-Embed Presence & Click
"anyware seeing a name one should be able to see the presence of this person "
context is passed to the person as well

Outlook plug-in for the same functionalities as Communicator ...

WPF Application -- The Glass store example ...
Business process scenarios ...
Microsoft Dynamics - the CRM has it's own integration UC plug-in

Office Communicator 2007 R2 SDK
msdn download samples

MVC introduction at Tech-Ed Barcelona

http://www.lostintangent.com/

MVC patter representation on top of ASP.NET
MVC pattern

View (Presentation ) Model ( Logic ) Controller (Input)


Unit testing ... at the whole of the code ..
MVC is Extensible ... ( parts of it are replacable .. )
Default implementation ( it just works by convention .. )
yet you can configure and change it later on ...

Creating default MVC application
download the MVC .Net
Project - New - ASP.NET MVC Web Application
System - create unit tests
User - Yes
Test Framework -- Visual STudio Unit test ( other also available one can create their own too )
The application is creating sample controller , sampl tests .
Project Structure -
Controllers - by convention ... put those here
Models -
Views -

Routing - URL pattern and map that to controllers ...
public static void RgisterRoutes (Routecollertion

by default the controller classes end up with the name Controller ...

The controller is primarily for receiving input ...

Well , the testability is the best feature ... but in is in Beta still and does not support complicated controls ...

View open connections in sql server 2005

SOURCES:
http://www.sqlservercentral.com/scripts/Lock+and+Connection+Management/64433/
USE SQL_PRACTICE
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('dbo.Util_ConnectionSummary') IS NOT NULL DROP PROCEDURE Util_ConnectionSummary
GO

/**
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_ConnectionSummary
By Jesse Roberge - YeshuaAgapao@Yahoo.com

Reports summaries of connections, running requests, open transactions, open cursors, and blocking at 3 different levels of aggregation detail, ranking trouble groups first.
Most useful for finding SPIDs thare being hoggy right now - activity monitor gives session-scoped resource consumption, this aggregates active request scoped resource consumption.
Also useful for quickly finding blocking offenders and finding programs that are not closing cursors or transactions.
Returns 3 result sets:
Server-wide Total / Summary (No Group By)
Connections and requests grouped by LoginName, HostName, Programname
Connections and requests grouped by SessionID
Orders by ActiveReqCount DESC, OpenTranCount DESC, BlockingRequestCount DESC, BlockedReqCount DESC, ConnectionCount DESC, {group by column(s)}

Required Input Parameters
none

Optional Input Parameters
none

Usage:
EXECUTE Util_ConnectionSummary

Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagarize, conceal the source, or prohibit copying & re-distribution of this script/proc.

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as
published by the Free Software Foundation, either version 3 of the
License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU Lesser General Public License for more details.

see for the license text.

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
**/

CREATE PROCEDURE dbo.Util_ConnectionSummary AS

--All connections
SELECT
SUM(ConnectionCount) AS ConnectionCount,
SUM(CONVERT(bigint, ISNULL(dm_tran_session_transactions.TransactionCount,0))) AS OpenTranCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.OpenCursorCount,0))) AS OpenCursorCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.ClosedCursorCount,0))) AS ClosedCursorCount,
ISNULL(SUM(dm_exec_blockrequests.BlockingRequestCount),0) AS BlockingRequestCount,
SUM(dm_exec_requests.ActiveReqCount) AS ActiveReqCount,
SUM(dm_exec_requests.open_resultset_count) AS OpenResultSetCount,
SUM(dm_exec_requests.open_transaction_count) AS ActiveReqOpenTranCount,
SUM(dm_exec_requests.BlockedReqCount) AS BlockedReqCount,
SUM(dm_exec_requests.wait_time) AS WaitTime,
SUM(dm_exec_requests.cpu_time) AS CPUTime,
SUM(dm_exec_requests.total_elapsed_time) AS ElapsedTime,
SUM(dm_exec_requests.reads) AS Reads,
SUM(dm_exec_requests.writes) AS Writes,
SUM(dm_exec_requests.logical_reads) AS LogicalReads,
SUM(dm_exec_requests.row_count) AS [RowCount],
SUM(dm_exec_requests.granted_query_memory) AS GrantedQueryMemoryKB
FROM
sys.dm_exec_sessions
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS ConnectionCount FROM sys.dm_exec_connections GROUP BY session_id
) AS dm_exec_connections ON sys.dm_exec_sessions.session_id=dm_exec_connections.session_id
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS TransactionCount FROM sys.dm_tran_session_transactions GROUP BY session_id
) AS dm_tran_session_transactions ON sys.dm_exec_sessions.session_id=dm_tran_session_transactions.session_id
LEFT OUTER JOIN (
SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FROM sys.dm_exec_requests GROUP BY blocking_session_id
) AS dm_exec_blockrequests ON sys.dm_exec_sessions.session_id=dm_exec_blockrequests.blocking_session_id
LEFT OUTER JOIN (
SELECT session_id, SUM(CASE WHEN is_open=1 THEN 1 ELSE 0 END) AS OpenCursorCount, SUM(CASE WHEN is_open=0 THEN 1 ELSE 0 END) AS ClosedCursorCount
FROM sys.dm_exec_cursors (0)
GROUP BY session_id
) AS dm_exec_cursors ON sys.dm_exec_sessions.session_id=dm_exec_cursors.session_id
LEFT OUTER JOIN (
SELECT
session_id,
SUM(CONVERT(bigint, open_transaction_count)) AS open_transaction_count,
SUM(CONVERT(bigint, open_resultset_count)) AS open_resultset_count,
SUM(CASE WHEN total_elapsed_time IS NULL THEN 0 ELSE 1 END) AS ActiveReqCount,
SUM(CASE WHEN blocking_session_id <> 0 THEN 1 ELSE 0 END) AS BlockedReqCount,
SUM(CONVERT(bigint, wait_time)) AS wait_time,
SUM(CONVERT(bigint, cpu_time)) AS cpu_time,
SUM(CONVERT(bigint, total_elapsed_time)) AS total_elapsed_time,
SUM(CONVERT(bigint, reads)) AS Reads,
SUM(CONVERT(bigint, writes)) AS Writes,
SUM(CONVERT(bigint, logical_reads)) AS logical_reads,
SUM(CONVERT(bigint, row_count)) AS row_count,
SUM(CONVERT(bigint, granted_query_memory*8)) AS granted_query_memory
FROM sys.dm_exec_requests
GROUP BY session_id
) AS dm_exec_requests ON sys.dm_exec_sessions.session_id=dm_exec_requests.session_id
WHERE sys.dm_exec_sessions.is_user_process=1

--Connections by LoginName, Hostname, and ProgramName
SELECT
sys.dm_exec_sessions.login_name, sys.dm_exec_sessions.host_name, sys.dm_exec_sessions.program_name,
SUM(ConnectionCount) AS ConnectionCount,
SUM(CONVERT(bigint, ISNULL(dm_tran_session_transactions.TransactionCount,0))) AS OpenTranCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.OpenCursorCount,0))) AS OpenCursorCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.ClosedCursorCount,0))) AS ClosedCursorCount,
ISNULL(SUM(dm_exec_blockrequests.BlockingRequestCount),0) AS BlockingRequestCount,
SUM(dm_exec_requests.ActiveReqCount) AS ActiveReqCount,
SUM(dm_exec_requests.open_resultset_count) AS OpenResultSetCount,
SUM(dm_exec_requests.open_transaction_count) AS ActiveReqOpenTranCount,
SUM(dm_exec_requests.BlockedReqCount) AS BlockedReqCount,
SUM(dm_exec_requests.wait_time) AS WaitTime,
SUM(dm_exec_requests.cpu_time) AS CPUTime,
SUM(dm_exec_requests.total_elapsed_time) AS ElapsedTime,
SUM(dm_exec_requests.reads) AS Reads,
SUM(dm_exec_requests.writes) AS Writes,
SUM(dm_exec_requests.logical_reads) AS LogicalReads,
SUM(dm_exec_requests.row_count) AS [RowCount],
SUM(dm_exec_requests.granted_query_memory) AS GrantedQueryMemoryKB
FROM
sys.dm_exec_sessions
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS ConnectionCount FROM sys.dm_exec_connections GROUP BY session_id
) AS dm_exec_connections ON sys.dm_exec_sessions.session_id=dm_exec_connections.session_id
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS TransactionCount FROM sys.dm_tran_session_transactions GROUP BY session_id
) AS dm_tran_session_transactions ON sys.dm_exec_sessions.session_id=dm_tran_session_transactions.session_id
LEFT OUTER JOIN (
SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FROM sys.dm_exec_requests GROUP BY blocking_session_id
) AS dm_exec_blockrequests ON sys.dm_exec_sessions.session_id=dm_exec_blockrequests.blocking_session_id
LEFT OUTER JOIN (
SELECT session_id, SUM(CASE WHEN is_open=1 THEN 1 ELSE 0 END) AS OpenCursorCount, SUM(CASE WHEN is_open=0 THEN 1 ELSE 0 END) AS ClosedCursorCount
FROM sys.dm_exec_cursors (0)
GROUP BY session_id
) AS dm_exec_cursors ON sys.dm_exec_sessions.session_id=dm_exec_cursors.session_id
LEFT OUTER JOIN (
SELECT
session_id,
SUM(CONVERT(bigint, open_transaction_count)) AS open_transaction_count,
SUM(CONVERT(bigint, open_resultset_count)) AS open_resultset_count,
SUM(CASE WHEN total_elapsed_time IS NULL THEN 0 ELSE 1 END) AS ActiveReqCount,
SUM(CASE WHEN blocking_session_id <> 0 THEN 1 ELSE 0 END) AS BlockedReqCount,
SUM(CONVERT(bigint, wait_time)) AS wait_time,
SUM(CONVERT(bigint, cpu_time)) AS cpu_time,
SUM(CONVERT(bigint, total_elapsed_time)) AS total_elapsed_time,
SUM(CONVERT(bigint, reads)) AS Reads,
SUM(CONVERT(bigint, writes)) AS Writes,
SUM(CONVERT(bigint, logical_reads)) AS logical_reads,
SUM(CONVERT(bigint, row_count)) AS row_count,
SUM(CONVERT(bigint, granted_query_memory*8)) AS granted_query_memory
FROM sys.dm_exec_requests
GROUP BY session_id
) AS dm_exec_requests ON sys.dm_exec_sessions.session_id=dm_exec_requests.session_id
WHERE sys.dm_exec_sessions.is_user_process=1
GROUP BY sys.dm_exec_sessions.login_name, sys.dm_exec_sessions.host_name, sys.dm_exec_sessions.program_name
ORDER BY
ActiveReqCount DESC, OpenTranCount DESC, BlockingRequestCount DESC, BlockedReqCount DESC, ConnectionCount DESC,
sys.dm_exec_sessions.login_name, sys.dm_exec_sessions.host_name, sys.dm_exec_sessions.program_name

--Connections by session_id
SELECT
sys.dm_exec_sessions.session_id,
MAX(sys.dm_exec_sessions.login_name) AS login_name, MAX(sys.dm_exec_sessions.host_name) AS host_name,
MAX(sys.dm_exec_sessions.program_name) AS program_name, MAX(sys.dm_exec_sessions.client_interface_name) AS client_interface_name,
MAX(sys.dm_exec_sessions.status) AS status,
SUM(ConnectionCount) AS ConnectionCount,
SUM(CONVERT(bigint, ISNULL(dm_tran_session_transactions.TransactionCount,0))) AS OpenTranCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.OpenCursorCount,0))) AS OpenCursorCount,
SUM(CONVERT(bigint, ISNULL(dm_exec_cursors.ClosedCursorCount,0))) AS ClosedCursorCount,
ISNULL(SUM(dm_exec_blockrequests.BlockingRequestCount),0) AS BlockingRequestCount,
SUM(dm_exec_requests.ActiveReqCount) AS ActiveReqCount,
SUM(dm_exec_requests.open_resultset_count) AS OpenResultSetCount,
SUM(dm_exec_requests.open_transaction_count) AS ActiveReqOpenTranCount,
SUM(dm_exec_requests.BlockedReqCount) AS BlockedReqCount,
SUM(dm_exec_requests.wait_time) AS WaitTime,
SUM(dm_exec_requests.cpu_time) AS CPUTime,
SUM(dm_exec_requests.total_elapsed_time) AS ElapsedTime,
SUM(dm_exec_requests.reads) AS Reads,
SUM(dm_exec_requests.writes) AS Writes,
SUM(dm_exec_requests.logical_reads) AS LogicalReads,
SUM(dm_exec_requests.row_count) AS [RowCount],
SUM(dm_exec_requests.granted_query_memory) AS GrantedQueryMemoryKB
FROM
sys.dm_exec_sessions
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS ConnectionCount FROM sys.dm_exec_connections GROUP BY session_id
) AS dm_exec_connections ON sys.dm_exec_sessions.session_id=dm_exec_connections.session_id
LEFT OUTER JOIN (
SELECT session_id, COUNT(*) AS TransactionCount FROM sys.dm_tran_session_transactions GROUP BY session_id
) AS dm_tran_session_transactions ON sys.dm_exec_sessions.session_id=dm_tran_session_transactions.session_id
LEFT OUTER JOIN (
SELECT blocking_session_id, COUNT(*) AS BlockingRequestCount FROM sys.dm_exec_requests GROUP BY blocking_session_id
) AS dm_exec_blockrequests ON sys.dm_exec_sessions.session_id=dm_exec_blockrequests.blocking_session_id
LEFT OUTER JOIN (
SELECT session_id, SUM(CASE WHEN is_open=1 THEN 1 ELSE 0 END) AS OpenCursorCount, SUM(CASE WHEN is_open=0 THEN 1 ELSE 0 END) AS ClosedCursorCount
FROM sys.dm_exec_cursors (0)
GROUP BY session_id
) AS dm_exec_cursors ON sys.dm_exec_sessions.session_id=dm_exec_cursors.session_id
LEFT OUTER JOIN (
SELECT
session_id,
SUM(CONVERT(bigint, open_transaction_count)) AS open_transaction_count,
SUM(CONVERT(bigint, open_resultset_count)) AS open_resultset_count,
SUM(CASE WHEN total_elapsed_time IS NULL THEN 0 ELSE 1 END) AS ActiveReqCount,
SUM(CASE WHEN blocking_session_id <> 0 THEN 1 ELSE 0 END) AS BlockedReqCount,
SUM(CONVERT(bigint, wait_time)) AS wait_time,
SUM(CONVERT(bigint, cpu_time)) AS cpu_time,
SUM(CONVERT(bigint, total_elapsed_time)) AS total_elapsed_time,
SUM(CONVERT(bigint, reads)) AS Reads,
SUM(CONVERT(bigint, writes)) AS Writes,
SUM(CONVERT(bigint, logical_reads)) AS logical_reads,
SUM(CONVERT(bigint, row_count)) AS row_count,
SUM(CONVERT(bigint, granted_query_memory*8)) AS granted_query_memory
FROM sys.dm_exec_requests
GROUP BY session_id
) AS dm_exec_requests ON sys.dm_exec_sessions.session_id=dm_exec_requests.session_id
WHERE sys.dm_exec_sessions.is_user_process=1
GROUP BY sys.dm_exec_sessions.session_id
ORDER BY
ActiveReqCount DESC, OpenTranCount DESC, BlockingRequestCount DESC, BlockedReqCount DESC, ConnectionCount DESC,
login_name, program_name, host_name, session_id
GO

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

/*
USAGE
EXEC Util_ConnectionSummary

*/

backup all user defined

SOURCES:
http://www.sqlservercentral.com/articles/Backup/64454/
-- command for taking backup of userdefined databases
DECLARE @cursor AS CURSOR
DECLARE @dbname AS VARCHAR(20),
@query AS VARCHAR(100)
SET @cursor = CURSOR SCROLL FOR
SELECT NAME FROM MASTER..Sysdatabases
WHERE NAME NOT IN ('master', 'model','msdb', 'tempdb')
OPEN @cursor
FETCH NEXT FROM @cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @query = 'BACKUP DATABASE '+ @dbname +' TO DISK = ''C:\backup\'+ @dbname+'.bak '' WITH INIT'
EXEC(@query)
FETCH NEXT FROM @cursor INTO @dbname
END
CLOSE @cursor
DEALLOCATE @cursor

backup all system databases in

SOURCES:
http://www.sqlservercentral.com/articles/Backup/64454/
-- command for taking backup of system databases
DECLARE @cursor AS CURSOR
DECLARE @dbname AS VARCHAR(20),
@query AS VARCHAR(100)
SET @cursor = CURSOR SCROLL FOR
SELECT NAME FROM MASTER..sysdatabases WHERE NAME IN('master', 'model','msdb')
OPEN @cursor
FETCH NEXT FROM @cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @query = 'BACKUP DATABASE '+ @dbname +' TO DISK = ''D:\temp\'+@dbname+'.bak '' WITH INIT'
EXEC(@query)
FETCH NEXT FROM @cursor INTO @dbname
END
CLOSE @cursor
DEALLOCATE @cursor

sql server 2005 cheat sheet as a

alter procedure procUtils_SqlCheatSheet

as
set nocount on

--what was the name of the table with something like role
/*
SELECT * from sys.tables where [name] like '%role%'
*/
--what was the name of procedure with something like role
/*
select * from sys.procedures where [name] like '%role%'
*/
/*
exec sp_helpText Sec_Add_User_Role
*/
--how to list all databases in sql server
/*
SELECT database_id AS ID, NULL AS ParentID, name AS Text FROM sys.databases ORDER BY [name]
*/

--HOW-TO LIST ALL TABLES IN A SQL SERVER 2005 DATABASE
/*
SELECT TABLE_NAME FROM [POC].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'dtproperties'
ORDER BY TABLE_NAME


*/
--HOW-TO ENABLE XP_CMDSHELL START
-------------------------------------------------------------------------
-- configure verbose mode temporarily
-- EXECUTE sp_configure 'show advanced options', 1
-- RECONFIGURE WITH OVERRIDE
--GO


--ENABLE xp_cmdshell
-- EXECUTE sp_configure 'xp_cmdshell', '1'
-- RECONFIGURE WITH OVERRIDE
-- EXEC SP_CONFIGURE 'show advanced option', '1';
-- SHOW THE CONFIGURATION
-- EXEC SP_CONFIGURE;


--turn show advance options off
-- GO
--EXECUTE sp_configure 'show advanced options', 0
-- RECONFIGURE WITH OVERRIDE
-- GO

--HOW-TO ENABLE XP_CMDSHELL END
-------------------------------------------------------------------------

--HOW-TO IMPLEMENT SLEEP
-- sleep for 10 seconds
-- WAITFOR DELAY '00:00:10' SELECT * FROM My_Table

/* LIST ALL PRIMARY KEYS

SELECT
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME AS TABLE_NAME,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME AS COLUMN_NAME,
REPLACE(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE,' ', '_') AS CONSTRAINT_TYPE
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
WHERE
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME <> N'sysdiagrams'
ORDER BY
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME ASC

*/

--HOW-TO COPY TABLE AND THE WHOLE TABLE DATA , COPY TABLE FROM DB TO DB
--==================================================START
/*
use Poc_Dev
go
drop table tbGui_LinksVisibility

use POc_test
go
select *
INTO [POC_Dev].[dbo].[tbGui_LinksVisibility]
from [POC_TEST].[dbo].[tbGui_LinksVisibility]


*/
--HOW-TO COPY TABLE AND THE WHOLE TABLE DATA , COPY TABLE FROM DB TO DB
--====================================================END
--=================================================== SEE TABLE METADATA START
/*



SELECT c.name AS [COLUMN_NAME], sc.data_type AS [DATA_TYPE], [value] AS
[DESCRIPTION] , c.max_length as [MAX_LENGTH] , c.is_nullable AS [OPTIONAL]
, c.is_identity AS [IS_PRIMARY_KEY] FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id
= c.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and
c.name = sc.column_name
WHERE class = 1 and t.name = 'PutHereYourTableName' ORDER BY SC.DATA_TYPE


*/
--=================================================== SEE TABLE METADATA END

--=================================================== LIST ALL TABLES AND THEIR DESCRIPTOINS START
/*

SELECT T.name AS TableName, CAST(Props.value AS varchar(1000)) AS
TableDescription
FROM sys.tables AS T LEFT OUTER JOIN
(SELECT class, class_desc, major_id, minor_id,
name, value
FROM sys.extended_properties
WHERE (minor_id = 0) AND (class = 1)) AS
Props ON T.object_id = Props.major_id
WHERE (T.type = 'U') AND (T.name <> N'sysdiagrams')
ORDER BY TableName
*/
--=================================================== LIST ALL TABLES AND THEIR DESCRIPTOINS START

--=================================================== LIST ALL OBJECTS FROM DB START /*


/*
--HOW-TO LIST ALL PROCEDURE IN A DATABASE
select s.name from sysobjects s where type = 'P'
--HOW-TO LIST ALL TRIGGERS BY NAME IN A DATABASE
select s.name from sysobjects s where type = 'TR'
--HOW-TO LIST TABLES IN A DATABASE
select s.name from sysobjects s where type = 'U'
--how-to list all system tables in a database
select s.name from sysobjects s where type = 's'
--how-to list all the views in a database
select s.name from sysobjects s where type = 'v'
*/



/*
Similarly you can find out other objects created by user, simple change type =

C = CHECK constraint

D = Default or DEFAULT constraint

F = FOREIGN KEY constraint

L = Log

FN = Scalar function

IF = In-lined table-function

P = Stored procedure

PK = PRIMARY KEY constraint (type is K)

RF = Replication filter stored procedure

S = System table

TF = Table function

TR = Trigger

U = User table ( this is the one I discussed above in the example)

UQ = UNIQUE constraint (type is K)

V = View

X = Extended stored procedure
*/



--=================================================== HOW-TO SEE ALL MY PERMISSIONS START


/*

SELECT * FROM fn_my_permissions(NULL, 'SERVER');
USE poc_qa;
SELECT * FROM fn_my_permissions (NULL, 'database');
GO

*/
--=================================================== HOW-TO SEE ALL MY PERMISSIONS END
/*
EXEC sys.sp_addextendedproperty @name=N'DecriptionKey', @value=N'DescriptionValue' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Action_Log'
*/

-- HOW-TO BACK-UP DATA BASE TO A FILE
/*

BACKUP DATABASE SQL_PRACTICE TO DISK = 'D:\DATA\dbbackup.bak' WITH INIT


*/

--HOW-TO GET TABLE COLUMN META DATA

/*


select object_name(c.object_id) "TABLE_NAME", c.name "COLUMN_NAME", s.name "DATA_TYPE" , c.max_length "MAX_LENGTH" , c.is_nullable "IS_NULLABLE", c.is_identity "IS_PRIMARYKEY" , c.precision "PRECISION"
from sys.columns c
join sys.systypes s on (s.xtype = c.system_type_id)
where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')
AND object_name(c.object_id) in (select name from sys.tables where name like 'PRS_Action_tb')


*/
set nocount off