Very Simple Script, But Not Works

[Guest DiegoBellini]

I'm not that to do with this simple script.

The procedure works correctly in SQL, but no in voiceguide.

 

Please, help me

 

Thanks

VG.zip

[SupportTeam]

From the attached script and traces we can see that you are trying to run this command in the DB Query module:

 

Exec sp_ATH_EmergencyBlock 100104231,'583235367'

 

Which database are you using? Can you .ZIP and post the database itself (with a few rows of sample data) so that we can replicate this on our test system?

 

 

165258.750 9 1 db oledb RunOleDbQuery DisableATH, C:\Program Files\VoiceGuide\Scripts\Roosevelt Roads\CustMast_bkup, Provider=SQLOLEDB.1;Password=ivr123;Persist Security Info=True;User ID=ivr;Initial Catalog=CustMast_bkup;Data Source=SERVER-FDO1, Exec sp_ATH_EmergencyBlock 100104231,'583235367'

 

165258.750 16 1 db oledb ConnectAndRun_OleDb begin. Create connection: [Provider=SQLOLEDB.1;Password=ivr123;Persist Security Info=True;User ID=ivr;Initial Catalog=CustMast_bkup;Data Source=SERVER-FDO1]

165258.750 16 1 db oledb connection open call [Provider=SQLOLEDB.1;Password=ivr123;Persist Security Info=True;User ID=ivr;Initial Catalog=CustMast_bkup;Data Source=SERVER-FDO1]

165258.750 16 1 db oledb connection open returned.

165258.750 16 1 db oledb DataAdapter.Fill init.

165258.750 16 1 db oledb DataAdapter.Fill call.

165258.890 16 1 db oledb DataAdapter.Fill returned.

165258.890 16 1 db xmlData=<vgmDBQueryOleDb />

165258.890 16 1 ERROR v7.0.2942.25251 (Mon 21/01/2008 14:01:43.15) ConnectAndRun_OleDb : DataTableReader Cannot be created. There is no DataTable in DataSet.

at System.Data.DataSet.CreateDataReader()

at ..()

165258.890 16 1 oVgmDbQuery_AdoNetCommon_Completed SqlQueryType=READER, RowsCount=0

165258.890 16 1 oVgmDbQuery_AdoNetCommon_Completed strError=DataTableReader Cannot be created. There is no DataTable in DataSet.

[Guest DiegoBellini]

QUOTE

From the attached script and traces we can see that you are trying to run this command in the DB Query module:

Exec sp_ATH_EmergencyBlock 100104231,'583235367'

Which database are you using? Can you .ZIP and post the database itself (with a few rows of sample data) so that we can replicate this on our test system?


165258.750 9 1 db oledb RunOleDbQuery DisableATH, C:\Program Files\VoiceGuide\Scripts\Roosevelt Roads\CustMast_bkup, Provider=SQLOLEDB.1;Password=ivr123;Persist Security Info=True;User ID=ivr;Initial Catalog=CustMast_bkup;Data Source=SERVER-FDO1, Exec sp_ATH_EmergencyBlock 100104231,'583235367'

165258.750 16 1 db oledb ConnectAndRun_OleDb begin. Create connection: [Provider=SQLOLEDB.1;Password=ivr123;Persist Security Info=True;User ID=ivr;Initial Catalog=CustMast_bkup;Data Source=SERVER-FDO1]
165258.750 16 1 db oledb connection open call [Provider=SQLOLEDB.1;Password=ivr123;Persist Security Info=True;User ID=ivr;Initial Catalog=CustMast_bkup;Data Source=SERVER-FDO1]
165258.750 16 1 db oledb connection open returned.
165258.750 16 1 db oledb DataAdapter.Fill init.
165258.750 16 1 db oledb DataAdapter.Fill call.
165258.890 16 1 db oledb DataAdapter.Fill returned.
165258.890 16 1 db xmlData=<vgmDBQueryOleDb />
165258.890 16 1 ERROR v7.0.2942.25251 (Mon 21/01/2008 14:01:43.15) ConnectAndRun_OleDb : DataTableReader Cannot be created. There is no DataTable in DataSet.
at System.Data.DataSet.CreateDataReader()
at ..()
165258.890 16 1 oVgmDbQuery_AdoNetCommon_Completed SqlQueryType=READER, RowsCount=0
165258.890 16 1 oVgmDbQuery_AdoNetCommon_Completed strError=DataTableReader Cannot be created. There is no DataTable in DataSet.

It's a store procedure that it realizes update in a table
They sending the table, a top select 10 of the same one, and the procedure.
If you run the procedure in the query analyzer with the same parameters , it works correctly.

Thanks

Table___query___procedure.zip

[SupportTeam]

Which database are you using?

[Guest DiegoBellini]

QUOTE

Which database are you using?

I do not understand very well your question

I'm are using Microsoft SQL Server 2005.
Server: SERVER-FDO1
Database: CustMast_bkup

It's ok?

[SupportTeam]

Can you .ZIP and post the database itself (with a few rows of sample data) so that we can replicate this on our test system?

[Guest DiegoBellini]

QUOTE

Can you .ZIP and post the database itself (with a few rows of sample data) so that we can replicate this on our test system?

Already I sent the table, the procedure, and a query.
Please, they can be more specific with the request that they do to me, I do not understand that it is what they need specifically
If they refer to a back up of the database, it is impossible since it weighs 20GB.
Thanks

[SupportTeam]

If we cannot get a sample DB which we can setup on our test systems here to try out the query then looks like tests of various possible solutions would need to be tried on hyour system.

Please update to this version of VG v7: [old link removed] this version handles the execution of the "EXEC" SQL command a bit different. Let see if it works on your system/DB. Please post the vgEngine trace of the DB Query call to let us see how it went.

Another approach would be to not use a Database Query module but to run the EXEC command form within a VBScript. Create a VBScript that connects to your database and run the command from there.

[Guest DiegoBellini]

QUOTE

If we cannot get a sample DB which we can setup on our test systems here to try out the query then looks like tests of various possible solutions would need to be tried on hyour system.

Please update to this version of VG v7: [old link removed] this version handles the execution of the "EXEC" SQL command a bit different. Let see if it works on your system/DB. Please post the vgEngine trace of the DB Query call to let us see how it went.

Another approach would be to not use a Database Query module but to run the EXEC command form within a VBScript. Create a VBScript that connects to your database and run the command from there.

Not know to write in language VBScript, because of it I thought to use a module database query.
Can they give me some idea of as writing it, with this information that I sent?

Thanks

[SupportTeam]

Please try the "_exec" version of VG first, as per our previous post.

 

Some examples of Database queries are in the Help file page which covers the Run VBScript module.

[Guest DiegoBellini]

QUOTE

Please try the "_exec" version of VG first, as per our previous post.

Some examples of Database queries are in the Help file page which covers the Run VBScript module.

Ok, let's try this.

This one is my script that fails. It is an exact copy of the script of voiceguide. This one prepared to run in windows. Because I have this mistake when I execute it in windows. I do not know Very much visual Basic.

The mistake is:
Line: 1
Char: 50
Error: Operation is not allowed when the object is closed.
Code: 800A0E78
Source ADODB.Recordset

And this is the script

'------------------- Windows Enviroment ------------------------------------------------

'Parametros del store procedure
Sucursal = 1
Cajero = 801
SocioNum = 5225
SysDate = CStr(Date) + " " + CStr(time)
Status = ""
DR_Account = "39010.01.00005225.0001"
CR_Account = "22200.23.00005225.0123"
Amount = 200
CodigoDeTrans = "Transferencia entre cuentas"
Module = "TELECOOP"
Return = 0

Const adParamInput = 1
Const adParamOutput = 2
Const adInteger = 3
Const adCurrency = 6
Const adCmdStoredProc = 4
Const adExecuteNoRecords = 128
Const adVarChar = 200

Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString ="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CoopDB_Telecoop;Data Source=GLCIVRDEMO"
Set adoCmd = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")

conn.Open
With adoCmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "SP_RegisterTransaction"
.CommandTimeout = 120

.Parameters.Append .CreateParameter("@Sucursal", adInteger, adParamInput, , Sucursal)
.Parameters.Append .CreateParameter("@Cajero", adInteger, adParamInput, , Cajero)
.Parameters.Append .CreateParameter("@SocioNum", adInteger, adParamInput, , SocioNum)
.Parameters.Append .CreateParameter("@Date", adVarChar, adParamInput, 30, SysDate)
.Parameters.Append .CreateParameter("@Status", adVarChar, adParamInput, 50, Status)
.Parameters.Append .CreateParameter("@DR_Account", adVarChar, adParamInput, 30, DR_Account)
.Parameters.Append .CreateParameter("@CR_Account", adVarChar, adParamInput, 30, CR_Account)
.Parameters.Append .CreateParameter("@Amount", adCurrency, adParamInput, , Amount)
.Parameters.Append .CreateParameter("@CodigoDeTrans", adVarChar, adParamInput, 150, CodigoDeTrans)
.Parameters.Append .CreateParameter("@Module", adVarChar, adParamInput, 30, Module)
.Execute Return, , adExecuteNoRecords
End With

rs.Open adoCmd,,adOpenDynamic
rs.MoveFirst
If Not rs.EOF Then
Return = rs.Fields(0).Value
End If

Set adocmd.ActiveConnection = Nothing

If Return = 0 Then
MsgBox("Error")
Else
MsgBox("Exito Nro: " + Return)
End If

Set conn = Nothing
Set vg = Nothing


'----------------------------- VoiceGuide Enviroment ----------------------------


Set vg = CreateObject("VoiceGuide.CommandLink")

'Parametros del store procedure
Sucursal = 1
Cajero = 801
SocioNum = $RV[GetCustID]
SysDate = CStr(Date) + " " + CStr(time)
Status = ""
DR_Account = "$RV[FromAccount]"
CR_Account = "$RV[ToAccount]"
Amount = $RV[TransFondosDollCents]
CodigoDeTrans = "$RV[Description]"
Module = "TELECOOP"
Return = 0
ConnStr = "$RV[sqlConnStr]"

Const adParamInput = 1
Const adParamOutput = 2
Const adInteger = 3
Const adCurrency = 6
Const adCmdStoredProc = 4
Const adExecuteNoRecords = 128
Const adVarChar = 200

Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = ConnStr
Set adoCmd = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")

conn.Open
With adoCmd
.ActiveConnection = conn
.CommandType = adCmdStoredProc
.CommandText = "SP_RegisterTransaction"
.CommandTimeout = 120

.Parameters.Append .CreateParameter("@Sucursal", adInteger, adParamInput, , Sucursal)
.Parameters.Append .CreateParameter("@Cajero", adInteger, adParamInput, , Cajero)
.Parameters.Append .CreateParameter("@SocioNum", adInteger, adParamInput, , SocioNum)
.Parameters.Append .CreateParameter("@Date", adVarChar, adParamInput, 30, SysDate)
.Parameters.Append .CreateParameter("@Status", adVarChar, adParamInput, 50, Status)
.Parameters.Append .CreateParameter("@DR_Account", adVarChar, adParamInput, 30, DR_Account)
.Parameters.Append .CreateParameter("@CR_Account", adVarChar, adParamInput, 30, CR_Account)
.Parameters.Append .CreateParameter("@Amount", adCurrency, adParamInput, , Amount)
.Parameters.Append .CreateParameter("@CodigoDeTrans", adVarChar, adParamInput, 150, CodigoDeTrans)
.Parameters.Append .CreateParameter("@Module", adVarChar, adParamInput, 30, Module)
'.Execute Return, , adExecuteNoRecords
End With

rs.Open adoCmd,,adOpenDynamic
rs.MoveFirst
If Not rs.EOF Then
Return = rs.Fields(0).Value
End If

Set adocmd.ActiveConnection = Nothing

If Return = 0 Then
vg.Script_Goto $RV_LINEID,"","ErrorTransfiriendo",""
Else
vg.RvSet $RV_LINEID,"TrnNumber",Return
vg.Script_Goto $RV_LINEID,"","SayNumeroConfirmacion",""
End If

'rs.close
'Set rs = Nothing
'conn.Close
Set conn = Nothing
Set vg = Nothing

'--------------------------------------------- end ---------------------------------------

[SupportTeam]

Please try the "_exec" version of VG first, and post the VoiceGuide trace from the attempt to run the EXEC query.

 

The VBScript error suggests that the error is on first line of the VBScript... but the first line of the "Windows Environment" is a comment and not 50 characters long, so I'm not sure why you are getting this error. Recommend you start with a simpler VBScript to first ensure you have a working VBScript and then gradually add more to it.