| 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 meset 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! |
|
|
|
|
set nocount on in sql job