Helping people on the various forums gives me a great sense of satisfaction. Recently, I was looking going through the unanswered questions section in the Ask module of this site, when I stumbled across a very interesting question:

How to get the IP address associated to the SQL Server without the use of xp_cmdshell?

Now, the solution that I am going to present today is a way of getting the client connection and SQL server IP address using DMVs. I myself have never used this in production, because all of our servers are on static IPs, so we knew and planned the IPs even before the systems were ever built. Hence, if you notice any obvious flaws in this approach, please feel free to discuss them in the comments section below.

You can customize the script provided below as per your need. Basically, you need to connect to your instance of SQL Server with a user having VIEW SERVER STATE rights (required by the DMV) and simply run the script.

USE tempdb;

GO

SELECT sec.client_net_address,

       sec.client_tcp_port,

       sec.local_net_address,

       sec.local_tcp_port,

       st.text,

       ses.session_id,

       ses.login_time,

       ses.original_login_name,

       ses.nt_domain,

       ses.nt_user_name,

       ses.host_name,

       ses.program_name,

       ses.host_process_id,

       ses.client_version,

       ses.client_interface_name,

       ses.ansi_defaults,

       ses.ansi_null_dflt_on,

       ses.ansi_nulls,

       ses.ansi_padding,

       ses.ansi_warnings,

       ses.arithabort,

       ses.open_transaction_count,

       ses.concat_null_yields_null,

       ses.transaction_isolation_level,

       ses.lock_timeout

FROM sys.dm_exec_sessions AS ses

INNER JOIN sys.dm_exec_connections AS sec ON ses.session_id = sec.session_id

INNER JOIN sys.dm_exec_requests AS ser ON sec.connection_id = ser.connection_id

CROSS APPLY sys.dm_exec_sql_text(ser.plan_handle) AS st

WHERE ses.is_user_process = 1;

GO

The output looks similar to the one shown in the screen-shot below:

The columns of interest are:

• client_net_address = IP address of the client who established the connection
  
• client_tcp_post = TCP Port number over which the client established the connection
  
• local_net_address = Represents the IP address on the server that this connection targeted
  
• local_tcp_port = Represents the Server TCP port that the connection targetted

Please note: The IP addresses and port information will only be available in case of TCP/IP connections. Shared Memory and Named Pipes do not use IP addresses to establish the connection.

Further Reading

• sys.dm_exec_connections [Link]

Until we meet next time,

Be courteous. Drive responsibly.

Throughout my years of work in the IT industry, I have learnt that hanging around in the forums is always a learning experience. Recently, I was looking for a bit of a challenge and went to the unanswered questions section in the Ask module of this site. I stumbled across this question.

The Question

The person who posted the query had inherited a 3rd party database where he found three (3) different stored procedures with the same schema, same name, but with different functionality. However counter-intuitive it may seem, it’s quite possible. Try it out for yourself by using the script shown below:

USE tempdb;
GO

IF OBJECT_ID('dbo.proc_ManipulateNumbers','P') IS NOT NULL
    DROP PROCEDURE dbo.proc_ManipulateNumbers;
GO

--SP #1 - Addition
CREATE PROCEDURE dbo.proc_ManipulateNumbers; 1 
    @iA INT,
    @iB INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @@PROCID AS ProcedureId,
           'SP #1 - (@iA + @iB) = ' AS Operation, 
           (@iA + @iB) AS Value;
END
GO

--SP #2 - Multiplication
CREATE PROCEDURE dbo.proc_ManipulateNumbers; 2
    @iA INT,
    @iB INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @@PROCID AS ProcedureId, 
           'SP #2 - (@iA * @iB) = ' AS Operation, 
           (@iA * @iB) AS Value;
END
GO

--SP #3 - Division
CREATE PROCEDURE dbo.proc_ManipulateNumbers; 3 
    @iA INT,
    @iB INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @@PROCID AS ProcedureId,
           'SP #3 - (@iA / @iB) = ' AS Operation,
           (@iA / @iB) AS Value;
END
GO

/************** The Test *****************/
--Run the first procedure
EXEC dbo.proc_ManipulateNumbers;1 @iA = 10, @iB = 2;
GO

--Run the second procedure
EXEC dbo.proc_ManipulateNumbers;2 @iA = 10, @iB = 2;
GO

--Run the third procedure
EXEC dbo.proc_ManipulateNumbers;3 @iA = 10, @iB = 2;
GO

--Check the ObjectId of the procedure
SELECT OBJECT_ID('dbo.proc_ManipulateNumbers','P') AS ProcedureObjectId;
GO

If you run the script provided above, this is what you would find as the output:

The output tells us that we have three stored procedures with the same schema, same name, same ObjectId, but different functionality – one adds two numbers whereas the others either multiply or divide them.

(If you are getting a headache by looking at the output, a sip of coffee might help)

The question therefore is:

How is it possible to have multiple stored procedures with different logic, but the same name?

How can the same ObjectId be assigned to all these mysterious procedures?

Numbered Stored Procedures – The Answer

The answer resides in a little known feature of SQL Server – numbered stored procedures.

Notice the use of semi-colon (;) followed by a number in the CREATE PROCEDURE statement. The semi-colon is not used as a statement terminator here, but is used to indicate that the definition is actually a different version of a stored procedure with the same name and should be grouped together.

When attempting to access these procedures, all one needs to do is again use the semi-colon (;) followed by the stored procedure version number. Hence, when we execute the following, SQL Server knows to use version #2, i.e. the one where supplied inputs are multiplied.

--Run the second procedure
EXEC dbo.proc_ManipulateNumbers;2 @iA = 10, @iB = 2;
GO

Because they share the same objectId, they can be dropped with a single DROP PROCEDURE statement.

WARNING!

Per Books-On-Line, “This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.”

Hence, while as interesting as this feature may sound, please avoid using this feature in new development work. Also, as versions move along, start moving away from this implementation.

Further Reading:

• Semi-colon as a statement terminator [Link]
• CREATE PROCEDURE statement [Link]

I had never heard of numbered stored procedure before encountering the mentioned Ask post. Had you heard about this in the past? Have you ever used numbered stored procedures? Why? Do let me know.

Until we meet next time,

Be courteous. Drive responsibly.

I am sure that many of my readers will be able to co-relate with the situation in today’s post.

We recently faced a situation at the office where one of our application servers was unable to communicate to the database server – the servers could “ping” each other just fine, but the SQL Server was inaccessible. While there were indications of connectivity being an issue, we did not have any definitive proof. The IT administrator continued to maintain that the server has been configured in the same way as all our other application servers were. We were therefore required to come up with a mechanism to test connectivity from the application server to the database server without the use of SSMS, SQLCMD (being the application server, it didn’t have any SQL client components installed) or any other applications. We had to use something that is shipped with the operating system.

Universal Data Link (UDL) file

Creation and Basic Configuration

The Microsoft Data Access Components (MDAC) provide the Data Link Properties dialog box as the common user interface for specifying connection information to a data provider on Windows 2000 and later operating systems. One can use the Data Link Properties dialog box to save connection information in a universal data link (.udl) file. We will be using this *.udl file to configure the most common connection string parameters and then help us test the connectivity to our database server.

1. On the desktop, or any other folder, right-click and create a new Text document

2. Rename the newly created file to “Test Database Connectivity.udl” (you can have the file name of your choice, the extension has to be .udl)

3. Double-click the file to open the “Data Link Properties” dialog box

4. In the “Provider” tab, choose the appropriate provider in use by the application

5. Provide the connection information in the “Connection” tab

6. You can define the connection timeout value in the “Advanced” tab

7. Other connection properties can be defined in the “All” tab

8. Click “OK” to save the data link information into the UDL file

Advanced Editing

Under the covers, the UDL file is nothing but a connection string stored in plain text. For seasoned administrators, the UDL file can therefore be opened in NOTEPAD where advanced connection string parameters can be defined by simply editing the connection string.

Shown below is the information from the UDL file we just defined above:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID=””;Initial Catalog=AdventureWorks2012;Data Source=W8SQLSERVERSQL2K12;Application Name=My Test Application;Initial File Name=””;Server SPN=””

Testing connectivity using the UDL file

Once the connection string definition in the UDL file has been finalized, one needs to double-click on the UDL file in the Windows explorer again to launch the “Data Link Properties” window.

To test the connection, we need to navigate out to the “Connection” tab and simply click “Test Connection” – If the connection succeeds, the underlying hardware, firewall and other configuration is as required.

Summary

The UDL file method of testing connection strings is not new. Yet, it is often forgotten as being one of the most easiest ways to test SQL Server connectivity. I would like to hear from you regarding the methods you use for testing connection strings when no SSMS, SQLCMD or other tools/applications are available at your disposal.

Further Reading:

• Testing Connection Strings using SSMS [Link]

Until we meet next time,

Be courteous. Drive responsibly.

Recently, I wrote a post on using Regular expressions in CHECK constraints. Based on this post, I was asked a very interesting question:

Can a computed column have a primary key constraint defined on it?

Quite frankly, I had never encountered such a situation. I did not have an answer to the question off the top of my head and I therefore requested some time for research. This week-end, I ran a small test in order to get an answer to this question. This post is the answer to the question I was asked.

Attempting to define a primary key on a non-persisted computed column

The script provided below has a computed column (non-persisted), which I am attempting to define as a primary key:

USE tempdb;
GO
IF OBJECT_ID('tempdb..#ComputedColumnAsPk') IS NOT NULL
    DROP TABLE #ComputedColumnAsPk;
GO

CREATE TABLE #ComputedColumnAsPk (RecordId INT IDENTITY(1,1),
                                  RecordValue VARCHAR(20),
                                  ComputedColumn AS ('AWC' + CAST(RecordId AS VARCHAR(10))),
                                  CONSTRAINT pk_ComputedColumnsAsPK PRIMARY KEY CLUSTERED (ComputedColumn)
                                 );
GO

Execution of the script yields the following error message:

Msg 1711, Level 16, State 1, Line 7
Cannot define PRIMARY KEY constraint on column 'ComputedColumn' in table '#ComputedColumnAsPk'. The computed column has to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 7
Could not create constraint or index. See previous errors.

Changes required to define the primary key

Reading the error message in detail tells us that the primary key constraint creation failed because:

• The column was not persisted
• The column is not nullable

Now, we know that the computed column consists of a static string and the RecordId column. The RecordId is an IDENTITY column, and based on the rules for IDENTITY columns it is automatically a NOT NULL column.

All that remains therefore is to convert the non-persisted computed column to a persisted computed column.

Executing the script provided below succeeds and we can validate that by checking the primary key definition (also provided in the script below):

IF OBJECT_ID('tempdb..#ComputedColumnAsPk') IS NOT NULL
    DROP TABLE #ComputedColumnAsPk;
GO

CREATE TABLE #ComputedColumnAsPk (RecordId INT IDENTITY(1,1),
                                  RecordValue VARCHAR(20),
                                  ComputedColumn AS ('AWC' + CAST(RecordId AS VARCHAR(10))) PERSISTED,
                                  CONSTRAINT pk_ComputedColumnsAsPK PRIMARY KEY CLUSTERED (ComputedColumn)
                                 );
GO

--Using sp_help to get the table design and verify that the primary key was indeed created
sp_help #ComputedColumnAsPk
GO

--Get the Computed Column definition
SELECT scc.is_persisted,* 
FROM sys.computed_columns AS scc
WHERE scc.name = 'ComputedColumn';
GO

Conclusion

As we can see through this little experiment, it is possible to have a primary key constraint defined on a computed column provided the column is not null-able and is persisted.

I am yet to come across a scenario in the projects I handle where I would need computed column as a primary key. If you, the kind reader has come across such a situation, please let us know by your comments in the comments section below.

Until we meet next time,

Be courteous. Drive responsibly.

At work, I am both a DBA and a technical lead for a couple of tracks. It is not uncommon to have situations where I have been working on a query for sometime and am pulled into a completely different task as part of a query resolution activity. When I return back to work on my query, it is sometimes hard to remember whether a particular result set is that of the most recent changes or that of a previous change iteration.

The SQL Server Management Studio provides a quick configuration option which allows me to include the query that produced a particular result as part of the informational messages generated during the query execution. This option is aptly worded “Include the query in the result set”.

To access this option, simply navigate out to Tools –> Options –> Query Results –> Results to Grid (Or Results to Text, if one is generating output as Text).

Here’s the option in action:

After setting this option, whenever I execute a query, the query is printed in the Messages tab. As can be seen in the image below, if the Query Editor has a query different than that available in the Messages Tab, it is a direct indication that the results in the Results tab is not generated by the query in the editor.

Summary

The ability to co-relate a result set with the query that produced the result set is a great productivity booster because it reduces the time it takes me to “get back into the zone”.

I trust this tip was helpful for you. Do share with me other productivity tips that you may have.

Until we meet next time,

Be courteous. Drive responsibly.