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.