SQL Job Problem -SET NOCOUNT ON

[SQLBill]

I have two different SQL Server 2000 servers. Both are running the same version of SQL Server. I have the same jobs on both of them. All the jobs begin with SET NOCOUNT ON. The jobs on one server (call it SQLA) run fine and return the results without the rowcount at the end. The jobs on the other server (call it SQLB) run fine, except they return the results WITH the rowcount at the end. As I said, it's the same jobs on both machines - I copied them from one (SQLA where they work) to the other (SQLB where they keep giving the rowcount).

By the way, by rowcount I mean that line that says:
(10 row(s) affected)

This is really frustrating me......I checked the queries for typos, but there aren't any - SET NOCOUNT ON is pretty easy to type.

One other thing, if I run the query in QA I don't get the rowcount when I use SET NOCOUNT ON.

-SQLBill

 

[osjohnm]

Hi SQLBill

Did you create the job in EM or did you script it through Query Analyser?

This is a long shot but I know that often EM doesn't always set certain options on. Basically if you create a stored procedure or view that accesses a linked object it won't work in EM, you have to use SET_ANSI_NULLS ON, SET_ANSI_WARNINGS ON in query analyser before you execute your create statement.

Your scenario doesn't use any linked objects but perhaps it worth a try to create the job in query analyser unless u did in the first place to see if that makes a difference.

Hope that this might help you out, good luck this is a strange one.

John

 

[SQLBill]

I originally wrote it via Enterprise Manager as a job. And it works on one server but not the other. The job was written the same way, using the same methods both times. On one server it works - on the other it doesn't. When it didn't work properly on my second server, I ran it via Query Analyzer and it worked just fine. It's driving me buggy - I just can't figure out why SET NOCOUNT ON isn't working on my jobs, but it will work in QA.

-SQLBill

 

[osjohnm]

Hi SQLBill

So it seems that sql server is ignoring the set nocount command on server 'B' but executes the rest of the sql statement without a problem?...weird!

Have your tried stopping and starting the SQL services and re-running the job?

Perhaps if you restart the actual server and try and run the job it might fix itself or maybe drop the job, restart the services and re-create the job or reboot and re-create the job.

I know thats a long shot but maybe windows or sql has cached something or something to that effect is preventing sql server from executing set nococunt on.

Maybe try and create a completely different job that also uses set nocount and see if that works.

Keep me posted

John

 

[SQLBill]

I've already restarted it, no luck. I'll try the reboot and see what that does. It happens with all the jobs on the second server.

-SQLBill

 

[SQLBill]

Rebooted the server, still not working properly. The jobs still return the number of rows that are affected. But the same query run in QA doesn't give me the number of rows affected. This has me stumped.

-SQLBill

 

[osjohnm]

In that case I'm assuming that the problem is maybe in EM.
You have tried all the possible causes and that hasn't fixed it.

All that I that can think of is to install the SQL Service Pack again.

One last test, how about putting the job's syntax into a stored procedure which has set nocount on and let the job execute that instead?

Or post your code and then I will try and replicate it on one of my servers and try and get the same error.

John

 

[SQLBill]

John,

I'm getting ready to call Microsoft about this problem, but I might try reloading EM first.

I have several scripts, but I've been using a simple one to test this issue. (The simpler the better for the test).


set nocount on
select getdate()


returns

------------------------------
2002-09-23 16:04:53.003

(1 row(s) affected)


-SQLBill

 

[MeanGreen]

SQLBill,
I don't know if this will help, but Microsoft has documented a similar issue as a bug in SQL 2000. Here is the link:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q294412&

Hope it helps.

 

[SQLBill]

MeanGreen,

Thanks for the link. That doesn't explain the problem, but it shows me that it's a MS problem that I have to live with. Oh well, that's the way these things go. I'm just glad that it's not affecting both of my servers. I was considering reinstalling Enterprise Manager, but I'm at a point where that would not be quite a good thing.

Again, thanks for the link.

-SQLBill