Describes the csp_ExportData SQL Stored Procedure.

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
@FilePathVARCHAR(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.
@TransHeaderIDsVARCHAR(MAX)NULLThe 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.
@ProductIDsVARCHAR(MAX)NULLThe 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
@PrintQueriesBIT0 (False)When 1 (True), any query executed by the stored procedure is written to SQL Server Management Studio's client messages.
@ExecQueriesBIT1 (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.
@KeepTempDBBIT0 (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.
@TempDBNameVARCHAR(150)NULLSpecifies 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.
@TempDBPathVARCHAR(4000)NULLSpecifies the path to store the temporary database's .mdf and .ldf files. By default, it will use SQL Servers default location.
@SkipBackupBIT0 (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:

code_formatsql

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


You could leave a comment if you were logged in.