Search results matching tags 'T-SQL', 'SQL', 'SQL 2008', and 'SQL Server 2008'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=T-SQL,SQL,SQL+2008,SQL+Server+2008&orTags=0Search results matching tags 'T-SQL', 'SQL', 'SQL 2008', and 'SQL Server 2008'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Calculating Holidays in SQLhttp://sqlblog.com/blogs/michael_coles/archive/2010/02/25/calculating-holidays-in-sql.aspxThu, 25 Feb 2010 21:51:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:22626Mike C<P>Ask about&nbsp;nearly any kind of SQL-based date calculation in the <A title="SQL Public Newsgroup" href="http://groups.google.com/group/microsoft.public.sqlserver.programming/topics?hl=en">newsgroups</A>, and you'll likely get responses that include use of an <A title="Auxiliary Calendar Table @ ASPFAQ" href="http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html">auxiliary calendar table</A>.&nbsp; It's a really good idea, and something I highly recommend to anyone who has to do date-based calculations on the server.&nbsp; For those who do dimensional modeling, you'll probably notice the auxiliary calendar table closely resembles a Time dimension with one-day intervals.&nbsp; I won't get into too much detail on it, since ASPFAQ and the newsgroups cover it well.</P>
<P>What I do want to talk about is calculating holidays, which can be important when populating your auxiliary calendar table.&nbsp; When you create an auxiliary calendar table you may need to perform calculations based on business days, for instance; or you may need to schedule activities before, after, or even during certain holidays.&nbsp; There are two types of holidays that I'll address:</P>
<UL>
<LI>Fixed holidays that fall on the same day every year</LI>
<LI>Floating holidays for which the date can change from year to year</LI></UL>
<P>Fixed holidays include holidays that always fall on the same day each year.&nbsp; Some examples of fixed holidays include <A title="Merry Christmas!" href="http://en.wikipedia.org/wiki/Christmas">Christmas</A> (always December 25 in the West), <A title="Oh, Canada!" href="http://en.wikipedia.org/wiki/Canada_Day">Canada Day</A> (always July 1), <A title="July 4th!" href="http://en.wikipedia.org/wiki/Independence_Day_(United_States)">U.S. Independence Day</A> (always July 4).&nbsp; These are fairly easy to detect and set in your auxiliary calendar table.&nbsp; Just look for the static month + day combinations in your table and update it accordingly.</P>
<P>Floating holidays are far more interesting and difficult to calculate.&nbsp; Take Easter.&nbsp; Easter actually has two definitions.&nbsp; The popular definition is:&nbsp;</P>
<UL>
<LI>"Easter Day is the first Sunday after the full moon that occurs next after the vernal equinox"</LI></UL>
<P>This&nbsp;is not the exact ecclesiastical definition though.&nbsp; The full moon here is not the astronomical full moon but an "ecclesiastical moon" that's determined&nbsp;through precalculated lookup tables.&nbsp; The ecclesiastical rules are:</P>
<UL>
<LI>Easter falls on the first Sunday following the first ecclesiastical full moon that occurs on or after the day of the vernal equinox</LI>
<LI>This particular ecclesiastical full moon is the 14th day of a tabular lunation (new moon)</LI>
<LI>The vernal equinox is fixed as March 21</LI></UL>
<P>I'm not going to recount the long and painful history leading to the modern definition of Easter--but the details&nbsp;can be found on <A title="Easter Controversy" href="http://en.wikipedia.org/wiki/Easter_controversy">Wikipedia</A>&nbsp;if you're interested.&nbsp; The main point is that Easter calculation is fairly complex.&nbsp; The udf_CalculateEaster function below accepts the year (integer) as a parameter and returns the date of Easter for that year (datetime).&nbsp; Notice the large number of calculations required to make this function work:</P>
<P><STRONG>CREATE FUNCTION dbo.udf_CalculateEaster <BR>(<BR></STRONG><STRONG>&nbsp; @Year INT<BR>)<BR>RETURNS DATETIME<BR>AS<BR>BEGIN<BR>&nbsp;&nbsp;&nbsp; DECLARE @Date DATETIME,<BR>&nbsp;&nbsp;&nbsp; @c INT, <BR>&nbsp;&nbsp;&nbsp; @n INT, <BR>&nbsp;&nbsp;&nbsp; @i INT, <BR>&nbsp;&nbsp;&nbsp; @k INT, <BR>&nbsp;&nbsp;&nbsp; @j INT, <BR>&nbsp;&nbsp;&nbsp; @l INT, <BR>&nbsp;&nbsp;&nbsp; @m INT, <BR>&nbsp;&nbsp;&nbsp; @d INT;</STRONG></P>
<P><STRONG>&nbsp;&nbsp;&nbsp; SELECT @n = @Year - 19 * (@Year / 19),<BR>&nbsp;&nbsp;&nbsp; @c = @Year / 100,<BR>&nbsp;&nbsp;&nbsp; @k = (@c - 17) / 25,<BR>&nbsp;&nbsp;&nbsp; @i = @c - @c / 4 - (@c - @k) / 3 + 19 * @n + 15,<BR>&nbsp;&nbsp;&nbsp; @i = @i - 30 * (@i / 30),<BR>&nbsp;&nbsp;&nbsp; @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11)),<BR>&nbsp;&nbsp;&nbsp; @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4,<BR>&nbsp;&nbsp;&nbsp; @j = @j - 7 * (@j / 7),<BR>&nbsp;&nbsp;&nbsp; @l = @i - @j,<BR>&nbsp;&nbsp;&nbsp; @m = 3 + (@l + 40) / 44,<BR>&nbsp;&nbsp;&nbsp; @d = @l + 28 - 31 * (@m / 4),<BR>&nbsp;&nbsp;&nbsp; @Date = CAST<BR>&nbsp;&nbsp;&nbsp; (<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CAST(@Year AS CHAR(4)) + <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RIGHT<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '0' + CAST<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @m AS VARCHAR(2)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ), 2<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) + <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RIGHT<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '0' +CAST<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @d AS VARCHAR(2)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ), 2<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) AS DATETIME<BR>&nbsp;&nbsp;&nbsp; );<BR>&nbsp;&nbsp;&nbsp; RETURN @Date;<BR>END;<BR>GO</STRONG></P>
<P>If you want the date for Easter 2010, just run a query like the following:</P>
<P><STRONG>SELECT dbo.udf_CalculateEaster (2010);<BR>GO</STRONG></P>
<P>The result is <EM>2010-04-04</EM>.</P>
<P>Another somewhat daunting type of floating holiday is the type that is based on the Nth weekday of the month. These holidays are defined using terms like "the 4th Thursday of November" (<A title="Turkey Day!" href="http://en.wikipedia.org/wiki/Thanksgiving">U.S. Thanksgiving</A>), "the&nbsp;3rd&nbsp;Monday in January" (<A title="MLK Day!" href="http://en.wikipedia.org/wiki/Martin_Luther_King_Day">U.S. Martin Luther King Day</A>), or even "the *last* Monday in May" (<A title='Formerly "Decoration Day"' href="http://en.wikipedia.org/wiki/Memorial_day">U.S. Memorial Day</A>). These floating holidays are not as hard to calculate as Easter, but still a little tricky (particularly Memorial Day).</P>
<P>To calculate these dates we need a function like udf_nthWeekDay below. This function accepts 4 parameters:</P>
<UL>
<LI>
<DIV>@n = integer that represents which instance of the weekday you're looking for.&nbsp; This would be the 4 in "4th Thursday&nbsp;in November".</DIV></LI>
<LI>
<DIV>@weekDay = 3-character day of week ("SUN" = Sunday, "MON" = Monday, etc.)&nbsp; I used character abbreviations instead of numbers just to make it a little easier.</DIV></LI>
<LI>
<DIV>@year = integer year for which to calculate</DIV></LI>
<LI>
<DIV>@month = integer month for which to calculate (1 = January, 2 = February, etc.)</DIV></LI></UL>
<P>As you can see below, the Nth weekday calculation is pretty simple.</P>
<P><STRONG>CREATE FUNCTION dbo.udf_nthWeekDay<BR>(<BR>&nbsp; @n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INT, <BR>&nbsp; @weekDay CHAR(3),<BR>&nbsp; @year&nbsp;&nbsp;&nbsp; INT, <BR>&nbsp; @month&nbsp;&nbsp; INT<BR>)<BR>RETURNS DATETIME<BR>AS<BR>BEGIN<BR>&nbsp; DECLARE @date&nbsp;&nbsp;&nbsp; DATETIME,<BR>&nbsp;&nbsp;&nbsp; @dow&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INT,<BR>&nbsp;&nbsp;&nbsp; @offset&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INT,<BR>&nbsp;&nbsp;&nbsp; @wd&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INT;<BR>&nbsp;&nbsp;&nbsp; <BR>&nbsp; SELECT @wd = CASE @weekDay<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN 'SUN' THEN 1<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN 'MON' THEN 2<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN 'TUE' THEN 3<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN 'WED' THEN 4<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN 'THU' THEN 5<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN 'FRI' THEN 6<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN 'SAT' THEN 7<BR>&nbsp;&nbsp;&nbsp; END,<BR>&nbsp;&nbsp;&nbsp; @date = CAST<BR>&nbsp;&nbsp;&nbsp; (<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CAST(@year AS VARCHAR(4)) +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RIGHT<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '0' + CAST<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @month AS VARCHAR(2)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ), 2<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) +<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '01' AS DATETIME<BR>&nbsp;&nbsp;&nbsp; ),<BR>&nbsp;&nbsp;&nbsp; @dow = DATEPART(dw, @date),<BR>&nbsp;&nbsp;&nbsp; @offset = @wd - @dow,<BR>&nbsp;&nbsp;&nbsp; @date = DATEADD(day, @offset + (@n - CASE WHEN @offset &gt;= 0 THEN 1 ELSE 0 END) * 7, @date);<BR>&nbsp; RETURN @date;<BR>END;<BR>GO</STRONG></P>
<P>To get the date for Thanksgiving 2024 you could call the function like this:</P>
<P><STRONG>SELECT dbo.udf_nthWeekDay<BR>&nbsp; (<BR>&nbsp;&nbsp;&nbsp; 4, <BR>&nbsp;&nbsp;&nbsp; 'THU',<BR>&nbsp;&nbsp;&nbsp; 2024, <BR>&nbsp;&nbsp;&nbsp; 11<BR>&nbsp; );</STRONG></P>
<P>The result is <EM>2024-11-28</EM>.&nbsp; Now for a holiday like U.S. Memorial Day ("the last Monday in May") the calculation is a little trickier.&nbsp; You have no idea up front how many Mondays there are in any given May.&nbsp; As an example, in 2010 and 2011 there are 5 Mondays in May; in 2012 and 2013 there are only 4.&nbsp; You could try to figure out the number of Mondays in May of the given year, but that's way too much work.</P>
<P>There's one thing we know about the last Monday in May with absolute certainty:&nbsp; after the last Monday in May, the next Monday we encounter will *always* be the first Monday in June.&nbsp; So the easier way to figure out the last Monday in May is to calculate the first Monday in June and then back into it (subtract 7 days), like this:</P>
<P><STRONG>SELECT DATEADD<BR>(<BR>&nbsp; day, <BR>&nbsp; -7,<BR>&nbsp; dbo.udf_nthWeekDay<BR>&nbsp; (<BR>&nbsp;&nbsp;&nbsp; 1, <BR>&nbsp;&nbsp;&nbsp; 'MON',<BR>&nbsp;&nbsp;&nbsp; 2012, <BR>&nbsp;&nbsp;&nbsp; 6<BR>&nbsp; )<BR>);</STRONG></P>
<P>By calculating the date of the first Monday in June 2012, we can then subtract 7 days to get the date of the last Monday of May: <EM>2012-05-28</EM>.</P>
<P>You can use the functions to calculate holidays for your auxiliary calendar table.&nbsp; I covered U.S. federal (and some other national) holidays in this one, but it can be extended to cover other national, state, and local holidays.</P>Parent-Child Build Scripts with SQLCMDhttp://sqlblog.com/blogs/michael_coles/archive/2010/01/10/parent-child-build-scripts-with-sqlcmd.aspxSun, 10 Jan 2010 18:33:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:20821Mike C<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>On the <A href="http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/7837d5428e6c83fd?hl=en#">SQL Server public programming newsgroup</A> someone recently posted a question about an SSMS error (<A href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=269566#details">"Cannot parse script. 'System.OutOfMemoryException' thrown."</A>) I hadn’t encountered this error myself, but the workaround is to break up very large scripts (50+ MB) into smaller scripts. Adam Machanic posted a T-SQL Tuesday challenge to post <A href="http://sqlblog.com/blogs/adam_machanic/archive/2010/01/04/invitation-for-t-sql-tuesday-002-a-puzzling-situation.aspx">a solution to a puzzling situation</A>, so this actually gives me a good opportunity to share how I structure my own build scripts -- which avoids this issue entirely.</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>When I create database build scripts, I use the&nbsp;<A href="http://msdn.microsoft.com/en-us/library/ms162773.aspx">SQLCMD</A> utility to run them from the command line instead of using SSMS or another tool. SQLCMD has its own <A href="http://msdn.microsoft.com/en-us/library/ms162773.aspx#sectionToggle3">commands</A>, which it parses separately from SQL/T-SQL statements. These commands are not understood by SQL Server or other scripting tools like SSMS (<I style="mso-bidi-font-style:normal;">exception: you can run SSMS in <A href="http://msdn.microsoft.com/en-us/library/ms174187.aspx">SQLCMD mode</A>, but that’s another story</I>). These special SQLCMD commands all start with a ":" at the front of the line.</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>The SQLCMD command that makes parent-child structured build-scripts possible is the ":r" or "run" command, which tells SQLCMD to run another script file from within the current script file. In the figure below I’ve set up a local directory structure with database object creation scripts in subdirectories:</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><IMG style="WIDTH:367px;HEIGHT:544px;" src="http://e60ybw.bay.livefilestore.com/y1pbBwOsoJdF21J9eW0lf7zCk782rocpyFX5YFOkwiggop15Lzj9HOBHrhOEoj0jRq7wdUr8BaYFFcpRou_irLDIAXzS_bY7al3/sqlcmd-folders.png" width=367 height=544></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><SPAN style="mso-no-proof:yes;"></SPAN></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>The <EM>\Scripts</EM> directory contains a <EM>Create.All.Sql</EM> script. This script uses the SQLCMD <EM>run</EM> command to execute the <EM>Database\Create.Database.Sql</EM> script, the <EM>Create.All.Schemas.Sql</EM> script, and so on.<SPAN style="mso-spacerun:yes;">&nbsp; </SPAN>The <EM>Create.All.Schemas.Sql</EM> script calls the <EM>Person.Schema.Sql</EM> and <EM>Sales.Schema.Sql</EM> scripts in turn. The other <EM>Create.All.*</EM> scripts each call the object creation scripts in their subdirectories as well. Here’s what my Create.All.Sql script looks like:</FONT></P><PRE>/*<BR>&nbsp;&nbsp;&nbsp;&nbsp;Create All Items<BR>*/<BR><BR>
:r Database\Create.Database.sql<BR>:r Schemas\Create.All.Schemas.sql<BR>:r Types\Create.All.Types.sql<BR>:r Tables\Create.All.Tables.sql<FONT size=3 face=Calibri>&nbsp;</FONT></PRE>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>Each <EM>:r</EM> command kicks off the next level of child packages in turn.</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>SQLCMD has another great feature known as scripting variables that you can use to create dynamic scripts. Essentially you define a scripting variable on the command line with SQLCMD's <EM>-v</EM> command line option. Now the way scripting variables work, they are replaced wholesale in your scripts with their replacement value. So if you define a scripting variable named <EM>environment</EM> you can replace it with a value like "Dev", "QA" or "Prod" anywhere it occurs in your script. This is great for making dynamic scripts that need to be built across multiple environments.</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>In the example I've used a scripting variable named <EM>database</EM>. You can set the value of the <EM>database</EM> variable from the command line with the <EM>-v</EM> option. In the example below I set the <EM>database</EM> variable to the value "Test".</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri><IMG style="WIDTH:843px;HEIGHT:187px;" title="SQLCMD Sample Command Line" alt="SQLCMD Sample Command Line" src="http://e60ybw.bay.livefilestore.com/y1py7Y3IXJHMFCeZDgZtZNWmVfQwxljC67X_AsNqD8JsJ1OM2OCxuApxSfz5V1Ze44963nb3_tZV4GUah4-4Dj0disbCrc5c7FD/Set-Scripting-Variable.png" width=843 height=187></FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><SPAN style="mso-no-proof:yes;"></SPAN></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>The nice thing about the SQLCMD scripting variables is that once you declare them you can access them from the parent script you run (in this case <EM>Create.All.Sql</EM>) or from any child scripts that are run (like <EM>Create.Database.Sql</EM>, <EM>Create.All.Schemas.Sql</EM>, <EM>Person.Schema.Sql</EM> and <EM>Sales.Schema.Sql</EM>). Here’s the <EM>Create.Database.Sql</EM> script from the example:</FONT></P><PRE>/*<BR><BR>&nbsp;Create database<BR><BR>*/<BR><BR>USE master;<BR>GO<BR><BR>
CREATE DATABASE <STRONG><U>$(database)</U></STRONG>;<BR>GO<FONT size=3 face=Calibri>&nbsp;</FONT></PRE>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>The scripting variable is accessed in the script with <EM>$(database)</EM>. The scripting variable is replaced with its value by SQLCMD, so in the example SQL Server sees this:</FONT></P><PRE>/*<BR><BR>&nbsp;Create database<BR><BR>*/<BR><BR>USE master;<BR>GO<BR><BR>
CREATE DATABASE <STRONG><U>Test</U></STRONG>;<BR>GO<FONT size=3 face=Calibri>&nbsp;</FONT></PRE>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>One thing to keep in mind when you use this scripting pattern is that every script should end with the batch terminator (default is "GO").&nbsp; If not you could end up with one script running into another and get some strange, not-very-helpful&nbsp;error messages.</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>Another thing you need to know is that scripting variables are replaced <EM>wholesale</EM> with their replacement text. This makes&nbsp;them very flexible, since you can replace text anywhere in the script with anything you want. It can also be dangerous if your script is run by someone with malicious intent. A malicious user can replace a scripting variable with T-SQL statements that could damage your data or database structure; so keep your scripting variable-enabled scripts out of the hands of potentially malicious users.</FONT></P>
<P style="MARGIN:0in 0in 10pt;" class=MsoNormal><FONT size=3 face=Calibri>I've attached a sample ZIP file with the directory structure shown in the example above. The scripts build out a few database objects from the AdventureWorks sample database.</FONT></P>Mission Accomplished: NJSQL Saturday Eventhttp://sqlblog.com/blogs/michael_coles/archive/2009/10/24/mission-accomplished-njsql-saturday-event.aspxSun, 25 Oct 2009 00:03:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:18207Mike C<P>Thanks to all those who made the&nbsp;NJSQL Saturday event held in Iselin, NJ today a success!</P>
<P>Those lucky enough to attend were given a sneak preview of one of the best young presenters out there--MVP Jacob Sebastian--who will also be speaking at PASS.&nbsp; I have to hand it to Jacob: he gave one of the best presentations on a SQL Server topic I think I've seen yet.&nbsp; Between yours truly, Jacob Sebastian, and Robert Pearl, attendees learned best practices for defensive coding and error handling (XACT_ABORT anybody?), learned the concepts behind SQL Server's new spatial data types and saw how to make use them in conjunction with Web-based mapping apps (where's that A Train run again?), and learned a bit about auditing and the internals of the transaction log and CDC.</P>
<P>Thanks to NJSQL leader and event coordinator Melissa Demsak, attendees had&nbsp;plenty to eat and everyone got a chance to win several door prizes including t-shirts, books, and copies of Windows 7 Pro and Ultimate (Steve Ballmer's signature edition, no less)!</P>
<P>For those who asked for it, I'll be cleaning up my sample code and sample geospatial database to shrink it down and pass it along (with the other presentation materials) to Melissa for posting.&nbsp; Currently the sample database is about 99 MB in size, but it contains several tables that aren't really necessary for the demo code to run.&nbsp; Today I presented on the SQL Server geospatial "what" and "why";&nbsp;at the NJSQL UG meeting in November I'll be diving into the "how".&nbsp;&nbsp;Check back at <A href="http://www.njsql.org/">http://www.njsql.org</A> for details.</P>
<P>Thanks again to all our sponsors and the NJSQL and NYC SQL Server UG for making this event happen!</P>