# Migrating from SQL Server to PostgreSQL with AWS Schema Conversion Tool
<a name="CHAP_Source.SQLServer.ToPostgreSQL"></a>

You can use the SQL Server to PostgreSQL extension pack in AWS SCT. This extension pack emulates SQL Server database functions in the converted PostgreSQL code. Use the SQL Server to PostgreSQL extension pack to emulate SQL Server Agent and SQL Server Database Mail. For more information about extension packs, see [Using extension packs with AWS Schema Conversion Tool](CHAP_ExtensionPack.md). 

**Topics**
+ [Privileges for PostgreSQL as a target database](#CHAP_Source.SQLServer.ToPostgreSQL.ConfigurePostgreSQL)
+ [SQL Server to PostgreSQL conversion settings](#CHAP_Source.SQLServer.ToPostgreSQL.ConversionSettings)
+ [Converting SQL Server partitions to PostgreSQL version 10 partitions](#CHAP_Source.SQLServer.ToPostgreSQL.PG10Partitions)
+ [Migration considerations](#CHAP_Source.SQLServer.ToPostgreSQL.MigrationConsiderations)
+ [Using an AWS SCT extension pack to emulate SQL Server Agent in PostgreSQL](CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Agent.md)
+ [Using an AWS SCT extension pack to emulate SQL Server Database Mail in PostgreSQL](CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Mail.md)

## Privileges for PostgreSQL as a target database
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ConfigurePostgreSQL"></a>

To use PostgreSQL as a target, AWS SCT requires the `CREATE ON DATABASE` privilege. Make sure that you grant this privilege for each target PostgreSQL database.

To use the converted public synonyms, change the database default search path to `"$user", public_synonyms, public`.

You can use the following code example to create a database user and grant the privileges.

```
CREATE ROLE user_name LOGIN PASSWORD 'your_password';
GRANT CREATE ON DATABASE db_name TO user_name;
ALTER DATABASE db_name SET SEARCH_PATH = "$user", public_synonyms, public;
```

In the preceding example, replace *user\$1name* with the name of your user. Then, replace *db\$1name* with the name of your target database. Finally, replace *your\$1password* with a secure password.

In PostgreSQL, only the schema owner or a `superuser` can drop a schema. The owner can drop a schema and all objects that this schema includes even if the owner of the schema doesn't own some of its objects.

When you use different users to convert and apply different schemas to your target database, you can get an error message when AWS SCT can't drop a schema. To avoid this error message, use the `superuser` role. 

## SQL Server to PostgreSQL conversion settings
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ConversionSettings"></a>

To edit SQL Server to PostgreSQL conversion settings, choose **Settings**, and then choose **Conversion settings**. From the upper list, choose **SQL Server**, and then choose **SQL Server – PostgreSQL**. AWS SCT displays all available settings for SQL Server to PostgreSQL conversion.

SQL Server to PostgreSQL conversion settings in AWS SCT include options for the following:
+ To limit the number of comments with action items in the converted code.

  For **Add comments in the converted code for the action items of selected severity and higher**, choose the severity of action items. AWS SCT adds comments in the converted code for action items of the selected severity and higher.

  For example, to minimize the number of comments in your converted code, choose **Errors only**. To include comments for all action items in your converted code, choose **All messages**.
+ To allow to use indexes with the same name in different tables in SQL Server.

  In PostgreSQL, all index names that you use in the schema, must be unique. To make sure that AWS SCT generates unique names for all your indexes, select **Generate unique names for indexes**.
+ To convert SQL Server procedures to PostgreSQL functions.

  PostgreSQL version 10 and earlier doesn't support procedures. For customers who aren't familiar with using procedures in PostgreSQL, AWS SCT can convert procedures to functions. To do so, select **Convert procedures to functions**.
+ To emulate the output of `EXEC` in a table.

  Your source SQL Server database can store the output of `EXEC` in a table. AWS SCT creates temporary tables and an additional procedure to emulate this feature. To use this emulation, select **Create additional routines for handling open datasets**.
+ To define the template to use for the schema names in the converted code. For **Schema name generation template**, choose one of the following options:
  + **<source\$1db>** – Uses the SQL Server database name as a schema name in PostgreSQL.
  + **<source\$1schema>** – Uses the SQL Server schema name as a schema name in PostgreSQL.
  + **<source\$1db>\$1<schema>** – Uses a combination of the SQL Server database and schema names as a schema name in PostgreSQL.
+ To keep the letter case of your source object names.

  To avoid conversion of object names to lower case, select **Avoid casting to lower case for case sensitive operations**. This option applies only when you turn on case sensitivity option in your target database.
+ To keep the parameter names from your source database.

  To add double quotation marks to the names of parameters in the converted code, select **Keep original parameter names**.

## Converting SQL Server partitions to PostgreSQL version 10 partitions
<a name="CHAP_Source.SQLServer.ToPostgreSQL.PG10Partitions"></a>

When you convert a Microsoft SQL Server database to Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) or Amazon Relational Database Service for PostgreSQL (Amazon RDS for PostgreSQL), be aware of the following.

In SQL Server, you create partitions with partition functions. When converting from a SQL Server portioned table to a PostgreSQL version 10 partitioned table, be aware of several potential issues:
+ SQL Server allows you to partition a table using a column without a NOT NULL constraint. In that case, all NULL values go to the leftmost partition. PostgreSQL doesn’t support NULL values for RANGE partitioning.
+ SQL Server allows you to create primary and unique keys for partitioned tables. For PostgreSQL, you create primary or unique keys for each partition directly. Thus, PRIMARY or UNIQUE KEY constraint must be removed from their parent table when migrating to PostgreSQL. The resulting key names take the format `<original_key_name>_<partition_number>`.
+ SQL Server allows you to create foreign key constraint from and to partitioned tables. PostgreSQL doesn’t support foreign keys referencing partitioned tables. Also, PostgreSQL doesn’t support foreign key references from a partitioned table to another table.
+ SQL Server allows you to create indexes for partitioned tables. For PostgreSQL, an index should be created for each partition directly. Thus, indexes must be removed from their parent tables when migrating to PostgreSQL. The resulting index names take the format `<original_index_name>_<partition_number>`.
+  PostgreSQL doesn’t support partitioned indexes.

## Migration considerations
<a name="CHAP_Source.SQLServer.ToPostgreSQL.MigrationConsiderations"></a>

Some things to consider when migrating a SQL Server schema to PostgreSQL: 
+ In PostgreSQL, all object’s names in a schema must be unique, including indexes. Index names must be unique in the schema of the base table. In SQL Server, an index name can be the same for different tables.

  To ensure the uniqueness of index names, AWS SCT gives you the option to generate unique index names if your index names are not unique. To do this, choose the option **Generate unique index names** in the project properties. By default, this option is enabled. If this option is enabled, unique index names are created using the format IX\$1table\$1name\$1index\$1name. If this option is disabled, index names aren’t changed.
+ A GOTO statement and a label can be used to change the order that statements are run in. Any Transact-SQL statements that follow a GOTO statement are skipped and processing continues at the label. GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. GOTO statements can also be nested.

  PostgreSQL doesn’t use GOTO statements. When AWS SCT converts code that contains a GOTO statement, it converts the statement to use a BEGIN…END or LOOP…END LOOP statement. You can find examples of how AWS SCT converts GOTO statements in the table following.  
**SQL Server GOTO statements and the converted PostgreSQL statements**    
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.SQLServer.ToPostgreSQL.html)
+ PostgreSQL doesn't support a MERGE statement. AWS SCT emulates the behavior of a MERGE statement in the following ways:
  + By INSERT ON CONFLICT construction.
  + By using the UPDATE FROM DML statement, such as MERGE without a WHEN NOT MATCHED clause.
  + By using CURSOR, such as with a MERGE with DELETE clause or by using a complex MERGE ON condition statement.
+ AWS SCT can add database triggers to the object tree when Amazon RDS is the target.
+ AWS SCT can add server-level triggers to the object tree when Amazon RDS is the target.
+ SQL Server automatically creates and manages `deleted` and `inserted` tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. AWS SCT can convert the usage of these tables inside DML trigger statements.
+ AWS SCT can add linked servers to the object tree when Amazon RDS is the target.
+ When migrating from Microsoft SQL Server to PostgreSQL, the built-in SUSER\$1SNAME function is converted as follows:
  + SUSER\$1SNAME – Returns the login name associated with a security identification number (SID).
  + SUSER\$1SNAME(<server\$1user\$1sid>) – Not supported.
  + SUSER\$1SNAME() CURRENT\$1USER – Returns the user name of the current execution context.
  + SUSER\$1SNAME(NULL) – Returns NULL.
+ Converting table-valued functions is supported. Table-valued functions return a table and can take the place of a table in a query.
+ PATINDEX returns the starting position of the first occurrence of a pattern in a specified expression on all valid text and character data types. It returns zeros if the pattern is not found. When converting from SQL Server to Amazon RDS for PostgreSQL, AWS SCT replaces application code that uses PATINDEX with aws\$1sqlserver\$1ext.patindex(<pattern character>, <expression character varying>) .
+ In SQL Server, a user-defined table type is a type that represents the definition of a table structure. You use a user-defined table type to declare table-value parameters for stored procedures or functions. You can also use a user-defined table type to declare table variables that you want to use in a batch or in the body of a stored procedure or function. AWS SCT emulated this type in PostgreSQL by creating a temporary table.

When converting from SQL Server to PostgreSQL, AWS SCT converts SQL Server system objects into recognizable objects in PostgreSQL. The following table shows how the system objects are converted. 

 


| MS SQL Server use cases | PostgreSQL substitution | 
| --- | --- | 
| SYS.SCHEMAS | AWS\$1SQLSERVER\$1EXT.SYS\$1SCHEMAS | 
| SYS.TABLES | AWS\$1SQLSERVER\$1EXT.SYS\$1TABLES | 
| SYS.VIEWS | AWS\$1SQLSERVER\$1EXT.SYS\$1VIEWS | 
| SYS.ALL\$1VIEWS | AWS\$1SQLSERVER\$1EXT.SYS\$1ALL\$1VIEWS | 
| SYS.TYPES | AWS\$1SQLSERVER\$1EXT.SYS\$1TYPES | 
| SYS.COLUMNS | AWS\$1SQLSERVER\$1EXT.SYS\$1COLUMNS | 
| SYS.ALL\$1COLUMNS | AWS\$1SQLSERVER\$1EXT.SYS\$1ALL\$1COLUMNS | 
| SYS.FOREIGN\$1KEYS | AWS\$1SQLSERVER\$1EXT.SYS\$1FOREIGN\$1KEYS | 
| SYS.SYSFOREIGNKEYS | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSFOREIGNKEYS | 
| SYS.FOREIGN\$1KEY\$1COLUMNS | AWS\$1SQLSERVER\$1EXT.SYS\$1FOREIGN\$1KEY\$1COLUMNS | 
| SYS.KEY\$1CONSTRAINTS | AWS\$1SQLSERVER\$1EXT.SYS\$1KEY\$1CONSTRAINTS | 
| SYS.IDENTITY\$1COLUMNS | AWS\$1SQLSERVER\$1EXT.SYS\$1IDENTITY\$1COLUMNS | 
| SYS.PROCEDURES | AWS\$1SQLSERVER\$1EXT.SYS\$1PROCEDURES | 
| SYS.INDEXES | AWS\$1SQLSERVER\$1EXT.SYS\$1INDEXES | 
| SYS.SYSINDEXES | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSINDEXES | 
| SYS.OBJECTS | AWS\$1SQLSERVER\$1EXT.SYS\$1OBJECTS | 
| SYS.ALL\$1OBJECTS | AWS\$1SQLSERVER\$1EXT.SYS\$1ALL\$1OBJECTS | 
| SYS.SYSOBJECTS | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSOBJECTS | 
| SYS.SQL\$1MODULES | AWS\$1SQLSERVER\$1EXT.SYS\$1SQL\$1MODULES | 
| SYS.DATABASES | AWS\$1SQLSERVER\$1EXT.SYS\$1DATABASES | 
| INFORMATION\$1SCHEMA.SCHEMATA  | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1SCHEMATA | 
| INFORMATION\$1SCHEMA.VIEWS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1VIEWS | 
| INFORMATION\$1SCHEMA.TABLES | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1TABLES | 
| INFORMATION\$1SCHEMA.COLUMNS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1COLUMNS | 
| INFORMATION\$1SCHEMA.CHECK\$1CONSTRAINTS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1CHECK\$1CONSTRAINTS | 
| INFORMATION\$1SCHEMA.REFERENTIAL\$1CONSTRAINTS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1REFERENTIAL\$1CONSTRAINTS | 
| INFORMATION\$1SCHEMA.TABLE\$1CONSTRAINTS | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1TABLE\$1CONSTRAINTS | 
| INFORMATION\$1SCHEMA.KEY\$1COLUMN\$1USAGE | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1KEY\$1COLUMN\$1USAGE | 
| INFORMATION\$1SCHEMA.CONSTRAINT\$1TABLE\$1USAGE | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1CONSTRAINT\$1TABLE\$1USAGE  | 
| INFORMATION\$1SCHEMA.CONSTRAINT\$1COLUMN\$1USAGE | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1CONSTRAINT\$1COLUMN\$1USAGE  | 
| INFORMATION\$1SCHEMA.ROUTINES | AWS\$1SQLSERVER\$1EXT.INFORMATION\$1SCHEMA\$1ROUTINES | 
| SYS.SYSPROCESSES | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSPROCESSES | 
| sys.system\$1objects | AWS\$1SQLSERVER\$1EXT.SYS\$1SYSTEM\$1OBJECTS | 

# Using an AWS SCT extension pack to emulate SQL Server Agent in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Agent"></a>

SQL Server Agent is a Microsoft Windows service that runs SQL Server jobs. SQL Server Agent runs jobs on a schedule, in response to a specific event, or on demand. For more information about SQL Server Agent, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent?view=sql-server-ver15).

PostgreSQL doesn't have an equivalent for SQL Server Agent. To emulate the SQL Server Agent features, AWS SCT creates an extension pack. This extension pack uses AWS Lambda and Amazon CloudWatch. AWS Lambda implements the interface that you use to manage schedules and run jobs. Amazon CloudWatch maintains the schedule rules.

AWS Lambda and Amazon CloudWatch use a JSON parameter to interact. This JSON parameter has the following structure.

```
{
    "mode": mode,
    "parameters": {
        list of parameters
    },
    "callback": procedure name
}
```

In the preceding example, *`mode`* is the type of the task and `list of parameters` is a set of parameters that depend on the type of the task. Also, `procedure name` is the name of the procedure that runs after the task is completed.

AWS SCT uses one Lambda function to control and run jobs. The CloudWatch rule starts the run of the job and provides the necessary information to start the job. When the CloudWatch rule triggers, it starts the Lambda function using the parameters from the rule.

To create a simple job that calls a procedure, use the following format.

```
{
    "mode": "run_job",
    "parameters": {
        "vendor": "mysql",
        "cmd": "lambda_db.nightly_job"
    }
}
```

To create a job with several steps, use the following format.

```
{
    "mode": "run_job",
    "parameters": {
        "job_name": "Job1",
        "enabled": "true",
        "start_step_id": 1,
        "notify_level_email": [0|1|2|3],
        "notify_email": email,
        "delete_level": [0|1|2|3],
        "job_callback": "ProcCallBackJob(job_name, code, message)",
        "step_callback": "ProcCallBackStep(job_name, step_id, code, message)"
    },
    "steps": [
        {
            "id":1,
            "cmd": "ProcStep1",
            "cmdexec_success_code": 0,
            "on_success_action": [|2|3|4],
            "on_success_step_id": 1,
            "on_fail_action": 0,
            "on_fail_step_id": 0,
            "retry_attempts": number,
            "retry_interval": number
        },
        {
            "id":2,
            "cmd": "ProcStep2",
            "cmdexec_success_code": 0,
            "on_success_action": [1|2|3|4],
            "on_success_step_id": 0,
            "on_fail_action": 0,
            "on_fail_step_id": 0,
            "retry_attempts": number,
            "retry_interval": number
        },
        ...
]
}
```

To emulate the SQL Server Agent behavior in PostgreSQL, the AWS SCT extension pack also creates the following tables and procedures.

## Tables that emulate SQL Server Agent in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Agent.Tables"></a>

To emulate SQL Server Agent, the extension pack uses the following tables:

**sysjobs**  
Stores the information about the jobs.

**sysjobsteps**  
Stores the information about the steps of a job.

**sysschedules**  
Stores the information about the job schedules.

**sysjobschedules**  
Stores the schedule information for individual jobs. 

**sysjobhistory**  
Stores the information about the runs of scheduled jobs.

## Procedures that emulate SQL Server Agent in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Agent.Procedures"></a>

To emulate SQL Server Agent, the extension pack uses the following procedures:

**sp\$1add\$1job**  
Adds a new job.

**sp\$1add\$1jobstep**  
Adds a step to a job.

**sp\$1add\$1schedule**  
Creates a new schedule rule in Amazon CloudWatch. You can use this schedule with any number of jobs.

**sp\$1attach\$1schedule**  
Sets a schedule for the selected job.

**sp\$1add\$1jobschedule**  
Creates a schedule rule for a job in Amazon CloudWatch and sets the target for this rule.

**sp\$1update\$1job**  
Updates the attributes of the previously created job.

**sp\$1update\$1jobstep**  
Updates the attributes of the step in a job.

**sp\$1update\$1schedule**  
Updates the attributes of a schedule rule in Amazon CloudWatch.

**sp\$1update\$1jobschedule**  
Updates the attributes of the schedule for the specified job.

**sp\$1delete\$1job**  
Deletes a job.

**sp\$1delete\$1jobstep**  
Deletes a job step from a job.

**sp\$1delete\$1schedule**  
Deletes a schedule.

**sp\$1delete\$1jobschedule**  
Deletes the schedule rule for the specified job from Amazon CloudWatch.

**sp\$1detach\$1schedule**  
Removes an association between a schedule and a job.

**get\$1jobs, update\$1job**  
Internal procedures that interact with AWS Elastic Beanstalk.

**sp\$1verify\$1job\$1date, sp\$1verify\$1job\$1time, sp\$1verify\$1job, sp\$1verify\$1jobstep, sp\$1verify\$1schedule, sp\$1verify\$1job\$1identifiers, sp\$1verify\$1schedule\$1identifiers**  
Internal procedures that check settings.

## Syntax for procedures that emulate SQL Server Agent in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Agent.Syntax"></a>

The `aws_sqlserver_ext.sp_add_job` procedure in the extension pack emulates the `msdb.dbo.sp_add_job` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-job-transact-sql?view=sql-server-ver15). 

```
par_job_name varchar,
par_enabled smallint = 1,
par_description varchar = NULL::character varying,
par_start_step_id integer = 1,
par_category_name varchar = NULL::character varying,
par_category_id integer = NULL::integer,
par_owner_login_name varchar = NULL::character varying,
par_notify_level_eventlog integer = 2,
par_notify_level_email integer = 0,
par_notify_level_netsend integer = 0,
par_notify_level_page integer = 0,
par_notify_email_operator_name varchar = NULL::character varying,
par_notify_netsend_operator_name varchar = NULL::character varying,
par_notify_page_operator_name varchar = NULL::character varying,
par_delete_level integer = 0,
inout par_job_id integer = NULL::integer,
par_originating_server varchar = NULL::character varying,
out returncode integer
```

The `aws_sqlserver_ext.sp_add_jobstep` procedure in the extension pack emulates the `msdb.dbo.sp_add_jobstep` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobstep-transact-sql?view=sql-server-ver15). 

```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_step_id integer = NULL::integer,
par_step_name varchar = NULL::character varying,
par_subsystem varchar = 'TSQL'::bpchar,
par_command text = NULL::text,
par_additional_parameters text = NULL::text,
par_cmdexec_success_code integer = 0,
par_on_success_action smallint = 1,
par_on_success_step_id integer = 0,
par_on_fail_action smallint = 2,
par_on_fail_step_id integer = 0,
par_server varchar = NULL::character varying,
par_database_name varchar = NULL::character varying,
par_database_user_name varchar = NULL::character varying,
par_retry_attempts integer = 0,
par_retry_interval integer = 0,
par_os_run_priority integer = 0,
par_output_file_name varchar = NULL::character varying,
par_flags integer = 0,
par_proxy_id integer = NULL::integer,
par_proxy_name varchar = NULL::character varying,
inout par_step_uid char = NULL::bpchar,
out returncode integer
```

The `aws_sqlserver_ext.sp_add_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_add_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-schedule-transact-sql?view=sql-server-ver15). 

```
par_schedule_name varchar,
par_enabled smallint = 1,
par_freq_type integer = 0,
par_freq_interval integer = 0,
par_freq_subday_type integer = 0,
par_freq_subday_interval integer = 0,
par_freq_relative_interval integer = 0,
par_freq_recurrence_factor integer = 0,
par_active_start_date integer = NULL::integer,
par_active_end_date integer = 99991231,
par_active_start_time integer = 0,
par_active_end_time integer = 235959,
par_owner_login_name varchar = NULL::character varying,
*inout par_schedule_uid char = NULL::bpchar,*
inout par_schedule_id integer = NULL::integer,
par_originating_server varchar = NULL::character varying,
out returncode integer
```

The `aws_sqlserver_ext.sp_attach_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_attach_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-attach-schedule-transact-sql?view=sql-server-ver15). 

```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_schedule_id integer = NULL::integer,
par_schedule_name varchar = NULL::character varying,
par_automatic_post smallint = 1,
out returncode integer
```

The `aws_sqlserver_ext.sp_add_jobschedule` procedure in the extension pack emulates the `msdb.dbo.sp_add_jobschedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobschedule-transact-sql?view=sql-server-ver15). 

```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_name varchar = NULL::character varying,
par_enabled smallint = 1,
par_freq_type integer = 1,
par_freq_interval integer = 0,
par_freq_subday_type integer = 0,
par_freq_subday_interval integer = 0,
par_freq_relative_interval integer = 0,
par_freq_recurrence_factor integer = 0,
par_active_start_date integer = NULL::integer,
par_active_end_date integer = 99991231,
par_active_start_time integer = 0,
par_active_end_time integer = 235959,
inout par_schedule_id integer = NULL::integer,
par_automatic_post smallint = 1,
inout par_schedule_uid char = NULL::bpchar,
out returncode integer
```

The `aws_sqlserver_ext.sp_delete_job` procedure in the extension pack emulates the `msdb.dbo.sp_delete_job` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-job-transact-sql?view=sql-server-ver15). 

```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_originating_server varchar = NULL::character varying,
par_delete_history smallint = 1,
par_delete_unused_schedule smallint = 1,
out returncode integer
```

The `aws_sqlserver_ext.sp_delete_jobstep` procedure in the extension pack emulates the `msdb.dbo.sp_delete_jobstep` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-jobsteplog-transact-sql?view=sql-server-ver15). 

```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_step_id integer = NULL::integer,
out returncode integer
```

The `aws_sqlserver_ext.sp_delete_jobschedule` procedure in the extension pack emulates the `msdb.dbo.sp_delete_jobschedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-jobschedule-transact-sql?view=sql-server-ver15). 

```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_name varchar = NULL::character varying,
par_keep_schedule integer = 0,
par_automatic_post smallint = 1,
out returncode integer
```

The `aws_sqlserver_ext.sp_delete_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_delete_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-delete-schedule-transact-sql?view=sql-server-ver15). 

```
par_schedule_id integer = NULL::integer,
par_schedule_name varchar = NULL::character varying,
par_force_delete smallint = 0,
par_automatic_post smallint = 1,
out returncode integer
```

The `aws_sqlserver_ext.sp_detach_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_detach_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-detach-schedule-transact-sql?view=sql-server-ver15). 

```
par_job_id integer = NULL::integer,
par_job_name varchar = NULL::character varying,
par_schedule_id integer = NULL::integer,
par_schedule_name varchar = NULL::character varying,
par_delete_unused_schedule smallint = 0,
par_automatic_post smallint = 1,
out returncode integer
```

The `aws_sqlserver_ext.sp_update_job` procedure in the extension pack emulates the `msdb.dbo.sp_update_job` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-job-transact-sql?view=sql-server-ver15). 

```
par_job_id integer = NULL::integer
par_job_name varchar = NULL::character varying
par_new_name varchar = NULL::character varying
par_enabled smallint = NULL::smallint
par_description varchar = NULL::character varying
par_start_step_id integer = NULL::integer
par_category_name varchar = NULL::character varying
par_owner_login_name varchar = NULL::character varying
par_notify_level_eventlog integer = NULL::integer
par_notify_level_email integer = NULL::integer
par_notify_level_netsend integer = NULL::integer
par_notify_level_page integer = NULL::integer
par_notify_email_operator_name varchar = NULL::character varying
par_notify_netsend_operator_name varchar = NULL::character varying
par_notify_page_operator_name varchar = NULL::character varying
par_delete_level integer = NULL::integer
par_automatic_post smallint = 1
out returncode integer
```

The `aws_sqlserver_ext.sp_update_jobschedule` procedure in the extension pack emulates the `msdb.dbo.sp_update_jobschedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-jobschedule-transact-sql?view=sql-server-ver15). 

```
par_job_id integer = NULL::integer
par_job_name varchar = NULL::character varying
par_name varchar = NULL::character varying
par_new_name varchar = NULL::character varying
par_enabled smallint = NULL::smallint
par_freq_type integer = NULL::integer
par_freq_interval integer = NULL::integer
par_freq_subday_type integer = NULL::integer
par_freq_subday_interval integer = NULL::integer
par_freq_relative_interval integer = NULL::integer
par_freq_recurrence_factor integer = NULL::integer
par_active_start_date integer = NULL::integer
par_active_end_date integer = NULL::integer
par_active_start_time integer = NULL::integer
                par_active_end_time integer = NULL::integer
par_automatic_post smallint = 1
out returncode integer
```

The `aws_sqlserver_ext.sp_update_jobstep` procedure in the extension pack emulates the `msdb.dbo.sp_update_jobstep` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-jobstep-transact-sql?view=sql-server-ver15). 

```
par_job_id integer = NULL::integer
par_job_name varchar = NULL::character varying
par_step_id integer = NULL::integer
par_step_name varchar = NULL::character varying
par_subsystem varchar = NULL::character varying
par_command text = NULL::text
par_additional_parameters text = NULL::text
par_cmdexec_success_code integer = NULL::integer
par_on_success_action smallint = NULL::smallint
par_on_success_step_id integer = NULL::integer
par_on_fail_action smallint = NULL::smallint
par_on_fail_step_id integer = NULL::integer
par_server varchar = NULL::character varying
par_database_name varchar = NULL::character varying
par_database_user_name varchar = NULL::character varying
par_retry_attempts integer = NULL::integer
par_retry_interval integer = NULL::integer
par_os_run_priority integer = NULL::integer
par_output_file_name varchar = NULL::character varying
par_flags integer = NULL::integer
par_proxy_id integer = NULL::integer
par_proxy_name varchar = NULL::character varying
out returncode integer
```

The `aws_sqlserver_ext.sp_update_schedule` procedure in the extension pack emulates the `msdb.dbo.sp_update_schedule` procedure. For more information about the source SQL Server Agent procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-schedule-transact-sql?view=sql-server-ver15). 

```
par_schedule_id integer = NULL::integer
par_name varchar = NULL::character varying
par_new_name varchar = NULL::character varying
par_enabled smallint = NULL::smallint
par_freq_type integer = NULL::integer
par_freq_interval integer = NULL::integer
par_freq_subday_type integer = NULL::integer
par_freq_subday_interval integer = NULL::integer
par_freq_relative_interval integer = NULL::integer
par_freq_recurrence_factor integer = NULL::integer
par_active_start_date integer = NULL::integer
par_active_end_date integer = NULL::integer
par_active_start_time integer = NULL::integer
par_active_end_time integer = NULL::integer
par_owner_login_name varchar = NULL::character varying
par_automatic_post smallint = 1
out returncode integer
```

## Examples for using procedures that emulate SQL Server Agent in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Agent.Examples"></a>

To add a new job, use the `aws_sqlserver_ext.sp_add_job` procedure as shown following.

```
SELECT * FROM aws_sqlserver_ext.sp_add_job (
    par_job_name := 'test_job',
    par_enabled := 1::smallint,
    par_start_step_id := 1::integer,
    par_category_name := '[Uncategorized (Local)]',
    par_owner_login_name := 'sa');
```

To add a new job step, use the `aws_sqlserver_ext.sp_add_jobstep` procedure as shown following.

```
SELECT * FROM aws_sqlserver_ext.sp_add_jobstep (
    par_job_name := 'test_job',
    par_step_id := 1::smallint,
    par_step_name := 'test_job_step1',
    par_subsystem := 'TSQL',
    par_command := 'EXECUTE [dbo].[PROC_TEST_JOB_STEP1];',
    par_server := NULL,
    par_database_name := 'GOLD_TEST_SS');
```

To add a simple schedule, use the `aws_sqlserver_ext.sp_add_schedule` procedure as shown following.

```
SELECT * FROM aws_sqlserver_ext.sp_add_schedule(
    par_schedule_name := 'RunOnce',
    par_freq_type := 1,
    par_active_start_time := 233000);
```

To set a schedule for a job, use the `aws_sqlserver_ext.sp_attach_schedule` procedure as shown following.

```
SELECT * FROM aws_sqlserver_ext.sp_attach_schedule (
    par_job_name := 'test_job',
    par_schedule_name := 'NightlyJobs');
```

To create a schedule for a job, use the `aws_sqlserver_ext.sp_add_jobschedule` procedure as shown following.

```
SELECT * FROM aws_sqlserver_ext.sp_add_jobschedule (
    par_job_name := 'test_job2',
    par_name := 'test_schedule2',
    par_enabled := 1::smallint,
    par_freq_type := 4,
    par_freq_interval := 1,
    par_freq_subday_type := 4,
    par_freq_subday_interval := 1,
    par_freq_relative_interval := 0,
    par_freq_recurrence_factor := 0,
    par_active_start_date := 20100801,
    par_active_end_date := 99991231,
    par_active_start_time := 0,
    par_active_end_time := 0);
```

## Use case examples for emulating SQL Server Agent in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Agent.UseCases"></a>

If your source database code uses SQL Server Agent to run jobs, you can use the SQL Server to PostgreSQL extension pack for AWS SCT to convert this code to PostgreSQL. The extension pack uses AWS Lambda functions to emulate the behavior of SQL Server Agent.

You can create a new AWS Lambda function or register an existing function.

**To create a new AWS Lambda function**

1. In AWS SCT, in the target database tree, open the context (right-click) menu, choose **Apply extension pack for**, and then choose **PostgreSQL**. 

   The extension pack wizard appears. 

1. On the **SQL Server Agent emulation service** tab, do the following: 
   + Choose **Create an AWS Lambda function**.
   + For **Database login**, enter the name of the target database user.
   + For **Database password**, enter the password for the user name that you entered on the preceding step.
   + For **Python library folder**, enter the path to your Python library folder.
   + Choose **Create AWS Lambda function**, and then choose **Next**.

**To register an AWS Lambda function that you deployed earlier**
+ Run the following script on your target database.

  ```
  SELECT
      FROM aws_sqlserver_ext.set_service_setting(
          p_service := 'JOB', 
          p_setting := 'LAMBDA_ARN', 
          p_value := ARN)
  ```

  In the preceding example, *`ARN`* is the Amazon Resource Name (ARN) of the deployed AWS Lambda function.

The following example creates a simple task that consists of one step. Every five minutes, this task runs the previously created `job_example` function. This function inserts records into the `job_example_table` table.

**To create this simple task**

1. Create a job using the `aws_sqlserver_ext.sp_add_job` function as shown following.

   ```
   SELECT
       FROM aws_sqlserver_ext.sp_add_job (
           par_job_name := 'test_simple_job');
   ```

1. Create a job step using the `aws_sqlserver_ext.sp_add_jobstep` function as shown following.

   ```
   SELECT
       FROM aws_sqlserver_ext.sp_add_jobstep (
           par_job_name := 'test_simple_job', 
           par_step_name := 'test_simple_job_step1', 
           par_command := 'PERFORM job_simple_example;');
   ```

   The job step specifies what the function does.

1. Create a scheduler for the job using the `aws_sqlserver_ext.sp_add_jobschedule` function as shown following.

   ```
   SELECT
       FROM aws_sqlserver_ext.sp_add_jobschedule (
           par_job_name := 'test_simple_job', 
           par_name := 'test_schedule', 
           par_freq_type := 4, /* Daily */
           par_freq_interval := 1, /* frequency_interval is unused */
           par_freq_subday_type := 4, /* Minutes */
           par_freq_subday_interval := 5 /* 5 minutes */);
   ```

   The job step specifies what the function does.

To delete this job, use the `aws_sqlserver_ext.sp_delete_job` function as shown following.

```
PERFORM aws_sqlserver_ext.sp_delete_job(
    par_job_name := 'PeriodicJob1'::character varying,
    par_delete_history := 1::smallint,
    par_delete_unused_schedule := 1::smallint);
```

# Using an AWS SCT extension pack to emulate SQL Server Database Mail in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Mail"></a>

You can use SQL Server Database Mail to send email messages to users from the SQL Server Database Engine or Azure SQL Managed Instance. These email messages can contain query results or include files from any resource on your network. For more information about SQL Server Database Mail, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail?view=sql-server-ver15).

PostgreSQL doesn't have an equivalent for SQL Server Database Mail. To emulate the SQL Server Database Mail features, AWS SCT creates an extension pack. This extension pack uses AWS Lambda and Amazon Simple Email Service (Amazon SES). AWS Lambda provides users with an interface to interact with Amazon SES email sending service. To set up this interaction, add the Amazon Resource Name (ARN) of your Lambda function. 

For a new email account, use the following command.

```
do
$$
begin
PERFORM sysmail_add_account_sp (
    par_account_name :='your_account_name',
    par_email_address := 'your_account_email',
    par_display_name := 'your_account_display_name',
    par_mailserver_type := 'AWSLAMBDA'
    par_mailserver_name := 'ARN'
);
end;
$$ language plpgsql;
```

To add the ARN of your Lambda function to the existing email account, use the following command.

```
do
$$
begin
PERFORM sysmail_update_account_sp (
    par_account_name :='existind_account_name',
    par_mailserver_type := 'AWSLAMBDA'
    par_mailserver_name := 'ARN'
);
end;
$$ language plpgsql;
```

In the preceding examples, *`ARN`* is the ARN of your Lambda function.

To emulate the SQL Server Database Mail behavior in PostgreSQL, the AWS SCT extension pack uses the following tables, views, and procedures.

## Tables that emulate SQL Server Database Mail in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Mail.Tables"></a>

To emulate SQL Server Database Mail, the extension pack uses the following tables:

**sysmail\$1account**  
Stores the information about the email accounts.

**sysmail\$1profile**  
Stores the information about the user profiles.

**sysmail\$1server**  
Stores the information about the email servers.

**sysmail\$1mailitems**  
Stores the list of the email messages.

**sysmail\$1attachments**  
Contains one row for each email attachment.

**sysmail\$1log**  
Stores the service information about sending email messages.

**sysmail\$1profileaccount**  
Stores the information about the user profiles and email accounts.

## Views that emulate SQL Server Database Mail in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Mail.Views"></a>

To emulate SQL Server Database Mail, AWS SCT creates the following views in the PostgreSQL database to ensure compatibility. The extension pack doesn't use them, but your converted code can query these views.

**sysmail\$1allitems**  
Includes a list of all emails.

**sysmail\$1faileditems**  
Includes a list of emails that couldn't be sent.

**sysmail\$1sentitems**  
Includes a list of sent emails.

**sysmail\$1unsentitems**  
Includes a list of emails that aren't sent yet.

**sysmail\$1mailattachments**  
Includes a list of attached files.

## Procedures that emulate SQL Server Database Mail in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Mail.Procedures"></a>

To emulate SQL Server Database Mail, the extension pack uses the following procedures:

**sp\$1send\$1dbmail**  
Sends an email to the specified recipients.

**sysmail\$1add\$1profile\$1sp**  
Creates a new user profile.

**sysmail\$1add\$1account\$1sp**  
Creates a new email account that stores such information as Simple Mail Transfer Protocol (SMTP) credentials, and so on.

**sysmail\$1add\$1profileaccount\$1sp**  
Adds an email account to the specified user profile.

**sysmail\$1update\$1profile\$1sp**  
Changes the attributes of the user profile such as description, name, and so on.

**sysmail\$1update\$1account\$1sp**  
Changes the information in the existing email account.

**sysmail\$1update\$1profileaccount\$1sp**  
Updates the email account information in the specified user profile.

**sysmail\$1delete\$1profileaccount\$1sp**  
Removes an email account from the specified user profile.

**sysmail\$1delete\$1account\$1sp**  
Deletes the email account.

**sysmail\$1delete\$1profile\$1sp**  
Deletes the user profile.

**sysmail\$1delete\$1mailitems\$1sp**  
Deletes emails from internal tables.

**sysmail\$1help\$1profile\$1sp**  
Displays information about the user profile.

**sysmail\$1help\$1account\$1sp**  
Displays information about the email account.

**sysmail\$1help\$1profileaccount\$1sp**  
Displays information about email accounts associated with the user profile.

**sysmail\$1dbmail\$1json**  
An internal procedure that generates JSON requests for AWS Lambda functions.

**sysmail\$1verify\$1profile\$1sp, sysmail\$1verify\$1account\$1sp, sysmail\$1verify\$1addressparams\$1sp**  
Internal procedures that check settings.

**sp\$1get\$1dbmail, sp\$1set\$1dbmail, sysmail\$1dbmail\$1xml**  
Deprecated internal procedures.

## Syntax for procedures that emulate SQL Server Database Mail in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Mail.Syntax"></a>

The `aws_sqlserver_ext.sp_send_dbmail` procedure in the extension pack emulates the `msdb.dbo.sp_send_dbmail` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver15).

```
par_profile_name varchar = NULL::character varying,
par_recipients text = NULL::text,
par_copy_recipients text = NULL::text,
par_blind_copy_recipients text = NULL::text,
par_subject varchar = NULL::character varying,
par_body text = NULL::text,
par_body_format varchar = NULL::character varying,
par_importance varchar = 'NORMAL'::character varying,
par_sensitivity varchar = 'NORMAL'::character varying,
par_file_attachments text = NULL::text,
par_query text = NULL::text,
par_execute_query_database varchar = NULL::character varying,
par_attach_query_result_as_file smallint = 0,
par_query_attachment_filename varchar = NULL::character varying,
par_query_result_header smallint = 1,
par_query_result_width integer = 256,
par_query_result_separator VARCHAR = ' '::character varying,
par_exclude_query_output smallint = 0,
par_append_query_error smallint = 0,
par_query_no_truncate smallint = 0,
par_query_result_no_padding smallint = 0,
out par_mailitem_id integer,
par_from_address text = NULL::text,
par_reply_to text = NULL::text,
out returncode integer
```

The `aws_sqlserver_ext.sysmail_delete_mailitems_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_delete_mailitems_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-mailitems-sp-transact-sql?view=sql-server-ver15).

```
par_sent_before timestamp = NULL::timestamp without time zone,
par_sent_status varchar = NULL::character varying,
out returncode integer
```

The `aws_sqlserver_ext.sysmail_add_profile_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_add_profile_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-add-profile-sp-transact-sql?view=sql-server-ver15).

```
par_profile_name varchar,
par_description varchar = NULL::character varying,
out par_profile_id integer,
out returncode integer
```

The `aws_sqlserver_ext.sysmail_add_account_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_add_account_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-add-account-sp-transact-sql?view=sql-server-ver15).

```
par_account_name varchar
par_email_address varchar
par_display_name varchar = NULL::character varying
par_replyto_address varchar = NULL::character varying
par_description varchar = NULL::character varying
par_mailserver_name varchar = NULL::character varying
par_mailserver_type varchar = 'SMTP'::bpchar
par_port integer = 25
par_username varchar = NULL::character varying
par_password varchar = NULL::character varying
par_use_default_credentials smallint = 0
par_enable_ssl smallint = 0
out par_account_id integer
out returncode integer
```

The `aws_sqlserver_ext.sysmail_add_profileaccount_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_add_profileaccount_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-add-profileaccount-sp-transact-sql?view=sql-server-ver15).

```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
par_sequence_number integer = NULL::integer,
out returncode integer
```

The `aws_sqlserver_ext.sysmail_help_profile_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_help_profile_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-profile-sp-transact-sql?view=sql-server-ver15).

```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
out returncode integer
```

The `aws_sqlserver_ext.sysmail_update_profile_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_update_profile_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-update-profile-sp-transact-sql?view=sql-server-ver15).

```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_description varchar = NULL::character varying,
out returncode integer
```

The `aws_sqlserver_ext.sysmail_delete_profile_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_delete_profile_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-profile-sp-transact-sql?view=sql-server-ver15).

```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_force_delete smallint = 1,
out returncode integer
```

The `aws_sqlserver_ext.sysmail_help_account_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_help_account_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-account-sp-transact-sql?view=sql-server-ver15).

```
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
out returncode integer
```

The `aws_sqlserver_ext.sysmail_update_account_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_update_account_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-update-account-sp-transact-sql?view=sql-server-ver15).

```
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
par_email_address varchar = NULL::character varying,
par_display_name varchar = NULL::character varying,
par_replyto_address varchar = NULL::character varying,
par_description varchar = NULL::character varying,
par_mailserver_name varchar = NULL::character varying,
par_mailserver_type varchar = NULL::character varying,
par_port integer = NULL::integer,
par_username varchar = NULL::character varying,
par_password varchar = NULL::character varying,
par_use_default_credentials smallint = NULL::smallint,
par_enable_ssl smallint = NULL::smallint,
par_timeout integer = NULL::integer,
par_no_credential_change smallint = NULL::smallint,
out returncode integer
```

The `aws_sqlserver_ext.sysmail_delete_account_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_delete_account_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-account-sp-transact-sql?view=sql-server-ver15).

```
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
out returncode integer
```

The `aws_sqlserver_ext.sysmail_help_profileaccount_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_help_profileaccount_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-help-profileaccount-sp-transact-sql?view=sql-server-ver15).

```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
out returncode integer
```

The `aws_sqlserver_ext.sysmail_update_profileaccount_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_update_profileaccount_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-update-profileaccount-sp-transact-sql?view=sql-server-ver15).

```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
par_sequence_number integer = NULL::integer,
out returncode integer
```

The `aws_sqlserver_ext.sysmail_delete_profileaccount_sp` procedure in the extension pack emulates the `msdb.dbo.sysmail_delete_profileaccount_sp` procedure. For more information about the source SQL Server Database Mail procedure, see [Microsoft technical documentation](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-profileaccount-sp-transact-sql?view=sql-server-ver15).

```
par_profile_id integer = NULL::integer,
par_profile_name varchar = NULL::character varying,
par_account_id integer = NULL::integer,
par_account_name varchar = NULL::character varying,
out returncode integer
```

## Examples for using procedures that emulate SQL Server Database Mail in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Mail.Examples"></a>

To send an email, use the `aws_sqlserver_ext.sp_send_dbmail` procedure as shown following.

```
PERFORM sp_send_dbmail (
    par_profile_name := 'Administrator',
    par_recipients := 'hello@rusgl.info',
    par_subject := 'Automated Success Message',
    par_body := 'The stored procedure finished'
);
```

The following example shows how to send an email with query results.

```
PERFORM sp_send_dbmail (
    par_profile_name := 'Administrator',
    par_recipients := 'hello@rusgl.info',
    par_subject := 'Account with id = 1',
    par_query := 'SELECT COUNT(*)FROM Account WHERE id = 1'
);
```

The following example shows how to send an email with HTML code.

```
DECLARE var_tableHTML TEXT;
SET var_tableHTML := CONCAT(
    '<H1>Work Order Report</H1>',
    '<table border="1">',
    '<tr><th>Work Order ID</th><th>Product ID</th>',
    '<th>Name</th><th>Order Qty</th><th>Due Date</th>',
    '<th>Expected Revenue</th></tr>',
    '</table>'
);
PERFORM sp_send_dbmail (
    par_recipients := 'hello@rusgl.info',
    par_subject := 'Work Order List',
    par_body := var_tableHTML,
    par_body_format := 'HTML'
);
```

To delete emails, use the `aws_sqlserver_ext.sysmail_delete_mailitems_sp` procedure as shown following.

```
DECLARE var_GETDATE datetime;
SET var_GETDATE = NOW();
PERFORM sysmail_delete_mailitems_sp (
    par_sent_before := var_GETDATE
);
```

The following example shows how to delete the oldest emails.

```
PERFORM sysmail_delete_mailitems_sp (
    par_sent_before := '31.12.2015'
);
```

The following example shows how to delete all emails that can't be sent.

```
PERFORM sysmail_delete_mailitems_sp (
    par_sent_status := 'failed'
);
```

To create a new user profile, use the `aws_sqlserver_ext.sysmail_add_profile_sp` procedure as shown following.

```
PERFORM sysmail_add_profile_sp (
    profile_name := 'Administrator',
    par_description := 'administrative mail'
);
```

The following example shows how to create a new profile and save the unique profile identifier in a variable.

```
DECLARE var_profileId INT;
SELECT par_profile_id
    FROM sysmail_add_profile_sp (
        profile_name := 'Administrator',
        par_description := ' Profile used for administrative mail.')
    INTO var_profileId;
    
SELECT var_profileId;
```

To create a new email account, use the `aws_sqlserver_ext.sysmail_add_account_sp` procedure as shown following.

```
PERFORM sysmail_add_account_sp (
    par_account_name :='Audit Account',
    par_email_address := 'dba@rusgl.info',
    par_display_name := 'Test Automated Mailer',
    par_description := 'Account for administrative e-mail.',
    par_mailserver_type := 'AWSLAMBDA'
    par_mailserver_name := 'arn:aws:lambda:us-west-2:555555555555:function:pg_v3'
);
```

To add an email account to the user profile, use the `aws_sqlserver_ext.sysmail_add_profileaccount_sp` procedure as shown following.

```
PERFORM sysmail_add_profileaccount_sp (
    par_account_name := 'Administrator',
    par_account_name := 'Audit Account',
    par_sequence_number := 1
);
```

## Use case examples for emulating SQL Server Database Mail in PostgreSQL
<a name="CHAP_Source.SQLServer.ToPostgreSQL.ExtensionPack.Mail.UseCases"></a>

If your source database code uses SQL Server Database Mail to send emails, you can use the AWS SCT extension pack to convert this code to PostgreSQL.

**To send an email from your PostgreSQL database**

1. Create and configure your AWS Lambda function.

1. Apply the AWS SCT extension pack.

1. Create a user profile using the `sysmail_add_profile_sp` function as shown following.

1. Create an email account using the `sysmail_add_account_sp` function as shown following.

1. Add this email account to your user profile using the `sysmail_add_profileaccount_sp` function as shown following.

   ```
   CREATE OR REPLACE FUNCTION aws_sqlserver_ext.
   proc_dbmail_settings_msdb()
   RETURNS void
   AS
   $BODY$
   BEGIN
   PERFORM aws_sqlserver_ext.sysmail_add_profile_sp(
       par_profile_name := 'Administrator',
       par_description := 'administrative mail'
   );
   PERFORM aws_sqlserver_ext.sysmail_add_account_sp(
       par_account_name := 'Audit Account',
       par_description := 'Account for administrative e-mail.',
       par_email_address := 'dba@rusgl.info',
       par_display_name := 'Test Automated Mailer',
       par_mailserver_type := 'AWSLAMBDA'
       par_mailserver_name := 'your_ARN'
   );
   PERFORM aws_sqlserver_ext.sysmail_add_profileaccount_sp(
       par_profile_name := 'Administrator',
       par_account_name := 'Audit Account',
       par_sequence_number := 1
   );
   END;
   $BODY$
   LANGUAGE plpgsql;
   ```

1. Send an email using the `sp_send_dbmail` function as shown following.

   ```
   CREATE OR REPLACE FUNCTION aws_sqlserver_ext.
   proc_dbmail_send_msdb()
   RETURNS void
   AS
   $BODY$
   BEGIN
   PERFORM aws_sqlserver_ext.sp_send_dbmail(
       par_profile_name := 'Administrator',
       par_recipients := 'hello@rusgl.info',
       par_body := 'The stored procedure finished',
       par_subject := 'Automated Success Message'
   );
   END;
   $BODY$
   LANGUAGE plpgsql;
   ```

To view the information about all user profiles, use the `sysmail_help_profile_sp` procedure as shown following.

```
SELECT FROM aws_sqlserver_ext.sysmail_help_profile_sp();
```

The following example displays the information about the specific user profile.

```
select from aws_sqlserver_ext.sysmail_help_profile_sp(par_profile_id := 1);
select from aws_sqlserver_ext.sysmail_help_profile_sp(par_profile_name := 'Administrator');
```

To view the information about all email accounts, use the `sysmail_help_account_sp` procedure as shown following.

```
select from aws_sqlserver_ext.sysmail_help_account_sp();
```

The following example displays the information about the specific email account.

```
select from aws_sqlserver_ext.sysmail_help_account_sp(par_account_id := 1);
select from aws_sqlserver_ext.sysmail_help_account_sp(par_account_name := 'Audit Account');
```

To view the information about all email accounts that are associated with the user profiles, use the `sysmail_help_profileaccount_sp` procedure as shown following.

```
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp();
```

The following example filters the records by identifier, profile name, or account name.

```
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_profile_id := 1);
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_profile_id := 1, par_account_id := 1);
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_profile_name := 'Administrator');
select from aws_sqlserver_ext.sysmail_help_profileaccount_sp(par_account_name := 'Audit Account');
```

To change the user profile name or description, use the `sysmail_update_profile_sp` procedure as shown following.

```
select aws_sqlserver_ext.sysmail_update_profile_sp(
    par_profile_id := 2,
    par_profile_name := 'New profile name'
);
```

To change the email account settings, use the `ysmail_update_account_sp` procedure as shown following.

```
select from aws_sqlserver_ext.sysmail_update_account_sp (
    par_account_name := 'Audit Account',
    par_mailserver_name := 'arn:aws:lambda:region:XXXXXXXXXXXX:function:func_test',
    par_mailserver_type := 'AWSLAMBDA'
);
```