SSRS (SQL Server Reporting Services)

SQL Server Reporting Services provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports for your organization, as well as programming features that enable you to extend and customize your reporting functionality.

Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality for a variety of data sources. Reporting Services includes a complete set of tools for you to create, manage, and deliver reports, and APIs that enable developers to integrate or extend data and report processing in custom applications. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components.

With Reporting Services, you can create interactive, tabular, graphical, or free-form reports from relational, multidimensional, or XML-based data sources. You can publish reports, schedule report processing, or access reports on-demand. Reporting Services also enables you to create ad hoc reports based on predefined models, and to interactively explore data within the model. You can select from a variety of viewing formats, export reports to other applications, and subscribe to published reports. The reports that you create can be viewed over a Web-based connection or as part of a Microsoft Windows application or SharePoint site. Reporting Services provides the key to your business data.

SQL CMD Utility

What is XP_CMDSHELL ?

XP_CMDSHELL - extended stored procedure.
With this extended stored procedure you have the ability to run any command line process within your SQL script, so you can embed this within your stored procedures, jobs or batch processing.
In SQL Server 2000 this option is enabled by default, but to limit access to using xp_cmdshell only members of the sysadmin server role have default rights.
To extend rights to other users you can use the GRANT statement to allow execute rights.
With SQL Server 2005 the use of xp_cmdshell is turned off by default. As SQL Server continues to mature Microsoft has put many safeguards in place to minimize the security risks of unwanted code being executed within SQL Server or outside of SQL Server.

Examples of XP_SMDSHELL Commands

     1) XP_CMDSHELL Directory Search

For example. If the User dose not have access to the physical SQL Server machine, but if he want to see all the files in the “C:\temp” directory that have a “.sql” extension. He can do this by issuing the following command:

exec master.dbo.xp_cmdshell ‘dir c:\temp\*.sql’

    2) Create a new directory using XP_CMDSHELL
This is the simple example Imagine a user is in process of building a new SQL Agent job using Enterprise Manager. To organize the output from all the steps in this new job, he want the output of each step to go into a common directory on the server, where each step has its own output file. The only problem is the new directory does not exist. here we assume that used do not have access to the server, or do not want to walk down the hall to the computer room to create this new directory. No problem he can just enter the following command in Query Analyzer from the privacy of your office to accomplish creating the new directory:

exec master.dbo.xp_cmdshell ‘mkdir “c:\temp\SQL Agent Output\new_job\*


3) Run DTS package via stored procedure using XP_CMDSHELL
As you can see, it is very easy to execute DOS commands using xp_cmdshell. So can xp_cmdshell also run executables? The answer is YES! Suppose you have a DTS Package that you want to run via a stored procedure. There is no “sp_executeDTS” stored procedure, but you can use the following xp_cmdshell command to run your DTS package:

exec master.dbo.xp_cmdshell ‘dtsrun -E -Sserver1 -N”Export Invoices”‘

Note :This command executed the “dtsrun” executable with the necessary parameters to run the “Export Invoices” DTS package. Are you starting to see how the xp_cmdshell extented store procedure can provide you with some alternatives and also expands the functionality you can build into a T-SQL script.

CTE [Common Table Expression]

Overview : 
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
A CTE can be used to:

• Create a recursive query.
• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.
CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Structure of a CTE

---

Example :
A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT <column_list>

FROM expression_name;