Reza Alirezaei’s Blog

Quite frankly this subject is a bit tricky and usually highly subjective point of view, so you probably never get a clear answer, unless you spend a considerable amount of time to find out different methodologies and compare them with your needs and start forming your own strategy based on different scenarios. Hereby I also tried to address it from my own perspective and according to my experience. In other words this is what I do in most of my work with technology.

• Rushing blindly into technology, without being fully aware of business needs and conducting more research on real-world business problems instead of more research on the product itself is something that you seriously may want to avoid.
  
  
• Again it varies from one case to another case, but generally speaking I’d prefer to have the implementation to a minimum level of risk from the technology perspective and once achieved, I’ll take a higher level cooperation between technology and business process. I have not invented this approach.It is proven by a lot of ERP vendors and is known to be very effective. .
  
  
• To get more out of your technology investment, the easiest way is to INTEGRATE everything together to have a better understanding on whether you need to apply technology or not. Why having bundles of software and services if you can easily package everything?
  
  
• Always ask yourself questions as follows in advance : 
  
  
  
  
  • Do I have a clear hard look at the Return on Investment (ROI) if I ever decide to bring the technology in-house?
    
  • What is the Total Cost of Ownership (TCO) and how it affects your other objectives?
    
    
  • Variety of solutions in the today’s IT market has made most of enterprise environments heterogeneous which means different platforms and that means Maintaining Compatibility (my nightmare;-)) – Is the technology (you are bringing in)  platform dependant or completely neutral?
  
  
  
• Software as a Service (SaaS) or On-demand concept in some cases is a better way to go for. Think ahead to see if you can use the technology as a service with low capital investments and rapid realization of the business benefit.

To wrap it up, I believe that true business value comes through how expertise is delivered, and the way that expertise is delivered is not necessarily diving into the new technology. Watch out not to get trapped by toolsets, utilities and etc, which are notoriously designed to help you to accomplish things better rather than putting you in trouble!

Happy incorporating and good luck with your business

Let’s say that you have a SELECT statement that needs to return some rows. But as the SELECT statement is running, it run into a row that it needs, but that row has been previously locked, and is still locked. In most cases, the SELECT statement will have to wait until the blocking lock is released. This could be a second, a minute, or who knows how long. Let’s also assume that you don’t really care that much if the locked row is returned or not, and that you are more concerned with performance than accuracy. Or, perhaps you goal is even to prevent the same user from viewing the same data at the same time, and you don’t want any currently locked rows to be viewed by your SELECT statement.

If the above is true, consider using the READPAST locking hint. If this hint is added to a SELECT query (and it can only be used in a SELECT statement), if a row it needs is locked with a row-level lock (only, other locks are still honored), it will skip that locked record and continue on, until it is done, preventing a lock from stalling the query, providing quick results. Also, for this hint to work, the READ COMMITTED isolation level has to be in place.

While using this lock will be rare, it can come in handy in unique situations, helping to boost the performance of your application.

*****

When you normally SELECT data from a table, SQL Server will apply a shared lock on the record or records you are examining. A shared lock allows other transactions to read the same records as you, but the other ones can’t modify these records.

If you intend to UPDATE a record, SQL Server will apply what is called an update lock when it first reads the record, and then when you are ready to actually perform the UPDATE, the update lock is changed to an exclusive lock while the update occurs, and then the lock is released. When an update lock is held on a record, it does allow other transactions to see the record, but it prevents the other transaction from acquiring an exclusive lock on it. This is done in order to prevent potential deadlocks. Another transaction can only get an exclusive lock on the record once the update or exclusive lock is removed.

As you noticed, when you SELECT records, only a shared lock is used. And if you want to UPDATE a record, then an update lock is used. But what if you want to use a SELECT statement, but want to force an updated lock, not a shared lock. This is possible if you use the UPDLOCK hint. Adding the UPDLOCK hint to a SELECT statement uses an update lock instead of a shared lock. Sometime, this can come in handy when you want to read some records, but don’t want to block others from reading the same records, but you want to be able to UPDATE these records you have SELECTED knowing for sure that the data you SELECTED has not changed in the meantime. If you just SELECTED the record without the UPDLOCK hint, and then decided to later, in the same transaction to modify them, you would not be assured the records you SELECTED were not changed because another user could have changed the records.

*****

Sometimes, it is important that you get an exclusive lock until the end of your transaction. You can accomplish this using the XLOCK hint. If desired, you can combine the XLOCK hint with the PAGLOCK or the TABLOCK hints, but it cannot be used along with the NOLOCK or the UPDLOCK hints.

If you do use this hint, keep in mind that using it can reduce concurrency, which can hurt your database’s performance.

CREATE PROCEDURE spAddEmployee

(

      @FirstName varchar(50),

      @LastName varchar(50)

)

AS

DECLARE @Result int

BEGIN TRANSACTION

IF EXISTS

(

      SELECT

            NULL

      FROM

            Employees WITH (UPDLOCK)

      WHERE

            FirstName = @FirstName AND

            LastName = @LastName

)

      BEGIN

            SELECT @Result = -1

      END

ELSE

      BEGIN

            INSERT INTO

                  Employees

            (

                  FirstName,

                  LastName

            )

            VALUES

            (

                  @FirstName,

                  @LastName

            )

            SELECT @Result = @@ERROR

      END

IF @Result <> 0

      BEGIN

            ROLLBACK

      END

ELSE

      BEGIN

            COMMIT

      END

RETURN @Result

First of all, I have been fairly busy since I last updated my English blog as things are crazily stressful lately.I really forgot that I have a blog that I’m sort of committed to keep it up-to-date ,but good news is that ,I prepared couple of data mining articles and case studies in regards to CF applications to be published in my weblog as soon as I can. BTW MSDN guys promised me not to cut off my article on “How to implement an interactive installation package for your CF Application” and also publish it not later than the end of November (Apparently there are tons of articles waiting to be picked up there), but honestly cannot wait until it gets stale, I will be bringing it up in summary soon in my weblog, so stay tuned.

I’ve seen this annoying issue in both sharepoint portal server and Reporting services. I don’t really grasp that why error handling within some objects (say, chart object in the reporting services) output the inner exception to the end user!? What’s the use of that? Look at the scary exception below:

· Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. (rrRenderingError) Get Online Help

• Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown.
  
  
  
  • An error has occurred during rendering of chart MarkDistributionGraphEnglish. Details: Axis object – Auto Interval Error (rsErrorDuringChartRendering)
    
    
    
    • Axis object – Auto Interval Error

Well, if there is an internal error in any objects, they could have logged it somewhere and just simply STOP the object from rendering its content. 

If you’ve ever filtered the data within the report using reporting services GUI, you may have realized that “%” as an escape character (wildcard) won’t work and you need to replace it with asterisk(“*”) instead.Let’s say you want to filter a field of the dataset called “Fields!Employees_Name.Value” to only contain employees whose first names start with “Jo”. Obviously there are two ways to do so:

1. Return the filtered data from the data source and just show it in the report
   
2. Return the raw (non-filtered) data  from the data source and just filter it out either on data source level or the container bound to the data source (In this case a Table)

Well, I usually go for the first option, but there are some cases that your data source is shared between couple of containers in your report and you cannot filter it from the scratch. In these circumstances you got to define custom filters on container you wish to show data in it.If you right click on the table object in the report designer, select properties and choose the “Filter Tab” ,you can define the filters there using different operators ( = ,<>,Like and etc) . In our example we expect something like

            Expression                                        Operator                    Value

  Fields!Employees_Name.Value                          Like                             Jo%          (WRONG)

Amazingly this doesn’t work.I don’t understand why the standard T-SQL escape characters are changed in reporting services for Like operator!? , but the right way of doing that is as follow:

            Expression                                        Operator                      Value

Fields!Employees_Name.Value                            Like                             “Jo*”        (RIGHT)

Now the question is what if I want to filter all the names starting with “Jo” OR “Te”?.First thing you may do is to add another line, and then you are tapped because GUI by default “AND” the conditions instead of “OR” and the GUI doesn’t let you change “AND” to “OR”, because the operator “LIKE” requires exactly 1 and only 1 filter value.If you still want to have a wildcard in both statements, then I guess you cannot do it in that way (you’d better change your data source to return the filtered data), but if you know exactly what you are looking for then there is a workaround which is directly changing the RDL (XML –Formatted) code. You need to use operator “IN” instead of “Like” as follows:

<Filters>
  <Filter>       
     <FilterExpression>=Fields!Employees_Name.Value</FilterExpression>
     <Operator>In</Operator>
     <FilterValues>
          <FilterValue>John</FilterValue>
          <FilterValue>Johan</FilterValue>
          <FilterValue>Johnoton</FilterValue>
     </FilterValues>
   </Filter>
</Filters>