Blog ala Vidar

SQL, AppFrame and other cool technologies

Monthly Archives: August 2010

Auto Populate Value Lists

In AppFrame we’ve got something called value lists. These are meant to make filtering even easier. When creating your filter in for example a “Documents” CR3 form, and you choose the field “Responsible”, this of course have a foreign key to some kind of Persons-table. When choosing Responsible, why not produce a list of Persons so that you can click on the person instead of having to type in the PersonID or what ever is making it unique? Well, that’s the point of value lists. I just created a script to auto populate these, based on the foreign keys. This will only take the FKs that are bound on one column though! I’ll probably fix that tomorrow, but till then, here’s the script:

DECLARE
  @ForeignKeyBaseTable AS NVARCHAR(128),
  @PrimaryKeyTable AS NVARCHAR(128),
  @ForeignKeyColumn AS NVARCHAR(128),
  @PrimaryKeyColumn AS NVARCHAR(128),
  @ColumnName AS NVARCHAR(128),
  @ColumnLength AS SMALLINT,
  @IsNullable NVARCHAR(5),
  @ColumnType NVARCHAR(15),
  @SQL AS NVARCHAR(MAX)

DECLARE @SelectColumns AS TABLE (
	ColumnName NVARCHAR(128),
	ColumnLength INT,
	IsNullable NVARCHAR(5)
)

SET NOCOUNT ON

SELECT
  O.name AS ForeignKeyBaseTable,
  OBJECT_NAME(FK.referenced_object_id) AS PrimaryKeyTable,
  FKC.constraint_column_id AS ColumnNumber,
  FKColumn.name AS ForeignKeyColumn,
  PKColumn.name AS PrimaryKeyColumn,
  CASE WHEN T.name IN ('tinyint', 'smallint', 'int') THEN 'Int32'
		WHEN T.name IN ('smalldatetime', 'datetime', 'date', 'time') THEN 'DateTime'
		WHEN T.name IN ('float', 'decimal', 'numeric') THEN 'Long'
		WHEN T.name IN ('bigint') THEN 'Int64'
		ELSE NULL
  END AS ColumnType,
  FKColumn.max_length AS ColumnLength,
  CASE WHEN FKColumn.is_nullable = 0 THEN 'false' ELSE 'true' END AS IsNullable
	INTO #FKColumns
	FROM sys.foreign_keys AS FK
	INNER JOIN sys.objects AS O ON FK.parent_object_id = O.object_id
	INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.object_id
	INNER JOIN sys.columns AS FKColumn
		ON FKColumn.object_id = FKC.parent_object_id
		  AND FKColumn.column_id = FKC.parent_column_id
	INNER JOIN sys.columns AS PKColumn
		ON PKColumn.object_id = FKC.referenced_object_id
		  AND PKColumn.column_id = FKC.referenced_column_id
	INNER JOIN sys.types AS T
		ON FKColumn.system_type_id = T.system_type_id
		  AND FKColumn.user_type_id = T.user_type_id
	WHERE T.name IN ('tinyint', 'smallint', 'int', 'smalldatetime',
					'datetime', 'date', 'time', 'float', 'decimal',
					'numeric', 'bigint', 'varchar', 'nvarchar')
	  AND O.type = 'U'
	  AND O.name LIKE 'atbl_%'
	  AND NOT EXISTS (SELECT *
						FROM sys.foreign_key_columns
						WHERE constraint_object_id = FK.object_id
						  AND constraint_column_id > 1)
	ORDER BY O.name, OBJECT_NAME(FK.referenced_object_id), FKC.constraint_column_id

WHILE EXISTS (SELECT * FROM #FKColumns)
BEGIN
	SELECT TOP 1
	  @ForeignKeyBaseTable = ForeignKeyBaseTable,
	  @PrimaryKeyTable = PrimaryKeyTable,
	  @ForeignKeyColumn = ForeignKeyColumn,
	  @PrimaryKeyColumn = PrimaryKeyColumn,
	  @ColumnType = ColumnType,
	  @ColumnLength = ColumnLength,
	  @IsNullable = IsNullable
		FROM #FKColumns

	SET @SQL = '<?xml version="1.0" encoding="utf-16"?>
<afRecordSource xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <IsFilterChanging>false</IsFilterChanging>
  <UniqueTable>' + @PrimaryKeyTable + '</UniqueTable>
  <ViewName>' + REPLACE(@PrimaryKeyTable, 'atbl_', 'atbv_') + '</ViewName>
  <SelectColumns>
	<afSelectColumn>
	  <Name>' + @PrimaryKeyColumn + '</Name>
	  <DisplayFormatType>0</DisplayFormatType>
	  <DisplayWidth>0</DisplayWidth>
	  <DisplayAlignment>0</DisplayAlignment>
	  <FieldAlias />'

	IF @ColumnType IS NOT NULL
	BEGIN
		SET @SQL = @SQL + '	  <DataType>' + @ColumnType + '</DataType>'
	END

	SET @SQL = @SQL + '
	  <Size>' + CAST(@ColumnLength AS NVARCHAR(128)) + '</Size>
	  <Nullable>' + @IsNullable + '</Nullable>
	</afSelectColumn>'


	INSERT INTO @SelectColumns
	  (ColumnName, ColumnLength, IsNullable)
	SELECT
	  name, max_length, CASE WHEN is_nullable = 1 THEN 'true' ELSE 'false' END
		FROM sys.columns
		WHERE object_id = OBJECT_ID(@PrimaryKeyTable)
		  AND (name LIKE '%Description%'
			OR REPLACE(name, 'ID', '') LIKE REPLACE(@PrimaryKeyColumn, 'ID', ''))
		  AND name <> @PrimaryKeyColumn

	WHILE EXISTS (SELECT * FROM @SelectColumns)
	BEGIN
		SELECT TOP 1
		  @ColumnName = ColumnName,
		  @ColumnLength = ColumnLength,
		  @IsNullable = IsNullable
			FROM @SelectColumns

		SET @SQL = @SQL + '
	<afSelectColumn>
	  <Name>' + @ColumnName + '</Name>
	  <DisplayFormatType>0</DisplayFormatType>
	  <DisplayWidth>0</DisplayWidth>
	  <DisplayAlignment>0</DisplayAlignment>
	  <FieldAlias />
	  <Size>' + CAST(@ColumnLength AS NVARCHAR(128)) + '</Size>
	  <Nullable>' + @IsNullable + '</Nullable>
	</afSelectColumn>'

		DELETE
			FROM @SelectColumns
			WHERE ColumnName = @ColumnName
	END


	SET @SQL = @SQL + '
  </SelectColumns>
  <OrderByColumns>
	<afOrderByColumn>
	  <Name>' + @PrimaryKeyColumn + '</Name>
	</afOrderByColumn>
  </OrderByColumns>
  <GroupByColumns />
  <WhereClause />
  <FilterString />
  <DistinctRows>false</DistinctRows>
  <MaxRecords>0</MaxRecords>
  <IgnoreDesignTimeMaxRecords>false</IgnoreDesignTimeMaxRecords>
  <MasterChildCriteria />
  <DisableAutoLoad>false</DisableAutoLoad>
  <InitMode>false</InitMode>
</afRecordSource>'

	UPDATE stbl_Database_Fields
		SET
		  ValueListRecordSource = @SQL,
		  ValueListValueMember = @PrimaryKeyColumn
		WHERE DBObjectID IN (@ForeignKeyBaseTable,
							REPLACE(@ForeignKeyBaseTable, 'atbl_', 'atbv_'),
							REPLACE(@ForeignKeyBaseTable, 'atbl_', 'atbx_'),
							REPLACE(@ForeignKeyBaseTable, 'atbl_', 'aviw_'))
		  AND FieldName = @ForeignKeyColumn

	DELETE
		FROM #FKColumns
		WHERE ForeignKeyBaseTable = @ForeignKeyBaseTable
		  AND PrimaryKeyTable = @PrimaryKeyTable
		  AND ForeignKeyColumn = @ForeignKeyColumn
		  AND PrimaryKeyColumn = @PrimaryKeyColumn
END

DROP TABLE #FKColumns
Advertisements

DELETE FROM atbl_Module_%

You ever struggled with deleting everything from a module (test data for instance) before you start the data-transfer of your live data? Here’s a quick script that will loop through all tables in a namespace and delete everything in all tables in that namespace. It will stop after failing 10 times on the same table.

DECLARE @TableName AS NVARCHAR(128)
DECLARE @SQL AS NVARCHAR(MAX)

SELECT
  name AS TableName, 0 AS FailedToDeleteNumber
    INTO #tables
    FROM sys.tables
    WHERE name LIKE 'atbl_Module_%'

WHILE EXISTS (SELECT * FROM #tables WHERE FailedToDeleteNumber &lt; 10)
BEGIN
    SELECT TOP 1
      @TableName = TableName
        FROM #tables
        ORDER BY FailedToDeleteNumber, TableName

    SET @SQL = 'DISABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)

    BEGIN TRY
        SET @SQL = 'DELETE FROM [' + @TableName + ']'
        EXEC (@SQL)

        DELETE
            FROM #tables
            WHERE TableName = @TableName
    END TRY
    BEGIN CATCH
        UPDATE #tables
            SET FailedToDeleteNumber = FailedToDeleteNumber + 1
            WHERE TableName = @TableName
    END CATCH

    SET @SQL = 'ENABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)
END

DROP TABLE #tables

Geocaching

When I was younger (a year ago or something), I play a pirate very often, waving with a sword going on treasure-hunts etc. This weekend, I found a new hobby which reminds me a lot of this; Geocaching.

Geocaching

The point is to use a GPS enabled device to find packages set out by others. These packages comes in four different sizes, from a couple of centimeters (1 inch) to 20-30 cm (about 11 inches). You locate a package you want to find on the internet (www.geocaching.com), plot the GPS coordinates into your device (in my case, iPhone) and then start walking. Some of these packages might be very close to a main road, while others are out on an island where you have to use a boat to go out and find it. When you’ve found the location, you start looking for the package. You can get some hints from the application if you need it, or you can ignore it.

Me and a friend had to check this out, so we located the package closest to my apartment. After we found it we were so excited that we just had to find more of them. We ended up finding 4 packages, all in the area where I live.

When you find a package, you will probably find a notepad in there, to write your name and date, and a couple of other things. If you take anything, remember to put in something with the same or greater value. The things we’ve found is marbles, pens, reflexes and similar. I really recommend this for people that want to do something while walking around.

Here’s a couple of links which shows the packages around the cities where most of my readers lives:

Who are YOU?

After reading Adam Machanic’s blog post with the title “Who Are You?” I though I’d do the same. So, who are you? I know many of my readers are Omega employees, but I don’t know who’s actually reading my blog. If you don’t mind, please leave a comment about yourself. Doesn’t matter if you’re an Omega employee or not. I’d like to know more about you, so please tell me a bit about yourself. This can include your name, company, where you live, what you do, your URL(s), twitter name or anything else you’d like to share. Please also let me know what you’d like to read (more) about at my blog.

If you want to know more about me, you can take a look at my about-page. Thanks for sharing 🙂

Renaming users

One of our customers are moving from one hosting provider to another, and because of this we have to migrate their databases and stuff. While doing this, they’re also changing their domain name (in Active Directory), and since that wasn’t enough change, all users will have a new naming convention in their usernames. With this in mind I’ve created a SQL script that will take “OldUser” and “NewUser”, look for all columns that have NVARCHAR(128), which is not computed, and update all of them. To make it easier, I’ve disabled the triggers and then enabled them when the query is finished.

SET NOCOUNT ON

DECLARE @OldUser AS NVARCHAR(128)
DECLARE @NewUser AS NVARCHAR(128)
DECLARE @TableName AS NVARCHAR(128)
DECLARE @ColumnName AS NVARCHAR(128)
DECLARE @SQL AS NVARCHAR(MAX)

SELECT
  @OldUser = 'OldDomain\NordnesVidar',
  @NewUser = 'NewDomain\NordVida'

SELECT
  O.name AS TableName, C.name AS ColumnName
    INTO #columns
    FROM sys.sysobjects AS O
    INNER JOIN sys.syscolumns AS C ON O.id = C.id
    WHERE O.xtype = 'U'
      AND O.name LIKE '[as]tbl%' -- Naming convention in Omega for tables
      AND C.xusertype = 231 --NVARCHAR
      AND C.length = 256 -- 128 (unicode)
      AND C.iscomputed = 0
      AND C.name NOT IN ('Domain')
    ORDER BY O.name, C.name

WHILE EXISTS (SELECT * FROM #columns)
BEGIN
    SELECT TOP 1
      @TableName = TableName,
      @ColumnName = ColumnName
        FROM #columns

    PRINT @TableName + ' - ' + @ColumnName

    SET @SQL = 'DISABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)

    SET @SQL = 'UPDATE [' + @TableName + '] '
    SET @SQL = @SQL + 'SET [' + @ColumnName + '] = ''' + @NewUser + ''' '
    SET @SQL = @SQL + 'WHERE [' + @ColumnName + '] = ''' + @OldUser + ''''
    EXEC (@SQL)

    SET @SQL = 'ENABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)

    DELETE
        FROM #columns
        WHERE TableName = @TableName
          AND ColumnName = @ColumnName
END

DROP TABLE #columns

Windows Small Business Server Code Name Aurora

You might have noticed that Microsoft released a new preview of its new version of Home Server code named “Vail” yesterday. I haven’t had a chance to have a look it yet, but I have installed the new version of SBS (Small Business Server) which is Home Server’s big brother. It’s very similar to Home Server, which is not a big surprise since it’s basically the same product, but SBS contains a couple of features you won’t see in Home Server. The SBS product focuses on small businesses (which might have something to do with the naming?) with less than 25 employees/users.

When installing, the only thing I was required to type was server name, company name, username, password and product key. Other than this, changing regional settings and time zone was the only things I had to do before everything was up and running. And by everything I mean a server with Active Directory, DNS, File Services and IIS installed. In addition to this, it’s ready for you to add (up to 25) users, (up to 25) computers and manage shared folders. Since this is using AD you of course have the option of creating group policies, logging on with the same account on several computers, but since this is also based on Home Server, you Aurora will make backups of all your computers and monitor core services, low disk space, check that anti-virus is up to date etc. If you’re interested, you can set up email notifications for these types of alerts.

By default Aurora creates a couple of shared folders; one for each user and one shared folder which everyone can access. You can of course create new shared, change them or delete whatever you want there.

I think this is a very nice product for small businesses but also branch offices. A feature you can install is “Branch Cache”, so I guess you can add the SBS as a domain in your big company’s forest in AD. When looking at the screenshots in the product overview you can see a much more detailed dashboard than the one included in this preview. I’m really looking forward to the release, and I’m 99.9% sure I’ll migrate my Home Server over to this product when it’s available!

5 minutes interview #25–Petri Tapio Wilhelmsen

Petri has just taken over for Rune Grothaug at Microsoft, being NNUG’s contact inside of Microsoft. He’s been a developer for 3 years, and an MVP for 1 year.

What did you do before you started at Microsoft?
I was a consultant at Avanade Nordics where I worked with technologies like Silverlight, WPF, Microsoft Surface and SharePoint for three years, but my passion for technology started long before that. When I was 10 years old I got a Christmas gift from my father, a book named “Du og Dino lærer Basic”, basically a children book on how to program with Basic. Later I started doing graphics- and game programming using C++, and participating in communities. I was also in 2009 awarded the Microsoft MVP award on graphics programming.

What will be your main everyday tasks at Microsoft?
Well, right now I’m trying to learn what I should do. But, it will be mostly about helping- and building communities, MSDN Live and MSDN Flash. But, my main task will be to reach out to Norwegian developers, and be their contact-person in Microsoft.

Do you have any plans on how to make the community even stronger?
Right now, my focus has been to learn my new role. As I earlier mentioned, I haven’t been able to think too much about this yet, but I do have some plans. I will have to learn what communities we have and work from there.

Other than computers, do you have any hobbies?
During the winters I usually go snowboarding with my friends, and even snow kiting when I have that possibility. During the summers I’m kite surfing around Oslo and sometimes out of the country. Other than that I like to play games on my Xbox360 and my computer.

New design

Our blogs at http://blogs.appframe.com got a new design yesterday. I’m very pleased with it. There are a couple of things not working at the moment (search for instance), but they’ll probably be up and running on Monday.

The reason for the new design is that we’ve moved over to WebSiteCMS, which is a part of AppFrame. Now we can use either Live Writer or AppFrame to post to our blogs. We’ve changed the commenting (and vote-system) to use Disqus, which in my opinion is pretty sexy.

Any comments?

Columns that are not in use

I’m working on upgrading a client to our new framework. While doing this, we’re creating new, clean and sexy, data models. Changing column names that are partly Norwegian and partly English into only English.

While I was doing this, I found a table which were heavily used, and had A LOT of fields. 173 to be exact. So, how do I check which columns that are not in use? Fastest way is to check all fields if they’ve got any values at all. This is of course not that accurate, since a field could have been used once, 10 years ago, and never later, but we’ll start with this. Here’s a script to find all fields in one table, that does not have any values:

SET NOCOUNT ON

DECLARE @TableID AS NVARCHAR(128)
SET @TableID = 'your_table'

DECLARE @ColumnName AS NVARCHAR(128)
DECLARE @SQL AS NVARCHAR(MAX)

SELECT
  name AS ColumnName
    INTO #columns
    FROM sys.syscolumns
    WHERE id = OBJECT_ID(@TableID)

WHILE EXISTS (SELECT * FROM #columns)
BEGIN
    SELECT TOP 1
      @ColumnName = ColumnName
        FROM #columns

    SET @SQL = 'IF NOT EXISTS (SELECT * '
    SET @SQL = @SQL + ' FROM ' + @TableID
    SET @SQL = @SQL + ' WHERE ' + @ColumnName + ' IS NOT NULL) '
    SET @SQL = @SQL + 'BEGIN '
    SET @SQL = @SQL + '    PRINT '''+ @ColumnName + ''''
    SET @SQL = @SQL + 'END'
    EXEC(@SQL)

    DELETE
        FROM #columns
        WHERE ColumnName = @ColumnName
END

DROP TABLE #columns