How to purge Microsoft SQL email Items

How to purge Microsoft SQL email Items

Purge

There are times that housecleaning older mail in your database needs to be done.  A simple scheduled task can be done every month to purge what is no longer necessary.  SQL Server stores all mails and attachments in msdb database. To avoid unnecessary growth of msdb database you should remove these mail history unless it is required for auditing or other purposes.

As always make sure you have your backups done prior to running any sort of removal in the event you did something incorrectly.  By purging you are reducing the amount of wasted space in the msdb database that could be used for other things activities.

Simply running the following statement using T-SQL you can check the amount of database mails that have been processed from the catalog msdb.dbo.sysmail_allitems.   There are 3 additional  views which shows mails in their different statuses:  sysmail_faileditemssysmail_unsentitems and sysmail_sentitems.

 

SELECT  COUNT(*)  FROM  msdb.dbo.sysmail_allitems

You should get back the count of how many mail items are stored in the msdb.

How to delete mail

To delete mail items you can use system stored procedure sysmail_delete_mailitems_sp, it has below syntax:

sysmail_delete_mailitems_sp [@sent_before] [@sent_status]

You can delete mail using either of the parameters, @sent_before deletes all mail that were sent before specified date, and @sent_status deletes all mails with specified status.

For example, to delete all mails which are sent and are older than current month we can use:

EXEC  msdb.dbo.sysmail_delete_mailitems_sp

@sent_before = ‘2017-05-10 00:00:00’,

@sent_status = ‘sent’

You can modify the @sent_before to do the following:

DECLARE @PurgeDate datetime = dateadd(dd,-30,getdate());  

@PurgeDate will be the date from 30 days or older which is a good idea to remove that or you can change it to
-10 for anything older than 10 days.

So the statement would be

EXEC  msdb.dbo.sysmail_delete_mailitems_sp @sent_before=@PurgeDate;

You may choose to only delete mail items that have been successfully sent.  If that is the case then you can run the following code to delete your database mail based on the sent­_status of “sent”.

If you are not periodically deleting your mail then you might want to consider cleaning up old database mail that no longer has value.  Determine what retention period you should use for your database mail.  With your established retention period then just set up a SQL Agent job to purge your database mail based on your retention period.

How the 9-to-5 workday has disappeared

How the 9-to-5 workday has disappeared

 

Work has changed dramatically over the last few decades, according to a report in The Wall Street Journal recently release it has become more time-consuming, less stable, and more flexible.

In 1973, 6% of Americans said they worked excessive hours while in 2016, 26% said they worked more than 48 hours a week.

Insurance coverage by employers has also dropped since 1973, although companies now provide more benefits to aid work-life balance, such as paid parental leave and remote work options.

Businesses are spending less on employees, both in terms of compensation and capital investment, while investors get triple the payout from 30 years ago.

This has all contributed to workers increasingly acting like free agents in the job market.  While they have more control and flexibility, the “safety net that once came with full-time work has frayed.”

Don’t worry be happy

Americans are happier at work, but they might just be settling for less.

For the first time since 2005, more than half of U.S. workers say they’re satisfied with their jobs, according to the Conference Board, a research group. Employment is up, wages are finally rising and layoffs are near record lows, resulting in a more optimistic, contented workforce.

That buoyancy is giving Americans confidence to pull out their wallets.  Depending on whether the tax plan for 2017 gets implemented the middle class will continue to get hammered with taxes to maintain the social programs such as social security, ACA and other various programs.

There is no such thing as a free lunch but employers are looking to try and maintain a profit.  Small business owners will gain from the tax overhaul plan by not getting double taxed for corporate and personal income.  This fact is not known by most Americans.

Hopefully the work place will improve over the next few years putting wages back into a reality check.

Meet FMS President Luke Chung

Meet FMS President Luke Chung

FMS

Access

Since FMS started in 1986 they have assisted countless individuals and organizations make better data driven decisions. As database experts, FMS help our clients improve accuracy, efficiency, and costs. FMS give managers better control of their processes, provide data to key decision makers, and help developers add features and gain insight into their applications. Collecting, analyzing, and presenting data in ways that empower individuals is what they provide.

Having used their tools for various Microsoft Access Projects and applications Baron Software highly recommends visiting the FMS web site as well as stopping at this event to listen to Mr. Chung.

Meet FMS President Luke Chung at the Microsoft Access User Group Meeting in New York City

September 11, 2017, 6:30 PM
Microsoft Headquarters, New York City

FMS President Luke Chung is the featured presenter at the Microsoft Access User Group meeting in New York City.

Learn from his experiences with Microsoft Access, SQL Server, Azure cloud computing, and techniques to improve your productivity. He’ll answer your questions and demonstrate FMS products.

Includes FMS product giveaways.

We look forward to seeing you at this Free Event.

For more info: read the User Group Announcement

Location

11 Times Square, East side of 8th Ave. between 41st and 42nd Streets (nearer 41st) Belasco Room on the 6th Floor.

The building is across the street from the Port Authority bus terminal. Subway: A, C, or E train to 42 Street-Port Authority Bus Terminal.

Bring Picture ID to get thru security.

How to fix setup errors with SAP Crystal Reports 2016

How to fix setup errors with SAP Crystal Reports 2016

SAP

It is always a small burden to install or execute the SAP Crystal Reports 2016 updates or service packs on a Windows 10 machine.  Things that just drive you crazy to put in a simple update or patch throws you off the track in so many minor ways.

Using Crystal Reports 2016 with Visual Studio is a robust reporting tool that you can access information from your SQL database or even Microsoft Access.  You can also purchase the standalone tool allowing you to build custom reports that you can load into the Crystal Server for multiple users to gain reporting.

A few fixes for updating

Some of the minor “dumb” things that can be fixed in seconds to complete your task are :

  1. Always execute the patch or service pack with Administrator rights otherwise you will get the misleading “Ports … must be opened”.  At first you think the AntiVirus or Firewall are using the ports but the error is very misleading.
  2. After you start the update the next possible problem could be the “suppressed reboot” error thus failing the update.  This is another misleading problem and this could be left over from any previous problems with executing the update.  Following the steps below to solve this issue

The following resolution involves editing the registry. Using the Registry Editor incorrectly can cause serious problems that may require you to reinstall the Microsoft Windows operating system. Use the Registry Editor at your own risk. It is strongly recommended that you make a backup copy of the registry files before you edit the registry. For information on how to edit the registry key, view the ‘Changing Keys And Values’ online Help topic in the Registry Editor (Regedit.exe).

  1. Click Start > Run. The Run Dialogue box appears.
  2. Type “regedit” in the Open field. Click OK. The Registry Editor appears.
  3. Navigate to \HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\
  4. Double click on the PendingFileRenameOperations
  5. Delete the content of this value
  6. Click OK > Yes > OK
  7. Launch the installer and no reboot is required

So this resolves installing any Crystal Reports 2016 update/patch/service pack.

Now if SAP can only get the patch or service pack to install quicker.

A simple approach to software development

A simple approach to software development

Development

Setting up your software development for a new application there are quite a few set of software developers that tend to jump on their machine and begin coding.  I would strongly suggest that an application which could be successful needs to start from thought to paper (even electronic) before any new code is typed.

Now as some suggest creating designs or layouts are the best method for them and sometimes that does make sense.  Creating a sketch board  or laying out the screen design is a simple way to visually see what you want to achieve.

Here are the steps that you can use when you are preparing to create a new application.

Sitting at a table or desk
Using white boards do help when you plan to get your thought on to the board for the initial start of the project.  You can quickly put down what your clients (consumers) wish to have or to fulfill their needs.  Simple step is to list all of the pieces in bullet point what you think the client is expecting from your application.

Always remember that developers can write a great application that nobody wants.

Flowcharts
Yes this is one of computer 101 concepts that no software developer wishes to use any longer but if you set up a general flowchart it will show you things you did not think of.

Screen Design
Screen layouts are extremely important, it gives the visual aspect on what the application will look like.  The best thing is that it could be done on paper without using a computer.  Sure it looks like stick men but it does show the layout that you think clients will be happy with.

Data Model
This is essential if you plan on having some sort of database manager that will capture the information entered with your application.  A proper data model will show exactly what you think each table and record size will be.  The database manager is another piece where you must either sell to the client on a robust database or a small foot print.

Know your customer
Sitting with your clients or consumers or customers, you need to understand the business.  You may not be a retail sales guru but you have to understand what the client wants and possibly increase business.  This does not mean as a software developer you need to allow the client overrun the project where your team gets the blame for it falling apart but you have to understand how the client starts and ends the day.

Team work
The biggest and one of the most difficult piece is to have the presentation in front of the software team.  You must be prepared to accept criticism from your peers.  They may see something you did not see or whether there is a market for your application.

Starting a new application for a market can be either a hard task or you can set up a plan that will get you to the end line quickly and successfully.

Visual Studio 2017 Version 15.3 Released

Visual Studio 2017 Version 15.3 Released

Visual Studio 2017

For Visual Studio 2017 version 15.3, Microsoft focused on improving accessibility, particularly using Visual Studio with the most popular screen readers. Microsoft made over 1,700 improvements but if you are using Visual Studio 2017 in a low-vision or no-vision mode, a lot has improved.

Couple of the more major things Microsoft improved.

  • Debugging is much more accessible. Debugger windows like the Call Stack, Locals, Autos, and Watch windows were inaccessible to screen readers. That’s now fixed.
  • The VS editor’s text adornments let developers know about features available at particular points on a line of code, such as breakpoints, lightbulbs, and error and warning “squiggles.” Customers can now discover and navigate between these adornments via the new “Show Line Annotations” command set, which you can find on the editor context menu.

In addition to accessibility,  many fixes for reliability issues to improve performance, many of which were reported through report-a-problem. Here are some of the more notable ones that had high votes that were fixed:

  • A crash that could occur in C# and VB projects when editing linked files, files in Shared Projects, or files used in projects targeting multiple runtimes.
  • A race condition when debugging C# or VB projects that could cause Visual Studio to crash when ending the debugging session.
  • A crash in C# or VB projects when malformed metadata is encountered in the code file.
  • A crash that could occur when compiling a local function in C#.

Additional key improvements

  • Azure Functions Support. 
  • Broad Azure sign-in support.
  • Improved container support.
  • Continuous Delivery Tools now included.

For the full list of improvements check out the release notes for Visual Studio 2017 version 15.3.

New WinDbg debugger available in preview!

New Microsoft debugger WinDbg available in preview!

Debugging Tools for Windows

Preview

Microsoft is excited to announce a preview version of a brand new WinDbg a Windows debugger.  WinDbg has more modern visuals, faster windows, a full-fledged scripting experience, built with the easily extensible debugger data model front and center.

Things to know

  • Installation - You can install the WinDbg Preview from the store if you have Windows 10 Anniversary Update or newer at https://www.microsoft.com/en-us/store/p/windbg/9pgjgd53tn86 – WinDbg Preview uses some features from the Windows 10 Anniversary Update, so that’s required for now.
  • Feedback - Familiarize yourself a bit with the Feedback Hub!  Microsoft will be using the Feedback Hub to help prioritize what you want Microsoft to work on! The Windows Insider website has a great overview on how to give good feedback – https://insider.windows.com/en-us/how-to-feedback Once you’ve read that, just hit the ‘Feedback Hub’ button on the home ribbon.
  • Questions - Send feedback in the Feedback Hub, or tweet @aluhrs13 for answers. A FAQ on the blog sometime in the upcoming weeks will be posted.
  • Documentation - Microsoft has some early documentation up on MSDN that’s preliminary and subject to change at https://go.microsoft.com/fwlink/p/?linkid=854349, you can give Microsoft feedback or propose edits to that documentation by hitting “Comments” or “Edit” on any page. Keep your eyes on the MSDN blog – https://blogs.msdn.microsoft.com/windbg – for more updates and tips.
  • Videos - You can view on Channel 9 studio with recorded episodes of Defrag Tools to help explain some of the basics of WinDbg Preview.
    • Defrag Tools #182 – Basics of WinDbg Preview and some of the features
    • Defrag Tools #183 – WinDbg Preview and a quick demo
    • Coming Soon – Defrag Tools #184 – A walk through the scripting features in WinDbg Preview

Restrictions and other things worth noting

  • At this point in the preview, Microsoft is only offering WinDbg Preview through the Windows Store. That means only devices running Windows 10 Anniversary Update can install it.
  • You might hit errors when trying to do something that requires elevation. You’ll have to manually launch WinDbg Preview elevated.
  • The concept of a workspace is going to be changing a lot. A workspace in WinDbg Preview is vastly different from one in WinDbg. The MSDN documentation linked above has more information.

The 2017 Microsoft Product Roadmap

The 2017 Microsoft Product Roadmap

The 2017 Microsoft Product Roadmap has been updated and released for the public to view what is going to be coming this year.  One large item is the MS SQL Server release for Linux.  This alone may open the doors for Microsoft to take over the Linux market from MYSQL who has been the center piece on Linux for years.   This year’s product roadmap looks to be a bit less crowded, though major changes are on tap for Microsoft’s productivity solutions, while Windows 10 is poised for another landmark update.  https://rcpmag.com/articles/2011/02/01/the-2011-microsoft-product-roadmap.aspx

Microsoft is attacking the Linux arena at this point giving Windows developers the necessary tools to work with MS Code to build Python applications or access a SQL server.

Microsoft Visual Studio 2017

Another major release will be Visual Studio 2017 that will:

  • Enhancements to productivity: Visual Studio 2017 features new filtering capabilities in IntelliSense, improvements to navigation and debugging, live code analysis and editing, and the ability to access files without projects.
  • Greater mobile development support: Developers can build iOS, Windows and Android apps using JavaScript, C# and C++. Visual Studio 2017 also comes with a mobile test recorder for application testing purposes.
  • More streamlined development for the cloud: “Visual Studio 2017 RC improves DevOps workflows from Git-based version control to making it much simpler to create continuous integration and continuous deployment pipelines,” according to Montgomery.
  • Speed and performance improvements: Visual Studio 2017 is faster to install and start up, has a smaller memory footprint, and loads solutions between two to four times faster than its predecessor.

The roadmap looks pretty strong with the different avenues that developers can grow new products and possibly get additional business / profits from.  Microsoft also will be delivering a new XBox as well as the Surface Pro 5 so 2017 seems to be a strong Microsoft year to come.

Happy 15th Birthday .NET!

Happy 15th Birthday .NET!

Microsoft

Today marks the 15th anniversary since .NET debuted to the world. On February 13th, 2002, the first version of .NET was released as part of Visual Studio.NET.  Microsoft was building its “Next Generation Windows Services” and unleashed a new level of productivity with Visual Studio.NET. Since the beginning, the .NET platform has allowed developers to quickly build and deploy robust applications, starting with Windows desktop and web server applications in 2002. You got an entire managed framework for building distributed Windows applications, ASP.NET was introduced as the next generation Active Server Pages for web-based development, and a new language, C# came to be.

With the release of Visual Studio 2017 coming on March 7th and Visual Studio’s 20th anniversary, .NET Core tools reach 1.0. Tune in March 7th and watch the keynote and live Q&A panels.  The biggest question for developers will be the Visual Studio 2017 release whether to go with the community or professional version.

 

Saving SQL Column Headers Results

Baron Software

Saving SQL Column Headers Results

Sometimes you can run into problems that tend to take up time and could have been solved in a minute.  One of these strange problems is when you attempt to save a Microsoft SQL query result grid to a CSV file.

Using Microsoft SQL Server Management Studio (SSMS)

Now most of DBA or Developers tend to use the Microsoft SQL Server Management Studio (SSMS) to do the necessary general SQL work.  As you expected when you execute a query the results grid appears displaying the results.  I as countless others tend to copy the information and save it in an excel spreadsheet to do further analysis.  But there are times you may need to save the information into a CSV file for the purpose of processing the information later or with a different application.

If you use the option to save out to a CSV file you will notice that the column headers are missing and you are pretty much baffled.

A simple click to solve the issue

It is not you but apparently Microsoft decided not to default a simple setting that saves the column headers in your CSV file.  What you need to simply do is click on Tools – Options which will open the Options Dialog box.

In this Dialog box you will click on Query Results – Results to Grid.  You will see the “Include column headers when copying or saving the results”, click to enable it.  You should see the similar as the image below.  Click OK to close the dialog and will have to restart the Server Management Studio application for the settings to take hold.

MS SQL Server

This is something we all take for granted and by diving into the vast amount of options you can solve it.  So go ahead and take a look to see if you have it set or need to.