SQL Server 2000 store procedure

saugortgarcia · **Posted:** Wed May 10, 2006 2:24 pm

Hi, I am testing a db connection and I have to run some db store procedures. I am having some trouble trying to get right with this. I am lunching a sp that retun a value, but teleflow does not capture anything. I think my error could be in how I am building the app in teleflow, could you give me some clues? Teleflow log: enGenic TeleFlow: Build 2006.0207 (c) 1994 - 2006 enGenic All rights reserved. 2006/05/10 17:15:42.578: Driver successfully opened. 2006/05/10 17:15:42.593: Loading language 'C:\Program Files\TeleFlow\Lang_English.dll' 2006/05/10 17:15:42.609: Setting system variable '@SYSDRIVE' to 'C:' 2006/05/10 17:15:42.609: Setting system variable '@SYSDIR' to '\VAPP\RepMan' 2006/05/10 17:15:42.609: TeleFlow Application: C:\VAPP\RepMan\Generica.Tap 2006/05/10 17:15:42.609: Ver: 0200 S/N: 00000 2006/05/10 17:15:42.609: TeleFlow Procedure: > Tam\TP_Bienvenida.Tam Ver: 0200 S/N: 00000 2006/05/10 17:15:42.609: TeleFlow Procedure: Tam\InputMan.Tam Ver: 0200 S/N: 00000 2006/05/10 17:15:42.625: TeleFlow Procedure: Tam\Defaults.Tam Ver: 0200 S/N: 00000 2006/05/10 17:15:42.625: TeleFlow Procedure: Tam\TP_Consulta.Tam Ver: 0200 S/N: 00000 2006/05/10 17:15:42.625: TeleFlow Procedure: Tam\TP_ProFax.Tam Ver: 0200 S/N: 00000 2006/05/10 17:15:42.625: TeleFlow Procedure: Tam\TP_Operador.Tam Ver: 0200 S/N: 00000 2006/05/10 17:15:42.625: TeleFlow Procedure: Tam\TP_DBLogger.Tam Ver: 0200 S/N: 00000 2006/05/10 17:15:42.625: Setting system variable '@SYSDRIVE' to 'C:' 2006/05/10 17:15:42.625: Setting system variable '@SYSDIR' to '\VAPP\RepMan' 2006/05/10 17:15:42.625: Setting system variable '@P1' to '' 2006/05/10 17:15:42.625: Setting system variable '@P2' to '' 2006/05/10 17:15:42.625: Setting system variable '@P3' to '' 2006/05/10 17:15:42.625: Setting system variable '@P4' to '' 2006/05/10 17:15:42.625: Setting system variable '@P5' to '' 2006/05/10 17:15:42.625: Setting system variable '@P6' to '' 2006/05/10 17:15:42.640: Setting system variable '@P7' to '' 2006/05/10 17:15:42.640: Setting system variable '@P8' to '' 2006/05/10 17:15:42.640: Setting system variable '@P9' to '' 2006/05/10 17:15:42.640: Setting system variable '@PROMPT' to 'C:\Program Files\TeleFlow\Languages\English' 2006/05/10 17:15:42.640: Setting global variable '@VOLUME' to '0' 2006/05/10 17:15:42.640: Setting global variable '@TIMEOUT' to '3000' 2006/05/10 17:15:42.640: Setting system variable '@FAXQPTR' to '' 2006/05/10 17:15:42.640: Setting system variable '@PORT' to '1' 2006/05/10 17:15:42.640: Setting system variable '@CALL_LINE' to '' 2006/05/10 17:15:42.640: Setting system variable '@SPELLTONES' to '' 2006/05/10 17:15:42.640: Setting system variable '@RECORD_RESULT' to '' 2006/05/10 17:15:42.640: Setting global variable '@SYS_DATA' to '' 2006/05/10 17:15:42.640: Setting system variable '@CHAR_SOH' to '' 2006/05/10 17:15:42.640: Setting system variable '@CHAR_STX' to '' 2006/05/10 17:15:42.640: Setting system variable '@CHAR_ETX' to '' 2006/05/10 17:15:42.640: Setting system variable '@CHAR_EOT' to '' 2006/05/10 17:15:42.640: Setting system variable '@CHAR_ENQ' to '' 2006/05/10 17:15:42.640: Setting system variable '@CHAR_ACK' to '' 2006/05/10 17:15:42.640: Setting system variable '@CHAR_BEL' to '' 2006/05/10 17:15:42.640: Setting system variable '@CHAR_BS' to '' 2006/05/10 17:15:42.640: Setting system variable '@CHAR_TAB' to ' ' 2006/05/10 17:15:42.656: Setting system variable '@TAB' to ' ' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_LF' to ' ' 2006/05/10 17:15:42.656: Setting system variable '@LINEFEED' to ' ' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_VT' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_FF' to '' 2006/05/10 17:15:42.656: Setting system variable '@FORMFEED' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_CR' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_SO' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_SI' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_DLE' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_DC1' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_DC2' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_DC3' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_DC4' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_NAK' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_SYN' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_ETB' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_CAN' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_EM' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_SUB' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_ESC' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_FS' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_GS' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_RS' to '' 2006/05/10 17:15:42.656: Setting system variable '@CHAR_US' to '' 2006/05/10 17:15:42.671: Setting system variable '@SPACE' to ' ' 2006/05/10 17:15:42.671: Setting system variable '@CHAR_DEL' to '' 2006/05/10 17:15:42.671: Setting system variable '@HANGUP' to '' 2006/05/10 17:15:42.671: Setting system variable '@FATAL' to '' 2006/05/10 17:15:42.671: Setting system variable '@CALL_SEIZED' to '' 2006/05/10 17:15:42.671: Setting system variable '@CALL_START' to '' 2006/05/10 17:15:42.671: Setting system variable '@CALL_END' to '' 2006/05/10 17:15:42.671: Setting system variable '@CALL_ELAPSE' to '' 2006/05/10 17:15:42.671: Setting system variable '@WEEKDAY' to '' 2006/05/10 17:15:42.671: Setting system variable '@DATE' to '' 2006/05/10 17:15:42.671: Setting system variable '@TIME' to '' 2006/05/10 17:15:42.671: Setting system variable '@UNIX_TIME' to '' 2006/05/10 17:15:42.671: Setting system variable '@HOOK_STATUS' to '' 2006/05/10 17:15:42.671: Setting system variable '@APPID' to '1001' 2006/05/10 17:15:42.671: Setting system variable '@CALLOUT_RESULT' to '' 2006/05/10 17:15:42.671: Setting system variable '@CALLOUT_REASON' to '' 2006/05/10 17:15:42.671: Setting system variable '@TRUNK_TYPE' to '' 2006/05/10 17:15:42.671: Setting system variable '@SYS_CALLREF' to '' 2006/05/10 17:15:42.671: Initializing... 2006/05/10 17:15:42.671: Initialization done 2006/05/10 17:15:42.671: ==>TP_Bienvenida 2006/05/10 17:15:42.671: [1] START 2006/05/10 17:15:42.671: [3] Reload On 2006/05/10 17:15:42.671: [65] Global Event 2006/05/10 17:15:42.687: Setting system variable '@HANGUP' to 'LABFINISH0' 2006/05/10 17:15:42.687: [49] Run Flowchart 2006/05/10 17:15:42.687: Flowchart: 'Defaults' 2006/05/10 17:15:42.687: TP_Bienvenida==>Defaults 2006/05/10 17:15:42.687: [1] START 2006/05/10 17:15:42.687: [122] Set Language 2006/05/10 17:15:42.687: Loading language 'C:\Program Files\TeleFlow\Lang_Spanish.dll' 2006/05/10 17:15:42.687: Setting system variable '@PROMPT' to 'C:\Program Files\TeleFlow\Languages\Spanish' 2006/05/10 17:15:42.687: [86] Set Global Variable 2006/05/10 17:15:42.703: Setting global variable '@MENULOOP' to '3' 2006/05/10 17:15:42.703: [96] Set Global Variable 2006/05/10 17:15:42.703: Setting global variable '@DBLOOP' to '4' 2006/05/10 17:15:42.703: [100] Set Global Variable 2006/05/10 17:15:42.703: Setting global variable '@PLAY_VOLUME' to '0' 2006/05/10 17:15:42.703: [102] Set Global Variable 2006/05/10 17:15:42.703: Setting global variable '@

saugortgarcia · **Posted:** Wed May 10, 2006 2:26 pm

2006/05/10 17:15:42.703: Setting global variable '@REC_VOLUME' to '0' 2006/05/10 17:15:42.703: [107] Set Global Variable 2006/05/10 17:15:42.703: Setting global variable '@SYSLOC' to 'C:\VAPP\RepMan' 2006/05/10 17:15:42.703: [104] Connect Point 2006/05/10 17:15:42.703: [105] Connect Point 2006/05/10 17:15:42.703: [132] Set Global Variable 2006/05/10 17:15:42.703: Setting global variable '@RINGCOUNT' to '5' 2006/05/10 17:15:42.703: [88] Set Global Variable 2006/05/10 17:15:42.718: Setting global variable '@COUNTER' to '0' 2006/05/10 17:15:42.718: [98] Set Global Variable 2006/05/10 17:15:42.718: Setting global variable '@DB_COUNTER' to '0' 2006/05/10 17:15:42.718: [109] Set Global Variable 2006/05/10 17:15:42.718: Setting global variable '@VOLUME' to '0' 2006/05/10 17:15:42.718: [111] Compare 2006/05/10 17:15:42.718: @TRUNK_TYPE = ANALOG? 2006/05/10 17:15:42.718: (string):SIMULATED = (string):ANALOG? 2006/05/10 17:15:42.718: FALSE 2006/05/10 17:15:42.718: [116] Set Global Variable 2006/05/10 17:15:42.718: Setting global variable '@PICKUP_RINGCOUNT' to '0' 2006/05/10 17:15:42.718: [118] Set Global Variable 2006/05/10 17:15:42.718: Setting global variable '@CONNECTION_DELAY' to '500' 2006/05/10 17:15:42.718: [129] Set Global Variable 2006/05/10 17:15:42.718: Setting global variable '@HANG_WAIT' to '1000' 2006/05/10 17:15:42.718: [131] Connect Point 2006/05/10 17:15:42.718: [2] FINISH 2006/05/10 17:15:42.734: TP_Bienvenida<==Defaults 2006/05/10 17:15:42.734: [19] Wait for Call 2006/05/10 17:15:44.234: Wait for 2 rings and then pickup 2006/05/10 17:15:50.828: Setting global variable '@LLAMANTE_ANI' to '' 2006/05/10 17:15:50.828: Setting global variable '@LLAMANTE_DNIS' to '' 2006/05/10 17:15:50.828: Connection delay: 500 ms 2006/05/10 17:15:51.328: [50] Set Global Variable 2006/05/10 17:15:51.328: Setting global variable '@COUNTER' to '3' 2006/05/10 17:15:51.328: [17] Set Global Variable 2006/05/10 17:15:51.328: Setting global variable '@LLAMADA_INGRESO' to '2006/05/10 17:15:51' 2006/05/10 17:15:51.328: [69] Run Flowchart 2006/05/10 17:15:51.328: Flowchart: 'TP_DBLogger' 2006/05/10 17:15:51.328: Setting local variable '@T_OP' to 'NEW' 2006/05/10 17:15:51.328: TP_Bienvenida==>TP_DBLogger 2006/05/10 17:15:51.343: [1] START 2006/05/10 17:15:51.343: [3] DB Connect 2006/05/10 17:15:51.343: ConnectString: DSN=VAppRepDB;UID=ivradmin;PWD=ivradmin 2006/05/10 17:15:51.359: SQL Error State: 01000 Native Error Code: 1645 ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'IVRCDR'. 2006/05/10 17:15:51.359: SQL Error State: 01000 Native Error Code: 1647 ODBC Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. 2006/05/10 17:15:51.375: SQL Error State: HY092 Native Error Code: 0 ODBC Error: [Microsoft][ODBC SQL Server Driver]Invalid attribute/option identifier 2006/05/10 17:15:51.375: [9] Case 2006/05/10 17:15:51.375: Case argument '@T_OP' evaluates to 'NEW' 2006/05/10 17:15:51.375: Case 1 value 'NEW' evaluates to 'NEW' 2006/05/10 17:15:51.375: [25] Set Global Variable 2006/05/10 17:15:51.375: Setting global variable '@T' to '@T' 2006/05/10 17:15:51.375: [6] SQL Statement 2006/05/10 17:15:51.375: Warning time threshold not specified, using default of 2000 ms 2006/05/10 17:15:51.375: <----- BEGIN -----> declare @T int exec sp_addnewcall '', '', '2006/05/10 17:15:51', 1, 'I', 'CESTATICKET_V0', @T output select @T <----- END -----> 2006/05/10 17:15:51.390: Elapsed Time: 0.015 2006/05/10 17:15:51.390: [8] SQL End 2006/05/10 17:15:51.390: [5] DB Disconnect 2006/05/10 17:15:51.390: [2] FINISH 2006/05/10 17:15:51.406: TP_Bienvenida<==TP_DBLogger 2006/05/10 17:15:51.406: [25] Play 2006/05/10 17:15:51.406: Script code 'Vox\Eng\Scripts\msj000.Wav' evaluates to 'Vox\Eng\Scripts\msj000.Wav' 2006/05/10 17:15:51.406: Playing 'C:\VAPP\RepMan\Vox\Eng\Scripts\msj000.Wav' 2006/05/10 17:15:55.187: [27] Play 2006/05/10 17:15:55.187: Script code 'Vox\Eng\Scripts\msj001.Wav' evaluates to 'Vox\Eng\Scripts\msj001.Wav' 2006/05/10 17:15:55.187: Playing 'C:\VAPP\RepMan\Vox\Eng\Scripts\msj001.Wav' 2006/05/10 17:16:04.859: Caller hung up 2006/05/10 17:16:04.906: HANGUP 2006/05/10 17:16:04.906: Attempting to jump to Hangup Global Event label: 'LABFINISH0' 2006/05/10 17:16:04.906: [66] Label 2006/05/10 17:16:04.906: Label: LABFINISH0 2006/05/10 17:16:04.906: [67] Wait 2006/05/10 17:16:04.921: Waiting 1500 milliseconds 2006/05/10 17:16:06.421: [21] Hang Up 2006/05/10 17:16:06.421: [63] Set Global Variable 2006/05/10 17:16:06.421: Setting global variable '@LLAMADA_TERMINO' to '2006/05/10 17:16:06' 2006/05/10 17:16:06.421: [7] Go to Label 2006/05/10 17:16:06.437: Label: 'LABFINISH1' 2006/05/10 17:16:06.437: [6] Label 2006/05/10 17:16:06.437: Label: LABFINISH1 2006/05/10 17:16:06.453: [2] FINISH 2006/05/10 17:16:06.453: End of application. 2006/05/10 17:16:08.625: XMLList destructor done Store procedure: CREATE PROCEDURE [dbo].[sp_AddNewCall] @call_an VARCHAR(25), @call_dns VARCHAR(25), @call_begin VARCHAR(25), @call_port int, @call_typ char, @call_app VARCHAR(25), @call_dbid int output AS INSERT INTO call_dump(call_ani,call_dnis,call_start,call_port,call_type,call_vapp) VALUES(@call_an,@call_dns,CAST(@call_begin AS DATETIME),@call_port,@call_typ,@call_app) declare @Call_ID int select @Call_ID = call_ref from call_dump where call_ani = @call_an and call_dnis = @call_dns and call_start = CAST(@call_begin AS DATETIME) and call_port = @call_port and call_type = @call_typ and call_vapp = @call_app INSERT INTO call_dump_detail VALUES( @Call_ID, 'MENU_PPL', @call_app, CAST(@call_begin AS DATETIME)) -- SELECT @Call_ID AS CALL_DBID select @Call_dbid = call_ref from call_dump where call_ani = @call_an and call_dnis = @call_dns and call_start = CAST(@call_begin AS DATETIME) and call_port = @call_port and call_type = @call_typ and call_vapp = @call_app return @call_dbid GO

Chris · **Posted:** Mon May 15, 2006 10:26 am

We only use stored procedure we are provided by others, so I can't advise you on precisely what the problem is, however, here is a description of the typical problem people have getting results returned in TeleFlow, and what they do to get past it: Because of the way TeleFlow retrieves data, column names must be returned. Here is a cut down description from someone who created stored procedures that work from within TeleFlow, where they had this problem at first, and subsequently fixed it: " Originally I had the final select statement as: > SELECT @MessageType, @PIN This didn't work because there were no column names for what was returned. So I changed it to: SELECT @MessageType MessageType, @PIN PIN where there were column names as MessageType and PIN. " I suspect that this line select @Call_dbid = call_ref from call_dump Needs to have something like this: "select @Call_dbid Call_dbid" to return it to TeleFlow.