SQL Jobs failing with Token Error but code works fine in Query Analyser

[Brett Levin]

Hi,

I am experiencing the strangest error. Every job is failing on the SQL
Server Agent engine but the sprocs that the jobs execute work fine from
Query Analyser. The jobs seem to hang and then error after a few minutes
with the following error code:

Unknown token received from SQL Server [SQLSTATE HY000] (Error 0)

As I said, the same sprocs do not yield the same error from Query
Analyser. These jobs have been running for some time and I have never
seen this error. Most of these sprocs do rely on cursor fetching and
record updates.

I would greatly appreciate any advice or assistance.

Thank you kindly,
Brett


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jacco Schalkwijk]

Hi Brett,

This might be an issue with Service Pack 3, and more specifically with MDAC
2.7 that comes with it. Did the problem start after you installed the
Service Pack? In that case it should be fixed in the new version of MDAC
that comes with Service Pack 3a.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

Post by Brett Levin
Hi,
I am experiencing the strangest error. Every job is failing on the SQL
Server Agent engine but the sprocs that the jobs execute work fine from
Query Analyser. The jobs seem to hang and then error after a few minutes
Unknown token received from SQL Server [SQLSTATE HY000] (Error 0)
As I said, the same sprocs do not yield the same error from Query
Analyser. These jobs have been running for some time and I have never
seen this error. Most of these sprocs do rely on cursor fetching and
record updates.
I would greatly appreciate any advice or assistance.
Thank you kindly,
Brett
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Brett Levin]

Hi Jaco

Thanks so much for your help. The funny thing is that I am still running
SP2 and havent installed SP3 at all, it just seems as though overnight
something has caused complete chaos.

The funny thing is that if I run the sprocs from QA, they work fine. It
also seems that only jobs with Cursors are being affected by this �
these jobs are fairly old so it baffles me as to why all of a sudden
these errors are appearing. Should I try and play around with the
different type of cursors? They are primarily used to fetch a few
fields, store a few select statements in variables and then update the
record before fetching next.

Thanks again for your help.

Regards,
Brett




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jacco Schalkwijk]

Hi Brett,

A new version of MDAC might have been installed on your server by a
different application or an OS upgrade. Jobs in SQL Server are run
externally from SQL Server itself by the SQL Server Agent and access SQL
Server via the SQL OLEDB provider. It is this driver that has a bug in MDAC
version 2.71, the version that is included with SQL Server 2000 Service Pack
3, but probably also with other Service Packs or hotfixes for Windows or
other applications. The following article describes how you can check for
the current version of MDAC on your server:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;301202

In almost all cases you don't need to use cursors. If you post a typical
(not too large)example of your cursors I can help you to rewrite it as a
set-based solution, which will not only not be affected by this problem, but
will also perform better.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

Post by Brett Levin
Hi Jaco
Thanks so much for your help. The funny thing is that I am still running
SP2 and havent installed SP3 at all, it just seems as though overnight
something has caused complete chaos.
The funny thing is that if I run the sprocs from QA, they work fine. It
also seems that only jobs with Cursors are being affected by this -
these jobs are fairly old so it baffles me as to why all of a sudden
these errors are appearing. Should I try and play around with the
different type of cursors? They are primarily used to fetch a few
fields, store a few select statements in variables and then update the
record before fetching next.
Thanks again for your help.
Regards,
Brett
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jacco Schalkwijk]

Hi Brett,

You can replace the cursor with the (untested) code below:

UPDATE dbWtech.dbo.tblprofile
SET Purchases = (SELECT isnull(sum(PchTotal),0)
from dbWintech.dbo.tblPchInfo pi
where pi.userid = dbWtech.dbo.tblprofile.userid)

A quick fix might be the rollback the MDAC installation, see the article
below:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/data_mdacinstall.asp

MDAC version 2.8 has been released recently and this might include a fix for
this bug, but it looks like there is no release manifest as yet, so I
couldn't check if it is actually included. As it is a bug, you can contact
Microsoft Product Support Services for information and you will not be
charged for the call.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.

Hi Jaco,
come to think of it - I did run a Windows Update yesterday and that
could be the source of this all. It sounds definately like a MDAC error
because its only occuring on SQL Server Agent (Jobs). Could the cursors
alone be causing this?
Regarding cursors, this could be vital for me - I have probably 20-30
jobs running over 24 hrs most of which use cursors (some include 1000's
of rows). I have attached an example, please let me know if I should
change things around. Below is a very simple script which is a snippet
of how most of my algorithms work - I would appreciate any help/advice.
Feel free to be critical!
declare cursor1 cursor for select userid
from dbWtech.dbo.tblprofile
open cursor1
begin
from dbWintech.dbo.tblPchInfo
UPDATE dbWtech.dbo.tblprofile
end
close cursor1
deallocate cursor1
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!