Index Properties window does not show the Fill Factor set

This was another issue we came across. See the below images, They show the same index property of AdventureWorks database in 2005 instance. The first image from SQL Server 2005 Management Studio and the second from the SQL Server 2008 Management Studio. Note that 2005 MS does not show the fill factor set properly. Is it a bug? Gooled but so far, no luck on this.

Happy New Year!

Hi everybody, wish you all very happy, lovely, peaceful new year!

Unable to process the objects in Analysis Services but deploy

This ate few minutes from my allocated time :). I was able to successfully deploy the Analysis Services 2008 project to another server that has only SSAS 2008 installed but had no way of processing. I was able to connect to the SSAS via MS too and see the deployed items but it did not allow me to process too. This was the error; OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [2]. ; 08001. I was bit confused but finally figured out the problem. The issue was with the data source. Though the connection string that was used with data source in the project can be used for connecting to the data source, once deployed, SSAS cannot connect to the data source via same credential. I changed the credential of project's the data source that allows to connect to the data source from both machine. Finally I was able to deploy and process the objects :). You may face for same, hence shared it.

How sys.dm_db_index_physical_stats - LIMITED shows the leaf level fragmentation?

This question of raised by one of my students in my new batch, excellent question. We all know that when we use LIMITED mode, it scans only the pages above the leaf level. If that is the case, how does it report the fragmentation of leaf pages (index_level = 0)? I could not answer immediately, so, started digging it with many articles, many blog posts. Most say what BOL says, some had some additional, valuable points too. Finally I found the answer from Kalen's blog. The reason is the pointers in the upper level. It uses pointers to calculate the fragmentation of leaf level without scanning the leaf level. LIMITED mode does not scan leaf level at all. Great. One more question to be answered. Will blog once I solved that too.

Silly mistake I had made :)

I had written code filtering some records out and had assumed that it would produce the result I want. After few minutes I realized that how careless I was; here is a similar implementation that I had done; Assume that we have a table like this: And want to get all records that "value"s are not equal to 0. This was my code: select * from test where [value] != 0 But it should be written like this :). select * from test where isnull([value], -1) != 0 Hope you wont make that mistake.

Are practice exams worth to purchase?

I was thinking of doing 70-448 (MS SQL Server 2008 Business Intelligence Development and Maintenance) exam and thought to take a practice exam from one of the exam providers. Once googled, I realized that they are bit expensive to me (most were around US$ 125) and had no way of purchasing them but downloaded one sample test that contained 10 questions. Here are 2 questions out of 10, and I decided NOT to purchase any. This question says that there is an error but no description of the error, then how can we figure out the correct answer :)? And I think that the explanation is not relevant to the question too. Again, I am confused, where is the method named "DMO"? Almost all question are just like these. Do you think that they are correct? Have I taken them wrong? Are they worth enough to purchase by spending US$125?

Char column contains a lot of NULLs. Should change to varchar?

Most of the time, the decision of the usage of data types, char or varchar is depending on, whether the values are fixed-length or variable-length. Varchar is used when the length is vary and number of characters expected are greater than 10 (heard about this before? ). There is another fact to be considered which most ignore which is nullability of the column. What if the type is char and it is fixed-length, and contains a lot of NULLs. We cannot forget that char takes up the assigned number of bytes even it contains NULL. Therefore, considering this factor, if your char column contains lot of NULLs, you should really think of changing it to varchar data type.

New batch for course 2779B

Another batch for course 2779B that is for exam:70-431 was started on last Saturday at NetAssist. Fourteen students have been registered but some were absent at the first day . Some of students come with SQL Server background but some are not, unfortunately it is one of prerequisites. Anyway, just like the other batches, hope this batch will learn well too.

Why Kilpfolio-Uninstall open so many browsers?

I had installed Klipfoloio for testing purposes, and then I decided to uninstall it today. Un-installation started smoothly, once the “done” button was pressed, it opened so many browsers like this… not sure about cause but it did.

MSSQLSERVER 2005 update 3073 == 3282 ???

I was facing some issues with my SSIS packages and thought to install hotfixes that have been released after SP2. I had a cumulative hot fix that is build 3152 (this is not the latest but wanted to try and see) and tried to install…. I am not sure that this gives a wrong info, or my knowledge on this is poor. Anyway there are many cumulative hotfixes have been released, need to find the latest and install. SP3 CTP is available, I might be trying out that too.

Third-party controls for Office PerformancePoint 2007

Are you using? I have been looking for some nice, elegant chart controls for Office PerformancePoint 2007 but still the search is not success. One way to achieve this goal is, using third-party controls with Reporting Services and using them in PerformancePoint reports. I tried with Dundas controls but it did not give the look and feel that I was expecting. My next try was using ChartFX but it did not work well with my environment. I feel that it is because of my operating system that is Windows 2008 (64-bit) and SQL Server 2005 (64-bit). I have opened a ticket with ChartFX support but so far no luck. I heard from a reliable source that controls are being designed by a local company but it has been not opened for other parties. If you guys are using any type of controls with Office PerformancePoint 2007, please let me know.

Next version of SQL Server - Kilimanjaro

Few months back, Microsoft had announced the next version of SQL Server ~Kilimanjaro~ that is due on 2010. Two major things related to the new version are; Project Gemini: Is related to Analysis Services (or it is). It uses column-oriented and in-memory technologies with Microsoft Excel. It seems that this is capable enough for handling 100M records in memory and creating a model for analysis (Are we moving away from star/snowflake schema in relational data warehouses? And things like cube, dimensions…). I saw couple of blog posts regarding Gemini and QlickView, not sure whether Microsoft going to use its technology (or has already used). QlickView is a tool that supports in-memory business analysis that can be quickly implemented with existing data sources. Project Madison: SQL Server is going to handle not only terabyte data but petabyte. Microsoft has acquired DATAllegro technologies for addressing this. Seems Microsoft SQL team has concentrated a lot on Business Intelligence, and we will see a greater enhancement with Kilimanjaro.

My Previous blog posts

If anyone look for my previous blog posts, please visit http://dineshpriyankara.spaces.live.com. I still use live.com blog but not for technical related posts. I may discontinue using it.