Get DBMail information

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by mobasha » Get DBMail information

Get DBMail information

Rate Topic

Display Mode

Topic Options

Author

Message

mobasha

Posted Monday, July 14, 2008 12:40 PM

Say Hey Kid

Group: General Forum Members
Last Login: Sunday, January 29, 2012 1:45 AM
Points: 710, Visits: 1,284

Comments posted to this topic are about the item Get DBMail information

..>>..

MobashA

Post #533828

John H Marsh

Posted Friday, September 12, 2008 1:26 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, February 28, 2013 1:54 AM
Points: 1,325, Visits: 1,376

Hello,

When I tried out your script under SQL 2005, I got the following errors:-

Msg 8152, Level 16, State 2, Procedure sysmail_help_profile_sp, Line 20
String or binary data would be truncated.

(3 row(s) affected)
Msg 8152, Level 16, State 2, Procedure sysmail_help_account_sp, Line 20
String or binary data would be truncated.

(0 row(s) affected)

I updated the table column definitions as below, and now it runs fine.

Regards,

John Marsh

CREATE TABLE #temp01
(
profile_id INT,
[name] sysname,
description NVARCHAR(256)
)
INSERT INTO #temp01
EXECUTE msdb.dbo.sysmail_help_profile_sp ;
CREATE TABLE #temp02
(
profile_id INT,
profile_name sysname,
account_id INT,
account_name sysname,
seq int
)
INSERT INTO #temp02
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp ;
CREATE TABLE #temp03
(
account_id INT,
[name] sysname,
[description] NVARCHAR(256),
email_address NVARCHAR(128),
display_name NVARCHAR(128),
replyto_address NVARCHAR(128),
servertype sysname,
servername sysname,
port INT,
username NVARCHAR(128),
use_default_credentials bit,
enable_ssl bit
)
INSERT INTO #temp03
EXECUTE msdb.dbo.sysmail_help_account_sp ;
SELECT a.name,
b.account_name,
c.description,
c.email_address,
c.display_name,
c.replyto_address,
c.servertype,
c.servername,
c.port,
c.username,
c.use_default_credentials,
c.enable_ssl
FROM [#temp01] AS a
INNER JOIN [#temp02] AS b ON a.profile_id = b.[profile_id]
INNER JOIN [#temp03] AS c ON b.account_id = c.account_id
DROP TABLE #temp01
DROP TABLE #temp02
DROP TABLE [#temp03]

www.sql.lu
SQL Server Luxembourg User Group

Post #568305

mobasha

Posted Friday, September 12, 2008 3:51 AM

Say Hey Kid

Group: General Forum Members
Last Login: Sunday, January 29, 2012 1:45 AM
Points: 710, Visits: 1,284

thats good.
thanks..

..>>..

MobashA

Post #568361

dabrown

Posted Thursday, September 18, 2008 10:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 25, 2011 7:51 AM
Points: 3, Visits: 50

And just for performance, I substituted table variables for the temp tables (and renamed the variables to fit my team's conventions) ...

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[procGetDbMailInformation]
(@intRowCount INT = NULL OUTPUT)

AS

DECLARE @intError INT

DECLARE @tblTemp01 TABLE
(
ProfileId INT,
MailName NVARCHAR(50),
MailDescription NVARCHAR(256)
)

INSERT INTO @tblTemp01
EXECUTE msdb.dbo.sysmail_help_profile_sp ;

DECLARE @tblTemp02 TABLE
(
ProfileId INT,
ProfileName NVARCHAR(50),
AccountId INT,
AccountName NVARCHAR(50),
Sequence INT
)

INSERT INTO @tblTemp02
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp ;

DECLARE @tblTemp03 TABLE
(
AccountId INT,
MailName NVARCHAR(50),
MailDescription NVARCHAR(50),
EmailAddress NVARCHAR(50),
DisplayName NVARCHAR(50),
ReplyToAddress NVARCHAR(50),
ServerType NVARCHAR(50),
ServerName NVARCHAR(50),
Port INT,
UserName NVARCHAR(50),
UseDefaultCredentials NVARCHAR(50),
EnableSsl int
)

INSERT INTO @tblTemp03
EXECUTE msdb.dbo.sysmail_help_account_sp ;

SELECT a.MailName,
b.AccountName,
c.MailDescription,
c.EmailAddress,
c.DisplayName,
c.ReplyToAddress,
c.ServerType,
c.ServerName,
c.Port,
c.UserName,
c.UseDefaultCredentials,
c.EnableSsl

FROM @tblTemp01 AS a
INNER JOIN @tblTemp02 AS b ON a.ProfileId = b.ProfileId
INNER JOIN @tblTemp03 AS c ON b.AccountId = c.AccountId

SELECT @intError = @@Error, @intRowCount = @@RowCount

PRINT 'Row Count: ' + CONVERT(NVARCHAR, @intRowCount)
PRINT 'Error: ' + CONVERT(NVARCHAR, @intError)

RETURN @intError

Post #571981

Rob Maurer

Posted Friday, October 03, 2008 7:28 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 10, 2013 6:11 AM
Points: 69, Visits: 339

I ran the original code and got this error (v2000)

Server: Msg 2812, Level 16, State 62, Line 7
Could not find stored procedure 'msdb.dbo.sysmail_help_profile_sp'.
Server: Msg 2812, Level 16, State 62, Line 17
Could not find stored procedure 'msdb.dbo.sysmail_help_profileaccount_sp'.
Server: Msg 2812, Level 16, State 62, Line 34
Could not find stored procedure 'msdb.dbo.sysmail_help_account_sp'.

(0 row(s) affected)

Rob Maurer

Post #580261

mobasha

Posted Sunday, October 05, 2008 12:04 AM

Say Hey Kid

Group: General Forum Members
Last Login: Sunday, January 29, 2012 1:45 AM
Points: 710, Visits: 1,284

this code is writen using SQL 2005 am not sure but it might need some adjustments to work on 2000.

..>>..

MobashA

Post #580811

« Prev Topic | Next Topic »

Permissions