IUpdatable, what is it supposed to do?

I’m setting up an OData service by means of WCF Data Services. The DataContext publishes a list of BloodPressureMeasurements. These are queried from a more generic model in a lower tier, which in turn is persisted somewhere. So in essence I work with a model specific for the service, hence we call it the ServiceModel.

To publish the BPM’s, the DataContext contains a

public IQueryable<BloodPressureMeasurement> BloodPressureMeasurements

and in the get method I take care of the mapping from the generic model to the ServiceModel. That was the relatively easy part.

Now I want to enable adding new BPM’s. For the service to accept that, the DataContext has to implement IUpdatable. I have found several examples using either Entity Framework or LINQ to SQL. In both cases, most of the actual ‘IUpdatable-work’ is offloaded to the underlying techniques. And neither the official documentation nor the examples explain why IUpdatable is even there, and what the implementation should accomplish.

I’ve started implementing it nevertheless, and that made me discover exactly that: what should it do? In essence, when the data has to be updatable, WCF Data  Services needs a place to collect all the changes, whereafter it can ask to save all those changes (or discard them). So the IUpdatable implementation should provide some sort of delta-collection. If you get that, it becomes more clear what each of the methods should do.

And while experimenting with IUpdatable and writing this blog, I finally found a very valuable resource on the Astoria Team Blog (now WCF Data Service Team Blog, why didn’t I look there earlier?) Read, it’s a good explanation.

I chose a very simple solution: two List<BloodPressureMeasurement> variables, one for additions, one for deletions (editing is not permitted in our case). Since BPM objects are small POCO’s, I can send the objects themselves around. If you instead want to move around references to the objects, you have to implement ResetResource and ResolveResource.

Furthermore, there are no master-detail collections to be taken care of, so I could also leave out SetReference, Add- & RemoveReferenceToCollection.

After implementing I discovered one more thing that might be handy to know: If you implement a ChangeInterceptor on your service, I will be called in this sequence:

1. all the methods on IUpdatable (as implemented by the context) for assembling the delta
2. your ChangeInterceptor
3. IUpdatable.SaveChanges

Happy Data Servicing!

Rijtje records genereren in SQL Server?

Hier is een recursieve oplossing. Geinspireerd op de code van deze site: http://mhimu.wordpress.com/2009/05/07/sql-dynamic-date-range-number-range-in-oracle-sql-server-and-db2400/. Daar doet de schrijver het met dateranges.

NB: Recursie kent een grens. In SQL Server default 100 stappen, met OPTION (MAXRECURSION n) kun je een andere waarde kiezen. De Books Online over MAXRECURSION:

MAXRECURSION number

Specifies the maximum number of recursions allowed for this query. number is a non-negative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.

When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.

Because of this error, all effects of the statement are rolled back. If the statement is a SELECT statement, partial results or no results may be returned. Any partial results returned may not include all rows on recursion levels beyond the specified maximum recursion level.

For more information, see WITH common_table_expression (Transact-SQL).

In dit voorbeeld maak ik er een tabelletje met users mee, maar je kunt zelf vast ook andere opties verzinnen.

WITH IntRange (i) AS
(
SELECT 1 as i
UNION ALL
SELECT (1 + i) as i FROM IntRange WHERE i < 150
)
SELECT i from IntRange
OPTION (MAXRECURSION 150)
GO

WITH IntRange (i) AS
(
SELECT 1 as i
UNION ALL
SELECT (1 + i) as i FROM IntRange WHERE i < 150
)
SELECT i as ID, 'user_' + CONVERT(varchar(3), i) as Username, 'password_' + CONVERT(varchar(3), i) as Pwd
INTO #users
from IntRange
OPTION (MAXRECURSION 150)
GO

SELECT * FROM #users
GO

VS2010 Settings not updating

Nitty gritty detail, could drive you mad.

In Visual Studio 2010 I have a C# Windows application. I changed the connectionstring for the database in the app.config file. Later, I opened the Properties | Settings. VS noticed that the app.config had changed and suggested to change the settings accordingly. Yes, VS, I would like that, thank you. 

But then (as it turned out much later), it does NOT change the default setting in the Settings.Designer.cs, in the

[global::System.Configuration.DefaultSettingValueAttribute(....)]

When you simply run the application, it will read app.config (or actually the values that were copied to <applicationname>.exe.config). But when you - a I did - use it's classes from a Test project, it will only use the default setting.

Solution: open the editor in the Properties | Settings designer, test your connection (if you want to), and then save the setting. Now VS sees that you altered it, and adjusts the .cs file accordingly.

Talend SSO, NTLM to SQL Server 2008

Fresh laptop, let's re-install the Talend Open Studio for some testing. I usually work with SQL Server through Integrated Authentication (automatically logging on with your Windows account). Not so easy with Talend though. I finally got it to work by:

- downloading jTDS 1.2.5 from http://sourceforge.net/projects/jtds/files/jtds/

- extracting it, navigate to subfolder x86\SSO

- copy ntlmauth.dll to Windows\System32 (could be anywhere in your PATH if I understood several posts well)

- restart TOS

- leave username and password in the DbConnection settings empty

- in ServerName just the name of my computer (no instance name appended)

And then, ole, it worked!

Can you express order in UML?

Today a collegue asked me if he could express order in a class diagram. Well, you can't.

Situation: class QuestionList, composition of multiple Question items. Actually, each question in the QuestionList is of a specific subtype of Question. How to express that the Question items should be in a specific order?

The Association in the class diagram offers the attribute 'ordered' in the source or target role. This leads to the first set of classes in the diagram below. However, you cannot infer from it that in the QuestionList, an Object of type FirstQuestion should be first, and an object of type SecondQuestion second. Or even that you should have one of each.

Next option is to use Objects instead of Classes. As in the second diagram. It shows that MyQuestionList consists of one object of type FirstQuestion and one of type SecondQuestion, but still no order.

The problem is that the order in which questions are to be asked is a dynamic feature, not a static one. And since a Class Diagram is for static constructions, you cannot express order very well. So we go on to a more dynamic diagram: Collaboration. In this diagram you could use the role binding to state which type of question fulfills which role. And then read 'role' as 'being the first question' and so on. See the diagram below. It still isn't machine processable, but at least you have modelled it.

I'm open to better solutions.

Oops, Visual Studio 2010 installed SQL Server Express

Fresh machine! Fresh Win7 pro!

I already loaded SQL Server 2008 R2.
Next is Visual Studio 2010. Full install or custom install? Well, what the h*, let's do a full install.

Oops, I did not anticipate that the full install of an IDE includes an RDBMS. My mistake I guess.

So I ended up with two SQL Server instances, one for 2008 R2, one for Express. Fresh machine becomes mess machine. In just 2 hours.

Problem is: When you re-run VS setup, it offers to add/remove features, but SQL Server Express is NOT one of those features. The uninstall team at MS clearly did not count on an RDBMS being included either. On to Add/Remove Programs (which, on a side-note, in Dutch is called 'Software', which is still an English noun...). No such entry as 'Microsoft SQL Server Express'. Hmmm.

But hey, I found out: In Add/Remove Programs find the entry 'Microsoft SQL Server 2008'. Select it. Choose 'Uninstall/Change'. It opens a window, choose 'Remove'. After some more steps you will be presented the choice which instance you would like to remove. Choose SQL Server Express. In the selectionlist for individual features do not select the Shared Features (you'll want to keep them for your 2008 R2 instance). Next, next, finish.

Win7 may complain about the uninstall not being compatible or something like that. Ignore it. It has uninstalled properly, as you can check by not seeing the SQL Server Express services in Run | services.msc any more.

VMWare upgrade requires expert SQL Server knowledge

Today our System Administrator wanted to upgrade the VMWare server (aka vCenter Server) to version 4.1, thereby reusing the existing management database. Our SA is no database expert, and he shouldn't need to be. VMWare thinks otherwise.

To set the scene: the new version is 4.1, which requires a new server because the server OS has to be 64bit. The existing management database is called 'VMware' and located on a SQL Server 2005 instance. On a previous install the SA was instructed to create a 'VMware' user, and create several tables under that account. And therefore automatically in the 'VMware' schema (which in SQL Server is not exactly the same as a user). So in the VMWare database about 1/3 of the objects lives in the dbo schema, and the rest in the VMware schema. While running the upgrade that leads to the following error:

"Setup found that multiple schemas exist in the database. Please the remove extra schemas before continuing."

Apparently, VMWare knows that such a thing may have happened before, and have written an instruction to solve it. It's here. Unfortunately, it is not enough to make it work. It took us two hours, so I write it down here to save you that time. And forget about the 8 steps on the aforementioned page.

1. Make a backup from the original VMware database, before even starting the upgrade process.
2. Start SQL Server Management Studio, and open a new Query Window.
3. Execute: USE VMware;
4. Execute: SELECT schema_id from sys.schemas where name = 'VMware';
   
5. Execute: SELECT 'ALTER SCHEMA dbo TRANSFER ' + sys.objects.name from sys.objects where sys.objects.type in ('U', 'V', 'P') and sys.objects.schema_id = ;
   
6. Copy the result of step 5 from the result window to the query window, and execute this list of statements. You now have moved everything from the VMware schema to the dbo schema.
7. I suppose you still want to use the VMware user to perform the upgrade, so you have to make sure that when you do, the upgrade scripts doesn't create new objects in the associated VMware schema again. It appears that SQL Server Management Studio lets you set the default schema for user VMware in the properties window. You can change it, but it won't remember the change... So, back to scripting:
8. Execute: DROP SCHEMA VMware;
   
9. Execute: DROP USER VMware;
10. Execute: CREATE USER VMware WITH DEFAULT_SCHEMA = dbo;
11. Finally, we came across another error in the upgrade log: "Error while upgrading: ERROR [42000] [Microsoft][SQL Native Client][SQL Server]CREATE INDEX failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods." Well, if you know that, VMware, include a statement in the upgradescript to make sure the setting is correct. But hey, we'll do it ourselves:
12. Execute: SET ARITHABORT ON;
    
13. Make another backup of the VMware database, prior to starting the upgrade.
14. OK, by now the vCenter Server setup worked through the database upgrade script (using the VMware login for access to the database). I hope it does for you as well.