Friday, June 17, 2011

Problems Installing SharePoint 2010 on Windows 2008R2 Server (Configuration Failed- error)


This Knol is a bit of a departure for me in that I'm not suggesting a solution to a particular problem more I'm just sharing with the world my total exasperation when trying to get SharePoint 2010 setup and running on an internal test server (so I can test SQL Server Reporting Services Integrated Mode - I'm not even trying to test SharePoint!).

This Knol runs through the process of installing SharePoint 2010 until a "Configuration failed" error occurs in effect ending the process.

I find it staggering that this hasn't been properly tested by Microsoft prior to release of SharePoint 2010 - if I was trying to install software from a different manufacturer I'd expect these kind of problems but performing a practically automated installed by the people who wrote both peices of software and it not working is not really acceptable.

Hopefully someone at Microsoft will read this and duplicate the problem on their servers and provide a solution. Hopefully.


Build and configure your Windows 2008R2 server, to exactly duplicate the error message (and process below) after you've booted into Windows add the feature ".NET Framework 3.5.1" as well as the prerequisite Features and Services (IIS) that it requires.

Once you've done that you're ready to go through the process below.

Run the SharePoint 2010 Installer (as provided on the DVD):


Click on "Install software prerequisites" under the "Install" section:


Click "Next", accept the licensing agreement (after reading it of course!), then click "Next" to begin the installation:


This will take about 10 minutes, probably for the first time on an installation dialog (that I’ve noticed) you actually get the time the process started as well as how long it’s been running for. Something I think we should encourage!

Actually it doesn’t – it lets you know when the current step started and how long that’s been running for. Much less useful. Ah well, we’re getting there I suppose!

Once the installation has completed you’ll see the dialog:


Click on "Finish" to restart your system.

Once your system comes back up re-run the SharePoint 2010 installer and this time select "Install SharePoint Server". After a few seconds you'll be prompted to enter your Product Key, type that in, wait a few seconds for it to be validated, then click "Continue", accept the licensing agreement, and then click "Continue":


Select "Standalone" as we're building a single box instance (rather than adding a new component(s) to an existing farm):


The installation process will now continue, once it has completed it will display the following dialog:


Clicking on "Close" will trigger the SharePoint Products Configuration Wizard to start up:


Click "Next" and a dialog will appear:


This dialog is simply warning you that if you're on a production server that users are actually using then they can expect the service to be unavailable (potentially) depeding on what you select in this wizard. We're on a new server and so don't really care. Click "Yes" to continue:


The configuration will now take place and then the following dialog is displayed:


Nice.

For the sake of completeness here is the text of that error message:

"Configuration Failed


One of more configuration settings failed. Completed configuration settings will not be rolled back. Resolve the problem and run this configuration wizard again. The following contains detailed information about the failure:


Failed to create the configuration database.


An exception of type System.Security.Cryptography.CryptographicException was thrown. Additional exception information: The data is invalid.


To diagnose the problem, review the application event log and the configuration log file location at:





Click Finish to close this wizard."

Why installing the latest version of SharePoint on the latest version of Windows Server doesn't work out of the box is beyond me.

Friday, June 3, 2011

SSRS: Accessing Report Builder 3

This blog post covers accessing and installing the "Report Builder" tool that comes with Microsoft SQL Server Reporting Services (SSRS) 2008R2.


All access to Report Builder is obtained via Internet Explorer. In order to get access to the system you will require specific permission – without it the instructions below will not work (the “Report Builder” button will not be visible).

To start Open “Internet Explorer” and go to the following URL:

https:///Reports/

Where is the name of the SQL Server on which Reporting Services has been installed.

After a few seconds pause Internet Explorer will display something similar to:


The available options on your screen will depend on the permissions the administrator of the system has defined for you.

This is the "main" page for SQL Server Reporting Services. This version is slightly updated for the latest 2008R2 version of SQL Server.

Looking at the "buttons" on the menu bar click on the one labelled "Report Builder":


This will bring up a dialog:


If this is the first time you've clicked "Report Builder" then the software will download and install on your machine. The installed software is user-specific so no-one else using your computer will have access to it and it does not require elevated permissions to install (i.e. it will not trigger UAC on Vista or Windows 7).

It's about 70MB so the download could take a few minutes. Once it's completed you will see a splash screen as Report Builder starts up:


This splash screen is displayed while the application starts, as soon as the application is setup you will see a selection screen:


Select the type of report you want to proceed (i.e. Table or Matrix Wizard).

SSRS: Adding Parameters in Query Designer (When Using a DataModel as a Data Source)

This blog post gives an example of how to add a parameter to a report using Microsoft SQL Server 2008R2 and the Report Builder 3 tool.


After running Report Builder and opening your report you will see something like this;


Expand the "Datasets" node in the treeview on the left;


If you right-click the active Dataset (DataSet1 - highlighted above) and then select "Dataset Properties";


This screen is showing you a lot of technical details about the Query you have put together. Click "Query Designer ..." to get to something more meaningful. To use the "Dataset Properties" screen you will need  training.

The "Query Designer" screen appears and, if you used the wizard to generate your reports,  you'll remember it from them:


Click on the "Filter" icon in the bar at the top. This will display the "Filter Data" screen:


Scroll down the Field list (bottom left) until you find the field you're interested in adding as a parameter. For the sake of this example I'm going to add the "INVOICE DATE" field. When you find the field you're after you can either double-click it or drag/drop it into the "xxxx INVOICESs with:" area):


If you hover over the "equals" (next to INVOICE DATE) it will turn into a click-able link. Click it:


Select "On or After" in the list and then hover over the "INVOICE DATE" (which will also turn into a click-able link) and right-click it:


Select "Prompt" (this means the user will get a prompt).

Click "OK" to save the changes and close the dialog.

We are now back to the "Query Designer" main page and it looks like nothing has changed. Click "Run" button:


A dialog will now appear asking for a value for the "INVOICE DATE" parameter.


Click "OK" and the report will run and give you values for the specified date.

Click "OK" to save the changes to the Query and then click "OK" to save changes to the Dataset and return to the main editor.

Click "Run" at the top left:


This will switch to the report run viewer and, as there is a report, will prompt you at the top of the screen for a value:


Enter "20-APR-2010" and either hit return or click "View Report". The report will now run with the specified parameter.

SSRS: Formatting a Parameter as Date/Time

This Knol covers how to convert an existing text-based parameter in Microsoft SQL Server Reporting Services to a Date/Time providing the users with a Date/Time picker to select a value rather than forcing them to enter text (i.e. 10-JAN-2010) without making a typo.


Assuming you have your report already open in Report Designer and you've just run it. The report we're going to deal with has a single parameter "INVOICE_DATE". Click "Design" at the top left:


Expand the Parameters item in the "Report Data" treeview (on the left):


Highlight "INVOICE_DATE", right-click and then select "Parameter Properties". The "Report Parameter Properties" dialog will now appear:Highlight "INVOICE_DATE", right-click and then select "Parameter Properties". The "Report Parameter Properties" dialog will now appear:


On the General tab change the Prompt to "Invoice Date: " and the Data type to "Date":On the General tab change the Prompt to "Invoice Date: " and the Data type to "Date/Time":


Click "OK".

Click "Run" the top left:


You can not see that instead of having a simple text-edit you've got a little button to click which will bring up the date picker.

SSRS: Specifying a Default Value for a Date/Time Parameter

This blog post covers how to specify a default parameter value for a Date/Time Parameter in a report written using Microsoft SQL Server Reporting Services (SSRS). You can easily modify this to specify default values for other types of parameter.


Assuming you have your report already open in Report Designer and you've just run it. The report we're going to deal with has a single parameter "INVOICE_DATE". Click "Design" at the top left:


Expand the Parameters item in the "Report Data" treeview (on the left):


Highlight "INVOICE_DATE", right-click and then select "Parameter Properties". The "Report Parameter Properties" dialog will now appear:Highlight "INVOICE_DATE", right-click and then select "Parameter Properties". The "Report Parameter Properties" dialog will now appear:


We're interested in changing the "Default Value" so select that tab:


Select the "Specify values" radio group:


Click "Add":


Then click the function button (highlighted) on the right of the new line:


Enter the expression:

=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))

(You're probably wondering where I got this from - type "reporting services first day of the month" into Google - it's the first result).

This expression will give you the first day of the PREVIOUS month. Click "OK". Click "OK". Click "OK" (you should be back to the main designer now with no additional dialogs open).

Click "Run", your report Parameter will now have a default value (and if it's the only parameter your report will now run automatically).

Thursday, June 2, 2011

Noetix: Identifying Un-used Columns For Removal

This blog post covers a description of an application (source code provided) that identifies columns which are not required (i.e. all values of the column in the view are the same) and generates a script that somone who has completed the NoetixViews Customization Certification course can include in the Generation Scripts.


The first thing to note is how this works; it opens every view doing a MIN/MAX on every column. It has been tested on a 16GB database and works fine - of course the main requirement of an application like this is to have enough TEMP tablespace in order to work. This is the reason the columns are dealt with individually rather than just opening each view and doing a MIN/MAX on every column in the view in a single hit - this was blowing out the 5GB of TEMP tablespace we had available on the test system.

The application is a simple console application:


All it does is process the views in the database using the SQL:

SELECT DISTINCT UPPER(v.VIEW_NAME) VIEWNAME
  FROM n_views v
 WHERE EXISTS
 (SELECT 'x'
          FROM all_views av
         WHERE av.OWNER = USER
           AND av.VIEW_NAME = UPPER(v.VIEW_NAME))
   AND NVL(v.OMIT_FLAG, 'N') = 'N'
   AND NVL(v.Special_Process_Code, 'X') NOT IN ('LOV', 'BASEVIEW')
   AND (UPPER(v.VIEW_NAME) = '%VIEWNAME%' OR '%VIEWNAME%' = 'ALL')
ORDER BY UPPER(v.VIEW_NAME)

This gives a list of all the views in the database. The %VIEWNAME% is replaced with the first argument on the command line (optional). If nothing is specified then it is replaced with ALL.

Views which are omitted by default are automatically excluded as are all views which are of type LOV (list of values) or BASEVIEW.

Once the list of views has been obtained then the list of columns in each view is retrieved using the SQL:

 SELECT COLUMN_NAME, DATA_TYPE, COLUMN_ID
  FROM ALL_TAB_COLUMNS ATC
 WHERE ATC.OWNER = USER
   AND ATC.TABLE_NAME = '%VIEWNAME%'
   AND ATC.COLUMN_NAME NOT LIKE 'A$%'
   AND ATC.COLUMN_NAME NOT LIKE 'Z$%'
   AND ATC.DATA_TYPE NOT IN ('ROWID', 'LONG')
   AND INSTR(ATC.COLUMN_NAME, '$') = 0

The list of  columns excludes the A$ and Z$ columns that are managed by Noetix and columns which contain a "$" symbol (flexfields) as if you want to remove these you can disable them in the e-Business Suite itself.

Once the appliction has the list of columns it looks through them building the SQL:

SELECT 1 AS IGNORE
   ,TO_CHAR(MIN()) AS MIN
   ,TO_CHAR(MAX()) AS MAX
FROM

The MIN and MAX values are then compared, if they are the same when an SQL file is created for the suppression of the field (for future reference the MIN and MAX values returned are stored in the file).

As you can imagine when accessing the GL views this query can take quite a while - for our 16GB database processing all the views in our relatively simple configuration will take about a week (running multiple processes has gotten this down to 2-3 days).

This isn't quick - but it is effective - we are removing around 3,000 columns from the views. That's 3,000 less columns in the help and for our end users to "accidentally" pick the wrong one from!

Source Code: here (Visual Studio 2010, 20kb Zip Archive)
Build Environment: VS2010 + Oracle Client