Thursday, June 25, 2009

A simple Cursor sample

declare @mid int , @comid int
declare tmp_cur cursor for
select distinct memberid, communityid from _cleanup_dups order by memberid

open tmp_cur
Fetch next from tmp_cur into @mid, @comid

while @@fetch_status = 0
begin
print @mid
print @comid
Fetch next from tmp_cur into @mid, @comid
print '---'
end

close tmp_cur
deallocate tmp_cur

Wednesday, June 24, 2009

Sending scheduled SQL query results via HTML e-mail using SSIS

Sending email from SQL using sp_send_dbmail

declare @sub nvarchar(1000)
set @sub = 'Engage Daily Import Counts for: '+Convert(nvarchar(100),getdate(),101)

exec msdb.dbo.sp_send_dbmail
@Subject=@sub,
@query = '
set nocount on;
--select "Engage Import Results for: "+Convert(nvarchar(100),getdate(),101);
select count(*) as Users from PstgreSQL.dbo.users;
print "
";
select COUNT(*) as PostalCode from PstgreSQL.dbo.PostalCode;
print "
";
select COUNT(*) as Users_stage from PstgreSQL.dbo.Users_stage;
print "
";
select COUNT(*) as PostalCode_stage from PstgreSQL.dbo.PostalCode_stage;
',
@recipients = 'palexander@spark.net',
@body_format = 'HTML'


--select 'Import Results for: '+Convert(nvarchar(100),getdate(),101);

Tuesday, June 09, 2009

Find a Table or view within entire SQL server

EXEC master..sp_MSForEachDB '
IF EXISTS
(
SELECT 1
FROM [?].INFORMATION_SCHEMA.tables
WHERE table_NAME = ''vw_fctmemberdetail_ca''
)
BEGIN
SELECT ''[?]'';


SELECT ''['' +table_CATALOG + '']'', ''['' + table_NAME + '']''
FROM [?].INFORMATION_SCHEMA.tables
WHERE table_NAME = ''vw_fctmemberdetail_ca'';
END';