T-SQL to find out Space by File Group

T-SQL script to find out SQL SERVER FileGroup space details:

SELECT
 fg.groupname AS 'File Group',
 Name, 
 CONVERT (Decimal(15,2),ROUND(f.Size/128.000,2)) 
  [Currently Allocated Space (MB)],
 CONVERT (Decimal(15,2),
 ROUND(FILEPROPERTY(f.Name,'SpaceUsed')/128.000,2))
  AS [Space Used (MB)],
 CONVERT (Decimal(15,2),
 ROUND((f.Size-FILEPROPERTY(f.Name,'SpaceUsed'))/128.000,2))
  AS [Available Space (MB)]
FROM dbo.sysfiles f (NOLOCK)
JOIN sysfilegroups fg (NOLOCK) ON f.groupid = fg.groupid
ORDER BY fg.groupname

Read More

SQL SERVER: T-SQL to read all attributes of the XML

We have reviewed how to read XML with SQL server in this Post, and how to read XML with Namespace in this POST. Today I would like to share how to read all attributes of the XML and have result in Table format.

 

Recently, my team assigned one requirement to import XML file. To read an XML file is achieved by following this POST but here, we have a requirement to read each Attribute name and Attribute Value to store that info in normalized table by comparing Attribute Name. Let me explain with example.

 

XML:

 

Expected Result:

 

Query to achieve the same:

DECLARE @XML AS XML

SELECT @XML = ''

SELECT CAST(x.v.query('local-name(.)') AS VARCHAR(100)) AS AttributeName
 ,x.v.value('.', 'VARCHAR(100)') AttributeValue
FROM @XML.nodes('//@*') x(v)
ORDER BY AttributeName

Here, We have used “@*”, which gives us way to read the attributes for the ROOT tag and “local-name” gives is the Attribute name. That’s it.

 

Reference: Tejas Shah ( www.SQLYoga.com )

Read More

SQL Yoga – Read XML with T-SQL statement in SQL SERVER

In my previous post, “XML Result sets with SQL Server”, we review to generate result sets in XML from SQL server. Then I got a comment from the team, to also have post to read XML in SQL Server. To read XML in SQL server, is also simple. Lets read the XML which is created by XML PATH in previous post. Read XML Elements with T-SQL: DECLARE @SQLYoga TABLE(     ID INT IDENTITY,     Data VARCHAR(50),     CreatedDate DATETIME DEFAULT(GETDATE()),     CreatedBy INT     ) INSERT INTO @SQLYoga(Data) SELECT 'SQLYoga' UNION ALL SELECT 'Tejas Shah' UNION ALL SELECT 'Generate XML' DECLARE @xml XML SELECT @xml = (         SELECT    *         FROM    @SQLYoga         FOR XML PATH('Record'), ROOT('Records')     ) SELECT @xml Now, please find query to read the query to read XML generated above: SELECT         x.v.value('ID[1]', 'INT') AS ID,         x.v.value('Data[1]', 'VARCHAR(50)') As Data,         x.v.value('CreatedDate[1]', 'DATETIME') AS CreatedDate FROM    @xml.nodes('/Records/Record') x(v) This query generates the output as follows: That’s it. It is much simple and you can get rid of the complex coding in application. Let me know your comments or issues you are facing while working on this. Reference: Tejas Shah (www.SQLYoga.com)

Read More

SQLYoga – XML Result sets with SQL Server

Recently, found that people making much complex code to have an XML in application. I have found that they return result set in Table to the application and have applied code to convert that data table to XML by Asp. Net application. When I review the process, I have suggested that why don’t you use the XML feature of the application. They surprised the simple use of it and make their life easy. Let me have sample query to generate XML result sets in SQL Server database. There are two ways to have an XML from query: 1. FOR XML RAW 2. FOR XML PATH Please find following sample queries where both of the way are being used: Lets create sample data DECLARE @SQLYoga TABLE(     ID INT IDENTITY,     Data VARCHAR(50),     CreatedDate DATETIME DEFAULT(GETDATE()),     CreatedBy INT     ) INSERT INTO @SQLYoga(Data) SELECT 'SQLYoga' UNION ALL SELECT 'Tejas Shah' UNION ALL SELECT 'Generate XML' Generate XML with XML ROW SELECT * FROM @SQLYoga FOR XML RAW, ROOT('Records') XML RAW, returns the XML by each record with row element and all columns as attributes in the XML. Generate XML with XML PATH SELECT    * FROM    @SQLYoga FOR XML PATH('Record'), ROOT('Records') XML PATH, returns the XML by each record with separate element and also columns as element within respected element of the record. That’s it. It is much simple and you can get rid of the complex coding in application. Let me know your comments or issues you are facing while working on this. Reference: Tejas Shah (www.SQLYoga.com)

Read More

SQL SERVER: Backup the database and upload it on FTP and on Network

Recently one of my friend called me and asked me that his MS SQL SERVER Database server is crashed and the problem is, he has the database backups on the same server. So now as the server is crashed, and he cannot have any backup to restore the database. I suggested him, first get data recovery tool to get the data from crashed server and then restore it to make it work, so application will be up. Then, to overcome this kind of problem in future, I suggested him to always keep the database backup on another server and it is best to have database back up on another location too. He agreed with me that, and asked me a good question that: "Do you know if there is any kind of software by which I can schedule this and get out of this problem?" As he needs the solution quickly, I checked it out some Tools and I come with the very cool tool "SQL SERVER Backup And FTP". This is very simple to use. This tool works as: Connect SQL SERVER Select Database(s) Select "Network Path", "FTP connection" Enter Email Address, so software will notify the user when backup is done We need to install this software on the server and we can take SQL SERVER Database backup on Network and FTP together. First please install this software from SQL SERVER Backup And FTP and Let me explain it how it works: Connect SQL SERVER: First connect to SQL SERVER Database, for which we need to take backup. We need to Enter SQL SERVER Name and credentials to connect to SQL SERVER Select Database: Once SQL SERVER is connected, all databases of that SQL server will be listed. We need to select all the databases for which we need to take backup as follows: Here I have done following: Checked two databases, pubs and SQLYoga. Checked "Network Folder" and select Network path Checked "FTP SERVER" and specify FTP Server, to put backup on FTP Server Checked "Email Notification", so I will be notified about success/failure of moving database backup. Unchecked "Schedule this job", as I just need to run it once. If we need to run it on daily basis, we can specify schedule to run it on that time daily. That's it and now we are out of stress to move database backup to another server or on FTP Let's click on "Run now" to check it out. After process is completed, Two zip files, pubs and SQLYoga is on Network path as well as on FTP. Another cool feature is that, we can schedule Full Backup, Differential Backup, and Transaction Backup, so we can have very minimal amount of data loss. Here, I have configured this software as: Take full backup of database at every 24 hours Take differential backup at every 1 hour Take Transaction backup at every 15 minutes Here, we assume that it is ok that we have 15 minutes data loss. It is depends on your application to decide this time window. Let's take one case study to get more understanding about to restore these backups: e.g. Database server is crashed at 8:20 AM, How can I restore my database from the backups? Restore full backup database which was taken at mid night 3 AM. As we have configured to have differential back at every hour, we also have Differential backup of 8:00 AM, so restore differential database backup. We have also configured transaction backup for every 15 minutes, so we have one transaction log backup which is taken on 8:15 AM, so let's restore it. So, now we have data up to 8:15 AM As we have assume that Organization is good if we loss 15 minutes of data. Here in this case, as we have Transaction backup of 8:15 AM,  we are losing transactions which are made between 8:15 AM - 08:20 AM. So, we should proceed with 08:15 AM transaction backup and make database available Another, option is also available, to remove old Differential backup and Transaction backup. Once new differential backup is generated, there is no need to keep old differential backup. We only need to restore recent differential backup, so we can remove old differential backups. We can also remove all transaction log backup which are generated before Differential Log. SO to remove old backups, this software is also providing an option for the same, we just need to checked this option and that's it. This software is also allowed following settings (advanced): You can setup custom "temp" folder, by default its using "C:\Windows\Temp" Compression Settings (Zip) Encryption I like this tool, I recommend this tool to take back up and move it on FTP. Try to use it and let me know your comments

Read More

SQL SERVER: Cross tab Query with Pivot (Dynamic Pivot)

As I mentioned earlier, PIVOT, in my earlier post, where we have reviewed the PIVOT with static columns. In practical world, we may need to have dynamic columns that needs to be displayed as below:

 

As Mentioned in Image, Here, dates are dynamic, based on Order Date, 10/25,10/26,10/27,10/28 etc..To achieve the same with Dynamic columns please find query for the same:

 

CREATE TABLE #TestTable(ctrl_no INT, id int, Amount NUMERIC(18,2), OrderDate DATE)

INSERT INTO #TestTable(ctrl_no , id , Amount , OrderDate )
SELECT 1000029,100016,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100018,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100016,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,100018,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/27/2012'

DECLARE @MinDate DATE
	,@MaxDate DATE
DECLARE @Dates VARCHAR(MAX)

SELECT @Dates = ''

SELECT @MinDate = MIN(OrderDate)
	,@MaxDate = MAX(OrderDate)
FROM #TestTable

WHILE @MinDate <= @MaxDate
BEGIN
	SELECT @Dates = @Dates + '[' + CONVERT(VARCHAR(10), @MinDate, 101) + '],'

	SELECT @MinDate = DATEADD(d, 1, @MinDate)
END

SELECT @Dates = LEFT(@Dates, LEN(@Dates) - 1)

DECLARE @qry NVARCHAR(MAX)

SELECT @qry = N'

SELECT    id,
        ' + @Dates + '
FROM (
    SELECT    id,
            ISNULL(Amount,0) AS Amount,
            OrderDate
    FROM #TestTable t
) x
PIVOT(
    SUM(Amount) FOR OrderDate IN(' + @Dates + ')
) p '

EXEC (@qry)

DROP TABLE #TestTable

Let me know if you have any question/comment.

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

Read More

Configure SQL Reporting Service, to Access Reports using External IP With IIS 7

Hi All, Recently I come to situation where I need to Configure Reporting services on server having configuration: Window server 2008 and IIS 7. I need to configure reports in manner, so User can access this reports thru External IP Address. I found configuration for IIS 7.0, so user can access reports externally. To able to access the reports externally, do the steps as mentioned below: 1. Create Virtual Directory in IIS 7.0 : 2. Go to Handler Mappings : 3. Double Click on "Handler Mapping". You will get screen like the given below: 4. Now see Top Right Corner: Click On "Add Managed Handler". Here you need to fill up the info like: Fill this info as: Request path: Reserved.ReportViewerWebControl.axd Type: Microsoft.Reporting.WebForms.HttpHandler Name: Reserved-ReportViewerWebControl-axd Thats it. Now try with External IP. You can access the reports with External IP too.

Read More

SQL SERVER: Cross tab query with PIVOT

We had a User group meeting for "Cross Tab Queries", where I have explained how to write PIVOT queries. Many times developer has an issue while writing query with PIVOT. Let me have a sample code for query with PIVOT keyword.

 

PIVOT is used to transform rows to column, which gives result in such a way which can be send to the user directly. Let me take an example. We have an order table where we have each order stored in a row in a table as displayed below:

 

ctrl_no	id	Amount	OrderDate
1000029	100016	990	10/25/2012
1000029	100018	990	10/25/2012
1000029	100016	660	10/26/2012
1000029	100018	660	10/26/2012
1000029	206007	660	10/26/2012
1000029	206007	660	10/27/2012

This information has to be saved in a row in relational database. When this information needs to be display it on the application, it needs to be displayed as below, so user can use this info for decision making:

id	10/25/2012	10/26/2012	10/27/2012
100016	990.00	660.00	NULL
100018	990.00	660.00	NULL
206007	NULL	660.00	660.00

Did you see the difference? how easy user can review the information. Lets see an query how to achieve the expected result with PIVOT keyword.

CREATE TABLE #TestTable (
	ctrl_no INT
	,id INT
	,Amount NUMERIC(18, 2)
	,OrderDate DATE
	)

INSERT INTO #TestTable(ctrl_no , id , Amount , OrderDate )
SELECT 1000029,100016,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100018,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100016,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,100018,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/27/2012'

SELECT id
	,[10/25/2012]
	,[10/26/2012]
	,[10/27/2012]
FROM (
	SELECT id
		,Amount
		,OrderDate
	FROM #TestTable t
	) x
PIVOT(SUM(Amount) FOR OrderDate IN (
			[10/25/2012]
			,[10/26/2012]
			,[10/27/2012]
			)) p

DROP TABLE #TestTable

 

Let me know if you have any question/comment.

Reference: Tejas Shah (www.SQLYoga.com)

Read More

SQL SERVER: Limit memory usage of SQL SERVER

Recently, we have found that SQL SERVER is using most of the memory on the server, due to heavy queries are running on my different teams and it freezes the whole server. Due to time constraint team doesn’t have time to optimize those queries, so we have found a way to configure SQL SERVER to not to use all available memory. We have an option to configure how much memory can be used by SQL SERVER as below: 1. Connect to the SQL SERVER instance for which we need to limit memory usage and click on properties: 2. Go to “Memory”, where you can see the memory options that can be used by SQL SERVER as below: By default, SQL SERVER is using maximum server memory, so to resolve the freezing issue, we need to change it as per requirement. So SQL SERVER will use only that much memory and other processes can run in the server. Let me know your comments. Reference: Tejas Shah (http://www.SQLYoga.com)

Read More

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 Reference: Tejas Shah (http://www.SQLYoga.com)

Read More

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)

Read More

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: 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: Now, we have created Operator, so, lets set it up for the job as below: 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)

Read More

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: 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)

Read More

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 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

Read More

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: In Solution Explorer, add a new folder and name it as "Reports". Right click on "Reports", select "Add" and click on" New Items". Give preferable name to report with extension RDLC and click Add. 2. Create Dataset from Report Data Source: 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". Click on "New" and select "Dataset" as displayed in following screenshot:     3. If your "Data Source" is listed in options, select it from there, else click on "New".      4. If your "Connection" is listed in options, select it from there, else click on "New Connection", to make your connection.     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:     6. Once you have selected DB Object in Dataset, you can find out all the columns that are available in selected dataset as follows:      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 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. RDLC, allows you to design your report with schema that you have selected as DataSet 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: Add an "aspx" page in the solution. We have to use "Report Viewer" control on that page, in which our Report will be displayed 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. 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)

Read More

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

Read More

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 Vaidyanathan [twitter] Topic: HTML 5 Download Presentation and Demo Files
	Jacob Sebastian [twitter | blog] Topic: SQL SERVER Worst practices Download Presentation and Demo Files
	Pinalkumar Dave [twitter | blog] Topic: SQL SERVER Performance troubleshooting using Waits and Queues
	Tejas Shah [twitter | blog] Topic: Asp.Net Tips and Tricks Download Presentation and Demo Files
We will upload this session photos soon

Read More

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: Report Server URL: http://<URL>/ReportServer UserName: xxxx Password: yyyy 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 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: 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.

Read More

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" Check Solution Explorer (Right Side) and click on "Add New Report" Select Data Source. Enter information to connect with database and click on "Next" Build query which returns data to be displayed on the report. Select Report Format. For simple report we select "Tabular" format 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 For Sample report, I have selected only some of columns that should be displayed on Report. Select Table style Enter Report name, "Sample Report" It displays summary info we have selected so far. And that's it. We are ready with the report now. Preview Tab, we can preview report 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: 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" 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: 4. Click on "My Reports" 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. Now, in the application, we need to create one ASPX page where we need to use ReportViewer control as follows: 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.   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.

Read More