Thursday, July 26, 2012

SQL Server 2012 LocalDB

I've been playing around with the new LocalDB edition of SQL Server 2012 (read about it here).

In short LocalDB is an edition of SQL Server 2012, comparable in features with express edition, but running as a user process not a service. LocalDB is targeted developers, but I could think of a few other uses for it.

LocalDB automatically creates an instance called "v11.0" - the full name of this instance is "(localDB)\v11.0". Connecting to this instance from PowerShell can be a bit of a challenge, since LocalDB is only fully supported by DOTNET 4.0.3 and above. This means that PowerShell can't work with it "out-of-the-box", except through named pipes (but who really uses those anymore? :-) )

Trying to use QuerySQL (a script I put on the blog back in 2009) will only result in an error, as the connection cannot be made.

QuerySQL "(localDB)\v11.0" "SELECT * FROM sysfiles"

As mentioned before, this can be solved by the use of named pipes. To discover current name* of the pipe, use the following command:

SQLlocalDB i "v11.0"

The last line in the output contains the current name* of the named pipe, which can then be used for the QuerySQL:

QuerySQL "np:\\.\pipe\LOCALDB#822B23E5\tsql\query" "SELECT * FROM sysfiles"

* Please not that the named pipe does not have a static name. Restart the computer, and the name will change.

Another way of making PowerShell and LocalDB play nice is to make PowerShell aware of DOTNET 4.0.3. This can be done by creating a file called “powershell.exe.config” in the C:\Windows\System32\WindowsPowerShell\v1.0 . The file should contain the following:

<?xml version="1.0"?> 
    <startup useLegacyV2RuntimeActivationPolicy="true"> 
        <supportedRuntime version="v4.0.30319"/> 
        <supportedRuntime version="v2.0.50727"/> 

Be aware that this not an officially supported way of using PowerShell, so it might break other stuff ...

After starting a new PowerShell, the earlier command should work just fine:

QuerySQL "(localDB)\v11.0" "SELECT * FROM sysfiles"

1 comment:

Unknown said...

Powershell V3 is another option for fixing this issue as it uses .NET 4. Powershell V3 is in RC status as of this post.