One thing I also just realized is that you are doing all of this to try to improve performance of a stored procedure. Could you include the...

You may want to check on the IO's for these queries to get a better feel for the sorts of reads SQL is doing under the covers (set statistics io...

This is one of those things where it really depends.

In general, the SQL optimizer does what it thinks will work the best based on the data that it has available...

I would check for triggers. You to have either sp:stmtstarting or sp:stmtcompleted in order to see that those have run, so it is quite possible you do...

Interestingly I see this sort of thing FAR more often in the scripts that are built through wizards, maintenance plans, etc.. ...

Based on your procedure you have everything wrapped up into a single transaction (begin tran at the beginning, commit at the end). You could invariably keep the log...

sp_send_dbmail works just fine for doing that. You just need to know the provider information (i.e. phonenumber@txt.att.net or phonenumber@tmomail.net, etc..). Works just like sneding a normal email.

As another alternative (if you are the anti-trigger type) SQL Magazine had a good article a few years back to get this information in a pretty convenient way.

http://www.sqlmag.com/Article/ArticleID/96056/sql_server_96056.html

If you do...

I think what you have will work just fine. However this is a good article on doing some error trapping in powershell. Perhaps you can get some extra...

In this case Charindex is just looking for the next comma in the string starting at the position of the previous comma. I assume @RoleNames is just a comma...

I have no experience with CDOSYS but I have been using xp_smtp_sendmail for years on 2000 servers and have never had an issue with it. As long as the...

I would absolutely consider just using partitioning for a situation of this nature.

This is a great Whitepaper on setting it up and managing partitioning in SQL 2005

http://msdn.microsoft.com/en-us/library/ms345146.aspx#sql2k5parti_topic6

This focuses more on...

Open the package and do a Save As, where you specifiy a different server.

Or this site has a nice tool for moving packages around.

http://www.sqldts.com/

This sounds to me like it is not able to find the login and therefore is looking for you to pass in the password as well.

you could do something generic...

I may be incorrect in my assumption but this looks like an orphaned users issue.

http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm