June 4, 2011

Microsoft Community TechDays at Ahmedabad

Great News, Community TechDays event is back on 11th June, 2011 at Ahmedabad. This time I got a chance to have a session in this Community TechDays.

There are attractive sessions as follows:

1. HTML5 - Future of the Web, Harish Vaidyanathan, Evangelist Lead, Microsoft
2. SQL Server Performance Tuning, Pinalkumar Dave, Evangelist, Microsoft
3. T-SQL Worst Practices, Jacob Sebastian, Microsoft MVP
4. LightSwitch On The Cloud!, Mahesh Dhola
5. Asp.Net Tips and Tricks, Me (Tejas Shah)

Please register for the event ASAP.

CTD 201

April 25, 2011

SSRS: Configure User Authentication with ReportCredentials

Many times I found that the Reporting service is configured to access as "Anonymous Access", so any user having URL can access reports from external world. Ideally it should be access by only users/application. For that SQL SERVER Reporting service should not be  accessed by "Anonymous Access". It also prevents Report server from unauthorized access.

NOTE: In shared hosting, Hosting provider provides username/password and only those users can get access to the reports for that specific user.

Today, I am going to explain to how to send user credentials from Report application, so Reporting service can authenticate user and allow to get reports. Only users having correct credentials can access the reports from the server. We are going to configure application to send credentials, so application can get access to the reports.

To configure application to get access reports from Shared hosting/Domain, we need to do following:

1. Get credentials from shared hosting/Domain controller to access the reports

2. Configure Application to Authenticate (.Net)

1. Get credentials:

Whenever we need to deploy/access report from the Report Server, we should have following information:

  • Report Server URL
  • UserName/Password to access the reports

This information will be provided by Shared hosting provider/Domain controller. Information like:

To check this information, Type Report URL in browser. It will ask for Username and password (if anonymous access is disabled). Enter given credentials, if Credentials are correct then Reporting service allow you to access the URL.

2. Configure Application to Authenticate (.Net)

As we get Basic information in step #1, now we need to build application that pass credentials to the Report server, so Report server authenticate the request and allow application to access the reports.

To authenticate request to Report server we need to implement IReportServerCredentials to send Report Credential to Report Server.

  • Create a class to implement IReportServerCredentials

SSRS ReportCredentials (2)

Please find VB code here:

Public Class ReportCredentials
Implements Microsoft.Reporting.WebForms.IReportServerCredentials

Private _userName As String, _password As String, _domain As String

Public Sub New(ByVal userName As String, ByVal password As String, ByVal domain As String)
_userName = userName
_password = password
_domain = domain
End Sub
Public ReadOnly Property ImpersonationUser() As System.Security.Principal.WindowsIdentity _
Implements Microsoft.Reporting.WebForms.IReportServerCredentials.ImpersonationUser
Get
Return Nothing
End Get
End Property

Public ReadOnly Property NetworkCredentials() As System.Net.ICredentials _
Implements Microsoft.Reporting.WebForms.IReportServerCredentials.NetworkCredentials
Get
Return New System.Net.NetworkCredential(_userName, _password, _domain)
End Get
End Property

Public Function GetFormsCredentials(ByRef authCoki As System.Net.Cookie,
ByRef userName As String,
ByRef password As String, _
ByRef authority As String) As Boolean _
Implements Microsoft.Reporting.WebForms.IReportServerCredentials.GetFormsCredentials
userName = _userName
password = _password
authority = _domain
authCoki = New System.Net.Cookie(".ASPXAUTH", ".ASPXAUTH", "/", "Domain")

Return True
End Function

End Class


Now we have created "ReportCredentials" class, now we need to use this class object to send credential info via Report Viewer Control as follows:

SSRS ReportCredentials 2

Please find VB code here:


Imports Microsoft.Reporting.WebForms

Partial Class Report
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles Me.Load

If Not IsPostBack Then
Dim ReportServerUrl As String = "https:///ReportServer"
Dim ReportDataSourceName As String = "/My Reports/"
With Me.ReportViewer
.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote
With .ServerReport
.ReportServerUrl = New Uri(ReportServerUrl)
.ReportPath = ReportDataSourceName + "ReportName"
.ReportServerCredentials = New _
ReportCredentials("Domain", "UserName", "Password")
.Refresh()
End With
End With
Page.Title = "Reports - " + Request.Params("Report")
If Request.Params("HideOptions") = 1 Then
ReportViewer.ShowParameterPrompts = False
End If
End If
End Sub
End Class

That's it, now you can access your reports deployed at Shared Hosting/Domain from application,
Report server allows to access if credentials are correct.

Let me know if you have any question/problem in deploying/accessing reports from Shared Hosting/Domain.

April 19, 2011

SSRS: Introduction to SQL SERVER Reporting Services

Report is a better presentation of data. From report user/Management can easily analyze the information. Report is easy to explain such information like Sales graph, profitability etc..

SQL SERVER provides rich kind of report that can be easily generated from SQL SERVER and send it to user/management in different formats like Excel, word, HTML etc..SSRS (SQL SERVER Reporting Services) also supports CHARTS which is very useful when we need to develop reports for Management.

To start with report, We can see how to create a report with SSRS. We will learn this by following steps:

1. Create Report.
2. Deploy Report on server
3. Use Report from application (Asp. Net)

1. Create a Report

  • Open Visual Studio and select "Report Server Project"

Introduction to SQL SERVER REPORTING Services SQLYoga#1

  • Check Solution Explorer (Right Side) and click on "Add New Report"

    Introduction to SQL SERVER REPORTING Services SQLYoga#2

  • Select Data Source. Enter information to connect with database and click on "Next"

    Introduction to SQL SERVER REPORTING Services SQLYoga#3

  • Build query which returns data to be displayed on the report.

    Introduction to SQL SERVER REPORTING Services SQLYoga#4

  • Select Report Format. For simple report we select "Tabular" format

    Introduction to SQL SERVER REPORTING Services SQLYoga#5

  • Select which are the columns that needs to be displayed on the report. User can also select columns if report needs to make GROUP BY

    Introduction to SQL SERVER REPORTING Services SQLYoga#6

    For Sample report, I have selected only some of columns that should be displayed on Report.

  • Select Table style

    Introduction to SQL SERVER REPORTING Services SQLYoga

  • Enter Report name, "Sample Report"

    Introduction to SQL SERVER REPORTING Services SQLYoga

    It displays summary info we have selected so far.

    And that's it.

    We are ready with the report now.

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Preview Tab, we can preview report

    Introduction to SQL SERVER REPORTING Services SQLYoga

    So, report is done. Now we need to deploy this report on server, so user can use it from server.

2. Deploy Report to Report Server:

    To deploy report on Report server, we need to have Report server information like Report Server URL, Credentials to connect to Report Server. Today we will just deploy report to Local Report server, I will explain in another post to How to deploy report to the server which requires credentials.

  • Click on Report Project and open Properties:

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Here we need to enter Report server information where we need to deploy this report and from where application/user can get this report.

    • Make OverwriteDataSources : True
    • TargetReportFolder : "My Reports" (given by hosting provider/Remote computer)
    • Target Server URL: <Report URL>(Report Server URL)

    Now we are done with server information and we need to deploy report.

  • Once properties are set, we need to deploy report on server, right click on report file and click on "Deploy"

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Now report is deployed on the server. We can check that report is on server or not by:

    1. Go to your browser (IE, FF, Chrome etc.)

    2. Enter URL: Go to Report Server URL (http://<IP/domain>/ReportServer

    3. It will display page like:

    Introduction to SQL SERVER REPORTING Services SQLYoga

    4. Click on "My Reports"

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Hurray, we can find our reports here on the server.

    Now, report is deployed on the server, now we need to build application from where we need to use this report


3. Use Report from application (Asp.Net)

  • To use this report from Web application, we need to create "New Website" OR you can use existing website if you have.

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Now, in the application, we need to create one ASPX page where we need to use ReportViewer control as follows:

    Introduction to SQL SERVER REPORTING Services SQLYoga

    Microsoft provides this control to load reports in web/desktop application. Now we need to write a code in this page, so Report Viewer control can load report on this page.

    Introduction to SQL SERVER REPORTING Services SQLYoga

     

This is simple code, which tells Report Viewer control to load report. Let me explain each line.

1. ReportServerUrl and ReportDataSourceName are set up with server information.

2. Then we need to setup "ReportName", it tells report viewer control to load that report from Report server.

     .ReportPath = ReportDataSourceName + "ReportName"

    NOTE: "ReportName", will be actual report name

3. And we also need to setup Report Credentials, so "Report server" can allow our application to access this report.

That's it.

Now run application and you can find that you can access "Reports" in application.

August 23, 2010

SQL SERVER: Display Row count of all the tables

Recently we have performance issue in SQL SERVER and we decided to move some large tables to different file group, so we can improve SQL SERVER performance, as it uses other disks I/O. Now challenge is to identify large tables from many tables (approx 700 tables).

There are few ways to display row count of all the tables:
  1. Use Procedure: sp_msForEachTable (Simplest way)

  2. Read information from system tables ( approximate count, Preffered)

Let me explain both of the method:

1. Use Procedure: sp_msForEachTable:

This is the simplest way that we can make COUNT(*) to each table to find number of rows from each table. This is undocumented stored procedure,"sp_msForEachTable" gives ability to make a query to all tables.
EXEC sp_msForEachTable 'SELECT ''?'' AS TableName,COUNT(*) AS NoOfRows FROM ?'

Its nothing but just a simple dynamic SQL statement where "?" will be replaced by table name. So query will be like:
SELECT COUNT(*) FROM BuildVersion


This procedure, "sp_msForEachTable", will loop to each table for current database and count rows from each table. So it gives result like:



By this way, we can get row count from all the tables for current database. But question is: How to use this result set? How to find out which table has maximum rows? To find out this, I need to check each table's rows manually and then only I can find out which table has maximum rows. This is time consuming and not the good way to find out table with maximum rows. Let see another method on which we can find it out it easily.

2. Read information from system tables:

By reading system tables, we can also find out rows from each table. We can use following query to find out number of rows from each table.
SELECT 
 st.Name AS TableName,
 SUM(  
  CASE  
   WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows  
   ELSE 0  
  END  
 ) AS NoOfRows
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.tables st ON st.object_id = p.Object_ID
INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
GROUP BY st.name

NOTE: This method is used to get an *approximate* count for all tables.

This query solved my purpose. In this query, it will give result in single result set, so we can manipulate it easily.



E.g. List out all tables and sort it out based on the NumberOfRows.
So, my query will be:
SELECT 
 st.Name AS TableName,
 SUM(  
  CASE  
   WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows  
   ELSE 0  
  END  
 ) AS NoOfRows
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.tables st ON st.object_id = p.Object_ID
INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
GROUP BY st.name
ORDER BY NoOfRows DESC


Let me know your opinions.

August 8, 2010

SQL SERVER Session at Dotnetchaps - Developer Conference 2010


Dotnetchaps is a group of people focused on Microsoft technologies .Net and SQL Server. The User Group organizes technical sessions for sharing knowledge on .Net and SQL Server. The motto of this group is to Learn, Share and Grow.

DotNetChaps user group is also associated with www.sqlpass.org community and officially opened the SqlPass chapter at Anand. The chapter name is Anand India Sql Server Users group.

Today, I am presenting a SQL SERVER session in this User Group meeting organized at SURAT, GUJARAT.

My session is at 2:10 PM which consists of following:

1. What's new in SQL SERVER 2008 R2?
2. Demo of SSRS Reports

In my SQL SERVER session, I am going to explain Utility Control Point (UCP), Data-Tier Application (DAC), Unicode compression. I am going to explain this feature by making examples on SQL SERVER R2, so people have better idea about it.

For SQL SERVER Reporting Services, its very simple to Design, develop and deploy report to report Server. It is very simple to integrate deployed report to web application.

August 1, 2010

SQL SERVER: Find executing queries

Recently my company owner reported that SQL SERVER is running slow. I searched for it and get following T-SQL query. SQL SERVER provides dynamic views, which contains current information about all details.
 
SELECT OBJECT_NAME(ObjectID) as ObjectName,
  st.Text, 
  DB_NAME(database_ID) as dbname,
  Blocking_session_ID as BlockingSessionID,  
  *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
 
It will give us details like about running SQL Objects, with Database name:
 
 
Reference : Tejas Shah http://www.SQLYoga.com

June 9, 2010

My First article on SQL SERVER Central about Access variables values from Trigger

I am glad to inform you all that, my article on "Accessing variable from Trigger", which I wrote a few days back, have been published by "SQL SERVER CENTRAL" site.

I would recommend all SQL lovers, to subscribe to the news letter of SQL SERVER CENTRAL, which consists of tips and tricks, useful in real world applications.

Please feel free to contact me at tejasnshah.it@gmail.com for any MS SQL SERVER query/help.

PS: Hope you all will read more and more articles from me, in forthcoming newsletters ;)

Enjoy Reading!!

April 6, 2010

SQL SERVER SSIS: Get File Name with Flat File Source, Data Flow Component

As I explained earlier about For Each Loop Container, which process each file from selected folder.

There is a requirement to save File name along with record, so later on we can identify which record comes from which file.

Let me explain it how to achieve with FileNameColumnName property of Flat File Connection to get it easily.

1. Setup Flat File Connection with CSV file, as mentioned Basic Example of Data Flow Task.
2. Right click on Flat File Source, and click on Show Advanced Editor:
Derived FileName Show Advanced Editor
3. Click on "Component Properties" and go to FileNameColumnName, Custom properties:
  Derived FileName Component Properties



4. Setup FileNameColumnName value with desired column name. Let's say "File Name". Congratulations, This column is added to output list with actual filename of that connection.

image
Let me know your comments/questions.
 

March 7, 2010

SQL SERVER: Execute Stored Procedure when SQL SERVER is started

We have a requirements to execute Stored Procedure when SQL SERVER is started/restarted and we need to start some processes. I found that SQL SERVER provides a way to call Stored Procedure when SQL services are restarted.

SQL SERVER provides this SP: "sp_procoption", which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way.

Let me show you how to use it. Syntax to use SP:

EXEC SP_PROCOPTION     
@ProcName = 'SPNAME',
@OptionName = 'startup',
@OptionValue = 'true/false OR on/off'



  • @ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in "master" database.
  • @OptionName, should be "startup" always.
  • @OptionValue, this should be set up to execute this given sp or not. If it is "true/on", given sp will be execute every time when SQL SERVER is started. If it is "false/off", it will not.


That's it, I hope this is very clear to use this feature.


Reference : Tejas Shah (http://www.SQLYoga.com)


January 10, 2010

SQL SERVER: SSIS - Derived Column Data Flow Transformation

As I explained earlier about Foreach Loop Container. One of regular reader of blog send me an email about one issue.

Let me share that problem with all readers.

With this example, Foreach Loop Container, What to do if we want to save file name along with each row, so we can come to know that which row is from which file ?

This is very practical problem that we need to fix.

To solve this, I come up with following solution.

1. I used "Derived Column", one of Data Flow Transformations in Data Flow Operations.


 SSIS For Each Loop Container II 

2. Configure Derived Column:

SSIS Derived Column Data Flow Transformation Configuration 
As we have variable, FileName, as defined in, SQL SERVER: SSIS - Foreach Loop Container. Here I used that variable as a new column. By dragging  that User variable to Expression.

By default it assign UNICODE STRING DataType to this new column. We need to change it by:

A. Right click on "Derived Column", Go to Show Advanced Editor
B. Set DataType to String as:

SSIS For Each Loop Container II Advanced Editor

3. That's it. Now just add it to Destination Column Mapping with your Database column.

Let me know your suggestions.