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

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.

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)

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)

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)

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)

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)

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)

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