VSTSC's Community Server

Wait! SQL Agent Job

steve — Sun, 14 Mar 2010 18:43:00 GMT

This is the second in at least a two part series dealing with SQL Agent Jobs. In this post, we are creating a new procedure to be used instead of the system procedure sp_start_job when you need to wait for a SQL Agent Job prior to moving on to the next step in a program. You may need to do this if you have a procedure that calls a SQL Agent job, or a SQL Agent Job that calls a second SQL Agent Job or a separate job scheduler that is reaching over to a SQL Server to call a job, and I’m guessing there are other reasons, but these are the reasons I’ve come across.

To accomplish this waiting on a SQL Agent Job we utilize the procedure introduced in part I of this series get_job_status_sp. We use the built in sp_start_job procedure to call the SQL Agent Job as you normally would but we then use the get_job_status_sp to let us waitfor a job before returning control back to the calling processes. There are a few other system stored procedures that are not documented in Books Online, so once again if this kind of thing makes you squeamish I understand. There is some risk of using system procedures that are not documented because they could change any time without notice. Unfortunately if you need this functionally you don’t have a lot of choice. Outside of those items, this is basically just a while loop using the get_job_status_sp along with the waitfor command. It’s pretty simple and pretty straight forward so if you feel the need to modify it in the future it should be easy to do so.

One note of change to that procedure get_job_status_sp, I have moved it into a new schema “jobs” inside the util database instead of leaving it in the dbo schema. All of the new code is provided as well as the original on this website.

Here it is below:

(Also available without commentary here: 0004_utilis.jobs.get_job_status_sp.sql )

(You may also want the util db build script here: 0001_create_database_util.sql and the 0002_create_schemas_for_database_util.sql schema script)

(Finally the entire jobs schema will be kept up to date with each post at CompleteJobsSchema.zip)

use util

--======================--

--Logging some basic info

--======================--

print @@SERVERNAME

print SUSER_NAME()

print GETDATE()

:So the code above is just some standard logging information that I like to record when my scripts run incase I’m am writing the output to a log somewhere. This was something I saw Andy Leonard do in one of his presentations and I think it’s a good idea, so I’ve started to incorporate it in all my scripts. This will also be a good thing to have if you have an automated deployment environment or want to have one in the future. (Standard code disclaimer/blurb…sorry but I think it’s worth repeating)

-- ==Depending on your build enviomrent you may want to comment out the destructive drop below== --

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[jobs].[start_job]') AND type in (N'P', N'PC'))

DROP PROCEDURE [jobs].[start_job]

: Nothing special above except for if you do not want to script out your permissions every time you run this script you may want to comment it out so you don’t accidently remove permissions on this proc. When I run this script my account is either in the sysAdmin role or I script out the permissions in a separate file. (Standard code disclaimer/blurb…sorry but I think it’s worth repeating)

create proc jobs.start_job

@job_id UNIQUEIDENTIFIER = NULL

,@job_name SYSNAME= NULL

,@check_seconds int=59

: Notice that the procedure does not require either variable but it will need one; this is to be handled later in the script. Some people like to have the comments below listed right before the create object statement so it is recorded in the system tables along with the object but not in it. I like to have inside the object if I can. (Standard code disclaimer/blurb…sorry but I think it’s worth repeating)

-- =============================================== --

-- Author:Steve Fibich

-- Create date: 02/25/2010

-- Description: This procedure allows you to call

-- a SQL Agent Job and wait until it finishes. It

-- also waits for the called SQL Agent job to finish

-- if it is already running before it response to its

-- initial call. This procedure has 3 variables; First

-- is the job_id the second is job name only one of these

-- can be populated and one of these must be populated.

-- The third is a truly optional varaible that determines

-- the interval with which the procedure will sleep while

-- waiting for a called SQL Agent job to finish, this by

-- default is set to 59 seconds.

-- Value Description (From Books Online for SP_HELP_JOBS)

-- 0 Returns only those jobs that are not idle or suspended.

-- 1 Executing.

-- 2 Waiting for thread.

-- 3 Between retries.

-- 4 Idle.

-- 5 Suspended.

-- 7 Performing completion actions.

-- =============================================== --

BEGIN

SET NOCOUNT ON

:SET NOCOUNT ON, why bother returning information about rows affected, we aren’t affecting any rows and this information would provided any value.

if ((@job_id is null and @job_name is null) or (@job_id is not null and @job_name is not null))

BEGIN

RAISERROR(N'Either @job_id or @job_name is required.',10,1)

END

if @check_seconds >59

BEGIN

RAISERROR(N'@check_seconds must be less than 60. @check_seconds=%d',10,1,@check_seconds)

END

: Ok the max value that is allowed for the check seconds variable is 59, and the reason for this is twofold. First I think checking a job any longer than 60 seconds apart leaves a window that is two large, if I allowed the window to be 30 minutes or 23 hours this would allow this project to run for that long after the job had finished if the job just finished after the job status check. If you leave the value for check status at 59 seconds and the job runs for an hour you have only check that status 60 times, which doesn’t seem too long to me. Second, and this was one of the important reasons was changing a Integer to a string formatted for the wait command was just easier if it was less than 60. Maybe in a future enhancement I’ll change it to take minutes and hours but so far this has served me well.

IF ((@job_id IS NOT NULL) OR (@job_name IS NOT NULL))

BEGIN

EXECUTE msdb.dbo.sp_verify_job_identifiers

'@job_name'

,'@job_id'

,@job_name OUTPUT

,@job_id OUTPUT

END

:Calling sp_verify_job_identifiers gaurantees that we have a valid job id, it will over right our @job_id with a valid value if we only had a job_name and if we had a job_id it will overwrite it with the same value and populate the @job_name variable.

declare @job_status int

declare @len tinyint

declare @delay as char(8)

declare @check_seconds_vc char(2)

set @check_seconds_vc=cast(@check_seconds as char(2))

set @len=len(@check_seconds_vc)

set @delay = '00:00:00'

set @delay=stuff(@delay,9-@len,@len,@check_seconds_vc)

:Above is some logic to convert a integer value into a seconds value in the string format for the waitfor command. Long story short I’m stuffing the @check_seconds variable converted into a charater string into a preformated string ’00:00:00’ based on the length of the check seconds string.

-- ==Intial Job check== --

execute jobs.get_job_status_sp

@job_id=@job_id

,@job_status=@job_status output

WHILE @job_status not in (4,5)

BEGIN

waitfor delay @delay

execute jobs.get_job_status_sp

@job_id=@job_id

,@job_status=@job_status output

END

: This initial status check allows for this job to wait for a SQL Server Agent job if it is already running.

-- ==Job is called; wait loop == --

if @job_status in (4,5)

BEGIN

-- print 'pre-execute'

execute msdb.dbo.sp_start_job @job_id=@job_id

waitfor delay '00:00:00'

-- print 'post-execute'

execute jobs.get_job_status_sp

@job_id=@job_id

,@job_status=@job_status output

-- print 'status' + cast(@job_status as char(10))

WHILE @job_status not in (4,5)

BEGIN

waitfor delay @delay

execute jobs.get_job_status_sp

@job_id=@job_id

,@job_status=@job_status output

END

: Finally the meat of the procedure. After the initial check of the job status we know that SQL Agent job is not running. At this point we execute the required job using the system procedure sp_start_job and then we check the status, who knows it could be a very fast running job, or it could have failed. Note the waitfor delay ’00:00:00’ while this has a wait of nothing I found if I checked the job status right away with nothing else I could actually get the status before the job was started, but then a fraction of a second later it was running. This tells me that there is some delay between starting a job with sp_start_job and getting the correct information back from the xp_sqlAgent_enum_jobs extended stored procedure which then also introduces this delay into my get_job_status_sp. Also note that get_job_status_sp is now in the jobs schema. Then we wait if the job is running, which is what we expect. Then check the job status and continue this wait/check loop until the job is finished. That’s it that simple.

END --PROC

:TEST!:

: The code below tests the new procedure and insures it properly runs and waits for a SQL Agent job. This test assumes you created the SlowlyRunningJob in part I of this series.

use util

--======================--

--Logging some basic info

--======================--

print @@SERVERNAME

print SUSER_NAME()

print GETDATE()

execute msdb.dbo.sp_start_job @job_name='SlowlyRunningJob'

-- ==Failure after this section runs because the job is already running== --

execute jobs.start_job

@job_name='SlowlyRunningJob'

,@check_seconds=2

execute msdb.dbo.sp_start_job @job_name='SlowlyRunningJob'

-- ==Success after this section runs== --

If anyone knows how to accurately or more accurately get the status of a job without using xp_sql_agent_enum jobs please let me know.

SQL Server Agent Job Status

steve — Tue, 09 Mar 2010 17:45:00 GMT

I wanted to talk about SQL Server Agent Job's status, but before we get into that I want to talk about Utility databases. I've noticed over the years some of the DBA's and database developers have utility databases and some have a folder of commonly used scripts. It seems as if the more efficient ones have a utility database they can deploy in an environment or to individual servers in an environment and quickly have a set of useful tools up and running in an environment. Unfortunately I fall in to the latter category, where I just have a series of folders that have useful scripts that I have to hunt down and run when I remember, key there is remember that a problem I am facing I have already solved. I am going to make a utility database that I will carry with me from now on. This will be known as util, and hopefully this blog will be my repository for those scripts.

Now on to the main point of this entry, Job Statuses or more importantly how can you see the status of a job running in SQL Server Agent other than using the GUI. Well simple enough Microsoft offers a good procedure sp_help_job that will show you along with the status information a slew of other information, in multiple result sets. I’ll just go and capture those result sets in a table variable a select the status value that I am looking for.

Unfortunately its not that easy, you cannot insert results from a stored procedure into a temporary table or a table variable if that procedure returns multiple result sets that are different or if the first stored procedure nests other stored procedures in side it. In this case sp_help_jobs does both, insert into #tempTable/@varaibleTable exec sp_help_jobs will not work. So off to plan “B”, what does the sp_help_job do? In short a lot. In more detail you can use the use sp_helptext procedure in the msdb database to see what sp_help_jobs is doing. (I’ve learned a lot about SQL Server from sp_helptext!)

use msdb

exec sp_helptext sp_help_job

Now looking through all of that code you will find that it is calling sp_verify_jobs, you pass this procedure a job name or a job id and with the correct parameters and it will return those variables verified and filled in with the missing information. If you pass in job name, job name is verified and job_id will be passed out, and vice versa. I haven’t spent a lot of time investigating this job but it seems to work very well and is integrated into system stored procedures so I will take it at its face value.

use msdb

exec sp_helptext sp_verify_job_identifiers

As we continue to look into sp_help_job it looks as if the system procedure sp_get_composite_job_info is returning the result set that I need. It also returns multiple result sets so I need to dig into that procedure. Inside this procedure I come across an extended stored procedure xp_sqlAgent_enum_jobs, which is returning the information in a single result set that I am looking for. So with this in mind I start to write my own job information gathering proc.

Here it is below:

(Also available without commentary here: 0002_utilis.jobs.get_job_status_sp.sql )

(You may also want the util db build script here: 0001_create_database_util.sql)

use util

--======================--

--Logging some basic info

--======================--

print @@SERVERNAME

print SUSER_NAME()

print GETDATE()

-- ==Depending on your build enviomrent you may want to comment out the destructive drop below== --

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_job_status_sp]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[get_job_status_sp]

create procedure dbo.get_job_status_sp

@job_id UNIQUEIDENTIFIER = NULL

,@job_name SYSNAME = NULL

,@job_status INT OUTPUT

-- =============================================== --

-- Author:Steve Fibich

-- Create date: 02/25/2010

-- Description: This procedure returns the status

-- of a job and only this value. This information

-- is avilable from sp_help_job but the result

-- of that stored procedure can not be collected

-- into a variable or temp table.

-- This procedure is based heavily on sp_help_job

-- and sp_get_composite_job_info. The majority

-- of the code is coppied from those two stored procedures

-- Value Description (From Books Online for SP_HELP_JOBS)

-- 0 Returns only those jobs that are not idle or suspended.

-- 1 Executing.

-- 2 Waiting for thread.

-- 3 Between retries.

-- 4 Idle.

-- 5 Suspended.

-- 7 Performing completion actions.

-- =============================================== --

BEGIN

if ((@job_id is null and @job_name is null) or (@job_id is not null and @job_name is not null))

BEGIN

RAISERROR(N'Either @job_id or @job_name is required.',10,1)

END

:Above we are enforcing the caller to pass either job_id or job_name but not both, even though the procedure itself allows both variables to be null. The reason to not allow both is that I do not want to get conflicting information a job_id that does not correspond to a job name, if I got that what would be executed?

SET NOCOUNT ON

DECLARE @xp_results TABLE

(job_id UNIQUEIDENTIFIER NOT NULL

,last_run_date INT NOT NULL

,last_run_time INT NOT NULL

,next_run_date INT NOT NULL

,next_run_time INT NOT NULL

,next_run_schedule_id INT NOT NULL

,requested_to_run INT NOT NULL

,request_source INT NOT NULL

,request_source_id sysname COLLATE database_default NULL

,running INT NOT NULL

,current_step INT NOT NULL

,current_retry_attempt INT NOT NULL

,job_state INT NOT NULL) -- This is the value we care about!

:Above we are declaring a table variable to hold the values returned from the extended stored procedure.

DECLARE @can_see_all_running_jobs INT

DECLARE @retval INT

DECLARE @category_id INT

DECLARE @job_id_as_char VARCHAR(36)

DECLARE @res_valid_range NVARCHAR(200)

-- Remove any leading/trailing spaces from parameters (except @owner_login_name)

SELECT @job_name = LTRIM(RTRIM(@job_name))

-- Turn [nullable] empty string parameters into NULLs

IF (@job_name = N'')

SELECT @job_name = NULL

IF ((@job_id IS NOT NULL) OR (@job_name IS NOT NULL))

BEGIN

EXECUTE @retval = msdb.dbo.sp_verify_job_identifiers

'@job_name'

,'@job_id'

,@job_name OUTPUT

,@job_id OUTPUT

END

--select @job_id

SELECT @can_see_all_running_jobs = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

IF (@can_see_all_running_jobs = 0)

BEGIN

SELECT @can_see_all_running_jobs = ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 0)

END

:Above we are checking to see if the user has the appropriate permissions to access sqlAgent job information. This was being done in the system stored procedures, and it makes good sense, so I figured I should continue this security practice in my own proc.

DECLARE @job_owner sysname

SELECT @job_owner = SUSER_SNAME()

insert @xp_results

execute master.dbo.xp_sqlagent_enum_jobs

@can_see_all_running_jobs

,@job_owner

, @job_id

select @job_status=job_state from @xp_results

:Finally we call the xp_sqlAgent_enum jobs with the appriopriate parmaters and return the result set into the @xp_results table variable and assign the value for the job_state int othe @job_status variable to be outputed by this proc.

END

declare @job_status int

execute util.dbo.get_job_status_sp

@job_name='SlowlyRunningJob'

,@job_status =@job_status OUTPUT

select @job_status

:Lastly we have a go statement to separate out our procedure logic and call the procedure we just called with a job named slowlyRunnignJob. This job has the following code in it.

declare @counter int

set @counter =0

while @counter < 10

BEGIN

waitfor delay '00:0:01'

set @counter=@counter+1

END

(I did not include this as a file so if you want it grab it here and stick it into a sql job for testing.)

So you may be saying to yourself, well that’s great and now you can get a job status value into a varaible, but really what is the point. Did you just break apart system stored procedures and cherry pick the one value you want and stick into your proc for some very limited purpose. The answer is more or less yes, but the purpose is bigger than just to retrive that one value. The purpose is to allow you to check job status programtically and allow that programtic checking to do something with the status. This will allow us to create a SQL Server Agent job calling process that can wait for the job to stop prior to calling the next step, but this is the subject of another post.

My blog is moving!

timbenninghoff — Tue, 09 Feb 2010 03:10:00 GMT

Yup, you read the title right. My blog is going to be moving. For a number of different reasons it's time for me to strike out on my own, so I'm following Brent Ozar's excellent How To Start a Blog series to make that migration happen. One of those reasons is that my wife has given me a project of migrating her ftp-hosted Blogger blog over to WordPress. She's got scores of readers and I'd like to make that transition as seamless as possible for them. And, it's against my nature to make changes to production without having done some sort of test, so my migration of my own blog to WordPress will be a good test run that, while not identical, will give me some basis of understanding for when I take the leap with her blog.

I've also known that I have to move this year, and having this imminent change hanging over me was just another impediment to sitting down and getting my thoughts out, and getting to work on all of the other home tech projects I want to do this year. It's time for action, I sez.

I'll make a more official announcement once I'm ready to declare my new home, but it shouldn't be to hard to find the new home if you'd like to watch the messy metamorphosis.

Why Microsoft gets a bad name with Support (part 2 - no you cannot escalate)

brian — Wed, 27 Jan 2010 20:34:00 GMT

So apparently my request to have the issue promoted to the next level of support was denied, and once again, the answer is to tell the business owner (essentially) jump through hoops because a data issue can't be fixed. Let me point out another frustration, the over-politeness that feels almost belittling; like they're talking to a 5 year old or something. If you're 'truly sorry' then fix the problem! If this was Ray Ozzie or some other CXO at Microsoft I'm sure this would've been fixed by now.

---

Hello Brian,

Thank you for replying to us at Microsoft Office Live Workspace Support.

My name is Akash XXXXX and I have updated Service Request # 1121112775, which was created to track your concern.

I am aware of your concern that you are unable to share with a particular email ID. Also, you would like to hear personally from the Next level team and the team above. I realize the importance of your concern and I would provide you with the information.

Brian, I am sorry to inform you that, we will not be able to escalate the case further to Next level or the team above as we have received the update from our Next level team to tell you that the concern is with the particular email ID. Also, before providing you with the resolution we have done enough troubleshooting and tried to resolve the concern but found out that the problem is with the particular email ID.

Additionally, as an alternative I would suggest you to use another email ID to share workspace with the user so that you do not face any problem while sharing.

Once again, I truly apologize that we were not able to provide you with an experience that was completely satisfactory to you. While I realize that only our future interactions can restore your complete confidence in us, I want to assure you that your experience was unusual and is not indicative of the quality service Microsoft Office Live is capable of providing. Microsoft Office Live intention is to provide our customers with the best products and customer experience that is both satisfying and enjoyable.

For your convenience, our support staff is available 24 hours a day, 7 days a week. If you have additional questions, concerns or need further assistance, please be sure not to change the subject line while replying to this email so that we can urgently address any remaining concerns.

You may be selected to receive a survey. Your feedback is valuable to us as we are very interested in hearing about your support experience. Should you receive a survey, we appreciate you taking the time to respond.

Have a great day, Brian!

Regards,

Akash XXXX
Microsoft Office Live Workspace Support

Why Microsoft gets a bad name with Support

brian — Wed, 27 Jan 2010 13:27:00 GMT

First off, know that I love all things Microsoft. I believe they are a great company and are often put under a microscope and blamed for things because they are successful and many people out there just don't want that for various reasons.

But what I wanted to write about today is where they are truly dropping the ball and my hope is that someone here in American Microsoft will see this and route it through the proper channels so that not only is the problem addressed, but that whoever is in charge of overseas support gets the message that 'I am unable to help you' is an unacceptable answer here in America, its the equivalent of saying 'I am unwilling to help you' in my mind. An acceptable answer may be 'We can't help you at this time unless your willing to pay $X/hr or you can wait until 2nd quarter', but 'too bad' doesn't fly.

So if you haven't used it, Microsoft Office Live is great! Definitely check it out! It's a great way to get 'Share Point' like features for free along with a free web site that even my 7 year old daughter could design. I really mean that, and these just are not the cheesy 'front-page' web-sites you may be thinking about, these are professional looking sites that are extensible by developers such as ourselves but simple enough for the less than knowledgeable! Plus there are many other great tools/features in Office Live.

So here's the problem. The web site I made for our church turned out great and I had been selling the church staff on using the 'Workspace' feature to upload and share their documents with for collaboration; a hard sell because like most companies, Outlook is the CMS. Regardless, still got them going on it, but one of the pastors was unable to be added as an editor (or viewer) to any workspace. I started a trouble ticket and after several emails back and forth their answer was 'we can't fix it, sorry'.

Now besides the fact that the 'wording' of these emails is horrible, which I can get past because I know these are just Indian work farms of folks who can barely speak English and work off of a script, I can't get past that they don't seem to understand American work-ethic or the American way. There is no such thing as 'can not fix' for something that is obviously a data issue (since several other users don't have an issue). The last thing the owner of a company (or the equivalent in this case) want to hear is 'nope, sorry, can't fix, oh well, make another user name and password you'll need to track so you can do what you want to do'. America got to where she is by the 'can-do' people that have made it this great place; we don't accept anything less.

It's this type of 'I repro'd the problem, and you're right, it doesn't work, too bad' is what drives people to use Google equivalents. Not that their support is any better, but in many cases, all it takes is a single issue like this for people to feed into the herd mentality.

The other thing that bothered me was them resetting the passwords on two accounts without first giving us a heads up. Pastor will probably come in today unable to access their email or update the web site and not know why (since the alternate email is an email account they might not check until later today).

And let me be clear, I know this isn't the fault of Ranju, he's just doing his job and what he's told, the problem is with the policies and practices of the support team.

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

Hello Brian,

Thank you for writing back to Microsoft Office Live Workspace Support. My name is Ranju XXXXXX and I am working on the Service Request # 1121112775, which was created to track your concern related to sharing workspace with the email ID 'xyz@xyz.net'.

I have accessed your Microsoft Office Live Workspace account associated with 'xyz@xyz.net' (user having problem) as well as the account associated with 'xyz@xyz.net' (Office Live admin/owner account). I have checked all the features of these accounts. I found that there is a problem with the Microsoft Office Live Workspace account of the email ID 'xyz@xyz.net' which is the reason why you are unable to share it with the email ID 'xyz@xyz.net'.

We have investigated this problem along with our Next level team. Based on the information provided by our Next level team, I am sorry to inform you that, you will not be able to share your Workspace with this particular email ID 'xyz@xyz.net'. I would request you to use another email ID to share Workspace with the user.

We sincerely apologize for the inconvenience you are facing because of this issue. We appreciate your cooperation and patience as we moved ahead investigating the issue.

I am sending two reset password links of the sign in Windows Live IDs 'xyz@xyz.net' (user with problem) and 'xyz@xyz.net' (Office Live owner/administrator) to your alternate email address 'xyz@xyz.net'. Check the inbox and junk folder of the email account of 'xyz@xyz.net' and reset the password within 24 hours. The reset password link will expire after 24 hours.

Please note that the first reset email that you receive is for the Windows Live ID 'xyz@xyz.net'. Use the first reset password email for resetting the password of ' 'xyz@xyz.net'. The second reset password is for Windows Live ID 'xyz@xyz.net'. In addition, you can also reset the password of 'xyz@xyz.net' from your Microsoft Office Live Small Business account as well.

We appreciate your input and involvement in Microsoft Office Live Workspace service. Microsoft Office Live Workspace service is in its initial developing state (i.e. in Beta period) and there are lot of enhancements and applications/features yet to be introduced.

I would suggest you to submit a request as a feedback by visiting Feedback for Office Live Workspace Beta, so that it is considered when Microsoft Office Live Workspace upgrade takes place.

Please accept my apologies for not being able to assist you fully. Thank you for patience and understanding on this matter.

For your convenience, our support staff is available 24 hours a day, 7 days a week. If you have additional questions, concerns or need further assistance, please be sure not to change the subject line so that we can urgently address any remaining concerns.

Best Regards,

Ranju XXXX

Microsoft Office Live Workspace Support

PowerShell Function Gotcha

timbenninghoff — Wed, 27 Jan 2010 03:34:00 GMT

In a renewed effort to read one tech book a month in 2010, I've restarted Bruce Payette's Windows PowerShell In Action just as I've been using PowerShell to solve a problem at work. I was attempting to use a SendEmail function I found on a blog, and when I tried to call the function like this:

SendEmail("someaddress@someplace.com","Someaddress@someplace.com","This is a subject","This is the body of the email");

I got the following error:

Exception setting "From": "Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Net.Mail.MailAddress"."
At C:\PowerShell\SendEmailTest.ps1:15 char:10
+     $msg. <<<< From = $From
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

Exception calling "Add" with "1" argument(s): "The parameter 'addresses' cannot be an empty string.
Parameter name: addresses"
At C:\PowerShell\SendEmailTest.ps1:26 char:20
+         $msg.To.Add <<<< ($singleAddress)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "Send" with "1" argument(s): "A from address must be specified."
At C:\PowerShell\SendEmailTest.ps1:56 char:17
+     $client.Send <<<< ($msg)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

The parameter 'addresses' is an empty string? How is that possible? I clearly entered someaddress@someplace.com which is most definitely not an empty string. Fortunately, I read about this PowerShell function gotcha in Mr. Payette's book on page 183, where goes on to describe things like PowerShell looking at the contents of parentheses as an expression and very smart people being tripped up by this.

To make a long story short, I should have called the function with the arguments separated by spaces, like:

SendEmail "someaddress@someplace.com" "Someaddress@someplace.com" "This is a subject" "This is the body of the email";

I'll take this opportunity to plug the book again. It's little pieces of advice like this that have saved me a ton of time as I've learned PowerShell.

2010 Professional Goals

timbenninghoff — Tue, 19 Jan 2010 02:26:00 GMT

Time to make my professional goals again for the new year. In conjunction with listing my new goals, I'll also talk about some of the goals for last year and how I fared.

Certification: Pass exam 70-453 in order to earn my MCITP: Database Administrator 2008. Bonus Goal: Pick another certification and take at least one exam for it. Server Admin? BI Developer? Database Developer??

Last year: I reached my goal of getting my MCITP: Database Administrator certification.

Blogging: Make at least one blog post a week. Move blog to a new site.

Last year: Didn't quite make the one blog post a week. I'm hoping to do better this year, starting now. It's only 52 posts, right?

Reading: One technology book a month.

Last year: Man, didn't hit this one either. Uh oh, I don't like where this review of last year is headed...

Technology: Focus on learning Powershell, SSIS, and SSAS.

Last year: Undefineable goal, but my early enthusiasm for Powershell really kind of died out as I didn't have any places to apply it at work. 2010 is starting out very differently, and I've already developed some automation scripts with Powershell for work, and I'm starting to recommend it for certain things to our developers as well.

Scripting: Write 1 script a week for my toolbox.

Last year: Tried this goal, and, by my count, I ended up with 74 new scripts, in both T-SQL and Powershell, that make my job easier. Hurray! I got one.

Learning: Attend 6 of the monthly local SQL Server user group meetings this year. Attend PASS Summit.

Last year: Yup, I did manage to attend my goal of 1 user group meeting last year, and I also attended the PASS Summit.

Volunteer: Donate some of my time to PASS, or to my local SQL Server User Group. I'm already slated to volunteer at SQL Saturday 31 in Chicago.

Last year: Didn't make it a goal, so this one's a new one.

How do we use SQL Server?

timbenninghoff — Thu, 10 Dec 2009 01:59:00 GMT

I was tagged in a recent post by Jeremiah Peschka asking how we use SQL Server. Jeremiah has uttered that words that compel me to reveal my true identity, despite the great risk I take in doing so. Fear not, for the true consciousness of the one who identifies himself as Tim Benninghoff is safe, and, due to his cooperative nature, has been granted access to our vast Yithian libraries to occupy his time while I borrow his body.

This SQL Server, as far as I have been able to discover, is a data manipulation system of great power, but fraught with great danger for the uninitiated. In the current organizational construct to which I have attached this Tim form in order to learn more about this SQL Server, I have found that it is primarily used as the platform by which Independent Software Vendors, under the ruse of providing something called 'solutions', unleash their torment upon the guardians of information of humanity, which your age has called DBAs. Some of these 'ISVs' do seem to have in their possession a true, complete, unaltered copy of the manuscript known as Books Online, and access to the further writings of SQL Server's technological priesthood, known as MVPs, but I find that the vast majority of these ISVs seem to have based their machinations on apocryphal, if not outright heretical, knowledge compilations of SQL Server. In a query to the Tim consciousness, he suggests that ignorance drives their decisions. From the discomfort and distractions they inflict upon me in this Tim form, I am forced to disagree with him and attribute their actions to malice of the darkest and most sinister nature. Perhaps DBAs will one day unlock the secrets of time, and be able to transport their consciousness in order to escape the ISVs, much as we have done to escape our destruction at the hands of the flying polyps.

Another thought framework, which I have heard referred to as the Frame of the Main, seems to perform most of the data support for this organizational construct, also referred to as a company by the other humans amongst whom I walk. However, I have noticed that a transition of certain functions away from the Frame of the Main to SQL Server seems to be under way. This transition has been a difficult one, as former practitioners of the COBOL pictograms move their domain knowledge into the much revered relational storage archetype of SQL Server. Of note, an electronic library exists, referred to by the humans here as the Data Warehouse, that collects information from this Frame of the Main and transfers it to the Data Warehouse. The curious and remarkable thing is that the SQL Agent is the primary mechanism by which this data movement, known as ETL, is performed. These SQL practitioners have found ways to apply the power of the SQL Agent that I had not thought possible!

As if the onslaught of the SQL Server ISVs were not enough, a great ravening beast, fearfully referred to as The Oracle, appears to be hatching some arcane and nefarious plan to increase its dominion over the company, employing its own ISVs to champion its cause. The Oracle's plan seems to be working, as even now this Tim form is being instructed to assist in transporting data to the systems in The Oracle's web of influence.

All is not gloom for me in this Tim form, as we have begun the installation of the latest universal understanding, SQL Server 2008, and have nearly retired all instances of the reliable, but ancient in the information techology timeframe, SQL Server 2000. I look for ways to employ some of its new features such as PowerShell support, Central Management Server, Policy Based Management, and Management Data Warehouse to the greater glory of the company.

It is time that I continue this meme, and so I call upon the entities known as Allen Kinsel and KenDrah to answer the question, How do YOU use SQL Server? I instruct you to REVEAL ALL!

Hey man, what the heck are you doing? What is all this....omg. This is how you repay me for being totally cool in the library of Yith? NO! Don't hit publish!

Blank Page for SQL Server 2008 Reporting Services Report Manager

timbenninghoff — Wed, 11 Nov 2009 02:27:00 GMT

Recently when I tried to access Reporting Services' Report Manager for a SQL Server 2008 Developer Edition SP1 instance on a Windows 2008 R2 Server, I got a completely blank screen. It was not a mostly blank screen still showing the header, nor was I getting the blank page after being prompted for my user credentials three times. No, the page was loading immediately and was completely blank. I checked the Windows logs, the SQL Server logs, and the Reporting Services log, and no errors were being logged.

Fortunately I knew that the security department had recently made changes to that instance to enable Kerberos authentication in order to support Kerberos delegation for a web app that connected to our SQL Server and Reporting Services. They'd dutifully documented all of the changes, and so since I didn't have any error logs to go on, I knew this was the only change that had happened recently and likely had to be the culprit.

We used the same domain account to run both the SQL Server service and the Reporting Services in order to make our lives a tad bit easier, and I double checked that all of the SPNs were registered properly for the web server, SQL Server, and Reporting Services. Figuring it had to be something related to the Reporting Services configuration, I noticed that they'd changed a setting in the RSReportServer.config file, located at C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer in a default install, and noticed they'd changed <AuthenticationTypes> to <RSWindowsKerberos/>. I wanted to accept Kerberos or NTLM tokens, so BOL pointed me in the direction of using <RSWindowsNegotiate/>. But, despite BOL suggesting that this setting would be omitted if I were running in native mode and using a domain user for the service, I caved to my rebellious streak, removed <RSWindowsKerberos/>, replaced it with <RSWindowsNegotiate/> and hoped for the best. It worked! The web app continued to work via Kerberos authentication/delegation, and I was able to access my Report Manager again.

I'm sure there's a moral to this story somewhere. Mainly, though, I'm just very pleased that the security department and the consultant they had helping them were so diligent in documenting their changes. I probably owe them a cookie or something.

I'm a square.

timbenninghoff — Thu, 22 Oct 2009 01:07:00 GMT

PASS Summit 2009 is rapidly approaching, and one of the features of the conference is the chance to meet new people who also work with SQL Server. In order to help attendees meet new people, some folks have put together SQL PASS Twitter Bingo. I've volunteered to be one of the squares on the randomly-generated bingo cards, and I'm really looking forward to meeting lots of attendees.

Now, you've read the proposed rules and seen the sample cards and you're probably wondering how, in the name of all six-legged beings, you are going to find a handful of people at a conference attended by a couple thousand people. And to make matters worse, how are you going to find me, since my Twitter avatar isn't a picture of me?

Well, the goal of the game is to meet people, not be a detective, so I'm going to try to make myself more conspicuous by doing the following:

1) Publish my agenda on my Google calendar - The sessions I plan on attending at the conference will be public, so feel free to look up my calendar to find out where I'm going to be.

2) Twitter - I'll be tweeting my locations under my nom de plume, Bugboi. Sessions may end up being full, or I may change my mind, and my calendar may not reflect where I actually am. Twitter updates will point you in the right direction.

3) Bugs - Each day I'll be wearing a different insect-themed t-shirt, potentially paired with a blazer or jacket if it's cold. Look for the bugs and that will probably be me, unless, of course, there is some other totally awesome person there who likes insects there at the conference.

But, what if after all that effort you still can't find me? Well, if you've found one person on your card, you might ask them if they know anyone else on the card, and they just might be able to point you in my direction. Or the helpful folks staffing the Quest and PASS booths might be able to help you get started as well.

Good luck, and I hope to see you at the Summit!

Design Anti-Pattern: Orphans by Design

andy — Wed, 19 Aug 2009 22:00:00 GMT

Introduction

It's a surprisingly easy thing to do: create orphans by design in a database. In this post I present one such example.

Relationship First

First you need a a relationship between two tables. In this example, we'll start with a parent-child relationship.

Looking at this diagram, you can infer a relationship between Parent and Child. The likely candidate key is ParentID. But - and this is a big butt (typo intentional) - there is no relationship present in the database. If there was a relationship in the database, the diagram would appear as shown:

This is called referential integrity in database-speak. It's important because it defines and maintains this relationship between parent and child records. It prevents "orphans" - rows in the child table that do not have related rows in the parent.

"So?"

"What's the big deal, Andy?" I'm glad you asked.

Let's look at the downside of maintaining a relationship while developing against a couple tables with referential integrity applied:

You have to write inserts a certain way. You cannot, for example, just poke a row into Child. It has to have a matching row in Parent. The value you insert into the Child.ParentID column must have a matching value in the Parent.ParentID column. If it doesn't already exist, you have to insert a row into Parent before you do the insert into Child.
You have to do deletes in a certain way. Similar to inserts, you cannot just delete a row willy-nilly from the Parent table. It may have related rows in the Child table, and those rows would be "orphaned" if you delete the parent.

Now let's look at the upside of maintaining a relationship while developing against a couple tables with referential integrity applied:

Your code will be built against a production-ready version of the database. Dropping keys may make your inserts work while developing, but it will cost you later. Truth is, you may develop bad code - and lots of it - before you realize the scope of the damage.
Persistence is the art of state and data lends itself to certain designs. One key of good database design is: Identify the relationships and build the tables around them. It will be easier to design, build, maintain, load, and ultimately use.
These aren't just helpful suggestions, well-designed databases perform and scale better; and well-designed databases are designed with referential integrity.

Constraints In Action

If you attempt to insert a row into the Child table without a related Parent table row, an error is raised. For example, if you attempt to execute this statement:

insert into Child

(ParentID, ChildName, ChildValue)

Values(1, 'A', 12)

an error similar to the following is raised:

Msg 547, Level 16, State 0, Line 2

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Child_Parent". The conflict occurred in database "TestDB", table "dbo.Parent", column 'ParentID'.

Defeating Constraints

It's possible to defeat constraints in several ways. I'm not going to exhaust all the ways you can do this here. I'm just going to advise that you not do it... unless you are absolutely certain you understand the implications of this action.

The Anti-Pattern

The anti-pattern is shown in the first image above: there's a relationship, but it's managed elsewhere. There are occassions when the right design involves managing the relationship in another location and not in the database. Those occassions are rare.

:{> Andy

Andy's Active August - Day 11

andy — Tue, 11 Aug 2009 23:14:00 GMT

My #ActiveAugust goals have hit a snag. I'm injured. I've learned the hard way that I need to be careful, and I'll likely be sidelined for a few days healing. Durnit!

AndysActiveAugust Metric: 10 for 11, or 0.91.

:{<

Andy's Active August - Days 9 and 10

andy — Tue, 11 Aug 2009 03:14:00 GMT

Sundays are the nemesis of my #ActiveAugust goals. It's a busy day with church and usually some sort of family activites thereafter. Last Sunday, 2 Aug, was the only day thus far I hadn't exercised. So this Sunday I was more than a little concerned.

Christy wasn't feeling well, so we didn't get to church. Since Christy hadn't slept well the night before, a long summer's nap was in order. We kept Riley up a little longer than usual and talked Emma into laying down for her nap with Christy and Riley; and Stevie Ray and I made for the gym.

For the third day in a row, I jogged a mile without stopping. Then I did 75 reps with 100 pounds. And then, when I stopped by the child care room, Stevie Ray was in the middle of a fierce game of UNO, so I returned to the gym and did 1/2 mile on the elliptical.

Take that, Sunday!

This morning I went to gym around 6:00 AM and jogged my mile on the treadmill - again non-stop. Four in a row.

Thursday and Friday are going to be tough this week, as I'm working from Richmond Thursday and presenting in Richmond Thursday night. We'll see how it goes...

AndysActiveAugust Metric: 9 for 10, or 0.9.

:{>

Andy's Active August - Days 7 and 8

andy — Sat, 08 Aug 2009 23:21:00 GMT

It's been a couple days since I last blogged. In between a couple long days at work, installing Windows 7 on the Red XPS laptop, and Riley's birthday cookout; I managed to exercise both days. My #ActiveAugust goals revolve around making exercise a habit. I want to work up to a challenging (for me) routine. My thinking is: If I can do this for a month (or most of a month) I can continue.

To review, my goals are: Before #ActiveAugust ends I want to be able, in a single workout:

Jog 1 mile on the treadmill without taking a walk-break;
Do 200 reps with 100 pounds.

Yesterday morning I hit half of my two-part goal. I ran a continuous mile on the treadmill without taking a walk-break. I was overjoyed, but I have to share I thought it was a fluke. And since running on the treadmill is easier for me than running ITRW (in the real world), my test was to try running to Worsham store and back.

The terrain from my driveway to Worsham store is challenging. I start off going downhill for a good bit, then up a steep short hill. I go down a little and up a gentle slope to Route 15, then back the way I came. The end for me is a decent upslope for the last tenth of a mile or so. That's what I mean by challenging.

I decided to jog it this afternoon, on a muggy dog day of August. And I made it!

Now if you saw me "jogging" you'd laugh. I don't run, I shuffle. But I shuffled a mile without a break two days straight. Steve Jones' record isn't in danger, but I feel like a million bucks (well, a sore million bucks). It's time to start working on getting those reps up, while maintaining a mile a day.

AndysActiveAugust Metric: 7 for 8, or 0.875.

:{> Andy

Andy's Active August - Day 6

andy — Fri, 07 Aug 2009 00:52:00 GMT

Today was also close. After staying up too late last night due to work (and being old - old people don't need sleep), my #ActiveAugust aspirations were nearly dashed by sleeping in until after 6:00 AM again for the second day in a row. In addition, it rained here most of last night - making it too wet to walk here near the house like yesterday.

Technically, I could walk up the road to Worsham store just fine. It's when I meet a vehicle that it gets dicey. The Commonwealth of Virginia has decided to balance the budget by never mowing Abilene Road again. The weeds are about waist-high, even a foot off the road. I don't mind high weeds. I do mind high wet weeds.

"Pedestrians have the right of way" I hear you thinking. Folks, this is Farmville Virginia. We don't so much rely the law of the land around here as much as we rely on the laws of physics. So when I say "vehicle," I mean "the two tons of rolling steel." In other words, I need to get off the road when a car approaches.

As if that wasn't enough, I had enough work for three mornings queued up. What to do, what to do?

I grabbed my gear and made for the gym. Since I was running late, I decided jog as much as I could and skip the weights today. I wasn't running fast, but I managed to jog 0.6 miles continuously on the treadmill at 4.5 mph. I ran probably another 0.2 miles near the end of the workout - sprinting 1/10th of a mile at 6 mph. I knocked out 20 minutes of cardio, cooled down, and returned home to start the business day.

AndysActiveAugust Metric: 5 for 6, or 0.833.

:{>

Andy's Active August - Day 5

andy — Thu, 06 Aug 2009 01:04:00 GMT

Today was close. After being inspired by Tim Benninghoff's post about Dynamic SQL (and writing one of my own), I stayed up too late last night. The alarm is set for 5:30 AM to give me enough time to get up and stirring before heading to the gym at 6:00. I hit snooze a few times and didn't get up until about 6:30.

That didn't leave enough time to make the gym and get back in time for the start of my work day. I thought about it for a bit, then threw on my gym shoes and headed outside. It was a balmy 68 or so out this morning and as humid as Virginia gets in August. It didn't matter. I was on a mission.

I walked from the old farmhouse up to Worsham store - about 1/2 mile. On the way back I detoured down a farm road for an addition 0.2 miles or so. Then I went past our driveway on the return trip for another 0.3 miles or so. I guestimate I walked 1.5 miles.

AndysActiveAugust Metric: 4 for 5, or 0.8.

:{>

Dynamic T-SQL Today

andy — Wed, 05 Aug 2009 03:09:00 GMT

I was just reading Tim Benninghoff's latest post: Dynamic SQL is my administration buddy and it got me thinking (Tim's posts often get me thinking) about how much I use dynamic SQL each day.

Just today I needed to create a test query to count the rows in every table in a database, then create another query to truncate each table in the same database. To prove the truncation had occurred, I would need to execute the test script before and after the truncate script. There were several tables in the database.

So I built the following T-SQL script to build the counts query:

select 'select' + char(13) + char(10)
union all
select
'(select count(*) from ' + schema_name(schema_id) +
'.' + name + ') as ' + name + '_RecCount,'
from sys.tables

This output of this query resembled the following:

select
(select count(*) from dbo.incr) as incr_RecCount,
(select count(*) from dbo.PKTest) as PKTest_RecCount

(Except there were lots more tables) This was cool - it created a single row containing counts of all the tables in the database.

The truncate script read:

select
'truncate table ' + schema_name(schema_id) + '.' + name
from sys.tables

And the generated output reads:

truncate table dbo.incr
truncate table dbo.PKTest

Pretty cool. It took way less time to write the T-SQL generator code than it would have taken to type this out.

I ran the counts script, saved the results in Excel, executed the truncates, re-ran the counts script, and pasted those results into Excel below the previous results. A quick visual confirmed the truncates worked, and I have a handy artifact to attach (along with the generated scripts) to the ticket.

:{> Andy

Andy's Active August - Day 4

andy — Wed, 05 Aug 2009 02:13:00 GMT

I did something today I haven't done in weeks - I made it to the gym for the second day in a row, Maybe it's all psychosomatic (or just plain psycho), but I felt better today.

I did 1.5 miles on the treadmill - enough to get a minimum good cardio workout for 20 minutes - and then started on the weights. In keeping with not hurting myself and ramping up slowly, I only did 20 reps with 100 pounds.

I'm a little late setting goals but here they are. Before #ActiveAugust ends I want to be able, in a single workout:

Jog 1 mile on the treadmill without taking a walk-break;
Do 200 reps with 100 pounds.

I realize this isn't that much for most folks. It's huge for me.

AndysActiveAugust Metric: 3 for 4, or .75.

:{> Andy

Dynamic SQL is my administration buddy.

timbenninghoff — Tue, 04 Aug 2009 02:28:00 GMT

In the age of SQL injection attacks, the dangers of dynamic SQL are pretty well documented. But, as with most things, there's a time and a place for them. I recently indulged in a trip down memory lane, reviewing the scripts in my 'toolbox' that I use for administration, and I was reminded of how much I use dynamic SQL for my day-to-day administrative tasks. For many, this blog post isn't going to break any new ground. Consider it an homage to my favorite aspect of dynamic sql in t-sql; the ability to do administrative tasks en masse.

For example, at work we use SQL Agent extensively for plenty of administrative tasks. In those rare instance when one of those jobs should break, we like to know what happened in as much detail as possible. So, we use SQL Agent's step history to log that information to a text file and email the log file to us. But, when I'm building a large job, going into the Advanced page of the Job Step properties through SSMS and defining the output file for each step can get rather tedious.

Enter dynamic SQL. After I've built the job, I open up a Query window, and build a quick script like this:

DECLARE @sql varchar(max);

SET @sql = '';

SELECT @sql = @sql + 'EXEC msdb.dbo.sp_update_jobstep @job_id='''+CAST(job.job_id AS varchar(max))+''',@step_id='+CAST(step.step_id as varchar(10))+',@output_file_name=''G:\JobLogs\<jobname>.log'',@flags=6'
FROM msdb.dbo.sysjobs AS job
JOIN msdb.dbo.sysjobsteps AS step
ON job.job_id=step.job_id
WHERE job.name = '<jobname>'
AND step.step_id > 1;

EXEC(@sql);

As you can see, using the information from the SQL Agent system tables stored in msdb, I build a variable to string together executions of sp_update_jobstep in order to update all of the steps in my job, except the first one, to use the same log file, to append the step's history to that log file, and to also include the step information into SQL Agent's history, and then I execute that statement, which modifies all of the steps at the same time.

In jobs that have 10s, if not a 100 steps, I've saved myself quite a bit of time from having to go through the GUI.

Now,this certainly isn't the only way I could have done this task. I could also have scripted out the job through SSMS, done a Find/Replace to change the values for @flags and @output_file_name. but, since I'm weird and I also want my first step to NOT append to the log file because I want the file to be 'new' every time the job executes, I'd have to then go back and search through the code to find the first step and change the @flags value. Then, in SQL Server 2005, I'd have to drop the job and run the script to recreate. But, no, I think I like dynamic SQL just fine.

Andy's Active August - Days 2 and 3

andy — Mon, 03 Aug 2009 21:30:00 GMT

As suspected, Sunday proved to be a challenge to my plans for an #ActiveAugust. But this morning - Monday, 3 Aug 2009, which also happens to be Riley's 2nd birthday (Happy Birthday Boof!) - I dragged my tired self out of bed earlier than I had been and hit the gym. I did 25 minutes total on the treadmill - barely qualifying as a useful workout.

I didn't touch the weights - maybe tomorrow. I was telling my marketing and project manager friends (ok, I have one project manager who hasn't blocked me and a former marketing person as a friend) that I was lifting 10,000 pounds! That's 10 sets of 10 reps with 100 pounds. Oddly, they both got the math. Right away.

It had a been a while since I worked out. I have this theory that entropy accelerates with age. Exhibit A: I don't think I'd been to the gym since before my last birthday.

I'm sore. But I think that's part of it - I'm sore with a bunch of other #ActiveAugust tweeps. We're sore together - isn't that cool? Tomorrow I hope to make it two days in a row - for the first time in more than a month. Economic analysis: July's payment to the YMCA was profitable for them and expensive for me. Durnit!

On a side note - check out @sqlagentman's funny blog post about his (in)ActiveAugust!

AndysActiveAugust Metric: 2 for 3, or .666.

:{> Andy

Andy's Active August - Day 1

andy — Sun, 02 Aug 2009 03:59:00 GMT

1 Aug 2009: Today's cardio was mowing.

About half of the three acres we (and the mortgage company) own is covered with grass - the kind that insists on growing. It's been a couple weeks since I gave it all a good mow. My neighbors were shaming me with their mowed lawns, and I'm a sucker for peer pressure (as I mentioned in my previous post).

We own a riding mower. Christy found it three years ago when we bought the house. It was used and needed a little love. But it was only $275 for a 42" tri-blade deck, 18HP Briggs and Stratton engine. And it was running. Most of our yard lends itself to riding mower mowing. The hill out front does not.

For the hill, I use a push mower. It's one that bags the grass, so it actually gets heavier as I go. Mowing the hill takes about 30 minutes. It's a decent cardio workout - as good or better as I get at the gym. The 90 DegF heat and humidity added to the fun.

So Day One is in the books. Tomorrow will be tricky. I don't usually go to the gym on Sundays, and I've already mowed the grass. I'll need to be creative.

:{> Andy

Andy's Active August - Intro

andy — Sun, 02 Aug 2009 03:15:00 GMT

I'm going to participate in #ActiveAugust for a couple reasons:

A bunch of my Twitter friends are doing it.
I really need to be more active.

My plans are to go cardio each day. For me, doing something that gets my heart rate into workout range every day for 31 days would be astounding - and very good for me. Most studies say it takes 21 days to form a habit. This has the engineer's allure of 10 redundant days!

I know the value of inspiration in physical activity. I was never good at sports in school - almost always picked last for anything that required speed or physical coordination (not looking for sympathy, just sharing). As I told my oldest daughter earlier tonight: When she was her daughter's (my granddaughter's) age, I was in Basic Training at Fort McClellan, AL. (Note to those considering enlisting: Go NORTH if you're going to do Basic Training in the summer months!) I remember thinking "I'll never make this. I can't run." But I could run. The first morning we got up early (well, "early" if you didn't grow up on a farm) and we ran around a block - probably not more than 1/10th of a mile. We ran in formation. We were in step and maintained a distance from each other. We chanted while we ran. There was a palpable sense of camaraderie. One day near the end of boot camp, we ran about 6 miles. In August. In Alabama. (See previous note...)

I've been inspired by the people in our SQL Server community: Tim Ford, Donald Farmer, Thomas LaRock, T. Jay Belt, Paul Randal, Chuck Boyce, Kimberly Tripp, Matt Masson, Denny Cherry, Lara Rubbelke, John Welch, Paul Waters, Jessica Moss, Louis Davidson, Brian Kelley, Brent Ozar, Mike Walsh, Joe Webb, Grant Fritchey, Jorge Segarra, everyone in the Richmond Developer Comunity, and so many others (apologies, I know I left people out!). They share their expertise on Twitter, but they share so much more - their lives. I'm so blessed to be part of this community, and humbled by their acceptance. I no longer feel like the last kid chosen.

It's time. Maybe past time. I've been inspired long enough - it's time to act on the inspiration. So I'm in.

:{> Andy

PS - Steve Jones encouraged me to have a separate blog for personal stuff. Thanks Steve!

WeatherData: Create the Database

andy — Thu, 30 Jul 2009 02:01:00 GMT

Introduction

This is the first post in a series called WeatherData. In this series I am going to demonstrate principles of test-driven database development (TD3).

Build the Solution

Did you know you can use SSMS to build a database project? You can. I'm going to use SSMS 2005. To follow along, open SSMS and click File > New > Project:

When the New Project form displays, select the SQL Server Scripts template in the uppoer pane:

In the lower portion of the New Project form, name the project WeatherData:

When you click Ok, the database project is created. To view the Solution Explorer, click View > Solution Explorer:

Solution Explorer, for our empty WeatherData solution, looks like this:

Write the Test - Part 1: Create the Query File

Test-First Development is a popular variantof Test-Driven Development. You write and execute a test first, execute it, and it fails. This works very well for me, as most of my initial development efforts fail anyway! ;)

The first thing we'll need to do is create the database, so our first test will check for the database.

To create the test, right-click the Queries folder and click New Query:

When you create the first query, you'll be prompted to connect to a database:

Once you connect, the connection is added to the Connections folder:

Before we actually write the test, let's give SQLQuery1.sql a more descriptive name. I'd really like to be able to create a subfolder here and name it Tests. Alas, I cannot. So I am constrained to separate types of query files by their name. In this case, I choose to identify test queries by prefixing them with the text: "Test_".

Right-click SQLQuery1.sql and click Rename:

I chose to name my test "Test_CreateWeatherData.db.sql":

I like the file naming convention employed by Visual Studio Team System Database Edition. It's [ScriptName].[ObjectType].sql; so I use it here.

Write the Test - Part 2: Write Some Test T-Sql

It's important to stop here and talk some about test theory.

Let's talk first about test results. How will you know the test succeeded or failed? Do you want to return results that can be stored for posterity? If so, how? We'll start with test results that are only available at test time. We are finally ready to actually write some T-Sql!

The first test will be simple:

Use WeatherData

When this test is executed in SSMS, the result is an error message:

Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'WeatherData'. No entry found with that name. Make sure that the name is entered correctly.

The test failed, which is what we wanted, so Success! (Are you confused yet?)

Error Trapping

Another way to approach this test is to trap the error in a way that doesn't cause an error condition to occur. I can hear you asking: "Andy why is it important to not cause an error condition?" I'm glad you asked! Exceptions in the T-Sql will stop the T-Sql execution. The technical term for that is "bad." If the test code doesn't complete, our test results will be inconclusive.

Inconclusive is a valid test result. It's pretty obvious tests can either succeed or fail. Based on this, most assume there are two possible outcomes to a test: success or failure. There are actually four states to two-state logic:

On (Success in testing)

Off (Failure)

Don't Know (Inconclusive)

Don't Care (Ignored)

In SQL Server 2005 and beyond, T-Sql provides just the trick for this: Try/Catch. Also included are a couple functions to check for the expected error: Error_Number() and Error_Message(). Let's test this by executing the following T-Sql:

Begin Try
Select Convert(int, 'One')
End Try
Begin Catch
Print Error_Number()
Print Error_Message()
End Catch

The results are:

-----------
(0 row(s) affected)
245
Conversion failed when converting the varchar value 'One' to data type int.

The cool part is the query completed successfully - without error:

Let's apply this to our database test query:

Begin Try
Use WeatherData
End Try
Begin Catch
Print Error_Number()
Print Error_Message()
End Catch

The results are the same as before:

Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'WeatherData'. No entry found with that name. Make sure that the name is entered correctly.

What happened? Some errors are not trapped by Try/Catch (see Books Online TRY...CATCH topic, Errors Unaffected by a TRY...CATCH Construct section). One category or error unaffected by Try/Catch is "Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution."

Let's TRY Again

It's obvious we need another approach, Try/Catch just won't help here. We can check master.sys.databases for the WeatherData database using the following query:

Use master
Select name
From sys.databases
Where name = 'WeatherData'

This is a better test. We want to test for the existence of the database on this instance of SQL Server, and this does just that. Let's wrap it in a conditional statement that reports the results of the test:

Use master
If Exists(Select name
From sys.databases
Where name = 'WeatherData')
Print 'Success: WeatherData exists.'
Else
Print 'Failure: WeatherData does not exist.'

Executing this script reports the result:

Failure: WeatherData does not exist.

Again, the test returns a Failure result, which is exactly what we want. I like this test. Let's keep it.

Next Step: Create the Database

Next, let's write a re-executable T-Sql statement to create the database. I like the test code so much, I'm going to re-use it to make the Create Database statement re-executable:

If Not Exists(Select name
From sys.databases
Where name = 'WeatherData')
Create Database WeatherData
go

Re-Test

Finally, we'll re-execute the test after creating the database. Why? Having the test fail is the first step in Test-Driven Database Development. After we meet the condition under test (or attempt to meet it), we need to re-execute the test to determine if we have accomplished the task under test.

When complete, my test-driven T-Sql looks like this:

Use master
If Exists(Select name
From sys.databases
Where name = 'WeatherData')
Print 'Success: WeatherData exists.'
Else
Print 'Failure: WeatherData does not exist.'

If Not Exists(Select name
From sys.databases
Where name = 'WeatherData')
Create Database WeatherData
go

Use master
If Exists(Select name
From sys.databases
Where name = 'WeatherData')
Print 'Success: WeatherData exists.'
Else
Print 'Failure: WeatherData does not exist.'

This returns the following results:

Failure: WeatherData does not exist.
Success: WeatherData exists.

Cool.

Kicking It Up Another Notch

This is good, but we can make it even better.

First, save the test query alone as the file Test_CreateWeatherData.db.sql:

Use master
If Exists(Select name
From sys.databases
Where name = 'WeatherData')
Print 'Success: WeatherData exists.'
Else
Print 'Failure: WeatherData does not exist.'

Next, create a new query named CreateWeatherData.db.sql in Solution Explorer with the following T-Sql statements:

Use master
go

Print ''
Print 'Create WeatherData database'

If Not Exists(Select name
From sys.databases
Where name = 'WeatherData')
begin
Print ' > Creating WeatherData database.'
Create Database WeatherData
end
Else
Print ' > WeatherData database exists.'

Save the CreateWeatherData.db.sql script.

Finally, create one more script in the database project named DeployV1.ver.sql containing the following T-Sql statements:

/*

Notes:

1. Turn on SQLCMD mode.

2. If you receive an error like "Incorrect syntax near ':'",
see Note 1.

3. Correct the file paths, replacing the default
with the full path to your files.

*/

Print ''
Print 'Deploying WeatherData v1.'

Print ''
Print 'Calling Test_CreateWeatherData.db.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateWeatherData.db.sql"
Print 'Test_CreateWeatherData.db.sql called.'

Print ''
Print 'Calling CreateWeatherData.db.sql...'
:r "C:\Projects\WeatherData\WeatherData\CreateWeatherData.db.sql"
Print 'CreateWeatherData.db.sql called.'

Print ''
Print 'Calling Test_CreateWeatherData.db.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateWeatherData.db.sql"
Print 'Test_CreateWeatherData.db.sql called.'

Print ''
Print 'WeatherData v1 deployed.'

Before executing this script, drop the WeatherData database (if you've created it). Also, follow the instructions in the comments. Turn on SQLCMD mode in SSMS. When you do this, the SqlCmd scripts will be highlighted in gray as shown:

Execute the DeployV1.ver.sql script. If all goes as planned, you should see the following results:

Deploying WeatherData v1.

Calling Test_CreateWeatherData.db.sql...
Failure: WeatherData does not exist.
Test_CreateWeatherData.db.sql called.

Calling CreateWeatherData.db.sql...

Create WeatherData database
> Creating WeatherData database.
CreateWeatherData.db.sql called.

Calling Test_CreateWeatherData.db.sql...
Success: WeatherData exists.
Test_CreateWeatherData.db.sql called.

WeatherData v1 deployed.

Q & A

I hear you thinking: "Andy, this was a lot more work than simply executing a Create Database statement." I agree. This is an example of Test-Driven Database Development. It's a good demonstration of the amount of additional work required to accomplish TD3.

"So why in the world would we do this?" I am so glad you asked that question!

While this is extra work for the first version of the database, the Unit Test developed here will live on as a Regression Test for the remainder of this database's lifecycle.

"Why not backup and restore the database?" You can do that. It works in many scenarios but not all. Remote deployment, for instance. Scripting the database will work wherever restoring a backup will work, but the opposite doesn't always hold.

If you use source or version control, there is some value in comparing scripts. If you don't use source or version control, I predict you will one day.

Make no mistake, there are other ways to accomplish everything I've demonstrated here. There are some fantastic tools out there that help accomplish these tasks. I'll write about them soon.

:{> Andy

Tricks and Gotchas of Logon Triggers, or, Let Me In, Stupid DAC

timbenninghoff — Tue, 28 Jul 2009 01:16:00 GMT

The internet abounds with documentation and blog posts about logon triggers, introduced to SQL Server 2005 in service pack 2. Recently I had the opportunity to work with them developing an imperfect solution to provide a temporary bandage for a vendor's poor SQL security design; a recipe for delving into the dusty, forgotten corners of the functionality. From that experience, combined with some help from Books Online, I've come away with a couple hints and tips, all of which were tested on SQL Server 2005 SP2.

Errors In Your Trigger Can Prevent Everyone From Connecting

Let's say you've created a trigger, but made the mistake of referencing a table that doesn't exist in the trigger's logic. Well, that error is going to prevent anyone, even administrators, from connecting to the server because of the trigger execution. Yikes!

Failures to connect to SQL Server because of a logon trigger are logged to your SQL log file. Fortunately, an error like a non-existent table will also show up in the log file as part of that logon trigger failure to connect. You should be able to use this to give you clues on how to fix your logon trigger.

Know How You Are Going to Disable the Trigger

So, you probably already know that you can use a DAC (Dedicated Administrator Connection) to get into SQL Server, but it's a little more complicated than just using admin:<server name>. You're probably panicking because you've launched SSMS and are trying to connect using admin:<server name>, but you keep getting a message saying 'Dedicated administrator connections are not support. (ObjectExplorer)'. Reading closely you'll see that you can't use Object Explorer with a DAC. Trying connecting via a Query instead.

If you're DBA who doesn't have access to the server on which SQL Server resides, you'll want to make sure you have remote DAC enabled on your server before you start your logon trigger work, either via the SQL Server Surface Area Configuration for Features or sp_configure.

Also, if you try to connect via a DAC and see this error message from SSMS:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (Microsoft SQL Server, Error : 10053)

or this error message from sqlcmd:

HResult 0x2745, Level 16, State 1

TCP Provider: An established connection was aborted by the software in your host machine. Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

...then it means that one of your co-workers, or your evil subconscious, have already made the single DAC connection allowed to a server, and you'll have to track them down and wrestle the DAC away from them.

If you don't have SQL Browser running, you might not be able to connect via a DAC either, receiving some long error about the SQL Browser and port numbers in sqlcmd, or in SSMS about a 'network-related or instance-specific error'. In this case, you'll need to check your SQL error log to find the port that the DAC is listening on, and then use sqlcmd -S<server IP>,<port> to connect to the server.

Know How To Find The Triggers

If you use descriptive names like me and can't remember the exact spelling, you need to be able to find the exact name of the trigger once you've connected via a DAC. The system view sys.server_triggers is your friend. Armed with the name of your logon trigger from this view, DISABLE TRIGGER <trigger name> ON ALL SERVER will help you out of your jam. Or, if you're feeling particular vindictive, DROP TRIGGER <trigger name> ON ALL SERVER will work too.

What if you have multiple logon triggers and don't know which one is stopping you? I'm afraid you'll have to use a process of elimination and the sys.server_triggers.is_disabled column to help you keep track of where you are at.

Although, there is a way to create your logon trigger so that it both prevents a user from connecting AND logs that failed connection to a table you create. That sounds like a good topic for my next blog post.

Thanks go out to @MladenPrajdic and @afernandez on Twitter, both very smart, helpful people whom you should follow, for the inspiration for this post.

Electronic Arts and "Sin To Win"

andy — Sat, 25 Jul 2009 17:28:00 GMT

Introduction

(Found via Brett Petersel, Twitter) So, EA is publicizing a new game called Dante's Inferno, and they chose a contest at Comic-Con to promote it: Sin to Win. There's a decent synopsis here. Take a picture with a "booth babe" at Comic-Con, tweet about it with the hashtag #lust, repeat the process.

Executive Summary

Bad idea.

Detailed Analysis

This is a contest sponsored to promote the game? They're only at the second circle - what happens when they get to the seventh (the violent)?

You know, maybe it's true that "all publicity is good publicity." I wouldn't know. I haven't gone after bad publicity intentionally, so it could be that I'm just missing out.

I don't think the single act of the contest was a disaster. No, they've put a lot of effort into spinning this into the PR nightmare it has become. Their reactions - and make no mistake, these are reactions - are worded in the vein "We know you're upset but that's your problem." Take this image, for instance, posted at twitpic:

I grabbed this image because similar messages posted at TwitPic have disappeared. I'm not confused by what EA did, and neither are the people who are complaining. I understand exactly what they want folks to do in this contest. Side-stepping the issue isn't working out well here. And it's difficult to take an apology for the offense seriously when the contest hasn't been cancelled.

Here's hoping they stop now, seriously analyze where the money for purchasing video games originates (in families), admit their utter fallacy, and identify and promise to counsel the marketeers involved.

Andy