Wednesday, October 29, 2008

How to install Oslo with SQL Server Express Edition ?

As I was delving into the Visual Studio DSL Tools to design my first DSL for , I came across this new modeling platform by Microsoft : Oslo. In fact, it is so new that the first CTP has just been released.

Haven van OsloAs I already wrote on the blog of Salamanca, the vision for this new tool seems to be exactly what I had been needing :

It's actually taking the kind of models that you're seeing arising in specific domains, like software management in System Center, or your data design over in SQL, or your process activities over in BizTalk and saying, we need to take all these domains and be able to put them into one model space. In fact, we need to let people create their own domains that aren't just isolated, but that exist in this one modeling space. And that's what Oslo is about.

That's it. I just quoted Bill Gates !

So it was unavoidable that I tried to download and install the October 2008 CTP on my computer. Everything was OK, except for the fact that the installer could not create the repository on my SQL Server 2008 Express Edition database. The message was :

Create repository database failed, please try later

So I tried later, as suggested in the installed ReadMe file. Indeed, my SQL Server instance not being the default instance on my system, but rather being a named instance (the name being "SQLEXPRESS"), I understand why the repository creation failed. The trouble is that the command lines included in the file to manually create the repository are incorrect. So here are the correct ones :

CreateRepository.exe /db:.\SQLEXPRESS
mx.exe -i:Models.mx -db:Repository -s:.\SQLEXPRESS

Please note that the second command failed at first with the following message :

There is insufficient system memory in resource pool 'internal' to run this query.

But, as suggested on this Microsoft Connect SQL Server issue, letting the server "sit for a moment" solved the problem...

Monday, August 25, 2008

The road to Salamanca

I haven't been around this blog for quite some time now. Don't take this as another useless and meaningless apology for not sticking to a self assigned schedule. The reason for this short post is just to tell the world that we have decided to turn our software factory into an open source project, hosted on CodePlex : . And there is even a blog attached to it, for which I am the main contributor. So that will be a excellent reason (I hope) for me not to find the time to update this one :-)

Salamanca is a product I really grew found of, and which I think brings great promises, especially in the field of with the concept of activities, that can be thought of as a View-Controller pattern.

Granted, as the size of the observable world seen from this blog is rather small, this post will also be used as a test for the trackback and reactions mechanisms on The road to Salamanca.

Thursday, June 5, 2008

Missing

I got stuck with this error message yesterday :

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

Mmmh. Let me give you some context. I have been working for almost a year now on this ASP .NET application :

  • connected to an Oracle 10g database.
  • uses the Microsoft ADO .NET provider for Oracle (preferred over ODP .NET 10g for its Visual Studio 2005 integration and its ability to work above Oracle Instant Client).
  • bundled Oracle Instant Client 10.2 (more on that later...).
  • uses impersonation.
  • tested on numerous development environments and a couple of servers without a glitch.

This error occurred on a new and freshly installed Windows Server 2003 server. Without a clue, I looked for help on the Internet, basing my search on the error message. An I found a good one, or so I thought. Let me get this straight : if you get the error above, it is very likely that you have to tweak your security rights on your Oracle client installation as suggested on the previous link. But not in my case, because of the way I had bundled Oracle Instant Client in my application :

  • the DLLs are deployed in the application bin directory.
  • the application bin directory is added in front of the %PATH% environment variable at application startup. This is how it is done :

In the Global.asax file :

So in my case, the Oracle client installation was my application bin directory, which had the proper rights. This did not prevent me from trying to give everyone full access, just to make sure. But, very predictively, it failed to solve my problem.

I began to sweat a little. Did I really need to install a full Oracle client on the server ? No : I proved otherwise. I thought...

At last resort, I decided to use the indispensable Reflector for .NET to check the piece of code responsible for throwing the exception. Nothing very fancy there, but I noticed after a while a peculiar trace mechanism. I tried to analyze it to find a way to enable it. And after a long while, I found out that Microsoft ADO .NET providers could be plugged into the Event Tracing for Windows for debugging. Not very straightforward, but I managed to get it work. And here is the interesting piece of trace I got :

<oc|ERR|THROW> 'System.DllNotFoundException: Impossible de charger la DLL 'oci.dll': Le module spécifié est introuvable. (Exception de HRESULT : 0x8007007E) ,   à System.Data.Common.UnsafeNativeMethods.OCILobCopy2(IntPtr svchp  IntPtr errhp  IntPtr dst_locp  IntPtr src_locp  UInt64 amount  UInt64 dst_offset  UInt64 src_offset) ,   à System.Data.OracleClient.OCI.DetermineClientVersion()'

Please pardon my french ;-) "Come on", I said to myself, "how can you not find this DLL when it is clearly in your %PATH% ?" Or is it not ? To be sure, I checked with Process Explorer. Indeed, it is.

The truth is out there : what if it could find the said DLL, but could not load it for any reason ? Indeed, a search focusing more on DllImport than on Oracle confirmed this : a missing dependency could trigger this exception. So I decided to check dependencies with the Dependency Walker.

image

So the Oracle Instant Client relies on MSVCR71.DLL, which is just not there. Isn't that DLL supposed to be by default in the C:\WINDOWS\system32 directory ? Well, it used to, but not any more.

This is it. This dependency is so common that it had been somehow installed on all previous environments but not this one. I just added it to the application installation packages, alongside OCI.DLL, and that was it.

Note : Oracle Instant Client 11.1 is delivered with all its dependencies, including MSVCR71.DLL...

Wednesday, February 20, 2008

About Visual Studio, SSIS and DSLs

I have had the opportunity to discover Sql Server 2005 Integration Services (SSIS) lately. That was a double blow. At first, I found it highly usable, though rough on the edges : still only a very promising tool. And then I found it was the perfect example of a software factory created with the Visual Studio Domain-Specific Language Tools. It is great to see Microsoft eating its own dog food, and create a great tool with it.

I kept playing working with it for a while and then I slightly became more and more frustrated by this experience. For instance, the process of modifying a data flow is tedious and laborious if a data source structure has been changed. And as happened with the Windows Forms designer, I soon felt the urge to bypass the designer to quickly mess with the code. My deep feeling is that designers are great to get started, editors are great to get finished. But in the case of SSIS, there is no such thing as code. All you can have is a XML file containing your flow design, messed up with presentation concerns, useless metadata, magical numbers... Great for machines, useless for humans.

The problem here, is that you have no Domain-Specific Language (DSL) per se. Or, better, that the DSL has been generated by a machine. Some people have identified this flaw and felt the need for proper code, even for ETLs. I have even found a code only ETL being developed. This, in my opinion, is the right thing to do, and I shall remember it when I design my own Software Factories : a DSL is mandatory, the designer is here for comfort.

SSIS is a great tool. It just lacks a DSL.

Tuesday, February 12, 2008

HowTo : Log DDL events in your Oracle database

Update (30/10/2008) : trigger code updated, due to a bug pointed out by Michel in the comments.

Databases should be under version control. Not only that, but I believe that SQL scripts should be treated as regular code : can your database be built in one step ?

But if you do that, you will want to scripts database changes too. This seems quite easy to do if you manage the whole application, but far less so if your it falls under the responsibility of, say, your customer DBA. But in any case, I think it is a good idea to track all the changes made on the structure of your database. So here is some simple scripts that I use on a daily basis to achieve this.

First of all, create a table 

CREATE TABLE log$ddlevents
(
EventDate DATE DEFAULT SYSDATE NOT NULL,
Owner VARCHAR2(30),
EventType VARCHAR2(30) NOT NULL,
ObjectType VARCHAR2(18) NOT NULL,
ObjectName VARCHAR2(128),
DatabaseUser VARCHAR2(30) NOT NULL,
OsUser VARCHAR2(30),
MachineName VARCHAR2(64),
ProgramName VARCHAR2(64),
SqlText VARCHAR2(4000)
);

This table can be automatically filled thanks to the appropriate trigger :

CREATE OR REPLACE TRIGGER trg_ad_logddlevent
AFTER ALTER OR CREATE OR DROP OR RENAME ON SCHEMA
DECLARE
sqlTextPart ora_name_list_t;
sqlText VARCHAR2(4000);
i PLS_INTEGER;
BEGIN
FOR i IN 1 .. ora_sql_txt(sqlTextPart)
LOOP
-- Make sure that we only retrieve the first 4000 characters
DECLARE
l PLS_INTEGER; -- Length of the current string
l1 PLS_INTEGER; -- Length of the concatenated string (can be >=4000)
lp PLS_INTEGER; -- Length to be concatenated
BEGIN
SELECT NVL2(sqlText, LENGTH(sqlText), 0)
INTO l
FROM DUAL;
SELECT l+NVL2(sqlTextPart(i), LENGTH(sqlTextPart(i)), 0)
INTO l1
FROM DUAL;
SELECT DECODE(SIGN(l1-4000), -1, l1, 4000-l)
INTO lp
FROM DUAL;
sqlText:=CONCAT(sqlText, SUBSTR(sqlTextPart(i), 1, lp));

IF (l1>=4000)
THEN
EXIT;
END IF;
END;
END LOOP;

INSERT INTO log$ddlevents (
Owner,
EventType,
ObjectType,
ObjectName,
DatabaseUser,
OsUser,
MachineName,
ProgramName,
SqlText
) VALUES (
ora_dict_obj_owner,
ora_sysevent,
ora_dict_obj_type,
ora_dict_obj_name,
ora_login_user,
SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'HOST'),
SYS_CONTEXT('USERENV', 'MODULE'),
sqlText
);
END;