Clay's SQL Blog

Free ebook: Introducing Microsoft SQL Server 2012

2012-02-01T07:16:00.000-08:00

Microsoft Press is releasing a free ebook: Introducing Microsoft SQL Server 2012 (second DRAFT preview)by Ross Mistry and Stacia Misner. This Draft Preview release is only available in PDF format. To get the full details, look here; Microsoft Press.

Correcting Hadoop's HDFS java.io.IOException Errors

2012-01-08T09:06:00.000-08:00

This past Christmas and New Year, like the last three years now, I accompanied my wife to Bogota, Colombia for the holidays. The difference about this trip was; our pet Mickey was going with us, we had no side trips planned, and I was taking my new Lenovo T420 with me. We arrived in Bogota late at night on December, 22nd, and after being greeted by family, we took a cab to my mother-in-laws house. There’s something both exciting and terrifying about cab rides in Bogota, but after time, for me at least, it’s just fun.

I woke up the next morning well rested and we began our Christmas vacation. I began setting up a new wireless router that I had brought with me so that I could work remotely from wherever was most comfortable. After having installed the router, I connected with my new laptop and set my priorities for the task that I needed to complete. Was the typical task, update my time, respond to some emails, finalize peer reviews, and check up on database backups. After these tasks were completed, I had time to relax.

What was planned for us was day to day, but mostly we would go out to have lunch or dinner with friends and family, then return home. On a few nights, we engaged in consuming heavy amounts of adult beverages and dancing which is the custom in Colombia. Most of our time however, was spent at home. I would wake up early, before anyone else, and sit at the dining room table, next to the window with my laptop and watch the sun rise over the mountains enjoying some fresh Colombian coffee.

With nothing to do so early in the morning, I decided to log onto my desktop back in DC. When I connected to my desktop, I saw that I had left open a ssh connection to a small Hadoop cluster I had setup to do some testing, except HDFS was not working properly. This was the perfect opportunity to find out what went wrong in my install and configuration of the cluster. The only catch was, is that I would have do everything from the command shell, no gui. I had followed Michael Noll’s “Running Hadoop on Ubuntu”,but now, I was getting errors in the namenode logs.

I was getting java.io.IOException errors. In Michael Noll’s how-to, he describes how he addressed this error by reformatting the cluster. He described how he stopped all running daemons and deleted the /app/hadoop/tmp/hdf/name/data directory and then ran bin/hadoop namenode –format . Somewhere in my troubleshooting my errors and researching online, I found that it was also a good idea to add the following properties to the hdfs-site.xml configuration file.

< !-- Adding dfs.data.dir dfs.name.dir 1/1/2012. -- >
< property >
< name > dfs.data.dir < /name >
< value > /app/hadoop/tmp/dfs/name/data < /value >
< final > true < /final >
< /property >
< property >
< name > dfs.name.dir < /name >
< value > /app/hadoop/tmp/dfs/name < /value >
< final > true < /final >
< /property >

Also, if you get permission denied (publickey,password), you may want to check that the paths for the properties you added to the hdfs-site.xml file are correct. If this problem persist, you might try running the following on all nodes;

sudo chown –R hduser:hadoop /app/hadoop

Some of the other errors that I ran into were as follows;

Cannot lock storage /app/hadoop/tmp/dfs/name. The directory is already locked.
org.apache.hadoop.hdfs.server.namenode.FSNamesystem: Fatal Error : All storage directories are inaccessible.
ERROR org.apache.hadoop.hdfs.server.namenode.NameNode: java.net.BindException: Problem binding to Address already in use

I did not do a good job at keeping notes during my troubleshooting these issues, but it seemed that whenever I would try to fix one thing, a different error would pop up. I did find that I had made an typo in the hdfs-site.xml file. At the end of each path, I had added a /. Therefore, instead of /app/hadoop/tmp/dfs/name, I had /app/hadoop/tmp/dfs/name/. But once I corrected that and delete all data in the HDFS directory and then ran format, everything worked! So here is how that went.

After stopping all daemons and correcting the paths in the hdfs-site.xml file, I then deleted all data in the HDFS directory on all nodes.

hduser@bigdata1:/app/hadoop/tmp/dfs$">hduser@bigdata1:/app/hadoop/tmp/dfs$ sudo rm -rf *

Then, I ran the format.

hduser@bigdata1:/usr/local/hadoop/hadoop$ bin/hadoop namenode –format

The output looks like;

12/01/04 07:59:15 INFO namenode.NameNode: STARTUP_MSG:
/************************************************************
STARTUP_MSG: Starting NameNode
STARTUP_MSG: host = bigdata1/172.20.10.92
STARTUP_MSG: args = [-format]
STARTUP_MSG: version = 0.20.2
STARTUP_MSG: build = https://svn.apache.org/repos/asf/hadoop/common/branches/branch-0.20 -r 911707; compiled by 'chrisdo' on Fri Feb 19 08:07:34 UTC 2010
************************************************************/
12/01/04 07:59:15 INFO namenode.FSNamesystem: fsOwner=hduser,hadoop,adm,dialout,fax,cdrom,floppy,tape,audio,dip,video,plugdev,fuse,lpadmin,netdev,admin,sambashare
12/01/04 07:59:15 INFO namenode.FSNamesystem: supergroup=supergroup
12/01/04 07:59:15 INFO namenode.FSNamesystem: isPermissionEnabled=true
12/01/04 07:59:15 INFO common.Storage: Image file of size 96 saved in 0 seconds.
12/01/04 07:59:15 INFO common.Storage: Storage directory /app/hadoop/tmp/dfs/name has been successfully formatted.
12/01/04 07:59:15 INFO namenode.NameNode: SHUTDOWN_MSG:
/************************************************************
SHUTDOWN_MSG: Shutting down NameNode at bigdata1/172.20.10.92
************************************************************/

Next, I started HDFS.

hduser@bigdata1:/usr/local/hadoop/hadoop$ bin/start-dfs.sh

It’s output was;

starting namenode, logging to /usr/local/hadoop/hadoop/bin/../logs/hadoop-hduser-namenode-bigdata1.out
bigdata2: starting datanode, logging to /usr/local/hadoop/hadoop/bin/../logs/hadoop-hduser-datanode-bigdata2.out
bigdata3: starting datanode, logging to /usr/local/hadoop/hadoop/bin/../logs/hadoop-hduser-datanode-bigdata3.out
bigdata4: starting datanode, logging to /usr/local/hadoop/hadoop/bin/../logs/hadoop-hduser-datanode-bigdata4.out
bigdata1: starting secondarynamenode, logging to /usr/local/hadoop/hadoop/bin/../logs/hadoop-hduser-secondarynamenode-bigdata1.out

I ran the HDFS Admin Report to see the status of my cluster.

hduser@bigdata1:/usr/local/hadoop/hadoop$ bin/hadoop dfsadmin –report

The report displays the following;

Configured Capacity: 206701436928 (192.51 GB)
Present Capacity: 186873368576 (174.04 GB)
DFS Remaining: 186873294848 (174.04 GB)
DFS Used: 73728 (72 KB)
DFS Used%: 0%
Under replicated blocks: 0
Blocks with corrupt replicas: 0
Missing blocks: 0

-------------------------------------------------
Datanodes available: 3 (3 total, 0 dead)

Name: 172.20.10.127:50010
Decommission Status : Normal
Configured Capacity: 68900478976 (64.17 GB)
DFS Used: 24576 (24 KB)
Non DFS Used: 6721228800 (6.26 GB)
DFS Remaining: 62179225600(57.91 GB)
DFS Used%: 0%
DFS Remaining%: 90.24%
Last contact: Wed Jan 04 08:00:20 EST 2012

Name: 172.20.10.128:50010
Decommission Status : Normal
Configured Capacity: 68900478976 (64.17 GB)
DFS Used: 24576 (24 KB)
Non DFS Used: 6732939264 (6.27 GB)
DFS Remaining: 62167515136(57.9 GB)
DFS Used%: 0%
DFS Remaining%: 90.23%
Last contact: Wed Jan 04 08:00:20 EST 2012

Name: 172.20.10.48:50010
Decommission Status : Normal
Configured Capacity: 68900478976 (64.17 GB)
DFS Used: 24576 (24 KB)
Non DFS Used: 6373900288 (5.94 GB)
DFS Remaining: 62526554112(58.23 GB)
DFS Used%: 0%
DFS Remaining%: 90.75%
Last contact: Wed Jan 04 08:00:17 EST 2012

After this I started MapReduce.

hduser@bigdata1:/usr/local/hadoop/hadoop$ bin/start-mapred.sh

It’s output was this;

starting jobtracker, logging to /usr/local/hadoop/hadoop/bin/../logs/hadoop-hduser-jobtracker-bigdata1.out
bigdata3: starting tasktracker, logging to /usr/local/hadoop/hadoop/bin/../logs/hadoop-hduser-tasktracker-bigdata3.out
bigdata2: starting tasktracker, logging to /usr/local/hadoop/hadoop/bin/../logs/hadoop-hduser-tasktracker-bigdata2.out
bigdata4: starting tasktracker, logging to /usr/local/hadoop/hadoop/bin/../logs/hadoop-hduser-tasktracker-bigdata4.out

Success!

Now there are three web interface URLs that you can use to check up on your clusters health, there are;

http://localhost:50030/ – web UI for MapReduce job tracker(s)
http://localhost:50060/ – web UI for task tracker(s)
http://localhost:50070/ – web UI for HDFS name node(s)

2011-06-29T08:38:00.000-07:00

It's time again for SQL Saturday Washington DC! With last year's event being such a huge success, this year's event will be even better! We are expecting over 250 attendees to show up for a full day of training from some of the best speakers in the SQL Server community.

This is one event you don't want to miss! Everyone who attends will get a SQL Saturday T-Shirt and lots of swag! Free breakfast and lunch will be provided. We will have several SQL Server vendors that will be presenting the latest upgrades and solutions. And at the end of the day, we will be raffling off some awesome door prizes provided by our many sponsors!

SQL Saturday is a FREE one day training events for SQL Server professionals. SQL Saturday was initially the idea of three DBAs that wanted a Code Camp style event just for SQL Server professionals. It began with the first SQL Saturday in Tampa, Florida. The event was such a huge success, more events followed. After over 40 successful events, The Professional Association for SQL Server took over the administration of SQL Saturday events.

SQL Saturday events are usually divided into three tracks consisting of BI, Database Development, and Database Administration. Typically there will be five sessions per track. SQL Saturday tries to recruit local SQL Server professionals to present at the event, but occasionally, more nationally known speakers may also present.

SQL Saturday is all about sharing issues and solutions, and gaining knowledge that will make you a better SQL Server professional.

We’ll see you there!!!

Register at: http://www.sqlsaturday.com/96/eventhome.aspx

FOLLOW US!

SQL Server: An error occurred while executing batch. Error message is: The directory name is invalid.

2011-06-10T11:37:00.000-07:00

I logged onto the server this morning to delete some files because the disk had run out of free space. After deleting some files, I went to run some maintenance scripts in SSMS and received the following error:

An error occurred while executing batch. Error message is: The directory name is invalid.

After doing some online research, I found out that the reason for this error is because SQL Server cannot find the temp folder in which to store the query results. To correct this, try logging off and back on to the machine that you are running SSMS on. If the error persists, reboot the machine.

Cannot edit job steps in SSMS 2008 R2

2011-06-02T07:27:00.000-07:00

When attempting edit the job step, or view the job step details, I received the following error:

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the IClassFactory failed due to the following error: c001f011. (Microsoft.SqlServer.ManagedDTS)

You may also get this error when attempting to create a new job step.

I did a search and found several old post requesting help with the same problem. Microsoft corrected the problem with the last Hotfix; Cumulative Update package 7 for SQL Server 2008 R2.

For more information on Cumulative Update package 7 for SQL Server 2008 R2 and how to download it, go here; http://support.microsoft.com/kb/2507770

MADExop: The Mid Atlantic Developer Expo in Hampton, VA June 30 - July 1, 2011

2011-05-18T08:34:00.001-07:00

Ok, so I get an email from Andrew Duthie at Microsoft asking me to get the word out about the upcoming Mid Atlantic Developer Expo (MADExop) in Hampton, VA. Now this was the first time I had heard about it and really wanted go after I visited the website and read all the cool sessions they had lined up. But then, I saw that for only $20 you could bring your child for an all day kid geekout session! Now, what could be better? You go for two days of awesome and, did I mention cheep?, developer training from the best developers around and inspire your child to follow in your footsteps at the same time!

Take a look at the lineups for Thursday and Friday sessions:
Click here for a PDF of the Day 1 Agenda
Click here for a PDF of the Day 2 Agenda

Register here!
MADExpo 2011 Registration!

Idera's SQLsafe Restore Error "too recent to apply to the database"

2011-05-12T09:59:00.000-07:00

Last week, my manager came to me and asked if I could perform our first restore since installing the new SQLsafe Backup and Recovery software. Because I was the one that pushed for SQLsafe to take over as our Backup and Recovery solution for all production SQL Servers, I was more than happy to show off SQLsafe's ease of use in restoring databases. I select the point in time that I wanted to restore to and clicked NEXT.

I was expecting for the database to be restored in no time, but instead, and with horror written all over my face, I received the following error message in the "Result Text" with a BIG RED "Error" Progress indicator next to my Restore status.

-------------------------------------------- snip -------------------------------------------

<!--[if gte mso 9]> Normal 0 false false false EN-US X-NONE X-NONE

" Server instance: INSTANCE/NAME, Database: mas500_pl

The log in this backup set begins at LSN 94000000227900001, which is too recent to apply to the database. An earlier log backup that includes LSN 94000000221200001 can be restored.

RESTORE LOG is terminating abnormally."

Normal 0 false false false EN-US X-NONE X-NONE

--------------------------------------------------------------------------------------------

With embarrassment, I turned to my manager and told him that I would call Idera's support staff and get help with restoring the database. I called the support number and got routed to a voice mailbox. However, within just a few minutes, I got a call back from Carl at Idera's Customer Support. I explained to him the issue I was having and after some brief research on his part, he said based on the message I was receiving that it looked like another backup had been done and that the SQLsafe backup that I was attempting to use was not the most current.

I then opened SSMS and started the restore database wizard. I selected the database that I wanted to restore and then clicked file and add to browse to the folder. Then SURPRISE! The default backup folder appeared with recent backups of the database that I was attempting to restore.

I then clicked on the SQL Server Agent and found a Full Backup job scheduled to run every Sunday at 2:00 am which was after the SQLsafe Full backups.

To restore the database, I first had to restore the native Full backup from Sunday morning with the following sql script;

RESTORE DATABASE [mas500_pl]

FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\mas500_pl_db_201105010200.BAK'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

Next I opened SQLsafe and restored each Differential, one at a time, with "Force restore", "Ingnore Checksum Errors", and with non-recovery, "Not accessible", until the last Differential. For the last Differential, I restored it with recovery or "Fully accessible".

So a BIG Thank You goes out to Carl at Idera's Customer Support!

PASS Summit 2010 Keynote David DeWitt

2010-11-12T19:03:00.001-08:00

This was the best presentation during all of PASS Summit 2010. Everyone enjoyed Dr. DeWitt's keynote session on SQL Query Optimization.

PASS Summit 2010 Keynote David DeWitt

View more presentations from GraySystemsLab.

SQL Server Row Counts in Properties Are Wrong!

2010-06-10T09:22:00.000-07:00

One of my users came to me and asked why the row count in the table properties was different from the SELECT COUNT(1) from the table. This was a database that was sent to us from a different company and attached to our server. The first question for me was; where does row count in properties come from in SQL Server? As it turns out, it gets this count from the sysindexes table which we can see from the following;

USE DatabaseName
GO
SELECT rowcnt
FROM dbo.sysindexes
WHERE OBJECT_NAME(id) = 'TableName'
GO

-- Returns
330013

A count of the table results in the following;

USE DatabaseName
GO
SELECT COUNT(1) AS CNT
FROM dbo.TableName
GO

-- Returns
331882

To update the sysindexes table with the correct row counts, we run the following DBCC;

USE DatabaseName
GO
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS;
GO

Now when we run our queries again, we get the following;

USE DatabaseName
GO
SELECT rowcnt
FROM dbo.sysindexes
WHERE OBJECT_NAME(id) = 'TableName '
GO

SELECT COUNT(1) AS CNT
FROM dbo.TableName
GO

-- Returns 
331882
-- Returns 
331882

Cast Scientific Notation as Money or Varchar

2010-05-24T08:43:00.000-07:00

I run into Scientific Notation on occasion and always refer back to my TSQL scripts folder. Most of the time, I'll have a column where about 50% of the rows contain Scientific Notation. In these cases, I use a CASE statement.

First, CAST as VARCHAR:

LTRIM(RTRIM(CAST(CAST('2.50823E+12' AS FLOAT) AS NVARCHAR)))

Second, CAST as MONEY:

LTRIM(RTRIM(CAST(CAST(CAST('2.50823E+12' AS FLOAT) AS NVARCHAR)AS MONEY)))

Now, let's see this in a CASE statement:

,CASE
WHEN [AMOUNT] LIKE '%E-%' THEN LTRIM(RTRIM(CAST(CAST(CAST([AMOUNT]AS FLOAT) AS NVARCHAR)AS MONEY)))
WHEN [AMOUNT] = ' ' THEN NULL
ELSE [AMOUNT]
END AS [AMOUNT]

CHECKDB - A severe error occurred on the current command. The results, if any, should be discarded.

2010-04-14T09:15:00.000-07:00

CHECKDB found 0 allocation errors and 0 consistency errors in database

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

I ran into a case where I had corrupted pages in my database. I had previously had this database on a SQL Server 2005 instance. When I ran CHECKDB in SSMS 2005, it returned info messages detailing the consistency errors. However, after restoring the database on a 2008 instance and running CHECKDB in SSMS 2008, I received the message above. At first, you get this, “What now!” feeling. Don't panic. If you go to the SQL error log, you’ll see the CHECKDB results. Be sure that you are running CHECKDB with the WITH ALL_ERRORMSGS option.

Adding Leading Zeros in T-SQL

2009-10-01T13:49:00.000-07:00

Today I ran into a situation where I had a character data type column that contained product ID numbers and these numbers were missing leading numbers. The column was a CHAR(11) as all product IDs should begin with leading zeros and be 11 characters long. To add leading zeros to a NVACHAR, VARCHAR, OR CHAR type column, use the RIGHT function.

SELECT RIGHT('00000000000' + LTRIM(RTRIM(PRODUCT_ID)),11) AS PRODUCT_ID

Or;

SELECT RIGHT(REPLICATE('0', 11) + LTRIM(RTRIM(PRODUCT_ID)),11) AS PRODUCT_ID

PASS SQL Summit 2009 Discount Code

2009-09-14T06:57:00.000-07:00

With time running out for taking advantage of the pre-registration discounts to PASS 2009, here is a discount code for $100 off the registration fee. Get $100 off SQL Summit with code 24HR3D. See you at PASS 2009!

Microsoft Launches Non-Profit Open Source Foundation

2009-09-11T06:53:00.000-07:00

I’m not sure what to think of this. I have always been a fan of the open source movement. I know, then why am I a Microsoft DBA? I can only say, at the time, my employers required me to support Microsoft products. And after working with SQL Server for many years, I became comfortable with it.

Anyway, Microsoft launched a new Non-Profit Open Source Foundation yesterday named, The CodePlex Foundation. And already, there are a lot of discussions about it. I think that Microsoft is trying to continue to build its community by having developers create open source software that will assist the community as a whole.

Also, I have no doubt that Microsoft has a business motivation in this as well, because after all, Microsoft is a profit driven company. It could be that Microsoft would like to see if there are ways for them sell their products to entities that run open source, or it could be something more. Either way, I don’t believe open source developers will embrace it as evident in this Slashdot post.

The CodePlex Foundation : http://codeplex.org/

New SQL Server Background

2009-09-10T05:15:00.000-07:00

I just picked up on Twitter that Buck Woody (MSFT) has published a blog post about SQL Server Desktop Screen Background. Use the link below to get it.

http://blogs.msdn.com/buckwoody/archive/2009/09/07/sql-server-desktop-screen-background.aspx

SQL Server Snapshot Sparse File Size

2009-07-31T10:57:00.000-07:00

When you execute the CREATE AS SNAPSHOT sql statement, the snapshot creates a NTFS sparse file. In a sparse file only “nonzero” information is stored on the disk. The maximum size of a database snapshot is the size of the source database at snapshot creation. In Windows Explorer, the snapshot files are shown with a size matching that of the source database. However, when you go into the file properties you will see that the size on disk is 0KB or a multiple of 64KB (representing 1-8 data pages) if some data has been transferred.

At the beginning, there is no source/user data in the snapshot file. It’s only after changes are made in the source database that data pages get written to the snapshot file. When a data page in the source database is changed, the original data page is first copied to the snapshot file before updating in the source database (copy-on-write). The first time any page is written to the sparse file the entire 64K extent is allocated. All other pages in the extent are written as zero's.

When a sparse file is populated, Windows limits the amount of data that may reside in the file. Once the amount of data stored in the sparse file exceeds the limit further data storage in the file may be prevented.

• Windows 2003 - 64GB (Error 1450 returned)
• Windows 2008 and Vista - 16GB (Error 665 returned)

Windows 2003 Error: The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x000031abb4e000 in file with handle 0x00000F74. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.

Windows 2008 Error: The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x000005bd3dc000 in file 'Test.mdf:MSSQL_DBCC8'

Let’s take a look at what takes place when the snapshots are created.

A sparse file is created. This file will have the same file size as the database. Remember, the only data that may be contained in the snapshot file at this point are pointers to the source database data files. Only after something changes in the source database will copy-on-write insert data into the snapshot file.
The database is checkpointed (all dirty pages in the buffer cache are written to disk).
If uncommitted transactions are in progress in the database, the snapshot will reflect the state of the database before these transactions started in other words, in the context of the snapshot, these transactions will be rolled back – but transactions in the database itself are not.
The snapshot is brought online and is ready for use.

Quick Note:

From a Disaster Recovery standpoint, SQL Server snapshots are not very useful as they require an online database to function. In situations where there is a hardware failure, the database is no longer online and the snapshot becomes useless. Also, snapshots cannot be restored when users are accessing the database. So if there is a roll back in progress or some other process that does not allow the database’s state to be changed, snapshot restores can not be applied. However, in situations where an object was accidently deleted or altered, and needs to be restored, snapshots are ideal.

For a more detailed look inside how Copy-On-Write works, see: http://blogs.msdn.com/psssql/archive/2009/01/20/how-it-works-sql-server-sparse-files-dbcc-and-snapshot-databases-revisited.aspx

Can’t Delete Jobs (Microsoft SQL Server, Error: 547)

2009-07-01T14:40:00.000-07:00

I was creating a new job to handle weekly database maintenance and when I attempted to delete the old job, I received the following error;

TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Job 'Weekly_Maintenance'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.

The statement has been terminated. (Microsoft SQL Server, Error: 547)
------------------------------

If you try deleting the job from the sysmaintplan_subplans table, you also get the following error:

Msg 547, Level 16, State 0, Line 41

The DELETE statement conflicted with the REFERENCE constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_log", column 'subplan_id'.

I wanted to create a stored procedure that I could just pass a job name to and it would check each of the tables and delete all references for me. Here is what I came up with.

CREATE PROC dbo.DropJob
@JobName AS VARCHAR(200) = NULL
AS
DECLARE @msg AS VARCHAR(500);

IF @JobName IS NULL
BEGIN
SET @msg = N'A job name must be supplied for parameter @JobName.';
RAISERROR(@msg,16,1);
RETURN;
END
IF EXISTS (
SELECT subplan_id FROM msdb.dbo.sysmaintplan_log WHERE subplan_id IN
( SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName)))
BEGIN
DELETE FROM msdb.dbo.sysmaintplan_log WHERE subplan_id IN
( SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName));

DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName);

EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
ELSE IF EXISTS (
SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName))
BEGIN
DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName);

EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
ELSE
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
GO

Now you can call the SP with the following;

USE [msdb];
EXEC dbo.DropJob @JobName = N'Shrink_AWP_Databases.Subplan_1';

Convert Legacy 6 Character and 7 Character CC YY MM DD (ISO) Dates

2009-06-24T12:57:00.000-07:00

The dates are stored as Century, Year, Month, and Day, where Century is 0 for 19 and 1 for 20, years are stored as two digits, and months and days always have leading zeros. In this example, somewhere along the way, the leading zeros for the Century were dropped.

Sample Data:

Let’s start solving this by creating our test data.

CREATE TABLE [dbo].[TestDates](
[Row_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[Legacy_Date] [varchar](8),
[SQL_Date] [datetime])
GO

INSERT INTO [HI_Plaintiff].[dbo].[TestDates] ([Legacy_Date])
VALUES('1000118')
GO

INSERT INTO [HI_Plaintiff].[dbo].[TestDates] ([Legacy_Date])
VALUES('1000229')
GO

INSERT INTO [HI_Plaintiff].[dbo].[TestDates] ([Legacy_Date])
VALUES('1000509')
GO

INSERT INTO [HI_Plaintiff].[dbo].[TestDates] ([Legacy_Date])
VALUES('960426')
GO

INSERT INTO [HI_Plaintiff].[dbo].[TestDates] ([Legacy_Date])
VALUES('981003')
GO

INSERT INTO [HI_Plaintiff].[dbo].[TestDates] ([Legacy_Date])
VALUES('950899')
GO

Here is a simple query that will only update valid ISO dates to SQL Server’s datetime data type.

UPDATE dbo.TestDates
SET SQL_Date = CONVERT(VARCHAR(10), CONVERT(DATETIME, CONVERT(VARCHAR(8), Legacy_Date + 19000000)), 101)
WHERE ISDATE(CONVERT(VARCHAR(8), Legacy_Date + 19000000)) = 1

Copy Several Tables to a Different Database Using Dynamic SQL

2009-06-10T05:11:00.000-07:00

Here is a quick example of how to used dynamic SQL in a cursor to copy several tables from one database to another. I was given a list of 150 tables that one of my customers needed to be copied. I didn’t want to script all of them so I created this cursor. All you need to do is create a list of the tables to be copied.

In this example, I’m using Excel. In my list the SOURCE is the name of the table that we are copying from and the TARGET is where it’s being copied to.

In the SOURCE database, create the following table.

CREATE TABLE [dbo].[TRANSFER_TABLE_LIST](
[SOURCE_TABLE] [varchar](100),
[SOURCE_SCHEMA] [varchar](100),
[SOURCE_DATABASE] [varchar](100),
[TARGET_TABLE] [varchar](100),
[TARGET_SCHEMA] [varchar](100),
[TARGET_DATABASE] [varchar](100)
)
GO

Now, import your table list into the TRANSFER_TABLE_LIST table and create the following cursor.
DECLARE @SQL AS VARCHAR (2000), @SOURCE_TABLE AS VARCHAR (100),
@SOURCE_SCHEMA AS VARCHAR (100), @SOURCE_DATABASE AS VARCHAR (100),
@TARGET_TABLE AS VARCHAR (100), @TARGET_SCHEMA AS VARCHAR (100),
@TARGET_DATABASE AS VARCHAR (100)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT [SOURCE_TABLE]
,[SOURCE_SCHEMA]
,[SOURCE_DATABASE]
,[TARGET_TABLE]
,[TARGET_SCHEMA]
,[TARGET_DATABASE]
FROM [dbo].[TRANSFER_TABLE_LIST]
OPEN c1
FETCH NEXT FROM c1
INTO @SOURCE_TABLE, @SOURCE_SCHEMA, @SOURCE_DATABASE, @TARGET_TABLE, @TARGET_SCHEMA, @TARGET_DATABASE
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = 'SELECT * INTO [' + @TARGET_DATABASE + '].[' + @TARGET_SCHEMA + '].['
SET @SQL = @SQL + @TARGET_TABLE + '] FROM [' + @SOURCE_DATABASE + '].['
SET @SQL = @SQL + @SOURCE_SCHEMA + '].[' + @SOURCE_TABLE + '];'
EXEC (@SQL)

FETCH NEXT FROM c1
INTO @SOURCE_TABLE, @SOURCE_SCHEMA, @SOURCE_DATABASE, @TARGET_TABLE, @TARGET_SCHEMA, @TARGET_DATABASE
END
CLOSE c1
DEALLOCATE c1

If you receive the following error:

Msg 203, Level 16, State 2, Line 23
The name 'SELECT * INTO [TransCorpReporting].[dbo].[Products_cust_trans] FROM [TransCorp].[dbo].[Products_cust_trans];' is not a valid identifier.

This means that you forgot to add ( ) around the @SQL variable.

Visit Falafel King at PASS 2009

2009-03-16T13:07:00.000-07:00

If you’ve been to the PASS Community Summit before, you’ll know that PASS does a great job at providing meals during the Summit. However, if you should miss meal time, don’t worry, just walk down Pike Street to 1st Street. There you’ll find Falafel King.

Falafel King in downtown Seattle is the best purveyor of Middle Eastern food I have ever had the privilege of patronizing. You can get an awesome meal for under $10.00. If you ever visit Seattle, you need to pay Falafel King a visit!

PASS: http://www.sqlpass.org/

Send Database Mail from a Trigger

2009-02-26T14:27:00.000-08:00

I was attempting to set up a trigger to send an email when a record was inserted into a table. I followed the instructions in Books Online, however, somewhere along the way, I ended up with the following error;

[SqlException (0x80131904): EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.]

As I attempted to solve for this error, I ended up using EXECUTE AS in the trigger. When attempting to use this method, I received the following error;

[SqlException (0x80131904): Cannot execute as the server principal because the principal "SQLMail" does not exist, this type of principal cannot be impersonated, or you do not have permission.]

The I decieded to trun away from all the post that I was following on the internet and start from scratch in a test enviorment. Here’s what I did:

As dbo, create the database Principal that will use Test database to send the email.

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'password'
GO

Create the database Principal that will use msdb database to send the email.

USE [master]
GO
CREATE LOGIN [MailUser] WITH PASSWORD=N'password'
GO
EXEC master..sp_addsrvrolemember @loginame = N'Mail_User', @rolename = N'sysadmin'
GO

Now start setting up the mail. To send Database Mail using stored procedures, you must be a member of the DatabaseMailUserRole database role in the msdb database.

USE [msdb]
GO
CREATE USER [MailUser] FOR LOGIN [MailUser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'DatabaseMailUserRole', N'MailUser'
GO

Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database.

To list all other members of the DatabaseMailUserRole execute the following statement:

EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';

Go to Surface Area configuration for features and expand Database engine option and select DatabaseMail option and checked Enable Database mail Stored procedure and click OK or Apply button.

Create a new Database Mail account.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Test_Database_Mail',
@description = 'Mail account for testing sending email.',
@email_address = 'somebody@domain.com',
@replyto_address = 'somebody@domain.com',
@display_name = 'Clay McDonald',
@mailserver_name = 'mail.domian.com'

Create a Database Mail profile.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Test_Email_Profile',
@description = 'Profile for testing sending email.'

Add the account to the profile.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Test_Email_Profile',
@account_name = 'Test_Database_Mail',
@sequence_number = 1

Grant access to the profile to all users in the msdb database.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Test_Email_Profile',
@principal_name = 'public',
@is_default = 1

Check to see if Service Broker is enabled.

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

is_broker_enabled will be 1 if Service Broker is enabled for the given database, otherwise it'll be 0.

To enable Service Broker run the following;

ALTER DATABASE msdb
SET ENABLE_BROKER;

Now test that Database Mail is working.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test_Email_Profile',
@recipients='somebody@domain.com',
@body = 'This is a test for Database Mail.',
@body_format = 'TEXT',
@subject = 'Database Mail Test'
-- eMail sent!

Now I create my test database.

USE [master]
GO
CREATE DATABASE [Test] ON PRIMARY
( NAME = N'Test', FILENAME = N'D:\MSSQL\DATA\Test.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Test_log', FILENAME = N'D:\MSSQL\LOG\Test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'Test', @new_cmptlevel=90
GO

Then create test table that will fire the trigger.

USE [Test]
GO
CREATE TABLE [dbo].[NewMail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Some_Text] [varchar](max) NULL
)
GO

Create the trigger.

USE Test
GO
CREATE TRIGGER TestMailTrigger
ON dbo.NewMail
AFTER INSERT
AS
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test_Email_Profile',
@recipients='somebody@domain.com',
@body = 'This is a test for Database Mail.',
@body_format = 'TEXT',
@subject = 'Database Mail Test'
END
GO

Test the trigger.

USE Test
GO
INSERT INTO dbo.NewMail (Some_Text)
VALUES('Fire the trigger to send mail')
GO
-- Mail sent!

Now we test logging in as TestUser. First, add TestUser to the Test database.

USE [Test]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [Test]
GO
EXEC sp_addrolemember N'db_datareader', N'TestUser'
GO
USE [Test]
GO
EXEC sp_addrolemember N'db_datawriter', N'TestUser'
GO

Now, disconnect and reconnect as Test_User.

USE Test
GO
INSERT INTO dbo.NewMail (Some_Text)
VALUES('Fire the trigger to send mail')
GO
-- It works!

NOTE: Database Mail messages and their attachments are stored in the msdb database. Messages should be periodically deleted to prevent msdb from growing larger than expected and to comply with your organizations document retention program. Use the sysmail_delete_mailitems_sp stored procedure to permanently delete e-mail messages from the Database Mail tables. An optional argument allows you to delete only older e-mails by providing a date and time. E-mails older than that argument will be deleted. Another optional argument allows you to delete only e-mails of a certain type, specified as the sent_status argument. You must provide an argument either for @sent_before or @sent_status. To delete all messages, use @sent_before = getdate().

If you have permission issues, you can start over by deleting Database Mail Accounts.
In SQL Mgt Studio, under Management -> Database Mail, you can select “Manage Database Mail accounts and profile”. Here you’ll find options for managing accounts and profiles.

Receiving Error: 18461 When Attempting to Move System Databases

2009-02-25T07:20:00.000-08:00

I was attempting to move the master, tempdb, msdb, and model. After I added the –c option, -m option, and set trace flag 3608, and restarted, I got Error: 18461 when attempting to either connect via SSMS or sqlcmd in the consol. I had no idea what the problem was. Here is how it all played out;

I logged onto the Database Server remotely. In the SQL Server Configuration Manager, I added the –c option, the –m option, and set trace flag 3608 in Startup Parameters with (;-c;-m;-T3608).

I stopped all SQL Services (SSIS, FullText, Analysis Services, Reporting, SQL Server Browser, and SQL Agent) and restarted SQL Server (MSSQLSERVER).

When I attempted to connect via SSMS or sqlcmd in the consol, I receive the following error;

Login failed for user 'cmcdonald'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

Then I attempted to login with Dedicated Administration Connection (DAC). I did this by opening SSMS window without connecting to a SQL Server. Then I clicked on the Database Engine Query button .
Next, I added ADMIN: and the name of my SQL Server Instance.

When I clicked Connect, I was connected to the instance. You can also connect with sqlcmd:

sqlcmd -A -d testDB -E -S \namedinstance

-A is for the DAC
-d is for the database
-E is for integrated security
-S is for the instance (in this example, a named instance)

Now I begin moving system databases starting with the model database.

1. Detach the model database by using the following commands:

use master
go
sp_detach_db 'model’
go

2. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder to the E:\Sqldata folder.

3. Reattach the model database by using the following commands:

use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go

4. Remove -c -m -T3608 from the startup parameters in SQL Server Configuration Manager.

5. Stop and then restart SQL Server. You can verify the change in file locations by using the sp_helpfile stored procedure. For example, use the following command:

use model
go
sp_helpfile
go

Next, moved the msdb database.

1. Add -c -m -T3608(;-c;-m;-T3608) in the startup parameters in the SQL Server Configuration Manager and then restart SQL Server.

2. Make sure that the SQL Server Agent service is not currently running.

3. Log in with DAC and detach the msdb database as follows:

use master
go
sp_detach_db 'msdb'
go

Note: If your login fails, you may need to restart the windows server.

4. Move the Msdbdata.mdf and Msdblog.ldf files from the current location (D:\Mssql8\Data) to the new location (E:\Mssql8\Data).

5. Remove -c -m -T3608 from the startup parameters box in Enterprise Manager.

6. Then stop and restart SQL Server.

7. Reattach the msdb database as follows:

use master
go
sp_attach_db 'msdb','E:\Sqldata\msdbdata.mdf’,'E:\Sqldata\msdblog.ldf’
go

To moved the master database, do the following;

1. Change the path for the master data files and the master log files in SQL Server Enterprise Manager. Note: You may also change the location of the error log here.

2. Right-click the SQL Server in Enterprise Manager and then click Properties.

3. Click Startup Parameters to see the following entries:

-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf

-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log file.

4. Change these values as follows:

a. Remove the current entries for the Master.mdf and Mastlog.ldf files.

b. Add new entries specifying the new location.

c.

-dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf

5. Stop SQL Server.
6. Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
7. Restart SQL Server.

Move tempdb files by using the ALTER DATABASE statement.

1. Determine the logical file names for the tempdb database by using sp_helpfile as follows:

use tempdb
go
sp_helpfile
go

-- tempdev 1 G:\MSSQL\tempdb.mdf PRIMARY
-- templog 2 G:\MSSQL\templog.ldf NULL

The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.

2. Use the ALTER DATABASE statement, specifying the logical file name as follows:

use master
go
Alter database tempdb modify file (name = tempdev, filename = ' E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = ' E:\Sqldata\templog.ldf')
go

You should receive the following messages that confirm the change:

Message 1

File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.

Message 2

File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.

3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.

4. Stop and then restart SQL Server.

Here are some helpful links;

Moving System Databases: http://support.microsoft.com/kb/224071
Using DAC: http://msdn.microsoft.com/en-us/library/ms178068.aspx
SQL Server Startup Options: http://msdn.microsoft.com/en-us/library/ms190737.aspx

First Post

2008-12-12T11:59:00.000-08:00

Hello! My name is Clay McDonald. I’ve been developing databases for 10 years now and thought it was about time to start sharing some of what I have learned. I’ll try to post at least once a week.