May 3, 2013

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

SQLYoga Resultset of XML RAW

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

SQLYoga Resultset of XML PATH

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)

April 30, 2013

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:

  1. Connect SQL SERVER
  2. Select Database(s)
  3. Select "Network Path", "FTP connection"
  4. 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

SQLYoga SQL Server Backup

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

SQLYoga SQL Server Backup

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.

SQLYoga SQL Server Backup

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?

  1. Restore full backup database which was taken at mid night 3 AM.
  2. 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.
  3. 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
  4. 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):

SQLYoga SQL Server Backup

  • 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

December 24, 2012

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:
 
SQLYoga Dynamic PIVOT with SQL SERVER
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

November 20, 2012

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 :

9

2. Go to Handler Mappings :

10

3. Double Click on "Handler Mapping". You will get screen like the given below:

11

4. Now see Top Right Corner: Click On "Add Managed Handler". Here you need to fill up the info like:

12

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.

November 5, 2012

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)

November 2, 2012

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:

SQL SERVER Limit SQL SERVER Memory usage2. Go to “Memory”, where you can see the memory options that can be used by SQL SERVER as below:

SQL SERVER Limit SQL SERVER Memory usage SQL SERVER Properties
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)

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)