Alternative Color in the SSRS Report

=iif(rownumber(nothing) mod 2, "Gainsboro","Transparent")

Dynamic date in Connection string in the SSIS package

RIGHT((DT_STR,4,1252)YEAR(DATEADD("dd",0,getdate())),4) 
+""+ RIGHT("0"  +(DT_STR,4,1252)MONTH(DATEADD("dd",-1,getdate())),2)
+""+ RIGHT("0" +(DT_STR,4,1252)DAY(DATEADD("dd",0,getdate())) ,2) 

How to display Line nos in the SQL Server Report

=rownumber(nothing).ToString+"."

How to find out SQL Server Details

exec xp_msver
GO


exec xp_msver 'ProcessorCount'
GO


exec xp_msver 'ProductVersion', 'Platform'
GO

How to know Job History in SQL Server

USE MSDB
GO


DECLARE @JOBID VARCHAR (200)
SELECT @JOBID = 'CE2E392B-696E-4AF3-92D9-77F471CA424B'


SELECT J.JOB_ID, J.NAME,J.DESCRIPTION,J.DATE_CREATED AS CREATEDON, J.DATE_MODIFIED AS MODIFIEDON, 
JA.SESSION_ID AS SESSIONID, JA.RUN_REQUESTED_DATE AS [RUN REQUESTED DATE], JA.START_EXECUTION_DATE AS [EXECUTION DATE TIME], 
JA.LAST_EXECUTED_STEP_DATE AS [LAST EXECUTED DATE TIME], JA.STOP_EXECUTION_DATE AS [STOP EXECUTION DATE TIME], 
JA.NEXT_SCHEDULED_RUN_DATE AS [NEXT SCHEDULED],JH.INSTANCE_ID, JH.STEP_NAME AS [STEP NAME], JH.MESSAGE, 
JH.RUN_STATUS AS [STEP RUN STATUS], JH.RUN_DATE AS [STEP RUN DATE], JH.RUN_TIME AS [STEP RUN TIME], 
JH.RUN_DURATION, JH.SERVER AS [EXECUTED ON SERVER], JS.SCHEDULE_ID, JS.NEXT_RUN_DATE, JS.NEXT_RUN_TIME,
JSS.LAST_OUTCOME_MESSAGE, JSS.LAST_RUN_DURATION,JST.STEP_NAME, JST.DATABASE_NAME
FROM SYSJOBS J 
INNER JOIN SYSJOBACTIVITY JA ON J.JOB_ID= JA.JOB_ID
INNER JOIN SYSJOBHISTORY JH ON J.JOB_ID= JH.JOB_ID
INNER JOIN SYSJOBSCHEDULES JS ON J.JOB_ID= JS.JOB_ID
INNER JOIN SYSJOBSERVERS JSS ON J.JOB_ID= JSS.JOB_ID
INNER JOIN SYSJOBSTEPS JST ON J.JOB_ID= JST.JOB_ID
WHERE J.JOB_ID= @JOBID

SQL Query to find out Service Account details

DECLARE @ServiceaccountName varchar(250) 


EXECUTE master.dbo.xp_instance_regread 
N'HKEY_LOCAL_MACHINE', 
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', 
N'ObjectName', 
@ServiceAccountName OUTPUT, 
N'no_output'
  
SELECT @ServiceaccountName 

How to Shrink database in SQL Server

--Shrink Log


if object_id('tempdb.dbo.#file_info','u') is not null
drop table tempdb.dbo.#file_info


create table #file_info
(
dbname varchar(255),
logical_name varchar(255),
physical_name varchar(255)
)


insert into #file_info (dbname)
select '['+name+']' from master..sysdatabases where name not in ('Database1','Database2')


declare @sql varchar(2000), @dbname varchar(255)
declare c_1 cursor for select distinct dbname from #file_info
open c_1
fetch next from c_1 into @dbname
while @@fetch_status <> -1
begin
select @sql = 'update #file_info 
set physical_name = filename, logical_name = name
from '+@dbname+'..sysfiles 
where dbname = '''+@dbname+''' 
and filename like ''%.ldf'''


exec (@sql)


fetch next from c_1 into @dbname
end
close c_1
deallocate c_1


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


declare @shrink_sql varchar(2000),
@shrink_dbname varchar(255),
@shrink_logical_name varchar(255)
declare c_2 cursor for select dbname,logical_name from #file_info
open c_2
fetch next from c_2 into @shrink_dbname,@shrink_logical_name
while @@fetch_status <> -1
begin
select @shrink_sql = '
use '+@shrink_dbname+'
dbcc shrinkfile ('+'['+@shrink_logical_name+']'+', notruncate)
dbcc shrinkfile ('+'['+@shrink_logical_name+']'+', truncateonly)
'
exec (@shrink_sql)
fetch next from c_2 into @shrink_dbname,@shrink_logical_name
end
close c_2
deallocate c_2
go

How to Check File Existance in the SSIS package

SET
--Read only Variables:
--User::SourceFileExtension,User::SourceFileName,User::SourceFilePath
--ReadWrite Variables:
--User::bolFileExists






' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO


_
_
 Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase


    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum




    Public Sub Main()
        Dim fileLoc, fileName, fileExtension, FileDate As String
        If Dts.Variables.Contains("User::SourceFilePath") = True AndAlso _
           Dts.Variables.Contains("User::SourceFileName") = True AndAlso _
            Dts.Variables.Contains("User::SourceFileExtension") = True Then
            fileLoc = CStr(Dts.Variables("User::SourceFilePath").Value)
            fileName = CStr(Dts.Variables("User::SourceFileName").Value)
            FileDate = Now.Date.ToString("yyyy") & Now.Date.ToString("MM") & Now.Date.ToString("dd")
            fileExtension = CStr(Dts.Variables("User::SourceFileExtension").Value)
            If File.Exists(fileLoc + fileName + FileDate + fileExtension) Then
                Dts.Variables.Item("User::bolFileExists").Value = True
                Dts.TaskResult = ScriptResults.Success
            Else
                Dts.Variables.Item("User::bolFileExists").Value = False
                Dts.TaskResult = ScriptResults.Failure
            End If
            'Dts.TaskResult = ScriptResults.Success
            ' Else
            ' Dts.TaskResult = ScriptResults.Failure
        End If
    End Sub


End Class

ReIndexing of all the tables in the selected SQL DB

DECLARE @SQL VARCHAR(500),
@TBLNAME VARCHAR(100)


DECLARE C_1 CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U'
OPEN C_1
FETCH NEXT FROM C_1 INTO @TBLNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @SQL = 'DBCC DBREINDEX ('''+@TBLNAME+''','''',90)'
EXEC (@SQL)
INSERT INTO REINDEX_AUDIT (INDEX_NAME,START)
VALUES (@TBLNAME,GETDATE())


FETCH NEXT FROM C_1 INTO @TBLNAME
END
CLOSE C_1
DEALLOCATE C_1


GO