July 14, 2008 at 12:40 pm
Comments posted to this topic are about the item Get DBMail information
..>>..
MobashA
September 12, 2008 at 1:26 am
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
September 12, 2008 at 3:51 am
thats good.
thanks..
..>>..
MobashA
September 18, 2008 at 10:51 am
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
October 3, 2008 at 7:28 am
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
October 5, 2008 at 12:04 am
this code is writen using SQL 2005 am not sure but it might need some adjustments to work on 2000.
..>>..
MobashA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply