This content has been marked as final. Show 11 replies
based on the way the stored procedure is designed it looks like I'll have to declare varaibles, execute SP and select variable results.
Does any one have an idea of how to translate this to CCA sql acceptable syntax?
DECLARE @WasSuccess bit, @Message nvarchar(200), @ConfirmationNumber int
exec spocName ?CustAccount, ?CreditCardNbr, ?ExoMonth,?ExpYear,?CSC,?etc,@WasSuccess = @WasSuccess OUTPUT, @Message = @Message OUTPUT, @ConfirmationNumber = @ConfirmationNumber OUTPUT Select @WasSuccess, @Message, @ConfirmationNumber
Ensure your DB guy has added 3 dummy, fake, input placeholders parameters to the Sproc. CCA requires that you send in the placeholders for output.
The Sproc should completely ignore and take no action on those 3 extra input parameters.
Then use this syntax exactly in CCA:
EXEC ProcessCreditCardPaymentV2EnteredBy ?AccNbr, ?CC, ?CCMM, ?CCYY, ?CSC, ?FstNm, ?LstNam, ?Amnt, ?Zip, ?Recur, ?Test, $WasSuccess, $Message, $ConfirmationNumber
As with any CCA to DB action, you should always build in a short delay e.g. play a 1 second silence WAV prompt (or better build a loop to check the SQLStatus variable) after sending it.
Then play back your variables which will be:
$WasSuccess, $Message and $ConfirmationNumber
That is correct.
Change the sproc.
For them to work with CCA, the Sproc needs to be looking for x number of dummy parameters.
Where x = the number of output parameters.
Have the Sproc completely ignore those extra input parameters...CCA sends them but you have to live with it or not use Sprocs with CCA.
Yes SQL2005, correct that is what you need to change.
Then it should work happy with CCA.
Hi again, would you by any chance have some information/documentation on how the spoc should be set up? My DBA guys have been assisting me on this and they created a procedure that requires me to declare the return variables in CCA not only as $Returned variable, but as DECLARE @ReturnedVar1, etc..
CCA doesn't like that and fails even when the code works just fine in MS SQL 2005 server. They've changed the spoc a few times now but seem to not be able to simply ignore $returnvars and only populate them on return of execution of spoc.
Thanks a lot!
Run this script in your DB:
/****** Put in your DB name here ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[usp_GetUser_Info_By_Username]
( @UserName varchar(50),
/****** FirstName , LastName and PhoneNumber are just place holder from CCA
CCA will ALWAYS pass in placeholder parameters for the return variables
the SPROC should just ignore these as this sample does. ******/
SET NOCOUNT ON;
DECLARE @UserID Varchar(25)
Select FirstName, LastName, Comm as PhoneNumber from users where username = @UserName and companyid = 1 and isdeleted = 0
Then in CCA Campaign call it like this:
EXEC usp_GetUser_Info_By_Username ?userName, $FirstName, $LastName, $PhoneNumber
--> NOTE in this sample the only thing the SPROC cares about is userName.
--> NOTE 2 be sure the username exists for company 1 or change the companyid
--> NOTE 3 be sure you have garneted execute rights to the SPROC for the ODBC DSN user you have defined.