How to resolve Failed to create designer “Microsoft.Reporting.WebForms.ReportViewer” ?

 

Dear All,

One of frustrating point is when you are in hurry and you got error like “Microsoft.Reporting.WebForms.ReportViewer” .

As shown in below figure.

Actually , I was trying to migrate one web project from vs2010 to vs2013 and I faced this issue.

To resolve this I did following changes

Updated  keys which are highlighted in web.config

<compilation debug="false" targetFramework="4.5">
  <assemblies>
    <add assembly="Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
    <add assembly="Microsoft.ReportViewer.Common, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
    <add assembly="Microsoft.Build.Framework, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
    <add assembly="System.Management, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
  </assemblies>
  <buildProviders>
    <add extension=".rdlc" type="Microsoft.Reporting.RdlBuildProvider, Microsoft.ReportViewer.Common, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
  </buildProviders>
</compilation>

 

<httpHandlers>
     <add path="Reserved.ReportViewerWebControl.axd" verb="*" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" validate="false" />
   </httpHandlers>

<handlers>
     <add name="ReportViewerWebControlHandler" preCondition="integratedMode" verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
   </handlers>

And on page

<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %><%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

After changing this my problem is not resolve exactly. So I have updated NUGET Package for ReportViewer and we are good to go.

Thanks

RJ!!

How to resolve “No Entity Framework provider found for the ADO.NET provider with invariant name” ?

 

Hello friends,

sometimes you face lot of issue when you trying to deploy the the WCF/Website code which reference another project code in which we have used Entity framework.

We were very happy at the time of development and when we go to deploy it we faced a problem which state that

“No Entity Framework provider found for the ADO.NET provider with invariant name “

When I first time saw this error I thought it will resolve easily few simple configuration changes. I searched on Google and found numerous solutions which state to update entity framework version which I already updated.

Few post state configuration changes in web.config which I did but still same issue.

After sometime I just thought to copy entity framework related all the dll .

And ” Eureka ! “ all is working fine now.

Actually EntityFramework.SQLSERVER.dll was not in the folder and after copying this everything is working.

I hope when you this your problem also resolve.

Thanks

RJ !!

COUNT_BIG() is for counting big range TIP #50

 

Problem:- How to count large number of rows for specific condition ?

Solution

Suppose you have a large table which having more than millions of records and you want to count no of rows for specific condition  which itself contain more than 100 millions records.

Now in this case when we use COUNT function then we will get arithmetic overflow error message.

Now in such situation we have another function which can count this big ranges specially numeric data types.

The use of this is similar as COUNT function

For example you have to write

SELECT COUNT_BIG(1) FROM  tblMyLargeTable

I hope it may help you some time

Thanks & Enjoy

RJ !!!

How to get records who has phonetic similarity - a hidden feature in SQL SERVER TIP #49

Sometimes , It may required that you want a query who can provide you result which have phonetic similarity.

Lets understand this by example

Suppose you want to search a record whose pronunciation or phonetic sound are similar or close to then in that case you can use SOUNDEX property of  SQL SERVER which is a hidden feature and rarely used.

To understand it lets jump into the example.

Suppose you need to find records from people  table of adventureworks database whose firstname sound like “JOHN”

so to achieve this we will write following query

SELECT  FirstName,LastName
FROM Person.Person
WHERE SOUNDEX(FirstName)= SOUNDEX('John')

It will return all those records whose firstname sounds like “John”

please see below screen for more detail

I hope it may help you somewhere.

Enjoy !!!

RJ!!

A Interview question How to determine what value you have updated of a column ? A output clause example #TIP 48

 

Problem:-

Sometimes it may require that whatever you have updated you want to know what was the previous value of that column?

It was asked by many interviewer in the interview how to avoid trigger or how to know what was previous value before update ?

Solution:-

Now to achieve this the basic step is before updating the record select those value and have it in a temporary variable.

But We are doing here by a new feature (not exactly new introduced in SQL Server 2005) which is OUTPUT CLAUSE

Suppose I have tbluser table in which a user record with id 1 having userame “staff”

Now I have to update this in capital letter. To achieve this we have to write following query

UPDATE tblUser
SET UserName = 'STAFF'
OUTPUT INSERTED.UserName,
   deleted.UserName
WHERE userId= 1
   

When you run it you will get output as shown below

 

I hope it may helpful you somewhere enjoy !!!

RJ!!!

CONCAT a valuable function TIP #47

 

This is one  the new feature in SQL Server 2012 by which you can concatenate values.

The USP of the function is that it handle the NULL values also.

means if you are concatenating values in which null also exists then it handle those null value also means it concatenates only those values which does not have null.

For example

Suppose I want to concatenate the column like product name, product number, color and class of Product table of adventureWorks database and it might be possible that class & color may have null value. So we don’t need to do extra care of  those nullable column. We can write simple query as shown below

I hope this feature may help you somewhere in your technical life.

Thanks & Enjoy

Rj !!

Find first not null value from different columns TIP # 47

 

Problem:

Sometimes, it might be possible that we need not null value only from particular columns and if all column have null value then we provide a default value.

Lets understand this by a general and very interesting example

suppose a friend come to your house and you want to give him a treat then you check your first column or we can say first option “Is there any thing to eat ?” if that value is null then you go for second column or we can say second option “ Is there any thing to drink ?”

If that value is also null then you will choose 3rd or default option and ask friend to give you treat.

Isn’t it simple . Just kidding

Let’s understand  now with adventurework’s product table.

Suppose, We want to fetch productId, productname,product number, and any property (either color, class) and if both the column (color, class) are null then need to display “No Property found” in the column value.

so  I wrote following query  to achieve this

SELECT PRODUCTID ,
      Name,
     ProductNumber,
COALESCE(Color,class,'No Property found') As productProperty
FROM [Production].[Product]

so if you observer above figure you will find in the records where color found color value appear and if color value is null and class value found the class value appear and if both color and class value is null then we provide simple value which is “No Property found”

I hope this may help you.

Thanks  & Enjoy

RJ !!

Performance tips - How to determine last statistics update in table ? TIP #46

Hi,

For maintenance purpose we need to determine when the stats last update and analyze the data and if data is out dated then we need to update the stats.

To determine this we need to run the following scripts

SELECT OBJECT_NAME(s.object_id) AS [ObjectName]
      ,s.name AS [StatisticName]
      ,STATS_DATE(s.object_id, [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats s
INNER JOIN sys.objects obj ON obj.object_id = s.object_id
AND obj.type IN ('U','V')
ORDER BY STATS_DATE(s.object_id, [stats_id]) Asc,obj.name

 

For example I run the following code in adventurework2012

 

So according to update status date we do update statistics of those tables in database.

I hope this tip will help you.

Enjoy !!!

RJ

3 different way to find row count of a table ? TIP #45

 

Problem:-

Many times you want to know how many rows exists in the particular tables.

So let me share 3 different way to know this thing

Solution:-

Let me share 3 different options

Option 1:-

You all aware of this term which is Count function

SELECT COUNT(1)  As Rows FROM [Person].[Person]

Option 2:-

Sp_Space used is another way to determine rows in table as shown in below figure

Option 3:-

it is little bit tricky but you will enjoy seeing this. We count the row number from cluster index

 

I hope you enjoyed.

Thanks & Enjoy !!!

RJ

First_value & Last_Value according to group set is that easy ? tip #44

 

Problem:- 

Most of the time we require data in which we require first value and last value from different group of rows. Now how easy we can get result this is one of the challenge for us.

Solution:-

Lets understand this by an example. Suppose you have a sales table in which you maintain daily sales. Now your want a result sent in which you know what is first sale of the day and what is last sale of the day.

SQL SERVER 2012 provides you facility to achieve this task easily with First_Value & Last_Value function.

The syntax of first_value & Last value is exactly same as Row_Number, Dense_Rank & Rank_function.

See below example in which I have used Adventureworks SalesOrderHeader table.

Now if you see below snap I took a random date 2005-07-12 and fetched record and highlighted is first row & Last Row.

So on date 2005-07-2012 Sales order have 3953.9884 as a frist value and  772.5036 as a second value.

 

Now above specific result  we can achieve by first_Value & Last_Value function of SQL SERVER 2012 as shown below

so, In this way you can achieve the first_Value & last_Value from a group of rows

I hope this may help you somewhere.

Enjoy !!!

RJ