Pages

Tuesday 26 February 2013

Send SMTP Email using SQL Server

Many times it is needed to send a email from the database. The important reason is that you do not
need to pull the data in front end and then send emails from front end.
Also if the database server and application server are separate, it takes of the load from the application server.

Collaboration Data Objects (CDO)
 
For sending emails through SMTP Server I will be using Collaboration Data Objects (CDO).
CDO are part of Windows and are useful in sending SMTP Emails.
For more information on CDO Read here.

In SQL Server 2000, I’ll create a stored procedure that will be used to send emails using CDO.
I’ll explain how to send emails using GMAIL SMTP Server.

Here I have created a stored procedure sp_send_cdosysmail which accepts the following parameters
 
Parameter
Relevance
@from
Email Address of the Sender
@to
Email Address of the Recipient
@subject
Subject of the Email
@body
Body of the Email
@bodytype
Type of Body (Text or HTML)
@output_mesg
Output parameter that returns the status (Success / Failed)
@output_desc
Output parameter that returns the Error description if an error occurs


GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE PROCEDURE [dbo].[sp_send_mail]
            @from varchar(500) ,
            @to varchar(500) ,
            @subject varchar(500),
            @body varchar(4000) ,
            @bodytype varchar(10),
            @output_mesg varchar(10) output,
            @output_desc varchar(1000) output
AS
DECLARE @imsg int
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(500)

In the above SQL Snippet I have created the stored procedure and declared some variables that will be used later.

Create an OLE Instance of CDO

EXEC @hr = sp_oacreate 'cdo.message', @imsg out


SendUsing

SendUsing Specifies Whether to send using port (2) or using pickup directory (1)

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/
sendusing").value','2'


smtpserver

Specify your SMTP Server that you will use. Here I am using gmail SMTP Server.

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/
smtpserver").value', 'smtp.gmail.com'
 

sendusername

Specify the sender’s email address here. The account that will be used to send emails.

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/
sendusername").value', sender@gmail.com'


sendpassword

Specify the password of the account here.

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/
sendpassword").value', 'xxxxxxxxxxx'


smtpusessl

Specify where the SMTP server requires SSL (True) or not (False)

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/
smtpusessl").value', 'True'
 


smtpserverport

Specify the Port Number foy your SMTP Server (465 or 587)

EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/
smtpserverport").value', '587'
 
 
smtpauthenticate
Specify the Type of Authentication Required None (0) / Basic (1) 
EXEC @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/
smtpauthenticate").value', '1'
 


Send Email

Execute the OLE object to send email

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.update', null
EXEC @hr = sp_oasetproperty @imsg, 'to', @to
EXEC @hr = sp_oasetproperty @imsg, 'from', @from
EXEC @hr = sp_oasetproperty @imsg, 'subject', @subject
EXEC @hr = sp_oasetproperty @imsg, @bodytype, @body
EXEC @hr = sp_oamethod @imsg, 'send', null
 


Error Handling

Below snippet is checking if the mail is send successfully. If not it captures the Error message and the
Error Description in the output variables

SET @output_mesg = 'Success'
IF @hr <>0
      SELECT @hr
      BEGIN
            EXEC @hr = sp_oageterrorinfo null, @source out, @description out
            IF @hr = 0
            BEGIN
                  set @output_desc =  @description
            END
      ELSE
      BEGIN
            SET @output_desc = ' sp_oageterrorinfo failed'
      END
      IF not @output_desc is NULL
                  SET @output_mesg = 'Error'
END


Destroy the OLE Object Instance

EXEC @hr = sp_oadestroy @imsg
 


Calling and Execute the Stored Procedure

Below I am calling the Stored Procedure and passing the parameters.
Note: the Bodytype can be HTML (htmlbody) or Text (textbody)

DECLARE @out_desc varchar(1000),
        @out_mesg varchar(10)
 
EXEC sp_send_mail 'sender@gmail.com',
      'receiver@gmail.com',
      'Hello',
      '<b>This is s Test Mail</b>',
      'htmlbody', @output_mesg = @out_mesg output,
      @output_desc = @out_desc output
 
PRINT @out_mesg
PRINT @out_desc
 


Enable OLE Automation in SQL Server 2005

OLE Automation is disabled by default in SQL Server 2005 hence to make this stored procedure work you will need to run the following script.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Tip Pass table name dynamically to SQL Server query or stored procedure

DECLARE @Table_Name sysname, @DynamicSQL nvarchar(4000)
SET @Table_Name = 'Employees'
SET @DynamicSQL = N'SELECT * FROM ' + @Table_Name
EXECUTE sp_executesql @DynamicSQL
 
The above query is dynamically build and executed on the table based on the table name that is passed.
 
If you need to do the above with a stored procedure you can do in the following way
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Dynamic_SP
      @Table_Name sysname
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @DynamicSQL nvarchar(4000)
      SET @DynamicSQL = N'SELECT * FROM ' + @Table_Name
      EXECUTE sp_executesql @DynamicSQL
END
GO

And to execute the stored procedure
EXEC Dynamic_SP 'Employees'
 

Insert Excel Data into a SQL Server table using OPENROWSET

There are many ways to load excel data into SQL Server database, using DTS, BULK import, SSIS and many others. In this post I will go with OPENROWSET. This is very useful when we need ad-hoc connection to an OLE DB source.
Here below is the image of the excel file taken for demonstrate.

Here is the script to import the file into SQL Server database using OPENROWSET.

SELECT  exl.*
INTO #myExcelData
FROM OPENROWSET ('Microsoft.Ace.OLEDB.12.0'
,'Excel 12.0; Database=C:\Import\ExcelDataImport.xlsx; HDR=YES'
,'SELECT * FROM [Sheet1$]') AS exl
GO



Now see the data imported into our table.
1SELECT * FROM #myExcelData
2GO

SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet

Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio – Query Editor.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO



Run following code in SQL Server Management Studio – Query Editor.
USE [AdventureWorks];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO

USE [master] GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 GO

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)".
 
There are multiple reasons and multiple solutions to this problem. I will explain all the ones that I found while researching on the net and which together helped me to solve mine
 
Solution 1
First you need to check whether you have installed the 2007 Office System Driver: Data Connectivity Components which is necessary for Microsoft OLEDB ACE 12.0 driver to work. So if you have not done that download it by clicking the link below.
2007 Office System Driver: Data Connectivity Components
Once you downloaded and installed it on your system restart your machine to see if the issue is resolved for you or not.
 
Solution 2
Check whether the user login that you are accessing has rights on SQL Server Service or not. If not you will have to give your credentials so that the SQL Service runs using your login credentials.
Steps
1. Close SQL Server Management Studio. Type Services.msc in the run command to open the services window
2. Search for SQL Server Service and right click it and select properties as shown below

Search for SQL Server Service and right click properties

3. In the Log On Tab, select This Account. Click on Browse and type your login name there and the click on Check Names

Click on Check names to find your login account

4. Once it finds your login name press OK.
5. Now type your login’s passwords in both the fields as shown below press apply and then ok

Fill your username and password in the fields


6. Restart the services so that the new changes are applied as shown in figure below.
7. Now start SQL Server Management Studio and try to run the query if still not working try a system restart

Restart the SQL Server Service
 
 
 
Solution 3
Run the following Query in your SQL Server Management Studio close it and start again
USE [master] 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
GO 
 

Saturday 23 February 2013

How to align text vertical center in div with css

display: table-cell;
vertical-align: middle;


#box {
    height: 90px;
    width: 270px;
    background: #000;
    font-size: 48px;
    font-style: oblique;
    color: #FFF;
    text-align: center;
    margin-top: 20px;
    margin-left: 5px;
    display: table-cell;
    vertical-align: middle;
}
#wrapper {display:table;}
#cell {display:table-cell; vertical-align:middle;}

Friday 22 February 2013

using SetFocusOnError property in validation control

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected void Button1_Click(object sender, System.EventArgs e) {
        Label1.Text = "Your Country: " +
            TextBox1.Text.ToString() +
            "<br />City: " +
            TextBox2.Text.ToString();
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Validation example: using SetFocusOnError property in validation control</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Font-Size="Large" ForeColor="DarkTurquoise"></asp:Label>
        <br />
        <asp:Label ID="Label2" runat="server" Text="Country Name" AssociatedControlID="TextBox1"></asp:Label>
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        <asp:RequiredFieldValidator
             ID="RequiredFieldValidator1"
             runat="server"
             ControlToValidate="TextBox1"
             ErrorMessage="Input Country Name!"
             SetFocusOnError="true"
             >
        </asp:RequiredFieldValidator>
        <br />

        <asp:Label ID="Label3" runat="server" Text="City Name" AssociatedControlID="TextBox2"></asp:Label>
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <asp:RequiredFieldValidator
             ID="RequiredFieldValidator2"
             runat="server"
             ControlToValidate="TextBox2"
             ErrorMessage="Input City Name!"
             SetFocusOnError="true"
             >
        </asp:RequiredFieldValidator>
        <br />
       
        <asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click" />
    </div>
    </form>
</body>
</html>

how to validate RadioButtonList

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected void Button1_Click(object sender, EventArgs e)
    {
        Label1.Text = "Your favorite: ";
        Label1.Text += RadioButtonList1.SelectedItem.Text.ToString();
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>asp.net RequiredFieldValidator example: how to validate RadioButtonList</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h2 style="color:Green">RadioButtonList Validation</h2>
        <asp:Label
            ID="Label1"
            runat="server"
            Font-Size="Large"
            ForeColor="Crimson"
            >
        </asp:Label>
        <br /><br />
        <asp:Label
            ID="Label2"
            runat="server"
            Text="Favorite"
            Font-Bold="true"
            ForeColor="DodgerBlue"
            >
        </asp:Label>
        <asp:RadioButtonList
            ID="RadioButtonList1"
            runat="server"
            RepeatColumns="3"
            BackColor="DodgerBlue"
            ForeColor="AliceBlue"
            BorderColor="DarkBlue"
            BorderWidth="2"
            >
            <asp:ListItem>CheckBoxList</asp:ListItem>
            <asp:ListItem>TreeView</asp:ListItem>
            <asp:ListItem>Button</asp:ListItem>
            <asp:ListItem>SqlDataSource</asp:ListItem>
            <asp:ListItem>GridView</asp:ListItem>
            <asp:ListItem>Calendar</asp:ListItem>
            <asp:ListItem>BulletedList</asp:ListItem>
        </asp:RadioButtonList>
        <asp:RequiredFieldValidator
            ID="ReqiredFieldValidator1"
            runat="server"
            ControlToValidate="RadioButtonList1"
            ErrorMessage="Select your favorite!"
            >
        </asp:RequiredFieldValidator>
        <br />
        <asp:Button
            ID="Button1"
            runat="server"
            ForeColor="DodgerBlue"
            Text="Submit Favorite"
            OnClick="Button1_Click"
            />
    </div>
    </form>
</body>
</html>

Use asp:Menu and asp:MultiView to create tab control (C#)

<%@ Page Language="C#" %>

<script runat="server">

    protected void Menu1_MenuItemClick(object sender, MenuEventArgs e)
    {
        int index = Int32.Parse(e.Item.Value);
        MultiView1.ActiveViewIndex = index;
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        html
        {
            background-color:silver;
        }
        .tabs
        {
            position:relative;
            top:1px;
            left:10px;
        }
        .tab
        {
            border:solid 1px black;
            background-color:#eeeeee;
            padding:2px 10px;
        }
        .selectedTab
        {
            background-color:white;
            border-bottom:solid 1px white;
        }
        .tabContents
        {
            border:solid 1px black;
            padding:10px;
            background-color:white;
        }
    </style>
    <title>MultiView Tabs</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <asp:Menu
        id="Menu1"
        Orientation="Horizontal"
        StaticMenuItemStyle-CssClass="tab"
        StaticSelectedStyle-CssClass="selectedTab"
        CssClass="tabs"
        OnMenuItemClick="Menu1_MenuItemClick"
        Runat="server">
        <Items>
        <asp:MenuItem Text="Tab 1" Value="0" Selected="true" />
        <asp:MenuItem Text="Tab 2" Value="1" />
        <asp:MenuItem Text="Tab 3" Value="2" />
        </Items>    
    </asp:Menu>
    
    <div class="tabContents">
    <asp:MultiView
        id="MultiView1"
        ActiveViewIndex="0"
        Runat="server">
        <asp:View ID="View1" runat="server">
            <br />This is the first view
            <br />This is the first view
            <br />This is the first view
            <br />This is the first view
        </asp:View>        
        <asp:View ID="View2" runat="server">
            <br />This is the second view
            <br />This is the second view
            <br />This is the second view
            <br />This is the second view
        </asp:View>        
        <asp:View ID="View3" runat="server">
            <br />This is the third view
            <br />This is the third view
            <br />This is the third view
            <br />This is the third view
        </asp:View>        
    </asp:MultiView>
    </div>
    
    </div>
    </form>
</body>
</html>


Refer this link
http://www.java2s.com/Code/ASP/Components/UseaspMenuandaspMultiViewtocreatetabcontrolC.htm