SQL Server – Finding TCP Port Number SQL Instance is Listening on

August 13, 2014

http://sqlandme.com/2013/05/01/sql-server-finding-tcp-port-number-sql-instance-is-listening-on/

By default SQL Server listens on TCP port number 1433, and for named instances TCP port is dynamically configured. There are several options available to get the listening port for SQL Server Instance.

Here are a few methods which we can use to get this information.

Method 1: SQL Server Configuration Manager

Method 2: Windows Event Viewer

Method 3: SQL Server Error Logs

Method 4: sys.dm_exec_connections DMV

Method 5: Reading registry using xp_instance_regread

Let’s see how you can use each of these methods in detail:

Method 1: SQL Server Configuration Manager:

Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager

Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for

Step 3. Right Click on TCP/IP and select Properties

image

Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAll group.

image

If SQL Server if configured to run on a static port it will be available in TCP Port textbox, and if it is configured on dynamic port then current port will be available in TCP Dynamic Ports textbox. Here my instance is listening on port number 61499.

Method 2: Windows Event Viewer:

When SQL Server is started it logs an event message as ‘Server is listening on [ ‘any’ ‘ in windows event logs. Here will be actual port number on which SQL Server is listening.

To view this using Event Viewer:

Step 1. Click Start > Administrative Tools > Event Viewer.

Note: If Administrative Tools are not available on Start menu, go to Start > Control Panel > System and Maintenance > Administrative Tools > View event logs

Step 2. Navigate to Event Viewer > Windows Logs > Application

Step 3. Since huge amount of event are logged, you need to use filtering to locate the required logs. Right click on Application and select Filter Current Log…

image

Step 4. You can filter the events by Event ID and Event source. The event we are interested in has Event ID of 26022, and it’s source is SQL Server Instance. You need to filter by both Event ID and SQL Server Instance if you have multiple instances installed, for a single instance you can filter by Event ID only. Click on OK to apply the filter.

image

Step 5. Once the filter is applied, Locate message ‘Server is listening on [ ‘any’ …’. As we can see from below screenshot that SQL Server Instance is running on TCP Port 61499.

image

Method 3: SQL Server Error Logs:

When SQL Server is started it also logs an message to SQL Server Error Logs. You can search for port number in SQL Server Error Logs by opening SQL Server Error Log in notepad or via T-SQL using extended stored procedure xp_ReadErrorLog as below:

EXEC xp_ReadErrorLog 0, 1, N’Server is listening on’, N’any’, NULL, NULL, ‘DESC’
GO

Result Set:

LogDate ProcessInfo Text
2013-03-21 13:34:40.610 spid18s Server is listening on [ 'any' 61499].
2013-03-21 13:34:40.610 spid18s Server is listening on [ 'any' 61499].

(2 row(s) affected)

As we can see from the output that SQL Server Instance is listening on 61499.

Note: This method does not work if SQL Server Error Logs have been cycled. See sp_Cycle_ErrorLog for more information.

Method 4: sys.dm_exec_connections DMV:

DMVs return server state that can be used to monitor SQL Server Instance. We can use sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:

SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
GO

Result Set:

local_tcp_port
61499

(1 row(s) affected)

As we can see from the output… same as above Smile

Method 5: Reading registry using xp_instance_regread:

Port number can also be retrieved from Windows Registry database.

We can use extended stored procedure xp_instance_regread to get port number information using below T-SQL code:

DECLARE @portNumber NVARCHAR(10)

EXEC xp_instance_regread
@rootkey = ‘HKEY_LOCAL_MACHINE’,
@key =
‘Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll’,
@value_name = ‘TcpDynamicPorts’,
@value = @portNumber OUTPUT

SELECT [Port Number] = @portNumber
GO

Result Set:

Port Number
61499

(1 row(s) affected)

As we can see … same as above Smile Smile

Note: The above code will only work if SQL Server is configured to use dynamic port number. If SQL Server is configured on a static port, we need to use @value_name = ‘TcpPort’ as opposed to @value_name = ‘TcpDynamicPorts’.


Asp.net – try catch finally notes

March 27, 2014

The code inside a finally block will get executed regardless of whether or not there is an exception.

http://www.codeproject.com/Articles/154121/Using-Try-Catch-Finally

Exception handling in C#. In the below code

Try
{

//Throwing Error
}
Catch
{
//Getting Caught
}
Finally
{
int x =3;
}

int x = 5 ;

In Try after throwing an Error which is caught in Catch ,After that Finall Block Executes with Assignment of x=3. and after that next line x=5 is also executed.Program not getting terminated..What might be the reason ?


SqlServer notes : Inbuilt reports provided by Sqlserver itself

March 13, 2014

In Object Explorer

 

right click on server name and select Reports->Standard Reports->

Performance -Top Queries by Average CPU Time


SQL SERVER – Comma Separated Values (CSV) from Table Column

January 22, 2014

Is there a way to convert a single column record set such as

1
2
3

into ‘1,2,3’ string in SQL Server?

1st way :

 

CREATE FUNCTION [dbo].[fn_MyFunction]
(
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

    DECLARE @str NVARCHAR(MAX)

    DECLARE @Delimiter CHAR(2) 
    SET @Delimiter = ', '

    SELECT @str = COALESCE(@str + @Delimiter,'') + AColumn
    FROM dbo.myTable

    RETURN RTRIM(LTRIM(@str))

END

2nd Way :

select stuff( (select ',' + YourColumn
               from YourTable
               for xml path('')), 1, 1, '')
3rd Way :

DECLARE @Out VARCHAR(2048)
Select @Out = COALESCE(@Out + ', ', '') + [YourColumn] from YourTable

print @Out

4th way Best one :

SELECT SUBSTRING(
(SELECT ',' + s.Name
FROM HumanResources.Shift s
ORDER BY s.Name
FOR XML PATH('')),2,200000) AS CSV

 


MS word tips

January 16, 2014

To change uppercase lowercase

 

Shift + F3


Asp.net file upload control error

January 4, 2014

HTTP Error 404.13 – Not Found : The request filtering module is configured to deny a request that exceeds the request content length – See more at: http://www.shubho.net/2011/01/http-error-40413-not-found-request.html#sthash.dKksj9Q8.dpuf

http://www.shubho.net/2011/01/http-error-40413-not-found-request.htmlA

 

 

<requestLimits maxAllowedContentLength=”2048000000″></requestLimits>

 

http://www.shubho.net/2011/01/http-error-40413-not-found-request.html

 

I was developing an application that allows user to upload files to the server using the <asp:FileUpload/> control. In order to make sure that users can upload large files, I configured the web.config as follows to allow larger files to be uploaded (The default setting is 4 MB):
1
<httpRuntime maxRequestLength="512000"/><!--To allow up to 500MB-->
While testing the file upload functionality from within the visual studio development server (Right clicking on the aspx file and selecting browse), I found it working quite fine. But surprisingly, after hosting the Asp.net web site onto IIS (IIS 7), I found the file uploading functionality was no longer working, and, it was broken while trying to upload large files (I was trying with a file over 40MB in size). Following is the screen shot of the error message that I got:

Figure : The error message from IIS while trying to upload a large file
The error page also suggested me to do the followings:
“Verify the configuration/system.webServer/security/requestFiltering/requestLimits@maxAllowedContentLength setting in the applicationhost.config or web.config file.”
So, as suggested, I did the following configuration in the web.config of my Asp.net web site(By setting maxAllowedContentLength value in Bytes):
1
2
3
4
5
6
7
8
<system.webServer>
  <modules runAllManagedModulesForAllRequests="true"/>
  <security>
    <requestFiltering>
      <requestLimits maxAllowedContentLength="512000"></requestLimits>
    </requestFiltering>
  </security>
</system.webServer
1
Guess what, it didn’t work out! After modification when I tried to upload the file again, the same problem occured again. What happened?
According to the error message, the web.config or applicationhost.config should be configured according to the above suggestion. Modifying web.config didn’t work out. So, the applicationhost.config could be modified to see what happens.

Wait a minute! the applicationhost.config is the configuration file of IIS (IIS7.0 or heigher). Does that mean IIS has a Request size validation?

Yes it has. Until IIS 7.0 there was no Request size validation, but since IIS 7.0, the Request length is verified by IIS first, before deliverying the Request to Asp.net.
So, to be true, it doesn’t really make any sense to increase the maxAllowedContentLength value in web.config. The Request dies even before reaching the Asp.net. So, whatever is to be configured, it has to happen at IIS.
Well, as I figured out, there are two ways you can configure this value in IIS:
1. Configuring the applicationhost.config
Open the %WINDIR%\System32\inetsrv\config\applicationHist.config in editor and specify the following configuration within the security/requestFiltering section(By setting maxAllowedContentLength value in Bytes):
1
2
3
<requestFiltering>
      <requestLimits maxAllowedContentLength="512000000"></requestLimits>
</requestFiltering>
Note:
Modifying the above configuration worked for me in one PC (Running Windows 7+IIS 7.0), but, didn’t work on another one (Running Windows Vista + IIS 7.0). After configuring the applicationHost.config file, I tried to upload the large file and the same error message was appearing again. I don’t know why, but, if you have the same experience, applying the following approach (Configuring via IISManager) would definitely work. 
2. Configuring via IISManager
Open the IIS Manager and select the site or application you need to configure in the left panel
Select “Features View” and double click on the “Request Filtering” icon.
Figure : Request Filtering
Note :
If you can’t find the “Request Filtering”icon, you need to install the IIS Administration Pack from this link : http://www.iis.net/download/AdministrationPack. This is a lightweight installation which shouldn’t take too much time on a decent internet speed.
Double clicking on the “Request Filtering”icon will bring up the Request filtering configuration window. Right click on the window and select the “Edit Feature Settings” option:
Figure : Edit Feature Settings option in IIS
Finally, specify the Maximum allowable content length (In Byte) in the following window and click “OK” to save:
Figure : Specifying Maximum allowable content length in Bytes
This worked perfect for me and I was able to upload the large file now without any problem. Hope, this will work for you too :)

- See more at: http://www.shubho.net/2011/01/http-error-40413-not-found-request.html#sthash.dKksj9Q8.dpuf


Sqlserver notes

December 23, 2013

how to assign return value of stored procedure to a new variable
CREATE PROCEDURE dbo.sp_Name
@In INT,
@Out VARCHAR(100) OUTPUT

AS
BEGIN
SELECT @Out = ‘Test’
END
GO

If you are unable to change the stored procedure, another solution would be to define a temporary table, and insert the results into that

DECLARE @Output VARCHAR(100)

CREATE TABLE #tmpTable
(
OutputValue VARCHAR(100)
)
INSERT INTO #tmpTable (OutputValue)
EXEC dbo.sp_name 9999, 99989999, ‘A’, ‘S’, null

SELECT
@Output = OutputValue
FROM
#tmpTable

DROP TABLE #tmpTable

—-

sql server find string in another string

DECLARE @document varchar(64);

SELECT @document = ‘Reflectors are vital safety’ +
‘ components of your bicycle.';
SELECT CHARINDEX(‘bicycle’, @document);

Result : 48


Follow

Get every new post delivered to your Inbox.