Rick Caminiti dotCOM

I posted earlier on how to generate a rownumber in SQL 2000, but that would not work as a source query in SSIS for some reason.  Without having to create a stored procedure or temp tables based on other temp tables, I wanted a simple way to generate this rownum in SSIS.  I was considering something with variable expressions, but then I found a way to do it with a Script Component.

Drag a Script Component Task into your DataFlow and connect it between your source and destination tasks.  Open it up and click “Inputs and Outputs”.  Double Click “Output 0″ and highlight “Output Columns” and click Add Column.  For this code to work, name that new column “rownum“.

Then open the script tab and click “Design Script” and paste this code over what is already there:

  

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

 

Public Class ScriptMain

    Inherits UserComponent

    Dim counter As Integer = 0

 

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

 

        Row.rownum= counter

        counter = counter + 1

    End Sub 

End Class

 

Here’s are two quick ways to Delete data in one table based on another table.  This works perfectly in an ETL job where you have a staging table that indicate whether to Insert, Update, or Delete the data in the other table.

The first one uses an inner join, the second uses a simple EXISTS clause.

DELETE b

  FROM [dbo].[TABLE_1] b

 INNER JOIN [dbo].[TABLE_2] w

    ON b.[ROW_ID] = w.[ROW_ID]

 WHERE w.[CTRL_CD] = 'D'

 

 

DELETE b

  FROM [dbo].[TABLE_1] b

 WHERE EXISTS (

            SELECT 1

          FROM [dbo].[TABLE_2] w

         WHERE b.[ROW_ID] = w.[ROW_ID]

           AND w.[CTRL_CD] = 'D')

 

Oracle has a pseudo-column called ROWNUM that allows you to display the row number for each row.  There are infinite reasons why you might want to use the ROWNUM, but in SQL it’s not nearly as easy to determine.

 

In Oracle, it’s simply

SELECT ROWNUM from table_name

 

In SQL 2005+, there is actually a ROW_NUMBER() function, but you have to give it some more information to use it.

ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

 

For example:
use [Database]
select row_number() over (order by data_srce_id desc) rownum

      ,data_srce_id
from data_srce 

 

rownum	data_srce_id
1	8
2	7
3	6
4	5
5	4
6	3
7	2
8	1
9	-1

 

However, in SQL 2000 it’s a little more difficult.  The only way I’ve found is using the IDENTITY() function, which requires an INTO statement, so you pretty much have to load the results into a #temporary table and then select from the #temporary table like this:

use [Database]

SELECT IDENTITY(int, 1,1) id

      ,t1.[column1]           col1

      ,t1.[column2]           col2

      ,t1.[column3]           col3

      ,t2.[column2]           col4

  INTO #Temp

  FROM [dbo].[Table] t1

 INNER JOIN [dbo].[Table2] t2

    ON t.[column] = t2.[column]

 

select *

  from #Temp

This is a must-read if you’re using WordPress and want some SEO tips. 

1. The basic technical optimization: simplest stuff, highest rewards
2. Permalinks
3. Optimize your Titles for SEO
4. Optimize your Descriptions
5. Optimize the More text
6. Image Optimization
7. Template optimization
8. Breadcrumbs
9. Headings
10. Clean up your code
11. Aim for speed
12. Rethink that Sidebar
13. Advanced technical optimization: preventing duplicate content
14. Noindex, follow archive pages
15. Disable unnecessary archives
16. Pagination
17. Nofollowing unnecessary links
18. Altering your blog’s structure for high rankings
19. Pages instead of posts
20. New wine in an old bottle: use well ranking-posts to rank even better
21. Linking to related posts
22. Conversion optimization: get those readers to subscribe!
23. Comment optimization: get those readers involved
24. How should you get people to comment
25. Bond with your commenters
26. Keeping people in the conversation
27. Off site blog SEO
28. Follow your commenters
29. Use Twitter
30. Find related blogs, and work them
31. Conclusion

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.