Astronomical Calculations in SQL - Full Moon on Halloween

My daughter came to me yesterday and asked: Do you know that Full Moon on Halloween happens once in 800 years?

I said: - It's impossible!!!
- Google it! - replied she.

But think I'm better astronomer than Google and can calculate it by myself.

I've started from the time Moon needs to circle the Earth. That is 29.530588853 days.
Than I took the time of the next Full Moon, which is 2014-10-08 10:52am UTC and wrote SQL query:

DECLARE @Period datetime2 =       '2014-10-08 10:52am'; DECLARE @Days INT = 29; DECLARE @Minutes Numeric(38,9) = 764.047948320; ;with ctr as (     SELECT CAST(        ROW_NUMBER()        over(order by message_id)-24907 as NUMERIC        ) as rn     FROM sys.messages ), FM AS (     SELECT DATEADD(        DAY,@Days*rn,        DATEADD(MINUTE,@Minutes*rn,@Period)        ) as Period_Dt     FROM ctr) SELECT TOP 348 Period_Dt FROM FM WHERE Month(Period_Dt) = 10     and DAY(Period_Dt) = 31;

That query should work in SQL 2008R2 and higher.

Accordingly to my calculations the next Full Moon on Halloween will be in 2020.

By eliminating the WHERE and TOP clauses you can get just Full Moon timing for the last 2000 years and as far as SQL Server will allow you.

My first MS SQL Server Bug Report

This morning filed my first SQL Bug report: https://connect.microsoft.com/SQLServer/feedbackdetail/view/973290/temporary-stored-procedures-have-execution-context-of-these-databases-where-they-were-created

Here is the problem:  
Temporary Stored Procedures have execution context of these databases where they were created.


What actually happens:
Temporary Stored Procedures, when created, remember the database, which was current at the time of their creation and use that database as current during the execution of select statements from system tables.

In other words: if you want to get list of database objects within temporary Stored Procedure you can do it only from specified database or from the database where that procedure was created.

Not sure if I explained it clearly, but here is a simple example:

USE msdb; GO CREATE TABLE tbl_VeryTestTable_123456789ABCDE(ID INT IDENTITY(1,1), A VARCHAR(10)); GO CREATE PROCEDURE #SP_VeryTemporaryProcedure_123456789ABCDE AS SELECT DB_NAME() as 'Current Database', 'Temp SP' as 'Output Source'; SELECT 'Temp SP' as 'Output Source', name FROM sys.objects WHERE name = 'tbl_VeryTestTable_123456789ABCDE'; RETURN; GO USE Master; GO EXEC #SP_VeryTemporaryProcedure_123456789ABCDE; GO USE msdb; GO DROP TABLE tbl_VeryTestTable_123456789ABCDE; GO DROP PROCEDURE #SP_VeryTemporaryProcedure_123456789ABCDE; GO

What that script does:
1. Makes "msdb" database as "current".
2. Create a table in "current" "msdb" database.
3. Create temporary Stored Procedure which returns name of the "current" database and searches for just created table (in step #2) within the current database context.
4. Change current database context to "Master" database.
5. Run the new temporary Stored Procedure.
    As a result you get two output data sets:
    A. Current database: "Master"
    B. The table will be found in "current database context"!!!!! 
6. 7. 8. Return to msdb DB and cleanup after yourself.

How did I find this:
I've tried to create a temporary Stored Procedure which would return list of objects from "current" database it worked perfectly until I changed the current database. Being in different database confused me, I couldn't see objects I expected to see there and couldn't find objects which were listed by the
Stored Procedure.

I assume that bug is not very annoying (who use temp SPs at all???)  and I do not expect MS to fix it any time soon. At least we have to live with it in SQL 2012 and 2014.

So, the simple workaround would be to use dynamic SQL. 
Here is an example how to do this:

CREATE PROCEDURE #SP_VeryTemporaryProcedure_123456789ABCDE AS DECLARE @SQL VARCHAR(1000); SELECT DB_NAME() as 'Current Database', 'Temp SP' as 'Output Source'; SELECT 'Temp SP' as 'Output Source', name FROM sys.objects WHERE name = 'tbl_VeryTestTable_123456789ABCDE'; SELECT @SQL = ' SELECT ''Temp SP against "'     + DB_NAME() + '" database.''  as ''Output Source'', name FROM ['     + DB_NAME() + '].sys.objects WHERE name = ''tbl_VeryTestTable_123456789ABCDE'';'; EXEC (@SQL); RETURN; GO

This Stored Procedure will query really Current database.

If you have any other Ideas please let me know.