UPDATE against a table-valued function when declared inline

The other day I mentioned to a friend that you could only perform an UPDATE against a table-valued function in T-SQL when the function was declared as an inline TVF. He basically didn’t understand the point being made at all. That’s not really suprising as the idea of performing an UPDATE against a function does my head in, in terms of everything I ever thought I knew about programming languages.I really don’t think it should even be permitted but regardless, here’s an example:

 

USE tempdb;

GO

 

CREATE TABLE dbo.TestTable

( TestTableID int,

  TestTableName varchar(20)

);

GO

 

INSERT INTO dbo.TestTable

VALUES (1,’Hello’), (2,’Greg’);

GO

 

CREATE FUNCTION dbo.ListGNames()

RETURNS TABLE

AS

RETURN

( SELECT TestTableID, TestTableName

  FROM dbo.TestTable

  WHERE TestTableName LIKE ‘G%’

);

GO

 

SELECT * FROM dbo.ListGNames();

GO

 

UPDATE dbo.ListGNames()

SET TestTableName = ‘Fred’

WHERE TestTableName = ‘Greg’;

GO

 

SELECT * FROM dbo.ListGNames();

GO

 

SELECT * FROM dbo.TestTable;

GO

Note that the UPDATE is being performed against the *function* not against the underlying table.

What do you think? Should this behavior even be permitted?

Ola Hallengren’s Database Maintenance Scripts

Our buddy Ola Hallengren has released another version of his database maintenance tooling. (Free !) I’ve seen quite a lot of people using Ola’s scripts rather than standard database maintenance plans, and with happy outcomes.

 

In the new version, he has optimized performance in IndexOptimize for databases with large number of indexes or SQL Servers with large number of databases. Ola said that the time for rebuilding or reorganizing indexes is the same, but the time for investigating indexes has been reduced.

 

He also mentioned that he fixed an issue in database backup for databases with very long names (eg SharePoint databases). In the old version you could run into the limitation for the length of a backup device.

 

You’ll find info and updates here:

http://ola.hallengren.com/Versions.html

http://ola.hallengren.com/scripts/MaintenanceSolution.sql

 

Skype Error: Exception EXMLDocError in module ezPMUtils.dll at 000E8FCA

Skype sent down an auto-update today. Looks like it’s now 5.3.0.111. Regardless, every time I started Skype, it said:

Exception EXMLDocError in module ezPMUtils.dll at 000E8FCA

Reboots, etc. didn’t fix it. What did fix it was moving the file ezPMUtils.dll from the folder C:\Program Files (x86)\Skype\Plugin Manager to the folder C:\Program Files (x86)\Skype\Plugin Manager\MLS. Note that at first I tried copying the file to that folder but that did not fix the issue. Moving the file, however, did “fix” the issue in that the error no longer occurs. But of course, the extras manager also no longer functions. It would be really great to see *something* from the people at Skype about this issue.

Hope this helps someone.

It was also very interesting to notice that the auto-update installer still tried to move me across to Google Chrome, away from Internet Explorer. Obviously Skype isn’t really integrated into Microsoft yet 🙂

Microsoft EDW Architecture, Guidance and Deployment Best Practices

A team from our company has been working with the SQL product team members and reviewers to produce a series of enterprise data warehouse guidance documents. They cover the following topics:

Chapter 1 – Overview

Chapter 2 – Data Architecture

Chapter 3 – Integration Architecture

Chapter 4 – Database Administration

Chapter 5 – Performance, Monitoring and Tuning

You’ll find them here: http://msdn.microsoft.com/en-us/library/hh147624.aspx

It’s great to see these documents now published. Enjoy!

Changing drive letters: “The parameter is incorrect”

This error that I came across today typifies what I hate about error messages that I see in various programs.

I was trying to change a drive letter in the system management console in Windows Server 2008 R2. I had upgraded my drive to a new drive and when I restarted my Windows Server VHD installation, it had reassigned all my drive letters. So I tried to set the Drive D drive letter to Drive L (back to the same drive letter it used to have). I had previously changed the Drive E to Drive G without an issue. But when I tried to make this change, the error message popped up saying “The parameter is incorrect”.

What exactly is a normal user supposed to make of that error message? Surely we developers can do better than this. (I’m using the collective “we” here to include the Microsoft Windows Server developers).

What was the real problem? It was that Windows had decided, to allocate a page file onto that drive. I had no idea it had done so. But because it was using the file, it wouldn’t let me change the drive letter for the drive that was holding the file. Changing the placement of the paging file followed by a reboot, then allowed me to change the drive letter back to what it used to be.

But surely, there’s some point in the code where the real problem is detected and surely “we” could surface a better error message than “The parameter is incorrect”.

This message reminded me of an error that was common in VB6 days where the system would say “insufficient memory”. I’ve lost track of the number of users that I’ve seen trying to add memory to machines to fix that problem. What the user was supposed to have instead interpreted from the message was “You have moved or removed a DLL that I was depending upon”. The person who wrote the error message has obviously decided that the only reason that we can’t load a DLL that used to be there is that we must have run out of memory. Surely “we” developers can do better than this. The next time that one of us feels the need to expose a message that says “The parameter is incorrect” or “Unexpected error” or “Catastrophic error”, etc. etc., please can we spend a few more cycles thinking about how to surface something more meaningful that would give the user some chance of understanding the issue?