Describes the csp_ExportData SQL Stored Procedure.
Concept
When troubleshooting an issue in a user's database, it is often necessary to transfer a backup of their database to the Cyrious office. Most of the time, the entire database is not necessary to troubleshoot a single order, estimate, or product.
The csp_ExportData stored procedure allows us to make a backup of a portion of the user's data.
Three SQL Reports have been added to Control to easily execute this stored procedure from inside of Control. They are:
- Customer Support - Order Export
- Customer Support - Estimate Export
- Customer Support - Product Export
Parameters
csp_ExportData has the following parameters:
Standard Parameters
| ~ Parameter Name | ~ Data Type | ~ Default Value | ~ Description | ||||
| @FilePath | VARCHAR(4000) | (REQUIRED) | The path to save the data backup. Note: This path is relative to the SQL Server database server, not necessarily the machine executing the query. |
||||
| @TransHeaderIDs | VARCHAR(MAX) | NULL | The ID(s) of the the orders and/or estimates to be exported. These IDs most be a string containing a comma-delimited list of IDs. If left NULL, no orders or estimates will be exported. |
||||
| @ProductIDs | VARCHAR(MAX) | NULL | The ID(s) of the the products to be exported. Any products that are part of any orders/estimates specified in @TransHeaderIDs will automatically be exported. These IDs most be a string containing a comma-delimited list of IDs. If left NULL, no additional products will be exported. |
Parameters for Testing and Debugging
The following are parameters that can usually be ignored, except when testing or troubleshooting the export stored procedure.
| ~Parameter Name | ~Data Type | ~Default Value | ~Description | ||||
| @PrintQueries | BIT | 0 (False) | When 1 (True), any query executed by the stored procedure is written to SQL Server Management Studio's client messages. | ||||
| @ExecQueries | BIT | 1 (True) | When 0 (False), The queries are not actually executed. this is useful when used with @PrintQueries to view the queries generated, without actually executing anything. | ||||
| @KeepTempDB | BIT | 0 (False) | When 1 (True), the temporary database will not be deleted at the end of the process. This saves you from having to restore the backup when testing. | ||||
| @TempDBName | VARCHAR(150) | NULL | Specifies the name of the temporary database. By default, a unique name is generated. If specified, that name will be used for the temporary database. Note: If the database name already exists, the procedure will fail. |
||||
| @TempDBPath | VARCHAR(4000) | NULL | Specifies the path to store the temporary database's .mdf and .ldf files. By default, it will use SQL Servers default location. | ||||
| @SkipBackup | BIT | 0 (False) | When 1 (True), a SQL backup file will not be created. This is useful when testing to avoid having to clean up unnecessary files. |
Examples
Basic Usage
Simple Order/Estimate export:
EXEC csp_ExportData
@FilePath 'D:\Data\Backup',
@TransHeaderIDs '10094'
Simple Product export: code_formatsql
EXEC csp_ExportData
''@FilePath 'D:\Data\Backup', ''
''@ProductIDs '10011' ''
Multiple Orders/Estimates export: code_formatsql
EXEC csp_ExportData
''''@FilePath 'D:\Data\Backup', ''''
''''@TransHeaderIDs '10094,10095,10096' ''''
Multiple Products export: code_formatsql
EXEC csp_ExportData
''''''@FilePath 'D:\Data\Backup', ''''''
''''''@ProductIDs '10011,10012,10013' ''''''
Multiple Orders/Estimates and Products export: code_formatsql
EXEC csp_ExportData
''''''''@FilePath 'D:\Data\Backup', ''''''''
''''''''@TransHeaderIDs '10094,10095,10096', ''''''''
''''''''@ProductIDs '10011,10012,10013' ''''''''
Usage when testing and debugging Export to specified database, but do not create a backup or delete the temporary database: code_formatsql
EXEC csp_ExportData
''''''''''@FilePath '', ''''''''''
''''''''''@TransHeaderIDs '10094,10095,10096', ''''''''''
''''''''''@TempDBName 'StoreData_Export', ''''''''''
''''''''''@KeepTempDB 1, ''''''''''
''''''''''@SkipBackup 1 ''''''''''
Output queries, but do not actually create an export: code_formatsql
EXEC csp_ExportData
''''''''''''@FilePath '', ''''''''''''
''''''''''''@TransHeaderIDs '10094,10095,10096', ''''''''''''
''''''''''''@PrintQueries 1, ''''''''''''
''''''''''''@ExecQueries 0 ''''''''''''
Source Contributor: Gregory Schroeder Date: 02/14/2017 Version: Control 6.0