Geekery


Wireless Information System for Emergency Resp...

Image via Wikipedia

Situation: an SSIS package was configured to call a separate package contained within the same database.  Result: error:

Description: Error 0xC0014062 while preparing to load the package. The LoadFromSQLServer
method has encountered OLE DB error code 0x80040E09
(The EXECUTE permission was denied on the object 'sp_ssis_getpackage',
database 'msdb', schema 'dbo'.).  The SQL statement that was issued has failed.

Solution: find the user account that is associated with the connection in the “Execute Package” task. In SQL 2008 R2, that account needs to be granted the db_ssisoperator role in the msdb database, otherwise it can’t find the other package that is being called.  I’m not sure what other effects this might have on rights, but it seems to be the right role according to the description on this page, where it states that the ssisoperator role gets read rights only.

Advertisements

Sometimes I walk alone outside late at night, when the streets are empty of everything but the whisper of lives lived and lost, and my eye is drawn up to the billions of stars over my head, and I have to wonder: is a SUM/COUNT/MAX/MIN over a VALUES statement the most effective way to calculate an aggregate over columns instead of rows?

The answer, by the way, is “Yes”.

For example:

SELECT keycol,
(SELECT MAX(colval) FROM (VALUES(col1val),(col2val),(col3val),(col4val),(col5val), (col6val)) AS D(val)) AS MaxTable
FROM dbo.TableName

That’s pretty beautiful, considering the mess of UNION and CASE statements this would otherwise require.

I’ve been burned by this one so many times it’s not funny.  Although if you like seeing me get frustrated, then I guess it is funny.

Here’s the issue: you have a SQL SELECT statement that you’re using in a Cognos Report Studio report, and you’ve verified that it is syntactically correct. It runs fine in Studio Express, for example.  But then you try to add a filter (also one that is syntactically correct) in Report Studio, and you get an error.  The SQL SELECT statement is correct, the filter is correct, but enable the filter and if fails.  What…?

Short answer: Don’t put “ORDER BY” sort statements in the SQL SELECT command. Your order statements should only occur in Report Studio.

Long answer: 

The reason this errors out is that when you put in a filter in the report (not in the original SQL SELECT), Report Studio adds that filter to the end of the SELECT statement it constructs.  So if the SQL is:  

SELECT ApplicationName from R_Applications

And you add a filter like “[ApplicationName]=’something’”, then Cognos bundles them together and sends this request to the SQL server:

SELECT ApplicationName from R_Applications 
WHERE [ApplicationName]=’something’

If the statement in the original SQL is

SELECT ApplicationName from R_Applications
ORDER BY ApplicationName

Then when Cognos sends the statement with the filter enabled it sends:

SELECT ApplicationName from R_Applications
ORDER BY ApplicationName
WHERE [ApplicationName]=’something’

Which is a syntax error: WHERE cannot come after ORDER BY.

Ta da!

Caveat: running v8 of Report Studio, I hear rumors that this behavior changes slightly in later .x revisions.

When you create burst reports or run any Report Studio report so that it sends the results out as an email, you have a few options on the format in which the file goes out. Some of them don’t work so well in our environment because of restrictions on file types that we have in our email systems, so .htm or .html files will never make it past our filters: that one is obvious. What other types can you send reports out as?

  • CSV: success!
  • XML: success!
  • PDF: success!
  • Excel: FAIL!

That last one is a little confusing, to say the least. I would expect XML to fail before XLS. There’s a reason it does fail, though: no matter how you attempt to send an Excel attached file, Cognos actually sends out an .mht file instead, albeit with a MIME type of application/vnd.ms-excel. In my mind this is astonishingly backwards, especially considering there is no indication of what it’s going to do and why.

In any case, here’s how to fix it: you must add a server parameter to send the mht file using an .xls extension. This means you’re still sending an .mht file, but it at least looks like, and behaves like, an Excel file. To do this you must add a server parameter.

Steps:

  1. Click the Tools menu in the Cognos portal and select “Server Administration”
  2. Select “Set Properties” for ReportService
  3. Select the “Settings” tab
  4. In “Advanced Settings” (usually the first option), click the ‘Edit…’ link
  5. Select “Override”
  6. In the first empty set of boxes, type in the parameter name RSVP.FILE.EXTENSION.XLS and set the value to TRUE

Repeat the above steps to Set Properties for BatchReportService, and when you send the reports send them as Excel 2002 (NOT as Excel 2000 or Excel single sheet).

Not very smart, misleading, and the cause of the error you get when you try to open an “Excel” file that came from Cognos, where it states that the file is not in the format that the extension indicates. An error that you get every. time. you. open. the. file.

This is probably the best-written review and preview of the functionality in Office 12 I’ve seen so far. Lots of screenshots.  Like the ribbons, but that’s going to be a major interface change for users: how many will go right in and click the “view old-style menus” on first launch?

I went to the local SharePoint user group meeting yesterday and was pretty impressed with the integration with the next version of SharePoint (which is not fully addressed here), but the workflow and wiki/blog/RSS capabilities for SP that integrate right into Office are very nice. I’m not in love with the current version of SP as a blog tool: the permissions alone to allow comments without allowing full posting rights are a nightmare that no one has been able to implement correctly (without it being a maintenance nightmare), and you can’t really blog from Outlook or any other app in Office in any way that has meaning for the average user. That doesn’t stop people trying to use it for blogging: give someone a hammer, all problems start to look like nails.  But the next version… ahhhh, the next version.

Isn’t that always the case?

Strangely enough, the presentation mentioned that it was only supposed to be shown under NDA, but no one there signed anything.  I know I didn’t.  So how much can I talk about it?  Can I mention the Deleted Items folder?  The one we’ve been asking for since, oh, the Mesozoic era?

And of course, the biggest question so far has been… “But will it run Windows? Or Linux”

No reason they won’t apparently, in spite of the original protection that kept the beta versions from running on non-Mac hardware..

(Warning: annoying Javascript-based interface)

Link

“Phil Schiller, Apple’s senior vice president of worldwide product marketing, said in an interview Tuesday that the company will not sell or support Windows itself, but it also has not done anything to preclude people from loading Windows onto the machines themselves.

“That’s fine with us. We don’t mind,” Schiller said. “If there are people who love our hardware but are forced to put up with a Windows world, then that’s OK.”

So how about a dual-booting MacBook running OS X and RedHat with OpenOffice?   Or triple-booting running the Vista beta and Office 12?  Joy!
Now, there’s no reason to believe that something won’t change in the future to change this (similar to how Apple has limited certain functionality of iTunes with each update), but at least it’s not forbidden out of the gate.  I assume that the similar underlying hardware architecture will mean that virtualization of Windows apps under OS X should run faster, but there are probably caveats to that, and running Virtual PC under Rosetta is going to be painful for the time being (multiple layers of virtualization, translating from Intel to PowerPC back to Intel again).

So far we do know that the MacBook won’t have FireWire 800 (only 400), and there is still no word on the battery life anywhere.  That’s troubling, and would be a large compromise to that 4-5x speed increase (on Spec numbers that Apple has long derided as irrelevant) if you can only get an hour’s worth of power on the plane.  That speed increase applies to the computer, but it doesn’t mean I can get my work done 4-5x faster in order to finish before the battery dies.

I had mentioned del.icio.us in some meetings last week at work, because I am a strong believer in their “ad hoc taxonomy” approach (which allows end users to think about classification after the data has already been entered, not before where it will raise the bar for data entry). As it turns out, Yahoo! seems to agree: they just bought ’em. Genius move for them, as del.icio.us willl integrate nicely (philosophically as well as technically, one hopes) with their previous purchases Flickr and My Web.

Note that I don’t believe that this means formal taxonomies are useless or pointless or in any way inadvisable: quite the opposite. They are necessary on one end of the spectrum (e.g. the Enterprise Portals of the world) where structured information is a must. However, they are generally too complicated for the average user who just wants to send an email or post a document, which means that a rigorous, structured taxonomy is actually a significant barrier for data classification. Users will prefer to use a collaboration mechanism that doesn’t require a taxonomy, and also unfortunately doesn’t have any public way to perform searches on the data across users: Outlook.

I believe that there is a standard bell curve on this: along one extreme, rigorous taxonomies with strict data classification that requires its adherents to fully grok both the data they’re putting in and the *whole* taxonomy (not just the little bit they are using, otherwise how would they know it’s in the right place?). Along the other extreme, completely unclassified data with no taxonomy, no useful metadata, and no search/indexing capabilities. The problem? Unfortunately, because we don’t currently implement any tools that hit the middle of that bell curve, almost *all* of our data is ending up on this extreme: un-indexed, un-searchable, un-reachable by anyone save the original data creator, in an Inbox, a home folder or a SharePoint site only a handful have access to.

In the middle, there are less rigorous taxonomies that are user-defined in an ad hoc fashion, similar to the way del.icio.us does it. The user defines the tags that are useful and significant to them, selecting not only from their own classifications but from the classifications that the masses have associated with the same or similar data. This “mob-developed” tagging definition (call it “mogging” or “mobtagging” to give it a nice trendy neologism) does two things: (a) it reduces the amount of work required to tag/classify data, which makes it more palatable to the user, and (b) it actually demonstrates to the user the benefits of a taxonomy or tagging system because they are using it directly on their own data. They participate in the taxonomy and the data classification without thinking about it, because (and here’s the important part) the tags are public knowledge.

However, even with del.icio.us I believe there’s something missing: a human eye above the morass, gently nudging the tags in one direction or another. I’m not talking about just fixing typos: it’s about noticing that particular links and particular content and particular tags are associated, so it would behoove the company to tag other, related links with the same tags, suddenly making them available via the search and tagging terms that the users are already using. This is something that is not feasible at the internet level, but is definitely achievable at the enterprise level.

Of course, there’s no one tool to get there immediately, and I don’t really believe that this “human eye” concept is automatable using today’s technology anyway: maybe Google has something in the works (and in fact, one could argue that Google Base is a step in this direction). However, the key to all of this is collaboration, indexing and search, and integrating these things across all the tools that end-users use to publish their information. It’s why you’ll constantly find me ranting and raving about collaboration and publishing information much further than we do today, in ways that the users (not the I.T. people) find easy to manage.

Indexing: X1 Enterprise Edition (indexes and searches across file stores, SharePoint, email)
Collaboration: SharePoint Portal, del.icio.us, Groove, Outlook, blogs, Flickr

Rant over. For now.

Next Page »