Using Yahoo Mail instead of Gmail for SQL Server Database Mail?

Maybe….

I’m working on a notification system so that when certain procedures are run, certain people are notified.  I configured Gmail as the SMTP provider for my production box four months ago and it’s been flawless.  My job that runs my nightly DBCCs at 23:59 sends me a copy of the results every night at 23:59.

I never configured my development box with SMTP, until today.  I couldn’t find my config info on how I’d set up my production box (I have since found it) so I set up a new Gmail account.  Went through the account and profile creation via SSMS, and sent a test message.  And it didn’t go through.  Double-checked everything and it all looked fine.  Printed out the config from my production server and everything looked good.  It just refused to work.

Eventually I got an email from Google that said ‘Sign-in attempt prevented’.  It eventually penetrated my thick skull that the blue background that said REVIEW YOUR DEVICES NOW was actually a label that opened a configuration screen.  I acknowledged that yes, I was OK with the address in my neighborhood sending me email, and I assumed that would be the end of it.

It turned out that I was wrong.  As I said I found the mail configuration info for my production server so I added another profile to my dev box.  Still no dice.

Finally I decided to create a Yahoo mail account.  And it worked.  It isn’t as instantaneous delivery as Gmail was, at least right now, so we’ll see what happens with it.  Initial tests show that it takes Yahoo 3 minutes from when my server sends it and when my email client receives the message.

I’ll do some more experimenting tomorrow to try to get Gmail working, we’ll see what happens.

Advertisements

SQL Server: “Service Broker GUID does not match the one in sys.databases”

Yesterday I needed to enable database mail on my development server to work on another phase of my project.  Open SSMS, expand the Management Tab, right click on Database Mail and click on Configure Database Mail.  Easy peasy, right?  Except it would complain that I needed Service Broker running, fair enough, but when it tried to start it my system went away.

Worse yet, nothing in any logs.

I got a partial clue when I found a Stack Overflow post titled Enable SQL Server Broker taking too long time.  It had two helpful things:

SELECT name, is_broker_enabled FROM sys.databases

and

ALTER DATABASE [mydatabase] SET ENABLE_BROKER

From the first I learned that only TempDB was enabled.  I used the second line of code to turn it on for my development database, but it didn’t work for my MSDB.  A reply suggested adding WITH ROLLBACK IMMEDIATE to the ALTER.  When I did that and tried to run it, I got something like this:

Alter failed for database 'xxx'
Cannot enable the Service Broker in database "xxx" because the Service Broker GUID in the database (guid number) does not match the one in sys.databases (different guid number)
ALTER DATABASE statement failed.

Not very useful, EXCEPT it steered me to a blog post by Grumpy DBA, in which he describes the problem as a mismatch in the service broker GUID coming from copying a database from another server installation.  Fortunately he offered a solution:

ALTER DATABASE XXX SET NEW_BROKER WITH ROLLBACK IMMEDIATE

Here we force a new GUID upon the database, the Rollback Immediate forces any open connections closed.  Obviously you want to be VERY careful doing something like that in production, but you should be OK in development.
At least now I can get in to the database mail setup and get on with my project.

THANK YOU, GRUMPY DBA!

Another ‘gotcha!’ in Access that I need to remember

I’m working on two sets of menu actions that are almost, but not quite, identical.  The first set works against all of the students assigned to a specific teacher.  The second set pulls against a subset: it’s a caseload system where the teacher can say that ‘I only work with THESE kids’.  We have two districts with the potential for a hundred or more blind students, far more than one teacher should work with to provide good service.  So there will be multiple teachers with certain students assigned to them.

The solution was a Caseload table consisting of two fields: TeacherID and StudentID, those two fields are also the clustered PK.  I’m debating whether StudentID should also have a unique constraint, that’s a topic for a future meeting of the work group (which hasn’t happened since April).  If a student should only be assigned one teacher, then I could theoretically add the assigned teacher to the student record and use a filtered index which might give me better performance.

So the menu options are Edit Students, View Missing or Late (eye) Evaluations, Edit APH Status (an annual function that gives us more funding to get the student’s more stuff to help with their education, depending on severity of their visual impairment), and a browser that lets the teacher see if a specific student ID is assigned to what district.  The first three are duplicated for the caseload system, the fourth is replaced with a button for adding/removing students to the teacher’s caseload records.

Thus those three caseload functions join against the teacher’s records.  I got the add/remove code working late last week, today I started working on the other three functions, and I finally got them working.

Except for one problem — the second and third programs didn’t work.  They worked fine when I opened them from the Access Object Browser window, but not when I tried to open them from the menus.  I’d get a parameter box asking for a value that shouldn’t be needed.  I’d click Debug and go in to the code, and it didn’t make sense as the parameter being requested wasn’t there!

It finally occurred to me that it might be a macro parameter!  So I clicked on the builder for the On Open event, and sure enough, there it was.  Delete it, and the code works fine.  My real mistake was being lazy and using the Command Button Wizard to create a macro to open the forms rather than going to the fairly trivial effort of putting VBA code behind it.  It would probably be a good idea to go through my system and change all the buttons from macros to VBA, but I have two more major dragons to slay before I can go to internal testing, and the superintendent wants to get this out to the districts, so I think I’ll slay some dragons before I go back to that part of the code.

A couple of additional tools for digging into execution plans

Grant Fritchey has a book on execution plans, revised in 2012, available as a PDF for free from the Redgate site where print and Kindle editions are also available for purchase.  There are MANY free books available on Redgate’s site, highly recommended reading!

And there’s SQL Sentry PlanExplorer, in both a free and paid version, it’s an amped-up version of the basic SSMS tools.  Like Mladen’s tools mentioned in the previous post, it is invoked by a right-click on an execution plan object and opens in its own program window.  There’s a lot of interesting aspects to this tool, such as the Join Diagram that shows you sort of a relational diagram of your query.  One thing that’s quite nice is that each operator’s cost is shown above the icon with the most expensive operator in red and the next in yellow.  If you hover your mouse pointer over any tool you’ll get the same information as what you’d see in SSMS, but if you hover over the first (top left) icon, it will tell you if a good enough plan was found or if the optimizer timed out.