Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    29

    Question Unanswered: Proc weird behavior?

    Hi all,

    I have a procedure, based on some query & conditions which returns 1 or zero for given user_id. It was working perfectly fine for my user_id (which is returning 1), but when I executed this proc today, it failed (returning 0). I checked the data in the table. Every thing was intact. Finally I recreated the procedure, it started working fine. Do you guys know why this weird behavior?. ( I vaguely remember other couple of procedure started behaving like this few days ago. I put some debug statements and recreated, then it started working). Do I have to re-create the procs regularly? How many days once?

    Thanks

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    What version of ASE are you on? There shouldn't be anything goofy like that. Could you provide the SQL for the proc?
    Thanks,

    Matt

  3. #3
    Join Date
    Oct 2002
    Posts
    29
    Originally posted by MattR
    What version of ASE are you on? There shouldn't be anything goofy like that. Could you provide the SQL for the proc?
    I am using ASE 12.0.0.2.

    Here is the proc.

    create proc verify_user (@user_id char(08)) as
    begin
    declare @flag char(01)
    declare @version_name varchar(25)

    select @version_name = rtrim(program_name)
    from master..sysprocesses
    where spid = @@spid

    IF (@version_name = "APP 1.0")
    return 0

    select @version_name = "%"+rtrim(@version_name)+"%"


    IF exists(select * from version
    where version_name like @version_name
    and status_cd = 'A')
    BEGIN
    select @flag = u.flag
    from users u
    where u.user_id = @user_id

    if ( @flag = 'Y' )
    return 1
    else
    return 0
    END
    ELSE
    return 0

  4. #4
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    nmr,

    the procedure depends on how the client program logs into Sybase. When opening a connection, a client can pass a string tot he server, and this string later gets stored as "program_name". Your stored proc checks this string.

    Some clients use a hardwired string. For example the "isql" utility always passes a given name, and Artisan uses some other constant. In your case, you may have a client written in some other language, e.g. Java, Powerbuilder or Visual Basic. These languages can see the string, by passing a parameter usually called APPLICATION_NAME, or something like this, depending on the language.

    In a nut-shell: the random behaviour of your stored proc is probably caused by changes in the client code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •