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

Question

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())

Maybe your sp was hacked and a @hearbeat parameter inserted into the code. Your simple fix caused it to be re-compliled. — Dan Bracuk, Jan 30 '15 at 17:12
The sp used to exist and @heartbeat was a parameter. But the sp has been deleted months ago. — anthonybell, Jan 30 '15 at 17:13
Were all the synonyms associated to the sp_netiq_dblatency removed as well? — xQbert, Jan 30 '15 at 17:33
I text searched the entire database (with qwerybuilder's search utility) and I did not find anything using the proc. — anthonybell, Jan 30 '15 at 17:36
Do you use any database source control tools similar to Red-gate? We've experiences problems in past where red-gate has found a mismatch and overwritten the dev database with a previous version from months earlier and caused havok with sp's. — Christian Barron, Feb 01 '15 at 22:04
If you're using C# (I'm assuming Visual Studio) have you checked that you're not holding a local version of the database in Visual Studio that is becoming out of sync with the database? If there is a local version, run a schema comparison to make sure it's up to date. — Christian Barron, Feb 03 '15 at 09:20
This is occurring on production. It was running fine on production for years and now this error starting occurring for all the services using it suddenly about a month ago. — anthonybell, Feb 03 '15 at 14:46
@anthonybell can you share the whole procedure code so that this could be reproduced? — dom, Feb 04 '15 at 19:46

score 3 · Answer 1 · answered Feb 05 '15 at 20:57

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.

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

1 Answers1