CODEWITHSUNDEEP
sqlstored-proceduressybasesap-ase
anthonybell
anthonybell

Reputation: 5998

Why does this sybase error go away with any change to the query?

A query I've been using for years has suddenly started throwing a weird error. When I made a cosmetic change to the query (1+x instead of x+1), the error no longer occurs. The stored proc it's complaining about doesn't even exist on the server anymore!

I'm very curious if anyone has any ideas about what the issue is and why this "change" fixes it?

The query before:

UPDATE SOME_DB..JOB_QUEUE
SET ERROR_COUNT = ERROR_COUNT + 1,
    JOB_START_TIME = '{1}'
WHERE JOB_ID = {0}

The query after:

UPDATE SOME_DB..JOB_QUEUE
SET ERROR_COUNT = 1 + ERROR_COUNT,
    JOB_START_TIME = '{1}'
WHERE JOB_ID = {0}

The error it throws:

Sybase.Data.AseClient.AseException: Procedure sp_net_dblatency expects parameter @heartbeat, which was not supplied.

I was wondering if anyone has any ideas what is going on here?

Edit. Here is the stored proc. 

create procedure sp_net_dblatency
    @heartbeat datetime
as
    update DATABASE_1234..LATENCY set START_UTC_TIME=@heartbeat, END_UTC_TIME=getutcdate() where DATABASE_NAME=db_name()
    if (@@ROWCOUNT = 0)
        insert DATABASE_1234..LATENCY (DATABASE_NAME, START_UTC_TIME, END_UTC_TIME) values (db_name(), @heartbeat, getutcdate())

Upvotes: 8

Views: 611

Answers (1)

Owen Johnson
Owen Johnson

Reputation: 2526

Sybase's optimizer may have cached a plan that references the deleted procedure erroneously. It pays attention to history and caches all sorts of statistics and generates plans based on lots of cached information. Occasionally, I have found that it can start to generate very bad plans on very large queries. I have encountered this a few times, and reported it as a bug to Sybase, but they were not able to reproduce it (and I haven't been able to reproduce it reliably either).

The workaround is to either change the query slightly or to add a plan statement to override the erroneous plans. Queries with PLAN statements will use the provided plan instead of generating one. Hope that helps.

Upvotes: 3

Related Questions