Warning – some % of consumed workload has syntax errors. Check tuning log for more information in SQL Server 2008/2005

I have seen many SQL Developer used to use Database tuning advisor and many of them meet with warning like below.
30% of consumed workload has syntax errors. Check tuning log for more information.
You can see above error (percentage ratio may be changed in case to case) and think that your query is perfect and there is not error at all than why DTA (Database Tuning Advisor) showing syntax error?
Actually DTA is not much powerful to understand if you are using temp table in your stored procedure or calling sub stored procedure or may be using user define functions and it shows above warning. DTA works well only with simple Vanilla type plain SELECT statement.
So in that case, you have to find out the query on which you have doubts, run that query and records it in profiler and get help of DTA.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Error Fix: Msg 8101 An explicit value for the identity column in table ‘idIn_copy’ can only be specified when a column list is used and IDENTITY_INSERT is ON

This is really very well known and famous error when you are trying to insert value in identity column.  If you want to get rid of this error, you have to set “Identity_Insert” property to ON so that you can manually insert Identity value.
Let us see one small example.
–Source table
create table idIn
(
id int identity(1,1),
name varchar(10)
)
go
–destination table
create table idIn_copy
(
id int identity(1,1),
name varchar(10)
)
GO
–insert data into source table
insert into idIn
select ‘ritesh’ union all
select ‘rajan’
GO
–setting Identity_insert property ON
SET IDENTITY_INSERT idIn_copy ON
GO
–insert from source table to destination
insert into idIn_copy
select * from idIn
go
–setting Identity_Insert property to OFF
SET IDENTITY_INSERT idIn_copy OFF
go
One you will run INSERT query after setting IDENTITY_INSERT on, you will get the same error J
You have to give field list in INSERT statement, once you will give it, you will be ok. Have a look:
–setting Identity_insert property ON
SET IDENTITY_INSERT idIn_copy ON
GO
–insert from source table to destination
insert into idIn_copy(id,name)
select id,name from idIn
go
–setting Identity_Insert property to OFF
SET IDENTITY_INSERT idIn_copy OFF
go
This test is perform on SQL Server 2008 but expect to have same result in SQL Server 2005 too.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Save sub report as from main crystal report

Recently I was working in one of the very big windows application which has been developed with the following tools, framework and software.
1.)    Visual Studio 2008
2.)    .Net Framework 3.5
3.)    C#
4.)    SQL Server 2005
5.)    Crystal Report 11
There are lots of crystal reports used in that application. While working on that application (product) I needed to create on very big crystal report. Fortunately or unfortunately the same kind of report was a part of (as a sub report) one main report. If I can extract that sub report from main reports, I could probably save my so many hours.
Basically we used to create a simple report and import that report as a part of sub report in other main reports. Unfortunately main report was deleted by somebody so the only way was to extract report from main report as that report was working fine under main report.
I have opened that main report which has a link to sub report, I right click on that sub report link to get option “Save Sub report as” in visual studio but no luck.  Look at the image below.
Actually visual studio doesn’t have that option so I have seen many programmers start looking at sub report and create new report based on that which kills few hours if report is big.
So what is the solution? If you are having full licensed version of crystal report than open crystal report viewer, open same report in that and you will get an option to “Save sub report as” look at image below.
Isn’t it easy and time saving stuff?
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah