October 3, 2006 at 9:49 am
Hi,
I am trying to run a program which goes through a list of user objects in a sql server db and count for number of rows. If rows = 0, raise alarm.
Logic used:
1. Declare cursor for Get all name from sysobjects where type = 'U'
2. Loop through the cursor and check for number of rows
3. If rows = 0, set the flag to 1.
4. If flag =1, fail the job.
The coding is as follows:
declare hlab_cursor cursor for
select name from sysobjects where xtype='U'
order by name
declare @mycnt int;
declare @myname varchar(64);
declare @mysql varchar(300);
open hlab_cursor
fetch next from hlab_cursor into @myname
while @@FETCH_STATUS = 0
begin
print @myname
set @mysql = 'select count(*) from ' + @myname
select @mycnt = count(*) from @myane
set @mycnt = execute(@mysql)
print @mycnt
fetch next from hlab_cursor into @myname
end
close hlab_cursor
deallocate hlab_cursor
go
How do I get the rowcount from sql to check for 0 rows?
Thx,
Murali
October 3, 2006 at 10:04 am
This is the easy way:
exec
sp_MSforeachtable ' select ''?'',count(*) as NumberOfrecords from ?'
if you want you can insert results into the temp table and select from it for the tables with 0 row count:
Create
table #TempTable (TableName varchar (100),NumberOfrecords int)
Insert
#TempTable
exec
sp_MSforeachtable ' select ''?'',count(*) as NumberOfrecords from ?'
Select
* from #TempTable where NumberOfrecords < 1
Drop
Table #TempTable
Regards,Yelena Varsha
October 3, 2006 at 10:04 am
Change
set @mysql = 'select count(*) from ' + @myname
select @mycnt = count(*) from @myane
set @mycnt = execute(@mysql)
to
set @mysql = 'select @mycnt=count(*) from ' + @myname
exec sp_executesql @mysql, N'@mycnt int OUTPUT', @mycnt OUTPUT
Far away is close at hand in the images of elsewhere.
Anon.
October 3, 2006 at 10:20 am
you could do something like
select object_name(id), rows from sysindexes sjoin mytable mon object_id(m.name) = s.idwhere indid = 1and rows = 0October 3, 2006 at 10:25 am
Thx guys for the update.
We have a nightly DTS job which populates data from Oracle to SQL server.
It was designed to delete all contents on the sql tables before inserting data onto it.
Problem happened when the first part deleted all rows on the sql server tables and the data transfer from oracle failed due an ODBC error. There was no data on this sql server tables for almost 3 hours and we could not catch it (This db is viewed online continously).
My plan is to have a job run this script on the sql side and check for all user objects having 0 rows. Iam planning to have a @myflg set initially to 0 and check for 0s on all tables. If I run into any table with 0 rows, I will update @myflg to 1 and fail the job. If the job fails, I will be automatically notified which helps me to attend to it.
The second plan is to rewrite dts so that to copy all sql server data onto tmp tables before deleting it, transfer data from oracle to sql. If any of the above process fails, restore data from tmp back to sql server tables. So sql server data would always be available (if refreshed new data, if not previous data).
What do you think?
Thx
Murali
October 3, 2006 at 10:46 am
Thx David,
Your suggestion does work. Appreciate your help.
Murali
October 3, 2006 at 2:05 pm
Murali,
If you have many tables where data have to be replaced nightly then maybe you may have a second database same as the production one, import data there, count rows, do other validation and then when you are ready, backup the second database and restore over the production. It could be easily done by script.
Regards,Yelena Varsha
October 4, 2006 at 7:36 am
DECLARE
@MyResult int;
SET
@MyResult =
(
SELECT count(rowcnt)
FROM
sysindexes
WHERE
indid IN (1,0) AND OBJECTPROPERTY(id, 'IsUserTable') = 1 and rowcnt >0)
@MyResult
October 4, 2006 at 7:53 am
Thank you all for your inputs. Much appreciated.
Murali
October 4, 2006 at 9:42 am
If the thing fails then you just drop the new tables and wait till next time.
ATBCharles Kincaid
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply