SQL API Code Examples

Updated 18 March 2025

Sending and receiving SMS through the SQL API and checking the results of transmission requests is relatively simple.

Powershell examples to send and receive SMS via SQL API

Instructions:

Download and Extract to your computer.
Edit the powershell scripts in notepad.
Type in your SQL Connection credentials and SMS variables and save the file.
Change the recipient number to your own mobile phone.
Open the saved file in powershell and execute. (Right click on the ps1 file and select run with powershell)

Overview sending SMS messages using SQL Server (SQL API)

BNS Enterprise SMS Server runs on a Windows server VM running in your data centre or in cloud such as: AWS & Azure

SQL Server is an ideal way to interface to the SMS platform. Azure SQL Managed instance and AWS RDS MS SQL Server is fully supported.

Multiple applications can be authorised to access the database which is used to act as a message queue.

The diagram below shows the database (SMS-SQL-API) providing the interface to send and receive SMS messages.

The database contains 3 tables to:

Send a SMS message (SQL_API_FROM_App)
Check the results of submissions (SQL_API_TO_App_Results)
Receive incoming SMS messages (SQL_API_TO_App_Incoming)

The database can be named as required. For a global deployment across regions your solutions architect may call the database in your region something like: AZURE-SMS-SQL-API-ANZ meaning AZURE based SMS submission service for Australia and NZ.

Architecture diagram above shows how multiple SMS Servers can provide high availability for message queuing. The design allows scalability across your SMS servers.

SQL API Message flow outbound SMS between your application and the SMS Server

SQL API Message flow inbound SMS between the SMS Server and your application

Submitting a SMS request from your application

You will need to obtain a logical identifier called a business application sender email address dedicated to your application. The email address does not need to be an actual email address. It is used purely to identify your application request.

Security for your application to access SQL DB tables is controlled by your security team who manage the SQL Server where the database is located.

Your application will need a local SQL user login name to the SQL API database tables. Your database administrator will have the required permissions documented for row level security to partition your application from other applications.

Your application will write SMS requests for transmission using this table:

TABLE Tbl_SQL_API_FROM_APP

Column Mandatory values	Format	Comments
Main_App_UserName	VARCHAR(100) NOT NULL	This is your SQL User Login name. You can use the `SUSER_NAME()` function to get the login name that you’re currently using to access SQL Server
pk_Main_EventId	INT IDENTITY PRIMARY KEY	Primary key. ID which increments by 1. Your requests are processed first in first out order together with priority.
Main_App_Sender_Email	VARCHAR(100) NOT NULL	This is the email address of your business application. It is assigned by your SMS administrator
Main_SQL_Record_Created (Note this must be UTC Datetime)	DATETIME NULL	UTC Date and time that the record was written to the table.
Main_Priority	CHAR (1) NOT NULL	'H'=High, 'N'=Normal. Note: only use High priority if your application has been approved to use High Priority such as critical tokens which must get to a customer. If you enter 'H' in this field and the Main_App_Sender_Email in the business application profile is not HIGH then your SMS request will be rejected.
Main_App_Message_Type	INT NOT NULL	1=SMS 2=BulkSMS with recipients defined in Main_App_Bulk_Recipients
Main_App_Message	VARCHAR(MAX) NOT NULL	The SMS message to send. Contact your administrator to check the length of message you can send from your application. There is a limit imposed by SMS Service providers but also your organisation may limit your application within the SMS server. Default setting is 2000 characters maximum message size. Try to keep your outbound messages as small as possible to minimise cost. Character set supported is GSM03.38, see below for further information
Main_App_Receiver_Destination	VARCHAR(100) NOT NULL	Receiver SMS number. Use E.164 format For examples: Australian 61412xxxxxx UK 44xxxxxxxxx Philippines 639661234567


Optional values		Comments
Main_DoNotSendBefore	VARCHAR(4) NULL	Format is HHMM. Only required if you need to over-ride any default setting in the business application profile. The SMS administrator can setup a default of say 0800 hours in the business application profile to automatically tell the SMS platform not to release the SMS until 0800 hours. 0800 hours is based on the time zone system date of the SMS Server.
Main_DoNotSendAfter	VARCHAR(4) NULL	Format is HHMM. Only required if you need to over-ride any default setting in the business application profile. The SMS administrator can setup a default of say 2000 hours in the business application profile to automatically tell the SMS platform not to release the SMS until 2000hours. 2000 hours is based on the time zone system date of the SMS Server.
Main_AppCustom1 thru Main_AppCustom10	VARCHAR(100) NULL	These values are entirely up to you how you use them. They are very useful to allow your application to correlate transactions with results or incoming replies.
Main_App_Reference	VARCHAR(150) NULL	This value is any value you need to refer back to in your work flow. It could be a work order number, transaction number or any other value to help you identify what the SMS results relate to.
Main_ App_Protective_Marking	VARCHAR(20) NULL	This is reserved for Government customers who may wish to supply the Protective Marking similar to Email eg: SEC=UNOFFICIAL. If supplied the marking will be checked against the security classification of the SMS platform as to whether or not it can be sent to a public network.
Main_App_Bulk_Recipients	VARCHAR(MAX) NULL	List of mobile numbers E.164 eg: 61412123456,61413309222,etc

GSM03.38 character set supported by this interface.

How to obtain the results of your SMS request

The SMS platform writes results of the progress of your request to the following table. There may be one or more records written to this table which your application will read then delete.

Microsoft SQL Server Row Level Security is used to partition your records from other applications. The SQL User login you supplied in the FROM APP table is used to return results back to your application.

Security for your application to access SQL DB tables is controlled by your security team who manage the SQL Server where the database is located.

Your application will require Select and Delete permissions to the following table:

TABLE Tbl_SQL_API_TO_APP_Results

This table provides the results of outbound SMS submission requests only.

Recommendations:

1. Check this table for SMS results after a reasonable time frame eg: 1 min. Do not check too often unless your application has to obtain a rapid response. Rapid response would require you to check every 5 seconds.

When you have read the result record and update your application database then delete the result record from this table.

Primary key comprises Main_Sender_Email and Main_UniqueNo

Column	Format	Comments
Main_App_UserName	VARCHAR(100) NOT NULL	This is your SQL User Login name. It is used by Row Level Security controls in SQL Server
Main_Sender_Email (PK)	VARCHAR(100) NULL	Records listed in your select should all be your Business Application sender email address identifier.
Main_UniqueNo (PK)	INT	forms part of primary key
Main_Updated_UTCDateTime	DATETIME	UTC date and time based on the SMS Server local date time
the following fields are the remaining fields which are from the Main_Store Current DB. the application knows everything about the transaction from the current DB.
Main_EventId	INT	Event ID from the SMS platform current database.
Main_AppId	INT NOT NULL	value = 3 is SQL_API


Main_SQL_Record_Created	DATETIME	time originally created in the main store of the SMS platform
Main_Prod_Server	VARCHAR(30) NOT NULL	name of server which processed the SMS
Main_Process_State	INT NOT NULL	1=Request passed validation and has been inserted into the main current database for transmission 3 = Request failed duplicate check. 50 = Message queued to SMSC when in send window, message ID returned. 52 = undelivered by mobile networkSMSC. Refer to SMSC Network Errors below if supported by the SMS provider. 55 = Message delivered to mobile users' home network or to their handset depending on Network provider 95 = Message request failed. Check [Main_SMSC_Network_Error_ReturnCode] Check [Main_General_Error_Desc]
Main_Priority	CHAR(1) NOT NULL	N or H priority assigned by SMS Server based on the business application profile defined in the SMS Console.
Main_DoNotSendBefore	VARCHAR(4) NULL	HHMM if set is the time window nominated by the business app profile or by the submitting application
Main_DoNotSendAfter	VARCHAR(4) NULL	HHMM if set is the time window nominated by the business app profile or by the submitting application
Main_BroadcastReference	VARCHAR(20) NULL	not applicable
Main_Company	VARCHAR(30) NULL	name of brand as specified in the business app profile
Main_Dept	VARCHAR(30) NULL	Dept is the name of the Department or Cost centre nominated by the business app profile or by the submitting application
Main_AppCustom1	VARCHAR(100) NULL	custom fields are values for use by the application.
Main_AppCustom2	VARCHAR(100) NULL
Main_AppCustom3	VARCHAR(100) NULL
Main_AppCustom4	VARCHAR(100) NULL
Main_ AppCustom5	VARCHAR(100) NULL
Main_ AppCustom6	VARCHAR(100) NULL
Main_ AppCustom7	VARCHAR(100) NULL
Main_ AppCustom8	VARCHAR(100) NULL
Main_ AppCustom9	VARCHAR(100) NULL
Main_AppCustom10	VARCHAR(100) NULL
Main_App_Reference	VARCHAR(150) NULL	App Reference as defined by the submitting application
Main_EPM_X_Header	VARCHAR(20) NULL	Protective marking assigned by the application.
Main_Subject	VARCHAR(MAX) NULL	This is the complete SMS message.
Main_SMSC_Receiver_SMSNo	VARCHAR(20) NULL	For sending operations, this is the receiver SMS number supplied by the application.
Main_SMSC_Sender_SMSNo	VARCHAR(20) NULL	For sending operations, this is the sender SMS number value setup by the SMS administrator for use by this application.
Main_SMSC_Sender_FallBack_SMSNo	VARCHAR(20) NULL	used in high availability SMSC implementations
Main_SMSC_FallBack_RouteTaken	CHAR(1) NULL	used in high availability SMSC implementations
Main_PartA_Compiled_SMSMsg	VARCHAR(160) NULL	For sending operations, PART A is the first 160 characters of a multi-part SMS. Part B and C will contain additional message parts if the message is greater than 160.
Main_PartA_SMSC_ReturnCode	INT NULL	-1=initial state 0=queued 2=undeliverable 3=delivered
Main_PartA_SMSC_ReturnCode_Desc	VARCHAR(100) NULL	-1 null 0=queued 2=undeliverable 3=delivered
Main_PartA_SMSC_MessageId	VARCHAR(100) NULL	Message ID from the SMS Service provider for Part A.
Main_PartA_SMSC_Msg_Accepted	DATETIME NULL	time accepted from the SMS Service provider for Part A.
Main_PartA_SMSC_Delivery_Notification	DATETIME NULL	time that a DELIVERY Notification was advised from the SMS Service provider for Part A.
Main_PartB_Compiled_SMSMsg	VARCHAR(160) NULL	PART B for messages > 160
Main_PartB_SMSC_ReturnCode	INT NULL
Main_PartB_SMSC_ReturnCode_Desc	VARCHAR(100) NULL
Main_PartB_SMSC_MessageId	VARCHAR(100) NULL
Main_PartB_SMSC_Msg_Accepted	DATETIME NULL
Main_PartB_SMSC_Delivery_Notification	DATETIME NULL
Main_General_Error_Desc	VARCHAR(200) NULL	Refer to the list of general errors below.
Main_Received_Duplicate_Flag	CHAR(1) NULL
Main_Duplicate_Count	INT DEFAULT 0 NOT NULL
Main_Audit_Note	VARCHAR(500) DEFAULT ' ' NOT NULL
Main_Duplicates_Allowed_Counter	INT DEFAULT 0 NOT NULL
Main_SMS_Parts	INT NULL	-1 initial value the number of SMS message parts required to send the message.
Main_PartC_SMSC_ReturnCode	INT NULL	PART C is like Part A. Used for long SMS messages
Main_PartC_SMSC_ReturnCode_Desc	VARCHAR(100) NULL
Main_PartC_SMSC_MessageId	VARCHAR(100) NULL
Main_PartC_SMSC_Msg_Accepted	DATETIME NULL
Main_PartC_SMSC_Delivery_Notification	DATETIME NULL
Main_SMSC_Provider_Used	VARCHAR(20)	Which SMSC provider was used to send the message
Main_SMSC_Network_Error_ReturnCode	INT	Network error return code. refer to the TXT file below
Main_SMSC_Network_Error_Desc	VARCHAR(100)	Network Error Description
Main_SMSC_PrimaryOrBackup	CHAR(1)	Primary Server or Backup Server which processed the message

The SMS platform is usually configured to send back a series of results for each request.

Process states of results sent back to the application:

Developers should always look at the process state first.

1=Request passed validation and has been inserted into the main current database for transmission

50 = Message sent to SMSC with a message ID being returned to the SMS Server

52 = Undelivered by mobile networkSMSC. Refer to SMSC Network Errors below if supported by the SMS service provider.

A process state = 52 means that the SMS was unsuccessful.

If the SMS service provider returns a network error code it will be in the Main_SMSC_Network_Error_ReturnCodes. Refer to the Excel file below.

Process State 52 Main_General_Error_Desc and Main_SMSC_Network_Error_ReturnCodes

BNS Enterprise SMS Server developer error codes in Excel format below.

Note: Open XLS then select "download" from the bottom of the viewer screen.

55 = Message has been delivered to mobile users' home network or to their handset depending on SMS network provider

95 = Message request failed validation

A process state = 95 will have a reason in the Main_General_Error_Desc.

A process state = 95 will also have the return code 1000-1031 in the Main_SMSC_Network_Error_ReturnCode.

Process state 95 Main_SMSC_Network_Error_ReturnCodes & Main_General_Error_Desc

Main_SMSC_Network_Error_ReturnCode is in brackets

(1000) Network Error not available

(1001) Message Failed Email Protective Marking Tests

(1002) Invalid Recipient Mobile Number

(1003) No SMS Recipients found in message

(1004) Message failed Business Logic

(1005) Message length is determined as %1 which is greater than the configured maximum of %2 for this application

(1006) Message & Potential Disclaimer will not fit into two SMS Messages

(1007) Sender Domain Defaults restricts Users in this Domain %1 from Sending

(1008) Sender Domain Defaults restricts Contacts in this Domain %1 from Sending

(1009) Domain %1 not authorised to send SMS messages

(1010) Invalid Source Address. The Sender of the Message is invalid

(1011) Invalid Destination Address. The destination mobile number is invalid

(1012) SMS Message is empty

(1013) SMS Message failed to send because it was attempted in the last 24 hour window. No further duplicate warnings will be sent regarding this message

(1014) SMS Message contains un-supported characters : %1

(1015) An identitical message to the same recipient was queued in the last 24 hour window at %1.It is being processed by the gateway. No further duplicate warnings will be sent

(1016) The translated SMS Email Protective Marking Classification is greater than 30 characters. Please refer to Administrator to reconfigure

(1017) The translated SMS Email Protective Marking Classification is blank or not configured. Please refer to Administrator to reconfigure

(1018) Recipient SMS Number length is not within defined limits, Please refer to Administrator to reconfigure

(1019) SMPP Timeout to Carrier

(1020) The Main_App_Sender_Email value %1 is not defined in the Business Application Table.Please refer to Administrator to reconfigure

(1021) The Main_App_Receiver_Destination value %1 is not in the required format for this message type, expecting cell number

(1022) Unable to determine an SMS Server from the Main_App_Receiver_Destination value %1 to assign this message.Please check the value or contact Administrator

(1023) Requested High priority is not allowed because the application configured with a maximum priority of %1

(1024) Main_DoNotSendBefore: %1 and Main_DoNotSendAfter: %2 values in Business Application table or request need checking, minimum window is one hour

(1025) Main_App_Protective_Marking: %1 value has not passed EPM verification

(1026) Main_App_Message_Type: %1 is not a supported application

(1027) Internal System Error %1'; // 26/9/2022 Capture low level internal errors like TCP/IP errors from SMPP Component

(1028) Message exceeded its wait time of %1 minutes for a delivery receipt,deemed queued

(1029) Check the donotsendbefore and donotsendafter details in Business Application table or User Keyword pair minimum window is one hour

(1030) Unable to update the BusApp_App_UserName with value %1 for Business_App user %2, error received %3

(1031) BusApp_App_UserName %1 specified in this request is not assigned to Business_App user %2 so this request is rejected

How to access incoming SMS messages

Note: For incoming SMS applications which do not send SMS, your application must send at least 1 x SMS in order to register the Row Level Security SQL user login into the SMS platform's business application table.

The SMS platform writes incoming SMS messages which are results of the progress of your request to the following table. There may be one or more records written to this table which your application will read then delete.

Security for your application to access SQL DB tables is controlled by your security team who manage the SQL Server where the database is located.

Your application will require Read and Delete permissions to the following table:

TABLE Tbl_Sql_Api_Incoming_Msgs

This table provides incoming SMS messages to all applications.

Your application must select where:

Main_App_UserName = your SQL Login name

Microsoft Row Level Security (RLS) will only provide your application with a view of your RLS SQL UserName.

You can read next incoming SMS, process it then delete the incoming record from this table

Column	Format	Comments
Main_App_UserName	VARCHAR(100) NOT NULL	This is your SQL Login UserName
Main_Incoming_Business_App_Email_Address (PK)	VARCHAR(100) NULL	Records listed in your select should all be your Business Application sender email address identifier.
Main_UniqueNo (PK)	INT	forms part of primary key
Main_EventId	INT	Event ID from the SMS platform current database.
Main_SQL_Record_Created	DATETIME	time originally created in the main store of the SMS platform
Main_SMSC_Sender_SMSNo	VARCHAR(20) NULL	this is the sender SMS number value setup by the SMS administrator for use by this application.
Main_SMSC_Receiver_SMSNo	VARCHAR(20) NULL	this is the receiver SMS number supplied by the application.
Main_Received_Msg	VARCHAR(MAX) NULL	this is the sms message received
Main_Prod_Server	VARCHAR(30) NOT NULL	name of server which processed the SMS
Main_Company	VARCHAR(30) NULL	name of brand as specified in the business app profile
Main_Dept	VARCHAR(30) NULL	Dept is the name of the Department or Cost centre nominated by the business app profile or by the submitting application
Main_AppCustom1 thru Main_AppCustom10	VARCHAR(100)	Values that the SMS Server has been able to match to a previous transmission. eg: A customer has replied to SMS.
Main_SMSC_PrimaryOrBackup	CHAR(1) NULL	Primary Server or Backup Server which processed the message

Create your own Knowledge Base