Don't Use BETWEEN with DATETIME

I'm not going to try to convince you in English not to use BETWEEN with dates. Run the code below to see why it's not a good idea. (Yes, I know there are new date types - this post isn't for you.)

-- Never use BETWEEN with dates.  Just use >= and < . 

-- Drop temp table. 
if object_id('tempdb..#MyDates') is not null  drop table #MyDates

-- Create a table of edge-case dates to demonstrate the issue. 
create table #MyDates ( MyDate datetime not null)
insert #MyDates (MyDate) values ('2015-10-31 23:59:59.997')     -- October 
insert #MyDates (MyDate) values ('2015-11-01 00:00:00.000')     -- November 
insert #MyDates (MyDate) values ('2015-11-01 00:00:00.003')     -- November 
insert #MyDates (MyDate) values ('2015-11-29 23:59:59.997')     -- November 
insert #MyDates (MyDate) values ('2015-11-30 00:00:00.000')     -- November 
insert #MyDates (MyDate) values ('2015-11-30 00:00:00.003')     -- November 
insert #MyDates (MyDate) values ('2015-11-30 23:59:59.997')     -- November 
insert #MyDates (MyDate) values ('2015-12-01 00:00:00.000')     -- December 

-- (1)  Now let's get all the November dates using BETWEEN. 
-- We want to get 6 rows back, but this returns 4!  Notice '2015-11-30 00:00:00.003' and 
-- '2015-11-30 23:59:59.997' are missing from the results. 
select MyDate as '(1)' from #MyDates where MyDate between '2015-11-01' and '2015-11-30' order by MyDate   -- This is WRONG - do not use! 

-- (2)  Okay, that didn't work, so let's try extending out an extra day. 
-- We want to get 6 rows back, but this returns 7!  Notice '2015-12-01 00:00:00.000' is 
-- included in the results, but we only want November. 
select MyDate as '(2)' from #MyDates where MyDate between '2015-11-01' and '2015-12-01' order by MyDate   -- This is WRONG - do not use! 

-- (3)  Hmmm, let't try going up to the last second of the last day of November. 
-- We want to get 6 rows back, but this returns 5!  Notice '2015-11-30 23:59:59.997' 
-- is missing from the results, because it occurs *inside* that last second of November. 
select MyDate as '(3)' from #MyDates where MyDate between '2015-11-01' and '2015-11-30 23:59:59' order by MyDate   -- This is WRONG - do not use! 

-- (4)  So how *does* one use BETWEEN with dates correctly?  This is the only way. 
-- (Why 997?  Google for "sql server datetime 997" for more info.) 
select MyDate as '(4)' from #MyDates where MyDate between '2015-11-01' and '2015-11-30 23:59:59.997' order by MyDate   -- This is CORRECT! 

-- (5)  But who wants to type all that out?  Instead of using BETWEEN, do it this way. 
select MyDate as '(5)' from #MyDates where MyDate >= '2015-11-01' and MyDate < '2015-12-01' order by MyDate   -- This is CORRECT! 

-- Drop temp table. 
if object_id('tempdb..#MyDates') is not null  drop table #MyDates

OPENQUERY - Linked Server error "Deferred prepare could not be completed"

If you're using OPENQUERY and get this error:

OLE DB provider "SQLNCLI10" for linked server "MYREMOTESERVER" returned message "Deferred prepare could not be completed.".
Msg 916, Level 14, State 1, Line 1
The server principal "linkedServer" is not able to access the database "MyDatabase" under the current security context.

Check to be sure the SID of the login is the same as the SID of the database's user:

-- When copying a database from another server, you will need to run this 
-- if the instance already has a login with the same name (because the SIDs 
-- will be different because they came from different instances). 

-- SELECT DATABASE !!!  
use MyDatabase

-- Use this to get instance login sid.  
select SUSER_SID('linkedServer')

-- Display a list of users in current database and their User SID. 
exec sp_change_users_login @Action='report'

-- Set database user SID to corresponding instance login SID.  
alter user linkedServer with login = linkedServer

Finding All Databases Where User is not in Role

This script will find all the databases (matching a certain pattern) on the server where a given user is not a member of a given role. Because sometimes you need to know that.

-- Be sure to change the User and Role variables. 
declare @Sql      nvarchar(4000)    set @Sql = ''
declare @UserName sysname           set @UserName = 'Larry'
declare @RoleName sysname           set @RoleName = 'db_owner'

set @Sql = 'USE [?]

            IF ''?'' LIKE ''MYDATABASE%''
            BEGIN
                IF NOT EXISTS (SELECT * 
                                 FROM sys.database_role_members
                                WHERE USER_NAME(role_principal_id)   = ''' + @RoleName + '''
                                  AND USER_NAME(member_principal_id) = ''' + @UserName + ''')
                BEGIN
                    RAISERROR(''Database %s - user %s not in role %s'', 10, 1, ''?'',
                              ''' + @UserName + ''', ''' + @RoleName + ''') WITH NOWAIT
                END
            END'

exec sp_msforeachdb @Sql

Load all TRC Trace Files in a Folder into a Single Trace Table

This script will load all the trace (*.TRC) files in a given folder into a single trace table. You have to specify the database to create the trace table in, the folder the trace files are in, and the filename prefix (if any). Be careful: trace files can take up a lot of space in a hurry when imported into a table.

-- Load all TRC files in a given folder into a single trace table. 
set nocount on

-- Set these values. 
use MyMaintenanceDatabase
declare @TrcFolderName nvarchar(260)      set @TrcFolderName = 'C:\SQL_Trace\'
declare @TrcFilePrefix nvarchar(260)      set @TrcFilePrefix = 'MySpecificFilenamePrefixIfAny-'

-- Load the TRC filenames into temp table. 
declare @DosCmd nvarchar(4000)
set @DosCmd = 'dir /b ' + @TrcFolderName + @TrcFilePrefix + '*.trc'

if object_id('tempdb..#TrcFiles') is not null
    drop table #TrcFiles

create table #TrcFiles (TrcFileName nvarchar(260))

insert #TrcFiles
    exec master..xp_cmdshell @DosCmd 

delete #TrcFiles
 where TrcFileName is null

-- Iterate the trace files and read them into the trace table. 
declare @TrcFileName  nvarchar(260)
declare @TrcPathName  nvarchar(260)
declare @RowsAffected int

if object_id('dbo.trace_table') is not null
    drop table dbo.trace_table

declare cur cursor for
    select TrcFileName from #TrcFiles 
     
open cur
fetch next from cur into @TrcFileName

while @@fetch_status = 0
begin
    set @TrcPathName = @TrcFolderName + @TrcFileName
    raiserror('%s', 10, 1, @TrcPathName) with nowait

    if object_id('dbo.trace_table') is null
        select * into dbo.trace_table from ::fn_trace_gettable (@TrcPathName, /* number_files */ 1)
    else
        insert dbo.trace_table
            select * from  ::fn_trace_gettable (@TrcPathName, /* number_files */ 1)

    set @RowsAffected = @@rowcount
    raiserror('   %9d rows inserted', 10, 1, @RowsAffected) with nowait
    
    fetch next from cur into @TrcFileName
end

close cur
deallocate cur

-- Quirk in trace: update NULL DatabaseName's from their ID's. 
update dbo.trace_table
   set DatabaseName = db_name(DatabaseID)
 where isnull(DatabaseName, '')  = ''

set @RowsAffected = @@rowcount
raiserror('   %9d NULL DatabaseNames updated from their IDs', 10, 1, @RowsAffected) with nowait

-- Example cleanup: delete all rows that don't show their database. 
delete dbo.trace_table
 where DatabaseName is null

set @RowsAffected = @@rowcount
raiserror('   %9d NULL DatabaseNames deleted', 10, 1, @RowsAffected) with nowait

-- Example results: find unique logins per database per day. 
  select dateadd(dd, 0, datediff(dd, 0, StartTime))   as 'LoginDate'
       , LoginName
       , DatabaseName
    from dbo.trace_table
group by dateadd(dd, 0, datediff(dd, 0, StartTime))
       , LoginName
       , DatabaseName
order by LoginDate desc
       , LoginName
       , DatabaseName