TCP Port of SQL Server

By default the default TCP Port for SQL Server is 1433.  It can be changed for security purpose to minimize the potential threat of a hacker trying to access through the default port number.  But that is whole together a different story.

Every now and then somebody posts this question on forums- how can I tell what port my SQL Server instance is running on?  As sometimes while accessing SQL Server remotely you may need to add the TCP Port in the connection string.

There are more than 1 way to find out the answer.  From SQL Server Error Logs, Configuration Manager, Command Prompt, Registry Editor.   But today I will share a simple T-SQL script with you, from which you can easily find out the TCP port SQL Server instance is running on.

First lets see in regedit where to look for the Port number. 
The location of port number for SQL Server 2005 -

For default instance : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

For Named Instance :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ [Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp

If you don’t know how to open the regedit :
1. Click on Start and then on Run.
2. In run type regedit and click on Ok.

After checking out the port number manually now lets see the query I created.  It uses a non-documented extended stored procedure xp_regread.  Basically it is helpful in reading from the registry via Transact-SQL.  Since it is a non-documented extended stored procedure you won’t find any reference about xp_regread in Books Online. If you want to know more about it, best thing you can do is Google for xp_regread.  So here is the script which will give you the TCP port SQL Server is running on :

DECLARE @TcpPort VARCHAR(5)         ,@RegKey VARCHAR(100) IF @@SERVICENAME !='MSSQLSERVER'     BEGIN         SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp'     END     ELSE     BEGIN         SET @RegKey = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'     END EXEC master..xp_regread     @rootkey = 'HKEY_LOCAL_MACHINE'     ,@key = @RegKey     ,@value_name = 'TcpPort'     ,@value = @TcpPort OUTPUT SELECT @TcpPort AS PortNumber         ,@@SERVERNAME AS ServerName         ,@@SERVICENAME AS ServiceName

You will get the output something like this :

Other simple way of finding out the TCP port is from the Configuration Manager.
1. Start >> All Program >> Microsoft SQL Server 2005 >> Configuration Tools >> SQL Server Configuration Manager .
2. In the configuration manager click on SQL Server 2005 Network Configuration.
3. Then click on the Instance name for which you want to find out the Port Number.
4. Double click on TCP/IP.
5. Finally on IP Addresses, and you will see something like :

Alternating Row Background Color in Reports

Now lets see how to get alternating row background colors in Reports. This is one of the 1^st typical report enhancement you might be doing once you started working on Reporting Services.  See the following report : 

This can be achieved very easily in SSRS with the help of IIF and RowNumber function for a simple report with no groupings.   You can use a background color expression as:

= IIF(RowNumber(Nothing) Mod 2, "White","Gainsboro")

It becomes little complicated in cases of Matrix and when some groupings are involved.  In that case use following expression for the details row within a group:

= IIF(RunningValue(Fields!Some_Field.Value,Count,"Group_Name") Mod 2, "White","Gainsboro")

Here instead of RowNumber function I have used he RunningValue function.  Just remember to replace Some_Field with actual column name and Group_Name with actual Group Name in your report.

Multiplying Column Values

Well we all know how to add the values in a column using SUM and GROUP BY.  But what about multiplying the column values?  And also how to calculate running multiplication on a column similar to Running Totals?

Sometime back I came across this interesting question on MSDN SQL Server forums - How to multiply all values in a table column?

I don't know the reason of such requirement, but very interesting problem though.  Of course there is no such built-in function in SQL Server to do it for you.  But after thinking on it for few minutes, I realize that if you still remember the basics of Mathematics, it is not that difficult to do in T-SQL.

If you remember

A * B = ANTILOG ( LOG (A) + LOG(B) )

So looking at it again, it is very straight forward using LOG, ANTILOG and SUM in SQL Server. (For doing ANTILOG you have EXP function in SQL Server.) Let me show you how :

Lets create some sample data first.

-- Create sample table CREATE TABLE Scores ( ID INT IDENTITY, Category VARCHAR(1), ) GO -- Load sample data into table INSERT INTO Scores(Category, Score) SELECT 'A', 4 UNION ALL SELECT 'A', 5 UNION ALL SELECT 'A', 2 UNION ALL SELECT 'B', 5 UNION ALL SELECT 'B', 5 UNION ALL SELECT 'B', 2 UNION ALL SELECT 'B', 3 GO -- test the sample data SELECT Id, Category, Score FROM Scores GO

The sample data :

ID	CATEGORY	SCORE
1	A	4
2	A	5
3	A	2
4	B	5
5	B	5
6	B	2
7	B	3

Multiplying Column Values:  And here is the query to calculate the Total Product of Score Column for each Category.

SELECT Category, SUM(Score) as TotalSum,EXP(SUM(LOG(Score))) as TotalProduct FROM Scores GROUP BY Category

Result :

Category	TotalSum	TotalProduct
A	11	40
B	15	150

Second column you have normal total SUM for the Score column and in 3^rd column you have the total product of the Score column for each Category.

Calculating The Running Multiplication:  After multiplying the column values, next question is how to calculate the Running Product similar to Running totals? See the following table -

ID	Category	Score	RunningProduct
1	A	4	4
2	A	5	20
3	A	2	40
4	B	5	5
5	B	5	25
6	B	2	50
7	B	3	150

The logic is similar to what I just mentioned for Multiplying Rows and adding the Running Total logic to query.

SELECT O.Id, O.Category, O.Score         ,(  SELECT EXP ( SUM ( LOG (I.Score) ) )              FROM Scores I              WHERE I.Category = O.Category              AND I.Id <= O.Id          ) as RunningProduct FROM Scores O

But remember this solution only works for positive numbers, if column contains negative values then you will need to modify the query using ABS function and taking into account number of negative values.  Well but at least you will get a start from this post.

Expiration Date of SQL Server Evaluation Edition

If you have installed the Evaluation / Trial Version of SQL Server, you may like to find out what is the expiry date of the Trial Version.  The Trial Edition or Evaluation Edition of SQL Server will expire exactly 180 days after the install date.  Using it after the 180 days period is violation of MICROSOFT evaluation license terms.  Anyways after 180 days when an install of Evaluation Edition Expires, the SQL Server services stop functioning.  So you must purchase the license for SQL Server and upgrade the Evaluation copy before the expiry date of the Evaluation.

But then how to find out the exact expiry date of Trial Version?  There are 2 ways to find out

• From installation logs
• From Management Studio

From installation logs
A summary.txt file is get created during the installation of SQL Server.  So this summary.txt can give you the exact date and time of the SQL Server installation.  And the trial software will automatically expire after six months.  You can find summary.txt file under

<Drive>\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt

At the end of the file you can see something like

Setup succeeded with the installation, inspect the log file completely for status on all the components. Time : Tue Dec 30 15:59:55 2008

So after 180 days counting from 30 Dec 2008 i.e. 28 Jun 2009 the SQL Server Evaluation Edition will be expired.

From Management Studio
Now this is very simple. Open the Management Studio, click on the “Help” of Menu Bar and then on “About”.  A new window will appear, where you will see some thing like:

Microsoft SQL Server Management Studio (expires in 102 days)

See the Image :

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