Rick Caminiti dotCOM

Here’s a quick and dirty way to send email via MSSQL.  For some reason, we’re not allowed to send email using the Email Task in SSIS, so I chose this option.  Unfortunately, even if you have access to execute sp_SQLSMTPMail, you might not have access to run xp_cmdshell.

 

I ended up having to create my SSIS package with a SQL Task that runs this proc, then I had to run it with a SQL Agent Job with a proxy account that had access to xp_cmdshell.  Fun stuff.

 

 

 

 

declare @developerperson varchar(80)

declare @message_body varchar(1000)

 

set @message_body =

‘Dear ‘ + @developerperson + ‘Use \n for line breaks and escape your ”s’

 

 

EXECUTE dbo.sp_SQLSMTPMail

      @vcTo = ‘You@you.com’,

      @vcCC= ‘Them@them.com’,

      @vcSubject = ‘Super Cool SQL Email’,

      @vcBody = @message_body,

      @vcFrom = ‘Me@me.com’,

      @vcSMTPServer = ‘SMTPName’,

      @vcSenderName = ‘Some One’,

      @vcServerName = @@SERVERNAME –no need to specify because this is the default anyway

Or, lets say you want to email query results as an attachment:

EXECUTE dbo.sp_SQLSMTPMail

  @vcTo=‘you@you.com’

, @vcCC=‘them@them.com’

, @vcSubject=‘Here is your wonderful report that is terribly formatted as a txt file attachment’

, @vcquery= ‘select col1, col2, col3, col4 from database.schema.table’

 

 

 

 

 

 

 

 

Here’s the actual Proc definition: Read more…

If you’re trying to delete the first n records in a table, here’s how to do it in SQL 2005: Read more…

This was a great article on LinkedIn that I thought I would share.  I’ve done most of this and I’m ALWAYS getting LinkedIn messages for job openings.  It’s a very valuable resource in this economy!

 1. Make your LinkedIn profile 100% complete

•    Include all companies, education, and awards. These are the key items on which we recruiters search.
•    References are very important. The more we know about you the better.
•    The more robust your profile, the higher you will be in the Google search rankings.
•    The more information a recruiter has upfront, the more efficient the search process.
•    Update your LinkedIn profile here

2. Use a profile picture that you use on multiple sites

•    A picture helps to create and reinforce your online brand
•    It will help a recruiter identify you on the interview day at “Starbucks”
•    Add a profile picture here

3.  Use the “Specialties” box to fill in keywords

•    List as many keywords as possible that are relevant to your roles, capabilities, and interests that will help you turn up on LinkedIn Search
•    Update your profile summary and specialties here

4. Update your LinkedIn “Status” regularly

•    Link to articles you have written or in which you are quoted
•    Update status with your latest blog posts
•    Discuss business accomplishments
•    If you are actively looking for a job, tell people what you are looking for!
•    Update your status on other social networking sites such as Twitter. Try services like ping.fm that allow you to update your status across multiple sites.

5. Make changes to your profile after every job change or promotion

•    The recruiters you are linked to will notice these changes
•    It is harder to be found if people don’t know where you are
•    Update your LinkedIn profile as you would your change-of-address at the post office

6. Include your web site and blog links

•    Add suitable weblinks to the “Websites” section on your LinkedIn profile
•    This could range from your career blog to your Twitter profile. Alternatively, you can also link to a guest blog post you wrote recently.
•    Update your “Websites” section here

7.  Do not block incoming emails

•    No matter how popular you think you are, you will not be overwhelmed. I promise!
•    The LinkedIn community is all about participation so feel free to accept incoming communication from fellow LinkedIn users. Update your contact settings here.
•    If a recruiter reaches out to you and you are not interested, let him or her know or better yet refer a friend
•    LinkedIn actually allows you to control how you receive emails and notifications. Update your settings here.

8. Increase your number of trusted connections!

•    Use webmail importer to bring your real world professional relationships online and to find your contacts who are already on LinkedIn.
•    Depending on whether you’re a browser person or an Outlook person, check out the respective toolbars that will help organize your professional relationship either while you browse or check out your Inbox.

These are just a few tips that I’ve gleaned from my LinkedIn usage over the past year. If you’ve other tips that have helped you as a job seeker reach out to appropriate hiring managers or recruiters, feel free to leave a comment. Hope this helps.

I recently needed a way to execute a script overnight to count the number of records that were being updated in a given hour.  I needed it to run at a specific time and to execute in 10 minute incriments and stop .  I was hoping to log the results to a file, but this does just as well.  When it’s done executing, it spits it all to the output window so I can see it in the morning when I get back.

 

This script below will start at 11pm and end at midnight and it will execute every 10 minutes and display the current time and the count.  You could just as easily use a counter and have it execute a certain number of  times.

 

–don’t even start until 11pm

WAITFOR TIME ’23:00:00.000′

declare @endtime datetime

set @endtime = cast(’2009-05-09 00:00:00.000′ as datetime)

–stop at midnight

while getdate() < @endtime

begin

      –every 10 minutes, execute

      WAITFOR DELAY ’00:10:00′;

      select getdate(), count(*)

      from tablename

      where datetimecolumn > ’5/7/2009′

      and datetimecolumn < ’5/8/2009′

end

 

 

Kudos to Jerry for helping me with this.

Great post about management that I think everyone should hear.  These things sound ridiculously obvious, but somehow, it seems that these simple ideas escape everyone that is in a position to apply these principles.

 

Be transparent! Don’t try to hide the fact that we are in a recession. Profits are down and businesses are suffering. Talk about the company performance. Don’t sugar coat or spin it in ways that aren’t accurate. Your employees are smart – they are watching the news and hear the same stories you are.

Over-share with your staff, even more so than you would in a good economy. If they know that you understand and are feeling the same pains that they are feeling, they are more likely to figure out ways to help.

Reinforce your core values and your mission. Remind them of why you are in the business you are in and how important and vital their roles are to the organization. Help them to remember why they do the job that they do for you.

Do not do anything to affect your quality. Quality should remain key.

Don’t take advantage of your employees. It’s never a good thing when you have employees who are only working to save their jobs. Reaffirm that you are behind them and support them.

Check out the rest at http://stonecreekstaffing.blogspot.com/2009/05/tips-for-managing-employee-morale-in.html#comment-form and feel free to let that wonderful layer of middle management know what they should already know.

I have been in under the impression that you could not drop SQL Agent Jobs without being able to look at the properties and being able to get the JOB ID, since you need the jobid to pass to the sp_delete_job function.  Because of that, whenever I wanted to change a Job, I always had to incude a manual step to log onto the server, right click and delete the job.

However, I found a simple script to automate this, so now I can just include this in my script that will recreate the job.  Simple… should have known.
USE [msdb]
GO
DECLARE
@jobid sysname;
IF
EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'EXACT_JOB_NAME')
BEGIN
SELECT
@jobid = job_id FROM msdb.dbo.sysjobs_view WHERE name = N'EXACT_JOB_NAME'
EXEC  msdb.dbo.sp_delete_job @job_id=@jobid
END 

The other day I was trying to figure out how to send an email via Script Task in SSIS. For some reason, the Send Email Task wasn’t working for me (still can’t figure out why). Anyway, here’s what I ended up with.

Works like a charm…


' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net

Public Class ScriptMain
    Public Sub Main()
      Dim myHtmlMessage As MailMessage
      Dim mySmtpClient As SmtpClient

      myHtmlMessage = New MailMessage(“email@somewhere.com”, “email@somewhereelse.com”, “Subject”, “Body”)
      mySmtpClient = New SmtpClient(“SMTP”)
      mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
      mySmtpClient.Send(myHtmlMessage)
      Dts.TaskResult = Dts.Results.Success
    End Sub
End Class