Recover unsaved SQL query scripts

Imagine a situation when you are working on a query for couple of hours and suddenly your management studio is crashed or your PC is restarted and you forgot to hit the “CTRL + S” in between.  Worst nightmare of any developer.   It is always extrmely difficult to re-create the same query again.

In the past I used to go to this folder to find my unsaved sql scripts : C:\Users\YourUsername\AppData\Local\Temp

But somehow this was never realiable.  There were few occassions when I didn’t find the query I was looking for.   Few days back I came across this table valued system fuction sys.dm_exec_sql_text which can save our day from the nightmare of not saving the scripts.  More information about this function can be found on the Books Online here: sys.dm_exec_sql_text

Now striaght away jump to the query which can help us to recover the unsaved SQL query scripts from the management studio.  

--------------------------------------------------------------------------------------------------------
USE [DATABASE_NAME]
SELECT S.LAST_EXECUTION_TIME
,T.TEXT AS [SCRIPT]
FROM SYS.DM_EXEC_QUERY_STATS AS S
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(S.SQL_HANDLE) AS T
--WHERE T.TEXT LIKE '%SOMETHING%'
ORDER BY S.LAST_EXECUTION_TIME DESC
--------------------------------------------------------------------------------------------------------

Few notes:
1. You will need VIEW SERVER STATE permissions to execute this query.
2. Since there could be many queries running on the server you would like to use WHERE condition TEXT LIKE '%SOMETHING%'.  
3. You can recover the queries only till SQL Server keeps them in the memory.
4. DM_EXEC_QUERY_STATS has few more useful columns that can help you with some extra information related to your query. You may like to add them in this query.

Thanks
Mangal

Calculating Running Totals

Edit: If you are using SQL Server 2012 or 2014 you may like to see my latest post on the topic How to Calculate Running Totals in SQL Server

In one of my previous posts I wrote about Calculating Running Totals using a SELF JOIN.  But after realizing that, SELF JOIN is not efficient way of calculating Running Totals I thought of writing this post, with another technique without using Cursor.

In this post I'll show you how to Calculate Running Totals without using a cursor.  On the same line with my previous post, the different scenarios I’ll be covering in this post are -

• Running Total for all records.
• Running Total on each Group of Records.
• Running Total on each Date.

Here is our sample table :

ShipId	OrderId	ShipDate	Quantity
1	1	2009-02-01 12:06:16.820	10
2	1	2009-02-02 16:16:16.820	15
3	2	2009-02-01 13:26:16.820	20
4	2	2009-02-03 17:40:16.820	12
5	2	2009-02-04 13:05:16.820	15
6	3	2009-02-03 14:25:16.820	25
7	3	2009-02-04 15:50:16.820	50
8	3	2009-02-05 19:40:16.820	30
9	4	2009-02-06 15:30:16.820	20
10	4	2009-02-07 16:20:16.820	10
11	5	2009-02-05 17:05:16.820	35

Create Sample Data:

-- Create Table CREATE TABLE Sales ( ShipId INT, OrderId INT, ShipDate DateTime, Quantity INT ) GO -- Load Sample Data INSERT INTO Sales SELECT 1,1, 'Feb 01 2009 12:06:16:820PM',10 UNION ALL SELECT 2,1, 'Feb 02 2009 04:16:16:820PM',15 UNION ALL SELECT 3,2, 'Feb 01 2009 01:26:16:820PM',20 UNION ALL SELECT 4,2, 'Feb 03 2009 02:40:16:820PM',12 UNION ALL SELECT 5,2, 'Feb 04 2009 01:05:16:820PM',15 UNION ALL SELECT 6,3, 'Feb 03 2009 05:25:16:820PM',25 UNION ALL SELECT 7,3, 'Feb 04 2009 03:50:16:820PM',50 UNION ALL SELECT 8,3, 'Feb 05 2009 07:40:16:820PM',30 UNION ALL SELECT 9,4, 'Feb 06 2009 03:30:16:820PM',20 UNION ALL SELECT 10,4, 'Feb 07 2009 04:20:16:820PM',10 UNION ALL SELECT 11,5, 'Feb 05 2009 05:05:16:820PM',35

Case I : Running Total For all records.
Here I will calculate the Running Totals for each records. For that I'll be using a correlated sub query to calculate the Running Totals for the all the records. So the query will be :

SELECT O.ShipId, O.OrderId, O.ShipDate, O.Quantity         ,(SELECT SUM(I.Quantity)             FROM Sales I             WHERE I.ShipId <= O.ShipId          ) as RunningTotal FROM Sales O ORDER BY O.ShipId

Here is the result returned by above query :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	45
4	2	2009-02-03 17:40:16.820	12	57
5	2	2009-02-04 13:05:16.820	15	72
6	3	2009-02-03 14:25:16.820	25	97
7	3	2009-02-04 15:50:16.820	50	147
8	3	2009-02-05 19:40:16.820	30	177
9	4	2009-02-06 15:30:16.820	20	197
10	4	2009-02-07 16:20:16.820	10	207
11	5	2009-02-05 17:05:16.820	35	242

Case II: Running Total on each Group of Records.
Now instead of calculating the running total for each record we will calculate the running total for each OrderId. For that small change in our above query will be addition of one condition in WHERE clause of , that will be - I.OrderId = O.OrderId. So the final query will be:

SELECT O.ShipId, O.OrderId, O.ShipDate, O.Quantity         ,(SELECT SUM(I.Quantity)             FROM Sales I             WHERE I.OrderId = O.OrderId             AND I.ShipId <= O.ShipId          ) as RunningTotal FROM Sales O ORDER BY OrderId

And the Output :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	20
4	2	2009-02-03 17:40:16.820	12	32
5	2	2009-02-04 13:05:16.820	15	47
6	3	2009-02-03 14:25:16.820	25	25
7	3	2009-02-04 15:50:16.820	50	75
8	3	2009-02-05 19:40:16.820	30	105
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	30
11	5	2009-02-05 17:05:16.820	35	35

Case III: Running Total on each Date.
Now we will see how to calculate the Running Total for each day. For that some small modifications will required in our WHERE clause, observe them carefully and try to understand what I’m trying to do :

SELECT O.ShipId, O.OrderId, O.ShipDate, O.Quantity         ,(SELECT SUM(I.Quantity)             FROM Sales I             WHERE I.shipDate <= O.Shipdate             AND DateAdd(dd, DateDiff (dd, 0, I.Shipdate), 0)             = DateAdd(dd, DateDiff (dd, 0, O.Shipdate) ,0)           ) as RunningTotal FROM Sales O ORDER BY O.ShipDate

And the output :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
3	2	2009-02-01 13:26:16.820	20	30
2	1	2009-02-02 16:16:16.820	15	15
6	3	2009-02-03 14:25:16.820	25	25
4	2	2009-02-03 17:40:16.820	12	37
5	2	2009-02-04 13:05:16.820	15	15
7	3	2009-02-04 15:50:16.820	50	65
11	5	2009-02-05 17:05:16.820	35	35
8	3	2009-02-05 19:40:16.820	30	65
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	10

Mangal Pardeshi

Excel 2007 Linked Server

Lets see How to create a linked server with Excel 2007. Well most of the stuff you'll find on the net on how to create a linked Server is using Microsoft Jet 4.0 OLE DB Provider which is no longer able to connect to Excel 2007.

So here you go...
We will use "Microsoft.ACE.OLEDB.12.0" which come with Office 2007 pack. If you don't have it on Provider's list you can download it from 2007 Office System Driver: Data Connectivity Components

After downloading and Installing the ACE driver you can create the Linked server as

exec sp_addLinkedServer
@server= 'XLFile',
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'E:\Mangal.xls',
@provstr = 'Excel 12.0; HDR=No'

If you have the Column names in Excel do HDR=yes.

Now you can query your Excel just like any table - SELECT * FROM XLFile...[sheet1$]

Or if you don't want to create a Linked Server, and just want to execute the Excel then do -
SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=E:\Mangal.xls;HDR=No', 'Select * from [Sheet1$]' )
And if you execute the above query and you get the error
Msg 15281, Level 16, State 1, Line 1 SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off. That means first you need to enable the 'Ad Hoc Distributed Queries'.

For that first execute the following scripts. 
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
For Creating Linked Server from Management Studio:
Open the Management studio, connect to your server. And follow the steps
1. Go to "Server Objects" then Right CLick on "Linked Servers" and write "New Linked Server".
2. Give any name to your linked server. In our example I've given "XL".
3. Now select Server Type as "Other Data Source". Now select the provider"Microsoft Office 12.0 Access Database Engine OLE DB Provider."
4. Product Name : ACE 12.0
5. Date Source : E:\Mangal.xls --- Here you need to put the path of your Excel Sheet.
e.g. C:\Folder\New.xls Provider String : Excel 12.0; HDR=No 
Again remember if you have column names in Excel then HDR=Yes. 

- Mangal Pardeshi.

Calculating Running Totals in SQL

 

Edit: If you are using SQL Server 2012 or 2014 you may like to see my latest post on the topic How to Calculate Running Totals in SQL Server

NOTE: After realizing that, SELF JOIN is not efficient way of calculating Running Totals so I added another post on how to Calculate Running Totals using a co-related sub-query.  So instead of using the technique I used in this post you should use technique mentioned in my new post -  How to Calculating Running Totals

In this article I will show you a few different techniques for calculating Running Total on multiple rows. The different scenarios I’ll be covering in this post are -

• Running Total for all records.
• Running Total on each Group of Records.
• Running Total on each Date.

Here is our sample table :

ShipId	OrderId	ShipDate	Quantity
1	1	2009-02-01 12:06:16.820	10
2	1	2009-02-02 16:16:16.820	15
3	2	2009-02-01 13:26:16.820	20
4	2	2009-02-03 17:40:16.820	12
5	2	2009-02-04 13:05:16.820	15
6	3	2009-02-03 14:25:16.820	25
7	3	2009-02-04 15:50:16.820	50
8	3	2009-02-05 19:40:16.820	30
9	4	2009-02-06 15:30:16.820	20
10	4	2009-02-07 16:20:16.820	10
11	5	2009-02-05 17:05:16.820	35

Create Sample Data:

-- Create Table CREATE TABLE Sales ( ShipId INT, OrderId INT, ShipDate DateTime, Quantity INT ) GO -- Load Sample Data INSERT INTO Sales SELECT 1,1, 'Feb 01 2009 12:06:16:820PM',10 UNION ALL SELECT 2,1, 'Feb 02 2009 04:16:16:820PM',15 UNION ALL SELECT 3,2, 'Feb 01 2009 01:26:16:820PM',20 UNION ALL SELECT 4,2, 'Feb 03 2009 02:40:16:820PM',12 UNION ALL SELECT 5,2, 'Feb 04 2009 01:05:16:820PM',15 UNION ALL SELECT 6,3, 'Feb 03 2009 05:25:16:820PM',25 UNION ALL SELECT 7,3, 'Feb 04 2009 03:50:16:820PM',50 UNION ALL SELECT 8,3, 'Feb 05 2009 07:40:16:820PM',30 UNION ALL SELECT 9,4, 'Feb 06 2009 03:30:16:820PM',20 UNION ALL SELECT 10,4, 'Feb 07 2009 04:20:16:820PM',10 UNION ALL SELECT 11,5, 'Feb 05 2009 05:05:16:820PM',35

Case I : Running Total For all records.
Here I will calculate the Running Totals for each records. For that I will JOIN the Sales table with itself. So the query will be :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal FROM Sales A INNER JOIN Sales B ON A.ShipId >= B.ShipId GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity

Here is the result returned by above query :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	45
4	2	2009-02-03 17:40:16.820	12	57
5	2	2009-02-04 13:05:16.820	15	72
6	3	2009-02-03 14:25:16.820	25	97
7	3	2009-02-04 15:50:16.820	50	147
8	3	2009-02-05 19:40:16.820	30	177
9	4	2009-02-06 15:30:16.820	20	197
10	4	2009-02-07 16:20:16.820	10	207
11	5	2009-02-05 17:05:16.820	35	242

Case II: Running Total on each Group of Records.

Now instead of calculating the running total for each record we will calculate the running total for each OrderId. For that small change in our above query will be addition of one AND condition in ON clause, that will be - A.OrderId = B.OrderId. So the final query will be :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal FROM Sales A INNER JOIN Sales B ON A.ShipId >= B.ShipId AND A.OrderId = B.OrderId GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity ORDER BY OrderId

And the Output :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
2	1	2009-02-02 16:16:16.820	15	25
3	2	2009-02-01 13:26:16.820	20	20
4	2	2009-02-03 17:40:16.820	12	32
5	2	2009-02-04 13:05:16.820	15	47
6	3	2009-02-03 14:25:16.820	25	25
7	3	2009-02-04 15:50:16.820	50	75
8	3	2009-02-05 19:40:16.820	30	105
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	30
11	5	2009-02-05 17:05:16.820	35	35

Case III: Running Total on each Date.

Now we will see how to calculate the Running Total for each day. For that some small modifications will required in our ON clause, observe them carefully and try to understand what I’m trying to do :

SELECT A.Shipid, A.Orderid, A.Shipdate, A.Quantity, SUM(B.Quantity) AS RunningTotal FROM Sales A INNER JOIN Sales B ON A.shipDate >= B.Shipdate AND DateAdd(dd, DateDiff (dd, 0, A.Shipdate), 0) = DateAdd(dd, DateDiff (dd, 0, B.Shipdate) ,0) GROUP BY A.Shipid, A.Orderid, A.Shipdate, A.Quantity ORDER BY A.ShipDate

And the output :

ShipId	OrderId	ShipDate	Quantity	RunningTotal
1	1	2009-02-01 12:06:16.820	10	10
3	2	2009-02-01 13:26:16.820	20	30
2	1	2009-02-02 16:16:16.820	15	15
6	3	2009-02-03 14:25:16.820	25	25
4	2	2009-02-03 17:40:16.820	12	37
5	2	2009-02-04 13:05:16.820	15	15
7	3	2009-02-04 15:50:16.820	50	65
11	5	2009-02-05 17:05:16.820	35	35
8	3	2009-02-05 19:40:16.820	30	65
9	4	2009-02-06 15:30:16.820	20	20
10	4	2009-02-07 16:20:16.820	10	10

Hopefully these examples will help you understand different methods that can be used to calculate running totals.

- Mangal Pardeshi