Temporary table dropping

IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb..#temp1')
)
BEGIN
drop table #temp1
END

SQL Reports Parameters

Report Parameters

Next week From date :

=CDate(FormatDateTime(DateAdd("ww",1,DateAdd("d",-(DatePart("w",NOW)-2),NOW)),2))

Next week To date:

=CDate(FormatDateTime(DateAdd("ww",1, DateAdd("d",4, DateAdd("d",-(DatePart("w",NOW)-2),NOW) )) ,2))

Current week From date:

=CDate(FormatDateTime(DateAdd("d",-(DatePart("w",NOW)-2),NOW),2))

Current week to date:

=CDate(FormatDateTime(DateAdd("d",4,DateAdd("d",-(DatePart("w",NOW)-2),NOW)),2))

access to linked server

Linked server syntax
insert into [xxx.xxx.x.xx].bmsus.dbo.bimsus_AccountsReceivable_Outbound_Data
select * from bimsus_AccountsReceivable_Outbound_Data

SQL Large log file size

There are instance where the log files have grown up too large for example 200 gb of file size, in that case, here are the following steps to be done to reduce the log file.
1.Check the database options if it is simple change it to Full.
2. Backup the database using the below script.
--****************************************************
--Backup database script
--****************************************************
BACKUP DATABASE [lmkdw] TO
DISK = N'd:\lmkdw_backup.bak' WITH NOFORMAT,
INIT, NAME = N'lmkdw_backup', SKIP, REWIND, NOUNLOAD,
STATS = 10
3. Then backup the log file using the below script
--****************************************************
--Backup database script
--****************************************************
BACKUP LOG [apacdw] TO DISK = N'd:\lmkdw.trn'
WITH NOFORMAT, INIT, NAME = N'lmkdw-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
go
4. Now shrink the log file using the below script
DBCC SHRINKFILE (N'lmkdw_Log' , 786)
5. Finall turn the database options back to simple or based on your needs.

Robocopy Tool

Robocopy is a awesome tool to copy backup files. Here is the syntax to copy files.
robocopy "Z:\DATABASE\FULLBACKUP" "\\xxx.xxx.x.xxx\backups\FullBackup" /zb /copyall /purge /MIR

robocopy "source" "destination"

Copy options :
/S :: copy Subdirectories, but not empty ones./E :: copy subdirectories, including Empty ones./LEV:n :: only copy the top n LEVels of the source directory tree.
/Z :: copy files in restartable mode./B :: copy files in Backup mode./ZB :: use restartable mode; if access denied use Backup mode./EFSRAW :: copy all encrypted files in EFS RAW mode.
/COPY:copyflag[s] :: what to COPY for files (default is /COPY:DAT).(copyflags : D=Data, A=Attributes, T=Timestamps).(S=Security=NTFS ACLs, O=Owner info, U=aUditing info).
/DCOPY:T :: COPY Directory Timestamps.
/SEC :: copy files with SECurity (equivalent to /COPY:DATS)./COPYALL :: COPY ALL file info (equivalent to /COPY:DATSOU)./NOCOPY :: COPY NO file info (useful with /PURGE).
/SECFIX :: FIX file SECurity on all files, even skipped files./TIMFIX :: FIX file TIMes on all files, even skipped files.
/PURGE :: delete dest files/dirs that no longer exist in source./MIR :: MIRror a directory tree (equivalent to /E plus /PURGE).
/MOV :: MOVe files (delete from source after copying)./MOVE :: MOVE files AND dirs (delete from source after copying).

Copying folders, subfolders and files

Copying folders, subfolders and files from one location to another and over writing it.Very useful for backing up of files.

Const OverWriteFiles = TrueSet objFSO = CreateObject("Scripting.FileSystemObject")objFSO.CopyFolder "c:\vijay\t1" , "c:\vijay\t2" , OverWriteFiles
set objfso = nothing

set date field null

to set the date field null
update table name set date = null