Ms SQL send mail

Question

Hi somebody can help me with this please?

 declare  @account_name varchar(128) = 'Test Mail Account'
 declare   @description varchar(128)  = 'Mail account for administrative e-mail.'
  declare   @email_address varchar(128) = '[email protected]'
  declare   @replyto_address varchar(128) = '[email protected]'
  declare   @display_name varchar(128) = 'Manoj Pandey'
   declare  @mailserver_name varchar(128) = 'smtp.xxxx.net'
 declare    @port int = 587
declare     @username nvarchar(128)= 'xyz'
declare     @password varchar(128) = 'xxyyzz'
declare     @enable_ssl int = 1



    EXECUTE [msdb].[dbo].[sysmail_add_account_sp]
            @account_name,
            @description,
            @email_address,
            @replyto_address,
            @display_name,
            @mailserver_name,
            @port,
            @username,
            @password,
            @enable_ssl

give me an error messeage:

" Msg 8114, Level 16, State 1, Procedure sysmail_add_account_sp, Line 0 Error converting data type nvarchar to int. "

I can't find out why?

The source is from the http://sqlwithmanoj.wordpress.com/2010/09/29/database-mail-setup-sql-server-2005/

Thanks

Answer 1

First thing I would recommend is to use named parameters so stored proc exactly knows which parameter goes where:

   EXECUTE [msdb].[dbo].[sysmail_add_account_sp]
        @account_name=@account_name,
        @description=@description,
        .....

And also as per MSDN it looks like order you passing them is incorrect http://technet.microsoft.com/en-us/library/ms182804(v=sql.90).aspx

Answer 2

If you don't use named parameters (as Farfarak writes above) then you can not skip any of the parameters (although you do not need to use all of them). The order is as follows: See http://technet.microsoft.com/en-us/library/ms182804.aspx

sysmail_add_account_sp  [ @account_name = ] 'account_name',
    [ @email_address = ] 'email_address' ,
    [ [ @display_name = ] 'display_name' , ]
    [ [ @replyto_address = ] 'replyto_address' , ]
    [ [ @description = ] 'description' , ]
    [ @mailserver_name = ] 'server_name' 
    [ , [ @mailserver_type = ] 'server_type' ]
    [ , [ @port = ] port_number ]
    [ , [ @username = ] 'username' ]
    [ , [ @password = ] 'password' ]
    [ , [ @use_default_credentials = ] use_default_credentials ]
    [ , [ @enable_ssl = ] enable_ssl ]
    [ , [ @account_id = ] account_id OUTPUT ]

In addition the line declaring the enable_ssl should be the following:

declare     @enable_ssl bit = 1