Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get DBMail information


Get DBMail information

Author
Message
mobasha
mobasha
Right there with Babe
Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)

Group: General Forum Members
Points: 752 Visits: 1284
Comments posted to this topic are about the item Get DBMail information

..>>..

MobashA
John H Marsh
John H Marsh
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1449 Visits: 1455
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
mobasha
mobasha
Right there with Babe
Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)

Group: General Forum Members
Points: 752 Visits: 1284
thats good.
thanks..

..>>..

MobashA
dabrown
dabrown
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 57
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
Rob Maurer
Rob Maurer
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 359
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
mobasha
mobasha
Right there with Babe
Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)Right there with Babe (752 reputation)

Group: General Forum Members
Points: 752 Visits: 1284
this code is writen using SQL 2005 am not sure but it might need some adjustments to work on 2000.

..>>..

MobashA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search