« Back to CVP - All Versions

Re: New Message from Hemal Mehta in Customer Voice Portal (CVP) - CVP - All

Combination View Flat View Tree View
Threads [ Previous | Next ]
CVP MSSQL migration- The statement did not return a result set.
Answer
12/17/12 10:05 PM
Hi, currently i have an issue running stored procedures from MSSQL  Server, i’m trying to migrate the standalone outbound script to use MSSQL, but return the following error - The statement did not return a result set. On the MSSQL Management Studio seems to run fine this stored procedure.
I attach the code and logs, i'll appreciate your help.
 
Regards,
Claudio.
 
 
 
USE
GO
/****** Object:  StoredProcedure .    Script Date: 12/16/2012 18:31:40 ******/
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
--EXEC 3
ALTERPROCEDURE .
      @max_attempts
--WITH EXECUTE AS CALLER
AS
BEGIN
     
      declare @dnis_value     varchar(20)
      SET @dnis_value ='xxxx'
    declare @id_value   integer
    SET @id_value = 0
    declare @rna_value integer    
    SET @rna_value = 0
    declare @cli_value varchar(20)
    SET @cli_value = ''
    declare @uui_value varchar(256)
    SET @uui_value = ''
 
    --start transaction;
    BEGIN transaction
    select TOP 1 @dnis_value=dnis,
    @id_value =id,
    @rna_value = rna,
    @cli_value = cli,
    @uui_value = uui
    --into @dnis_value, @id_value, @rna_value, @cli_value, @uui_value
      from outdials
      where
            (current_status='OUTDIAL_PENDING')AND
             (attempts < @max_attempts) AND
             (next_retry < getdate())
      order by next_retry ASC
     
    if @dnis_value !='xxxx'
    BEGIN
         update outdials set current_status='OUTDIAL_IN_PROGRESS', attempts=attempts + 1
         where id = @id_value
     
    END
  
    COMMIT
 
    select
      @dnis_value as dnis,
      @id_value   as id,
      @rna_value  as rna,
      @cli_value  as cli,
      @uui_value  as uui
                                          
END
 
 
11.11.11.11.1355777435406.5049.outbound,12/17/2012 14:50:35.640, The error was: A built-in element encountered an exception of type com.audium.server.AudiumException. The statement did not return a result set. The root cause was: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
com.audium.server.AudiumException: A built-in element encountered an exception of type com.audium.server.AudiumException.
at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:413)
at com.audium.server.controller.Controller.goToAction(Controller.java:2911)
at com.audium.server.controller.Controller.goToElement(Controller.java:2643)
at com.audium.server.controller.Controller.continueCall(Controller.java:2468)
at com.audium.server.controller.Controller.doPost(Controller.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:879)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:662)
Caused by: com.audium.server.AudiumException: The statement did not return a result set.
at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:239)
at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:387)
... 20 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:408)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:199)
 
<!--EndFragment-->
Flag

RE: New Message from CLAUDIO RIVAS in Customer Voice Portal (CVP) - CVP - A
Answer
12/17/12 10:23 PM as a reply to CLAUDIO RIVAS.
I had the same issue; I do not know what Cisco says about it, but I could not get Call Studio to return a result from a stored procedure either. The stored procedure is run; I had no problem using it to update a row, the issue was then trying to return something.

I ended up just restructuring the way I wanted to do things, and only used stored procedures for inserts.
The other option would be to write your own DB element in Java. I plan on doing that to allow for stored procedures at some point, but haven’t yet as I didn’t have a big enough need for it.
Attachments:
Flag

RE: New Message from CLAUDIO RIVAS in Customer Voice Portal (CVP) - CVP - A
Answer
12/17/12 10:23 PM as a reply to CLAUDIO RIVAS.
I had the same issue; I do not know what Cisco says about it, but I could not get Call Studio to return a result from a stored procedure either. The stored procedure is run; I had no problem using it to update a row, the issue was then trying to return something.

I ended up just restructuring the way I wanted to do things, and only used stored procedures for inserts.
The other option would be to write your own DB element in Java. I plan on doing that to allow for stored procedures at some point, but haven’t yet as I didn’t have a big enough need for it.
Attachments:
Flag

RE: New Message from Asher Schweigart in Customer Voice Portal (CVP) - CVP
Answer
12/17/12 10:33 PM as a reply to Asher Schweigart.
I only replied once, my sent folder confirms that. Is something broken, or was that a glitch?
Flag

RE: CVP MSSQL migration- The statement did not return a result set.
Answer
12/17/12 10:33 PM as a reply to Asher Schweigart.
I have the exact same issue, the update part of the procedure seems to run because modify the value of the corrects collum and row. but seems to be something about the MSSQL driver.
Flag

RE: New Message from CLAUDIO RIVAS in Customer Voice Portal (CVP) - CVP - A
Answer
12/17/12 11:53 PM as a reply to CLAUDIO RIVAS.
What MSSQL library do you have ? I would suggest not to use the Cisco DB element. Just call it from within java code.
Hemal
________________________________
From: Cisco Developer Community Forums [cdicuser@developer.cisco.com]
Sent: Monday, December 17, 2012 4:00 PM
To: cdicuser@developer.cisco.com
Subject: New Message from CLAUDIO RIVAS in Customer Voice Portal (CVP) - CVP - All Versions: CVP MSSQL migration- The statement did not return a result set.

CLAUDIO RIVAS has created a new message in the forum "CVP - All Versions": -------------------------------------------------------------- Hi, currently i have an issue running stored procedures from MSSQL Server, i’m trying to migrate the standalone outbound script to use MSSQL, but return the following error - The statement did not return a result set.
<!--[if gte mso 9]><xml>
<officeDocumentSettings>
<o:AllowPNG/>
</officeDocumentSettings>
</xml><!-->

<!--[if gte mso 9]><xml>
<w:WordDocument>
<wiew>Normal</wiew>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:HyphenationZone>21</w:HyphenationZone>
<wunctuationKerning/>
<walidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<woNotPromoteQF/>
<w:LidThemeOther>ES-TRAD</w:LidThemeOther>
<w:LidThemeAsian>JA</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<wontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<wontFlipMirrorIndents/>
<wverrideTableStyleHps/>
<w:UseFELayout/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="&#45;-"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><!--><!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true"
DefSemiHidden="true" DefQFormat="false" DefPriority="99"
LatentStyleCount="276">
<w:LsdException Locked="false" Priority="0" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" Priority="39" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" Name="toc 9"/>
<w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" Priority="10" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" Priority="11" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" Priority="22" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" Priority="59" SemiHidden="false"
UnhideWhenUsed="false" Name="Table Grid"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" SemiHidden="false"
UnhideWhenUsed="false" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" SemiHidden="false"
UnhideWhenUsed="false" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" SemiHidden="false"
UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" SemiHidden="false"
UnhideWhenUsed="false" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" SemiHidden="false"
UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" SemiHidden="false"
UnhideWhenUsed="false" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/>
</w:LatentStyles>
</xml><!-->

<!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Tabla normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:12.0pt;
font-family:Cambria;
mso-ascii-font-family:Cambria;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Cambria;
mso-hansi-theme-font:minor-latin;}
</style>
<!-->

<!--StartFragment-->

I attach the code and logs, help needed.

Regards,
Claudio.



USE
GO
/****** Object: StoredProcedure . Script Date: 12/16/2012 18:31:40 ******/
SETANSI_NULLS ON
GO
SETQUOTED_IDENTIFIER ON
GO
--EXEC 3
ALTERPROCEDURE .
@max_attempts
--WITH EXECUTE AS CALLER
AS
BEGIN

declare @dnis_value varchar(20)
SET @dnis_value ='xxxx'
declare @id_value integer
SET @id_value = 0
declare @rna_value integer
SET @rna_value = 0
declare @cli_value varchar(20)
SET @cli_value = ''
declare @uui_value varchar(256)
SET @uui_value = ''

--start transaction;
BEGIN transaction
select TOP 1 @dnis_value=dnis,
@id_value =id,
@rna_value = rna,
@cli_value = cli,
@uui_value = uui
--into @dnis_value, @id_value, @rna_value, @cli_value, @uui_value
from outdials
where
(current_status='OUTDIAL_PENDING')AND
(attempts < @max_attempts) AND
(next_retry < getdate())
order by next_retry ASC

if @dnis_value !='xxxx'
BEGIN
update outdials set current_status='OUTDIAL_IN_PROGRESS', attempts=attempts + 1
where id = @id_value

END

COMMIT

select
@dnis_value as dnis,
@id_value as id,
@rna_value as rna,
@cli_value as cli,
@uui_value as uui

END


11.11.11.11.1355777435406.5049.outbound,12/17/2012 14:50:35.640, The error was: A built-in element encountered an exception of type com.audium.server.AudiumException. The statement did not return a result set. The root cause was: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
com.audium.server.AudiumException: A built-in element encountered an exception of type com.audium.server.AudiumException.
at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:413)
at com.audium.server.controller.Controller.goToAction(Controller.java:2911)
at com.audium.server.controller.Controller.goToElement(Controller.java:2643)
at com.audium.server.controller.Controller.continueCall(Controller.java:2468)
at com.audium.server.controller.Controller.doPost(Controller.java:696)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:879)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:662)
Caused by: com.audium.server.AudiumException: The statement did not return a result set.
at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:239)
at com.audium.server.voiceElement.ActionElementBase.service(ActionElementBase.java:387)
... 20 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:408)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
at com.audium.server.action.database.DatabaseAction.doAction(DatabaseAction.java:199)
<!--EndFragment-->
--
To respond to this post, please click the following link: http://developer.cisco.com/web/cvp/community/-/message_boards/view_message/9488329 or simply reply to this email.
Flag

RE: CVP MSSQL migration- The statement did not return a result set.
Answer
12/18/12 1:36 AM as a reply to CLAUDIO RIVAS.
Hi Claudio,
I don't have MSSQL to test this out, but I've been able to execute stored procs using MySql. While there might be a problem with the MSSQL driver, we can first try to rule out some studio problems before you resort to writing your own DB element.
1. In the DB element that you're using to call the stored proc, what do you have in the 'Type' setting?
2. And what do you have in the Sql Query setting?
 
 
Flag

RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Answer
12/18/12 1:42 AM as a reply to Janine Graves.
Janine,

Were you able to execute stored procedures in MySQL that returned a data set?
Flag

RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Answer
12/18/12 2:06 AM as a reply to Asher Schweigart.
I can execute stored procs and stored functions that return data.
But, the way the data is retrieved might be different that with MSSQL.
I have to use one DB element to exec the stored proc and then another DB element to retrieve the results.

The code below is also in the attached file (in case it looks like crap when I post this).  I copy the entire contents of the attached file right into my MySql command line client.

Things that start with dash dash '--' are comments, which explain how to call these from Studio DB element.

 

--paste all this into MySql to create the procedures and functions

-- ------------------------------------
-- ------------------------------------
-- Procedure to schedule the pickup

-- ------------------------------------
-- -----------------------------------
--Procedure with one in and one out parameter

delimiter ;
drop procedure if exists accountbal;
delimiter //
create procedure accountbal(in oacctnum int, out obalance int)
begin
  select balance1 into obalance from account where acctnum=oacctnum;
end //

delimiter ;

-- from Studio, execute this as an 'Update' to call the procedure
call accountbal(1111, @balance);
-- from Studio, execute this as 'Single' to get balance as element data
select @balance as balance;


-- -----------------------------------
-- -----------------------------------
--Procedure with 2 in and 2 out parameters
-- Get back NULL if acctnum doesn't exist
delimiter ;
drop procedure if exists withdraw;
delimiter //
create procedure withdraw(in oacctnum int, in oamount int, out onewbalance int(12), out osuccess varchar(6))
begin
  declare balance int(12);
  select 'false' into osuccess;
  select balance1 into balance from account where acctnum=oacctnum;
  select balance into onewbalance;
  if balance > oamount then
         update account set balance1 = balance1 - oamount where acctnum=oacctnum;
         select balance1 into onewbalance from account where acctnum=oacctnum;
         select 'true' into osuccess;
  end if;
end //

delimiter ;
--
-- In studio, execute this as an 'Update' by pasting this into the sql query:
call withdraw(1111, 1000, @newbalance,@success);
-- In studio, execute this as 'Single' by pasting this into the sql query:
select @newbalance newbalance, @success success;




-- ---------------------------------------
-- ---------------------------------------
-- Stored function
-- This function takes an account number and a transfer amount and moves that amount from balance1 to balance2
-- Returns 'true' if successful, else returns 'false'
delimiter ;
drop function if exists transfer;
delimiter //
create function transfersuccess(oacctnum int, oamount int) returns varchar(6) deterministic
begin
  declare bal1 int;
  declare bal2 int;
  declare success varchar(6);
  set success = 'false';
  select balance1 into bal1 from account where acctnum=oacctnum;

  select balance2 into bal2 from account where acctnum=oacctnum;
  if bal2 is null then
    update account set balance2=0 where acctnum=oacctnum;
  end if;

  if bal1 >= oamount then
         update account set balance1 = balance1 - oamount, balance2=balance2+oamount where acctnum=oacctnum;
         set success = 'true';
  end if;
  return success;
end
//
delimiter ;


-- In studio, execute this as 'Single' to call the function, and to get
-- back element data named 'success' with value true or false.
select transfersuccess(2222,1) as success;
-- then execute this as a 'Single' to get the new account information
select * from account where acctnum=2222;







 
Flag

RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Answer
12/18/12 2:08 AM as a reply to Janine Graves.
Oops, now I'll post the attachment on the forum.
Attachments:
Flag

RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Answer
12/18/12 1:41 PM as a reply to Janine Graves.
Have you executed stored procs on MS SQL without errors ?

From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
Sent: Monday, December 17, 2012 8:09 PM
To: cdicuser@developer.cisco.com
Subject: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - All Versions: RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A

Janine Graves has created a new message in the forum "CVP - All Versions": -------------------------------------------------------------- Oops, now I'll post the attachment on the forum.
--
To respond to this post, please click the following link: http://developer.cisco.com/web/cvp/forums/-/message_boards/view_message/9495749 or simply reply to this email.
Flag

Re: New Message from Hemal Mehta in Customer Voice Portal (CVP) - CVP - All
Answer
12/18/12 1:55 PM as a reply to Hemal Mehta.
I don't have MSSQL, so no, I haven't.
Flag

RE: CVP MSSQL migration- The statement did not return a result set.
Answer
12/18/12 9:20 PM as a reply to Janine Graves.
Hi Janine,
Seems there is something on the MSSQL stored procedures that CVP or MSSQL JDBC driver understand on a diferent way, we have changed the order on the stored procedure to have a response, we had an update first and a select after that, so thats why i dind't had an answer form MSSQL, fortunately changing the order of execution worked for us, so first execute a select this return something (for this time defaults), seems the result that MSSQL return to the query and the MSSQL JDBC driver is the first execution of the store.
The way i execute is this:
set nocount on;
EXEC . 3;
Hope this helps, if is helpful, please rate.
Regards,
Flag

RE: CVP MSSQL migration- The statement did not return a result set.
Answer
12/18/12 9:51 PM as a reply to CLAUDIO RIVAS.
If something changes with the way DB element works in future versions, you will be tied to it and will need to make changes again in your code.  If you were to use JDBC , hibernate you will have  very standard implementation that is more dependent on core java than how Cisco were to implement their underlying code.
Hemal
Flag

Re: New Message from Hemal Mehta in Customer Voice Portal (CVP) - CVP - All
Answer
12/18/12 9:56 PM as a reply to Hemal Mehta.
Hi Hemal, Can you post a sample of the source code for a simple custom element that execs a MSSQL stored proc? Thanks, Janine -- Janine Graves
Flag

RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Answer
12/19/12 12:34 AM as a reply to Janine Graves.
Janine,
I avoid custom elements unless really necessary. I focus my attention on having robust and well defined java code. Just use action class. Also all the variable etc are defined in a property file which makes it very configurable and I can change it on the fly without recompiling code.
I am on the road, but can post the sample code in a day or two.
Hemal
________________________________
From: Cisco Developer Community Forums [cdicuser@developer.cisco.com]
Sent: Tuesday, December 18, 2012 3:56 PM
To: cdicuser@developer.cisco.com
Subject: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - All Versions: Re: New Message from Hemal Mehta in Customer Voice Portal (CVP) - CVP - All

Janine Graves has created a new message in the forum "CVP - All Versions": -------------------------------------------------------------- Hi Hemal, Can you post a sample of the source code for a simple custom element that execs a MSSQL stored proc? Thanks, Janine -- Janine Graves
--
To respond to this post, please click the following link: http://developer.cisco.com/web/cvp/forums/-/message_boards/view_message/9533839 or simply reply to this email.
Flag

RE: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - A
Answer
12/19/12 12:34 AM as a reply to Janine Graves.
Janine,
I avoid custom elements unless really necessary. I focus my attention on having robust and well defined java code. Just use action class. Also all the variable etc are defined in a property file which makes it very configurable and I can change it on the fly without recompiling code.
I am on the road, but can post the sample code in a day or two.
Hemal
________________________________
From: Cisco Developer Community Forums [cdicuser@developer.cisco.com]
Sent: Tuesday, December 18, 2012 3:56 PM
To: cdicuser@developer.cisco.com
Subject: New Message from Janine Graves in Customer Voice Portal (CVP) - CVP - All Versions: Re: New Message from Hemal Mehta in Customer Voice Portal (CVP) - CVP - All

Janine Graves has created a new message in the forum "CVP - All Versions": -------------------------------------------------------------- Hi Hemal, Can you post a sample of the source code for a simple custom element that execs a MSSQL stored proc? Thanks, Janine -- Janine Graves
--
To respond to this post, please click the following link: http://developer.cisco.com/web/cvp/forums/-/message_boards/view_message/9533839 or simply reply to this email.
Flag