Warning: Use of undefined constant íWP_POST_REVISIONSí - assumed 'íWP_POST_REVISIONSí' (this will throw an Error in a future version of PHP) in /homepages/10/d210128362/htdocs/wp-config.php on line 109
Fixes »
Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /homepages/10/d210128362/htdocs/wordpress/wp-content/themes/suffusion/library/suffusion-walkers.php on line 39
Oct 182011
 

Whenever I restore a production database to a development server, I often end up with orphaned users. You can see in the screen shot below that username matt has no login associated with it.

An account becomes orphaned when there is a user account with security rights in the database that is not linked to a user account that can log in to the server.¬† This happens because SQL logins with the same username have different SID’s on different servers.¬† It is not a problem for Active Directory logins because the SID is stored in Active Directory.

Once an account becomes orphaned, it is a pretty easy fix:

USE <database name>
ALTER USER <username> WITH LOGIN = <username>

But it’s still a pain to remember to run this code to reset the logins.¬† I usually forget, and the scratch my head for a minute to figure out why the application won’t connect to the development database anymore.

Fix it once and for all

Microsoft has a handy script to fix orphaned users (link).  Download and run the script on your production server to create two stored procedures: sp_hexadecimal and sp_help_revlogin.

Then run:

EXEC sp_help_revlogin

Run the output from this procedure against your development server to create the appropriate logins with the correct username/password and SID.

Ever since I ran this, I have not had any more orphaned users when restoring databases back to dev.

Sep 092011
 

In my current job, I have a MySQL database server that I am responsible for.  We also run some reporting through Cognos against this server.  I ran across a problem while testing reports on an upgrade to Cognos 10.  I thought I would share the solution here in case it helps someone else out someday.

I kept running into error messages while testing the Cognos reports that hit the MySQL database:

  • RQP-DEF-0177 An error occurred while performing operation ‘sqlOpenResult’ status=’-28′.
  • UDA-SQL-0114 The cursor supplied to the operation “sqlOpenResult” is inactive.
  • UDA-SQL-0283 Metadata describing <column name> does not match results from the database.

I then opened up Cognos Framework Manager to look at the package.  The weird thing is that I was able to run a Test Sample and see data return in Framework Manager.  However the same data item was throwing errors in Report Studio.  When I looked at the data items in Framework Manager, they had a data type of nVarChar.

Time to bring in the big guns

I started working with our Cognos support group to figure out what was going on.¬† They were able to edit the definition of one of the data queries in Framework Manager.¬† When the updated the data item, the data type of the text columns changed from “nVarChar” to “Character Length 16”.¬† Once they made the change and updated the package, the reports started running again in Report Studio.

Woohoo, problem solved!

Oh wait, I spoke too soon…I went back in to Framework Manager to repeat our support group’s steps against other query items to update the whole package.¬† Unfortunately when I updated any of the data queries, the data type kept changing back to “nVarChar” and the reports threw errors again.¬† I even tried to re-update the data item that our support group fixed.¬† When I made my edit, the reports broke again.

The Solution

Long story short, and after 2 weeks and plenty of headaches….I had a different version of the MySQL ODBC driver installed on my PC from what our Cognos support group did.¬† I changed the driver version from 5.1 to 3.51, and finally it worked!¬† I figured that the newer driver would be better, but apparently Cognos 10 Framework Manager has a problem with the MySQL ODBC 5.1 driver, and needs the 3.51 driver instead.

With the older driver installed on my system, I was able to update the Framework Manager package and then pull back data error free in Report Studio.

One other thing of note: after I update the query subject in the Physical Layer in Framework Manager, the links broke in the Presentation layer.  I had to go back into the Presentation Layer to update the links there as well.