bi


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.

We have three different types of applications or software that are hosted on Citrix servers: pretty typical stuff.

  1. Fat client runs on Citrix, connects to a back-end hosted somewhere else
  2. Thin client (browser) runs on Citrix, connects to back-end hosted somewhere else
  3. Standalone client runs on Citrix directly, no back end.  Office applications (e.g., Word, Access) as well as full-blown virtual desktops fall into this category.

Option (2) always sounds like a strange one (wouldn’t any Citrix client also have a browser installed?), until you realize that we use those for remote connections.  We don’t allow direct access to internal applications from the outside world, but you can either VPN in or connect to the external network-facing Citrix boxes, and then run the apps from there.

We run Troux’s Enterprise Repository to model our Enterprise Architecture world, including the relevant parts of our application portfolio, and I’m trying to find the most appropriate way to model the Citrix relationships.  You see, in Troux’s world an application runs on software modules, which are instantiations of specific versions of software products on infrastructure components.  So as an example, an application (which is a business construct, referenced as a whole, different from the component pieces of software like MS SQL) called “HR Payroll Processing” runs on MS SQL 2005 on Server1.  In that case, the Application is “HR Payroll Processing”, the software module is “MS SQL 2005 on Server1”, which is an object that links the app to the server, and the infrastructure component is Server1.

We have never traditionally used any software modules for the clients.  In fact, we’ve never represented the clients (fat or thin) in the EA repository: never really needed to.  I’m trying to figure out whether it makes sense for us to model the clients that are installed on Citrix servers using software modules, or whether it actually makes more sense to use a different object type. Has anyone out there dealt with this?  Not necessarily on Troux, I’d be interested in hearing about experiences on any EA modeling tools.  My idea is that we would create, for the cases above:

  1. Software modules called “HR Payroll Processing Client – CitrixServer1”.  These would link to the application and the server objects.
  2. Software modules called “Browser Client – CitrixServer1”.  These would link to the application and the server objects.
  3. Software modules called “<Software name> – CitrixServer1” (e.g., “Outlook 2007 – CitrixServer110”).  These would link only to the server objects.

One of the complexities of this model is that we need to produce reports that show the owners for everything installed on the Citrix servers for DR and general operational purposes.  But now this means I have to assign owners to the Citrix installations of standalone software like Office, and the report will have to show the union of owners of the first two types (which is a secondary link to the application owner relationships) and the standalone software.

In the alternate approach, I create Application objects for each of the Citrix installations of standalone software, maybe make them children applications of a parent “Citrix implementation” app. This seems duplicative to me (an app called “Citrix Outlook 2007” that runs on a software module called “Outlook 2007 – CitrixServer1” that runs on “CitrixServer1”), in addition to going against our standard application definition which states that software <> application.  There are pros and cons to each approach, but we’re early enough in our EA gathering that we don’t know how this information will be updated and used in the future, so it’s hard to understand where the additional work will be least annoying: in the work needed to create additional application objects, or in the need to create output reports.  Either way, someone’s going to be doing more work than strictly necessary in order to make the output look consistent, and I know that if I have to create additional application objects that person will be me.  I’ll do it if I have to, but I want to make sure I’m doing the right thing.

Probably just open musing at this point: I know that if someone asked me this question my answer would be “it depends”.   Anyone else been working on EA modeling?  Anyone modeled their Citrix apps?

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.