Excel:Sorting Excel Sheets using VB Macro

Sub SortSheets()

Dim i As Integer, j As Integer
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move after:=Sheets(j + 1)
End If
Next j
Next i
End Sub

Cleaning Phone Numbers using CTE

Cleaning Phone Numbers using the Common Table Expressions

Declare
@Str1 VarChar(max),
@Str2 VarChar(max);
Select
@Str1='(208)*/ 555-1212',
@Str2='';

With PhoneClean as
(
Select
 Case
  when SubString(@Str1,1,1) like '[0-9]' then SubString(@Str1,1,1)
  else ''
 End[Chr],
 1[Idx]
Union All
Select
 Case
  when SubString(@Str1,Idx+1,1) like '[0-9]' then SubString(@Str1,Idx+1,1)
  else ''
 End,
 Idx+1
from PhoneClean
where (Idx+1)<=Len(@Str1) ) Select  @Str2=@Str2+Chr from PhoneClean option (MaxRecursion 0); Select  @Str2; 

Source:www.sqlservercentral.com  

Getting the ForeignKeys Base and Reference Tables in a Database

select d.name as fk,b.name as base,c.name as ref from sysforeignkeys a, sysobjects b, sysobjects c,sysobjects d
where b.id=a.fkeyid
and c.id=a.rkeyid
and d.id=a.constid

Sending Email in SSIS without using the Sendmail Task

Using the System.Web.Mail To send an attachment along with the email

Imports System.Web.Mail

Public Sub Main()

Dim message As MailMessage = New System.Web.Mail.MailMessage()
Dim Att As String

Att = Dts.Variables("Attachments").Value.ToString
Att = Att & Now.Day.ToString & Now.Month.ToString & Now.Year.ToString & ".xls"

'MsgBox(Att.ToString)

message.From = ""
message.To = ""
message.Subject = "Abandoned Emails " & Now.Day.ToString & Now.Month.ToString & Now.Year.ToString

Dim oattach As MailAttachment = New MailAttachment(Att)
message.Attachments.Add(oattach)

SmtpMail.SmtpServer = ""
SmtpMail.Send(message)

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

Who receives the email when a job fails?

Here is a script that gives you the list of operators name who receive notifications when a job fails

SELECT sysoperators.name AS JobName, sysjobs.name AS OperatorName, sysjobs.enabled, sysoperators.id
FROM sysjobs INNER JOIN
sysoperators ON sysjobs.notify_email_operator_id = sysoperators.id


You need to change the sysjobs.notify_email_operator_id to change the Notifications of the Operator.

When the Procedures were last modified

SELECT name, create_date, datepart(dy,create_date) as CreatedDayOfYear,
modify_date, datepart(dy,modify_date) as ModificationDayOfYear
FROM sys.sql_modules
JOIN sys.objects
ON sys.sql_modules.object_id = sys.objects.object_id
AND TYPE = 'P'
order by datepart(yyyy,modify_date) desc,
datepart(dy,modify_date) desc, name;

SSMS tools Pack

I found this new tool pack on Google Search...

http://www.ssmstoolspack.com/


Quite Useful for automating certain things on SQL SERVER

and this is how to create addins on SQL SERVER

http://aspalliance.com/1374_Extend_Functionality_in_SQL_Server_2005_Management_Studio_with_Addins.all

Executing the Same Query across all Databases

Eg:

Declare @sql varchar(1000)

set @Sql = 'select * from Contacts'

select 'use ' + name +
' ' + @sql from master.dbo.sysdatabases where dbid > 4


Copy the Statements to a new window and execute it.

Backup Report for the Server

Reports the ServerName , Database Name and LastBackupDate


select Convert(Varchar(25),server_name) as 'Server Name'
,convert(varchar(25),database_name) as 'Database'
,getdate() as reportdate, max(backup_finish_date) as 'Last_backup_date'
from msdb..backupset
Where database_name
in
(select name from master..sysdatabases)
and server_name = @@servername
group by server_name,database_name order by Last_backup_date DESC

Script for Changing the Job Owners across all the Jobs

Change the Owner of all the SQL Agent Jobs using the Following Script


SELECT 'EXEC MSDB.dbo.sp_update_job ' + char(13) +
'@job_name = ' + char(39) + j.[Name] + char(39) + ',' + char(13) +
'@owner_login_name = ' + char(39) + 'sa' + char(39) + char(13) + char(13)
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
WHERE l.[name] <> 'sa'
ORDER BY j.[name]

Using sp_send_dbmail in HTML format

Using sp_send_dbmail , the data in sql server can be sent in HTML format.

Eg:

I have a HTML file in the follwing format


and is saved as web.htm in a network location.

Here is the TSQL script that will send an email to a recipient in the HTML format using web.htm

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


declare @cmdshell varchar(1000)

if exists(select * from #cmdtable)
drop table #cmdtable

create table #CMDTABLE(line varchar(2000))

insert into #cmdtable
exec xp_cmdshell 'type "\\Your Network Share\web.htm" '

select @cmdshell = line from #CMDTABLE


exec msdb.dbo.sp_send_dbmail
@recipients = 'your email address'
,@body = @cmdshell
,@body_format = 'HTML'
,@subject = 'Testing with web.txt'

can use the same stuff for attaching a picture on the HTML page