Tagged: sql server 2005

Server TCP provider failed to listen on [ ‘any’ 1433]. Tcp port is already in use.

Issue – Today I faced very weird issue; on multi instance SQL Server machine, couple of SQL Services was not starting after Server reboot ( In my case it was SQL 2014 and SQL 2016 ) and every time I was trying to restart my SQL Services, I was getting below error message –

 

1

Solutions – On error message it’s look like some other processes was trying to use SQL default port 1433.

Hence it was not allowing to restart SQL Services with Port 1433.

On my server, we have multiple SQL Services installed ( SQL 2008 to SQL vNext ) hence I started checking if any of these SQL Server have assigned static port 1433.

I start checking assigned port for all SQL Server and I found SQL Server 2014 and SQL Server 2016 was having assigned static port 1433.

In order to resolve the issue, I removed static port entry and restart my SQL Services and it started successfully.

2

34

Advertisements

The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context

My Development team contacted me and said they are not able to access database  “The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context”

when I looked into server server I found –

  1. Login was there and access was granted.
  2. When I looked into database I found user was disable ( Red down arrow mark )

2

Here are two solutions –

  1. Most of the people recommended to recreate use by deleting and reading into database.

But main reason why it’s disabled because user is not having connect permission to database.

  1. if you grant connect permission it will resolve your issue.

use [YourDBName]

go

grant connect to [xyz\user]

1

How to bypass restart computer error message while installing SQL Patch

If you ever encounter restart computer error message while performing SQL Server patching and you don’t have opportunity\permission to restart your machine.

You can bypass this situation by deleting value from PendingFileRenameOperations key.

Here are the steps which you need to follow –>

  1. To Open Registry Editor. Click Start  ( win + R ) –> Run, input “regedit” and press Enter.
  2. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager, and delete any value in “PendingFileRenameOperations” key.

restart

regedit

rerun.PNG

Microsoft Best Practice Analyzer

Microsoft Best Practice Analyzer –

Best Practices Analyzer (BPA) is a server management tool that is available in Windows Server® 2008 R2. BPA can help administrators reduce best practice violations by scanning one or more roles that are installed on Windows Server 2008 R2, and reporting best practice violations to the administrator. Administrators can filter or exclude results from BPA reports that they do not have to see. Administrators can also perform BPA tasks by using either the Server Manager GUI, or Windows PowerShell cmdlets.

You will find more details in –
https://technet.microsoft.com/en-us/library/dd759260(v=ws.11).aspx

Installation of BPA – SQL 2012

Prerequisite
• PowerShell V2.0
• Microsoft Baseline Configuration Analyzer V2.0
• User should have admin access on server and SQL Server
o Note – You need to add your individual id or else you will get login error after running BCA.

Steps by Step Installation –
Step 1 – Download and Install the Microsoft Baseline Configuration Analyzer v2 (BCA) (follow default download instructions) – This will install to C:\Program Files\Microsoft Baseline Configuration Analyzer
Download link – https://www.microsoft.com/en-us/download/details.aspx?id=16475
Step 2- Install the Microsoft SQL Server 2012 Best Practices Analyzer (SQL BPA) (follow default download instructions) – This will install to C:\Windows\System32\BestPractices\v1.0\Models\SQL2012BPA\
https://www.microsoft.com/en-us/download/details.aspx?id=29302
Note – You will not able to see any icon for (SQL BPA) as it’s only install executables.

p1

Working –
Step 1 – Open Microsoft Baseline Configuration

p2

Note – If you wanted to connect remote computer, click on Connect to Another Computer.

p3

Step 2 – Select “SQL Server 2012 BPA” from drop down.
Step 3 – Go to Enter Parameters and select parameters you wants to select.

p4

Note – If it’s remote server provide server name in Alternate_Server_to_scan (Only server name not instance name), provide instance name in SQL_Server_Instanace_name fields.
Step 4 – Click on “Start Scan”
Step 5 – Analyze your result.

p6

Issues –
I was configuring BPA 2012 in windows 2012, after installing both BCA and SQL BPA, I was not able to see “SQL Server 2012 BPA” in drop down and to resolve the issue I have performed below steps –

p7

1. Navigate to the directory C:\Windows\System32\BestPractices\v1.0\Models and right-click the SQL2012BPA folder and make a copy.
2. Navigate to C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\ and paste the SQL2012BPA folder in this new location.
(Note – ProgramData is hidden folder which you need to see after checking “Hidden Items” in view of your file explorer)
3. Start the BCA with Run As Administrator and you will now see the SQL Server 2012 BPA listed in the “Select a product” dropdown.

Create Blocking Alert – SQL 2005 , SQL 2008 R2 and SQL 2012

We often need to create blocking alert in our Production server to capture existing blocking on server and keep record for future analysis.

Here are steps which help us to create blocking alerts-

Either you can receive blocking details through e-mail or you can log event id in application log and configure your monitoring tool to capture from application log.

STEP 1 –  Create SP to capture blocking details and generate e-mail\ event id in application log.

Note –  and datediff(minute, last_batch, getdate()) > 15, through which you can define blocking duration to generate e-mail or event, I am using 15 min

USE [db]

go

/****** Object: StoredProcedure [dbo].[usp_checkForLongRunningBlockers_log] Script Date: 5/18/2015 9:22:50 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[usp_checkForLongRunningBlockers_log]

as

declare @@leadblocker_spid smallint

declare @@dbname nvarchar(128)

declare @@login_time datetime

declare @@last_batch datetime

declare @@open_tran smallint

declare @@status nchar(30)

declare @@hostname nchar(128)

declare @@program_name nchar(128)

declare @@cmd nchar(16)

declare @@loginame nchar(128)

declare @@net_address nchar(12)

declare @@net_library nchar(12)

declare @@string varchar(2000)

select

@@leadblocker_spid = sp.spid,

@@dbname = db_name(sp.dbid),

@@login_time = login_time,

@@last_batch = last_batch,

@@open_tran = open_tran,

@@status = sp.status,

@@hostname = hostname,

@@program_name = program_name,

@@cmd = cmd,

@@loginame = loginame,

@@net_address = net_address,

@@net_library = net_library

FROM master.dbo.sysprocesses sp

LEFT JOIN sys.dm_exec_requests er

ON er.session_id = sp.spid

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

AND blocked = 0

and datediff(minute, last_batch, getdate()) > 15

set @@string =

‘Blocking Details -Date: ‘ + convert(varchar(20), getdate(), 9) + ‘

@@leadblocker_spid = ‘ + convert(varchar(5),@@leadblocker_spid) + ‘

@@dbname = ‘ + @@dbname + ‘

@@login_time = ‘ + convert(varchar(20), @@login_time, 9) + ‘

@@last_batch = ‘ + convert(varchar(20), @@last_batch, 9) + ‘

@@open_tran = ‘ + convert(varchar(5), @@open_tran) + ‘

@@status = ‘ + @@status + ‘

@@hostname = ‘ + @@hostname + ‘

@@program_name = ‘ + @@program_name + ‘

@@cmd = ‘ + @@cmd + ‘

@@loginame = ‘ + @@loginame + ‘

@@net_address = ‘ + @@net_address + ‘

@@net_library = ‘ + @@net_library + ”

if @@leadblocker_spid is null

return

else

–exec dbo.sp_SQLSMTPMail_html

— @vcTo = ‘nshukla@xyz.com’,

— @vcBody = @@string,

— @vcSubject = ‘Long Running Blocking’,

— @vcAttachments = null,

— @vcQuery = null,

— @vcFrom = ‘noreply@xyz.com’,

— @vcCC = ”,

— @vcBCC = ”,

— @vcSMTPServer = ‘nav-mail2.xyz.com’, — put local network smtp server name here

— @cSendUsing = ‘2’,

— @vcPort = ’25’,

— @cAuthenticate = ‘0’,

— @vcDSNOptions = ‘0’,

— @vcTimeout = ’30’,

— @vcSenderName = null,

— @vcServerName = null

EXEC xp_logevent 60000, @@string, error

GO

STEP 2 – Create table to capture blocking history –

USE [db]

GO

/****** Object:  Table [dbo].[BLOCKING_HISTORY]    Script Date: 5/18/2015 9:26:40 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[BLOCKING_HISTORY](

[spid] [smallint] NULL,

[kpid] [smallint] NULL,

[blocked] [smallint] NULL,

[waittype] [varbinary](5000) NULL,

[waittime] [bigint] NULL,

[lastwaittype] [nvarchar](500) NULL,

[waitresource] [nvarchar](500) NULL,

[dbid] [smallint] NULL,

[uid] [smallint] NULL,

[cpu] [int] NULL,

[physical_io] [bigint] NULL,

[memusage] [int] NULL,

[login_time] [datetime] NULL,

[last_batch] [datetime] NULL,

[ecid] [smallint] NULL,

[open_tran] [smallint] NULL,

[status] [nvarchar](500) NULL,

[hostname] [nvarchar](500) NULL,

[program_name] [nvarchar](500) NULL,

[hostprocess] [nvarchar](500) NULL,

[cmd] [nvarchar](500) NULL,

[nt_domain] [nvarchar](500) NULL,

[nt_username] [nvarchar](500) NULL,

[net_address] [nvarchar](500) NULL,

[net_library] [nvarchar](500) NULL,

[loginame] [nvarchar](500) NULL,

[context_info] [varbinary](5000) NULL,

[sql_handle] [varbinary](5000) NULL,

[stmt_start] [int] NULL,

[stmt_end] [int] NULL,

[request_id] [int] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

STEP 3 – Create SP to INSERT  blocking details in Blocking History table.

USE [db]

GO

/****** Object:  StoredProcedure [dbo].[usp_blockingEntry]    Script Date: 5/18/2015 9:25:52 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE Procedure [dbo].[usp_blockingEntry]

as

begin

SET NOCOUNT ON

declare @blockingcount int  =   ( select count(1) from sys.sysprocesses where blocked 0 )

if @blockingcount > 0

INSERT INTO [dbo].[BLOCKING_HISTORY]

(      spid

,      kpid

,      blocked

,      waittype

,      waittime

,      lastwaittype

,      waitresource

,      dbid

,      uid

,      cpu

,      physical_io

,      memusage

,      login_time

,      last_batch

,      ecid

,      open_tran

,      status

–,    sid

,      hostname

,      program_name

,      hostprocess

,      cmd

,      nt_domain

,      nt_username

,      net_address

,      net_library

,      loginame

,      context_info

,      sql_handle

,      stmt_start

,      stmt_end

,      request_id

)

select

spid

,      kpid

,      blocked

,      waittype

,      waittime

,      lastwaittype

,      waitresource

,      dbid

,      uid

,      cpu

,      physical_io

,      memusage

,      login_time

,      last_batch

,      ecid

,      open_tran

,      status

–,    sid

,      hostname

,      program_name

,      hostprocess

,      cmd

,      nt_domain

,      nt_username

,      net_address

,      net_library

,      loginame

,      context_info

,      sql_handle

,      stmt_start

,      stmt_end

,      request_id

from sys.sysprocesses  where blocked 0

end

GO

STEP – 4 – Create SP to get lead blocker –

USE [db]
GO

/****** Object: StoredProcedure [dbo].[usp_leadblocker] Script Date: 5/18/2015 9:44:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[usp_leadblocker]
as

select *
from
(
select
‘Blocking’ as State,
hostname as host,
db_name(dbid) as dbname,
SPID,
blocked,
lastwaittype,
dbid,
login_time,
last_batch,
open_tran,
status,
hostname,
program_name,
cmd,
nt_domain,
nt_username

from sys.sysprocesses (nolock)
where
spid in (
select distinct blocked
from sys.sysprocesses (nolock)
where
blocked 0
and blocked spid
)
and blocked = 0
and blocked spid

union all

select
‘Blocked’ as State,
hostname as host,
db_name(dbid) as dbname,
SPID,
blocked,
lastwaittype,
dbid,
login_time,
last_batch,
open_tran,
status,
hostname,
program_name,
cmd,
nt_domain,
nt_username
from sys.sysprocesses (nolock)
where
blocked 0
and blocked spid
) dt
order by 1 desc

GO

STEP 5 – T- SQL to check SPID text.

DECLARE @sqltext VARBINARY(128)

SELECT @sqltext = sql_handle

FROM sys.sysprocesses

WHERE spid = 64

SELECT TEXT

FROM sys.dm_exec_sql_text(@sqltext)

GO

***************************************

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 64
SELECT TEXT
FROM ::fn_get_sql(@sqltext)
GO

*****************************

DBCC INPUTBUFFER(64)
GO

*************************

SP_WHO2 64

go

How to grant access on SSIS Server

SSIS access permissions are managed via Component Service under DCOM Config

1. Run  –> dcomcnfg.exe or Component Services -> Computers -> My Computer -> DCOM Config -> MSDTSServer

2.Right click on MSDTSServer or MSDTSServer100 ( based on SQL version )

3.Click properties

4.Click on the Security tab

5.Select Customize and add the users/groups to the Launch/Activation and Access tabs accordingly

6.Add the user to the local Distributed COM Users group

7.Restart the SSIS

How to add user in local Distributed COM Users group

  1. Right-click My Computer>Manage>Configuration>Local Users & Groups
  2. Look for the Distributed COM Users group and add the account.

SQL Server 2012 Upgrade from Standard Edition to Enterprise edition

SQL Server 2012 Upgrade from Standard Edition to Enterprise edition

upgrade1

 

Step 1- Identify SQL executable file  , Right click and run as admin

upgrade2

 

Step 2 – click on Maintenance tab on left hand side.

upgrade3

Step 3- Click on Edition Upgrade

upgrade4

Step 4 – Click Okay

upgrade5

Step 5- Click Next

upgrade6

Step 6 – Click next on Production key

upgrade8

Step 7 – Click check on “I accept the License terms” and then click Next

upgrade7

Step 8 – Select Instance you want to upgrade form “Specify the Instance of SQL Server” and click next

upgrade9

 

Step 9 – Click next

upgrade10

Step 10 – Review upgrade and click Upgrade

upgrade11

 

Step 11 – Click Close

upgrade12

Step 11- Open SSMS and check version.

upgrade13

SSRS – How to convert in Day/Hour/Minute format in SSRS report

Scenario – In my SSRS report i have calculate Age field of Tickets like in 1 Day 2 Hours 20 min format and we are getting values in minute .

Solution –

I have created filed called Age(Minutes) in my SP or query

IN SQL –

datediff(minute,date_created, getdate()) as ‘Age (Minute)’

IN SSRS write expression

=  ( Fields!Age__Minute_.Value ) \ 1400 & ” days ” &  ((( Fields!Age__Minute_.Value ) MOD 1400) \ 60 ) & ” Hours ” & ((( Fields!Age__Minute_.Value ) MOD 1400) MOD 60 ) & ” Minutes “

How Get SQL Server Job Name with help of program name

How Get SQL Server Job Name with help of program name

SQLAgent – TSQL JobStep (Job 0x0854F6E4F9E5AE48B164966C0553C8B0 : Step 4)

Step -1 Create Below function

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[GetJobIdFromProgramName] (

@program_name nvarchar(128)

)

RETURNS uniqueidentifier

AS

BEGIN

DECLARE @start_of_job_id int

SET @start_of_job_id = CHARINDEX(‘(Job 0x’, @program_name) + 7

RETURN CASE WHEN @start_of_job_id > 0 THEN CAST(

SUBSTRING(@program_name, @start_of_job_id + 06, 2) + SUBSTRING(@program_name, @start_of_job_id + 04, 2) +

SUBSTRING(@program_name, @start_of_job_id + 02, 2) + SUBSTRING(@program_name, @start_of_job_id + 00, 2) + ‘-‘ +

SUBSTRING(@program_name, @start_of_job_id + 10, 2) + SUBSTRING(@program_name, @start_of_job_id + 08, 2) + ‘-‘ +

SUBSTRING(@program_name, @start_of_job_id + 14, 2) + SUBSTRING(@program_name, @start_of_job_id + 12, 2) + ‘-‘ +

SUBSTRING(@program_name, @start_of_job_id + 16, 4) + ‘-‘ +

SUBSTRING(@program_name, @start_of_job_id + 20,12) AS uniqueidentifier)

ELSE NULL

END

END

Step – 2

Execute below query –

SELECT *

FROM msdb.dbo.sysjobs

WHERE

job_id = dbo.GetJobIdFromProgramName (‘SQLAgent – TSQL JobStep (Job 0x0854F6E4F9E5AE48B164966C0553C8B0 : Step 4) ‘)

How to create Blocking alert and generate e-mail alert.

Being SQL DBA it’s one of Important task to have real time blocking alert on server.

This example demonstrate how to configure blocking alert and capture blocking

Step 1 – Create SP to check blocking log –

This SP will help to identify blocking on server with specific time ( in this example it’s for 2 min )  and log record with event id 60000 in windows application log , you have change your time period in datediff(minute, last_batch, getdate()) > 2 parameter

USE [Test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[usp_checkForLongRunningBlockers_log]
as

declare @@leadblocker_spid smallint
declare @@dbname nvarchar(128)
declare @@login_time datetime
declare @@last_batch datetime
declare @@open_tran smallint
declare @@status nchar(30)
declare @@hostname nchar(128)
declare @@program_name nchar(128)
declare @@cmd nchar(16)
declare @@loginame nchar(128)
declare @@net_address nchar(12)
declare @@net_library nchar(12)
declare @@string varchar(2000)

select
@@leadblocker_spid = sp.spid,
@@dbname = db_name(sp.dbid),
@@login_time = login_time,
@@last_batch = last_batch,
@@open_tran = open_tran,
@@status = sp.status,
@@hostname = hostname,
@@program_name = program_name,
@@cmd = cmd,
@@loginame = loginame,
@@net_address = net_address,
@@net_library = net_library
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
and datediff(minute, last_batch, getdate()) > 2

set @@string =

‘Blocking Details -Date: ‘ + convert(varchar(20), getdate(), 9) + ‘
@@leadblocker_spid = ‘ + convert(varchar(5),@@leadblocker_spid) + ‘
@@dbname = ‘ + @@dbname + ‘
@@login_time = ‘ + convert(varchar(20), @@login_time, 9) + ‘
@@last_batch = ‘ + convert(varchar(20), @@last_batch, 9) + ‘
@@open_tran = ‘ + convert(varchar(5), @@open_tran) + ‘
@@status = ‘ + @@status + ‘
@@hostname = ‘ + @@hostname + ‘
@@program_name = ‘ + @@program_name + ‘
@@cmd = ‘ + @@cmd + ‘
@@loginame = ‘ + @@loginame + ‘
@@net_address = ‘ + @@net_address + ‘
@@net_library = ‘ + @@net_library + ”

if @@leadblocker_spid is null
return
else

EXEC xp_logevent 60000, @@string, error

GO

Step 2 – Create SP to capture all blocking details in table for future reference –

USE [Test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[usp_blockingEntry]
as
begin
SET NOCOUNT ON
declare @blockingcount int = ( select count(1) from sys.sysprocesses where blocked <> 0 )

if @blockingcount > 0

INSERT INTO [dbo].[BLOCKING_HISTORY]
( spid
, kpid
, blocked
, waittype
, waittime
, lastwaittype
, waitresource
, dbid
, uid
, cpu
, physical_io
, memusage
, login_time
, last_batch
, ecid
, open_tran
, status
–, sid
, hostname
, program_name
, hostprocess
, cmd
, nt_domain
, nt_username
, net_address
, net_library
, loginame
, context_info
, sql_handle
, stmt_start
, stmt_end
, request_id
)

select
spid
, kpid
, blocked
, waittype
, waittime
, lastwaittype
, waitresource
, dbid
, uid
, cpu
, physical_io
, memusage
, login_time
, last_batch
, ecid
, open_tran
, status
–, sid
, hostname
, program_name
, hostprocess
, cmd
, nt_domain
, nt_username
, net_address
, net_library
, loginame
, context_info
, sql_handle
, stmt_start
, stmt_end
, request_id
from sys.sysprocesses where blocked <> 0
end

GO

Now if you have monitoring job which capture event log you can ask your monitoring team to configure monitoring with event id 17063 and description 60000 and you all set.

If your requirement is to generate email –

Step 3- You can use below SP. This SP will generate mail if any blocking occurring more than 2 mins ( Remember you need to configure Database mail server and create SP sp_SQLSMTPMail_HTML ( Find below )

USE [Test]

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[usp_checkForLongRunningBlockers_log]
as

declare @@leadblocker_spid smallint
declare @@dbname nvarchar(128)
declare @@login_time datetime
declare @@last_batch datetime
declare @@open_tran smallint
declare @@status nchar(30)
declare @@hostname nchar(128)
declare @@program_name nchar(128)
declare @@cmd nchar(16)
declare @@loginame nchar(128)
declare @@net_address nchar(12)
declare @@net_library nchar(12)
declare @@string varchar(2000)

select
@@leadblocker_spid = sp.spid,
@@dbname = db_name(sp.dbid),
@@login_time = login_time,
@@last_batch = last_batch,
@@open_tran = open_tran,
@@status = sp.status,
@@hostname = hostname,
@@program_name = program_name,
@@cmd = cmd,
@@loginame = loginame,
@@net_address = net_address,
@@net_library = net_library
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
and datediff(minute, last_batch, getdate()) > 2

set @@string =

‘Blocking Details -Date: ‘ + convert(varchar(20), getdate(), 9) + ‘
@@leadblocker_spid = ‘ + convert(varchar(5),@@leadblocker_spid) + ‘
@@dbname = ‘ + @@dbname + ‘
@@login_time = ‘ + convert(varchar(20), @@login_time, 9) + ‘
@@last_batch = ‘ + convert(varchar(20), @@last_batch, 9) + ‘
@@open_tran = ‘ + convert(varchar(5), @@open_tran) + ‘
@@status = ‘ + @@status + ‘
@@hostname = ‘ + @@hostname + ‘
@@program_name = ‘ + @@program_name + ‘
@@cmd = ‘ + @@cmd + ‘
@@loginame = ‘ + @@loginame + ‘
@@net_address = ‘ + @@net_address + ‘
@@net_library = ‘ + @@net_library + ”

if @@leadblocker_spid is null
return
else
exec dbo.sp_SQLSMTPMail_html
@vcTo = ‘abc@xxx.com’,
@vcBody = @@string,
@vcSubject = ‘Long Running Blocking’,
@vcAttachments = null,
@vcQuery = null,
@vcFrom = ‘noreply@xxxx.com’,
@vcCC = ”,
@vcBCC = ”,
@vcSMTPServer = ‘xxx’, — put local network smtp server name here

@cSendUsing = ‘2’,
@vcPort = ’25’,
@cAuthenticate = ‘0’,
@vcDSNOptions = ‘0’,
@vcTimeout = ’30’,
@vcSenderName = null,
@vcServerName = null

GO

 

Step 4 – Create  SPs – sp_SQLSMTPMail_HTML

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp_SQLSMTPMail_HTML]
@vcTo varchar(2048) = null,
@vcBody varchar(8000) = ”,
@vcSubject varchar(255) = null,
@vcAttachments varchar(1024) = null,
@vcQuery varchar(8000) = null,
@vcFrom varchar(128) = null,
@vcCC varchar(2048) = ”,
@vcBCC varchar(2048) = ”,
@vcSMTPServer varchar(255) = ‘xxxx’, — put local network smtp server name here
@cSendUsing char(1) = ‘2’,
@vcPort varchar(3) = ’25’,
@cAuthenticate char(1) = ‘0’,
@vcDSNOptions varchar(2) = ‘0’,
@vcTimeout varchar(2) = ’30’,
@vcSenderName varchar(128) = null,
@vcServerName sysname = null,
@text_or_html char(4) = ‘TEXT’

As

/*
Example of usage…
exec sp_configure ‘show advanced options’, 1
reconfigure with override
exec sp_configure ‘Ole Automation Procedures’, 1
reconfigure with override

exec sp_SQLSMTPMail_html
@vcTo = ‘nshukla@xxx.com’,
@vcBody = ‘<html>
<body>

<h4>I am so AWESOME:</h4>
<table border=”1″>
<tr>
<td>First</td>
<td>Row</td>
</tr>
<tr>
<td>Second</td>
<td>Row</td>
</tr>
</table>

<h4>That he can modify the &#39sp_SQLSMTPMail&#39 stored procedure:</h4>
<table border=”8″>
<tr>
<td>First</td>
<td>Row</td>
</tr>
<tr>
<td>Second</td>
<td>Row</td>
</tr>
</table>

<h4>To take HTML formatting:</h4>
<table border=”15″>
<tr>
<td>First</td>
<td>Row</td>
</tr>
<tr>
<td>Second</td>
<td>Row</td>
</tr>
</table>

<text><br><b>So we can send HTML mail on servers without DB_MAIL configured!</b></text>

</body>
</html>’,
@vcSubject = ‘Blocking details’,
@vcAttachments = ”,
@vcQuery = ”,
@vcFrom = ‘NoREPLY@xxxx.com’,
@html = ‘HTML’

*/

/*******************************************************************/
–Name : sp_SQLSMTPMail
–Server : Generic
–Description : SQL smtp e-mail using CDOSYS, OLE Automation and a
— network smtp server; For SQL Servers running on
— windows 2000.

–Note : Be sure to set the default for @vcSMTPServer above to
— the company network smtp server or you will have to
— pass it in each time.

–Comments : Getting the network SMTP configured to work properly
— may require engaging your company network or
— server people who deal with the netowrk SMTP server.
— Some errors that the stored proc returns relate to
— incorrect permissions for the various SQL Servers to
— use the SMTP relay server to bouce out going mail.
— Without proper permissions the SQL server appears as
— a spammer to the local SMTP network server.

–Parameters : See the ‘Syntax’ Print statements below or call the
— sp with ‘?’ as the first input.

–Date : 08/22/2001
–Author : Clinton Herring

–History :
/*******************************************************************/

Set nocount on

— Determine if the user requested syntax.
If @vcTo = ‘?’
Begin
Print ‘Syntax for sp_SQLSMTPMail (based on CDOSYS):’
Print ‘Exec master.dbo.sp_SQLSMTPMail’
Print ‘ @vcTo (varchar(2048)) – Recipient e-mail address list separating each with a ”;” ‘
Print ‘ or a ”,”. Use a ”?” to return the syntax.’
Print ‘ @vcBody (varchar(8000)) – Text body; use embedded char(13) + char(10)’
Print ‘ for carriage returns. The default is nothing’
Print ‘ @vcSubject (varchar(255))) – E-mail subject. The default is a message from’
Print ‘ @@servername.’
Print ‘ @vcAttachments (varchar(1024)) – Attachment list separating each with a ”;”.’
Print ‘ The default is no attachments.’
Print ‘ @vcQuery (varchar(8000)) – In-line query or a query file path; do not ‘
Print ‘ use double quotes within the query.’
Print ‘ @vcFrom (varchar(128)) – Sender list defaulted to @@ServerName.’
Print ‘ @vcCC (varchar(2048)) – CC list separating each with a ”;” or a ”,”’
Print ‘ The default is no CC addresses.’
Print ‘ @vcBCC (varchar(2048)) – Blind CC list separating each with a ”;” or a ”,”’
Print ‘ The default is no BCC addresses.’
Print ‘ @vcSMTPServer (varchar(255)) – Network smtp server defaulted to your companies network’
Print ‘ smtp server. Set this in the stored proc code.’
Print ‘ @cSendUsing (char(1)) – Specifies the smpt server method, local or network. The’
Print ‘ default is network, a value of ”2”.’
Print ‘ @vcPort (varchar(3)) – The smtp server communication port defaulted to ”25”.’
Print ‘ @cAuthenticate (char(1)) – The smtp server authentication method defaulted to ‘
Print ‘ anonymous, a value of ”0”.’
Print ‘ @vcDSNOptions (varchar(2)) – The smtp server delivery status defaulted to none,’
Print ‘ a value of ”0”.’
Print ‘ @vcTimeout (varchar(2)) – The smtp server connection timeout defaulted to 30 seconds.’
Print ‘ @vcSenderName (varchar(128)) – Primary sender name defaulted to @@ServerName.’
Print ‘ @vcServerName (sysname) – SQL Server to which the query is directed defaulted’
Print ‘ to @@ServerName.’
Print ”
Print ”
Print ‘Example:’
Print ‘sp_SQLSMTPMail ”<user@mycompany.com>”, ”This is a test”, @vcSMTPServer = <network smtp relay server>’
Print ”
Print ‘The above example will send an smpt e-mail to <user@mycompany.com> from @@ServerName’
Print ‘with a subject of ”Message from SQL Server <@@ServerName>” and a’
Print ‘text body of ”This is a test” using the network smtp server specified.’
Print ‘See the MSDN online library, Messaging and Collaboration, at ‘
Print ‘http://www.msdn.microsoft.com/library/ for details about CDOSYS.’
Print ‘subheadings: Messaging and Collaboration>Collaboration Data Objects>CDO for Windows 2000>’
Print ‘Reference>Fields>http://schemas.microsoft.com/cdo/configuration/>smtpserver field’
Print ”
Print ‘Be sure to set the default for @vcSMTPServer before compiling this stored procedure.’
Print ”
Return
End

— Declare variables
Declare @iMessageObjId int
Declare @iHr int
Declare @iRtn int
Declare @iFileExists tinyint
Declare @vcCmd varchar(255)
Declare @vcQueryOutPath varchar(50)
Declare @dtDatetime datetime
Declare @vcErrMssg varchar(255)
Declare @vcAttachment varchar(1024)
Declare @iPos int
Declare @vcErrSource varchar(255)
Declare @vcErrDescription varchar(255)
Declare @html varchar(255)

— Set local variables.
Set @dtDatetime = getdate()
Set @iHr = 0

— Check for minimum parameters.
If @vcTo is null
Begin
Set @vcErrMssg = ‘You must supply at least 1 recipient.’
Goto ErrMssg
End

— CDOSYS uses commas to separate recipients. Allow users to use
— either a comma or a semi-colon by replacing semi-colons in the
— To, CCs and BCCs.
Select @vcTo = Replace(@vcTo, ‘;’, ‘,’)
Select @vcCC = Replace(@vcCC, ‘;’, ‘,’)
Select @vcBCC = Replace(@vcBCC, ‘;’, ‘,’)

— Set the default SQL Server to the local SQL Server if one
— is not provided to accommodate instances in SQL 2000.
If @vcServerName is null
Set @vcServerName = @@servername

— Set a default “subject” if one is not provided.
If @vcSubject is null
Set @vcSubject = ‘Message from SQL Server ‘ + @vcServerName

— Set a default “from” if one is not provided.
If @vcFrom is null
Set @vcFrom = ‘SQL-‘ + Replace(@vcServerName,’\’,’_’)

— Set a default “sender name” if one is not provided.
If @vcSenderName is null
Set @vcSenderName = ‘SQL-‘ + Replace(@vcServerName,’\’,’_’) + ‘@xxxx.com’

— Create the SMTP message object.
EXEC @iHr = sp_OACreate ‘CDO.Message’, @iMessageObjId OUT
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error creating object CDO.Message.’
Goto ErrMssg
End

— Set SMTP message object parameters.
— To
EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘To’, @vcTo
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message parameter “To”.’
Goto ErrMssg
End

— Subject
EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘Subject’, @vcSubject
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message parameter “Subject”.’
Goto ErrMssg
End

— From
EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘From’, @vcFrom
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message parameter “From”.’
Goto ErrMssg
End

— CC
EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘CC’, @vcCC
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message parameter “CC”.’
Goto ErrMssg
End

— BCC
EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘BCC’, @vcBCC
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message parameter “BCC”.’
Goto ErrMssg
End

— DSNOptions
EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘DSNOptions’, @vcDSNOptions
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message parameter “DSNOptions”.’
Goto ErrMssg
End

— Sender
EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘Sender’, @vcSenderName
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message parameter “Sender”.’
Goto ErrMssg
End

— Is there a query to run?
If @vcQuery is not null and @vcQuery <> ”
Begin
— We have a query result to include; temporarily send the output to the
— drive with the most free space. Use xp_fixeddrives to determine this.
— If a temp table exists with the following name drop it.
If (Select object_id(‘tempdb.dbo.#fixeddrives’)) > 0
Exec (‘Drop table #fixeddrives’)

— Create a temp table to work with xp_fixeddrives.
Create table #fixeddrives(
Drive char(1) null,
FreeSpace varchar(15) null)

— Get the fixeddrive info.
Insert into #fixeddrives Exec master.dbo.xp_fixeddrives

— Get the drive letter of the drive with the most free space
— Note: The OSQL output file name must be unique for each call within the same session.
— Apparently OSQL does not release its lock on the first file created until the session ends.
— Hence this alleviates a problem with queries from multiple calls in a cursor or other loop.
Select @vcQueryOutPath = Drive + ‘:\TempQueryOut’ +
ltrim(str(datepart(hh,getdate()))) +
ltrim(str(datepart(mi,getdate()))) +
ltrim(str(datepart(ss,getdate()))) +
ltrim(str(datepart(ms,getdate()))) + ‘.txt’
from #fixeddrives
where FreeSpace = (select max(FreeSpace) from #fixeddrives )

— Check for a pattern of ‘\\*\’ or ‘?:\’.
— If found assume the query is a file path.
If Left(@vcQuery, 35) like ‘\\%\%’ or Left(@vcQuery, 5) like ‘_:\%’
Begin
Select @vcCmd = ‘osql /S’ + @vcServerName + ‘ /E /i’ +
convert(varchar(1024),@vcQuery) +
‘ /o’ + @vcQueryOutPath + ‘ -n -w5000 ‘
End
Else
Begin
Select @vcCmd = ‘osql /S’ + @vcServerName + ‘ /E /Q”‘ + @vcQuery +
‘” /o’ + @vcQueryOutPath + ‘ -n -w5000 ‘
End

— Execute the query
Exec master.dbo.xp_cmdshell @vcCmd, no_output

— Add the query results as an attachment if the file was successfully created.
— Check to see if the file exists. Use xp_fileexist to determine this.
— If a temp table exists with the following name drop it.
If (Select object_id(‘tempdb.dbo.#fileexists’)) > 0
Exec (‘Drop table #fileexists’)

— Create a temp table to work with xp_fileexist.
Create table #fileexists(
FileExists tinyint null,
FileIsDirectory tinyint null,
ParentDirectoryExists tinyint null)

— Execute xp_fileexist
Insert into #fileexists exec master.dbo.xp_fileexist @vcQueryOutPath

— Now see if we need to add the file as an attachment
If (select FileExists from #fileexists) = 1
Begin
— Set a variable for later use to delete the file.
Select @iFileExists = 1

— Add the file path to the attachment variable.
If @vcAttachments is null
Select @vcAttachments = @vcQueryOutPath
Else
Select @vcAttachments = @vcAttachments + ‘; ‘ + @vcQueryOutPath
End
End

— Check for multiple attachments separated by a semi-colon ‘;’.
If @vcAttachments is not null
Begin
If right(@vcAttachments,1) <> ‘;’
Select @vcAttachments = @vcAttachments + ‘; ‘
Select @iPos = CharIndex(‘;’, @vcAttachments, 1)
While @iPos > 0
Begin
Select @vcAttachment = ltrim(rtrim(substring(@vcAttachments, 1, @iPos -1)))
Select @vcAttachments = substring(@vcAttachments, @iPos + 1, Len(@vcAttachments)-@iPos)
EXEC @iHr = sp_OAMethod @iMessageObjId, ‘AddAttachment’, @iRtn Out, @vcAttachment
IF @iHr <> 0
Begin
EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out
Select @vcBody = @vcBody + char(13) + char(10) + char(13) + char(10) +
char(13) + char(10) + ‘Error adding attachment: ‘ +
char(13) + char(10) + @vcErrSource + char(13) + char(10) +
@vcAttachment
End
Select @iPos = CharIndex(‘;’, @vcAttachments, 1)
End
End

— TextBody
IF @html = ‘TEXT’
BEGIN
EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘TextBody’, @vcBody
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message parameter “TextBody”.’
Goto ErrMssg
End
END
ELSE
BEGIN
EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘HTMLBody’, @vcBody
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message parameter “TextBody”.’
Goto ErrMssg
End
END
— Other Message parameters for reference
–EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘MimeFormatted’, False
–EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘AutoGenerateTextBody’, False
–EXEC @iHr = sp_OASetProperty @iMessageObjId, ‘MDNRequested’, True

— Set SMTP Message configuration property values.
— Network SMTP Server location
EXEC @iHr = sp_OASetProperty @iMessageObjId,
‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/smtpserver&#8221;).Value’,
@vcSMTPServer
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message configuraton field “smtpserver”.’
Goto ErrMssg
End

— Sendusing
EXEC @iHr = sp_OASetProperty @iMessageObjId,
‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/sendusing&#8221;).Value’,
@cSendUsing
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message configuraton field “sendusing”.’
Goto ErrMssg
End

— SMTPConnectionTimeout
EXEC @iHr = sp_OASetProperty @iMessageObjId,
‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/SMTPConnectionTimeout&#8221;).Value’,
@vcTimeout
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message configuraton field “SMTPConnectionTimeout”.’
Goto ErrMssg
End

— SMTPServerPort
EXEC @iHr = sp_OASetProperty @iMessageObjId,
‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/SMTPServerPort&#8221;).Value’,
@vcPort
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message configuraton field “SMTPServerPort”.’
Goto ErrMssg
End

— SMTPAuthenticate
EXEC @iHr = sp_OASetProperty @iMessageObjId,
‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/SMTPAuthenticate&#8221;).Value’,
@cAuthenticate
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error setting Message configuraton field “SMTPAuthenticate”.’
Goto ErrMssg
End

— Other Message Configuration fields for reference
–EXEC @iHr = sp_OASetProperty @iMessageObjId,
–‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/SMTPUseSSL&#8221;).Value’,True

–EXEC @iHr = sp_OASetProperty @iMessageObjId,
–‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/LanguageCode&#8221;).Value’,’en’

–EXEC @iHr = sp_OASetProperty @iMessageObjId,
–‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/SendEmailAddress&#8221;).Value’, ‘Test User’

–EXEC @iHr = sp_OASetProperty @iMessageObjId,
–‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/SendUserName&#8221;).Value’,null

–EXEC @iHr = sp_OASetProperty @iMessageObjId,
–‘Configuration.Fields(“http://schemas.microsoft.com/cdo/configuration/SendPassword&#8221;).Value’,null

— Update the Message object fields and configuration fields.
EXEC @iHr = sp_OAMethod @iMessageObjId, ‘Configuration.Fields.Update’
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error updating Message configuration fields.’
Goto ErrMssg
End

EXEC @iHr = sp_OAMethod @iMessageObjId, ‘Fields.Update’
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error updating Message parameters.’
Goto ErrMssg
End

— Send the message.
EXEC @iHr = sp_OAMethod @iMessageObjId, ‘Send’
IF @iHr <> 0
Begin
Set @vcErrMssg = ‘Error Sending e-mail.’
Goto ErrMssg
End
Else
Print ‘Mail sent.’

Cleanup:
— Destroy the object and return.
EXEC @iHr = sp_OADestroy @iMessageObjId
–EXEC @iHr = sp_OAStop

— Delete the query output file if one exists.
If @iFileExists = 1
Begin
Select @vcCmd = ‘del ‘ + @vcQueryOutPath
Exec master.dbo.xp_cmdshell @vcCmd, no_output
End
Return

ErrMssg:
Begin
Print @vcErrMssg
If @iHr <> 0
Begin
EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out
Print @vcErrSource
Print @vcErrDescription
End

— Determine whether to exist or go to Cleanup.
If @vcErrMssg = ‘Error creating object CDO.Message.’
Return
Else
Goto Cleanup
End

GO