Problem Using Temp Table(Database Query)

[Guest prabum01]

hi,

 

I am facing a problem using temp table in query section of Database Query

 

Sample:

 

with Temp1 as

(

//Some Operation here....

)

Select Top 1 * From Temp1 E1 Where // some operation here ...

 

When I run the same I receive the following error in log file, but when i run the same query(in log file) in sql server I am able get the result.

 

ERROR v7.1.3687.32284 (2010-02-04 17:56:08.62) ConnectAndRun_AdoNetFactory : Specified cast is not valid.

 

Am I missing something.....

[SupportTeam]

Could you please post a copy of VoiceGuide's Debug Trace which captures the call, this will allow us to see what happened.

 

Debug Trace files are created in VG's \log\ subdirectory.

 

When posting traces/scripts please .ZIP them up and post them as attachments.

[Guest prabum01]

Here are log files.........

Log.zip

[SupportTeam]

Trace show the SQL query you are using is fairly complex:

 

with Temp1 as ( select ICEM.Extension,ICEM.ContactIDFKID,dbo.GetClientCallLogCount(ICEM.ContactIDFKID) as CallsAttended,

Max(ICCL.CallEndTime) as CallEndTime from IVRContactExtensionMapping ICEM

left join IVRClientCallLog ICCL on ICEM.ContactIDFKID=ICCL.ContactIDFKID

where ICEM.IsOnline=1 and ICEM.IsAvailable=1 group by ICEM.ContactIDFKID,ICEM.Extension )

Select Top 1 Extension From Temp1 E1 Where 1 = (Select Count(Distinct(E2.CallsAttended)) From Temp1 E2 Where

E1.CallsAttended >=E2.CallsAttended) order by E1.CallEndTime asc

 

To debug where the problem with the SQL lies would recommend starting with a simpler expression and then gradually making iot more complex. You woudl then be able to determine which past of the SQL is causing the problem.

[Guest prabum01]

There is no problem in the query, I am able to achieve the results when I implement it in sql server. Is this a problem with voice guide also I have few more questions

 

1. Whether we can use #Temp tables in Database Query

2. Whether we can use Functions in Database Query like (select dbo.GetID(ID),Column2... from Table1)

3. Is is possible to call stored procedures from Voice Guide.

[SupportTeam]

Please update your system to this version of VoiceGuide:

[old link removed]

and see if the SQL query has better success. Please post vgEngine traces as before capturing the call.

 

1. Whether we can use #Temp tables in Database Query

The SQL expression gets passed onto the database without any changes, so if the database supports it then yes.

 

2. Whether we can use Functions in Database Query like (select dbo.GetID(ID),Column2... from Table1)

As above.

 

3. Is is possible to call stored procedures from Voice Guide.

You would need to do this from within the 'Run VBScript' module. VBScripts usually use the DAO COM objects for database interaction - there are some examples of how to do this in the Help file. Otherwise you need to write an EXE and call it from a Run Program module.

[Guest prabum01]

Thanks, now the query is running perfectly.