Wednesday, March 31, 2010

Configuring Windows 2008 R2 for SharePoint 2010

If you are planning for installing SharePoint 2010 on Windows 2008 R2, here are some installations should be done before starting the SharePoint 2010 installation.

  • Open Server Manager and click on Add Roles.
  • Select Application Server. When selected, it prompts for “Add Required Features”, accept it and continue.
  • Under Application Server – Role Services, make sure below items are selected:
    • .NET Framework 3.5.1
    • Web Server (IIS) Support
    • TCP Port Sharing
    • HTTP Activation
    • TCP Activation
    • Named Pipes Activation
  • Install SQL Server 2008 (or R2) as the default instance.
  • Install ADOMD 10. You can download it from here.
  • Install Geneva Framework Runtime. You can download it from here.
  • Update windows via Automatic Updates.
  • Install the hotfix Windows6.1-KB976462-x64. You can download it from here.

Once you have all installed, you should be able to start and continue SharePoint installation without any issue. Have fun :).

Reporting Services Error on Doughnut Chart when Tooltip is set: Fixed in 2008 R2?

If you have read my previous post “Reporting Services Error on Doughnut Chart when Tooltip is set: Parameter is not valid”, you know about this error. I just checked this with 2008 R2 and, wow, it has been fixed it seems. Great.

Tuesday, March 30, 2010

Reporting Services Error on Doughnut Chart when Tooltip is set: Parameter is not valid

One of my colleagues had been facing a funny issue since yesterday, and I had to find a solution for it. It is related to Reporting Services 2008. He had created a doughnut chart that shows some values for some categories and had set the tooltip for the series. The data source he had used was Analysis Services cube. Report preview was working fine but he got the issue when the mouse is moved over it too see the tooltip. Whenever the mouse is moved over the doughnut, chart is disappeared and error is shown as Parameter is not valid. This does not happen when the tooltip is NOT set. Funny?

I am not sure whether it is a bug or it is the standard behavior of it. As usual I googled to see a solution but no luck. Fortunately I found the issue. It is because of some categories contain values “0”. Since “0” values are not displayed with doughnut charts (this is same for pie charts too), it throws this error when the mouse is moved over it. Once the zero values are excluded from the data set, it started working fine.

Interesting, let me show you how it comes. Create a report and add a dataset with following query.

   1: SELECT 'A' AS Type, 200 AS Value
   2: UNION
   3: SELECT 'B' AS Type, 150 AS Value
   4: UNION
   5: SELECT 'C' AS Type, 0 AS Value
   6: UNION
   7: SELECT 'D' AS Type, 40 AS Value
   8: ORDER BY 1

Then add a doughnut chart to the layout and drag the “Type” and drop onto Category Field Section. Next, drag the “Value” and drop onto Data Field Section. Now get the “Series Properties” of the “Value” which is on Data Field Section. Set the “Tooltip” as “[Value]”. Click on Preview to see the chart. You should see something like this.

Doughnut1 Move the mouse over it now. You should see this.

Doughnut2

If you remove the third record from the dataset, you will not get this issue. Let me share the MDX query which had been written for the original chart and fixed one.

   1: // Old query. It returns some members with zero values
   2: SELECT NON EMPTY { [Measures].[Measure1] } ON COLUMNS
   3:     , NON EMPTY { ([DimDimension].[Dimension1].ALLMEMBERS ) } 
   4:         DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
   5: FROM [Cube]
   6:  
   7:  
   8: // Modified query. It does not return members with zero values
   9: WITH MEMBER [Measures].[Measure1New] 
  10:     AS 'IIF([Measures].[Measure1] = 0
  11:             , NULL
  12:             , [Measures].[Measure1])'
  13:  
  14: SELECT NON EMPTY { [Measures].[Measure1] } ON COLUMNS
  15:     , NON EMPTY { ([DimDimension].[Dimension1].ALLMEMBERS ) } 
  16:         DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
  17: FROM [Cube]

Note that you will NOT get this issue if you enable 3D Effects on Doughnut :).
You may know some different reasons for this and different workaround for this. If so, share with us.

Thursday, March 25, 2010

What is the correct way for setting values for datetime?

How do we make sure that we accept values for datetime columns correctly? Has the month stored as it has been sent? Has the day stored as it has been sent? As long as you control the client application and SQL Server modules, you are sure about SQL Server’s identification of each components of datetime value. What if SQL Server accepts values from third-party application? Will they always send the datetime value as you asked?

Here is an example. The variable @datetime accepts a value. Assume that you accept datetime values in mmddyyyy format. If SQL Server works on default setting, it will recognize the moth of value as July with first setting. If you get it in different format, as showed in second setting, SQL Server will take some other part as the month and store, which is wrong in this scenario.

   1: DECLARE @datetime datetime
   2:  
   3: SET @datetime = '7/6/2010 2:30:00 pm'
   4: SELECT DATENAME(M, @datetime) AS MonthOfDate
   5:  
   6: SET @datetime = '6/7/2010 2:30:00 pm'
   7: SELECT DATENAME(M, @datetime) AS MonthOfDate

We can force SQL Server to always read the values as mmddyyyy by setting the DATEFORMAT. But, again, if client application sends it in different format, there is a chance of storing wrong values.

   1: SET DATEFORMAT mdy
   2: GO
   3: DECLARE @datetime datetime
   4:  
   5: SET @datetime = '7/6/2010 2:30:00 pm'
   6: SELECT DATENAME(M, @datetime) AS MonthOfDate
   7:  
   8: SET @datetime = '6/7/2010 2:30:00 pm'
   9: SELECT DATENAME(M, @datetime) AS MonthOfDate

What would be the best way? Why do not we stick with international standard? Yes, SQL Server accepts datetime values in ISO 8601 format. If accept values from third-party application, we can ask them to send the values in ISO 8601 format. The format of it is YYYY-MM-DDThh:mm:ss.

   1: DECLARE @datetime datetime
   2:  
   3: SET @datetime = '2010-07-06T14:30:00'
   4: SELECT DATENAME(M, @datetime) AS MonthOfDate

If SQL Server receives values in ISO 8601 format, it ignores all datetime related format such as DATEFORMAT, LANGUAGE and accepts the first part as the Year, second part as the Month, and third part as the Day. Note that date and time part has to be separated with “T” and time format should be in 24-hour format.

MDX Error with OLEDB: The Parameter is incorrect

If you have read my previous post, you know why I went for “OLE DB - Microsoft OLE DB Provider for Analysis Services 10.0” instead of “Microsoft SQL Server Analysis Services - .NET Framework Data Provider for Microsoft Analysis Services” for querying data from a cube into SSRS reports. Unfortunately I have to give up the OLE DB Data Source Type because it does not support for parameterizing the MDX query. When a parameter is added, I get following error:

An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset ‘DataSet1’.
The following system error occurred: The parameter is incorrect..
Error Code = 0x80070057, External Code = 0x00000000:.

As usual, I googled and found that it has been reported on January 2007 and has identified as a bug in .NET’s System.Data.OleDb. The thread has been updated again on April 2008, and seems not fixed. You can read the thread here.

Anyway, since it is related to .NET, I am going to start searching on .NET updates and fixes on this. I will share if I can find something and solve this issue with it.

Monday, March 22, 2010

MDX Error: First Axis of the query contains dimension other than Measure Dimension…

If you try to set some other dimension elements as the first axis other than measure dimension, you will receive an error like below:

error

I got this error with Reporting Services when I try to query the OLAP database since I had referred some other dimension in the first axis. How do we fix this:

All we have to do is, change the Data Source type for OLE DB and select the provider as Microsoft OLE DB Provider for Analysis Services 10.0. You may see some limitations with this :(.

Monday, March 15, 2010

Missing ADOMD.NET and SQLNCLI: PerformancePoint on Windows 2008 R2

If you see a “missing” issue with ADOMD.NET and SQLNCLI when configuring PerformancePoint 2007 on Windows Server 2008 R2, Download the latest releases from here and configure.

This is the Feature Pack for Microsoft SQL Server 2005 – December 2008. In addition to above files, it contains few more, including Microsoft OLEDB Provider for DB2 too.

SQL Server User Group Meeting – March 2010

Sri Lanka SQL Server User Group meeting will be held on Wednesday, 17th March, at Microsoft Sri Lanka. Dinesh Asanka will be doing a session related to Index-Filtering and Susanth Silva will be doing the next session on SQL Server Clustering. Read more on this…

If you interest on these subjects, want to learn, participate. As usual, we have great give-away items, pay attention and grab them.

Guys, http://sqlserveruniverse.com is up!!! Thanks for Gogula for hard working on this, and thanks for Preethi and Dinesh Asanka for helping him on this.

Does your DELETE operation deallocate empty data pages?

When we execute DELETE operations against tables, SQL Server is supposed to delete records and deallocate emptied, associated data pages. Deallocation is done, in order to make those emptied data pages available for other objects. If the pages are not released, the spaced used by the deleted records will be unusable. Because of that we need to make sure that pages are deallocated.

Why bother? SQL Server deallocates empty pages at the deletion!!! Yes, but there is an exception. It might not deallocate empty pages if the table is on the Heap. This is because SQL Server places locks on rows and pages when deleting on the heap, resulting empty pages remained allocated. Let me show you with an example:

Let’s create a table and insert some records. Note that no clustered index is defined, hence table is created on the heap.

   1: CREATE TABLE TestTable
   2: (
   3:     Id int identity(1,1)
   4:     ,Description varchar(4000)
   5: )
   6: GO
   7: INSERT INTO TestTable
   8:     (Description)
   9: VALUES
  10:     (REPLICATE('A', 4000))
  11:     ,(REPLICATE('B', 4000))
  12:     ,(REPLICATE('C', 4000))
  13:     ,(REPLICATE('D', 4000))
  14:     ,(REPLICATE('E', 4000))
  15:     ,(REPLICATE('F', 4000))
  16:     ,(REPLICATE('G', 4000))
  17:     ,(REPLICATE('H', 4000))
  18:     ,(REPLICATE('I', 4000))

When the table is queried, while IO statistics are on, you can see that 5 pages have been read for retrieving data.

It can be further checked by using DBCC IND too. When execute, it lists out 6 rows; 5 rows representing data pages (PageType = 1) and 1 row representing the IAM page (PageType = 10).

   1: SET STATISTICS IO ON
   2: SELECT * FROM TestTable
   3: SET STATISTICS IO OFF
   4:  
   5: DBCC IND('tempdb', TestTable, -1)

Now delete all records and run above codes again. You will still see that it reads 5 pages for data retrieval. If you run the DBCC IND again, you will see that same pages numbers are still allocated to this table. If you need to check and see whether the pages contain data, try with DBCC PAGE.

   1: -- Delete all records
   2: DELETE TestTable
   3:  
   4: -- This shows that it still
   5: -- reads 5 pages
   6: SET STATISTICS IO ON
   7: SELECT * FROM TestTable
   8: SET STATISTICS IO OFF
   9:  
  10: -- This shows that still the 
  11: -- pages are not deallocated
  12: DBCC IND('tempdb', TestTable, -1)
  13:  
  14: -- Check one page and see whether
  15: -- it contains data.
  16: -- Note that you have turn 3604 flag
  17: -- on to run DBCC PAGE
  18: -- Parameters are: Database, FileNumber,
  19: -- PageNumber, DisplayOption
  20: DBCC TRACEON(3604)
  21: DBCC PAGE ('tempdb', 1, 167, 3) WITH TABLERESULTS

See, pages are not released. There are few ways to make sure that data pages are deallocated at deletion of this scenario. First way is, using TABLOCK hint with DELETE statement.

   1: DELETE TestTable WITH (TABLOCK)
   2:  

This changes the way of placing locks, SQL Server places shared locks on the table instead of rows or pages, hence pages are deallocated.

Second way is, use TRUNCATE instead of DELETE. This deallocates pages too.

Another way is, adding a clustered index on the table and doing the deletion, which might time consuming.

Anyway, most of the tables we design have clustered indexes, if you have tables without clustered indexes, make sure you use one of above methods when deleting.

Tuesday, March 9, 2010

Just noticed a nice alert from Gmail.

If my composed mail has a sentence like “… find the attached …”, click on send button shows below:

gmail

SQL Server ANY and ALL Operators

When a scalar value has to be compared with a single-column set of values, we usually use IN or JOINs. In addition to that, we can extend the comparison by using ANY and ALL operators which have rarely been used. These two operators work differently, understanding them would be beneficial to all of us, avoiding complexity of queries.

ANY operator returns true when the scalar value matches with any value in the single-column set of values. ALL operator returns true when the scalar value matches with all value in the single-column set of values. The comparison can be extended with <, >, and <> operators too. Here are few sample queries to understand them:

   1: -- Lets create two tables and inser values
   2: USE tempdb
   3: GO
   4: CREATE TABLE Table1 (Id int)
   5: GO
   6: INSERT INTO Table1
   7: VALUES (1), (2), (3), (4), (5)
   8:  
   9: CREATE TABLE Table2 (Id int)
  10: GO
  11: INSERT INTO Table2 
  12: VALUES (1), (3), (5)
  13:  
  14: -- =ANY sees whether the scalar value is
  15: -- equal to any from Table2
  16: -- Eg. This checks: 
  17: -- (Table1:Id1 = Table2:Id1)
  18: -- OR (Table1:Id1 = Table2:Id3)
  19: -- OR (Table1:Id1 = Table2:Id5)
  20: SELECT Id FROM Table1 
  21: WHERE Id =ANY (SELECT Id FROM Table2)
  22: -- Finally, code returns 1, 3, and 5.
  23:  
  24: -- =ALL sees whether the scalar value is
  25: -- equal with all values in Table2.
  26: -- Eg. (Table1:Id1 = Table2:Id1)
  27: -- AND (Table1:Id1 = Table2:Id3)
  28: -- AND (Table1:Id1 = Table2:Id5)
  29: SELECT Id FROM Table1 
  30: WHERE Id =ALL (SELECT Id FROM Table2)
  31: -- Code returns nothing.
  32:  
  33: -- >=ANY sees whether the scalar value is
  34: -- greater than any value in Table2
  35: -- Eg. (Table1:Id1 > Table2:Id1)
  36: -- OR  (Table1:Id1 > Table2:Id3)
  37: -- OR (Table1:Id1 > Table2:Id5)
  38: -- Since the smallest one in Table2 is 1,
  39: -- As long as the Table1:Id is >= 1
  40: -- It is added to the resultset.
  41: SELECT Id FROM Table1 
  42: WHERE Id >=ANY (SELECT Id FROM Table2)
  43: -- Code returns 1, 2, 3, 4, and 5
  44:  
  45: -- >=ALL sees whether the scalar value is
  46: -- greater than all in Table2
  47: -- Eg. (Table1:Id1 > Table2:Id1)
  48: -- AND (Table1:Id1 > Table2:Id3)
  49: -- AND (Table1:Id1 > Table2:Id5)
  50: -- Since the highest number in Table2 is 5,
  51: -- As long as Table1:Id is >= 5, 
  52: -- It is added to the resultset.
  53: SELECT Id FROM Table1 
  54: WHERE Id >=ALL (SELECT Id FROM Table2)
  55: -- Code returns 5 only.

You should notice that =ANY is equal to IN. But the opposite of it, <>ANY and NOT IN are NOT equal. Remember, the equal one for NOT IN is, <>ALL.