Configuring SQL Server Express Edition for remote access


I wanted to access SQL Express on my local machine from within a Client Hyper=V virtual machine on the same Domain.

This article got me most of the way there:

http://akawn.com/blog/2012/01/configuring-sql-server-2008-r2-express-edition-for-remote-access/

But it was a bit out of date.

My steps were:

  1. Enable TCP/IP Protocol in SNAC
    image
  2. Restart SQL Server
  3. Configure (Windows 8) Firewall to allow all Inbound for sqlservr.exe
    image

    image

Footnote:

I thought this might be relevant (nice to be able to script it):

http://support.microsoft.com/kb/968872/en-us

But the problem is that this is for fixed ports and not compatible with the (default) Dynamic Ports settings above.

author: rohancragg | Posted On Wednesday, July 24, 2013 1:39 PM | Comments (0)

Joining to an arbitrary SQL set in EF5


I wanted to be able to write a hierarchical query in LINQ but found that it’s not something you can do.

I knew how to write TVFs or User Defined Table Valued Functions and had used them in joins in pure TSQL before but I suspected that it ought to be possible to re-use that SQL within an EF model so I went hunting…

Eventually I was inspired by this http://code.msdn.microsoft.com/windowsdesktop/Recursive-or-hierarchical-bf43a96e

First, I added Function Imports for my TVFs to my (classic Database-First edmx) model:

clip_image001

Update from Database:

clip_image002

Function Imports got added:

clip_image003

I also ensured that the Return Type was one of the Entities already in the model:

clip_image004

I love it! – it's an IQueryable just like any other EntitySet:

clip_image005

So that means I can do some LINQ joins between the function and other EntitySets:

image

Job Done Smile

author: rohancragg | Posted On Tuesday, July 9, 2013 5:39 PM | Comments (1)