Did you know that you can use SQL Stored Procedures (SP’s) as a data source in a Scribe Insight integration? You might have done something similar when you use SP’s as the data source for custom reports.
Using SQL Server as a data source in Scribe lends all the power and functionality of SQL Server to your integration. I have certainly leaned heavily on SQL tables and views for Scribe integrations but only recently learned the power of SQL SP’s.
An SP in SQL is a collection of SQL statements that typically manipulate data. Using the SP as a data source in Scribe brings the power of SQL data manipulation statements to your integration. So you can move or update data as needed in the source SP to prepare it for integration.
Here’s some handy tips I learned:
Tip 1: Start each SP with a SET NOCOUNT ON statement. This will keep Scribe from getting confused about what data is returned from the SP.
The result of each SP should be a set of data that your Scribe integration will use as the source. Typically the SELECT command will be the last statement in your SP that will display the data for your integration.
Tip 2: While developing and testing your integration you can use a SELECT command at the top of the SP followed immediately by RETURN to avoid the data manipulation steps you may want to skip. The result set of this SELECT command should match the output expected by the integration.
If your SP returns an error or anything other than the data set (even an empty one) as its result you will have trouble opening the Scribe DTS.
Tip 3: If you receive many errors from your Scribe DTS that uses an SP as the source return to the SP and test it to make sure the output set is what is expected.
And finally it is very helpful to document the output set, typically that’s as easy as printing the DTS report.
Tip 4: Make sure login user for your integration has rights to execute your SP and the objects it needs to run.
Tip 5: When you create your Scribe DTS use a Custom Query as the data source and use EXEC <myproc> as the SQL Query where <myproc> is your SP name.
The result will look familiar but will now be a powerful type of data source you can use anywhere you need it.
If you need additional information or assistance, just send us an email at Support@Crestwood.com and we can help you out.
