Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get DBMail information Expand / Collapse
Author
Message
Posted Monday, July 14, 2008 12:40 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay 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
Posted Friday, September 12, 2008 1:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:56 AM
Points: 1,330, Visits: 1,434
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
Posted Friday, September 12, 2008 3:51 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay 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
Posted Thursday, September 18, 2008 10:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 11:02 AM
Points: 3, Visits: 55
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
Posted Friday, October 03, 2008 7:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 14, 2014 8:09 AM
Points: 69, Visits: 344
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
Posted Sunday, October 05, 2008 12:04 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay 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 »

Add to briefcase

Permissions Expand / Collapse