The easiest way to test connection issues with Universal Data Link (.udl) files. No code required. Simply create an empty text file on a Windows machine with the extension .udl and double click it.

A wizard pops up and you can test your configuration pretty quickly.  If you did a default install of anything other than SQL Express, your instance name (the part to the right of the ‘\’ below) will default to MSSQLSERVER and shouldn’t be shown in the server name.  If you installed SQLExpress, your instance name will be SQLEXPRESS.

And, as a bonus, once you get it working you can open it in a text editor and extract your connection string. If it doesn’t work on your local box, try it on the server itself. If that works, skip on down to the end of this for the culprit.

You can also attempt to telnet directly to port 1433 (the default TCP/IP port for SQL Server) to test connectivity.  You won’t see anything once you get there, but if the connection succeeds, you are in business.

If you are not running SQL Server locally, you could also get into trouble if remote connections are not allowed. To check/change this, Open up Management Studio and right click on the instance you are connected to:

open the properties window:

and go to the connections tab and insure remote connections are allowed:

If none of that works, the problem you are probably having is related to the setup of SQL Server itself. Open up the SQL Server Configuration Manager:

then go to the SQL Server Network Configuration:

then double click on the Protocols for MSSQLSERVER:

and make sure that TCP/IP is enabled:

If TCP/IP is enabled, you may be running into firewall issues (it requires port 1433 by default, but it can be configured to a different port).   To see which port your SQL instance is running on, double click on the TCP/IP icon to open the properties page and on the IP Address tab you will find the TCP port you are running on:

connect · SQL Server · troubleshooting · UDL · Universal Data Link

This isn’t really big news, but I have had to look it up twice in the last quarter.  To find which stored procedures contain a specific text string, use:

SELECT Name, OBJECT_DEFINITION(OBJECT_ID)
FROM sys.procedures sp
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Language%'

Example · SELECT · SQL Server · Stored Procedures · sys.procedures

I have a project that requires me to send contracts that are stored in SQL Server to customers and I started poking around for the best way to do it.  First, I found a quick code snippet that sends an email using sp_send_dbmail:

 EXEC msdb.dbo.sp_send_dbmail
@recipients = 'lsmithmier@productiveedge.com',
@body = 'Just testing the mail',
@subject = 'Sending Mail using Database Mail' ;

 

When I first ran it on my database server (SQL Server 2008 R2) I got the following error:

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component
'Database Mail XPs' because this component is turned off as part of the
security configuration for this server. A system administrator can enable the
use of 'Database Mail XPs' by using sp_configure. For more information about
enabling 'Database Mail XPs', see "Surface Area Configuration"
in SQL Server Books Online.

So I cranked up Microsoft SQL Server Management Studio and set email up:

1.  Open up the Management section and right click on the Database Mail item:

2.  Choose Configure Database Mail

3.  Click Next

4.  Click Next

5.  Click Yes

6.  Set the Profile name and choose Add an SMTP account:

7.  Enter your settings:

8.  Set the profile as both Public and Default:

9.  Click Next

10. Click Finish

11. Click Close

and it is all setup!  Really easy compared to the old way of setting email up with SQL Server.  Then I re-ran the query and got the following response:

Mail queued.

 

But then what?  I ran a quick query and found that the email wasn’t sent correctly:

 use [msdb]
SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO 

so I looked back over my settings and updated the port number to hit the outgoing relay and everything started working!

DatabaseMail · SQL Server

I am often tasked with making modifications to existing UIs, and tend to try and limit the impact I have by pushing a lot of the formatting and BL into the database.  One piece of code that I regularly have to look up is the code to convert a result set into a comma delimited list.  Here is the SQL 2005/2008 code that I just created for use in a client environment:

CREATE FUNCTION [dbo].[fn_LinkedString]
(
	-- Add the parameters for the function here
	@ID varchar(10)
)
RETURNS nvarchar(1000)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result nvarchar(1000)

	-- Add the T-SQL statements to compute the return value here
	SELECT @Result= x.List
	FROM
		(
			SELECT
				L1.ID,
				List = SUBSTRING((SELECT (', '+ LinkedID)
										FROM Linked L2
										WHERE
											L1.ID = L2.ID
										ORDER BY
											ID,
											LinkedID
										FOR XML PATH('')
										), 3, 1000) FROM Linked L1
			GROUP BY ID
		) x
	WHERE
		x.ID = @ID
	-- Return the result of the function
	RETURN @Result

END

I wrapped the code in a scalar valued function to make it easier to use and to keep the code clean when used.

SQL Server · TSQL

I occasionally need to get some representative values from a table for testing and thought my solution might be something worth sharing.  I tend to think in nested selects and go back and clean up code into joins where appropriate.  Below you will see that I am using a group by and a min function to identify single instances of different types, and then pulling the required data in an outer select:

SELECT
	UserName
FROM
	Users
WHERE
	UserID in
	(
	SELECT
		MIN(UserID)
	FROM
		USERS
	WHERE
		Active = 1
	GROUP BY
		TypeCode
	)

Example · SQL Server · TSQL