sortitious

Subclipse overlay icons

2011-11-29T14:20:00.001+13:00

Lifted from http://stackoverflow.com/questions/3917925/what-do-the-arrow-icons-in-subclipse-mean/3920248#3920248 because something this valuable should be an article - not just an answer.

When working with the Subversion repository, remote changes (those on the repository) are incoming changes (remote → local), while local changes are outgoing changes (local → remote).

In views that compare the local and remote copies, incoming changes are denoted with a blue, left-facing arrow, while outgoing changes are denoted with a gray, right-facing arrow. These arrows may be decorated to indicate more specific operations.

The Package Explorer view, on the other hand, indicates a file's status with a different set of icons representing its local state. These icons are the ones most commonly seen, so let's start with them:

- A file ignored by version control. You can control what resources will be ignored by going to Window → Preferences → Team → Ignored Resources.
- A file not under version control. These are typically new files that you have not committed to the repository yet.
- A versioned file that needs to be added to the remote repository. These are typically files you have either renamed, or moved to a different directory.
- A deleted folder. These are folders that you have deleted locally without yet committing the changes to the repository. Note that files are usually removed from the view when they're deleted locally, so they are normally not seen with this icon.
- A file with no local changes.
- A file with local, uncommitted changes.
- A locked file.
- A conflicted file. These are typically files that had a commit/update conflict that you marked to resolve later.
- A file that has a tree conflict. These are typically files that have local changes, but have since been moved, removed, or renamed in the repository since the last local copy update.
- A file that is external to the project. Linked external files cannot be committed to the repository.
- A file that has been switched. These are files which belongs to a different working copy than their local parent directory.

In a synchronization view (e.g. via Synchronize with Repository or Commit), as previously described, there are icons which indicate which direction a change is occurring in as well as additional information about that change. These are described below:

Outgoing
- A file whose contents have been modified and will be committed to the repository.
- A file that will be newly added to the repository. This may coincide with a file removal in cases where a file is moved or renamed..
- A file that will be removed from the repository. This may coincide with a file addition in cases where a file is moved or renamed.
- A file with property changes, in the Commit dialog. On the Synchronize tab, property changes are currently reflected as a normal file modification ().

Incoming
- A file that has content changes committed to the repository that will be applied to the local copy.
- A new file that will be added to the local copy from the repository. Like the outgoing file addition, this may be the result of a move or rename.
- A file that will be removed from the local copy because it has been removed from the repository. Like the outgoing file removal, this may be the result of a move or rename.

Conflict
- A file that has been changed in both the local copy and repository independently, causing a need for conflict resolution. Fixing this condition involves opening up the conflict view or forcibly overwriting changes locally or remotely.
- A file that has a tree conflict. This can occur when there are new changes to a file on one end (either local or remote), and the file is moved, removed, or renamed on the other.

Finally, the Synchronize with Repository option opens the Synchronize tab () with the following buttons:

- Synchronizes the local copy with the currently selected repository when clicked. Selecting from the drop-down allows switching between different remote code bases.
- Shows only incoming changes (remote → local).
- Shows only outgoing changes (local → remote).
- Shows both incoming and outgoing changes (remote ↔ local).
- Shows conflicting changes.
- Updates all local resources with incoming changes after prompt.
- Brings up the commit dialog to commit all outgoing changes.
- Breaks up the change lists by revisions.

There are a few other views which weren't covered that have additional icons, but this should hopefully be a good start. Let me know if there is anything important missing, or any of these descriptions seem to differ from your actual experience.

Salesforce.com activity - parent field is Subject

2011-11-29T10:30:00.007+13:00

I needed to be able to restrict the criteria for a Salesforce.com workflow rule so that it was only triggered if an activity belonging to a particular custom object was changed.

To identify the object type I wanted to check the first 3 characters in the GUI field that's equivalent to the API WhatId.

Finding out from googleland which field I should use for this was surprisingly fruitless. Wrong choices:

Opportunity/AccountId
Contact/LeadId

Through trial and error it turns out that Subject was the right field! (!?!?Subject!?!?)

So my simple filter looks like "Subject starts with a99"

Finding my way around the Force.com Eclipse IDE

2011-11-23T09:43:00.007+13:00

I find that it's easy to accidentally close tabs and views in Eclipse, or send them to Fast View. The bottom right panel in particular seems to have a high mistake rate for me.

Views are easy to bring back if they were accidentally closed:

Force.com views - e.g. Window: Show View: Problems
Standard Eclipse views used by the Force.com perspective - e.g. Window: Show View: Other: Progress

If the views aren't visible after using Show View then they have probably ended up in Fast View.

Fast view is in the bottom left corner. In the image shown there are two icons: the Fast View menu and the Progress view.

To rescue the Progress view, click it and it will be displayed on top of the Project Explorer. Then move the view back to its rightful place.

To rescue other views, select the view you want from the Fast View menu and move it.

Don't select views in the Fast View menu that are already in the right place - they will disappear into Fast View.

Salesforce.com and validation rules

2011-03-23T11:07:00.010+13:00

Salesforce.com supports validation rules that are evaluated when DML is performed on a record. These rules can be defined using formulae (which means you can get far more sophisticated than "required field") and the error messages can be presented in user-friendly language.

This is a far better experience than specifying required="true" for each apex:inputField, which can be too simplistic to meet business rules and will return field labels that may not necessarily be user-friendly (e.g. "Date Customer Last Contacted for Product Category A" is not suitable for customers completing a satisfaction survey).

Unfortunately these validation rules are not evaluated simultaneously - they are worked through one by one until there is an error or no rules are left. This means that for a page with 20 validation rules, the end user may have to fix 'n' click 20 times before they can save the page contents.

The only way I'm aware of at present to perform simultaneous evaluation AND present user-friendly error messages is to codify the validation rules in the controller class - bleagh!

Any better suggestions?

Forcing numeric data type in Excel

2011-03-04T11:34:00.002+13:00

Excel data types are particularly significant in the following situations:

Formulae that perform comparisons such as Vlookup
Using Excel as a data source in a join query when you have poor control over type casts e.g. while using Microsoft Query

You can set the formats of cells, but this won't update until you actually edit the cells. If you have a lot of cells it's not feasible to edit them all individually.

I find the quickest way to force the conversion of text to number is to do the following:

Enter 1 in a cell
Copy the cell
Paste special into the range of cells you want to convert
Specify Value and Multiply

The cells you selected will be converted to numeric type.

Numeric is the easiest as Paste Special has number-specific options. For other data types I think you have to create a copy of the cells using a casting formula and use the calculated values instead of the originals. If I find any faster ways for non-numbers I will update this post.

User friendly object lookups in Force.com

2011-02-21T12:08:00.003+13:00

The typical approach to user lookups and searches in form development would be to:

fetch a list of object IDs and descriptions
display the descriptions to the user and allow them to select
return the ID(s)

This is tricky in Force.com because there is one standard object identifier field that is always used for lookups and searches. You can make this field an ID for referential integrity purposes, or you can make it a text field for user readability - but you can't do both.

Another ugliness is that the identifier field can't be removed from the object creation page and is editable by default.

A workaround is as follows:

In the object page layout, move the identifier field to the system information section. This shifts it out of the way a bit.
Create a custom button that puts a default value into the identifier field
Add a workflow rule to copy a value into the identifier field from another field

SQL Server search all table columns code

2010-06-25T11:41:00.003+12:00

Codekeep.net has a great stored-proc-free code snippet for text searching SQL Server database columns.

Unfortunately this code fails due to single quotes in the column names of the database I'm working with (I know I know, don't get me started!)

It's a simple fix.

On line 43, replace

@ColumnName

with

replace(@ColumnName,'''','')

Permissions required to access SQL Server from MS Query

2010-06-24T09:03:00.004+12:00

MS Query relies on system stored procedures to perform actions like fetching table lists from SQL Server.

Stored procedures that have been identified as required:

mssqlsystemresource.sys.sp_tables
mssqlsystemresource.sys.sp_datatype_info
mssqlsystemresource.sys.sp_columns

(to be completed)

Visio Viewer 2007 Red X Problem

2010-03-25T22:17:00.002+13:00

Symptom

Red X appears when attempting to open Visio documents from Internet Explorer via Visio Viewer 2007

Resolution

Download and install Visio Viewer 2007 SP2
Download and install Security Update for Visio Viewer 2007 (KB973709)

Originally posted at technet forums by CCI_Dave

Recovering lost offline files

2010-02-23T10:28:00.004+13:00

See http://www.jacksontechnical.com/article.htm?id=34

The steps are:
1. Download a Microsoft tool called CSCCMD.EXE
2. Run the following command:

CSCCMD /extract /target:c:\yourrestorefolder /recurse

This will pull the entire contents of the offline files repository from c:\windows\csc into c:\yourrestorefolder

Make sure that c:\yourrestorefolder is completely empty when using the /recurse switch.

A common cause of this problem is if you have a storage quota on your network drive and are trying to sync more files from your laptop than you're allowed to keep.

Virtual PC performance problems on Lenovo T61

2010-02-15T11:03:00.003+13:00

Virtual PC guy has written about how laptop power management can impair virtual pc performance - resulting in jerky mouse behaviour and delayed/lost keystrokes.

The specific fix depends on the laptop brand and model.

For a Lenovo T61 or similar, posts and comments I have read out there suggest disabling power management completely. However this will cause shortened battery life when not on AC.

So I tried to disable power management only when connected to AC, as follows (based on the assumption that virtual pc won't be used when running on battery):

Reboot
Hold down F1 to go into BIOS
Select Config: Power: Intel SpeedStep: Mode for AC
Change from Automatic to Maximum Performance

Unfortunately this didn't improve things noticeably.

Board 7 prerequisites

2010-02-10T14:12:00.003+13:00

Symptom

Board 7 Client launch fails with unexpected error

Cause and Solution

Missing prerequisites

If you're installing Board 7 on a sandboxed environment with limited or no internet connectivity then you have to manually install the prerequisites from redistributable executables.

Symptom

Can't connect to Local Engine from Client

Cause and Solution

No "first user" (i.e. administrator) specified. This is achieved from Server Configuration.

Subsequent users are created in the client.

Windows Server 2003 sandbox and activation

2010-02-09T11:37:00.007+13:00

Scenario:

Windows Server 2003 in its own workgroup installed and connected to a domain that uses a proxy server for handling external connections

Problem:

How to activate Windows Server 2003 over the internet?

All external connections must be authenticated by providing a domain username and password to the proxy. Windows Activation doesn't provide an opportunity to enter these - it only offers custom specification of the proxy.

Solution Options:

Use a volume license key instead - this doesn't require activation (however you have to have the volume license version of the software)

Use telephone activation. Tedious but it works

If you are mute or can't use telephone activation for other reasons? To be completed

SSIS handling errors in control flow

2009-12-22T15:13:00.002+13:00

To ensure that errors don't cause the package to stop, but all errors are reported back to parent containers, set the following:

FailParentOnFailure = True
MaximumErrorCount = 99999 (an "infinite" value)

SQL Server Agent Jobs and GUI Automation

2009-12-21T14:15:00.024+13:00

Scenario

An application vendor has provided an application database maintenance utility that only has a GUI interface. You want to automate this maintenance activity as a SQL Server Agent job.

The best solution would probably be to tell the vendor to add a command line interface to the utility to facilitate unattended use. Unfortunately the vendor may be unable or unwilling to achieve this.

A Solution

Use AutoIt to script and compile an executable that will perform unattended execution of the ute
Call the AutoIt-generated exe from a SQL job using CmdExec

Step 1 is challenging, particularly if the vendor's utility is complex or poorly documented. Step 2 can be difficult to implement due to environment constraints.

With AutoIt you have the choice of

installing the AutoIt environment and running the .au3 script file
compiling a .exe from the script

In my experience you'll have far less trouble getting the exe approved by change control than requesting deployment of the entire AutoIt installation.

1. AutoIt

AutoIt v3 is the latest available version. The download contains a useful help file and example scripts. The web also has plenty of documentation and forum discussions on most topics you might be interested in. I'll just cover off a few points I thought were important:

The language is a variant of Pascal
Line continuation is performed with _
Carriage return & line feed is represented by @CRLF
Variable $CmdLine[0] contains the number of command line parameters provided
ConsoleWrite() can be used to provide feedback and debug info
The included Au3Info.exe is a handy GUI spy for identifying those much-needed CLASS and INSTANCE values
Some functions are not suited to unattended use (see Q21 excerpt from AutoIt FAQ below)
If your script is too fast, add some Sleep(n) statements to let the GUI catch up
With the WinExists() function, AutoIt is able to handle GUI events that are too fast for a human to intercept or even perceive. Therefore writing a script based on manual user steps may be insufficient to properly control the GUI

Q21. Why my script doesn't work on locked station
A21. On locked station any window will never be active (active is only dialog with text "Press Ctrl+Alt+Del")In Windows locked state applications runs hidden (behind that visible dialog) and haven't focus and active status.
So generally don't use Send() MouseClick() WinActivate() WinWaitActive() WinActive() etc.Instead use ControlSend() ControlSetText() ControlClick() WinWait() WinExists() WinMenuSelectItem() etc.
This way you may have your script resistive against another active windows.and it's possibe to run such script from scheduler on locked Windows station.

2. SQL job

The SQL Server Agent is a service. By default, services do not interact with the desktop. This means your AutoIt exe can launch the utility but will be unable to detect or use any of its GUI elements.

To allow SQL Server Agent to interact with the desktop:

Start:Administrative Tools:Services
Right-click SQL Server Agent and select Properties
Select the Log On tab
Select Local System Account, check "Allow service to interact with desktop" and click OK
Restart SQL Server Agent (right-click and Restart)

Note the checkbox "Allow service to interact with desktop" is only available for the Local System Account. To use other accounts, they would need to be enabled to "Act as part of the operating system".

Potential problems are around whether Local System is suitable for running the agent service. Local System may not be suitable for other jobs on the same server. Some jobs may need permissions to other servers and it may not be appropriate to grant them to Local System.

A workaround would be to create a separate SQL Server Agent instance on the same server, just for jobs that need to interact with the desktop. However the additional instance will consume server resources.

A minor annoyance: a desktop-interaction-enabled SQL Server Agent will insist on keeping a Command Prompt window open while it's running.

SSIS - annoying animated data viewer creation

2009-12-15T11:51:00.002+13:00

Why is there an animation when creating a data viewer in Visual Studio? Was Thwarted Game Dev Guy given the job of implementing this?

Mild irritation becomes serious annoyance when a live issue is being tested on a box under heavy load and the lil data viewers are taking a minute each to parachute into the data flow.

Copying SQL Server 2005 push replication using Generate Scripts

2009-12-08T16:27:00.004+13:00

On the server where the existing replication is:

Right-click the publication (in SSMS, Replication: Local Publications) and select Generate Scripts...
Select To Create or Enable the Components
Choose file location and Save
OK and Close

On the server you want to copy it to:

If the server has never had replication before, manually create and delete a test publication (right-click Local Publications & select New Publication, choose some random stuff, then right-click the test publication and Delete). This will ensure the distribution database is created along with other stuff needed for replication
Open the script, replace the servers and logins as appropriate
Run on the target server

Using sp_msforeachtable on remote databases

2009-12-07T16:17:00.002+13:00

Yes you can! The only proviso is that your linked server must have RPC and RPC Out enabled.

Right-click the Linked Server and select properties
Select Server Options
Click RPC & RPC Out and OK

Then you can do wild stuff like

exec [remoteserver].[remotedb].[dbo].sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL"

SSAS and percentage measures

2009-11-23T15:01:00.004+13:00

Scenario

Let's say you have the following base measures that are the raw data held in the source system:

ListPriceDollars
StandardDiscountPercentage

The cube users require these two measures and an additional StandardDiscountPriceDollars measure to be available for use.

In this situation, it's easy to get stuck at the point of ensuring StandardDiscountPercentage displays sensible numbers at all aggregation levels. There are plenty of options to play with - aggregation functions, measure expressions, calculated members, etc. For people with more of a SQL than MDX background there's a lot to understand here. Throw in some null values and a parent-child relationship and you'll be torturing yourself trying to find the best way to represent this in SSAS.

Solution Option

One possibility is to completely avoid trying to aggregate percentages.

Change your source view or named query to calculate StandardDiscountPriceDollars (=Product.ListPriceDollars / (1 - (Product.StandardDiscountPercentage/100))
Don't use the raw StandardDiscountPercentage value in the cube - instead, create a simple replacement calculated member (= 1 - ([Measures].[Standard Discount Price Dollars] / [Measures].[List Price Dollars])

With a little bit of zero and null handling the job will be done.

Getting MDX from Excel pivot tables

2009-11-12T16:42:00.003+13:00

Data: Connections
Properties
Select Definition Tab
Add Log File=c:\temp\pivotlog.txt;
OK
Close
Refresh Pivot

Note: the definition text provided above is case sensitive! Particularly Log File

Displaying data source information in Reporting Services reports using expressions

2009-11-10T12:04:00.005+13:00

The following expression is about the best I have come up with:

=DataSources("myDataSource").DataSourceReference & " " & DataSources("myDataSource").Type

This will display:

Data source name - probably also "myDataSource" in this example. The full path is provided when the report is run in Report Manager
Data source type e.g. "OLEDB-MD" is an Analysis Services data source

Limitations:

You can't use expressions referring to data sources in the header or footer. But who wants the data source details displayed in the report body??
The data source reference has to be hard coded in every report. I'm not aware of any way to enumerate this to just fetch the first datasource. It would be fantastic if the following worked, but it doesn't:

=DataSources[0].DataSourceReference & " " & DataSources[0].Type

Pausing SQL Server transactional replication without losing queue contents

2009-11-02T11:38:00.004+13:00

To Pause:

Open SSMS and connect to the distributor
Disable and Stop the "Distribution clean up: distribution" job
Expand SQL Server Agent, right-click on Jobs, select View Job Categories and choose REPL-Distribution
Disable and Stop all listed jobs

To Continue:

Enable and Start all the jobs that were stopped

I have seen countless forum discussions for this topic but no succinct list of steps.

SSIS OLE DB Command error with NOT NULL constrained columns

2009-10-30T14:22:00.008+13:00

Symptom:

OLE DB Command fails with the following error reported to the package log:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.".

Possible Cause:

The data flow task is experiencing a constraint violation.

For a table ThisTable defined as follows:

ThisTableKey int not null
ThisTableValue varchar(10)

and OLE DB Command data flow task containing the following statement:

update ThisTable set ThisTableValue = 'UpdateVal' where ThisTableKey = ?

where ? has been mapped to a data flow column.

If the data flow column contains a NULL value then the task will fail because of the NOT NULL constraint on the column.

Diagnosis:

This is hard to diagnose in SSMS because the SQL statement above will run fine (and do nothing) when ? is substituted with NULL.

Instead add data viewers to the outputs of the offending OLE DB Command. You should see the following columns at debug time:

ErrorCode: -1071607702
ErrorColumn: 12345 (this is an example value)
ErrorDescription: The data value violated the schema constraint.

Open the package in a text editor and search for 12345. You will find a LineageId recorded against it. Search for the LineageId and you will find the column name (should be ThisTableKey).

Resolution:

Use a Conditional Split task to filter out all NULL values in the data flow column before the OLE DB Command.

SSAS Aggregation Design error

2009-09-21T19:02:00.003+12:00

Symptom:

The following error appears when deploying the cube or designing aggregations

The reference to attribute is not valid

Cause:

The partitions file has gotten out of sync with the cube file. Perhaps the cube file got checked back in to source control but the partitions file changes were undone.

Solution Options:

You may be able to hack the partitions file to clean up invalid attribute references.

Then again it may be easier just to roll back and reapply your changes.

SSAS processing error could be due to proactive caching settings

2009-08-27T14:24:00.003+12:00

Symptom:

"Errors in the high-level relational engine. The data source view does not contain a definition for the [object] table or view. The Source property may not have been set."

But the data source view doesn't contain this table.

Possible Cause:

A table has been specified for tracking in the notifications for proactive caching, but isn't available any more for some reason.

Solution Options:

Update the table name and location as required
Disable proactive caching for the partition

See http://www.mssqltips.com/tip.asp?tip=1563 for more details.

Stupid SSIS variable list bug

2009-07-20T18:58:00.003+12:00

Bring up the variable list and start editing the name of the first variable
Click the Name column of the variable list to sort the list
Hit Escape to cancel the edit

The variable name you were editing has now replaced the name of the variable that was top of the list after your sort! Cancelling the edit didn't stop this from happening.

Workaround: remember and reenter the variable name that was overwritten. Bleagh.

Adding folders to SSMS project

2009-07-02T12:19:00.008+12:00

Problem:

Can't create folders in a SQL Server 2005 Management Studio project.

It would be handy to be able to separate scripts into folders. Unfortunately only three default folders are available: Connections, Queries and Miscellaneous

Workaround:

Open the .ssmssqlproj file in a text editor
Copy one of the LogicalFolder tags e.g. Miscellaneous
Replace the Folder Name and Type. Make sure the Type value is different from all of the existing folders
Your new folder(s) should now be available in SSMS

Example insertion:

<LogicalFolder Name="Stored Procedures" Type="4" Sorted="true">
<Items />
</LogicalFolder>

Getting excel into a sharepoint list

2009-06-30T18:04:00.004+12:00

Symptom:

When you try to import an Excel spreadsheet into a Sharepoint list using the "Import Spreadsheet" option, the following message appears.

The website declined to show this webpage HTTP 403

Most likely causes:

This website requires you to log in.

What you can try:

Go back to the previous page.

More information

This error (HTTP 403 Forbidden) means that Internet Explorer was able to connect to the website, but it does not have permission to view the webpage.

For more information about HTTP errors, see Help.

Cause and Resolution:

Some have suggested reinstalling Office components on the server or formatting your spreadsheet in table format. Dunno, haven't tried.

Workaround:

Push from Excel as follows:

Data: List: Create List
Choose your list range and click OK
Data: List: Publish List
Enter url and name, and click Finish. All done!

SSIS can't find environment variables

2009-06-17T13:03:00.002+12:00

Scenario: Deploying SSIS packages that use environment variables and setting up a SQL Server scheduled task to kick them off.

Symptom: Can run packages manually, but the scheduled task fails.

Cause: Environment variables. You have probably logged off and on to the server a few times while attempting to run the packages manually, which means your login has the latest environment variables. However the SQL Server Agent may not have "logged in" recently and will be unaware of the new environment variables.

Resolution: Restart the SQL Server Agent (which is the equivalent of logging it back on to pick up the latest environment variables).

SSIS package location selection criteria

2009-06-16T13:56:00.002+12:00

Kirk Haselden's SSIS blog posts circa 2005-2006 are no more, so I am saving what little I have managed to copy or scrounge.

Advantages of Saving to Files:

Easier to do shared source control
Ultra secure when using the Encrypt with User Key encryption option
Not subject to network downtime problems (saved locally)
May escrow deployment bundles including miscellaneous files
Less steps to load into the designer
Easier direct access for viewing
May store packages hierarchically in file system
Projects in Visual Studio are disk based and require the package to be in the file system
Generally, a better experience during development

Advantages of Saving to SQL Server:

Easier access by multiple individuals
Benefits of database security, DTS roles and Agent interaction
Packages get backed up with normal DB backup processes
Able to filter packages via queries
May store packages hierarchically via new package folders
Generally, a better in-production experience

x:\desktop refers to a location that is unavailable

2009-05-16T22:46:00.003+12:00

If you get this error (I got it in Internet Explorer) then you probably had My Documents pointing to x: drive and moved the files to somewhere else (by right-clicking My Documents, selecting Properties and clicking Move).

One of the registry settings wasn't updated by the move.

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders

This probably still has a value of x:\desktop. Change it to %USERPROFILE%\Desktop

I'm using Windows XP Pro SP2, I have no idea which other OS's & versions are affected.

SSIS Breakpoint and other debug problems

2009-03-27T09:31:00.003+13:00

Symptoms:

When you add a or remove breakpoint from a breakpoint, the breakpoints of other tasks in other packages also change
SSIS ignores breakpoints or breaks unexpectedly during debug
Tasks execute unexpectedly during debug

Cause:

Duplicate Task Ids

When a package is created by copying another package, all the object IDs will be identical to the original package's IDs. This confuses visual studio with symptoms as described.

Resolution:

Install BIDS Helper if you haven't already, and use Reset GUIDs.

SSIS Parent Package variables - reading and writing in both directions

2009-01-29T16:04:00.025+13:00

This is an old old topic which I'm revisiting after a long time away, and I'm finding the available info quite bitsy, so here's my own writeup about it:

Discounted Option

The Set Variable Custom Task. It errors when referring to non local variables.

What's Left

Script Task in the Child Package (bleagh)

"The Parent Variable Read"

Public Sub Main()
Dim vars As Variables
'If package executed directly then no parent variables exist!
Try
Dts.VariableDispenser.LockForRead("ParentVar")
Dts.VariableDispenser.LockForWrite("ChildVar")
Dts.VariableDispenser.GetVariables(vars)
vars("ChildVar").Value = vars("ParentVar").Value
vars.Unlock()
Catch ex As Exception
'Do Nothing
Finally
Dts.TaskResult = Dts.Results.Success
End Try
End Sub

"The Parent Variable Write"

Public Sub Main()
Dim vars As Variables
'If package executed directly then no parent variables exist!
Try
Dts.VariableDispenser.LockForWrite("ParentVar")
Dts.VariableDispenser.LockForRead("ChildVar")
Dts.VariableDispenser.GetVariables(vars)
vars("ParentVar").Value = vars("ChildVar").Value
vars.Unlock()
Catch ex As Exception
'Do Nothing
Finally
Dts.TaskResult = Dts.Results.Success
End Try
End Sub

Notes

No need for Parent Package Configurations if the child and parent variables are differently named
There's no need to do anything in the Parent Package.
Don't put anything in ReadVariables or ReadWriteVariables! This will cause additional LockForRead, LockForWrite and Unlock method calls.

Wishlist - AFAIK these don't exist. I don't have time to give it a crack just now. It may be harder than it appears because nothing has been created in the past 3 years.

Set Parent Variable Custom Task
Set Multiple Variables Custom Task

Hard to Template

If you want to go large with a deep package invocation hierarchy then it will be somewhat tiresome to code. This is because local variables override parent variables if they have the same name, so you can't use the same package template for all hierarchy levels.

e.g. if you have a package template with variables var1 and localvar1, create two packages from it and call one package from the other, the child package will use the local var1 and ignore the parent var1.

This is unfortunate because it's becoming steadily easier to work with templates in SSIS using tools such as Pacman.

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

2008-11-12T17:00:00.005+13:00

(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

2008-11-01T22:03:00.007+13:00

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

2008-10-28T15:26:00.003+13:00

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

2008-09-12T16:09:00.013+12:00

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

2008-07-31T15:11:00.012+12:00

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)

2008-07-22T12:19:00.010+12:00

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

2008-07-17T16:53:00.004+12:00

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

2008-07-17T09:43:00.009+12:00

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

2008-06-15T21:07:00.004+12:00

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"

2008-06-06T22:24:00.002+12:00

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

2008-05-30T20:55:00.008+12:00

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

2008-05-26T11:16:00.007+12:00

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

2008-05-21T11:46:00.003+12:00

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

2008-05-20T13:55:00.001+12:00

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

2008-04-09T08:52:00.005+12:00

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

2008-03-07T14:31:00.016+13:00

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

2008-02-27T12:11:00.004+13:00

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

2008-02-22T11:36:00.005+13:00

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

2008-02-21T12:05:00.002+13:00

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

2008-02-07T14:02:00.001+13:00

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

2008-01-24T16:36:00.001+13:00

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

2008-01-22T16:25:00.001+13:00

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

2008-01-21T15:03:00.000+13:00

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.

8007000e error when deploying Analysis Services 2005 project

2007-12-18T14:12:00.000+13:00

I had some kind of machine specific project corruption, possibly to do with SourceSafe. Before troubleshooting your project, try deploying from another workstation - this worked for me.

Visual Studio using analysis services based report models is broken

2007-12-14T14:56:00.000+13:00

Designing reports using a report model based on an Analysis Services database seems such an obvious and natural thing to do.

Analysis Services provides a rapid querying engine
The report model requires no development to generate
Report developers can avoid having to learn MDX

Unfortunately this appears not to have been an area of focus for Microsoft. Prior to SQL Server 2005 SP2 it was workable but annoying, but now it is unusable.

"A connection cannot be made to the database. Set and test the connection string. Additional information: TODO - change code to use RsProxy object (Microsoft.ReportingServices.QueryDesigners)"

This error is thrown on the data tab of a Visual Studio 2005 Reporting Services project if:

Your data source is a report model generated from an Analysis Services 2005 database via Report Manager
You have SQL Server 2005 SP2 Client Tools installed on your workstation
You have manually added Perspective xml to your rdl code in an attempt to work around a known issue (Perspective not specified automatically for a report created in Report Designer)

If you remove the Perspective xml then the data tab won't error, but the prior known issue will kick in with one of the following errors when you attempt to execute the query or preview the report:

"An error occurred while executing the query. Failed to execute query. Additional information: Failed to execute query. (Microsoft.ReportingServices.DataExtensions)"

"The ‘PerspectiveID’ custom property for the ‘query’ perspective is either not set or is not set to the string data type."

Plenty TODO if you ask me.

Wherescape Red and Oracle Express - take 2

2007-10-23T17:20:00.000+13:00

The Validate Privileges option in Wherescape Setup Administrator (WSA) is supposed to assist you in getting your admin user granted up and ready to go.

So based on its recommendations you go into the XE home page and tick the following:

Create Database Link
Create Materialized View
Create Procedure
Create Table
Create View

and then run a little script as follows:

grant select any table to bob;
grant query rewrite to bob; --why the heck is this deprecated stuff here??
grant execute on sys.dbms_lock to bob;
grant select on sys.v_$session to bob;

and you should now be good to go, right?

Nope. If you haven't created any tablespaces and you don't select any on metadata creation, you will get a ton of errors related to availability of tablespace "USERS". Where did this come from? I didn't specify a tablespace anywhere and there doesn't appear to be a WSA default tablespace option, but WSA has gone and decided to plug it in the metadata creation sql.

The quick and dirty for getting round this is to give bob DBA rights. Thankfully this is just a tickbox away in XE.

Getting Oracle XE on Windows with Virtual PC to work

2007-10-19T17:01:00.000+13:00

Pretty much every time I have installed Oracle XE on Windows without taking any preparatory steps, I have had:

the home page go "page not found"
"shared memory realm does not exist" errors in sqlplus
port 8080 failed to come up in lsnrctl status/netstat -an find "TCP"
inaccessible Oracle odbc dsns with "tns:no listener" errors

My main preparation steps before installing XE on a Windows VPC:

Install the Microsoft Loopback Adapter
Use a static IP address
Disable all other networking

If this doesn't get it working, forget about checking whether the services are up - they will be.

Instead open up a command prompt and enter the following:

sqlplus / as sysdba
startup;

And this gets rid of the various problems I experience. This is in no way comprehensive. Oracle seems to be happy to leave their customers to trawl google for laundry lists of symptoms and fixes compiled by various bloggers and tweakers.

This isn't a bad article (though not specific to XE).
http://www.dba-oracle.com/oracle9iAS_tips_windows_service.htm
Neither is this
http://psst0101.wordpress.com/2007/06/13/getting-oracle-10201-to-start-automatically/

isnumber in sql

2007-09-28T14:55:00.000+12:00

http://laurentschneider.com/wordpress/2007/09/isnumber-in-sql.html

select x,
case when
regexp_like(:x, ‘^[+-]?(\d+\.?\d*\.\d+)([eE][+-]?\d+)?$’)
then to_number(x)
end n
from t;

Wherescape Red and multi stage ETL

2007-09-24T17:27:00.001+12:00

In Red, you associate dimensions with a fact in the Stage table. If your load table has data that needs to be transformed before it can be used to join to the appropriate dimension business keys then you may want a 2 step stage process to keep the transformations readable.

For instance if your load table has a column that contains data in the form ddmon (e.g. 01SEP) this can be converted to a date before joining to dim_date to get the dim_date_key. So stage_table_1 would have a date column derived from the ddmon column and stage_table_2 would have the date column plus a dim_date_key column using the relevant join criteria.

Of course stage_table_2 could be derived in one step but my preference is to keep the transformations simple.

Wherescape Red and data repositories

2007-09-19T11:17:00.001+12:00

I wanted to create a separate data repository from WslWarehouse (which is full of metadata), so I created a new SQL Server database instance called Test_Warehouse on the same server.

I had to create a system DSN called Test_Warehouse pointing to this instance. I don't see the need for this step. Red supports SQL Server in theory which means that I should be able to specify the server, database and credentials somewhere.

Then in Red I right-clicked Connections, selected New Object and specified properties as follows:

Name of object: Test_Warehouse
Connection Method: IP
ODBC Source: Test_Warehouse
(No need for credentials as I specified Windows Authentication in the DSN)

The next step is to get the load table to create itself in Test_Warehouse. I thought that Change Connect/Schema would do the trick but it made no difference. After much messing around and consulting help files I still didn't figure out how to do it. So it's back to mingling meta and data...

Wherescape Red and fixed width text source

2007-09-19T10:49:00.000+12:00

Two annoyances:

1. The data load wizard doesn't let you throw anything away in the source. You have to create junk columns to hold separators and other unneeded file content.

2. If you use the Back button in the data load wizard, it appears that you lose your column specifications. Actually the column definition has already been created and you are creating a duplicate. Clicking ok to Create and Load when you finish the wizard will not work because of this, and you will have to go back and delete the old column definitions.

If you get the following errors, create a c:\temp directory.

Attempt to bulk insert has FAILED![Microsoft][SQL Native Client][SQL Server]Cannot bulk load because the file "c:\temp\wsl6.fmt" could not be opened. Operating system error code 3(The system cannot find the path specified.).

[Microsoft][SQL Native Client][SQL Server]Cannot bulk load because the file "c:\temp\wsl6.fmt" could not be opened. Operating system error code 3(The system cannot find the path specified.).

.

Wherescape Red and creating facts

2007-09-19T10:27:00.000+12:00

Much the same as dimensions but I got stuck going from load to fact - I eventually figured out that stage tables are not just a nice to have, they are pretty much essential as there are wizard steps that won't appear when constructing facts directly from load tables.

Wherescape Red and projects

2007-09-18T15:22:00.000+12:00

At first I couldn't figure out how to drag-drop or multi-drag objects into a newly created project. Surely I wouldn't have to do them one at a time?? Turns out the trick is to select the container (for instance, Load Table) then make my selections from the middle pane, right-click and Add to Project.

Wherescape Red and creating dimensions

2007-08-23T10:13:00.000+12:00

My data source is a very denormalised reporting-style source system extract. I need to pluck out the dimensions and facts that are embedded in this source data, and I was hoping Red's much touted code generation would help me through this.

Dimension #1
- Dragged a load table into the dimension middle panel and specified the name
- Selected Normal dimension type
- Clicked OK to accept definition
- Clicked Finish to avoid creating the dimension based on the load table
- Deleted lots of irrelevant columns
- Right-clicked the dimension (in the left panel) and selected Create (recreate)
- Right-clicked the dimension and selected Properties
- Specified Update Procedure as ***Create New Procedure ***
- Selected the business key column
- Right-clicked the dimension and selected Update

Phew, I'm done! It didn't take long but I had a lot of steps to remember. It strikes me that a suitably constructed wizard could have completed a cycle of creating multiple dimensions and facts from one load table.

Dimension #2
More of the same, except that the Update failed with error:

-2 dim_2 update FAILED with error 2601 Step 100. Error Msg: Cannot insert duplicate key row in object 'dbo.dim_2' with unique index 'dim_2_idx_A'.

The dim_2 table was partially populated, so it appears a specific cursor row upset it partway through the data load. My first guess was there were multiple cursor rows for the same business key. However queries on the load table didn't find any.

Turns out that the generated update procedure doesn't handle nulls. To fix this I had to:
- right-click the dimension
- select properties
- click Rebuild for the update procedure
- tick Allow where clause editing and click OK to leave the business key as is
- type where business_key_column is not null and click OK

I guess there may be a tick box somewhere to make the procedure ignore nulls but I couldn't find it.

Now on to dimension hierarchies! I right-clicked the dimension and selected Add Hierarchy and it only offered me the option to reorganise the existing fields. I had to create separate dimension columns for calculated hierarchies by right-clicking the dimension and selecting Add Column.

Comments:
- The fields are mostly self explanatory but not very typo-proof, being free text fields.
- At various points it is necessary to update the dialog box which is done by clicking the Update button. Annoyingly this closes the dialog box and I had to reopen the column properties to be able to continue defining the column.
- The transformation tab could potentially hold some serious sql but is fixed size with limited IDE functionality.

Wherescape Red and Excel data source

2007-08-22T14:31:00.000+12:00

I wanted to use a multi tab multi column Excel file as a data source. I didn't do any prior reading of the help file or documentation, apart from my previous walk through of the tutorial. Call it an ease of use test :)

I dragged the xls file in to the load table area and was presented with a dialog box Specify the type of load required. I selected the 2nd option File Load (columns parsed) which superficially appeared to be the right choice, but nothing appeared to happen. I couldn't get the corresponding load table to be displayed in the browser.

I closed and reopened the browser, and the load table was displayed in the browser. However as soon as I tried to look at its properties etc I got the message The specified load table could not be found. I performed a validity check of the metadata and a problem was reported: There are no objects in ws_load_tab with an object key of n.

I wasn't prompted with any metadata cleanup options and I couldn't find any repair functions on the menus. So I opened up the wslwarehouse database in SQL Mgt Studio* and poked around in the metadata, eventually identifying the load table object definition in ws_obj_object. I deleted the offending row and ran another validity check which advised that the metadata was clean.

I've revisited the help file and I can't find anything specifically saying Excel isn't usable as a source, but I can't find instructions for loading Excel either. I think I can assume by omission that it's unsupported. Regardless this was pretty ungraceful error handling.

So I saved each xls tab as a separate tab-delimited text file and dragged them in. This time a dialog box popped up and the load definition process started. My thoughts on the wizard:

- It doesn't make any effort to identify the delimiter, header row or row separator. And char(9) isn't adequate to specify a tab; it has to be CHAR(09)
- It makes rather poor data type suggestions given that it's obviously sampling the data.
- Manual entry of data types aren't added to the data type drop down.
- The data happened to have a column header called Procedure which caused table creation and population to fail because it's a sql keyword. The wizard didn't identify this and change the column name to something else.

* Yes I've given up on XE/APEX which has become unavailable on reboot too many times for my limited patience. SQL Server doesn't die inexplicably on reboot, doesn't get upset when used on a virtual machine with no network adapters, doesn't stop working because it was installed by a domain admin, etc etc.

The Wherescape Red Sucks Honeypot Post

2007-08-20T23:01:00.000+12:00

I've been writing boring notes to self in this blog, fully expecting nobody to bother commenting. Now that someone has actually written a comment, I'm completely freaking out! I can't help but suspect that the commenter has undisclosed interests and is engaging in viral marketing. A lot of vendor-aligned consultants these days are expected to provide product discussion forum feedback as a background task - perhaps some vendors have stipulated public comms responsibilities too for their consultants?

Apologies if you think this is all too cynical and even somewhat misanthropic.

Oracle Express and VPC

2007-08-20T10:47:00.000+12:00

The Apex site was unavailable afte reboot, and sqlplus returned this error:

ORA-1034: ORACLE not available
ORA-27101: Shared memory realm does not exist

This thread discusses possibilities and suggests searching on OTN forums but the forums are currently unavailable!

I think it may be something to do with assigned ip address but discussion suggests Oracle should still be available via sqlplus.

Wherescape Red and Oracle Express

2007-08-17T13:54:00.000+12:00

Before installing the Red tutorials, do the following to save yourself manual tidyups:
* Create tablespaces called DATA and INDEX
* Create a user called wtutorial

What does this error mean during tutorial install?

insert rows into ws_hie_link(hl_hie_key, hl_obj_key, hl_child_key, hl_parent_col_key) values(ws_hierarchy_seq.currval, 71, 027city#) [Microsoft][ODBC Driver for Oracle][Oracle]ORA-00917: missing comma

Haven't figured out what to do about Red's attempts to create bitmap indexes.

PerformancePoint CTP4 appears overnight like a tent city

2007-08-17T10:22:00.000+12:00

http://connect.microsoft.com

Announcement came in the form of a handful of posts by independent bloggers.

I'm guessing that a VPC image will be available in about 3 weeks time based on previous track record.

Don't use @ symbol in passwords if Oracle is in the building

2007-08-16T14:30:00.000+12:00

The standard administrator password for Microsoft Virtual PC images is pass@word1

If you install an Oracle database on one of these images then the administrator will never be able to connect to the database.

connect user/password@destination

Forum post about asp.net locales

2007-08-10T14:39:00.000+12:00

From bhughes10 on 9/28/2006 4:51:02 PM
I saw a question from Norman Yuan several months back with the same problem I'm having now. Never saw an answer.My currency that should be displayed as $200.00 is displayed as A200.00 randomly when my production server is under load. It doesn't happen on the dev computers. An IIS reset temp fixes it but soon returns. I'm running ASP.NET app, .NET 1.1, developed on VS.NET2003. Windows Server 2003 Std. with latest SP, IIS6.0

From bhughes10 on 10/4/2006 4:43:01 AM
It's been 2 days with heavy loads on my server and no problems. I took your suggestion and created a new application pool for the site. Thanks Carl.
"Carl Daniel [VC++ MVP]" wrote:
> Norman Yuan wrote:
> > Still no solution here. Originally, I though it could have something
> > to do with our incapable server (PIII700Hz, 512MB, running Win2003
> > IIS6 and SQL Server2000 on the same box). But after upgrading to a
> > brand new, powerful server, the problem is still there. The $ sign
> > randomly changed, but not alway on the same web page. That is,
> > browsing the same page with different query string parameters, the $
> > sign sometimes is OK, sometimes is not.
> > Very frustrated with no cause found.
>
> This is caused by something running on a threadpool thread that changed the
> default locale. We ran into this issue with an ASP.NET application as well
> and through trial and error identified another ASP.NET application running
> in the same app pool that was changing the locale to German for some reason.
>
> There are two approaches you can take that might help:
>
> 1. Explicitly specify your intended locale (i.e. IFormatProvider) every
> where you convert numbers to strings.
> 2. Separate all applications into their own completely isolated application
> pools to keep locale changes from one application from "leaking" into
> another.
>
> -cd

P2V, SSIS, asp.net and datetime

2007-08-09T17:38:00.000+12:00

Recently I did a P2V of a production Windows 2003 server in order to create a dev environment.

Among other things it comprises an asp.net site that calls an SSIS package.

When I attempted to debug the existing SSIS package in visual studio, it failed with "string was not recognized as a valid datetime". When I checked the regional settings of the windows server I found they had not survived the P2V process and had reverted to US english. This post discusses the language translation issues that can occur with SQL Server, so as per previous blog post I updated the windows default regional settings. I left SQL Server as us_english as this is how it's set up in prod.

End to end debugging was fine after this. However when I deployed assemblies into the dev environment but I got exactly the same error at the point of calling the SSIS package from asp.net. Suspicion immediately fell on the app pool identity. However its database user has the same default language as all the other users - us_english.

I then noticed that Sharepoint on the same box was displaying US style dates. So it appeared that IIS or asp.net had a different default locale from windows! But no, updating the Sharepoint locale info didn't fix the asp.net site. It turns out that Sharepoint sites have their own locale info which is drawn from Sharepoint's own settings.

I decided to repair IIS but found that I couldn't get to Add/Remove Windows Components from the Add or Remove Programs control panel - I got an error as follows:

Setup library ocgen.dll could not be loaded, or OcOMEntry could not be found. Contact your system administrator. The specific error code is 0x7F.

Presumably this was another P2V side effect. I ran sfc /scannow but this didn't help.

Giving up on this, I went back to investigate the site's asp.net global configuration and noticed that the application settings had dates stored in the local format! So the translation was happening on the site itself. On the application tab I found that the culture and UI culture had changed to af-ZA (Afrikaans). Actually I suspected the values were blank and the dropdowns were just displaying the first entries on the lists. So I updated them to local culture and reset iis. The sites were now using local dates.

Some forum post

2007-08-09T13:05:00.000+12:00

RE: SSIS, relative path configuration file references and asp.net David Norman2/28/2006 7:19:28 PM

Sorry, this information is not correct. I was viewing logs that excluded OnWarning messages.
As far as I am currently aware, the only guaranteed place to put configuration files where they will be found by an asp.net site using an app pool identity other than Admnistrator is in \windows\system32\inetsrv

"David Norman" wrote:
> Config files for child packages called from asp.net will also be found during
> execution relative to this directory:
>
> \Documents and Settings\App Pool Identity User\
>
> So to have two separate package collections on the same server e.g. for test
> and live, you need two sites using different app pools with different
> identities. Otherwise both collections will use the same config files.
> > I think the way to structure this so you still have the flexibility to run
> the packages both directly and via the site is to put copies of the config
> files in the app pool home dir. The downside is you can get out of sync and
> access to the directory needs to be opened up to more people.
>
> But I won't add my voice to the chorus of SSIS bloggers who trumpet the need
> to follow disciplined practices - template packages, carefully designed
> folder structures, etc. These are just workarounds for a not very easy to use
> environment.
>
> One thing I believe IS good practice is to not edit live packages directly.
> If you're happy to hack away at the config file paths in live .dtsx files
> then you can you ignore these posts entirely.
>
> Hope this helps somebody, it's taken me a long time to figure all this out.
> David
>
> "David Norman" wrote:
> >
> Found it!!!!! Desperation wins through!!!
> >
> > \Windows\system\inetsrv (app pool location)
> >
> > But it's not going to be a very well structured package environment. And I
> > don't know if I can different locations for test and live will be possible.
> >
> > All in all, fairly ridiculous if you ask me.> > > > "David Norman" wrote:
> >
> > > I have a package collection with one parent package that calls several child
> > > packages that share a common configuration file referred to using a relative
> > > path (everything is in the same folder).
> > >
> > > If I put the child packages and config file in a different folder execute
> > > the parent package, the child packages fail to find the config file. If I put > > > the config file with the parent package, the child packages find it.
> > >
> > > With an asp.net site - the child packages don't find the config file in
> > > either their own or the parent package folder.
> > >
> > > This has led me to believe that the location where the "executable" is
> > > launched dictates where the config file needs to be. On this basis I have
> > > tried putting it in the following (increasingly desperate) locations:
> > >
> > > virtual dir root
> > > virtual dir bin
> > > site root
> > > drive root
> > > \Program Files\SQL Server\90\Binn (location of MsDtsSrvr.exe)
> > > \Windows\System (location of W3SVC)> > > the "My Documents" directory of the app pool identity
> > >
> > > One exception is that the config file is found if the app pool identity is
> > > an administrator of the SQL Server box - but this is not an acceptable
> > > solution. However at the rate I'm removing security in my search for a
> > > working setup, it won't be long before everyone's an administrator anyway!
> > >
> > > Can anyone give me any guidance?
> > >
> > > Ta in advance,

Multiple SSIS environments on the same server

2007-08-09T11:19:00.001+12:00

Got Dev and Test (and even Prod) environments on the same box? Want to avoid hacking the package xml when promoting SSIS solutions? There are a couple of configuration options:

1. XML configuration file

The key here is to use a relative path. The wizard guides you to click the Browse button and specify an absolute reference e.g. c:\ssisdev\config.dtsconfig. Instead, just type in config.dtsconfig. SSIS will look for the config file in the package location.

Depending on how you structure your solution, you don't necessarily want your config files in the same location as the packages that use them. I suggest you give up on this now and accept all config files will have to be in the same location as the packages that use them.

This is because longer relative paths such as ..\config.dtsconfig don't work (this particular example saves the config file to C:\Program Files\Microsoft Visual Studio 8\Common7 !!!)

2. SQL Server

This is a two phase approach. Firstly, set up a SQL Server configuration table. As part of this process you will reference or create a database connection. Then (and this is the cunning bit) create an XML config file with a relative path (as per option #1) that contains the connection string for the connection. You can then specify different configuration table locations in the xml config files.

This option also uses xml config files and is hampered by the same difficulties as option #1. You will need a separate config file for every package location, and you will need to maintain these config files individually. You could design to ensure your config files are all identical and write a script to copy them as part of the build; whether this is worth the effort depends on your solution.

SSIS package deployment does SOMETHING

2007-08-09T09:08:00.000+12:00

Everything I read out there says that SSIS package deployment to file system does nothing more than a file copy.

I created some SSIS packages and called them from an asp.net site on the same box. However the asp.net site couldn't find the packages until I deployed them to the file system.

So deployment does something, either to the permissions of the deployed packages or by registering them as available somehow. Is there a hidden GAC equivalent for packages (GPC??)

SSRS setup

2007-08-06T13:51:00.001+12:00

Tried to bring up http://server/reports and got the error:

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError) Get Online Help Bad Data. (Exception from HRESULT: 0x80090005)

Following instructions here got me nowhere as I got the same error on attempting to extract the symmetric key.

Apparently this is the wrong approach for SQL2K5, I found in the following comment:

For Sql Server 2005 you can change, backup and restore via Reporting Services Configuration Manager.
If you have no backup of the Encryption key you can delete the key and re-create using the change option. BUT BE WARNED!, you must re-enter connection strings and stored credentials in the reports and shared data sources after this.
Refer to the follow technet article BEFORE doing this.

Unfortunately I couldn't get the Initialization to change from x to tick. So I reinstalled SSRS and went through the Reporting Services Configuration Manager step by step to reconnect SSRS services to the SSRS database. At database setup it moaned about existing keys and the Initialization icon stayed as an X. So I went to the Encryption Keys step and deleted keys. At first this didn't look like it had improved things as the Initialization still appeared to have failed, but a refresh updated it to a tick.

Business Scorecard Manager development notes

2007-08-03T17:07:00.000+12:00

It's a typical requirement to have a scorecard that needs regular updating to refer to a new current period. It's also typical that the database contains data for future periods which means Time Intelligence can't be used to get the scorecard to dynamically update itself. So what options does MOBSM give us for programmatic control?

For the scorecard, all the necessary objects are exposed as per the sample code which updates the current and previous members for all kpis in all scorecards.

IBpm pmServer = PmService.CreateInstance(BsmServerUrl); ScorecardCollection scorecards = pmServer.GetScorecards();
for (int i = 0; i <>
{
Scorecard scorecard = scorecards[i];
{
KpiCollection kpis = pmServer.GetKpisFromScorecard(scorecard.Guid);
for (int j = 0; j <>
{
Kpi kpi = kpis[j];
KpiMeasure kpiactual = kpi.Actual;
kpiactual.TimeIntelligenceCurrentMember = new Member(specify member here);
kpiactual.TimeIntelligencePreviousMember = new Member(specify member here);
pmServer.CreateKpi(kpi);
}
}
}

Unfortunately it's not quite so easy to update the periods displayed in chart report views. The Kpi.ReportView.CustomData attribute is an xml string that contains the report view definition, and lurking within this is more encapsulated xml containing the chart definitions. By the time you're finished you will know xml traversal like the back of your hand (and if you have the same attitude to xml as me, you will like the back of your hand a LOT better!).

Addendum:
I realised that I'd be a lot better off adding some calculated members and hierarchies to the time dimension and referring to these instead of updating the current and previous time intelligence members for the scorecard and traversing the chart's IncludedMembers.

The techniques described above are more appropriate for dynamic update based on user selection than as part of a period end process.

SSAS development notes

2007-08-01T09:56:00.000+12:00

When the data source has changed, use menu option Data Source View:Refresh to update the dsv.

After creating dimensions, open the Cube definition and right-click in the Dimensions tree to add the new dimensions.

Deployment error:Failed trust relationship between workstation and server
http://prologika.com/CS/blogs/blog/archive/2007/06/11/trust-relationships.aspx
Fix: Remove all old domain references from roles

If you're having problems getting changes to display in your cubes, reprocess and redeploy absolutely everything. Reconnect and refresh the cube browser too.

I updated the solution deployment target to a different server but it kept reverting back to the previous target. Sourcesafe informed me that the only file that changed when I updated the deployment info was the .suo file. Further reading on the web suggests that source should be managed from within Visual Studio only, not via SourceSafe, and things are more likely to work properly.

Some guy wrote in a newsgroup:

Figured out how to get the members to appear in date order - it's not as simple as setting attribute orderby properties. The steps I took are as follows:

open the time dim
dimension:add business intelligence
specify attribute ordering
for my mmm-yy attribute, select ordering attribute = new attribute
select a suitable ordering attribute e.g. one with date data type
new attribute appears: "ordering mmm-yy onwhateverorderingattributeyouselected"
next and finish

Hope that helps somebody
David

"David Norman" wrote:
> If I have date data 2005-01-01, 2005-02-01, 2005-03-01 in Excel and use this
> as the X axis for a chart, I can format it as "mmm-yy" and the axis will> display Jan-05, Feb-05, Mar-05.
>
> How do I achieve the same result using the OWC chart component and cube data> source?
>
> If I drag the date dimension level from the field list to the category axis
> containing the same data, I don't have the equivalent date formatting options.
>
> If I create a named calculation Left(Month,3)+'-'+Year then the members list
> in alphabetical order instead of date order i.e. Feb-05 appears before Jan-05> on the axis.
>
> Any suggestions appreciated,
> David

New ordering attributes can break existing ordering that use the same attribute. It may be necessary to remove all ordering attributes and recreate them together.

Attaching to network printer without directory or dns assistance

2007-07-27T09:57:00.000+12:00

If you visit another company's office and connect your laptop to their windows network, you'll find that you aren't a member of their domain which can make a lot of typical office activities a bit more difficult to achieve. Printing is a biggie.

First step is to find out from someone on the domain what the ip address of the printer is. Get them to bring up the Printer Properties dialog and go to the Sharing tab. The printer will have a sharing name. Pinging the sharing name from the command prompt will give you the ip address.

Now you can Add Printer. Here's where is gets a bit counterintuitive: you select Local Printer (not Network Printer - I always thought local meant directly connected to your PC, but there are more options in this wizard path than I realised). Select Create a New Port and choose Standard TCP/IP Port. Enter the IP address (the port will be completed for you).

Then you have to select the Manufacturer and Model. My experience has been that if I choose something in the ballpark then plain old document printing will usually work. e.g. the model HP Laserjet 4000 Series PS provides printing for a HP Laserjet 4345 mfp printer, but its other fancy features may be unavailable.

The guidance out there on the web is to choose PS first followed by PCL6 followed by PCL if confronted with multiple similar choices.

SSIS development notes

2007-07-27T09:35:00.000+12:00

Mirror any settings you change in the package in your development package configuration. Otherwise you risk the config values blatting your changes.

Click OK to save transformation changes, don't just close the editor!

When tidying up the dtsConfig xml formatting, don't put newlines and tabs between the ConfiguredValue tags.

This error:

Error at Create x Table [DTS.Pipeline]: The index is not valid.ADDITIONAL INFORMATION:Exception from HRESULT: 0xC0048004 (Microsoft.SqlServer.DTSPipelineWrap)

just means that SSIS is playing up. Recreate the offending data source (I wonder if any of the service packs fix this?)

When copying and pasting data flow elements, unselect the copied elements before pasting.

You've added a dozen package variables in the wrong scope, and the scope can't be edited in the variable view. Aaargh! Do you have to delete and recreate them? No, you can open the code designer view of the package, find the xml for the variables and move them to the appropriate place. It's not really much easier I'm afraid.

When columns are added to the data source, the Union All transformation fails to recognise their existence. This transformation has to be recreated.

If you're developing on a virtual machine that has been created by restoring from a windows backup, the first tab of the Regional and Language Options control panel may revert back to US English. Changing all the settings on the front tab will get your usual desktop apps behaving properly, but SSIS may still be confused! If you have a SQL task that calls a stored procedure with a date parameter, you may find that the sp will receive a date in the wrong format. The way to fix this is to select the Default User Account Settings tick box on the Advanced tab of the Regional and Language Options control panel.

When executing solutions or packages within Visual Studio I get duplicate designer windows opening up. The icons for these dupes are the same as for miscellaneous files. The main nuisance is if after execution I decide to make further changes and I mistakenly change the dupes instead of the originals. What are these windows for?

Restoring windows backup didn't restore all regional and language options

2007-07-26T11:34:00.000+12:00

Strange, restoring into a vanilla os from a backup file didn't bring across the regional settings on the first tab of Regional and Language Options.

I only noticed this because I hit a documented SSIS error.

SQL Server 2005, Visual Studio 2005 and Visual Studio 2005 SP1

2007-07-25T17:42:00.001+12:00

If you have installed both SQL Server 2005 and Visual Studio 2005 then probably both of the following will be installed:

Microsoft Visual Studio 2005 Premier Partner Edition - ENU
Microsoft Visual Studio 2005 X Edition - ENU

Sure it's untidy that the VS2005 X install doesn't detect and replace Premier, but it hasn't caused any problems (so far). If you're like me, you haven't dared remove Premier because you're worried about regression, and probably you just use X because it is a superset of Premier anyway.

However now it's time to put Visual Studio 2005 SP1 on. What's it going to do?

It asked me if I want to install on Premier. But I don't use Premier! Oh no, it's chosen the wrong one! I cancelled the installation and to my surprise it then asked if I wanted to install on X? Phew! But now I'm not sure if I need to install on Premier also. Considering it's supposed to take a minimum of 90 minutes, do I really want to sit through that twice?

I decided to install only on X, and hit a "digital signature check" error. This post describes the fix for a software stack that's very similar to mine.

More P2V travails

2007-07-25T10:02:00.000+12:00

Before restoring from backup I changed the registry for intelppm.sys as per previous posting. The settings didn't revert on restore so the stop error went away.

Since stop errors that require registry fixes are a nuisance to sort out, it's tempting to preemptively disable intelppm.sys and processr.sys as part of the basic steps, immediately after firing up the restore vmc.

The runtime error I posted about previously was solved by repairing dotnetframework2.0. Not really sure why it broke. Probably for the same reason I was prompted for the dotnetfx install during the Win2K3 repair.

Better (but probably not best) practice for Windows/Virtual PC P2V

2007-07-24T13:57:00.001+12:00

Basic steps:

Back up the physical system to a bkf file
Create a vanilla vhd using the operating system of the physical system
Make a copy of the vanilla vhd to a restore vhd
Create an empty backup vhd
Mount the backup vhd using vhdmount
Copy the bkf file into the backup vhd (might need to compact or split it)
Unmount the backup vhd
Configure a vmc with restore vhd as disc 1 and backup vhd as disc 2
Fire up the vmc and restore the backup
Reboot, cross fingers and troubleshoot device issues

If there are issues:

Configure another vmc with the vanilla vhd as disc 1 and restore vhd as disc 2

Fire up the vmc and use windows tools to copy required files, edit offline registry etc

Don't faff around with recovery console or utility boot discs more than necessary - it's all a big pain and you'll end up having to juggle various iso's and vfd's.

Getting big files into vhds via vhdmount

2007-07-23T17:05:00.001+12:00

Having trouble using or copying big files from shared folders or linked drives because of throughput or memory constraints?

Mount a disc ,and copy in your files, then unmount and commit changes. Vhdmount then takes a loooong time merging undo discs, but at least it works!

Windows explorer integration for vhdmount helps out a lot with this.

I find this a great way to get image or backup files into blank vhds. I partition with the appropriate windows setup cd and then do a quick-format as prompted on mounting the vhd, followed by the file copy.

My experience is that if the file is really big (say 10GB) then at the end of the copy it will sit there saying "5 seconds remaining" for a long time. I haven't been patient enough to let it sit for a day to see if it completes, but there's very little activity of any kind so I think it just dies.

Resetting recovery console password and mounting vhd

2007-07-23T09:49:00.000+12:00

Instructions at petri.co.il and http://support.microsoft.com/kb/322672.

Didn't work with login used due to error: 0x32 Request not supported.

Googling for this error returned this page which unfortunately is only available in Italian. Excerpt:

Attenzione: non è possibile modificare la password di DSRM mediante l’utility NTDSUTIL nel mentre si opera in DSRM (Errore: “Setting password failed. Win32 Error Code: 0x32. Error Message: The request is not supported.”). Viceversa è possibile farlo da DSRM mediante la sequenza di tasti Control+Alt+Del e cliccando sul bottone “Change Password...” oppure inserendo il comando seguente: “net user Administrator *”.

Babelfish translated this as:

Attention: Error is not possible to meanwhile modify password of DSRM by means of utility the NTDSUTIL the work in DSRM (: "Setting password failed. Win32 Error Tails: 0x32. Error Message: The request is not supported."). Viceversa is possible to make it from DSRM by means of the sequence of Control+Alt+Del keys and cliccando on the button "Change Password..." or inserting the following commando: "net user Administrator *".

In the end I didn't need to figure this out because loginrecovery.com succeeded in deciphering the local administrator password.

I put a load of command line registry editing tools on a disc and mounted it on the guest. Unfortunately they all page faulted - they're obviously pretty old and haven't been updated to latest windows versions. I'm really surprised that I couldn't find a registry editor that can be used in the recovery console.

So then I decided to try and mount the vhd on the host so I could use the host's regedit to manually fix the guest's registry file.

I downloaded and installed vhdmount (part of Virtual Server) and from the command line I did the following:

cd c:\program files\microsoft virtual server\vhdmount
vhdmount /m /f vhdfile.vhd V:

which gave me the error "Invalid command line parameters."

I then tried just plugging in instead of mounting

vhdmount /p /f vhdfile.vhd

which resulted in error "Failed to plug in the Virtual Hard Disk (VHD). The specified file is not a valid VHD."

I then typed SET VHD_MOUNT_TRACE=1 and tried this command again which gave me:

[Info] Default user locale is 0x1409[Warning] Dictionary VhdMountStrings.dll doesn't exist.[Info] GetSCSIAddress called for VHD: vhdfile.vhd[Info] Verifying file: vhdfile.vhd[Error] Query failed: 0x57[Error] GetSCSIAddress failed. error code = 0x37[Info] MountVHD called for VHD: vhdfile.vhd[Info] Verifying file: vhdfile.vhd[Error] Invalid VHD signature found: 0x00[Warning] Footer of the given VHD is incorrect. Hence checking the VHD's header
The Virtual Hard Disk is successfully plugged in as a virtual disk device.[Info] VHDMount successful.

"Successful", huh. VHDMount is obviously very pleased to come anywhere near my vhd without crashing itself.

The vhd is split so perhaps this explains incorrect footer. I then tried to fire up Virtual PC to compact the vhd, but it hanged on the splash screen. So now I know what VHDMount was successful at: giving itself exclusive access to the vhd without doing anything useful! I had to kill VPC, unmount the vhd and start VPC again.

P2V dramas with Virtual PC and NTBackup

2007-07-19T08:54:00.000+12:00

I've been following the instructions in this discussion group posting with the exception that I'm using Virtual PC.

Installing a vanilla os on to a vpc is obviously no big deal. But it got harder sooner than I would have liked - restoring the bkf file. I have hit the following problems:

I couldn't restore from a a shared folder due to known issues.

I tried to copy the bkf file from a shared folder to the guest c drive but I kept getting a device i/o error. It appears that the host needs to have almost as much memory available as the size of the file being copied. Obviously this is problematic for bkf files that can be in the multi gigabyte range.

I then split the bkf file into multiple parts with winzip and minimised the amount of guest memory to maximise the chances of the copy completing successfully.

Finally I could restore the bkf file!

However when I restarted, I got an error box at the login screen as follows:

Microsoft Visual C++ 6.0 program

C:\Prog...

This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information.

Annoyingly this error box was right in front of the login box and I couldn't move the error box or close it (the mouse stopped working).

I tried entering login credentials blind without success (couldn't see what domain was selected and couldn't see the contents of any dialog boxes that popped up).

Finally I rebooted the image in the hope the error or the mouse failure would go away but I got a boot error on a black screen instead:

Windows could not start because of an error in the software.
Please report this problem as :
load needed DLLs for HAL.
Please contact your support person to report this problem.

Aha! Here was something referred to in the instructions. I booted from the Win2K3 CD and went to the recovery console to replace the hal file but found that it wouldn't take the current administrator password.

It turns out that the recovery console admin password will be the one used when Win2K3 was initially installed, and subsequent password changes don't affect this. I don't have that password and had to use http://www.loginrecovery.com/ to get the password back.

While I was waiting 48 hours (to get the password for free) I impatiently tried to repair the server using the Win2K3 CD to see if that would sort things out.

A required file called cpqteam.hlp could not be found. Who requires a help file?? Cancelled.

The next one was a bit tougher to figure out: install.exe. The default path on the c drive was c:\windows\microsoft.net\v2.0.50727\Microsoft .NET Framework 2.0. So I downloaded dotnetfx.exe, extracted the files, converted them to iso, captured the image as a cd and selected the file.

The repair chugged on a bit further and then crashed dramatically with stop error 0x000000CE DRIVER_UNLOADED_WITHOUT_CANCELLING_PENDING_OPERATIONS for file intelppm.sys.

Virtual PC Guy reckons I should disable the driver in the registry but with a non booting guest and no access to recovery console (at least until I hear from loginrecovery) I think I've hit the wall...

More P2V info from some forum

2007-07-17T15:27:00.000+12:00

“The Path To Fast P2V” – The Quicksilver Project

We all have tons of physical machines we want to migrate to Virtual Hosts, and we all want to do it in a smooth low administrative cost process.

Well, all we have to do is to use the VSMT right? (Virtual Server Migration Toolkit). The problem is that its too complex to use it, and in the end the hours you spend to run all the VSMT process is maybe even bigger than the one you would take to set it up the hole thing again without VSMT at all!!

This is what I came up after some small researching.

I Managed to P2V in 2 Single Steps, and I would like to share my experience with you.

Suppose you have a nice Windows 2003 Sp1 Machine running on a old Server, and you want to P2V it to Virtual Server. Simply you use your cloning solution of you choise (preferably I use Acronis and Ghost), and deliver the image to the Virtual machine as it was a physical one.

Logical when the Virtual Machine boots you get a “Blue Screen of Death” because of the hardware Incompatibility.

All you have to do is to boot up the Virtual Machine with the Windows 2003 Sp1 CD, choose the R option to go to the Recovery Console, when you just get the famous “Dos Like Console”

This is the amazing part.. you copy from the CD of Windows 2003 Sp1 the hal.dll and ntoskrnl.exe and override in the Windows system32 directory.

In simple Commands you do as follows..

1.boot the Virtual machine with the cd of Windows 2003 SP1

2. Press R for the Recovery Console

3. choose your Windows Installation (usually option 1)

Go to windows system32 folder by typping cd c:\windows\system32

Then enter the drive letter of cd-rom (usually dJ

At d: prompt type cd i386

Then type as follows

Expand ntoskrnl.ex_ c:

Expand hal.dl_ c:

For each command you will take a notification for overwriting the original files.. we choose yes to replace the files.

Then rebout the machine, remove the cd-rom of window 2003 sp1, let the machine boot and press F8 to go to option menu for Safe Boot.

THERE YOU ARE, THE TARGET MACHINE IS BOOTING WITH NO ERRORS.

IT WILL AUTO DISCOVER ALL THE HARDWARE AND IT WILL PLAY OK

100% TESTED, MIGRATED MANY MACHINES LIKE THAT.

PLEASE FEEL FREE TO CONTACT ME FOR ANY INFORMATION YOU MAY NEED.

BEST REGARDS.

ALEXOPOULOS IOANNIS MCSE MCT MVP Candidate.

Ioannis_alexopoulos@hotmail.com (my msn account, feel free to add me)

P2V info from microsoft.public.virtualserver

2007-07-17T15:25:00.001+12:00

1. Sylvain Lafontaine fill the blanks, no spam please
View profile
More options Mar 18, 5:40 am
Newsgroups: microsoft.public.virtualserver
From: "Sylvain Lafontaine"
Date: Sat, 17 Mar 2007 13:40:47 -0400
Local: Sun, Mar 18 2007 5:40 am
Subject: Re: Migrating a physical server as VM
Reply to author Forward Print Individual message Show original Report this message Find messages by this author
Yes, there are other tools to migrate a physical machine to a virtual. However, it's not always easy and with some of these tools/methods, you will have to make a reparation step to replace the HAL layer before Windows will be able to boot under its new virtualized environment. The ACPI set up in the BIOS is also another thing that you must check up before making the P2V migration:
http://www.rtfm-ed.co.uk/docs/vmwdocs/whitepaper-upgrading-cpus-on-no...
http://blogs.msdn.com/virtual_pc_guy/archive/2004/11/24/269412.aspx http://blogs.msdn.com/virtual_pc_guy/archive/2004/11/30/272662.aspx http://blogs.msdn.com/virtual_pc_guy/archive/2004/12/10/279667.aspx
Here are some other automated tools or manual methods for doing your P2V (Physical to Virtual) migration:
http://4sysops.com/archives/p2v-for-vmware-migrate-physical-to-virtua...
http://www.invirtus.com/content/view/808/394/
http://www.acronis.com/enterprise/products/ATISWin/universal-restore....
http://www.vmware.com/products/beta/converter/
http://www.rtfm-ed.eu/docs/vmwdocs/whitepaper-ultimatep2v-quickstart.pdf
In the case of VMWare converter, you will have to convert it back to VHD but only after removing the previous VMWare additions; see:
http://vmtoolkit.com/files/default.aspx http://vmtoolkit.com/blogs/paul/archive/2006/12/14/preparing-a-window...
If you want to use an imaging software like Ghost or Acronis, you will have to set up a second virtual environment for the sole purpose of restoring an image directly to a vhd: first you create two empty VHD then you attach them to the virtual environment as second and third virtual hard drives, you copy the image file(s) to the second hard drive and you restore it to the third one. All you have to do now is to detach this last vhd and use it as the main virtual hard drive for creating a new virtual environment. (There are other methods like using a booting DVD or a network connectin but these are often more complicated to set up.)
When using some older imaging softwares, it's also a good idea to partition the target VHD before making the restoration to make sure that the master boot record on the virtual drive has been correctly written or you can take the precaution of imaging the whole physical hard drive which is used as the source instead of just backing/restoring a single partition (and you must do it even if there is only one single partition on it); otherwise the MBR might be incorrectly written on the target virtual drive after the restoration. (Probably that you won't have this problem with a more recent version of these softwares but it's better to be safe then sorry.)
You might have some other problems, for example if your operating system was on the D:\ drive and you want it now to be on the C:\ drive. For a discussion of this, take a look at:
http://help.lockergnome.com/windows/Cloned-Drive-Ghost-10-Log-Drive-f...
http://groups.google.com/group/microsoft.public.windowsxp.hardware/br...
-- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please)

Virtual PC P2V and linked disc

2007-07-17T14:41:00.001+12:00

Following the process of creating the virtual machine as per KB912826 and starting the ASR Restore to restore the backup to it:

If after pressing F2 to initiate ASR you get the following error:

Setup was unable to restore the configuration of your system because of the following error: The capacity of the replacement hard disk drives is insufficient, and cannot be used to recover the partitions on the original system disk. The replacement hard disk drives must be at least as large as the disks present on the original system.

And you've read KB314686 and tweaked the dynamically expanding disc size but still can't get rid of the error message:

Then you've probably got the vhds the wrong way round! The dynamically expanding vhd is attached to the vm first and the linked vhd is attached second. It's possible to make this mistake if you aren't careful following the instructions in KB912826, because step 5f incorrectly refers to the "expandable virtual disk file that you created in step 3", but it's the linked disc file that's created in step 3.