Use SQL to send emails with query results? Great for reporting purposes and useful with dynamic queries.
I won't go into all the little details, you can Google them as easily as I can. Well, ok, I'll make it easy, they are here.
The one issue I've had with sp_send_dbmail is the character limits for the @Query parameter. I can hear you saying, why are you passing in a query with more than 4000 characters? Well Dorothy, sometimes I need that many JOINs to make it work. But seriously, consider the poor person who comes behind you.
I'm an avid commenter in queries as well as code. Once I found myself removing all the line breaks, extra spaces, and fully qualified column names just to make my dynamic query fit into 4000 characters, I had to step back and re-think my approach.
Instead of passing in a normal select statement I decided to declare a new varchar variable and construct an EXEC statement. Ran a quick test and it worked!
Goodbye 4000 char limit. I can create sub-processes for any dynamic SQL I need.
Declare @SQL = 'Select top 1 * from myTable'
exec msdb..sp_send_dbmail @profile_name='sa', @recipients=Nfirstname.lastname@example.org', @subject='TEST', @Query=@SQL
Updated to use Stored Procedure
Declare @SQL = 'EXEC mySproc ' + @myParam
exec msdb..sp_send_dbmail @profile_name='sa', @recipients=@recips, @subject=@Subj, @Query=@SQL, @execute_query_database = 'myDB',@Body = 'Hey I'm adding a header to the email!'
I added a couple of extra parameters to sp_send_dbmail just for fun. The most import one is @execute_query_database, you need that to run EXEC commands.
Any questions, drop me a comment or @jonreid on Twitter.
Friday is Wing Day at the Colemans Deli counter!
$5 for 10 wings at the Caribou Rd, Humber Rd or Humber Gardens locations.
My co-worker Troy is a bad influence, blame him.
They have plain, sweet & spicy, BBQ, and 1st degree. If you ask they will give you 3rd degree sauce on the side, for free I should add.
It's Friday, have a break and try something sinfully good
I've had this problem for ages but it hasn't been a big enough issue for me to spend time on.
Every time I connected my Galaxy to my laptop device driver installation failed and the initializing screen would continuously refresh until I hit the home key or unplugged the USB.
I got around this issue by moving files to and from my phone using the external SD card. Painful!
Looking for a quick solution, I was very close to factory resetting my phone but I was concerned about messing up the carrier configuration.
After a quick backup of my music and pictures to the external SD card one last time, I unmounted and formatted the internal SD. Immediate response when I plugged the phone back into my laptop!
Steps to format internal SD.
SD card and phone storage
Scroll down to Internal SD card
Unmount SD card
Format SD card
PS. I'm running 32 bit Windows 7. I understand there are some issues related to 64 bit but there is an updated driver for that.
New sandwich shop on Broadway! Just across the intersection from Subway we now have The BunWay. I have yet to catch this place open. I must assume they are hoping to cater to the late night crowd. No hours of business are posted.
One thing is certain. They enjoy living on the edge of copyright infringement. The tongue in cheek references to the other "Way" across the way and to "Stairway" aside, the symbols on the signage are right off the inner sleeve of Zeppelin IV. Of course you knew that already...
The apparent good taste of the owners in music is marred somewhat by the classy statement in the sign below:
Still, that is straight to the point. After a long night of patronizing the local beverage establishments, at least 5 in easy walking distance, "A Whole Lotta Meat" may seem very appealing. Zero available seating will still likely make Subway the sandwich shop of choice until the spring kicks in just the same.
UPDATE: Can't believe I missed the other Zeppelin reference. "Whole Lotta Meat"! I'm sure for some carnivores Meat = Love. Even the font is similar...
When you have a system that runs data on a weekly basis you often need to find the date of a specific weekday, past or present.
I've no doubt there are several ways to do this but the following is my solution:
--get most recent Friday's date
Declare @fridayDate date
SET DATEFIRST 6
SELECT @fridayDate = DATEADD(d,0-DATEPART(dw,GETDATE()),GETDATE())
In your procedure you can change the Datefirst to reflect the day of the week you are looking for, in my case I need Friday. Change the 0 to a 7 if you need the upcoming Friday. And finally, change the GetDate() calls to a variable if you want to pass in a date instead of using today.
If this is something you need often, toss it into a function.
Hope you find it useful!
Bandwidth caps are a hot topic in Canada the last few weeks. Caps have already been downgraded severely in Quebec and Ontario with the rest of the provinces to follow soon. The CRTC's recent decision to back up ISP bandwidth limits has been widely petitioned against and is now up for review.
Interestingly, Wikipedia seems to have the most updated list of caps for Rogers customers.
I've been holding back this post for a while waiting on an update from Rogers themselves.
As of Jan 31, 2011 the bandwidth restrictions on their website showed some major discrepancies when compared to the recent announcements:
Ultra lite has 60 GB limit! Right...
I'm happy (?) to report that as of this morning the site has been updated:
Rogers Hi-Speed Ultra-Lite provides a monthly usage limit of 2 GB (2,048 Megabytes)*
It only took 5 weeks for the site to be updated. Considering the size of the company the turn around time is decent. Especially since they never were liable due to the joy of small print.
FYI - I've been with Aliant since I moved back to Newfoundland. No hard set bandwidth limits at all at the time of this post. to quote their site, "As a guideline, in our experience typical residential bandwidth usage tends to be well within the range of 250GB per month". Hard to argue for Rogers in the face of those differences.