SQLServerCentral Article

Knowledge sharing


I became a computer programming 9 years ago at the age of 40 and some people looked at me skew thinking I must be crazy to make a career change at the age of forty. Well, I suppose that If I stayed in my job I would have been some area or regional manager and earning a very good salary but I would not have done what I really wanted to do.

Ever since I started working with computers in my work I wanted to know more but the people that knew did not want to share bringing me to the point I want to talk about. I have, most of my 9 years as a programmer or developer worked with MSSQL 7 and due to the fact that we were busy developing a new business management system, never had the opportunity to learn much more about SQL other than writing select, update, insert and delete stored procedures and creating tables with primary keys and other indexes. I did not know about triggers, statistics on tables and a heck of a lot of other stuff. I did not get involved in Database Administration until one of our clients upgraded there database to SQL Server 2005 and told me that I must now take full responsibility for the server and the database. To make a long story short I needed some knowledge all of a sudden and began searching the net and came upon this website. I was amazed at all the help a person got from the forums of this website and other websites. There were times, however, that I did not come right where either I asked the wrong questions or the wrong people answered my posts on the forums. I would like to mention one such example and may I just say that I do not deliberately want to point out the negative but rather the positive.

First example

I needed to setup database mail on SQL Server 2005 to alert me if any of the jobs failed but it continuously gave me an error that read something like this: "....mail session could not be opened....". I searched all over, posted a request for help on the forums on SQLServerCentral but could not get an answer. Eventually I came upon a website where somebody explains in nice detail exactly how to set up your database mail and for your convenience add the URL here. http://www.mssqltips.com/tip.asp?tip=1438

In this article though there is one crucial point missing because I deleted all the mail that I set up and followed this steps carefully but alas! still no joy. Before I found out that there was still a problem I decided to go and find my post on the forums but could not find it. I forgot how I typed it but came upon a post by someone else with the exact same problem and someone posted the answer to the problem and voila! my problem is solved.

Thanks to Jason Deyalsing (one of our members on SQLServerCentral) who posted the following answer to a question I had the solution to my problem

You need to make sure the mail profile is enabled in the SQL server agent properties.
Right click SQL server agent -> 'properties'
select 'Alert system' then check 'Enable mail profile' under 'mail session' then restart the SQL server agent service.

Alas! it worked and the e-mails started pouring into my mailbox because I set it up to send an e-mail when the job completes. I quickly changed that to e-mail me when the job fails.


There are so many things in SQL Server that either are not documented or documentation cannot be found and if we did not have these forums where we can share our knowledge and where we can ask questions then we would just have to give up sometimes. Look, I am not trying to break down the wonderful work that the people at Microsoft have done. I mean, this is a vast program and surely somewhere something will not be documented and it can be something small and once again I am only trying to bring out the positive and that is people that are willing to share knowledge with others.

Another example

I have another example. I created a Backup Cleanup maintenance plan on my database but it would not delete the old files. I looked everywhere and eventually came upon this site where I found a solution to the problem: http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=91&threadid=86777&enterthread=y.

I will add the following code that I got from one of the posts for your convenience.

declare @datestring varchar(12)
set @datestring = convert(varchar(12),dateadd(d,-3,getdate()),1)
EXECUTE master.dbo.xp_delete_file

Now for the undocumented (correct me if I am wrong) part. The directory name MUST end with a "\" and the file extension must be entered without the "." as in ".bak". Just enter "bak". For the purpose of deleting ".txt" files the first parameter must be 1. The first parameter is FileTypeSelected and its value can be either 0 for FileBackup or 1 for FileReport. All of these can be found on the site linked above but I just added it in to save you the trouble.

Last comments

Let us share our knowledge and do not become impatient with the rookies and those who take a bit longer than usual to understand. My boss used to say to me: "Manie, you take long to click (understand) but once you clicked then you can never be unclicked again". Yes, I am also a slow learner but thanks to all you wonderful people that share your knowledge I will also get there.


4.26 (35)

You rated this post out of 5. Change rating




4.26 (35)

You rated this post out of 5. Change rating