VoiceWorx.ai
Search…
MS SQL Server
Managed Connection – Microsoft SQL Server

What is SQL Server?

SQL SERVER is a relational database management system (RDBMS) developed by Microsoft. SQL Server comes with different versions and editions like Enterprise, Standard, Express and Developer edition. All of these editions are supported by the Voiceworx platform.
The VoiceWorx platform allows for the rapid integration of SQL Server to various voice channels such as, Alexa, Lex, Google Assistant and more.
Prerequisites
  • SQL Server should support remote connection.
  • Voiceworx IP should be whitelisted so connection can be established from voiceworx platform to the Microsoft SQL Server RDS instance.

Enabling Remote connections to the SQL server:

Option 1: Using Transact-SQL
  1. 1.
    Connect to the Database Engine.
  2. 2.
    From the Standard bar, click New Query.
  3. 3.
    Copy and paste the following example into the query window and click Execute.
EXEC sp_configure 'remote access', 0 ;
GO
RECONFIGURE ;
GO
Option 2: Using SQL Server Management Studio
To configure the remote access option
  1. 1.
    In Object Explorer, right-click a server and select Properties.
  2. 2.
    Click the Connections node.
  3. 3.
    Under Remote server connections, select or clear the Allow remote connections to this server check box.
Below screenshot shows above settings explaining this.

Configure Microsoft SQL Server with VoiceWorx platform:

To Configure Microsoft SQL Server, browse to: Integration > Connect System > Connect External System in the VoiceWorx platform and select options as shown below.
On the configuration screen, provide the required information as shown below.
You should be able to get these configuration settings from the SQL Server connectivity and security settings. You may need to contact SQL Server administrator for this
Configuration
Info
Service Instance Name
You can give any name here. This name will appear as Instance Name in VoiceWorx platform.
Host
Hostname or IP Address of SQL Server. If you are not using default port, include that. E.g. sqlserverhost:portnumber
User Name
SQL Server Username
Password
Database Password for the username
Database Name
Name of the Database that you are going to use.
Once you have saved settings, browse to: Custom Apps > Manage Apps and create a new Custom Skill by clicking on “Create New Custom App” button. Fill out the details as shown below:
Now, create a Data Source for the Microsoft SQL Server by navigating to Data Source tab in the same screen. Make sure to select the integration config we set up in previous step as Integration Configuration.
Now you can create methods for the SQL Server Instance.
As of the publication of this document, the VoiceWorx platform supports following Microsoft SQL Server (MSSQL) methods,
  1. 1.
    Get Databases
  2. 2.
    Get Tables
  3. 3.
    Get All Items
  4. 4.
    Get Item
  5. 5.
    Get Items
  6. 6.
    Post Item
  7. 7.
    Put Item
  8. 8.
    Post Item without Primary Key
  9. 9.
    Delete Item
  10. 10.
    Run Select Query

Method: GetDatabases

Parameters: Not required
It returns a list of all the instances available.
Parameter
Info
Method Name
GetDatabases
Request Type
GetDatabases
This is the method that you are using
Parameters
As explained in table above
Result Type
List of Records
It indicates that the method can a list of records
Record Limit
10
Record JsonPath
Each method returns data in json format and to pick any specific information from the json, we need to specify the Record JsonPath so Voice Worx platform will pick that data in consideration while displaying results.
Example: $.[*]
This selects all the records that is returned.
Empty Result Template
Sorry, no database found in the given server. How else can I help you?
Note:
Anything written in between {{ }} is represented as a json field and it’s case-sensitive.
Result Template
Here are the databases found {{Records}}. How else can I help you?
{{Records}} represents that the record template is going to loop around all the items in the list.
Record Template
{{DatabaseName}}
Note:
Anything written in between {{ }} is represented as a json field and it’s case-sensitive and is derived from the result obtained from the method.
It represents the single record of the list.
Below is a sample response format – You should get response like this.
[
{"DatabaseName":"database1"},{"DatabaseName":"Instance2"},
]
You can test whether the method has been created correctly by clicking on Test on the action menu. The object screen shall display all the values that are being retrieved from the method while Result tab presents how the output will be presented to the user.

Method: GetTables

Parameters:
Parameter
Sample Value
Info
DatabaseName
Db1
Name of the database instance selected from the dropdown.
Method Parameters:
Parameter
Info
Method Name
GetTables
Request Type
GetTables
This is the method that you are using
Parameters
As explained in table above
Result Type
List of Records
It indicates that the method can a list of records
Record Limit
10
Record JsonPath
Each method returns data in json format and to pick any specific information from the json, we need to specify the Record JsonPath so Voice Worx platform will pick that data in consideration while displaying results.
Example: $.[*]
This selects all the records that is returned.
Empty Result Template
No tables created for {{Input,DatabaseName}}. How else can I help you?
Note:
Anything written in between {{ }} is represented as a json field and it’s case-sensitive. {{Input.DatabaseName}} represents that it is replaced by whatever value is provided by the user in input. For example if the input parameter name was poviderName, it shall have been used as {{Input.providerName}} etc.
Result Template
Here are the tables found {{Records}}. How else can I help you?
{{Records}} represents that the record template is going to loop around all the items in the list.
Record Template
{{TableName}}
Note:
Anything written in between {{ }} is represented as a json field and it’s case-sensitive and is derived from the result obtained from the method.
It represents the single record of the list.
Below is a sample response format – You should get response like this.
[
{
"TableName": "Product"
}
]

Method: GetAllItems

Parameters:
Parameter
Sample Value
Info
TableName
Product
SQL Server database table name.
Method Parameters:
Parameter
Info
Method Name
GetAllItems
Request Type
GetAllItems
This is the method that you are using
Parameters
As explained in table above
Result Type
List of Records
It indicates that the method can a list of records
Record Limit
10
Record JsonPath
Each method returns data in json format and to pick any specific information from the json, we need to specify the Record JsonPath so Voice Worx platform will pick that data in consideration while displaying results.
Example: $.[*]
This selects all the records that is returned.
Empty Result Template
No tables created for {{Input,TableName}}. How else can I help you?
Note:
Anything written in between {{ }} is represented as a json field and it’s case-sensitive. {{Input.TableName}} represents that it is replaced by whatever value is provided by the user in input. For example if the input parameter name was poviderName, it shall have been used as {{Input.providerName}} etc.
Result Template
Here are the tables found {{Records}}. How else can I help you?
{{Records}} represents that the record template is going to loop around all the items in the list.
Record Template
{{FirstName}}
Note:
Anything written in between {{ }} is represented as a json field and it’s case-sensitive and is derived from the result obtained from the method. It shall be the field name in the table.
It represents the single record of the list.
Sample Response:
[
{
"Productid": "1",
"Name": "Smart Watch",
"Price": "100",
},
{
"Productid": "2",
"Price": "50",
"Name": "Product A"
}
]

Method: GetItem

Parameters:
Parameter
Sample Value
Info
TableName
Product
SQL Server Database Table name.
PrimaryKeyName
Productid
Name of the primary Key for the provided table
PrimaryKeyValue
1
Value for the Partition Key
Method Parameters:
Parameter
Info
Method Name
GetItem
Request Type
GetItem
This is the method that you are using
Parameters
As explained in table above
Result Type
Record
It indicates that the method returns a single record.
Record JsonPath
Each method returns data in json format and to pick any specific information from the json, we need to specify the Record JsonPath so Voice Worx platform will pick that data in consideration while displaying results.
Example: $.[*]
This selects all the records that is returned.
Empty Result Template
No data found for {{Input,TableName}}. How else can I help you?
Note:
Anything written in between {{ }} is represented as a json field and it’s case-sensitive. {{Input.TableName}} represents that it is replaced by whatever value is provided by the user in input. For example if the input parameter name was poviderName, it shall have been used as {{Input.providerName}} etc.
Result Template
Here are the details found {{Name}}. How else can I help you?
{{Records}} represents that the record template is going to loop around all the items in the list.
Sample Response:
{
"Productid": "1",
"Name": "Smart Watch",
"Price": "100",
}

Method: GetItems

Parameters:
Parameter
Sample Value
Info
TableName
Product
SQL Server database table name.
QueryParameters
{"Price":"100"}
Key Value pair in a valid json format.
Method Parameters:
Parameter
Info
Method Name
GetItems
Request Type
GetItems
This is the method that you are using
Parameters
As explained in table above
Result Type
List of Records
It indicates that the method returns a list of records.
Record Limit
10
Maximum number of items returned from the method.
Record JsonPath
Each method returns data in json format and to pick any specific information from the json, we need to specify the Record JsonPath so Voice Worx platform will pick that data in consideration while displaying results.
Example: $.[*]
This selects all the records that is returned.
Empty Result Template
No data found for {{Input,TableName}}. How else can I help you?
Note:
Anything written in between {{ }} is represented as a json field and it’s case-sensitive. {{Input.TableName}} represents that it is replaced by whatever value is provided by the user in input. For example if the input parameter name was poviderName, it shall have been used as {{Input.providerName}} etc.
Result Template
Here are the details found {{Records}}. How else can I help you?
{{Records}} represents that the record template is going to loop around all the items in the list.
Record Template
{{Name}}
Note:
Anything written in between {{ }} is represented as a json field and it’s case-sensitive and is derived from the result obtained from the method. It shall be the field name in the table.
It represents the single record of the list.
Sample Response:
[
{
"Productid": "1",
"Name": "Smart Watch",
"Price": "100",
}
]
Here there is only one matching record, If there are multiple, you should get multiple items in the json array.

Method: PostItem

Parameters:
Parameter
Sample Value
Info
TableName
Product
SQL Server database table name.
PartitionKeyName
Productid
Name of the Partition Key
PartitionKeyValue
1
Value for the Partition Key
ItemData
{"Name": "Smart Watch","Price": 100}
Item data in json format
Sample Response:
{
"Productid": 1,
"Name": "Smart Watch",
"Price": 100
}

Method: PutItem

Parameters:
Parameter
Sample Value
Info
TableName
Product
SQL Server database table name.
PartitionKeyName
Productid
Name of the Partition Key
PartitionKeyValue
1
Value for the Partition Key
ItemData
{"Name": "Smart Watch 2","Price": 111}
Item data in json format For example
Sample Response:
This is same as Post Item. The only difference is, Put Item updates the record and Post Item inserts the record.
{
"Productid": 1,
"Name": "Smart Watch 2",
"Price": 111
}

Method: DeleteItem

Parameters:
Parameter
Sample Value
Info
TableName
Product
SQL Server database table name.
PartitionKeyName
Productid
Name of the Partition Key
PartitionKeyValue
1
Value for the Partition Key
Response:
{"response": "Delete Successful"}

Method: RunSelectQuery

Parameters:
Parameter
Sample Value
Info
SelectStatement
select ProductId,Name from Product
A valid SQL select statement.
Sample Response:
[
{
"ProductId": 1,
"Name": "Smart Watch"
},
{
"ProductId": 2,
"Name": "Product A"
}
]

Create Intents

Create Intents corresponding to each method.

GetTablesIntent

Details:
Name: GetTablesIntent
IntentType: Search
Intent Action: Execute Method
Primary Method Data Source: SQL DS
Data source Method: GetTables

Create Slots

Create intents corresponding to every method. For every parameter used in the method, you need to create a corresponding slot. Slot name shall match the parameter name.
Details:
Name: DatabaseName
Slot Order: 0
Reprompt Message: “what’s the database name”
Type: Text
Custom App: SQL Demo
Intent: GetTablesIntent

Create Utterances

To invoke the intent, you need to create utterances. Utterances are short text that act as trigger to invoke the intent.

Utterance for GetTables

Details:
Value: get all tables
Custom App: SQL Demo
Intent: GetTablesIntent

Create Inputs

Create Input corresponding to the Intent
Details:
Name: GetTablesInput
Custom App: SQL Demo
Custom Intent: GetTablesIntent
DataSource : SQL DS
Data Source Method: GetTables

Create Publish and Publish to Channel

On the Publish tab, click on Add App publishing and fill following details:
External App Name: SQL Demo
App Type: Survey
Channel: Alexa
Skill Invocation: database demo
Messages: Fill with your suitable prompts.
After creating the publish, you’ll be able to publish the app to the channel selected and test the app as well.
On the developer console, once the skill builds successfully, you can invoke the intents and test the app. Here is a sample one,
You can refer to this document for more descriptive step by step guide for app creation and publishing in VoiceWorx.
Copy link
On this page
What is SQL Server?
Enabling Remote connections to the SQL server:
Configure Microsoft SQL Server with VoiceWorx platform:
Method: GetDatabases
Method: GetTables
Method: GetAllItems
Method: GetItem
Method: GetItems
Method: PostItem
Method: PutItem
Method: DeleteItem
Method: RunSelectQuery
Create Intents
GetTablesIntent
Create Slots
Create Utterances
Utterance for GetTables
Create Inputs
Create Publish and Publish to Channel