How do I run a SQL Stored Procedure from my DS script?

Article:HOWTO5106  |  Created: 2006-09-20  |  Updated: 2011-03-16  |  Article URL
Article Type
How To


I have a problem with using Custom Data Sources in Deployment Solution. Currently I have setup an SQL Custom Data Source called: CPCDB.

When I run the following script code from within a DS script, only the SELECT statement below returns a result.

strTestSelect = "%#CPCDB*"SELECT (SUBSTRING(Code,1,4) + UPPER(SUBSTRING(Region,1,6)) + 'SC' + '001') FROM tblCPCDeploySiteInfo WHERE Subnet = ''"%"

StrTestSP = "%#CPCDB*"EXEC spgqsSubnet4"%"


From Query Analyzer, both of these SQL statements return the same identical result. I can confirm that the Stored Procedure is allowed (as set within the Custom Data Sources - Allowed Stored Procedures list).

The Stored Procedure that I am running is identical to the SQL statement above. I've inlcuded it below for reference:

SELECT (SUBSTRING(Code,1,4) + UPPER(SUBSTRING(Region,1,6)) + 'SC' + '001')
FROM tblCPCDeploySiteInfo WHERE Subnet = ''

I can't find any example of a Stored Procedure being called from within DS using Custom Tokens and Custom Data Sources. How do I this? 


Use CALL instead of EXEC in your script, as with the following:

StrTestSP = "%#CPCDB*"{CALL spgqsSubnet4}"%"

If your stored procedure requires parameters, these should be passed as a comma-separated list in parentheses after the name of the stored procedure.

For instance, if your stored procedure requires two input parameters - an IP Address as a string and an integer - the syntax for the command is as follows:  

StrTestSP = "%#CPCDB*"{CALL spgqsSubnet4 ('',4)}"%"

Legacy ID


Article URL

Terms of use for this information are found in Legal Notices