Automated Trace File Collection

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime

set @DateTime = '2009-07-10 21:00:00.000'
set @DateTime = dateadd(mi,2,GETDATE())
--select @DateTime
set @maxfilesize = 20

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
declare @filename nvarchar(100)
set @filename = N'C:\SQL_PROFILER\SQL01-mnmember1'
exec @rc = sp_trace_create @TraceID output, 0, @filename, @maxfilesize, @Datetime
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 7
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - de04e276-c5f5-4a14-a12c-d8f42f0c5a8b'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go


/*
SELECT * FROM :: fn_trace_getinfo(default)


-- Set the trace status to start
exec sp_trace_setstatus 2, 1
-- stops the trace
exec sp_trace_setstatus 2, 0
-- closes the trace and deletes its definition from the server
WAITFOR DELAY '00:00:30'
exec sp_trace_setstatus 2, 2

*/

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

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);

Automating Performance Monitor Statistics Collection for SQL Server and Windows

Saving and Retrieving File Using FileStream SQL Server 2008

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';

Rob's SQL Server Blog

Rob's SQL Server Blog


Helpful Blog

Performance Dashboard Reports for SQL Server 2008

Performance Dashboard Reports for SQL Server 2008

Reindex Tables

--sp_helpindex 'jm_wo_transaction'
declare @tblname nvarchar(100),@sqlstring nvarchar(2000)
set @tblname = 'memberflat'

IF OBJECT_ID('tempdb..#tmpindexes') IS NOT NULL
drop table #tmpindexes

IF OBJECT_ID('tempdb..#tmptableindexes') IS NOT NULL
drop table #tmptableindexes

create table #tmptableindexes
(index_name nvarchar(200), index_description nvarchar(1000), index_keys nvarchar(1000), table_name nvarchar(100))
create table #tmpindexes
(index_name nvarchar(200), index_description nvarchar(1000), index_keys nvarchar(1000))


--select * from information_schema.tables where table_type = 'BASE TABLE'
--DECLARE tmptable_name CURSOR FOR select table_name from information_schema.tables where table_type = 'BASE TABLE' and table_schema = 'dbo'
DECLARE tmptable_name CURSOR FOR select table_name from information_schema.tables where table_type = 'BASE TABLE' and table_schema = 'dbo' and table_name=@tblname
OPEN tmptable_name

FETCH NEXT FROM tmptable_name INTO @tblname

WHILE @@FETCH_STATUS = 0
BEGIN
print 'print '''+@tblname+''''
print 'go'
set @sqlstring = 'DBCC DBREINDEX (['+@tblname+'],'''',90)'
print @sqlstring
--exec sp_executesql @sqlstring
print 'go'
FETCH NEXT FROM tmptable_name INTO @tblname
end

CLOSE tmptable_name
DEALLOCATE tmptable_name