Tuesday, July 23, 2013

Query to Find "Update" Dependencies on a Table or View (SQL Server 2012)

The built-in dependency finder in SQL Server Management Studio does not provide the ability to distinguish between dependencies that are read-only and dependencies that are read-write.  However, SQL Server 2012 does make this information available via the management views.


The query below will return all of the objects that reference the table (or view) identified by @SchemaName and @ObjectName where the referencing object can modify the data in the table (or view)... specifically this means an INSERT, UPDATE, or DELETE operation.


Note that dependency tracking in SQL Server is not perfect because of the possibility of using dynamic SQL, but this will hopefully be useful information.

DECLARE @SchemaName NVARCHAR(128) = 'dbo';
DECLARE @ObjectName NVARCHAR(128) = 'MyTableName';
SELECT re.referencing_schema_name, re.referencing_entity_name,
FROM sys.dm_sql_referencing_entities(@SchemaName + '.' + @ObjectName,'OBJECT') re
CROSS APPLY sys.dm_sql_referenced_entities(referencing_schema_name + '.' +
    referencing_entity_name,'OBJECT') ref
WHERE ref.is_updated = 1
  AND ref.referenced_entity_name = @ObjectName
  AND (ref.referenced_schema_name IS NULL OR ref.referenced_schema_name = @SchemaName);


This post was edited to fix code formatting and to specify SQL Server 2012 as this sadly does not appear to be supported in SQL Server 2008 or before.



MSDN: sys.dm_sql_referenced_entities (Transact-SQL)

MSDN sys.dm_sql_referencing_entities (Transact-SQL)

Wednesday, June 5, 2013

How To Make Your DLL Into A NuGet Package for a Private NuGet Feed

This is my reinterpretation of the documentation on NuGet.org, modified to remove the public publishing steps, and to add local publishing steps and a source control reminder.  This guide is intended to help enable the benefits of NuGet for proprietary code such as business-specific DLLs that are not fit for public distribution.  There are plenty of details, options, features, and gotchas with NuGet that are not covered here, but this should hopefully be enough to help you get started.

Step 1 is to set up a private NuGet feed.  You only need to do this once for your organization.

  • You can find out how to do that using the official documentation here: Creating a private NuGet feed
  • You should create a file share that you have at least "Change" rights on that points to the NuGet packages folder on the server where you configure the feed.

Step 2 is to write a cool or useful library (such as a DLL) in Visual Studio.

  • Be sure to fill-in the project properties under Assembly Information, including the version number if you haven't.

Step 3 is to get the NuGet.exe file which is not included by default even if you have NuGet Package Explorer working in Visual Studio.

  • Download NuGet.exe bootstrapper here: http://nuget.codeplex.com/releases/view/58939 .  This is a "bootstrapper only" which means that the first time you run it, it will download the real NuGet.exe program and overwrite itself.  Run it once from a command-line (just NuGet.exe) and you'll have the latest version (should be 600 KB or more).

Step 4 is to set up NuGet in your solution.

  • Create a folder under your solution called "NuGet" and copy NuGet.exe there.
  • Open the Package Manager Console window inside Visual Studio (under View... Other Windows... if you don't see it).
  • At the PM> prompt, type dir and hit Enter.  You should see the files and folders in your solution as well as the new NuGet folder that you just created.
  • At the PM> prompt, type .\NuGet\NuGet.exe   You should see the help for NuGet.exe printed in the console.  If so, PowerShell is working and NuGet is in the correct place in the subfolder under your solution.
  • At the PM> prompt, type .\NuGet\nuget.exe spec MyProjectFolderName\MyProjectName.csproj  This assumes that there is a C# project called MyProjectName in a subfolder under MyProjectFolderName under the current directory.  If so, NuGet will create a .nuspec file under the project subfolder.  If you click the Show All Files Show All Files button in Visual Studio, you should see it in the Solution Explorer.
  • Right-click the file and choose "Include in Project".  With the file selected in the Solution Explorer, hit F4 and ensure the Build Action is "None" and the Copy to Output Directory is set to "Do not copy".
  • Open the file and update all fields that you feel are relevant.  In particular, you should change the package ID field to be the name that you want the project to show as in the NuGet Package Explorer and get rid of any backslashes in the name.  You can remove the children of the dependencies attribute if the package stands alone.
  • Check-in your changes to Source Control!  This should include the .nuspec file, and ideally the path and binary to NuGet.exe (if your organization permits keeping binaries in Source Control).

Step 5 is to package up your project.

  • At the PM> prompt, type .\NuGet\nuget.exe pack MyProjectFolderName\MyProjectName.csproj  This will run NuGet to package up the built project to a .nupkg file.  This is the command that you will run every time you wish to update your NuGet package for publication.
  • You can confirm that this file was successfully created by unzipping it and checking to see if your DLL is inside.

Step 6 is to copy your .nupkg file to the shared folder that you set up in step 1.

  • You can do this with PowerShell in the Package Manager Console via COPY *.nupkg \\MyNuGetServerName\MyNuGetPathName$

Step 7 is to begin using your freshly created NuGet package in your team's other projects!

Friday, May 24, 2013

Performing an INSERT from a PowerShell script

This code demonstrates how to do an INSERT into SQL Server from a PowerShell script using an ADO.NET command object with strongly-typed parameters.  This script issues a DIR *.* command and inserts the results to the database.  While this example is not particularly useful, it is hopefully very simple and understandable to explain the concepts.

In general, parameterized SQL queries (like the below) perform better and are less vulnerable to SQL injection attacks than SQL queries created using string concatenation*.

The script code displayed in the blog post performs several INSERT statements using a row-by-row method.  As a bonus, I have uploaded a version of this script to SkyDrive that uses two flavors of Table-Valued parameters which should give you better performance for big datasets.


Download Scripts:

SkyDrive link to Row by Row only Script (has less commentary than this blog post, but code is identical)

SkyDrive link to Row by Row and Table Valued Parameter Script

Note: You have to edit the above files in a text editor to set the SQL Server name and DB name.  Once you edit the file it is no longer considered "remote" so you can run the file as long as the PowerShell ExecutionPolicy is set to RemoteSigned.


To try this script, first create a table in a junk database on a test SQL server:

CREATE TABLE FilesInFolder (


       TheFileName NVARCHAR(260) NOT NULL,

       FileLength BIGINT NOT NULL,

       LastModified DATETIME2 NOT NULL,




Then paste the following code into a .PS1 file (you have to edit the $DBServer and $DBName values first) and run it using Powershell:


function Do-FilesInsertRowByRow ([Data.SqlClient.SqlConnection] $OpenSQLConnection) {


    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand

    $sqlCommand.Connection = $sqlConnection


    # This SQL query will insert 1 row based on the parameters, and then will return the ID

    # field of the row that was inserted.

    $sqlCommand.CommandText = "SET NOCOUNT ON; " +

        "INSERT INTO dbo.FilesInFolder (TheFileName,FileLength,LastModified) " +

        "VALUES (@TheFileName,@FileLength,@LastModified); " +

        "SELECT SCOPE_IDENTITY() as [InsertedID]; "


    # I am adding the parameters without values outside the loop.  This means that inside the

    #  loop all I have to do is assign the values and say "run" - much less work than setting

    #  up everything from scratch in each iteration.

    # Also notice the doubled-up (()) - this is how you create a new object and then

    #  immediately pass it as a function parameter in PowerShell.

    # Next, the class names in square brackets are .NET types.  Powershell assumes System.,

    #  so you can omit it.  The double-colon is how you reference enumeration members.

    # Finally, I am piping the output of these calls to Out-Null since otherwise PowerShell

    #  would output the properties of the command object to the console on each call since

    #  there is no assignment made to a variable or another pipeline destination.

    $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@TheFileName",[Data.SQLDBType]::NVarChar, 260))) | Out-Null

    $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@FileLength",[Data.SQLDBType]::BigInt))) | Out-Null

    $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@LastModified",[Data.SQLDBType]::DateTime2))) | Out-Null


    # I love how I can foreach over a call like "dir *.*" in PowerShell!!

    foreach ($file in dir *.*) {

        # Here we set the values of the pre-existing parameters based on the $file iterator

        $sqlCommand.Parameters[0].Value = $file.FullName

        $sqlCommand.Parameters[1].Value = $file.Length

        $sqlCommand.Parameters[2].Value = $file.LastWriteTime


        # Run the query and get the scope ID back into $InsertedID

        $InsertedID = $sqlCommand.ExecuteScalar()

        # Write to the console.

        "Inserted row ID $InsertedID for file " + $file.Name






# Open SQL connection (you have to change these variables)

$DBServer = "MySQLServerName\MyInstanceName"

$DBName = "MyJunkDBName"

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"



# Quit if the SQL connection didn't open properly.

if ($sqlConnection.State -ne [Data.ConnectionState]::Open) {

    "Connection to DB is not open."




# Call the function that does the inserts.

Do-FilesInsertRowByRow ($sqlConnection)


# Close the connection.

if ($sqlConnection.State -eq [Data.ConnectionState]::Open) {






*By "in general", I mean that this is true most of the time, and in the few times when it is not true, the use of parameters is unlikely to leave you worse off than not using parameters.  Plainly, if you are using string concatenation to write SQL statements in any language, you are doing it very wrong and potentially leaving your database open for attack and exploitation.


I'm a member of the SkyDrive Insiders program; you can learn more about this program here.  I published a quick-start guide to using SkyDrive at work here.  If you have any questions about SkyDrive, please ask me!

Wednesday, February 27, 2013

Setting up an Internal Friendly URL

Note: This solution is not necessarily the best or most efficient solution in all cases.  It is, however, very simple and works with any site - even ones you have no control over.


Internal Web System XYZ must be accessed via an ugly URL such as http://randomservername.internalexample.com:8675309/SomeCrazyPath/SomeFile.htm

Desired End State:

Internal users should be able to type in a friendly URL (such as http://myapp.internalexample.com) and get sent directly to the "homepage" of the desired system.  Search result optimization and using deep links with the friendly URL are not required.

One Solution Using IIS 7.5 (should work with earlier versions by hunting for the same options):

  • Register myapp.internalexample.com and other related host names such as dev-myapp.internalexample.com in your organization's DNS.  If the server where you will be hosting the friendly URL is more likely to change its IP address instead of its name, use a CNAME record; if the server is more likely to change its name but keep its IP address, use an A record.
  • Open your IIS Manager and right-click Sites... Add Web Site.
    • The site name should be "myapp.internalexample.com (redirect)".
    • The physical path should be a folder in a secure location local to the web server.  (whatever is appropriate for your environment - it will only require a few KB of storage).
    • Set the host name to "myapp.internalexample.com" .
    • Hit OK.  This should create the site and start it automatically.
  • Go to the "HTTP Redirect" HTTP Redirect Screenshot option under the IIS section for the new site, and check the box for "Redirect requests to this destination".
    • Set the destination to your application's "real" full URL including the protocol (http or https), port numbers, deep path, document, query string, etc.
    • Check the box for "Redirect all requests to exact destination (instead of relative to destination), but do not check the box for "Only redirect requests to content in this directory (not subdirectories)".
    • The default status code "Found (302)" is fine.
    • Click the crazy Apply button in the far upper right.  Apply Screenshot
  • Next, open the "HTTP Response Headers" option HTTP Response Headers Screenshot 
    • Click the small "Set Common Headers..." button in the actions bar (upper right).
    • You can uncheck "Enable HTTP keep-alive" and set the box to expire web content "Immediately".
    • Click OK.

Set Common HTTP Response Headers dialog

  • Clients should now get redirected to the main site by visiting the friendly URL.  Because the HTTP header indicates that the content expires immediately, the IIS server hosting the friendly URL will be hit every time.  Since the only response is an HTTP redirect enforced by IIS (< 500 bytes), the load should be very light except in the most extreme circumstances, and this gives you the ability to easily change the redirect URL at some point and have the change take effect immediately.
  • If you want to include content in the root folder for the web site to make the intention clear to a server admin browsing to this folder, you can create a default.htm file with the following content:

    <!-- Note: This site is redirected via IIS Settings.  This  page should actually never be seen or used. -->
    <head><meta http-equiv="Refresh" content="0;url=
    http://myapp.internalexample.com" /></head>
    <body><a href="
    http://myapp.internalexample.com">Click here to continue to the site.</a></body>

Please note, though, that this extra step is absolutely not required, though, since the IIS server should be ignoring all site content due to the HTTP Redirect setting.  It could also "get stale" in case the redirection URL is updated in IIS but not in the HTML file which could cause more confusion.


Raw Request to http://dev-mysite.mydomain.com (captured with Fiddler):

Fiddler capture text (Request)

Raw Response from IIS (captured with Fiddler):


Wednesday, February 6, 2013

Using SkyDrive at Work for Collaboration - A Quick Start Guide

It seems that many of my projects in the past year or so have required sharing files with external business partners.  I've found that SkyDrive has been quite useful for this purpose.  Here's how to get started quickly and securely sharing files.

Setting up SkyDrive sync:

1. If you don't yet have a Microsoft account, go ahead and sign up using your normal work email address - you don't need a Hotmail account or any Microsoft-specific domain name as your Microsoft user ID anymore.  Use a strong password, or better yet use a password solution like LastPass.

2. Download the SkyDrive application for your system (Windows 7 client is 5.7 MB as of Feb 2013).  This step isn't strictly needed, but it does make the integration much more seamless.


3. When the setup finishes, a wizard will ask you to make some unimportant choices and then one very important choice.  One of the items in setup that I personally turn off is the ability to remotely retrieve files on my work PC via the SkyDrive web user interface.  You may find this feature useful, but I've decided that it does not make sense for my scenario.

4. Once the client is installed, you should have a SkyDrive folder in your user profile folder that is set up for synchronization.


5. In this folder, create a folder called "Work Collaboration".  This is where you will be making subfolders for projects that you'll be sharing with different sets of people.

When you create a new folder, it will show up with the blue circular arrows indicating that it is syncing with Skydrive...


... and after a few seconds, it should show up with a green checkbox indicating that it has synchronized with the cloud.


You are now ready to share files.

Here's how sharing works:

So, you need to share a file with a business partner?  Usually, you'd launch your mail client, type an email saying "here is the sales analysis spreadsheet you wanted to look at" and begin to attach the file. However, you know this spreadsheet is going to go back and forth at least a few times with edits. That's a lot of email! Here's the better way.

1. Create a new folder in your Work Collaboration folder called (for example) "Sales Analysis" or whatever makes sense for the project.


2. Copy the file there using Windows Explorer. This will automatically kick off the process of synchronizing it.

image image

3. Go back up one level (backspace key in Windows Explorer), right click on the folder you're going to share and click "SkyDrive... Share".


4. This will open your browser and log you in to SkyDrive.  You will be presented with the option to "Send email".  Send the email to the work address of your contact(s).  You should always check the box that says "Require everyone who accesses this to sign in" if you care about keeping the files secure.  You can also indicate if the recipients of the email can edit files in the folder (if unchecked, they will have read-only access).


Once you click Share, the list of users with granted permissions will display. You can click Done to exit.


What happens on the sharing partner's computer?

1. Your partner will get an email with content like this:


2. If they are not registered, they will be able to click the link "Sign Up now" on the SkyDrive login page.  You need to tell them to use their work email address to sign up for a Microsoft account.


3. Once they're logged in, they will see the folder and can work with the shared documents using the SkyDrive web site.  Any edits or new files uploaded to the folder will be automatically synchronized to your PC when changes are made to a shared folder in your SkyDrive.  If the documents are in one of the MS Office file formats, you or your sharing partners can even use the built-in web-based Office apps to view the documents or make edits.


4. If one of your business partners ever wants to get back to the place on SkyDrive that you've shared, they can just log in to their SkyDrive account and click "Shared" on the left-hand pane and your SkyDrive will be listed.


When I'm done sharing

When you're ready to stop sharing a file or folder, open it using the SkyDrive... Share option on the right-click menu, or if you are on the SkyDrive site already, use the "Sharing" link at the top.


On the left-hand pane, select the individuals you want to remove permissions for, and click "Remove Permissions".  They will no longer be able to access the selected items on your SkyDrive.


I hope you find SkyDrive as convenient and painless as I have.  If you have any questions about SkyDrive, please ask!  I'm a member of the SkyDrive Insiders program; you can learn more about this program here.

Saturday, February 2, 2013

Using Inline “With” Blocks to Augment a VB.NET Default Constructor

Say you have a simple data transfer class that looks like this:

Public Class Employee
    Public Property Name As String
    Public Property HireDate As Date
    Public Property EmployeeID As Integer
End Class

Normally to load up this class, you might call something like this:

Dim employee As New Employee
employee.Name = "Steve"
employee.HireDate = #1/1/2013#
employee.EmployeeID = 12345

In this case there is an implied Public Sub New() that gets called for you when the class is instantiated.  However, you could also call this:

Dim employee As New Employee With {.Name = "Steve", .HireDate = #1/1/2013#, _
                                                       .EmployeeID = 12345}

Or in VB 2010 or higher which supports implied line continuation, this:

Dim employee As New Employee With {
                .Name = "Steve",
                .HireDate = #1/1/2013#,
                .EmployeeID = 12345,

Note: if you happen to be inside a “With” block already, the inner “With” will take precedence over the scope of the outer “With”.  This applies to both inline “With” blocks and normal “With” blocks, though nesting “With” blocks is not recommended for code clarity’s sake.