Clear cache before generating the execution plan in SQL Server

Hi,

Are you doing SQL query tuning ???, so you must be generating execution plan...... Ok ..so before generating execution plan of your SQL query you shoud flush the proc out of your database and clear cache/buffers...below are the script for that.

1. Command to flush the proc out of database
DBCC FLUSHPROCINDB (db_id)

here db_id you can find by using following query in you database
Select db_id() -- this will return int number that number you can pass as db_id

2. Clear Cache

DBCC FREEPROCCACHE

2. Clear Buffers

DBCC DROPCLEANBUFFERS

After running above 3 commands in your database, you can generate execution plan, 

now you will  get exact execution plan.

How to generate data script for a table in SQL Server

Hi,

In your SQL Server database you might be having some master tables which contains master data entries of your application,

schema of these tables you can easily generate from SQL Server table properties and generate schema options, now if you want to generate data script of all the existing data in the table then you can use below SQL script to do that work

Say AppRoles table contains following data

AppRoleId	RoleDescription	Status
1	Admin	1
2	User	1
3	Guest	1
4	Demo User	1

select 'insert into tAppRoles (AppRoleID, RoleDescription, Status) values (''' + AppROleId + ''',''' + RoleDescription + ''',' + convert(varchar(1),status) + ')' from tAppRoles


Now when you will run above script in your database it will give you following result set

insert into tAppRoles (AppROleId, RoleDescription, Status) values (1, 'Admin', 1)
insert into tAppRoles (AppROleId, RoleDescription, Status) values (2, 'User', 1)
insert into tAppRoles (AppROleId, RoleDescription, Status) values (3, 'Guest', 1)
insert into tAppRoles (AppROleId, RoleDescription, Status) values (4, 'DemoUser', 1)

So this way you can get insert script of all the records, now just save it ....you are done !!!

SQL Reporting Services 2008 FAQs

Q:What is SQL Reporting Services?
A: SQL Server Reporting Services is a comprehensive, server-based solution that enables the creation, management, and delivery of both traditional, paper-oriented reports and interactive, Web-based reports. An integrated part of the Microsoft business intelligence framework, Reporting Services combines the data management capabilities of SQL Server and Microsoft Windows Server™ with familiar and powerful Microsoft Office System applications to deliver real-time information to support daily operations and drive decisions. (text from http://www.microsoft.com/sql/reporting/productinfo/overview.asp)

Q:How do I create a report?
A:There are 2 common ways of creating a report for use by SQL Reporting Services. Reports in SQL Reporting Services use the Report Description Language (RDL) which is an XML based standard for defining reports. Since it is simply XML, a report can, theoretically, be written using any text editor. This method, however, is not an approach favored by most since implementing RDL by hand can be laborious in addition to having a steep learning curve.

By far, the most common way of creating a report is by using the SQL Report Designer which is available to those owning licenses for both SQL Server and Visual Studio.NET 2008. The Report Designer is and Visual Studio.NET 2008 add-in packaged with SQL Reporting Services and, when installed, makes available a Report project type in Visual Studio.NET 2008.

Also on the horizon are numerous third party tools that are able to generate RDL. At the time of this writing, many of the available tools are still in beta testing but there are a handful that offer full products for consumer purchase and/or evaluation.

Q: Is the ReportViewer control included in SQL Server 2005/2008?
A: No. Report Server is a component of SQL Server 2005/2008, but the ReportViewer control is not. ReportViewer is only included in Visual Studio 2005/2008.

Q : Does SQL Server have to be installed in order to use the ReportViewer control?
A: No. The only prerequisite of the ReportViewer control is .NET Framework 2.0.

Q : Is a SQL Server license required to use the ReportViewer control?
A: No. The ReportViewer control is freely redistributable. It can work independently of SQL Server ('local mode') and a SQL Server license is not required. In remote mode the ReportViewer control talks to a Report Server. The Report Server does require a SQL Server license.

Q : Does the data for the report have to come from a SQL Server database?
A: No. Data can come from any source. It is the host application's responsibility to collect data from whatever source it needs to come from, and supply it to the control in the form of ADO.NET DataTables or a collection of business objects. The ReportViewer control does not know or care where the data is coming from.

Q: Can the ReportViewer view reports hosted on a SQL Server 2000 Report Server?
A: No. In remote mode the ReportViewer control can only view reports hosted on a SQL Server 2005 Report Server. Note that this has nothing to do with where the data for the report can come from. Data for the report can come from any source, including SQL Server 2000, Oracle, DB2 and non-database datasources.

Q: Is the ReportViewer control part of the .NET Framework?
A: No. The ReportViewer control is distributed with Visual Studio 2008, but it is not part of the .NET Framework.

Q: What editions of Visual Studio 2008 include ReportViewer?
A: ReportViewer is included in Standard and up. (i.e., all editions except Express.) An add-in is available for Visual Web Developer Express. (See below.)

Q8: What is the difference between RDL and RDLC formats?
A: RDL files are created by the SQL Server 2005 version of Report Designer. RDLC files are created by the Visual Studio 2008 version of Report Designer.
RDL and RDLC formats have the same XML schema. However, in RDLC files, some values (such as query text) are allowed to be empty, which means that they are not immediately ready to be published to a Report Server. The missing values can be entered by opening the RDLC file using the SQL Server 2005 version of Report Designer. (You have to rename .rdlc to .rdl first.)
RDL files are fully compatible with the ReportViewer control runtime. However, RDL files do not contain some information that the design-time of the ReportViewer control depends on for automatically generating data-binding code. By manually binding data, RDL files can be used in the ReportViewer control.
Note that the ReportViewer control does not contain any logic for connecting to databases or executing queries. By separating out such logic, the ReportViewer has been made compatible with all data sources, including non-database data sources. However this means that when an RDL file is used by the ReportViewer control, the SQL related information in the RDL file is simply ignored by the control. It is the host application's responsibility to connect to databases, execute queries and supply data to the ReportViewer control in the form of ADO.NET DataTables.

Q: What does the C in RDLC stand for?
A: The C stands for Client-side processing. RDL stands for Report Definition Language.

Q10: Why doesn't the ReportViewer control support parameter prompting in local mode?
A: The ReportViewer control does not prompt for parameters when in local mode. It prompts for parameters when it is connected to a Report Server.
In local mode it does not make sense for ReportViewer to prompt for parameters. The rationale is as follows: The most common use of report parameters is to pass to queries as values of query parameters. But unlike the Report Server, the ReportViewer control does not execute queries itself. Rather, queries are executed by the host application, and the result is passed to the ReportViewer control. So the ReportViewer control does not have the opportunity to set query parameters. Applications should take advantage of the parameterization features of Visual Studio data wizards instead.

Q: I installed Visual Studio 2008 and started Report Designer. I can't find the Preview tab. Where is the Preview tab that existed in SQL Server 2000 Report Designer?
A: There are two editions of Report Designer. One comes in the Visual Studio 2008 box. The other comes in the SQL Server 2005 box. If you are using the Report Designer included in SQL Server 2005, the Preview tab is still available. If you are using the Report Designer included in Visual Studio 2008, you have to run your application in order to preview the report.
Why is the Preview feature missing in the VS 2008 edition of Report Designer? Because the ReportViewer control does not contain any logic for connecting to databases or executing queries. Since it is your application's responsibility to supply data—and your application can supply data that originates anywhere, including objects that are internal to your application—data for previewing the report cannot be obtained without running your application.

Q: Why can't I just hand an RDL file to the report control and have it do everything and show me the report?
A: When the ReportViewer control is used in remote processing mode you can simply supply the url of the Report Server and the path to the report, and ReportViewer control will do the rest.
In local processing mode, the ReportViewer control is designed to integrate well with the host application. That includes being able to generate reports based on business objects that are internal to your application.
The ReportViewer control does not contain any logic for connecting to databases or executing queries. By separating out such logic, the ReportViewer has been made compatible with all kinds of data sources, including non-database data sources.
Rather than building in the logic to connect to databases and execute queries, ReportViewer is designed to integrate well with Visual Studio data wizards and the classes they generate, such as the TableAdapter class that knows how to connect to data sources, execute queries and fetch data.

Q: I dragged and dropped a field from the Data Sources window into the report. When I run the application the report only shows one row of data. How can I display all rows?
A: To see all rows, first drag and drop a Table or a List to the report, then drag and drop the field into the Table or List.

Q14: I am using the WebForms ReportViewer control. ReportViewer displays the message "Report is being generated" but the report is not displayed when this progress message goes away.
A: If you have set ReportViewer's height to a percentage, then in the .aspx file delete the line that sets doctype to xhtml. Why?

Q15: Does xls export require Excel to be installed?
A: No. The xls export feature is independent of Excel. Excel is only required to view the xls file.

Q16: Does PDF export require an Adobe license?
A: No. The ReportViewer control does not use any Adobe code to generate PDF files. PDF is an open format, and anyone can create PDF files without requiring a special license.

Q: What are the limitations of ReportViewer control?
A: Unlike the Report Server the ReportViewer control does not connect to databases or execute queries. Also, in local mode the only export formats available are Excel and PDF. (In remote mode all formats supported by the Report Server are available.) The ReportViewer control cannot be extended by adding custom renderers or custom report items.
Besides additional export formats, the Report Server offers better scalability, central storage, management and access control of reports, caching, subscriptions, etc.

Q: How can I learn more about Reporting Services?
A: Plenty of books and training are available.


Reference: http://www.gotreportviewer.com/

How to Install CRM Adapter for BizTalk Server 2009

Installing CRM for BizTalk Server 2006 is quite straight forward. The CRM adapter for BizTalk requires that BizTalk 2006 to be installed. But when I tried installing CRM adapter on BizTalk Server 2009, it fails.

What I realized while installing CRM adapter for BizTalk 2009, it is just checking if Biztalk 2006 installed on the system. So I started digging about and discovered that CRM installation is simply looking for the registry key that says BizTalk Server 2006 is installed. So here is a short step in order to get the adapter installed on BizTalk Server 2009.

Action items before installing CRM adapter for BizTalk 2009:

Before installing the CRM Biztalk adapter, take a backup of the registry (good practice) and then modify the following key (mentioned below), then install the adapter. After the adapter is installed, set the key back to its original value.

For 64 bit machine:

Start run >> type regedit then hit enter, navigate to

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\BizTalk Server\3.0

change ProductName = "Microsoft BizTalk Server 2009" to ProductName = "Microsoft BizTalk Server 2006"

For 32 bit machine:

Start run >> type regedit then hit enter, navigate to

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server\3.0

change ProductName = "Microsoft BizTalk Server 2009" to ProductName = "Microsoft BizTalk Server 2006"

You are done, now install CRM adapter, once installation is done successfully make sure you revert back the registry product name to its original value.