Entity Framework and SQL Server's Time datatype

For a current WPF project, I needed to store a time value on SQL Server 2008, using its handy time datatype. For data retrieval, I was using Entity Framework 6 (EF), and my models had been built with database-first; hence the entity models in Visual Studio were automatically generated by EF.

Interestingly, EF used the .NET TimeSpan type to map to the SQL Server time datatype. My view models (for MVVM pattern) had to match the TimeSpan type as well. This all seemed strange, until I realized there's no .NET Time type. Regardless of the strangeness, EF successfully converted values between TimeSpan and SQL Server time, as documented in this article.

Block Windows 10 forced updates

This looks promising in preventing the forced updates: Block Windows 10 forced updates without breaking your machine, part 1

SQL Server: build dynamic WHERE and ORDER BY clauses

For a recent project, I needed to build a dynamic SELECT statement without using lots of IF statements or resort to dynamic SQL statements, with the EXEC statement. Well, some research came to the rescue.

In my use case, the user chooses a search column from a dropdown box and then provides a search value. So the WHERE clause needs to be dynamic. This article provided the solution: using a combination of OR and AND statements to account for null values:

select * from Employee
 where 
   (lastName = @LastName AND @LastName IS NOT NULL)
  OR (employeeID = @employeeID AND @employeeID IS NOT NULL)
  OR (securityID = @securityID AND @securityID IS NOT NULL)
 order by
  case when @LastName is not null then CONVERT(char(50), lastName) + CONVERT(char(50), firstName) 
   when @employeeID is not null then employeeID 
   when @securityID is not null then securityID
  end

As for the ORDER BY clause, this article solved it. A CASE statement. Note also the multi-value option for the lastName column: I'd like to sort on lastName and then firstName. This is not allowed in an ORDER BY CASE statement; however, thanks to the 4 Guys from Rolla, the trick is to use string concatenation to solve this. Happy coding!

Find a column name across all tables on SQL Server

I'm doing a "black box" project, where I need to discover how a vendor app's database works. To that end, I've found this T-SQL useful in searching across the 140 tables for a column name, thanks to the tip from Pinal Dave's site,

USE myDatabase
GO

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%ColumnNameToFind%' 
ORDER BY schema_name, table_name;

Rename all empty SQL Server tables

I'm working on a large database on the developer server and need a way to sift through and analyze the data. This is for a project where we're going to discard these tables and build a new system; in the meantime, I need to work with the copy of the old data. To that end, I created this T-SQL script to append all empty tables' names with '__EMPTY'.

declare @new_name varchar(max), @query varchar(max)

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

;WITH TableRows AS
(
   SELECT 
      SUM(row_count) AS [RowCount], 
      OBJECT_NAME(OBJECT_ID) AS TableName
   FROM 
      sys.dm_db_partition_stats
   WHERE 
      (index_id = 0 OR index_id = 1)
      and (LEFT(OBJECT_NAME(OBJECT_ID), 3) <> 'sys')

   GROUP BY 
      OBJECT_ID
)

SELECT * into #temp
FROM TableRows
WHERE [RowCount] = 0
order by TableName

DECLARE db_cursor CURSOR FOR
 SELECT tablename
 FROM #temp

OPEN
db_cursor
declare @name varchar(max)
FETCH
NEXT FROM db_cursor INTO @name

WHILE (@@FETCH_STATUS = 0)

BEGIN
set @new_name = @name + '__EMPTY'
set @query = 'exec sp_rename ''' + @name + ''',''' + @new_name + ''''
exec(@query)

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor
deallocate db_cursor

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp

I used the technique described in this Stack Overflow post to find the empty tables.

SamsaraJS: Famo.us fork juices mobile Web UIs

The library provides native-like app experiences and takes on the mobile Web's performance issues: SamsaraJS: Famo.us fork juices mobile Web UIs

References greyed out in VBA window

I needed to add some references in an Access 2013 code module. Going to Tools, I found the References item greyed out. Research led to this article with a registry key to enable References:

[HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Access\Security]
"AccessVBOM"=dword:00000001

Note that I am an admin on this machine, running Office 2013 (hence the "15.0") and need VBA in Access. You'll need to modify the above to your needs.