Sql Stored Procedures

[corrigaj]

I'm baffled at this point...hopefully you can help.

 

Why is it I can run this query:

CREATE PROCEDURE WEB_TELESAS_CHECKDOB(@crnum varchar(7),@DOB datetime)as

 

Select PatientID from Patient where MRN=@crnum and DOB = @DOB

GO

 

By saying:

 

exec WEB_TELESAS_CHECKDOB '$RV[getCRNUM]' ,'$RV[getMonth]/$RV[getDay]/$RV[getYear]'

 

with no problems but the following query:

 

 

CREATE PROCEDURE Web_PPS_Login_Authentication_1 (@Alias as varchar(255), @DOB as varchar(50)) AS

 

IF (@Alias = '0000000') AND (@DOB = '12/12/1950')

BEGIN

SELECT 1 as PatientID

END

ELSE

BEGIN

SELECT 0 as PatientID

END

GO

 

 

By saying:

exec Web_PPS_Login_Authentication_1 '$RV[getCRNUM]' ,'$RV[getMonth]/$RV[getDay]/$RV[getYear]'

 

returns a movelast error

 

Thanks for your time

[SupportTeam]

Don't know.

 

If you are able to run:

 

exec WEB_TELESAS_CHECKDOB '$RV[getCRNUM]' ,'$RV[getMonth]/$RV[getDay]/$RV[getYear]'

 

then you should not experience any problems running:

 

exec Web_PPS_Login_Authentication_1 '$RV[getCRNUM]' ,'$RV[getMonth]/$RV[getDay]/$RV[getYear]'

 

Which module are you running this command from?

 

Could you please post a copy of VoiceGuide's Debug Printout which captures both calls, this will allow us to see what happened. When running the script click on VoiceGuide's View menu and select 'Event Trace Log' option - any log information will then appear in this window. You must open the trace window before making the call. You can enable the automatic saving of the logs to files in \log\ directory as well.

[corrigaj]

Will post the necessary debug files tomorrow when I'm back at work but for now I can tell you that I'm running them from a dbQuery module.

[corrigaj]

As requested, here is the debug file after running the SQL statement:

exec Web_PPS_Login_Authentication_1 '$RV[getCRNUM]' ,'$RV[getMonth]/$RV[getDay]/$RV[getYear]'

 

I hope you can help

debugFile.TXT

[SupportTeam]

It appears that running this stored procedure:

 

exec Web_PPS_Login_Authentication_1 '0000000' ,'12/12/1950'

 

results in some sort of an error returned by database itself. Have you tried running this procedure from some other apps/utilities to see if it works when called from other programs/tools?

 

This is probably more of a stored procedure or database engine related problem rather then a VoiceGuide one... Especially as the call to WEB_TELESAS_CHECKDOB is done fine...

[corrigaj]

I've just tried adding the line set nocount on to the stored procedure to eliminate the messages generated by SQL server and still no go.

 

Procedures runs fine from SQL Query Analyzer...have you tried running the procedure on your side to see if you get similar results?

 

I appreciate your time!

[SupportTeam]

Looking into why one stored procedure works while another one does not falls outside the scope of VoiceGuide software support.

 

You may want to try running them from a VBScript instead - it may give you better control over the call to the stored procedure and the results/errors returned, and you will also be able to test the call from outside VG before using the VBScript inside VG's "Run VBScript" module.