Get DBMail information

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

    ..>>..

    MobashA

  • 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

  • thats good.

    thanks..

    ..>>..

    MobashA

  • 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

  • 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

  • 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