set nocount on in sql job - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

set nocount on in sql job

Author

Topic

riya.johnson
Starting Member

17 Posts

Posted - 2009-06-23 : 07:11:39

Hi,

I have created a job where in there are multiple sql queries in one step.
I have set nocount on

But the result shows "n rows affected" after each result.

Same when run on a query analyser, does not yield results with "n rows affected"
I am using sql 2000

Regrds

Riya Johnson

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 07:14:48

Please explain WHERE you see the result from the job execution.
In the log file?

E 12°55'05.63"
N 56°04'39.26"

riya.johnson
Starting Member

17 Posts

Posted - 2009-06-23 : 07:20:05

Yes, I am capturing it into a txt file on hard disk.

Riya Johnson

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 07:21:42

Why?
Are you also dependant on some PRINT statements, such as headers and so on?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 07:22:01

Post the code for further examination.

E 12°55'05.63"
N 56°04'39.26"

riya.johnson
Starting Member

17 Posts

Posted - 2009-06-23 : 07:25:49

select 'DATABASE HEALTH CHECK SCRIPT'
Declare @date datetime
SET @date = getdate()
SELECT 'SCRIPT EXECUTION STARTED AT ' + CAST(@date AS varchar(40))

set nocount on
set quoted_identifier off

select convert(char(40), serverproperty ('servername'))"Server Name"

select convert(char(40), serverproperty ('instancename'))"Instance Name"

select @@version "Edition & Service Pack"

select convert(char(40), serverproperty ('productversion')) "ProductVersion"

exec master..sp_databases
go

select 'Audit Level'
exec master..xp_loginconfig
go

select 'SQL Logins'
use master
SELECT loginname FROM syslogins

Riya Johnson

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 07:29:27

Works for me

set nocount on

select 'DATABASE HEALTH CHECK SCRIPT'
Declare @date datetime 
SET @date = getdate()
SELECT 'SCRIPT EXECUTION STARTED AT ' + CAST(@date AS varchar(40))

select convert(char(40), serverproperty ('servername')) AS [Server Name]

select coalesce(convert(char(40), serverproperty ('instancename')), '<default instance>') AS [Instance Name]

select @@version "Edition & Service Pack"

select convert(char(40), serverproperty ('productversion')) AS ProductVersion

exec master..sp_databases

select 'Audit Level'
exec master..xp_loginconfig

select 'SQL Logins'
SELECT loginname FROM master..syslogins

E 12°55'05.63"
N 56°04'39.26"

riya.johnson
Starting Member

17 Posts

Posted - 2009-06-23 : 07:41:52

Are you running it using job?

It works fine in query analyser but not through sql job...

Riya Johnson

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 08:40:49

Works for me.
Did you notice that I changed place for "SET NOCOUNT ON"?

E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-06-23 : 08:44:22

moved from script library.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources