Thursday, December 31, 2009

Happy New Year!

Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010! Wish you all a very Happy and Prosperous New Year 2010!

Tuesday, December 8, 2009

Fixed: SharePoint 2010 Installation Error: Microsoft Geneva Framework Runtime: download error

No more errors with SharePoint 2010 prerequisites installation, and good thing is, no manual installation of any components.

If you refer my previous post that speaks about issues with SharePoint 2010 prerequisites installation and some fixes I did (I found from various post), you can see that it was unable to fix and install SharePoint by installing any. I started again, here is the screenshot with new installation....

Untitled

All I did was:

  • Installed W2008 R2 64-bit
  • Updated Windows (with automatic updates)
  • Installed SQL Server 2008 R2 Nov CTU

NO additional downloads and installations, if you face for same, try again and see.

Tuesday, December 1, 2009

SharePoint 2010 Installation Error: Microsoft Geneva Framework Runtime: donwload error

I have been trying to install SharePoint 2010 Beta on Windows 2008 R2 (64-bit) for a day but not successful yet.... Initially, I got four issues: Microsoft "Geneva" Framework Runtime: download error Microsoft Chart Controls for Microsoft .NET Framework 3.5: Installation skipped Microsoft Filter Pack 2.0: Installation skipped Microsoft SQL Server 2008 Analysis Services ADOMD.NET: Installation skipped In order to solve these issues, I downloaded all four and manually installed: Microsoft "Geneva" Framework Runtime Microsoft Chart Controls for Microsoft .NET Framework 3.5 Microsoft Filter Pack 2.0 Microsoft SQL Server 2008 Analysis Services ADOMD.NET I installed a hotfix for Windows (KB976462) too. While searching, two more hotfixes were found to be installed, KB971831 and KB968930, but OS did not allow me to run them. Finally I ended up with two issues, one with Geneva Framework Runtime and Filter Pack 2.0. Anyone can help me on this?

Sunday, November 29, 2009

Deep-Dive sessions at Peradeniya University

This was my third visit to Peradaniya University, we (Wela, Prabath, Susantha, Tharindu, and me ) did set of sessions on .NET, C#, and SQL Server, and everything went very well, a lot of questions..... Here are some photos: Wela: Showing Silverlight
Dinesh: Delivering SQL Server presentation
Prabath: Delivering presentation on C#
Susantha: Showing Windows Operating Systems
Tharindu: Explaining the importance of communities-Student Champ
Part of the audience

Wednesday, November 25, 2009

A lesson from my son - History of Computers

Thought to make a post on this, may help you to brush up your computer history knowledge too :) • The name of the first computer is ENIAC (Electronic Numerical Integrator and Calculator) • Abacus is the first known calculating device invented in China. • Charles Babbage is known as the Father of computers, who invented the first mechanical computer which was called the Analytical Engine.

Be in the know

I was not in the know, if you too, read this Merill's post, it is about the history of Microsoft Office Web Apps.

Tuesday, November 24, 2009

Connect to 64-bit Oracle 10g from Reporting Services 2008

Recently, I had to create couple of Reporting Services 2008 reports by using Oracle 10g as the data source. I had to use one machine as the developer machine and the server. Server was installed with 64-bit Windows 2008 and SQL Server 2008 64-bit. Oracle was installed in another machine and it was 64-bit too. Here are some of the problems, issues I faced, It may help you too. Connectivity problem with BIDS First problem I faced was connectivity with Oracle in BIDS. I tried to use Oracle provider that comes with SQL Server installation but it did not work. So the solution was, install Oracle full client (Administrator). What should be installed? 32-bit or 64-bit? In order to make the connection via BIDS, we need to install Oracle 32-bit client though the server is 64-bit. The main reason for this is, BIDS is a 32-bit application. Once 32-bit Oracle client installed, BIDS was able to connect to Oracle. Connectivity problem with Report Manager This was the second problem. Once the reports are publish, I got the same error when reports are viewed with Report Manager. This is because it requires Oracle 64-bit client. Once Oracle 64-bit client installed, it started working. OLEDB Provider for Oracle or Microsoft Oracle Provider? When I google, I saw some posts related this provider, it seems that Microsoft Oracle provider had not worked for many but it worked for me. I was able to make connection by using both providers. Passing Parameters to Oracle As usual, we can use "?" for represent parameters in the query if the provider is OLE DB (eg. WHERE column1 = ?). But remember, you need to use ":" for parameters if the provider is Microsoft Oracle provider (eg. WHERE column1 = :Parameter1) IN Clause with WHERE "IN" is not supported by OLE DB provider. If you have a requirment that needs to use "IN" with "WHERE", use Microsoft Oracle provider instead.

Monday, November 16, 2009

IIS 7 Stopped. PerformancePoint Error ???

It hit me again, this is the second time I experienced this problem. My VPC with Windows 2008, SQL Server 2008 and MOSS 2007 was working fine until I installed and configured the PerformancePoint. My bad, I did not take a backup of my VPC. Once the PerformancePoint configured (with SP3), I noticed that the Default Application Pool was getting stopped even after restarting and resetting. The entire IIS 7 stopped working. The error was 503, Service is unavailable. I applied all the solutions what I found with my searches, but as most have done, I have to start from the beginning. No solutions, but reinstall whole thing.......

PerformancePoint Server 2007 Service Pack 3

If you are unaware of SP3, here is the link, x86 and x64.

Wednesday, November 4, 2009

PerformancePoint error: Part III - Unable to connect to Server - SSRS 2008

It hit again, this time it is with Reporting Services 2008. PerformancePoint allows to connect with Reporting Server and lets to browse reports in the server, but the problem comes when we try to connect with the report. When the report is selected from the Browser, it throws an error saying "Unable to connect to server". After few minutes, we realized that the problem comes only with reports that have parameters, other reports can be connected. This is a bug and the hotfix is available here. For your reference, I had blogged two more issues I faced before, you can find them here (Part II)and here (Part I).

Monday, November 2, 2009

DML, DDL, DCL, TCL, DQL

When we discuss, when I teach, the mostly discussed SQL Server related languages are DML and DDL. But do you know that there are few more categories, they are DCL, TCL and DQL. Here is a brief note of it; DML - Data Manipulation Language - statements that perform changes to the database. Eg. INSERT, UPDATE, DELETE DDL - Data Definition Language - statements that modify objects in the database. Eg. CREATE TABLE, CREATE VIEW, DROP PROCEDURE DCL - Data Control Language - statements that controls the rights to objects. Eg. GRANT, DENY, REVOKE TCL - Transactional Control Language - statements that controls transactions in the database. Eg. COMMIT, ROLLBACK, SAVE POINT DQL - Data Query Language - statements that query the database. Eg. SELECT

Wednesday, October 28, 2009

Native OLE DB\SQL Server Native Client 10.0 does not list out the available databases

This held our work for few hours. When we tried to make a connection to SQL Server 2008 server with “Native OLE DB\SQL Server Native Client 10.0” provider from a developer’s machine through SSIS, it did not list out available databases, and did not allow to connect to a database even the name of the database is just typed in the “Select or enter a database name” input box. We doubt that the problem was related to some network problem or to some installed components but, since it allowed to connect with “Native OLE DB\Microsoft OLE DB Provider for SQL Server”, thought that it is related to “SQL Server Native Client 10.0”. We spent hours to find the reason but could not find any issue related to SQL Server components. Though the connection can be established with “Microsoft OLE DB Provider for SQL Server”, since it does not allow to run some of the new TSQLs and specially it does not recognize “Date” data type properly, we had to dig deeper…. Finally, my colleague Buddhika found the issue. Windows Firewall Settings was blocking the Native Client 10.0 requests. Once it is turned off, connection could be established. Again, turning off the settings is not a good practice, we tried to find a way to make a connection while Firewall Settings is on, and we found. Simply, the TCP port 1433 added as an exception for the Firewall Settings. It worked. So, in shorter form, if you cannot make a connection to SQL Server 2008 with Native OLE DB\SQL Server Native Client 10.0” provider, make sure that the SQL Server listening port is added as an exception with Windows Firewall Settings. I am sure that we will be getting the same with Analysis Services too, and adding TCP port 2383 as an exception might solve the issue.

Tuesday, October 27, 2009

Slowly Changing Dimension: Char for Type II Attributes

I used to use Char data type for character data types of variable length columns, if the maximum number of characters that will set for the attribute is less than 10. This is what most DBAs/DBEs do. Not only the maximum length, there are few more considerations for using Char instead of Varchar for character data types of variable length. One would be, when the length of all data values is approximately same. I made a mistake; this cannot be applied in everywhere, especially in Relational DW with Type II attributes. For example, if I have to make a dimension table with Marital Status that contains either Single or Married, I can make the column as either varchar(8) or char(8), so I made it as char(8) because I prefer to make my design according to experts suggestions. Unfortunately this is a Type II attribute which is designed to maintain history, hence when the record contains “Single”, even though the same value comes with a new data set, it considers as a changed (because of the length) and inserts a new record, making the old record as a historical record and it continues with next data set... It unnecessary adds new records to the table making the old ones as historical records. So, I made the all the Type II columns as varchar columns that were set as char to solve the issue, what do you think, any suggestions, any thoughts on this?

Thursday, October 22, 2009

Another SQL Server new course.

Last week, two batches, one for 2780B (SQL 2005) and another for 6231A (SQL 2008) were successfully completed and will be starting my next class, most probably within next two weeks, for 2778: Writing Queries with Microsoft SQL Server 2008, at NetAssist. If you interest to join my class, you are welcome. Although I started my conducting classes on.NET, last two years I have been doing classes, workshops only for SQL Server and Business Intelligence. Just thought to focus on two more subjects. Working closely with one of the reputed institutes regarding these two subjects, hope will be able to start them by January, 2010.

USER_NAME(), SUSER_NAME(), ORIGINAL_LOGIN()

Sometimes, we switch the execution context to different account when required. One of requirements when connected in such a manner may be, finding out the original account. This can be retrieved from ORIGINAL_LOGIN function. Not only this, the other functions such as USER_NAME, SUSER_NAME are useful too, if you need to info return from them. Thought to put down a small code, just to show the different between these functions; Login as "sa" and execute... -- Create a login and a user USE master GO CREATE LOGIN TestLogin WITH PASSWORD = '123', CHECK_POLICY = OFF GO USE AdventureWorks GO CREATE USER TestUser FROM LOGIN TestLogin -- Test the functions -- Returns dbo SELECT USER_NAME() -- Returns sa SELECT SUSER_NAME() -- Switch the execution context EXECUTE AS LOGIN = 'TestLogin' -- Returns TestUser SELECT USER_NAME() -- Returns TestLogin SELECT SUSER_NAME() -- Returns sa SELECT ORIGINAL_LOGIN() REVERT; USER_NAME: Returns the current user in the current context. If the user_id is submitted, returns the name of the given id. SUSER_NAME: Returns the current login in the current context. If server_user_id is submitted, returns the name of given id. ORIGINAL_LOGIN: Returns the original login in the session in which there are many implicit or explicit context switches.

Tuesday, August 25, 2009

New class on 2780B

I started another class for course 2780B - Maintaining a Microsoft SQL Server 2005 database. Though I wanted to do this on 2008 (6231A), this has been selected by the institute for participants because most of them are using 2005. In a way, it is good, because, doing another class on 2008 (6231A). Doing three classes per week is bit difficult, but I like it, getting more invitations, planing to have more classes.... this might stop me, attaching permanent to a company.

Monday, August 17, 2009

Microsoft XPS Document Writer cannot be found

I am experiencing this error with Microsoft PowerPoint 2007, when the hibernated machine which had PowerPoint presentation opened, is started. Searched for a solution but no luck so far, and many face this it seems. Anyone know the solution?

Sunday, August 16, 2009

Are you using "Windows Services window" for changing the SQL Server Service Account?

If you are using the "Windows Services" window for changing the assigned account for SQL Server service, you may face some problems later. When required, we should use SQL Server Configuration Manager, instead Windows Services because the Configuration Manager performs required operations such as configuring SQL Server related configurations, re-generating Service Master Key and decrypts and encrypts all objects that had been encrypted by Service Master Key. If you do not use Configuration Manager, you need to execute ALTER SERVICE MASTER KEY statement manually for completing the operation.

Monday, August 10, 2009

PerformancePoint error: Unable to connect to Server - Part II

Once I blogged about this error with this link. Today I faced the same, but the reason was different. My new machine is installed with Vista-Ultimate 64-bit. I started installing all the prerequtities for PerformancePoint, did everything. Once installed, tried to connect to http://localhost:40000/WebService/PmService.asmx through Dashboard Designer (Options box), but no luck. It gave an error saying "You have not admin privileges.". I had logged in as "Dinesh" and the account "Dinesh" is in Administrators group. I made sure again, that "Dinesh" is in Administrators group and "Dinesh" has permissions for the PerformancePoint web service too. I still do not know, why "Dinesh" could not make the connection..... he is in the Administrator group. I solved the problem by logging to the system as an Administrator. Once logged in, I was able to connect and then I used the "Permission" box for adding "Dinesh" as an Admin to the PerformancePoint. When I log in with "Dinesh" now, it allows to make the connection now. I think, by default, Administrator is added as the Admin of the PerformancePoint, so, he is the only person who can give permission to others. Keep this in mind, if you use some other account without using Administrator.

Saturday, August 8, 2009

Connecting to Analysis Services with the Port Number

First of all, let me tell you that why I need to connect to Analysis Services with the Port number; because the Analysis Services 2008 is running as a named instance and SQL Server Brower needs to be disabled as a security measure. Why do we need SQL Server Browser Services? By default, Analysis Services uses the port 2383. When you connect to the default instance (in my case, it is 2005), it connects to the SSAS without any problems, even though you have not specified the port with the connection string. If you try to connect to a named instance of Analysis Services (again, in my case, it is 2008), without specifying the port, you will still be able to connect, as long as the SQL Server Browser is running. Named instances of the SSAS use port numbers that are dynamically assigned, therefore they are varies. When you restart the service, it assigns a new one. Assigned port number can be seen with msmdredir.ini file in the C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig folder. Though we do not specify the port in the connection string (even with SSMS), SQL Server Browser handles the request and directs us to the correct instance, because it knows the port numbers assigned. Now my issue was, unavailability of SQL Server Browser hence cannot connect to the named instance. When we try to connect to the named instance with SSMS or BIDS, it says that Browser is not running. Since the Browser cannot be enabled (as a security measure), all we have to do is, find the port number (from the ini file specified above) and specify with the connection string. One thing you have to remember is, when specifying, it should be {MachineName}:{PortNumber}, NOT like {InstanceName}:{PortNumber}. If my machine name is DINESH-PC and named instance is SQL2008; When connecting to the default instance: DINESH-PC:2383 When connecting to the named instance: DINESH-PC:50019, NOT DINESH-PC\SQL2008:50019 If need to fix a port number, use the ini file or Port property in Analysis Services, for changing.

Wednesday, August 5, 2009

Foreign Keys with TinyInt do not recongnize by SSAS

This is still there, with SQL Server 2008 too. I usually use TinyInt for tables' primary key when the number of records to be held are less than hundred, so applied the same rule for my newest Data Warehousing project, completely forgetting the fact that Analysis Services cannot recognize the relationships between Fact and Dimensions that have TinyInt as the primary key. As usual, had to change all my Dimensions tables, changing primary keys to Smallint. Remember this, if you are designing a data warehouse.

Restoring one File Group

Today, I came across a situation where I had to explain the FILEGROUP restore of SQL Server. Since I have not done this for a long time, I brushed my knowledge with Preethi's help and thought to blog it too. If you have a database that has more than one file group, you may face to a situation where you need to restore only one file group, without restoring the entire database, saving time. One thing we have to remember is, secondary file group (or file) restore needs Primary file group restore. If your Primary file group contains a lot of tables, again, it takes time for restore operation (Usually, database that contains secondary file groups does not use primary for user-defined tables). So, make sure that you do not use Primary file group for your tables. Here is an example: Assume that we have a database that has three file group: Primary, FG1, and FG2. In order to restore file groups, you should have backups of all file groups (both full and differential are possible). Here are steps for restoring a file group (FG1); -- Backup tail-log if require BACKUP LOG MyDatabase TO DISK = 'D:\Backups\TailLog.bak' WITH NO_TRUNCATE, NORECOVERY -- Restore the Primary file group RESTORE DATABASE MyDatabase FILEGROUP = 'Primary' TO DISK = 'D:\Backups\MyDatabase_FG1.bak' WITH NORECOVERY -- Restore the FG1 file group RESTORE DATABASE MyDatabase FILEGROUP = 'FG1' TO DISK = 'D:\Backups\MyDatabase_FG1.bak' WITH NORECOVERY -- Restore the log (if need, can be restored to a specific time) RESTORE LOG MyDatabase FROM DISK = 'D:\Backups\TailLog.bak' WITH RECOVERY

Thursday, July 16, 2009

Was really busy

Had no time to update my blog for few weeks, was really busy with work: - New BI project: This is for one of the leading banks in Sri Lanka. This is the first time I am engaging with banking industry, in terms of BI. Lot of things to learn, really interesting. - New set of SQL Server questions: It is almost done, this is the third time I involved with questions making. I have already written questions to two Microsoft SQL Server 2008 exams and this is for, again Microsoft but not for MS certifications. Anyway, all questions have been reviewed, seems they are happy with my questions :). - New SQL Server 2005 batch: Done. Conducted another class for developers. Students were good, though there were gaps in knowledge levels. - New SQL Server 2008 batch: Really interesting, this is my first SQL Server 2008 class. Participants are really good, lot of questions. This course is bit different, have not taught Maintaining SQL Server course (6231A) before. I am not much comfortable with the official materials, seems course book is not so rich and found some mistakes too, anyway it is too early to make comments on it.

Wednesday, May 27, 2009

Another victory of Sri Lanka

This clearly shows the reality, this shows who wants to destroy the great country, Sri Lanka. Sri Lanka won again, defeating the international terror. How they voted at the UNHRC sessions Victory for Lanka’s fight against terrorism

Thursday, May 21, 2009

PerformancePoint error: Unable to connect to Server

Are you experiencing this? You might. There are different set of connection related errors with PerformancePoint 2007. If you get errors like "Unable to connect to the specified server. Make sure the address is correct" or "The PerformancePoint could not connect to the specified data source", most probably, you will find the solution here. If you face for an error like "Unable to connect to Server" with PerformancePoint dashboard designer options, the reason might be the unavailability of AJAX 1.0. Solution is here. It has another solution but installing AJAX fixed my problem.

Wednesday, May 20, 2009

Skipping prerequisities when installing PerformancePoint 2007 SP2

While installing the SP2 of PerformancePoint 2007, you may get an error like; Failed package requirement analysis, please refer to the log file for more information. This can be avoided by setting the SKIPREQCHECK as true with msiexec command. But you have remember one thing, when we install the product we use i switch but p switch has to be used when installing service packs. Please see the code below; Installing the product, skipping the prerequisites; msiexec /i PSCSrv.msi SKIPREQCHECK=1 Installing the service pack, skipping the prerequisities; msiexec /p PSCSrv.msi SKIPREQCHECK=1

Tuesday, May 19, 2009

Installing PerformancePoint 2007 SP2

If you are using PerformancePoint 2007 and you have not installed the SP2, then this is the time for installing it. As you know, it did not support natively for SQL Server 2008, and now it allows. You will be installing this SP2 under three scenarios; 1. Update PerformancePoint Server with SP2 only. No changes to SQL Server 2005. 2. Update SQL Server 2005 to SQL Server 2008 and then update PerformancePoint Server to SP2. 3. Install PerformancePoint Server RTM and SQL Server 2008 in a new environment. You need to make sure that you follow the correct steps for whatever the scenario you select. You can find the instruction for all scenarios here. You can download the SP2 from here(x86) and here(x64).

Monday, May 18, 2009

Sri Lanka is free from Terror

After 30 years, His Excellency Mahinda Rajapakse, President of Sri Lanka has freed the country from LTTE terror, keeping his word. How fortunate we are? My gratitude to President and victorious armed forces.

Saturday, February 28, 2009

Another BI workshop

This is the first workshop organized by IronOne Technologies. It was started on last Thursday and will continue till this Tuesday. I feel bit tired because it is very difficult to keep on talking :), without a partner. Almost all workshops that were organized before, were done by Gogula and me, hence work was shared, doing it alone is a very difficult task. Audience of the workshop is really good, some have work on BI related projects too. Everybody exposes an average knowledge, hence it is not so difficult to run the workshop.

Thursday, February 12, 2009

How to distinguish between measures and KPIs…

As you all know, measures are identified at the analysis stage of Data Warehouse/BI project. Are all identified measures KPIs? This question came up, while we were having a discussion on one of our new projects. It was an interesting question, thought to make a post on it, showing what I think, what I have read… In a way, yes, we can consider all measures as KPIs. But they become proper KPIs when it shows the quality of the value, health of the value. This requires another attribute to be attached: Let’s take an example, Sales Amount, obviously it is a measure. It shows the sales amount for some dimensions (for current year, for a product). Does it show the health of that value, whether it is good or bad? No, it is just a value. Important thing to remember is, KPI is not just a value, it indicates the health of the value too. So, if we need to make Sales Amount as a KPI, we need to use another attribute such as Target. Target value allows to judge the Sales Amount whether it is healthier or not, hence Sales Amount becomes a KPI. That’s how I see the difference between a measure and a KPI? Am I right? Any thoughts on this?

Wednesday, February 4, 2009

Is your architecture leading to Stovepipe data mart?

Question arose while we were discussing BI. I asked myself, whats the hell is "Stovepipe" data mart, I was aware of the word but picture behind it, did not come to my mind immediately. So, brushed up.... Stovepipe data marts are nothing but data marts that prevent integration of themselves for making a data warehouse because of the unavailability of conformed dimensions. In most cases, data marts (dimensions in data marts) are designed with their own rules, policies and mainly own structures. This results incompatible dimensions in different data marts though they represent same entity. It will be a mess, will be awkward situation when it comes for making a data warehouse, combining all data marts. Though data marts have many advantages, cost-wise, time-wise, etc.. this is the major disadvantage of them. Are you leading to a Stovepipe data mart?

Sunday, February 1, 2009

SSRS 2008 Video: Formatting text blocks individually in a single textbox

This is my first video, had been planning for months, and somehow was able to do one. I do not know whether the quality of this lesson is okay, because I made many mistakes while recording, this is my 8th or 9th recording :). This shows one of the newest features of Reporting Services 2008. Reporting Services 2005 did not allow us to format text blocks in a textbox with different styles. Now it is possible. Watch the video and see whether it is useful. Appreciate your comments on this. video

Friday, January 30, 2009

REPEATABLE READ is fine, but want to let others to read non-locked records

We usually set the REPEATABLE READ Transaction Isolation Level in order to overcome Inconsistent analysis (nonrepeatable read) and Lost Update concurrency problems. This level do not let other users to read records that are read by your transaction, but creates a deadlock situation. Here is a simple example;
   1: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
   2: BEGIN TRAN
   3:     DECLARE @Id int
   4:     SELECT TOP 1 @Id = Id
   5:     FROM dbo.Employee
   6:     WHERE IsUpdated = 0
   7:  
   8:     ......... -- more processing
   9:  
  10:     UPDATE dbo.Employee
  11:         SET Info = 'some text', IsUpdated = 1
  12:     WHERE Id = @Id
  13: COMMIT TRAN 
This code gets the first record available for updating (TOP 1, IsUpdated = 0) and does some processing, finally updates the record. This code does not allow others to modify the record while it is being accessed, avoiding inconsistent analysis and lost updates. But it reduces the concurrency. It does not allow other to get the second available record too, making a deadlock situation if the same is executed by another connection. There is no harm of letting others to access the second available record. How do we do it? This cannot be implemented with REPEATABLE READ isolation level. All we have to do is, use UPDLOCK and READPAST query hints. UPLOCK puts exclusive lock on the object and does not allow others to obtain a lock on the same. READPAST skips all locked records and read the non-locked records. Here is the corrected code;
   1: -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
   2: BEGIN TRAN
   3:     DECLARE @Id int
   4:     SELECT TOP 1 @Id = Id
   5:     FROM dbo.Employee
   6:     WITH (UPDLOCK, READPAST)
   7:     WHERE IsUpdated = 0
   8:  
   9:     ......... -- more processing
  10:  
  11:     UPDATE dbo.Employee
  12:         SET Info = 'some text', IsUpdated = 1
  13:     WHERE Id = @Id
  14: COMMIT TRAN 
This locks the record while it is being accessed but allows to process the next available record for others.

Friday, January 23, 2009

Reporting Services 2008 Error: The underlying connection was closed. An unexpected error occured on a send.

It hit me again. This does not allow you to deploy any reports or open report manager via the browser. This is because of the invalid configuration in reportserver.config file. The default value of the key SecureConnectionLevel has been set as 2, which requires SSL configuration. If you do not use SSL, make it as 0, making service least secure. Refer BOL for more info.

Reporting Services 2008 Error: Invalid namespace

This error comes when we try to configure Reporting Services 2008 with Reporting Services Configuration Manager. The reason for this is funny and identified as a known issue; It checks for wrong instance name. This has been fixed with the Cumulative Update Package 3. If you are unable to download, another method has been given with this which I used. It gives instructions for changing the reportingservices.mof file.

SQL 2008 Launch, Kandy: Few more pohotos

Doing my presentation.
Giving a prize to a winner.
All prize grabbers.
SQL Server crew

Monday, January 12, 2009

Sucess of Community Launch of MS SQL Server 2008, in Kandy

I am proud to announce that the SQL Server 2008 Community Launch, Kandy took place in grand scale. This was organized with some great difficulties, however we managed to make the event as a success event. Targeted audience was 100 and there were 90-odd participants. All participants were delighted and almost all appreciated the event. Following the agenda, Gogula delivered the KeyNote and Introduction to SQL Server 2008. Dinesh Asanka's presentation was the next one which was on developer track. Preethi did the third presentation, again it was on developer track, finally I did a presentation on BI. In addition to that Dinesh Asanka showed a presentation that spoke about red-gate tools. I did not get all photos for the event, once I have, we will publish on SQLServerUniverse.Com. I must thank to following companies, individual for making the event success; Gogula Aryalingam - This was his idea and credit must go to him. Dinesh Asanka - Did a fantastic job, not only delivering job, helping from start to end. Preethi - He was with us since we were planing, just like Dinesh Asanka and did a great job too. Wela (Microsoft Sri Lanka) - Supported us providing financial support, where we were in great difficulty. Rachel (RedGate software) - Supported us sending a package of goodies for delivering. It helped us very well. Akalanka (Peradani University) - Helped us to organized the event at the campus. Shamri (ESoft, Kandy) - Supported us by giving 30% discount vouchers on MS courses for all participants. L. H. Event Management - Supported the event by organizing the lunch. It was a great buffet :). And all participants - We would not be able to make this is as a success event without you guys. We will be doing the Colombo event soon. Though it was scheduled for this month, since we have some financial issues to be sorted out, we had put it off. Here are some pictures; At registration: Everybody got a SQLServerUniverse.Com pen, 30% discount voucher and a SQL Server 2008 file. Part of the audiance - just before starting the event During lunch time Phots of sessions and other activities will be published soon.

Sunday, January 4, 2009

Separating batches using a new word, instead of “GO”.

Can we use a different word instead of GO? Yes, it is possible because GO is not a T-SQL statement; it is a command that is recognized as a batch separator by client tools such as Management Studio and sqlcmd. Unfortunately most are unaware of this and consider it is as a T-SQL statement. If you need to use a new word with your management studio, follow these steps; Open Options window (Tools -> Options). Expand and select the node Query Execution -> SQL Server -> General. Find an option called Batch Separator. Set a new value as you want. Done. Now you can use the new word instead of GO. You may face for a problem if you execute the code in a different Management Studio. Because of that, the word GO is preferred and recommended but remember the fact; it is not a T-SQL statement.

Community Launch of MS SQL Server 2008, in Kandy

We, SQL Server MVPs are organizing Community Launch of SQL Server 2008, next week in Kandy and 4th week of January in Colombo with the support of Microsoft Sri Lanka, Red-gate and SQLServerUniverse. SQL Server MVPs (Dinesh Asanka, Gogula and me) and Preethi (One of experienced DBA with vast knowledge) will be delivering; What's new in SQL Server 2008. If you are in Kandy, join with us.

Thursday, January 1, 2009

MVP again

Microsoft MVP award team has decided keep my MVP title for another year. Thanks Microsoft award team, Microsoft Sri Lanka, Lead Lilian Quek for re-awarding me.