Import a Stored Procedure
Digitise Forms allows you to access existing databases using SQL Stored Procedures as an alternative to accessing the database directly. The Stored Procedure must be, or have been, written outside Form Studio, stored in a SQL Server database and then be imported into the Studio. If the Stored Procedure is later modified, you can reread the Stored Procedure and change the Datasource and Datasets accordingly.
This topic describes how to create a Datasource and Dataset(s) from an existing Stored Procedure; if you want to access an existing database directly refer to the topic Create an Imported Datasource instead or if you want to create a Digitise Forms Datasource see: Create a Digitise Forms Datasource.
Before you import a Stored Procedure, it will help to know some basic information about importing Stored Procedures with Form Studio:
When you import a Stored Procedure, Form Studio will create a special Datasource to handle data input and output via that Stored Procedure. As with other Datasources, this Datasource will need one or more Datasets which define the data items relevant to the Stored Procedure. Each Stored Procedure you import will have a separate Datasource with its own Dataset. The Datasource will be named:
SP_<stored procedure schema>_<stored procedure name>
e.g. the Stored Procedure dbo.ReportsByDate would have a Datasource called SP_dbo_ReportsByDate.
Stored Procedures can be used to add new records to a database or update existing records. They can also send data back to your form, in a so called Result Set. An individual Stored Procedure may only insert or update records, only return one or more Result Sets or do a combination of both.
In the case of inserting and updating records, the Stored Procedure will include one or more SQL INSERT or UPDATE statements and will generally require the values to be inserted/updated in the database to be passed into it. These values are passed in using parameters, with each item of data to be passed in requiring a separate parameter. The required parameters are defined within the Stored Procedure, with each definition specifying a name for the parameter, the type of data it can hold, e.g. a number, date or text string, and optionally a maximum length for the data. Parameter names always start with '@', such as @Start_Date, or @Name.
Where a Stored Procedure returns one or more Result Sets, it will contain one or more SQL SELECT statements. In order to select the records to be returned, the Stored Procedure may require one or more values to be passed into it which specify the records to be returned. For example, a Result Set could return a set of records relating to the user of the form, such as a list of their appointments or purchases, and so would require the appropriate information to identify the current user. Once again, this information is passed into the Stored Procedure, in the same way as values for inserting/updating, via parameters.
If a Stored Procedure only performs inserts and/or updates, you will need a Dataset, called Parameters, whose data fields match the Stored Procedure's parameters. This Dataset must contain a data field for each of the parameters and the data field definitions must match those of the parameter definitions in the Stored Procedure. You can't have only a sub-set of the required parameters.
If a Stored Procedure returns one or more Result Sets, however, you can choose which Result Sets you want to use within your form and which columns from each Result Set you want to be downloaded to your form. For each Result Set you want to use, you will need a corresponding Dataset with data fields matching columns returned in the Result Set. The Dataset data fields must have the same names as their corresponding Result Set columns and have matching definitions in terms of data type and, for text string fields, max length. In addition, you will also need a SQL query which will be used to execute the Stored Procedure. If the Stored Procedure also requires parameters to be passed in, whether to perform inserts and updates, as search criteria or for any other reason, a Dataset IS NOT created for these parameters but they are passed in via the SQL query. These Datasets will be named after the database tables from which the data is extracted or the Stored Procedure, if an empty Dataset is created. If this results in Datasets having the same names, a consecutive sequence of numbers is appended to the end of the second and subsequent Datasets, e.g. MyDataset2.
When you import a Stored Procedure, Form Studio uses a Microsoft function to attempt to read and interpret the Stored Procedure to discover which parameters, if any, the Stored Procedure needs and whether it returns any Result Sets, and if so the format of those Result Sets. It will then present the results to you for you to check and, if all is well, it will create an appropriate Datasource, Dataset and SQL query for you.
Note, however, that Stored Procedures can be complex and the function may not always be able to correctly or fully interpret the Stored Procedure. Input parameters should be fine, but Result Sets may cause difficulties. For this reason you will need to know what your Stored Procedure should return, if anything, so that you can check what Form Studio thinks will be returned against what you expect. If the two don't match, you will have various choices open to you to allow you to create appropriate Datasets, including manually creating Datasets - these options will be discussed further below. If you are familiar with, and have access to, SQL Server Management Studio (SSMS), you may be able to check the Result Sets returned by a Stored Procedure by executing it in SSMS.
Note, also, that if a Stored Procedure returns more than one Result Sets, Form Studio will only be told about the first one found when the Stored Procedure is analysed. If you want to use this Stored Procedure but none of the others, you won't need to do anything extra. However, if you don't want to use this Result Set in your form or you want to use one or more other Result Sets as well, you will have various choices about how to create Datasets for the other Result Sets, including manually creating them - these options will be discussed later. Where you want to work with multiple Result Sets from a Stored Procedure, you will need to create a separate Dataset for each Result Set within the Stored Procedure's Datasource.
Finally, the function which interprets the Stored Procedure's functionality, may experience problems and return an error to Form Studio. In this situation, Form Studio will display the error and, if a Result Set can't be determined, present you with a choice of options, similar to those available when Result Sets can't be correctly interpreted, to allow you to create the required Dataset.
It will help to import a Stored Procedure if you are familiar with SSMS and have permissions to view, edit and execute the Stored Procedure within it. If you aren't able to do this, you will need to get a description of any Result Sets you want to use from your SQL administration team.
For information about using the Stored Procedure Datasource and Datasets within your form, see Use an Imported Stored Procedure.
To import a Stored Procedure:
- Click on the lower half of the New Datasource button on the Ribbon's Data tab and then choose Import Stored Procedure,
- Right-click on the background of the Project Explorer's Datasources tab and choose Import Stored Procedure
or
- In the Project Explorer's Project tab, right-click on the Datasources node and choose Import Stored Procedure.
The SQL Database Connection dialog will appear, allowing you to specify the connection details for the remote database housing the Stored Procedure you want to import. Click on Save to continue or Cancel to abandon the import.
If you clicked Save, Form Studio will then connect to your chosen database and present a list of the Stored Procedures available:
Select the Stored Procedure you want to import and click the OK button. Alternatively, to close the dialog without importing a Stored Procedure, click Cancel.
If you choose the OK button, Form Studio will query the content of the chosen Stored Procedure and display the results in the Import Stored Procedure dialog box. This dialog box will vary, depending upon the type of Stored Procedure. The image thumbnails below show the dialog box reporting different results, click on an image to display it larger or on the links below each thumbnail to open the image in a separate window. If an error message is displayed, click Continue to display the Stored Procedure Import dialog or Close to abandon the import.
In this situation, the Import Stored Procedure dialog box will display a list of the parameters required by the Stored Procedure:
If the Stored Procedure doesn't return any Result Sets but does require the listed parameters, select Correct - this Stored Procedure does not return a Result Set. Create a new Dataset for the parameters. and then click OK. Form Studio will automatically create a Datasource, named after the Stored Procedure, and add a Dataset, called Parameters, including data fields for each of the parameters listed. Note, that if there is already a Dataset defined with this name, even if it is in another Datasource, a consecutive sequence of numbers will be appended to the end of the second and subsequent Dataset names, e.g. Parameters2.
Click Cancel instead if you want to close the dialog without creating the Datasource and Dataset.
If the Stored Procedure does in fact return one or more Result Sets, see later sections in this topic.
In this situation, the Import Stored Procedure dialog box will display nothing under the Parameters section at the top of the dialog, and below this it will display the data columns returned by the first Result Set found in the Stored Procedure:
If the format of the Result Set is correct and you want to use this Result Set in your form, make sure the first option, The Result Set broadly matches what I expect from the Stored Procedure and want to use. Create a new Dataset for it., is selected and then click OK.
Form Studio will automatically create a Datasource and add a Dataset with data fields matching the columns displayed for the Result Set in the Import Stored Procedure dialog box. The Dataset will be named the same as the Source Table from the Result Set results listed in the Import Stored Procedure dialog. If there is already a Dataset defined with this name, even if it is in another Datasource, a consecutive sequence of numbers will be appended to the end of the second and subsequent Dataset names, e.g. MyDataset2.
A SQL query will also be generated for the Dataset, which can be used to execute the Stored Procedure - see Use an Imported Stored Procedure. You cannot edit this query nor can you create additional queries.
- It is possible that sometimes the same column may appear in the Result Set multiple times. When this happens, the Dataset should only include the column once.
If the Result Set is only partly correct but you want to use this Result Set in your form, you can either create a Dataset using the columns listed here and then edit the Dataset manually to make it what you need or choose one of the other options, described below, instead. The Import Stored Procedure dialog box includes guidance notes to help you choose the best option for your needs.
To use the listed columns as the basis for your Dataset, choose The Result Set broadly matches what I expect from the Stored Procedure and want to use. Create a new Dataset for it. and then click OK. Form Studio will create a Datasource and Dataset as described above for a correct Result Set. You can then edit the Dataset as required.
If the Result Set is not sufficiently correct to want to use it as the basis for the Dataset or it is not what you expected or, irrespective of whether it is correct or not, you don't want to use this Result Set in your form anyway, you have a number of other choices:
The Result Set does not match what I expect or want from the Stored Procedure. Create a datasource but no dataset.
Selecting this option and then clicking OK will create a Datasource but won't create a Dataset within it.
This option is intended to be used where you want to use one or more Result Sets from this Stored Procedure but not the one currently presented and you are able to edit the Stored Procedure in, for example, SQL Server Management Studio.
After the Datasource has been created, you can modify the Stored Procedure to temporarily comment out all SELECT statements except one that produces a Result Set you do want to use, or which are before it in the Stored Procedure. Save your changes to the Stored Procedure. Then, back in Form Studio, click on the Datasources tab in the Project Explorer and right-click within the tab's background. Choose Import Stored Procedure. The Import Stored Procedure dialog box will be displayed again, as above, but this time the Result Set presented should be the first one you didn't comment out. Your options here are exactly the same as for the first Result Set, so if the Result Set is interpreted correctly and you want to create a Dataset for it, select The Result Set broadly matches what I expect from the Stored Procedure and want to use. Create a new Dataset for it. and click OK, otherwise choose one of the other options discussed in this section. If you choose to create the Dataset, the Dataset will be named the same as the Source Table from the Result Set results listed in the Import Stored Procedure dialog. If there is already a Dataset defined with this name, even if it is in another Datasource, a consecutive sequence of numbers will be appended to the end of the second and subsequent Dataset names, e.g. MyDataset2.
A SQL query will also be generated for the Dataset, which can be used to execute the Stored Procedure - see Use an Imported Stored Procedure. You cannot edit this query nor can you create additional queries.
Once you have a Dataset, you can manually edit that Dataset if required, to remove unwanted columns or add missing columns so that it matches the Result Set that will actually be returned.
You can repeat this process for each of the Result Sets available in the Stored Procedure that you want to make available to your form.
Once you have created Datasets for all the Result Sets you want to use, remember to restore your Stored Procedure back to its original state.
- If you don't want to use the first Result Set presented and are able to edit the Stored Procedure as described above, you must use this option before you edit the Stored Procedure. Don't cancel the Import Stored Procedure dialog box and then edit the Stored Procedure and re-import it from scratch, unless you intend to permanently change the Stored Procedure. This is because this option will keep a record of the first Result Set in the Stored Procedure so that when you publish your form and the publish process checks the, now restored, Stored Procedure it will know about the first Result Set and won't think the Stored Procedure has changed since it was imported.
Create a dataset for me to complete from table:
This option is intended to be used where you want to use one or more Result Sets from this Stored Procedure but the currently presented Result Set columns don't match any of the expected Result Sets sufficiently, or at all, to create a Dataset using those columns and you are not able, or don't want, to edit the Stored Procedure. If a Result Set you want to use is similar to, or based on, the columns in an existing database table, you can create a Dataset matching that table and then amend it manually to match the actual Result Set.
To do this, select this option and then choose the table you want to base your Dataset on from the drop-down list to the right. The drop-down list contains all the tables in the same database as the Stored Procedure. Click OK. Form Studio will create a Datasource for this Stored Procedure and then add a Dataset based on the table you selected. The Dataset will be named after the database table. If there is already a Dataset defined with this name, even if it is in another Datasource, a consecutive sequence of numbers will be appended to the end of the second and subsequent Dataset names, e.g. MyDataset2.
A SQL query will also be generated for the Dataset, which can be used to execute the Stored Procedure - see Use an Imported Stored Procedure. You cannot edit this query nor can you create additional queries.
Once you have a Dataset, you can then edit that Dataset as required to remove unwanted columns or add missing columns so that it matches the Result Set that will actually be returned.
Create an empty dataset for me to complete.
This option is intended to be used where you want to use one or more Result Sets from this Stored Procedure but the currently presented Result Set columns don't match any of the expected Result Sets sufficiently, or at all, to create a Dataset using those columns and you are not able, or don't want, to edit the Stored Procedure. This option allows you to create an empty Dataset and then amend it manually to match the actual Result Set.
To do this, select this option and click OK. Form Studio will create a Datasource for this Stored Procedure and then add an empty Dataset to it. The Dataset will be given the same name as the Stored Procedure. If there is already a Dataset defined with this name, even if it is in another Datasource, a consecutive sequence of numbers will be appended to the end of the second and subsequent Dataset names, e.g. MyDataset2.
A SQL query will also be generated for the Dataset, which can be used to execute the Stored Procedure - see Use an Imported Stored Procedure. You cannot edit this query nor can you create additional queries.
Once you have a Dataset, you can then edit that Dataset as required to add data fields so that it matches the Result Set that will actually be returned.
Cancel importing the Stored Procedure
Click Cancel to abandon importing this Stored Procedure. The Import Stored Procedure dialog box will close and the Datasource and Dataset won't be created.
In this situation, the Import Stored Procedure dialog box will display a list of the parameters required by the Stored Procedure under the Parameters section at the top of the dialog, and below this it will display the data columns returned by the first Result Set found in the Stored Procedure:
Where the Stored Procedure both returns one or more Result Sets and includes insert/update statements, you only need to create Datasets for any of the Result Sets that you want to use in your form. Each Dataset will include a SQL query, which can be used to execute the Stored Procedure and this query will include any parameters listed in the Parameters list at the top of the dialog box. The query parameters will need to be linked to Elements on the form or passed as parameters to the function which will execute the Stored Procedure in custom JavaScript - for more details, see Use an Imported Stored Procedure.
The options available to you in the Import Stored Procedure dialog are the same as described under Stored Procedure contains one or more Result Sets but no insert/update statements above and work in the same way here.
If the format of the Result Set is correct and you want to use this Result Set in your form, select The Result Set broadly matches what I expect from the Stored Procedure and want to use. Create a new Dataset for it. and then click OK. Form Studio will automatically create a Datasource and add a Dataset with data fields matching the columns displayed for the Result Set and generate a SQL query including any parameters required by the Stored Procedure.
If the Result Set is only partly correct but you want to use the first Result Set in your form, you can either create a Dataset using the columns listed here and then edit the Dataset manually to make it what you need or choose one of the other options available to create a Dataset.
If the Result Set is not sufficiently correct to want to use it as the basis for the Dataset or it is not what you expected or, irrespective of whether it is correct or not, you don't want to use this Result Set in your form anyway, choose one of the other options available to create a Dataset.
Refer to Stored Procedure contains one or more Result Sets but no insert/update statements above for more details about all the available options.
The Import Stored Procedure dialog box includes guidance notes to help you choose the best option for your needs.
In some cases, Form Studio may be unable to correctly identify any result sets and might be unable to decide if there actually are any. In this situation you may get an error message displayed when Form Studio attempts to import the Stored Procedure. If an error message appears, click Continue to continue with the import or Close to cancel the import.
If you click Continue, or no error message displays, and there are no parameters required by the Stored Procedure, the Import Stored Procedure dialog box will appear, looking something like this:
In this situation, the Parameters section is empty, since there are no parameters, and the Result Sets section is also empty because any Result Sets couldn't be interpreted correctly. If the Stored Procedure does actually return one or more Result Sets, you can still create a Dataset for the first result set that will be returned by selecting one of the available options. If you don't want the first Result set that will be returned or you want to use more than one Result Set in your form, refer to the section Import multiple Result Sets below.
The Import Stored Procedure dialog box includes guidance notes to help you choose the best option for your needs.
Create a dataset for me to complete from table:
This option is intended to be used where you want to use the first Result Set which is being returned and that Result Set is similar to, or based on, the columns in an existing database table. This option allows you to create a Dataset matching that table and then amend it manually to match the actual Result Set.
To do this, select this option and then choose the table you want to base your Dataset on from the drop-down list beside the option. Click OK. Form Studio will create a Datasource for this Stored Procedure and then add a Dataset based on the table you selected. The Dataset will be named after the database table. If there is already a Dataset defined with this name, even if it is in another Datasource, a consecutive sequence of numbers will be appended to the end of the second and subsequent Dataset names, e.g. MyDataset2.
A SQL query will also be generated for the Dataset, which can be used to execute the Stored Procedure - see Use an Imported Stored Procedure. You cannot edit this query nor can you create additional queries.
Once you have a Dataset, you can then edit that Dataset as required to remove unwanted columns or add missing columns so that it matches the Result Set that will actually be returned.
Create an empty dataset for me to complete.
This option is intended to be used where you want to use the first Result Set which is being returned but that Result Set doesn't really match any specific database table's columns sufficiently to warrant using an existing database table as the basis for the Dataset for this Result Set. This option allows you to create an empty Dataset and then amend it manually to match the actual Result Set.
To do this, select this option and click OK. Form Studio will create a Datasource for this Stored Procedure and then add an empty Dataset to it. The Dataset will be given the same name as the Stored Procedure. If there is already a Dataset defined with this name, even if it is in another Datasource, a consecutive sequence of numbers will be appended to the end of the second and subsequent Dataset names, e.g. MyDataset2.
A SQL query will also be generated for the Dataset, which can be used to execute the Stored Procedure - see Use an Imported Stored Procedure. You cannot edit this query nor can you create additional queries.
Once you have a Dataset, you can then edit that Dataset as required to add data fields so that it matches the Result Set that will actually be returned.
You could also use this option where a Result Set is expected but because the initial SQL in the Stored Procedure can't be interpreted correctly you get the dialog box shown above and there is no option to create a Dataset based on the actual Result Set. In this situation, you could use this option to create an empty Dataset when importing the Stored Procedure for the first time. Then you would need to comment out the initial SQL which is causing the problem and re-import the Stored Procedure so that this time a valid, understandable Result Set is the first Result Set received. You would then create a Dataset for this Result Set and repeat if necessary. When you have imported all the Result Sets, you would then need to delete the original Empty Dataset created and restore the Stored Procedure to its original state. This will ensure that Form Studio is aware of the original error and will allow you to publish your project without warning you about the problem again or preventing the publish altogether.
In some cases, Form Studio may be unable to correctly identify any result sets and might be unable to decide if there actually are any. In this situation you may get an error message displayed when Form Studio attempts to import the Stored Procedure. If an error message appears, click Continue to continue with the import or Close to cancel the import.
If you click Continue, or no error message displays, and there are parameters required by the Stored Procedure, the Import Stored Procedure dialog box will appear, looking something like this:
In this situation, the Parameters section displays the parameters found but the Result Sets section is hidden because any Result Sets couldn't be interpreted correctly.
If you don't want to import this Stored Procedure after all, click Cancel to close the dialog box without importing the Stored Procedure.
If the Stored Procedure doesn't return any Result Sets and you want to create a Dataset for the parameters, select Correct - this Stored Procedure does not return a Result Set. Create a new Dataset for the parameters. and then click OK. Form Studio will create a Datasource and Dataset, called Parameters, to match the parameters listed. A SQL Query will also be created for this Stored Procedure.
On the other hand, if the Stored Procedure does actually return one or more Result Sets, you can create a Dataset for the first result set that will be returned by selecting No - this Stored Procedure does return a Result Set. Show me the options.
If you don't want the first Result set that will be returned or you want to use more than one Result Set in your form, refer to the section Import multiple Result Sets below.
If you select No - this Stored Procedure does return a Result Set. Show me the options., the following options will be displayed:
The Import Stored Procedure dialog box includes guidance notes to help you choose the best option for your needs.
Create a dataset for me to complete from table:
This option is intended to be used where you want to use the first Result Set which is being returned and that Result Set is similar to, or based on, the columns in an existing database table. This option allows you to create a Dataset matching that table and then amend it manually to match the actual Result Set.
To do this, select this option and then choose the table you want to base your Dataset on from the drop-down list beside the option. Click OK. Form Studio will create a Datasource for this Stored Procedure and then add a Dataset based on the table you selected. The Dataset will be named after the database table. If there is already a Dataset defined with this name, even if it is in another Datasource, a consecutive sequence of numbers will be appended to the end of the second and subsequent Dataset names, e.g. MyDataset2.
A SQL query will also be generated for the Dataset, which can be used to execute the Stored Procedure - see Use an Imported Stored Procedure. You cannot edit this query nor can you create additional queries.
Once you have a Dataset, you can then edit that Dataset as required to remove unwanted columns or add missing columns so that it matches the Result Set that will actually be returned.
Create an empty dataset for me to complete.
This option is intended to be used where you want to use the first Result Set which is being returned but that Result Set doesn't really match any specific database table's columns sufficiently to warrant using an existing database table as the basis for the Dataset for this Result Set. This option allows you to create an empty Dataset and then amend it manually to match the actual Result Set.
To do this, select this option and click OK. Form Studio will create a Datasource for this Stored Procedure and then add an empty Dataset to it. The Dataset will be given the same name as the Stored Procedure. If there is already a Dataset defined with this name, even if it is in another Datasource, a consecutive sequence of numbers will be appended to the end of the second and subsequent Dataset names, e.g. MyDataset2.
A SQL query will also be generated for the Dataset, which can be used to execute the Stored Procedure - see Use an Imported Stored Procedure. You cannot edit this query nor can you create additional queries.
Once you have a Dataset, you can then edit that Dataset as required to add data fields so that it matches the Result Set that will actually be returned.
You could also use this option where a Result Set is expected but the initial SQL in the Stored Procedure can't be interpreted correctly. In this situation, you could use this option to create an empty Dataset when importing the Stored Procedure for the first time. Then you would need to comment out the initial SQL which is causing the problem and re-import the Stored Procedure so that this time a valid, understandable Result Set is the first Result Set received. You would then create a Dataset for this Result Set and repeat if necessary. When you have imported all the Result Sets, you would then need to delete the original Empty Dataset created and restore the Stored Procedure to its original state. This will ensure that Form Studio is aware of the original error and will allow you to publish your project without warning you about the problem again or preventing the publish altogether.
If the Stored Procedure you are importing returns multiple Result Sets, you may want to use more than one of them within your form.
Each Result Set you want to use will have to be imported separately and must have its own corresponding Dataset within the Datasource for the Stored Procedure.
You can run Import Stored Procedure as many times as you need, following the instructions under Stored Procedure contains one or more Result Sets but no insert/update statements and Stored Procedure contains one or more Result Sets and insert/update statements above, to import all the Result Sets you want from a Stored Procedure.
Once you have created a Dataset for a Result Set, you can subsequently edit the Dataset if necessary to add missing columns or remove unwanted columns so that it matches the Result Set that will actually be returned. You can remove any columns that you don't want or need to download for your form. Likewise, if you have created a new empty Dataset or the Result Set has columns which don't appear in the Dataset, you only need to add a data field to the Dataset for those columns in the Result Set that you want to use in your form. You don't have to allow for all columns in the Result Set within your Dataset, if you don't want or need them. Note, however, that you must not add any data fields to a Dataset for which there are no corresponding columns in the Result Set. Likewise, if the Dataset contains columns which are not in the actual Result Set, you will need to delete these columns from the Dataset.
To add a column to the Dataset, click on the Add Column button; to remove a column, deselect the Mapped checkbox for that column in the Dataset grid under the Mappings tab.
Remember that the names and data types of the data fields in the Dataset must match those of the corresponding columns in the Result Set.
If the Result Set column names are different to those found in the actual Database table, e.g. where the SELECT statement reads something like SELECT [Reporter_Surname] AS Surname, ..., use the Result Set column names when adding data fields to a Dataset. If Form Studio creates a Dataset for you in this situation, the FX Name column in the Dataset will contain the Result Set column name and the SQL Name column will contain the SQL database table column name. The Code Name column shows the column name converted to be compatible for use when referencing the column within custom JavaScript. This name will be generated automatically by Form Studio, can't be edited, is case-sensitive and shows the column name exactly as it must be used in custom JavaScript. Note that when adding a new column to a Dataset, the Code Name will only be displayed after you close the Dataset and re-open it.
If you are editing or adding Dataset data fields based on the columns in a SQL database table, you will notice that the data types available in Form Studio don't exactly match the data types used in SQL Server. This is because Digitise Forms uses C#/.NET data types rather than SQL Server data types. If you're not sure what data type you need to select, you can find conversion tables online, such as the following Microsoft web page:
For text string fields, the max length property of the field should also match between the Result Set and the Dataset field. Note, however, that because of the way the length values are calculated, when displayed in the Result Sets section of the Import Stored Procedure dialog, the Length property for columns which have a SQL Server data type of nvarchar or nchar is twice the actual Max Length value required in a Digitise Forms Dataset.
If you are not sure, what columns the Result Set will return, try running the Stored Procedure in SSMS if you can, otherwise you will need to speak to your SQL administration team. Note that if you want to remove a data field designated as a Primary Key field, you will need to deselect the Primary Key checkbox for that data field before you can deselect its Mapped checkbox.
Parameter Datasets should not be edited - you must include a data item in the Dataset for each of the parameters required by the Stored Procedure.
- If you want to be able to execute a Stored Procedure from your form but you are not interested in using any of the Result Sets it contains, you will still need to create a Datasource and Dataset for it, in order to generate a SQL query which can be used to execute the Stored Procedure.
- Whilst, you may be able to rename the Datasources created when you import a Stored Procedure, we recommend that you leave the default names as they are. You can, however, change the names of the Datasets to something more meaningful if you want to.
See also: