[SQLSTATE 01003] annoying. - 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 2005 Forums

Transact-SQL (2005)

[SQLSTATE 01003] annoying.

Author

Topic

robertleftwich
Starting Member

2 Posts

Posted - 2008-12-08 : 20:08:53

This is driving me nuts. It happens when things are busy. I've used the (Nolock) hint but to no avail. This is a part of as scheduled script to monitor all database backups across 18 SQL servers. Any one got any clues?
Rob

Job 'SQL Monitor' : Step 2, 'SQL Monitor Step 2 last sql backup' : Began Executing 2008-12-09 11:00:00

Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003

The job.

if exists (select * from tempdb..sysobjects where id = object_id(N'[tempdb]..[#tmp_last_sql_backup]'))
drop table #tmp_last_sql_backup

create table #tmp_last_sql_backup
(
[ID] INT IDENTITY (1, 1),
DataBaseName varchar(60),
ServerName varchar(60),
Last_Backup_Date varchar(120),
Days_Since_Last_Backup varchar(60)
)

use msdb

insert into #tmp_last_sql_backup select t1.name as 'DataBaseName',
@@servername as [ServerName],
case
when max(t2.backup_start_date) is not null then max(t2.backup_start_date)
else '19000101'
end as [Last_Backup_Date],
case
when datediff(day, max(t2.backup_start_date),getdate()) is not null
then datediff(day, max(t2.backup_start_date),getdate())
else 9999
end as [Days_Since_Last_Backup]
from master..sysdatabases as t1 with (nolock) LEFT JOIN msdb..backupset as t2 with (nolock)
on t1.name = t2.database_name
and t2.type = 'd'
where t1.name not in (select name from isshlpdsk.SizeLog.dbo.DatabasesToExclude)
group by t1.name

insert into isshlpdsk.sizelog.dbo.last_sql_backup
(DataBaseName,ServerName,Last_Backup_Date,Days_Since_Last_Backup)
SELECT a.DatabaseName, a.Servername, a.last_backup_date, a.days_since_last_backup
FROM [#tmp_last_sql_backup] AS a left OUTER JOIN
isshlpdsk.sizelog.dbo.last_sql_backup AS b ON
(a.DataBaseName = b.DataBaseName
AND a.ServerName = b.ServerName
AND a.Last_Backup_Date = b.Last_Backup_Date
)
where b.servername is null

DECLARE @Counter INT, @Rows INT, @dbName varchar(128), @days varchar(8), @servr varchar(64)
--set @dbName
SELECT @Counter = 1, @Rows = COUNT(*) FROM #tmp_last_sql_backup

WHILE @Counter <= @Rows
BEGIN
select @dbName = a.DataBaseName, @days = a.days_since_last_backup,
@servr = a.Servername from [#tmp_last_sql_backup] AS a
where a.ID = @Counter

update isshlpdsk.sizelog.dbo.last_sql_backup
set days_since_last_backup = @days
where DataBaseName =@dbName and
Servername = @servr

SET @Counter = @Counter + 1
END

Robert Leftwich

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-08 : 22:53:50

This is just a warning. You can turn them off (by using ansi_warnings off) or better yet deal directly with the null values by either excluding them or defaulting them to 0, etc.

* setting the warnings off and evaluating the nulls can skew the results of aggregates (avg, etc.).

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources