BCP QUERYOUT WITH FIRSTROW, LASTROW, BATCHSIZE ETC

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

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

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

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

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

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

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

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

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

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

-- 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

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

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

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

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

Satellite- Direct

Restore Database Script

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

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

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

Random Number Generation

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

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)

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

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

Remote Server Administration Tools for Windows 7

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

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

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

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

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

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

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:

• Part 1: Service and Master Keys
• Part 2: Certificates
• Part 3: Symmetric Keys

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

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

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