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