Question
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 = '192.168.33.0'"%"
StrTestSP = "%#CPCDB*"EXEC spgqsSubnet4"%"
MsgBox(strTestSelect)
MsgBox(strTestSP)
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:
CREATE PROCEDURE spgqsSubnet4
AS
SELECT (SUBSTRING(Code,1,4) + UPPER(SUBSTRING(Region,1,6)) + 'SC' + '001')
FROM tblCPCDeploySiteInfo WHERE Subnet = '192.168.33.0'
GO
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?
Answer
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 ('192.168.0.10',4)}"%"