SpurData Blog

/* borrowed 20150717 by wills - from pszanto at http://stackoverflow.com/a/13249209/377058 - made @Debug a parameter */ ALTER PROCEDURE [ dbo ].[ usp_Admin_TruncateNonEmptyTable ] @ TableToTruncate varchar ( 64 ), @ Debug bit = 1 AS BEGIN SET NOCOUNT ON -- GLOBAL VARIABLES DECLARE @ i int --DECLARE @Debug bit DECLARE @ Recycle bit DECLARE @ Verbose bit DECLARE @ TableName varchar ( 80 ) DECLARE @ ColumnName varchar ( 80 ) DECLARE @ ReferencedTableName varchar ( 80 ) DECLARE @ ReferencedColumnName varchar ( 80 ) DECLARE @ ConstraintName varchar ( 250 ) DECLARE @ CreateStatement varchar ( max ) DECLARE @ DropStatement varchar ( max ) DECLARE @ TruncateStatement varchar ( max ) DECLARE @ CreateStatementTemp varchar ( max ) DECLARE @ DropStatementTemp varchar ( max ) DECLARE @ TruncateStatementTemp varchar ( max ) DECLARE @ Statement varchar ( max ) -- 1 = Will not execute statement...

I'll try to expand on this, but anyway, here's some examples about how you have to be careful with IN and NOT IN in T-SQL in terms of when results will be returned. If the list you're comparing to has nulls, watch out. If the item you're comparing has nulls, watch out. If the list could be empty, check for that. IN and NOT IN may behave differently than you expect in all these situations. Below are most of the iterations. --in select 'row' where 1 in ( 1 , 2 , 3 ) --returns select 'row' where 1 in ( select 1 union select 2 ) --returns select 'row' where 1 in ( select null union select 2 ) --does NOT return - cannot determine if 1 "in" null, maybe it is, who knows? select 'row' where 1 in ( select top 0 f1 from ( select 1 f1 union select 2 ) qry ) --NO return b/c the top 0 qry returns "nothing", and 1 is NOT "in" nothing ("in nothing" = false) --so...

Reminder to self: When you are exploring a new concept, or getting familiar with a new technology that you intend to implement,  write your own documentation about it. By this I mean going beyond "keeping notes", where you say "Tech X allows you to do a,b,c". Instead expand it with how YOU would use Tech X, or how you ARE using it. "We use Tech X at Widgets Unlimited to overcome issues 1,2,3. Standards: We always enable Option A because Option B proved unreliable across a WAN" etc. This doesn't have to be glamorous. It just has to be personal. Why, and why bother? The act of summarizing everything you've read/seen on a topic helps you synthesize and internalize it. Fuzzy parts become clear. You are forced to really think through the topic, and make it real for your life. You've just created a great documentation resource that will be personally twice as valuable as anything you'll find online or in a book.

Quick note if you've recently upgraded to Visual Studio 2012 or beyond, and you're trying to create new projects on TFS 2010: You must still use the Visual Studio 2010 Team Explorer for that specific bit. If you looked at the compatibility page at http://msdn.microsoft.com/en-us/library/dd997788.aspx ("Compatibility between Team Foundation clients and Team Foundation Server"), you might be tempted to skip the text at the top and just scroll down to the "TFS 2010" section that applies to you. But if you do that, you'll miss this important point within the general guidance: "To create a team project or manage process templates on an on-premises TFS, you must connect using the same version level of Visual Studio or Team Explorer. That is, to create a team project on TFS 2013, you must connect from Team Explorer 2013." When you use VS 2012 against TFS 2010, and try to create a project, you may get the following error: "TF30170: The plugin Mic...

If you find the topic of SQL Server deadlocks a little intimidating and mysterious, you are not alone. But fear not, this Quick Start lays out the practical shortcut I bet is sufficient for most scenarios. Definition: What is a Deadlock A deadlock occurs when 2 threads are competing for similar resources, and Thread 1 HAS A and WANTS B, and Thread 2 HAS B and WANTS A. They’re stuck. SQL Server picks a loser and throws error 1205 "Transaction... has been chosen as the deadlock victim." The BIG PROBLEM if your application has a lot of deadlocks is unpredictable application behavior . This can especially show up in apps that call large stored procs where the failing statement gets rolled back, but the proc itself may just keep on trucking with the next statement. This is all based on what kind of error handling you're doing, whether you have XACT_ABORT on, whether you're doing explicit transactions with BEGIN TRAN or not, etc. Very Probably your application is no...

This is the best link ever on SQL Server support dates, not sure how I missed it before: http://support.microsoft.com/lifecycle/?c2=1044  

Have you ever been troubled by how terrible the "Import from Access" tools are in SQL Server? I can't even remember specifically how bad they are, I just remember the pain I felt the couple of times I tried. So I don't do it that way any more. I now just use this great tool from BullZip: Access to MSSQL. It works like you would expect, which is awesome. http://bullzip.com/products/a2s/info.php BullZip is one of my favorite companies because, like RedGate, they make simple tools that do their job well. Their PDF Printer goes on every machine I touch. It just works. Hope that helps, have a nice day.