Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

Archive for the ‘Tips’ Category

Moving SQL 2000 Logins to SQL 2005

Posted by jeffprom on December 10, 2009

If you backup a database on one server and restore it to another, you can have the problem where the database has logins associated to it, but the instance does not. One problem, is that the database has a unique SID associated with the login name. If you create a new instance login with the same name, it will generate a different SID than the database one. I’ve read that if you are using 2005 and above, you simply create the new login for the instance and run the following to sync up the SID’s.
USE YourDatabaseName
EXEC sp_change_users_login 'Update_One', 'UserName', 'UserName'
EXEC sp_change_users_login 'Auto_Fix', 'UserName'

However, in my case I was moving a database from SQL 2000 to SQL 2005 and these did not work.
The Update_One produced:
Msg 15063, Level 16, State 1, Procedure sp_change_users_login, Line 143
The login already has an account under a different user name.

What I had to do was:
1. Restore the database to the new server (SQL 2005)
2. Do NOT create a new login yet.
3. Run the following to find the unique SID associated to the database user account. Next we create a
new instance login with the same SID as the database account.

-- Look up the SID from the database
USE YourDatabaseName

SELECT AS [DB_LoginName], D.sid AS [DB_SID], AS [Server_LoginName], S.sid AS [Server_SID]
FROM sys.database_principals AS D LEFT OUTER JOIN sys.server_principals AS S ON =

-- Next, take that SID and put it in here to create the login account
CREATE LOGIN UserName WITH PASSWORD = 'Password', SID = 0xB0A2667BAEDE1B4AB93EAA0F9525DD21

You can re-run the SELECT query to verify that they are indeed the same.


Posted in T-SQL, Tips, Troubleshooting | Leave a Comment »

Date Formatting Functions

Posted by jeffprom on October 14, 2009

Different date formats are often neccesary on many occasions. Here are some Scalar-valued Functions you can use to help. Simply call your function and give it the full datetime field and it will return the formatted value depending on which funtion you call. For example; dbo.fn_dateYM(fullDateField) would return YYYY-MM.

-- Enter in full date. Return Year and Month: 2009-10
CREATE FUNCTION [dbo].[fn_dateYM]
@dateMDYTime smalldatetime
RETURNS varchar(7)
DECLARE @dateYM varchar(7)
SELECT @dateYM = CAST(YEAR(@dateMDYTime) AS CHAR(4)) + N'-' + RIGHT('00' + LTRIM(RTRIM(CAST(MONTH(@dateMDYTime) AS CHAR(2)))),2)

-- Enter in full date. Return Year: 2009
CREATE FUNCTION [dbo].[fn_dateY]
@dateMDYTime smalldatetime
RETURNS varchar(4)
DECLARE @dateY varchar(4)
SELECT @dateY = CAST(YEAR(@dateMDYTime) AS CHAR(4))

-- Enter in full date. Return Month, Day, and Year: 10-14-2009
CREATE FUNCTION [dbo].[fn_dateMDY]
@dateMDYTime smalldatetime
RETURNS varchar(25)
DECLARE @dateMDY varchar(25)
SELECT @dateMDY = convert(varchar(25), cast(@dateMDYTime as smalldatetime), 101)

-- Enter in full date. Return Month: 10
CREATE FUNCTION [dbo].[fn_dateM]
@dateMDYTime smalldatetime
RETURNS varchar(2)
DECLARE @dateM varchar(2)
SELECT @dateM = CAST(MONTH(@dateMDYTime) AS CHAR(2))

-- Enter in full date. Return Day: 14
CREATE FUNCTION [dbo].[fn_dateD]
@dateMDYTime smalldatetime
RETURNS varchar(2)
DECLARE @dateD varchar(2)
SELECT @dateD = CAST(DAY(@dateMDYTime) AS CHAR(2))

Posted in T-SQL, Tips | 1 Comment »

Renaming SQL Server

Posted by jeffprom on January 6, 2009

When you setup a server for replication you may find unexpectedly that SQL complains about your server name. The most likely reason is that the SQL Server name doesn’t match your machine name.
Run the following command to take a look:

The results should be identical. If they are different, you will need to rename your SQL Server name to match the machine name. To do this use the following:

use master
-- Remove old Server Name
exec sp_dropserver ‘OLDNAME’
-- Add New Server Name
exec sp_addserver ‘NEWNAME’,’LOCAL’

You then need to restart SQL Server. Now open a new query and run the following again:
They should now be the same.

Posted in Tips, Troubleshooting | Leave a Comment »

Database Mail

Posted by jeffprom on August 28, 2008

One great way to keep track of failed jobs is to setup email notifications. In SQL 2000 the email system was called SQL Mail. In SQL 2005 it is called Database Mail and is very different. Here are some instructions to setup Database Mail and some nifty queries to manage it.

Setup Database Mail:
1. In Management Studio expand the Management folder.
2. Right click on Database Mail, Configure Database Mail.
3. From the main menu you can add and edit profiles and accounts.
4. For a new system select the top option of ‘Set up Database Mail by performing the following tasks:’
5. Click Yes to enable the mail feature.
6. Fill in the appropriate fields with your mail info, choose defaults for the rest and make your way to the finish page.
7. Now right click on SQL server Agent, Properties, Alert System.
8. Check the box ‘Enable mail profile’. Your mail profile name should show up.
9. Restart the SQL Agent Service from within windows.
10. You can send a test by right clicking Database Mail, Send Test E-mail.

Setup an Operator:
1. Under SQL Server Agent right click Operators, New Operator.
2. Pick a name, and fill in the E-mail name (address).

Setup Job Failure Notifications:
1. Now under Jobs, select a job and right click properites, Notifications.
2. Check the E-mail box, select your Operator from the drop down, and select ‘When the job fails’.

Now your job will notify you via email if it fails. But what’s happening behind the scenes with the mail system? Here are some queries that let you see what’s going on.

USE msdb

-- Show Mail Event Log
SELECT event_type AS [Type], log_date AS LogDate, [Description]
FROM sysmail_event_log
ORDER BY log_date DESC

-- Show Sent Messages
SELECT recipients AS [To], [Subject], Body, sent_status AS [Status], sent_date AS DateSent
FROM sysmail_sentitems
ORDER BY sent_date DESC

-- Show Failed Messages
SELECT recipients AS [To], [Subject], Body, sent_status AS [Status], sent_date AS DateSent
FROM sysmail_faileditems
ORDER BY sent_date DESC

-- Show All Items
SELECT recipients AS [To], [Subject], Body, sent_status AS [Status], sent_date AS DateSent
FROM sysmail_allitems
ORDER BY sent_date DESC

-- Show Unsent Messages
SELECT recipients AS [To], [Subject], Body, sent_status AS [Status], sent_date AS DateSent
FROM sysmail_unsentitems
ORDER BY sent_date DESC

And there you have it. You can now setup email notifications and see what the email system is doing.

Posted in Database Mail, Tips | Tagged: | Leave a Comment »

SSRS Failed Subscription Notifications

Posted by jeffprom on August 22, 2008

Reporting Services can email reports to end users on a regular basis without allowing them access to the web portal. This gives you a little more control over the report and can make it easier for the end user. One problem is that with numerous reports scheduled with numerous parameters to numerous recipients it can be difficult to know if/when a report failed to be sent. It looks bad when the VP keeps asking why he didn’t get his report today.

SSRS subscriptions are very picky. If you have one invalid email address in a subscription with multiple recipients, the whole subscription fails and nobody gets the report. Likewise, if one of your report parameters changed the report will not be sent. Surprisingly there isn’t an out of the box solution to see what reports failed to run.

Here is a query I wrote that checks SSRS subscriptions and returns any failed reports. Use the database you setup for Reporting Services. (default is ReportServer)

SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description
FROM Subscriptions AS S
ON C.ItemID = S.Report_OID
WHERE LEFT (S.LastStatus, 12) != ‘Mail sent to’
AND LEFT (S.LastStatus, 12) != ‘New Subscrip’

The first time I ran this we had 4 reports that had failed and we weren’t aware of it! I’ve set this up as an SSRS report and view it daily, and have it emailed to me so I know all my reports were sent.

Posted in SSRS, Tips | Tagged: , | 10 Comments »