CAT | SQL
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:
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%'
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 = 'firstname.lastname@example.org', @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:
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!
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.
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 )