Tuesday, June 19, 2007

Installing Visual Studio 2005 SP1


Did you try installing SP1 for Visual Studio without any errors? If not or if you are still did not start the installation some of the following details will help.

The list of things that Microsoft had fixed in Visual Studio 2005 SP1 is located at http://support.microsoft.com/?kbid=918526.

Most of the time the setup will fail due to the insufficient disk space. So before trying to install the SP1 check whether you have at least 6.3 GBs of free disk space. If you have installed VS in other partition than Windows I recommend to check for free disk space in both partitions.
Another common error which can happen when installing this is,

Error 1718. File FileName was rejected by digital signature policy.

This error is due to the Software Restriction Policies (also known as SAFER) that exist in the operating systems that came after Windows XP. This was introduced to help users avoid running unsafer files. SAFER will be used by Windows Installer to check the file signatures to confirm that the files were not tampered. Note that this error can happen when installing any large Windows installer package or Windows installer patch.

This error occurs when there is no contiguous piece of virtual memory to accommodate the file. Then the Windows installer will fail to validate the file and result in this error.

There are few things you can do to fix this,

1. You can edit the registry to make the PolicyScope value to 1 before you install the package.

2. By changing the SAFER settings to allow administrators to install the package.

3. Installing the hotfix if you are running Windows 2003,

For any of this you can get more information from Microsoft by visiting http://support.microsoft.com/?kbid=925336.

Apart from that Visual Studio SP1 release note can be found at http://support.microsoft.com/?kbid=928957.

Also I suggest that you refer Heath Stewart's articles for more installation tips. Heath is Technical Lead working at Microsoft http://blogs.msdn.com/heaths/archive/tags/VS+2005+SP1/default.aspx.

Even though I had no luck recovering the applications without formatting the system which crashed with the failed installation of Visual Studio SP1, I guess you will have much more luck after reading these articles.

Wednesday, June 13, 2007

Have you tried Google Analytics?

Google Analytics is a free tool provided by Google which will analyze your web site usage. It will monitor your site and will generate reports such as
* number of users visited
* number of minutes spent on the site
* peak times
* place where users came
* what explorer used, etc.
This list is simply a short list of features. See for your self by visiting,

https://www.google.com/analytics/home.

You can log in using your GMail Id and password. Then you have to add your site to analytics and it will give you a script file which you needs to put in to your site. That's all to worry then Google will do the reporting to you.
Happy analysis.

Tuesday, June 12, 2007

World's Smallest Mobile Phone - WatchPhone


Have you seen the cool gadget SMS M 500. This is a watch with basic phone functionalities. This will include facilities and features such as

* Full SMS and MMS functionality
* MP3 Support AAC/MP3 and MP4 Support Video Playing
* Touchscreen with Built-in Stylus
* Memory: 128MB Built-in Memory
* Battery: 400mAh, Talk Time 200 Minutes, Standby Time 80 Hours
* USB: For Data Transfer and Recharging
* Bluetooth 2.0
* Date/Time
* Last Number Redial
* Recent Calls
* Missed Calls
* File Manager
* 199 Contacts
* Multi Languages
* Flight Mode
* Email
Roughly this will be priced around A$ 1000 (Australian Dollar 1000).
For the full details please visit the manufacturer SMS Australia at http://www.mymobilewatch.com/watch_specification.php.
Clearing Windows History


Have anyone tried removing items in Windows history as in Run window history items, most recent documents, web browser history, most recent applications like stuff?
As usual, a bizzare requirement of deleting the Run window history came to me.

After some wondering here and there I found that there is a registry position where the Run window history is recorded. You can find that entry at

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\RunMRU

in the registry editor. (To open Registry Editor go to Run window and type RegEdit.)
All the history entries dropped in the Run window will be in the list. If you wants to edit or delete an entry you can edit or remove them using the registry editor.
In some scenarios this list will be empty even though there are items in the Run history. In such a case there is an option you can set so that the next restart will erase all the history items. This method is really good if you want to start fresh removing al history items. This will remove Run history, recent programs, recent documents, internet browser history, etc by one shot.
Interested, ok so to do this first open the registry editor. Then go to the following location

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer

Now you need to add a Binary Value named ClearRecentDocsOnExit (Edit -> New -> Binary Value), then make the binary value of that to 1 (Edit -> Modify Binary Data).
The next time you shutdown or restart Windows it will take little longer to shutdown because it is clearing the history items. After you start again you will be free of your history items.

If you always want to start fresh at each restart keep the setting as it is. If you just needed to clear the history at that point like me you have to either delete the value we made or have to make the binary vale 0 so it will not clear the history at each restart. If you needs to change this find the ClearRecentDocsOnExit value and modify the value to read 0 so windows will keep your activity history.
Back in Sri Lanka

Came back to home sweet home. Even though the country is having problems at the moment it feels nice to be here in my motherland.
On my way back I enjoyed Singapore little bit.
If you would like to know more please see my photo album named Singapore. I think Singapore airport is one of the best airports in the world providing great facilities to travellers. From the airports which I have been I can say Singapore is the best. Their idea of having themed parks inside the airport is a brilliant idea. Also I heard that they are building there new terminal (Terminal 3) with much more features. So I guess anyone going to Singapore won't be disappointed.

Wednesday, May 09, 2007

Watched Spider Man 3 on IMAX

Spider Man 3 was released 3rd of May 2007. I had a chance to watch it in Melbourne IMAX theater. This was my first IMAX experience. I feel great to watch Spider Man on its release day.

It was a great movie experience. The speciality in IMAX is it is having very large screen and also the sounds system is also complicated and powerful. The screen is made out of tiny holes so that sound can come through the screen. When you watch you will feel as if you are in the movie. I think the experience is worth the extra money spent.
If you would like to know more about IMAX, pay a visit to their site www.IMAX.com.

Adding and Auto Sizing the background image of a MDI Form in .NET

Some months back I faced a problem of resizing an image on one of my tablet PC device applications. The image was on a Multiple Document Interface (MDI) form. Recently only I remembered this and did some research on this, and thought that better to put an entry.
People who have tried should be knowing that we cannot simply add an image to a form and make it automatically re size when the form changes it's size.
In a normal form we can put a picture box and make it fill the entire screen and can set the image properties to always stretch the image. This will re size the background image when ever you re size the form.
But in MDI forms this is not possible. Whatever control you put on a MDI form will appear on top of all other controls, hiding even the child forms that are open. I have found two elegant methods which can be used.

Method 1 -
Insert the following code into the MDI form code. Here we are using Paint event of the MDI form.

Private Sub frmMDI_Paint(ByVal sender As Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles MyBase.Paint
' Always the MDI form will be the last control in the control collection. The following will take the MDI forms reference so we can access the properties of it.
Dim varControl As Control = Me.Controls(Me.Controls.Count - 1)
' Loading the background image required to place on the MDI.
Dim varImage As Image = Image.FromFile("E:\Projects\Tablet\Arjuna\Images\Background-Landsc.jpg")
' Resize the image and assign it to the MDI form background.
varControl.BackgroundImage = New Bitmap(varImage, Me.Width, Me.Height)
End Sub


Method 2 -
Insert the following code into the MDI form code. Here we are using Resize event of the MDI form.

Private Sub frmMDI_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Resize
' Loading the background image required to place on the MDI.
Dim varImage As Image = Image.FromFile("E:\Projects\Tablet\Arjuna\Images\Background-Landsc.jpg")
' If there are no space, no point of drawing so exit the sub procedure.
If Me.ClientSize.Width = 0 OrElse Me.ClientSize.Height <= Me.StatusBar1.Height Then Exit Sub
' Creating the bitmap image which is used as the background image.
Dim varBitmap As New Bitmap(Me.ClientSize.Width, Me.ClientSize.Height - Me.StatusBar1.Height, Imaging.PixelFormat.Format24bppRgb)
' Getting the drawing surface of the bitmap (varBitmap).
Dim varGraphics As Graphics = Graphics.FromImage(varBitmap)
' Sets the interpolation mode of the graphics object. Interpolation mode determines how intermediate values between two endpoints are calculated
varGraphics.InterpolationMode = Drawing2D.InterpolationMode.HighQualityBicubic
' Draw the image on the bitmap.
varGraphics.DrawImage(varImage, 0, 0, varBitmap.Width, varBitmap.Height)
' Assign the bitmap as the background image of the MDI form.
Me.BackgroundImage = varBitmap
End Sub

If you don't have a status bar in your MDI form just remove the codes that are coloured Magenta.

Method 1 does have some problems like, it is visible when the background image re sizes and also I found that if you make the form small sometimes the background image will not be re sized.
So I prefer Method 2.

Wednesday, May 02, 2007

Silverlight is here

Microsoft has released a plug-in for delivering next generation media experiences and rich interactive applications for web. This is a cross-browser, cross-platform plug-in which facilities the WPFs' rich look and feel.
Silverlight was formerly known as WPF/E.
Besides me telling you about Silverlight go to Microsoft and see for your self, you also can find some nice samples done.

http://www.microsoft.com/silverlight

Tuesday, May 01, 2007

Debugging Stored Procedures

I know that all of you might have tried debugging Stored Procedures (SPs). But I know there are also people who are searching how to do this so I thought I needs to put an article on how to do this.

Please note that to dubug SPs you need to use an account which is a member of sysadmin role.

1.) SQL Server 2005
When you install SQL Server 2005 it will install SQL Server Business Intelliegnce Development Studio. You can use this or Visual Studio to debug SPs.

Start any prefered application from the above two and go to View menu and click on Server Explorer (or press Ctrl + Alt + S) to display the Server Explorer.

Now create a connection to the database where the SP you want to debug is located (Right click on Data Connections and click Add Connection...).

When the connection is added to the list expand the list and browse to the Stored Procedures and right click on the SP which you want to debug and select Step Into Stored Procedure. Now if the SP is requiring any parameters a dialog box will be displayed to enter the values. After entering them click Ok to run into the SP.

After you were taken into the debug mode you can use the same keys to debug SPs as if you are debugging application code (Step Into - F11, Step Over - F10, Step Out - Shift + F11).
If you like to use the Debug toolbar activate it by Clicking on View -> Toolbars -> Debug.

If you want to know what the buttons does, just hover on top of the tool so a helpful tooltip will appear. If you need more information press on the help tool which is the right most tool with a yellow question mark.

Also remember that all the additional features are also available for you to use (as if the Immediate Window) while debugging.


2.) SQL Server 2000
In SQL Server 2000 you have to use the SQL Query Analyzer to debug SPs.
Start SQL Query Analyzer and click on Tools -> Object Browser -> Show/Hide (or F8) to display the Object Browser if it is not already shown.

Now expand the database where the required SP is located and right click on the SP which you needs to debug inside Stored Procedures node.

Click on the Debug... to start the debugging, if the SP requires any values for its parameters a window will pop up to enter the values. After entering the values click on Execute.

When debugging starts you can use the available controls or shortcut keys (Step Into - F11, Step Over - F10, Step Out - Shift + F11, Run to Cursor - Ctrl + F10) to debug through the SP.



If you want to know what the buttons does, just hover on top of the tool so a helpful tool tip will appear. If you need more information press on the help tool which is the right most tool with a yellow question mark.

Sunday, April 29, 2007

Number Game - Excellent Number Trick

Did anyone visit http://www.learnenglish.org.uk/games/magic-gopher-central.swf. If you like simple but excellent tricks visit and enjoy.

If you did play the game visiting the british counsil website, some of you may have picked it up what is happening, but if you didn't then read on.

In this game what they are using is a simple number trick.
It is like this,

You first have to think of a two digit number.
For example we'll take 10,

If you add 1 and 0 then it'll be 1.
Now reduce it from 10 so the result will be 9.

To explain the process i'll take more numbers,
10 -> 1+0=1 -> 10-1 =09
13 -> 1+3=4 -> 13-4 =09
15 -> 1+5=6 -> 15-6 =09
19 -> 1+9=10 -> 19-10 =09

21 -> 2+1=3 -> 21-3 =18
25 -> 2+5=7 -> 25-7 =18
27 -> 2+7=9 -> 27-9 =18

33 -> 3+3=6 -> 33-6 =27
36 -> 3+6=9 -> 36-9 =27
38 -> 3+8=11 -> 38-11 =27

Did any one note anything?
There is always a common number coming for each 10 numbers from 0-9.
00-09 the common number is 0.
10-19 the common number is 9.
20-29 the common number is 18.
30-39 the common number is 27.
40-49 the common number is 36.
50-59 the common number is 45.
60-69 the common number is 54.
70-79 the common number is 63.
80-89 the common number is 72.
90-99 the common number is 81.

In the game what they does is they will put an image for all these numbers in there symbol board. So what ever number we guessed we will end up with one of the above numbers and all the above numbers will have the same image on them. Then the program displays the common image as our symbol saying that it read our mind.
The game ends like that but anyway I think it is better if people had the ability of reading other peoples' minds.

Wednesday, April 25, 2007

Sending EMail using SQL Server 2005

Did anyone try sending email using SQL Server?
Ok I tried and got it working, so if you would like to know more please read on. If you face problems while trying this please contact me and I will try helping you sort out the problem.

SQL Server (SQLS) provides Stored Procedure (SP) s which we can use to send mails using SQL. There are two methods in SQL Server 2005.

1.) SQL Mail - SQL Server 2000 provides SQL Mail (The SP you have to use in this method is xp_sendmail.). This method is also supported in SQL Server 2005 (SQLS 2005). But this will not be supported in the next versions of SQL Server. If you are doing a new development the best is to use the Database Mail. Do know that SQL Mail is not supported on 64 Bit versions of SQLS and also remember that SQL Mail is not installed in SQLS 2005 by default.

2.) Database Mail - SQL Server 2005 provides a new method called Database Mail (The SP you have to use in this method is sp_send_dbmail.). This is new to SQL Server 2005 so I will be discussing how to send mails using Database Mail in the following sections of this article using a SP which I wrote as an example. The following SP will read a table and will generate a HTML report according to the data and will send it as an EMail.

Please note that in this article HTML, HEAD, BODY starting and ending tags are proceeded with _ since they are not allowed to be used inside of posts.

--****************************************************************************************
-- Original Developer - Arjuna Chiththananda
-- Description - Sending EMails in SQL Server.
-------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [OWNER].[sp_SendEMail]
(
@ReportDate DATETIME
)
AS
BEGIN
SET NOCOUNT ON;
-- Declaring main variables required.
-- HTMLReport is used to store the report (web page) generated using HTML.
DECLARE @varReferenceNumber VARCHAR(10), @varPassengerName VARCHAR(100), @varDepartment VARCHAR(30), @varTravelInformation VARCHAR(100), @HTMLReport VARCHAR(MAX), @varSubject VARCHAR(75)
-- Starting the creation of web page report.
SET @HTMLReport = '<_html><_head><_title>Ar'
+ '<_body>

SQL Server EMail System

Email Report

'
+ '
Report Date -: ' + CONVERT(VARCHAR(15), @ReportDate, 105) + '
'
+ '

'
-- Checking a view for data.
IF (SELECT COUNT (*) FROM [OWNER].[vw_ListPassengers] WHERE ReserveDate BETWEEN @ReportDate AND @ReportDate) = 0
BEGIN
SET @HTMLReport = @HTMLReport + 'There are no records to display.'
END
ELSE
BEGIN
-- Creating the table required to format the displayed data.
SET @HTMLReport = @HTMLReport + '

'
+ ' '
+ ' '
+ ' '
+ ' '
+ ' '
-- Declaring a cursor to loop through data.
DECLARE curPsngrList CURSOR
FOR SELECT [RefId], [PassengerName], [Department], [Address] FROM [OWNER].[vw_ListPassengers] WHERE ReserveDate BETWEEN @ReportDate AND @ReportDate
OPEN curPsngrList
FETCH NEXT FROM curPsngrList INTO @varReferenceNumber, @varPassengerName, @varDepartment, @varTravelInformation
WHILE @@FETCH_STATUS = 0
BEGIN
-- Adding rows to the table for the records found.
SET @HTMLReport = @HTMLReport + ' '
+ ' '
+ ' '
+ ' '
FETCH NEXT FROM curPsngrList INTO @varReferenceNumber, @varPassengerName, @varDepartment, @varTravelInformation
END
-- Closing and deallocating the cursor.
CLOSE curPsngrList
DEALLOCATE curPsngrList
END
-- We are done with the report web page. Ending the web page properly.
SET @HTMLReport = @HTMLReport + '
Reference Number
Passenger Name
Department
Travel Information
' + @varReferenceNumber + '
' + @varPassengerName + '
' + @varDepartment + '
' + @varTravelInformation + '

'
-- Enabling Database Mail.
-- With the default settings Database Mail is not enabled to increase the performance and security of the server.
-- So before using Database Mail we have to enable it on the SQL Server. This is done by setting the configuration option 'Database Mail XPs'. But because is it an advanced configuration option first we have to enable showing advanced options by running the following.
EXEC master.dbo.sp_configure 'show advanced option', '1'
-- To apply the changes run RECONFIGURE.
RECONFIGURE
EXEC master.dbo.sp_configure 'Database Mail XPs', 1
-- On both above options 1 is used to enable the option and 0 is used to disable the option.
RECONFIGURE
-- Now we have enabled Database Mail now we have to start Database Mail.
EXEC msdb.dbo.sysmail_start_sp
-- To send EMails first you have to setup Database Mail. First you have to create an account.
-- I am getting the mail account settings from a table, following are the required variables.
DECLARE @varUserName NVARCHAR(128), @varPassword NVARCHAR(128), @varEMailAddress NVARCHAR(128), @varDisplayName NVARCHAR(128), @varMailServerName NVARCHAR(128), @varPort INT
-- Filling the required variables with the table data.
SELECT TOP 1 @varUserName=EMServerUserName, @varPassword=EMServerPassword, @varEMailAddress=EMSenderAddress, @varDisplayName=EMSenderName, @varMailServerName=EMServerName, @varPort=EMServerPort FROM [OWNER].[tblEMailServer] ORDER BY EMServerOrder, EMServerName
-- Checking whether the account already exists.
-- We can check for existing accounts in the sysmail_account table in the msdb database. As the final section of this article I will list down number of tables which will come in handy while you use Database Mail. (Note my account name is accArjuna.)
IF (SELECT COUNT(*) FROM [msdb].[dbo].[sysmail_account] WHERE [name] = 'accArjuna') = 0
BEGIN
BEGIN TRY
-- If the account does not exist we have to create the account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'accArjuna', -- Account name.
@username = @varUserName, -- Account user name to log in to the SMTP server ('Arjuna'),
@password = @varPassword, -- Password of the above mentioned account ('123').
@description = 'Arjuna Chith on Database Mail.', -- Description of the account.
@email_address = @varEMailAddress, -- EMail address to use to send the mails ('Arjuna@Company.Au').
@display_name = @varDisplayName, -- Name to display in the messages ('Arjuna Chiththananda').
@mailserver_name = @varMailServerName, -- SMTP mail server name ('Server.My.Company').
@port = @varPort -- Port of the mail server if it is diferent from 25 (25 is the default value).
END TRY
BEGIN CATCH
END CATCH
END
-- If the account already exists we will update it, since sometimes the details might have changed.
ELSE
BEGIN
EXECUTE msdb.dbo.sysmail_update_account_sp
@account_name = 'accArjuna', -- Account name.
@username = @varUserName, -- Account name to log in to the SMTP server ('Arjuna'),
@password = @varPassword, -- Password of the above mentioned account ('123').
@description = Arjuna Chith on Database Mail.', -- Description of the account.
@email_address = @varEMailAddress, -- EMail address to use to send the mails ('Arjuna@Company.Au').
@display_name = @varDisplayName, -- Name to display in the messages ('Arjuna Chiththananda').
@mailserver_name = @varMailServerName, -- SMTP mail server name ('Server.My.Company').
@port = @varPort, -- Port of the mai,server if it is diferent to 25 (25 is the default value).
@replyto_address = NULL, -- We can put a reply to address different from above address if we needs to make all replies to the send mail directed to another mail box.
@mailserver_type = 'SMTP', -- Mail server type. Currently only SMTP is supported.
@use_default_credentials = 0, -- Credentials to be used. (1 = SQL Server credentials, 0 = users the username and password given in the above and null = anonymous authentication.)
@enable_ssl = 0 -- If your mail server needs Secure Sockets Layer (SSL) then set it to 1 or make it 0. By making this 1 Database Mail will encrypt communication using SSL.
-- Instead of updating if you want we can delete and add the account again. That part is commented here because you don't need to have both update and delete + add. But if you want you can use this method also.
-- EXECUTE msdb.dbo.sysmail_delete_account_sp
-- @account_name = 'accArjuna' -- When deleting an account we need to give the account id or the name. I prefer giving the name.
-- Now we have deleted the account we will add the modifications as a new account.
-- EXECUTE msdb.dbo.sysmail_add_account_sp
-- @account_name = 'accArjuna', -- Account name.
-- @username = @varUserName, -- Account user name to log in to the SMTP server ('Arjuna'),
-- @password = @varPassword, -- Password of the above mentioned account ('123').
-- @description = 'Arjuna Chith on Database Mail.', -- Description of the account.
-- @email_address = @varEMailAddress, -- EMail address to use to send the mails ('Arjuna@Company.Au).
-- @display_name = @varDisplayName, -- Name to display in the messages ('Arjuna Chiththananda').
-- @mailserver_name = @varMailServerName, -- SMTP mail server name ('Server.My.Company').
-- @port = @varPort -- Port of the mail server if it is diferent from 25 (25 is the default value).
END
-- Now we have to add a Profile.
-- First check whether the profile exists.
IF (SELECT COUNT(*) FROM [msdb].[dbo].[sysmail_profile] WHERE [name] = 'prfArjuna') = 0
BEGIN
BEGIN TRY
-- If not add the profile.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'prfArjuna', -- Profile name to be used.
@description = 'Arjunas Profile' -- Profile description to be used.
END TRY
BEGIN CATCH
END CATCH
END
-- Now we have added an account and a profile we need to merge both these that is done here after checking whether it exists.
IF (SELECT COUNT(*) FROM [msdb].[dbo].[sysmail_profileaccount] WHERE [profile_id] IN (SELECT [profile_id] FROM [msdb].[dbo].[sysmail_profile] WHERE [name] = 'prfArjuna') AND [account_id] IN (SELECT [account_id] FROM [msdb].[dbo].[sysmail_account] WHERE [name] = 'accArjuna')) = 0
BEGIN
BEGIN TRY
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'prfArjuna', -- Profile to be linked.
@account_name = 'accArjuna', -- Account to be linked.
@sequence_number = 1 -- Determines the order of account usage if you have more than one account. If the first account fails it will try sending mails with the second one in the order.
END TRY
BEGIN CATCH
END CATCH
END
-- Grants the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.
BEGIN TRY
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'prfArjuna', -- Profile name to set.
@principal_name = 'public', -- Makes this profile a public profile.
@is_default = 1 -- Makes the profile the default profile for the principal.
END TRY
BEGIN CATCH
END CATCH
-- Fire RECONFIGURE to make the changes apply to SQL Server.
RECONFIGURE
-- The variable to hold the recipient list of our mail.
DECLARE @varRecipients VARCHAR(MAX)
-- Filling recipient list from the table (Note here I am getting the first one in the table (Which will be the last entry added).)
SELECT TOP 1 @varRecipients=EMailAddress FROM [OWNER].[tblEMail] WHERE EMailActive=1 ORDER BY EMailId DESC
-- Setting the subject of the EMail.
SET @varSubject = 'EMailing in SQL Server on ' + CONVERT(VARCHAR(15), @ReportDate, 105) + '.'
-- We have done the hard part so we will now send our mail.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'prfArjuna', -- Profile name to be used when sending mail.
@recipients = @varRecipients, -- Recipients who will get our mail. ('Arjuna@Company.Au).
@subject = @varSubject, -- Adding the subject to the mail.
@body_format = 'HTML', -- Format of the mail, this could be Text or HTML.
@body = @HTMLReport -- Message body of the mail.
-- If you want to mail an output of a query you can give your query as follows.
--@query='SELECT RefId FROM FlightBooking.SE.Vw_Report_ListPassengers'
END
--****************************************************************************************
Now we have finished the SP remember to run this sp under a sysadmin or serveradmin role to make sure the reconfigurations will take effect.
When we do Database Mail related things like creating accounts, profiles etc everything will be stored in the msdb. If you would like to query out the statuses, following tables might be helpful.
-- Lists down all the created mail accounts.
SELECT * FROM [msdb].[dbo].[sysmail_account]
-- Lists down all the attachments sent using Database Mail.
SELECT * FROM [msdb].[dbo].[sysmail_attachments]
-- Lists down all the created attachments using Database Mail.
SELECT * FROM [msdb].[dbo].[sysmail_attachments_transfer]
-- Lists down the configuration settings of Database Mail.
SELECT * FROM [msdb].[dbo].[sysmail_configuration]
-- Lists down all the created mail servers.
SELECT * FROM [msdb].[dbo].[sysmail_server]
-- Lists down the activity log of Database Mail, if any error occurs a log entry will be created.
SELECT * FROM [msdb].[dbo].[sysmail_log]
-- Lists down the mails sent from Database Mail.
SELECT * FROM [msdb].[dbo].[sysmail_mailitems]
-- Lists down the principal accounts configured.
SELECT * FROM [msdb].[dbo].[sysmail_principalprofile]
-- Lists down all the created profiles.
SELECT * FROM [msdb].[dbo].[sysmail_profile]
-- Lists down all the created profile accounts.
SELECT * FROM [msdb].[dbo].[sysmail_profileaccount]
-- Lists down the details of results that were returned from the queries that were run using Database Mail.
SELECT * FROM [msdb].[dbo].[sysmail_query_transfer]
-- Lists down the details of retry attempts made.
SELECT * FROM [msdb].[dbo].[sysmail_send_retries]
-- Lists down all the created mail servers.
SELECT * FROM [msdb].[dbo].[sysmail_server]
-- Lists down the mail server type (SMTP) and some settings.
SELECT * FROM [msdb].[dbo].[sysmail_servertype]

Thursday, April 12, 2007


Getting Date part only from SQL DateTime Value

Even though I have taken date part only from a datetime field earlier I had to struggle for some minutes yesterday, so I thought why I shouldn't put a blog entry on this so it will help me. Also I think that this will help many of you reading my blogs.

I found two ways to accomplish this without using DATEPART. I believe it is not good practice to use DATEPART and assemble the date by ourselves because it will sometimes change the original year, month and day order and at times that will cause some unnecessary problems.

Method 1

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

What is happening here is CAST(GETDATE() AS FLOAT) will cast the date into a floating point number {2007-04-12 22:12:00.837 = 39182.9236936343}.
Then the FLOOR function will return the largest integer part of the parameter. In our case the result of CAST(GETDATE() AS FLOAT) {39182.9236936343}. The result of this will be 39182.
Now I will caste this value again to a DATETIME value. This time, since there is no fraction part in the value that results in the time part to return as 00:00:00.000. {Fraction part represents the time.}
The final result will be 2007-04-12 00:00:00.000.

Method 2

SELECT CONVERT(NVARCHAR(20), GETDATE(), 101)

In this second approach what I am doing is simply Converting the date into a character. The last parameter of the CONVERT function {101} is the style number which will specify the conversion. Value 101 will return the value in the format of MM/DD/YYYY. {To acquire more information about conversion styles refer CONVERT function in SQL Books Online.} The result of this will be 04/12/2007.

One thing to remember when using second method is that the result will be in the format MM/DD/YYYY. So if you are comparing this value with another date you have to be careful to make the other comparing value also in the format of MM/DD/YYYY. Otherwise it will return wrong results.