Category Archives: SqlServer

Removing dead library servers and hosts from SCVMM

A while back I was setting up TFS Lab management so that we can start automating a lot of our integration testing. In the process of testing the deployment of the servers and the auto configuration scripts I was creating a lot of temporary virtual servers on the domain and using up IP’s on the network before they could be released. Therefore I wasn’t too popular with the OP’s guys and they decided to put the lab server in it’s own domain.

Unfortunately this caused a few issues as it meant that the SCVMM backed could not contact the hosts and the library servers as they had moved domain. Removing the hosts was a simple matter of dropping to PowerShell (There is a handy button at the top of the SCVMM Admin console) and running the following command:

Remove-VMHost "[SERVER_FQDN]" -Force

However the PowerShell command for removing dead library servers is not so helpful as it doesn’t have a “Force” flag and it comes up with an error saying it can’t be contacted:

Error (406) - Access has been denied while contacting the server

Luckily as the SCVMM backend is controlled by Sql server it wasn’t too difficult to find where the definitions of the library server were stored. Just connect to the SCVMM database instance ([YOUR_SCVMM_SERVER]\MICROSOFT$VMM$) and the table you need to look at is the tbl_ADHC_Library table. Firstly do a select on the table to get the GUID/UNIQUEIDENTIFIER of the library server(s) you need to delete:

use VirtualManagerDB
go

select
    LibraryID,
    ComputerName
from dbo.tbl_ADHC_Library

From this query take the LibraryID field and for each of the library servers you need removing, run the following command:

use VirtualManagerDB
go

exec prc_ADHC_Library_Delete '[LibraryID]'

Hey presto all the dead servers are now gone!

As always please make sure you have a backup and don’t blame me if you break your SCVMM server!!

Invoke-SqlCmd throws exception when trying to set the database via parameter

This was properly doing my nut today! Basically I created a generic script to fix orphaned users and replaced the database with the standard SqlCmd variable syntax:

use $(DatabaseName);

When running in SSMS setting the variables like this:

:setvar DatabaseName MyDatabase

works fine, as it does when setting as a param to the sqlcmd.exe:

sqlcmd -i C:\PathToScript.sql -v DatabaseName=MyDatabase

however when using the PowerShell Invoke-SqlCmd Cmdlet from the SqlServer PowerShell extensions it throws an exception that gives you absolutely no clue as to why:

Invoke-Sqlcmd : Object reference not set to an instance of an object.
At C:\Users\mayesr\Desktop\PowerShellScratchPad.ps1:22 char:14
+ Invoke-Sqlcmd <<<< -InputFile "$g_ScriptLocation\FixUser.sql" -ServerInstance "localhost" -Verbose + CategoryInfo : InvalidResult: (localhost:PSObject) [Invoke-Sqlcmd], NullReferenceExcep tion + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.GetScriptComm and

I eventually noticed that the Invoke-SqlCmd has a parameter that you can set the database with. Once that was set and the use statement removed, the script worked fine! Luckily it didn’t take me too long to notice but hopefully if you come across this post it will save you some time!!

UPDATE

After I thought I had fixed this I realised that it wasn’t the Database param at all and in-fact was any param! I eventually found out there is a bug in the MSDN docs (and/or code)!! The docs say that the following format should be used:

$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'"
Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray

However this doesn’t work and the following should be used instead (remove the spaces around the ‘=’):

$MyArray = "MyVar1='String1'", "MyVar2='String2'"
Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray

I just wish I’d scrolled down to the bottom of the page as other people had added comments saying this!! 😡

Bulk documenting common columns in Sql Server 2008

Recently I have just discovered the very cool app that is DataDictionaryCreator http://datadictionary.codeplex.com/. For those of you don’t know, it is a windows application that allows you to document your database with the documentation stored as extended properties within the database itself.

Almost every table that we have in our database contains 2 common fields that have the same meaning called “CreateDate” and “ModifyDate”, I’ll leave you with the hard task of trying to work out what they are for. Anyway to save myself manually adding the same description for these I knocked up a little sql script to write the data to the db (I did cheat a bit and had a peak at the sql exported from DataDictionaryCreator).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
use [DATABASE_NAME]
go

set nocount on;

declare @ColumnName nvarchar (128)
declare @TableName nvarchar (128)
declare @MS_Description nvarchar (500)
declare @Debug bit = 0
declare @SchemaName nvarchar (500)

declare @CommonColumnDocumentation table
(
    ColumnName nvarchar (500),
    MS_Description nvarchar(500)
)

declare @TablesToProcess table
(
    ColumnName nvarchar (500),
    TableName nvarchar(500),
    SchemaName nvarchar(500)
)


insert into @CommonColumnDocumentation
select 'CreateDate', 'This is the date the row was created. It is populated via a default constraint.' union all
select 'ModifyDate', 'This is the date the row was last modified. It is populated via an update trigger (If it is not being updated correctly please check the trigger exists).'

insert into @TablesToProcess
(
    ColumnName,
    TableName,
    SchemaName
)
select
    sc.name as ColumnName,
    so.Name as TableName,
    ss.Name as SchemaName
from syscolumns sc
 join sys.all_objects so on sc.id = so.object_id
 join sys.schemas ss on so.schema_id = ss.schema_id
where so.type = 'U'
    and sc.Name in (select ColumnName from @CommonColumnDocumentation)

while exists (select * from @TablesToProcess)
    begin
        select top 1
            @SchemaName = ttp.SchemaName,
            @ColumnName = ccd.ColumnName,
            @TableName = ttp.TableName,
            @MS_Description = ccd.MS_Description           
        from @TablesToProcess ttp
            join @CommonColumnDocumentation ccd on ccd.ColumnName = ttp.ColumnName
           
        if (@ColumnName is not null and @TableName is not null and @MS_Description is not null)
            begin
                print 'Updating documentation for ' + @SchemaName + '.' + @TableName + '.' + @ColumnName
               
                if(@Debug <> 1)
                    begin
                    if exists ( select 1 from ::fn_listextendedproperty (
                                    N'MS_Description',
                                    'Schema', @SchemaName,
                                    'Table', @TableName,
                                    'Column', @ColumnName))
                        begin
                            exec sp_dropextendedproperty
                              @name = 'MS_Description',
                              @level0type = 'Schema', @level0name = @SchemaName,
                              @level1type = 'Table', @level1name = @TableName,
                              @level2type = 'Column', @level2name = @ColumnName;
                        end
                       
                        exec sp_addextendedproperty
                            @name = N'MS_Description',
                            @value = @MS_Description,
                            @level0type = N'Schema', @level0name = @SchemaName,
                            @level1type = N'Table',  @level1name = @TableName,
                            @level2type = N'Column', @level2name = @ColumnName;
                    end
            end
           
            delete from @TablesToProcess
            where TableName = @TableName
                and ColumnName = @ColumnName       
       
    end

As always use at your own risk, and if anyone has any suggestions…

TextMate theme for SSMS 2005/2008

UPDATE: The SSMS 2008 version also works on SSMS 2008 R2!! 🙂

I’ve been using the the TextMate theme thanks to Rob Connery (See here)  on VS2005 and VS2008 for about a year now. (Previously I was using the DarkSide theme by Dave Reed (see here) but that was a little too Vibrant in areas and I found some of the blues quite difficult to read.)

There seemed to be no way of importing any styles or even changing the colours in SSMS2005/8 and found it almost painful to switch to the standard bright theme.

Luckily after a little digging on google I managed to find this post by Tomas Restrepo where he had actually written a tool to import the registry settings from VS2005 into VS2008. With a couple of mods to the code to point to VS2008 and SSMS2008 I was able to transfer most of the settings across. Unfortunately when I opened SSMS2008 it wasn’t quite what I had hoped as some of the text was still left as black so was effectively invisible. So I rolled up sleeves and stared digging about in the registry to perfect the theme.

Orginally I started off with this (for some reason LightScreen has made the colours a little duller than normal!! Edit: It was because it was setup to export as jpg!) :

screenshot.23

but wasn’t happy with the keywords still showing in white (“join” in the above example, but  also applies to “exists” and possibly a couple of others) so had a little tinker again and found the Registry setting required (SQL Operator Foreground). Using an unused colour from the original theme it came out like this:

screenshot.21

and now comes the dilema do I leave the colour set so the “join” keywords are and put up with it also changing the “=”, “(“, “*”, “.” etc to the modified colour as well.

The Registry settings for the 2 versions can be downloaded here (SSMS2005 is untested but should work):

I still want to update the theme so the output window is black with a green foreground like the original, but atm I can’t seem to see anything that looks like it would be the right key! look like I’ll have to suck it and see!! 😀

Let me know what you think!!

PS Please remember to take a back of you registry beforehand, and don’t come running to me if you break SSMS!!

Killing database connections with PowerShell

Recently I have been setting up a PowerShell script to automatically setup our PY (usually aka SIT) environment. One of the tasks in the script is to Kill all the connections to a database so I can restore a backup. I managed to find a function that met my needs using the SMO libraries. Unfortunately as we are still on Sql2000 (I know, I know! Its not for much longer now tho! 😀 ) the SMO Libraries were not available on our database server so I had to come up with an alternative. Basically I decided to go the route that required the least dependancies and this is what I came up with!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
function KillDBConnections([string]$serverName, [string]$DBName)
{
    $ConnectionString = "Data Source=$serverName;Initial Catalog=master;Integrated Security=SSPI"
    $connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
    $command = New-Object System.Data.SqlClient.SqlCommand;

    $command.Connection = $connection;
    $command.CommandType = [System.Data.CommandType]::Text;
    $command.CommandText = "SELECT spid FROM master..sysprocesses WHERE dbid=db_id('$DBName')";

    $connection.open();
    $reader = $command.ExecuteReader();
    $stringBuilder = New-Object System.Text.StringBuilder

    while ($reader.Read())
    {
        $stringBuilder.AppendFormat("kill {0};", $reader.GetValue(0));
    }

    $reader.Close();

    $command.CommandText = $stringBuilder.ToString();

    if($command.CommandText)
    {
        $command.ExecuteNonQuery();
    }
   
    $connection.Dispose();
}