October 16, 2012

SQL SERVER: T-SQL to find out when database was restored

Recently, we have found that our testing database has been restored on the server and we lost our recent work. We have to identify that at what time the database has been restored, so we can know that what are the changes need to be restored again on the database.

We have found that MSDB database keeps information for each restore we have made on the server and luckily we have found the time and user we had restored the database. Let me share the simple script that I have found:

SELECT    *
FROM    msdb.dbo.RestoreHistory WITH (nolock)
WHERE    destination_database_name = '<Database Name>'
ORDER BY restore_date DESC

SQL Yoga T-SQL to find out when database was restored

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

October 15, 2012

SQL SERVER: Clear Job History

Recently, we have implemented few jobs and we were testing the jobs. After our test runs successfully, we wanted to clear Job’s history, so we can have accurate job status. I have found following SQL to delete job’s history:

USE msdb
GO

EXEC dbo.sp_purge_jobhistory
@job_name = '<Job Name>'

This will clear all history for the specified job. If you wanted to clear job history up to specific date, you can use following:
USE msdb
GO

EXEC dbo.sp_purge_jobhistory
@job_name = '<Job Name>' ,
@oldest_date='2012-10-10'

If you wanted to clear job history for ALL SQL jobs, you should just execute:

USE msdb
GO

EXEC dbo.sp_purge_jobhistory


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

October 11, 2012

SQL SERVER: Configure notification to send an email when JOB fails

In my previous post, T SQL to display Job history, I have mentioned to get list with the job statuses. One of my developer asked me to share, How to configure Notification to send an email when Job fails. Developers problem is, he is getting empty dropdown while configuring notification as shown in below figure:

SQL Yoga Configure notification to send an email when JOB fails #1
Here, This dropdown contains list of Operators, so we have to create operators to whom we can send a notification email. Its very simple to configure operator, see below:

SQL Yoga Configure notification to send an email when JOB fails, create Operator #2
Now, we have created Operator, so, lets set it up for the job as below:

SQL Yoga Configure notification to send an email when JOB fails #3
Now, Mail will be send to the email specified in “ErrorEmails” operator.

Note: Make sure to Configure Database email to send an email from the SQL SERVER.

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

October 10, 2012

SQL SERVER: T-SQL to display Job history

We have multiple jobs running on the server as per the schedules. We need to have the status of the each job at the end of the day, so we can know that which jobs runs successfully and which gets failed.

We have found the table, sysjobhistory, which contains history and get the list as per oyur requirements. Let me share the query:

SELECT
    j.name As JobName,
    run_date As RunDate,
    RIGHT('000000' + CONVERT(varchar(6), Run_Time), 6) AS RunTime,
    CASE h.run_Status   
        WHEN  0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN  'Retry'
        WHEN 3 THEN 'Cancelled'
    ELSE ''
    END AS JobStatus,
    RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) As JobDurationinHHMMSS
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j on h.job_id = j.job_id
    AND h.step_id = 0
ORDER BY j.Name, h.run_date

Here, we have used “h.step_id = 0”, as we just concern about SQL server job status, we are not concerned about each step of the job. This query gives us result as follows:

SQL Yoga T-SQL to display job history


We have also configured, Notification email, which sends an email whenever job is getting failed. This query is used to review all job statuses together. Please let me know your comments

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

September 29, 2012

SQL SERVER: SSMS, Allow to create Stored procedure though table doesn’t exists

We are working on to synchronize our new SQL instance, with existing database and need to make sure all SQL OBJECTS are created on the SQL server instance, so we can execute the same stored procedures from the new SQL instance.

Our assignment is to create same replica of existing database to new SQL instance. We were also pretty much sure because all our Stored Procedures are also created on the new instance. As soon as we try to execute the stored procedure, we are getting, very simple error like:

Invalid object name 'XXXXXX'.

When we debug that, we come to know that TABLES are not created on the new SQL instance because of one error. Our main question here is: How come SQL SERVER allows to create Stored procedures, if table doesn’t exists?

After review it, we come to know that SSMS 2012 has an option, that we can configure this restriction to allow or not to allow, Check for object existence

Check for object existence I SQLYoga Tejas Shah
By default, "Check for object existence” is configures as FALSE, so SQL Server allows user to create stored procedure without checking dependent object is exists or not. Lets see one simple demo:

--Checking, if table exists or not. If it is exists, delete the table
if object_id('SQLYoga') IS NOT NULL
    DROP TABLE SQLYoga

--Creating a Stored procdedure which using the object "SQLYoga" which we have just deleted
CREATE PROC StoredProcedureWithoutObject
As
BEGIN
    SELECT * FROM SQLYoga
END

This is very simple example that first we have checked that, if table called “SQLYoga” exists in the database then drop that table and create a stored procedure which uses this table. As Check for object existence configuration is setup as “to allow”, we are able to create the Stored procedure though table is not exists on the table. Stored procedure won’t be executed and will give an error at the time of execution, as table doesn’t exists. To enable this configuration, go to SSMS->Tools->Options->SQL Server Object Explorer and setup “Check for object existence” to TRUE.

Please let me know your comments

November 21, 2011

SQL SERVER: Develop Reports with RDLC (Asp.Net)

As, I mentioned in my post, Load Reports without SQL SERVER Reporting Service with Asp.Net, we can develop a report without SQL SERVER Reporting Service. That is very simple to do with Microsoft Visual Studio. Please follow the following steps to generate simple report which can be load in browser without SQL SERVER Reporting Service.

We need to follow following steps to generate simple report with RDLC:
1. Create RDLC file.
2. Create Dataset from Report Data Source.
3. Design the Report
4. Runtime pass information to Report Data Source, so report will be generated with Data
Let's create one simple web application with RDLC report.
1. Create RDLC file:
  1. In Solution Explorer, add a new folder and name it as "Reports".
  2. Right click on "Reports", select "Add" and click on" New Items".
  3. Give preferable name to report with extension RDLC and click Add.
image

2. Create Dataset from Report Data Source:
  1. Click on "Report Data" tab displayed on left side of the screen and in case, it is not displayed go to "View" menu and click on "Report Data".
  2. Click on "New" and select "Dataset" as displayed in following screenshot:
image
    3. If your "Data Source" is listed in options, select it from there, else click on "New".
image
     4. If your "Connection" is listed in options, select it from there, else click on "New Connection", to make your connection.

image
    5. Once "Connection" is selected, select your Table/View/Stored Procedure/Function, from which you need to build your report and give appropriate name to Dataset:

image
    6. Once you have selected DB Object in Dataset, you can find out all the columns that are available in selected dataset as follows:
2011-11-18_1506
     7. Once you have this dataset, you will also find that one XSD file is also created in App_code folder, with the same schema, which is used to provide schema definition to RDLC file

RDLC

3. Design the RDLC Report:
                    In Step #2, we have created Dataset with columns that needs to be displayed on the report.  Now its time to use that Dataset and design the report.
You should drag, "Table" from "Toolbox" and assign property "Dataset" created Dataset in Step #2.
  1. RDLC, allows you to design your report with schema that you have selected as DataSet
2011-11-18_1511
4. Runtime pass information to Report Data Source, so report will be generated with Data:
        So far, we have done report designing, now its time to pass actual data to the Report Data Source, so report will be generated with actual Data. To display report on the browser, we have to use "Microsoft Reporting WebForms", which will load the report in the browser with ASPX page. Please follow the steps to pass information to RDLC Report:
  1. Add an "aspx" page in the solution.
  2. We have to use "Report Viewer" control on that page, in which our Report will be displayed
  3. We also need to add few lines of code, to dynamically pass the information (actual data) to the Report Data Source as follows:
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Web.UI;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Reporting.WebForms;
using System.Collections.Generic;
using System.Linq;
using System.Web.UI.WebControls;
using System.Data;
using DataAccessLayer;
 
public partial class Reports_ProjectDetail : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
          
               string connectionString = ConfigurationManager.ConnectionStrings["PISConnectionString"].ConnectionString;
               Database db = new SqlDatabase(connectionString);
               DbCommand command = db.GetStoredProcCommand("ReportProjectDetail");
        DataSet dataset = db.ExecuteDataSet(command);
               ReportDataSource datasource = new ReportDataSource("DataSet1", Dataset.Tables[0]);
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(datasource);
        ReportViewer1.LocalReport.Refresh();
        ReportViewer1.Visible = true;      
 }
    }
 
   
}










Let me explain how it works:

1. On Page load, we call one stored procedure to get data from database. Here I have used Microsoft Application block to connect to database and get the information
2. Once, we have the data in DataSet, we have created new "Report Data Source" with the SAME name: "Dataset1" and assign result table as per #1 and assign it to ReportViewer
3. And at last we refresh ReportViewer to display the information as per Datasource provided.

reprotviewf1
This report is ready and you can export it in  Excel, PDF, Word etc. This example is developed in Microsoft Visual Studio 2010

Let me know your comments and difficulties, if any

Reference: Tejas Shah (www.SQLYoga.com)

September 13, 2011

SQL SERVER: Load Reports without SQL SERVER Reporting Service with Asp.Net

Recently, we have weekly meetings with my team and one of developer is come up with question that, "Can we use Reports without having SQL SERVER Report Services?". That is very good question that came up by him, as he says client does not want to have SQL SERVER Reporting Services. Client SQL SERVER is hosted at Shared Hosting and does not want to extra pay to use SQL SERVER Reporting Service.


Answer of his question is, YES. We can have reports without using SQL SERVER Reporting Service with same layout.


Microsoft Visual Studio is providing file called "RDLC" file, by which we can develop report in Microsoft Visual Studio and that Report doesn't need to deploy on Report server and will be called from Asp.Net application itself. By RDLC, it is very simple to load report in browser. We are out of issue to deploy reports on the Report Server and execute it from there. RDLC file remains there with project files.


In the Next post, I will explain in details to configure RDLC report with ASP.Net

June 15, 2011

Community TechDays at Ahmedabad, 2011

 
We had an excellent Community TechDays on 11th June 2011 at Ahmedabad. We have huge crowd to attend the sessions. Please find presentations and demos which speakers have presented during the session.
 
harish

Harish Vaidyanathan [twitter]

Topic: HTML 5
Download Presentation and Demo Files

 
 jacob

Jacob Sebastian [twitter | blog]

Topic: SQL SERVER Worst practices

Download Presentation and Demo Files

 
pinal

Pinalkumar Dave [twitter | blog]

Topic: SQL SERVER Performance troubleshooting using Waits and Queues

 
tejasshah

Tejas Shah [twitter | blog]

Topic: Asp.Net Tips and Tricks

Download Presentation and Demo Files

 

We will upload this session photos soon

 

 

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.