MDX Query Designer Design Mode - smart in one direction, dumb in the other

(Visual Studio 2005 Report Designer using Reporting Services 2005 with Analysis Services 2005 data source)

Let's say you've created a great query with the MDX Query Designer and you want to reuse it in another report. So you select the dataset and click the Design Mode button to view the underlying MDX, copy it and switch to your new report to paste the MDX. Now you want to use the designer to tweak the query...but you can't. When you click the Design Mode button again, you get the following message:

Changing to design view will result in the current query being lost.

Now that's pretty annoying - unlike Microsoft Access or most other GUI query tools you could choose to name, the MDX Query Designer doesn't parse the MDX to reconstitute the GUI.

SSIS as sledgehammer, transcript as nut

The Register plays host to yet another pointless attempt to sell the universal applicability of BI tools - this time by Microsoft. The compleat Palinalysis could be performed with an advanced text editor.

SQL Server 2005 Report Builder - displaying prompted parameter values

This has already been covered off long ago by
http://blogs.msdn.com/bobmeyers/archive/2006/03/26/561147.aspx

=String.Format("Sales Year: {0}", Parameters!OrderYear.Value)

But if you get System.Object[] as the returned value, this is because the parameter is multivalued.

The solution is on this page:
http://msdn.microsoft.com/en-us/library/ms157328(SQL.90).aspx

=String.Format("Sales Year: {0}", Join(Parameters!OrderYear.Value))

The Join function concatenates all the parameter values into a single string.

Using Microsoft Metadata Samples Remotely

Everything documented in this post was a complete waste of time as you can launch DependencyViewer from the command line using the /depDb: switch to specify the remote repository. Example below

DependencyViewer.exe /depDb:"Server=myservername;database=SSIS_META;Integrated Security=SSPI;"

This switch is documented for DependencyAnalyzer, but there are no such instructions about command line usage of DependencyViewer except when you launch it from the command line with the standard /? switch. Must learn to mentally join up very widely spaced dots...

Microsoft provides sample code showing how to interrogate the metadata of the SQL Server 2005 tool suite. The process is as follows:

• Run scripts to create the SSIS_META database
• Run DependencyAnalyzer on your warehouse, which populates SSIS_META with lots of juicy metadata
• Use DependencyViewer to view the metadata in a graphical layout

I spotted an SSIS_META database on a remote server and naturally wanted to peruse its contents with DependencyViewer. However DependencyViewer assumes a local repository, so the person who created this database either had local login rights to the server or had a modified build that could connect to remote repositories. I had neither, so I set about making the necessary changes to the solution.

Form1.cs has a hard coded connection string:

private string connectionString = "server=localhost;database=SSIS_META;Integrated Security=SSPI;";

which I changed to:

private string connectionString = "server=myservername;database=SSIS_META;Integrated Security=SSPI;";

The rebuilt app threw an error on clicking the Load button as follows:

************** Exception Text **************System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=gfjhs98s90s67a6dssd' failed. at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.PermissionSet.Demand() at System.Data.Common.DbConnectionOptions.DemandPermission() at System.Data.SqlClient.SqlConnection.PermissionDemand() at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Samples.DependencyViewer.Form1.buttonTest_Click(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
The action that failed was:Demand
The type of the first permission that failed was:System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:Intranet

I was trying to run it from a network share, never a good idea - so I copied the exe to a local location. Next the load button gave me an hourglass for a while before throwing the following:

************** Exception Text **************System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.Samples.DependencyViewer.Form1.buttonTest_Click(Object sender, EventArgs e) at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

I put the connection string in a message box in at the start of ButtonTest_Click and found that the connection string was still pointing to localhost. More searching turned up another reference in Program.cs. The following change in this file got it connecting.

From:

[Argument(ArgumentType.AtMostOnce, HelpText = "ADO.Net SqlConnection compatible connection string to dependency database location.", DefaultValue = "Server=localhost;database=SSIS_Meta;Integrated Security=SSPI;")]
public string depDb;

To:

[Argument(ArgumentType.AtMostOnce, HelpText = "ADO.Net SqlConnection compatible connection string to dependency database location.", DefaultValue = "Server=myservername;database=SSIS_Meta;Integrated Security=SSPI;")]public string depDb;

VMWare Server Remote Console problems

Common situation

You have a VMWare Server 2.0 RC1 box (let's call it vmhost) that's being used as a sandbox and isn't allowed to be added to the primary company domain that everyone uses. Consequently it's set up in its own workgroup using DHCP.

Known Problem

The default url to access VMWare Server would be https://vmhost:8333/. However launching the console within Web Access by clicking the picture of the overlapping boxes will result in the following error:

Error opening the remote virtual machine vmhost: The host name could not be resolved.

VMWare Commentary

This is listed as a known issue in the release notes:

On Windows, the VMware Server desktop and Start menu shortcuts use the NetBIOS name in the connection URL. This might cause VI Web Access to fail to connect to VMware Server. VMware Remote Console connections might also fail, with the error Error opening the remote virtual machine machine_name: The host name could not be resolved.

Workaround: Enter the correct host name as the Fully Qualified Domain Name (FQDN) when prompted by the Windows installer. Or, if the URL specified in the shortcut does not work, use the correct host name, IP address, or localhost, as appropriate, in the connection URL. You can also manually enter the short name and the FQDN, or localhost, in the /etc/hosts file.

For the situation described, the IP address isn't static and the FQDN is not defined in DNS. Therefore none of this will work.

Workaround

Create an ASP page on the server including the following:

<a href="https://<%=Request.ServerVariables("LOCAL_ADDR")%>:8333" target="_blank">VMWare Server</a>

This will interrogate the server for the local IP address and construct a url that will work for both Web Access and Remote Console.

Edit: corrected the asp code which got messed up by unescaped characters. "Sorry about that, chief"

Classic ASP on IIS7 (for IIS6 old farts)

So you've got a shiny new Vista or Windows Server 2008, and now you want to create a test site with one classic asp page.

The problem is, if you're like me you had no idea how to navigate around the new UI. I had assumed

• Roles = security
• Features = bugs I'm insisting are by design

But it turns out I was soooo IIS6 in my thinking. I had to pull my head out of 2003 to understand.

Installing IIS7

• Start Menu:Administrative Tools:Server Manager
• Find the Roles Summary section
• Add Roles:Web Server (IIS)
• Follow the wizard and it's done. Web Server (IIS) will show up as an installed role in the Roles Summary

Adding ASP etc

• Click Web Server (IIS)
• Find the Role Services section
• Click Add Role Services
• Check Application Development (or you can just select ASP if that's really all you want)
• Complete the wizard and you're done. Application Development including ASP will show up as installed role services

Using Classic ASP in IIS7

• Start Menu:Administrative Tools:Internet Information Services (IIS) Manager
• Expand the server
• Select Application Pools. Look! There's a Classic .NET AppPool ready for your use
• Select your site
• Click Basic (or Advanced) Settings in the right panel
• Select the Classic App Pool

Troubleshooting Errors in Classic ASP

• Start Menu:Accessories:Command Prompt
• %windir%\system32\inetsrv\appcmd set config -section:asp -scriptErrorSentToBrowser:true
• This turns on detailed error messages

Congratulations, you've turned a hybrid into a Fred Flintstone car!

Happy 1st anniversary, blog

Or personal (but heavily sanitised) technical note diary or whatever...

Today someone asked me how long I've been writing to this blog and I got a surprise to find it's been exactly one year since my first article.

Let's see who's reading today by throwing in a few search terms on topics I've covered: wherescape businessobjects cognos oracle microsoft windows sql server p2v vmware db2

Thanks to everyone for searching, visiting and groaning!

Setting up an old Proliant for virtualization

I'm configuring an old DL580 G3 for virtualisation - both MS and VMWare. I blithely assumed it would have hardware virtualization, so I installed Windows Server 2008 with a view to using Hyper-V. Unfortunately I couldn't add the role.

Hyper-V cannot be installed. Server Manager has detected that the processor on this computer is not compatible with Hyper-V. To install this role, the processor must have a supported version of hardware-assisted virtualisation, and the feature must be turned on in the BIOS.

I had enabled VT ("Intel Hardware Virtualization") and XD Bit ("In-Memory Execution Prevention" in Advanced Settings) in the bios and powered down fully. (note to self: Ctrl-S followed by F9 to get to bios)

Securable, Intel Processor Identification Utility & VMWare Processor Checker all gave the thumbs down on hardware virtualisation support for the aging Xeons. Desperate for a positive answer, I almost went into the server room with vt.iso burnt onto a cd but decided I had to give it up as I had plenty enough expert opinions.

What now?

• XenServer also requires VT.
• VMWare ESX does not, but I want to use MS as well.

So I installed

• Virtual Server 2005 R2 SP1 - 64 bit
• VMWare Server 2.0 RC1 - unspecified, claims to support 64 bit VMs but installed to Program Files (x86) so guessing it's not

I followed Virtual PC Guy's instructions for setting up IIS, and after this Virtual Server installed with no problems.

VMWare Server had trouble with the url for opening up remote consoles. This is because the host server isn't part of the domain I'm on, so the hostname wasn't always available. This situation was easily worked around by using the ip address in the url (https://a.b.c.d:8333/ui) to login to VMWare Infrastructure Web Access.

IBM DB2 - ready for the gold watch and retirement

It's IBM DB2's 25th Anniversary this year, as discussed in this article at Intelligent Enterprise.

Page 3 is titled "A Vision Out Of Many Ideas", the author's intention being to emphasise the cross-disciplinary origins of DB2. But based on IBM's patchy track record in the database and business intelligence industry, I think it would be more accurate to drop the word "Many".

"CSC Cuts Corners"

I try to limit the quantity of regurgitated industry news that ends up in my blog, but this gave me an ABIMCL* moment. It goes to show that rebranding can be risky...

* Arch Back In My Chair Laughing

Proclarity visualisations included in AdCenter Analytics

Check out the screenshots here. Anyone who's familiar with Proclarity or PerformancePoint will recognise the Heat map (or "Tree Map" in AdCenter parlance - confusingly, Heat Map seems to refer to something else again).

I understand this particular visualisation won't be in the first release.

Since AdCenter Analytics is free, I trust this means that CALs will no longer be required to view Proclarity visualisations used in PerformancePoint solutions? (Somehow I suspect MS begs to differ on that point).

I'm pretty late to this news, and frankly it's because enterprise and soho/consumer analytics are poles apart. For what it's worth, I couldn't find anyone else out there who has commented on this either.

I considered signing up to the AdCenter Analytics beta but it looks like they'll only invite people with sites that have a bare minimum of 25000 page views a month. I have only had (google) analytics enabled for a week now, but with some optimistic extrapolation I might be racking up a mighty 500 or so...

Use Oracle install defaults if you want a crap character set

My big lesson with Oracle installations:

• Never install a starter database
• Never "next through" the default options when creating a database

This is because the default character set is usually something like WE8MSWIN1252, but a lot of apps require Unicode these days.

These default character sets are never strict subsets of Unicode character sets and consequently require a lengthy migration process to convert to something like AL32UTF8.

Instead use the Database Configuration Assistant to create your database, and make sure you select something suitable on the Character Sets tab.

I'm fully aware that best practice is to work through a 10 page checklist to tick off all the prerequisites, but sometimes life's just too short.

Out of Disk Space with VMWare Player and Virtual Disk Mount

My mounted vmdk file wouldn't accept any more contents - out of disk space. All the instructions I found for increasing the disk space required VMWare Workstation or Server.

I really really really didn't want to install these. Unfortunately I really really really had to.

I followed the instructions as per http://www.leonmeijer.nl/archive/2007/05/07/25.aspx

This type of maintenance is totally unnecessary with Virtual PC. What's worse, VMWare have made it very command-liney to do.

Enabling Shared Folders in VMWare Player

Add this to the .vmx file

sharedFolder0.present = "TRUE"
sharedFolder0.enabled = "TRUE"
sharedFolder0.readAccess = "TRUE"
sharedFolder0.writeAccess = "TRUE"
sharedFolder0.hostPath = "C:\"
sharedFolder0.guestName = "C"
sharedFolder0.expiration = "never"
sharedFolder.maxNum = "1"

Installing Business Objects on Windows Server 2003

Hit an error during BO XI R2 install at the CMS Log On step:

Unable to log on to the CMS.

The install was unable to log on to the CMS. Please ensure that your User Name and Password are correct, and try again.

I attempted to use the local Administrator login but the solution I found out there is to use a blank password!

Infoview comes up with

The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------------------------------------------------------------
A name was started with an invalid character. Error processing resource 'http://servername/businessobjects/enterprise115/In...
<%@ Register TagPrefix="ce" Namespace="CrystalDecisions.Enterprise.WebControls" Assembly="CrystalDecisions.Enterprise.Web...

The problem appears to be that IIS did not have a default dotnet version selection before the BOXI setup. There seemed to be nothing I could do to repair the situation - I had to reinstall BOXI after setting the dotnet version as follows:

• Open IIS Manager
• Right-click Default Web Site and select Properties
• Select the ASP.NET tab
• Select an ASP.NET version in the dropdown and click OK

Cognos Oracle Content Store Creation

Cognos "Quick" Start Installation and Configuration Guide transcript

Error: Cognos Configuration error creating new data store

Database connection failed
Content Manager failed to start because it could not load driver "oracle.jdbc.driver.OracleDriver"

This went away when I copied classes12.jar from ORACLE_HOME\jdbc\lib to c8_location\webapps\p2pd\WEB-INF\lib

Error: Cognos Configuration error creating new data store #2

Content Manager is unable to initialize the content store by using the initialization file "C:\Program Files\cognos\c8\configuration\schemas\content\oracle\dbInitTest_oracle.sql"

It appears the Schema user has insufficient privileges. I have given the following:

Roles

• Connect

System Privileges

• Alter any procedure, sequence, table, trigger
• Create any procedure, sequence, table, trigger, view
• Delete any table
• Drop any procedure, sequence, table, trigger, view
• Insert any table
• Update any table

When I execute the script in SqlPlus the error is ORA-01950: no privileges on tablespace 'X'. Apparently this means no quota has been allocated for this user.

I attempted to grant unlimited quota on all tablespaces but got an error ORA-30041: Cannot grant quota on the tablespace. I forgot that the temp table space doesn't have a quota, so I was a bit more specific in my selection.

Error: Cognos Configuration error creating new data store #3

Content Manager cannot start because the database character set for the content store is not supported. Before you restart Content Manager, you may need to recreate the content store database or clean it using dbClean_*.sql.

Following the instructions regarding character set, I found NLS_NCHAR_CHARACTERSET = AL16UTF16. Unfortunately this doesn't appear to be sufficient. SELECT NLS_CHARACTERSET FROM NLS_DATABASE_PARAMETERS returned WE8MSWIN1252. Some guidance on changing character set is that it's safest to export-create new db-import.

My steps for export are:

• Bring up Database Control
• Select Maintenance tab
• Select Export to Export Files
• Select Database and enter OS admin user and password
• (Create the export folder before doing the next step)
• Create Directory Object, enter name and folder path and select OK
• Select the name of the newly created dir object from Directory Object dropdown and select Next
• Select the dir object again and select Next
• Enter a job name and description and select Next
• Select Submit

The job kicked off but produced some errors saying it couldn't find the export directory object. Makes no sense to me, I thought I would just try and sneak on by.

I created a new instance with Database Configuration Assistant using character set AL32UTF8 and tried to import the export files into it but I hit a lot of errors.

I decided to use a separate instance just for Cognos. Phew, I could finally connect ok to the Content Database.

Cognos 8 Cryptographic Services

Copied c8_location\bin\jre\version\lib\ext\bcprov-jdk13-125.jar to java_home\lib\ext. I have no idea if this has to be done but I did it anyway.

Starting the Service

Selected Root node in Cognos Configuration and Action:Test. All came back good except mail server which I don't care about for the moment. Selected Action:Start which was also successful.

Getting the web server to respond

Created virtual directory for cognos8 at c8_location\. Worked through the IE settings and browsed to http://localhost/cognos8. 404 Page not found. Trawled around on web and found that I could Allow All Unknown CGI Extensions in IIS Web Extensions. This time I got 403 Not authorised to view this page.

Figured out that I didn't read the instructions correctly - there are 2 nested virtual directories.

• cognos8 points to c8_location\webcontent
• cgi-bin points to c8_location\cgi-bin. Created by right-clicking the previously created cognos8 virtual directory

At last it works! The Framework Manager install was straightforward after this.

OWB - table upsert mapping

Error:

VLD-2750: Missing update matching criteria in YOUR_DIM Match by Constraint is set to all constraints or a specific constraint for YOUR_DIM, then all mapped attributes of this constraint will be included in the WHERE clause in the UPDATE statement. If there is no constraint on the target, or none of the constraint attributes are mapped, set Match by Constraint to no constraints for YOUR_DIM, and define the match condition on the attributes of the target

Solution excerpted from Oracle forums:

When you use a surrogate key (eg. generated with a sequence, presumably for
a slowly changing dimension) you also have to define a unique natural key on
your target table, and use the natural key for matching/updating.

If you change your target table columns or constraints after the operator
was put on the mapping canvas, there is a possibility that OWB doesn't change
the "Loading Properties" correspondingly. To fix this;

1) first finish up your constraints work, then
2) set operator property "Match by constraint" to "No constraints", then
3) check column attribute properties "Loading Properties" for all
columns;
a) columns being part of the unique natural key should have "Load column when
Updating Row" set to "No", other columns should normally have this property set
to "Yes"

b) the "Match Column ...." properties should be set to "Yes" if part of the
natural key, "No" otherwise

c) "Load column when Inserting Row" should normally be "Yes".

4) Finally, you can change the operator property "Match by constraint" from
"No constraints" to your natural key

SSIS Oracle RDB to 10G source

Lessons for me from this exercise:

• I had to throw away the custom ODBC script tasks I had built for RDB and create a new OLE DB based extraction package
• Data that previously loaded into SQL Server Int columns now caused a datatype mismatch error. I had to change the target columns to Numeric
• This had the flow-on effect of causing many data flow data sources to break with an invalid index error. These data sources had to be recreated
• Some text data that had previously loaded in a trimmed state now had trailing spaces

So that was the dev environment. To get the packages to work via IIS required:

• Had to allow inprocess in SQL Management Studio for the provider OraOLEDB.Oracle http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=766133&SiteID=1
• Working my way through the steps in http://support.microsoft.com/kb/255084. The reboot actually made a difference

SSIS Oracle OLE DB connection

Set AlwaysUseDefaultCodePage=TRUE on the OLE DB Source component

http://blogs.conchango.com/jamiethomson/archive/2005/10/25/SSIS_3A00_-Connect-to-Oracle.aspx

So how do you do set this attribute for an Oracle ODBC source?

Everybody positioned in Leaders Quadrant for Business Intelligence

Is the recent BI M&A activity rendering this Gartner report meaningless?

Microsoft's rating upgrade is unfortunately overshadowed by their being too cheap to license a copy of the report. You have to go back to the old guard for this (thank you Cognos):

http://mediaproducts.gartner.com/reprints/cognos/vol4/article3/article3.html

Mea culpa, MS did actually dip into its wallet but didn't refer to the report location in their press release.

OWB - External Table to Dimension

I have a dev OWB 10g R2 environment into which I want to load up some reference dimensions from production. I don't have a connection to production so I thought I could load the dimensions as files since they are small. However they have a large number of attributes that I wanted to avoid rekeying if I possibly can.

Consequently I was looking for a way to load the dimensions from files with no transformation and minimum effort.

The following approaches don't work:

• Create the file location, create the external table using the import option and create the dimension using the import option. What appears to be a dimension import is actually a reimport of the external table.
• Create the file location, create the external table using the import option and create a mapping to generate the dimension operator. Unbound dimension operators aren't allowed in the mapping.

It looks like I wasn't going to be able to avoid rekeying the huge numbers of attributes until I discovered the Create Dimension expert, a free unsupported wizard that can be added to the OWB IDE.

My way to migrate or upgrade Oracle XE

Start from scratch! (assumes no apex stuff to migrate)

• Backup Oracle XE
• Install Oracle Standard 10.2.0.1
• Install the Microsoft Loopback Adapter
• Disable Microsoft Enhanced Internet Security Configuration
• Restore Oracle XE databases to Standard and test
• Uninstall Oracle XE

Advantages:

• No need to patch Standard to 10.2.0.3
• No need to run the upgrade wizard

Disadvantages:

• Shame about the apex

Oracle patch recursion - down the rabbit hole

I'm trying to migrate my Oracle XE database to Standard. I have Standard 10.2.0.1 and the instructions say they it needs to be 10.2.0.3 before this can be done. So I downloaded the upgrade "patch" (which is larger than the Standard install package). Once I downloaded it, I found that one of the prerequisites for this patch is another patch. So I downloaded that patch, only to find that it has a patch prerequisite also - opatch needs upgrading.

Cue one of those 60's rotating spiral patterns in conjunction with paranoid sound effects...

How difficult would it be to create a one-shot 10.2.0.3 install? I wouldn't mind having to reboot half way through, honest...

One of the patches required disabling of the Distributed Transaction Coordinator. If you don't restart it, you won't be able to do things like connect / as sysdba.

If you can't get restarted with Sqlplus as per instructions, you'll want to get on to the Enterprise Manager site. But you won't be able to without setting up "logon as a batch job" rights for your windows user.