How to sent email by using Sql Server cdo cdosys cdonts Microsoft sql server

How to sent email by using Sql Server

Sql Mail System provides an easy way to sent and receive emails using microsoft sql server. But you should have to install a MAPI susystem in the server which is comming automatically when you install windows NT. If you are using windows 2000 you should have to install an mapi client like microsoft outlook

There are other methods to send emails by using Microsoft sql server. You can configure CDONT(Collaboration Data Objects for NT Server),CDOSYS(Collaboration Data Objects for Windows 2000 ) to send emails directly from sql server. CDOSYS is available from windows 2000 and CDONTS is not supported by windows server 2003 and later Operating systems.


Confugure CDO for sending email using Microsoft Sql Server
CDONTS is a mail transfer protocol. CDONTS sends mail through SMTP. If you do not have SMTP you can use the SMTP.To use CDONTS to send email from sql server do the following steps.

1. Install iis in your coputer which running sql server.

2. Specify SMTP mail server as your "smart-host".

3. Create a stored procedure which can send mail.

When using CDONTS you need not have to use outlook in your server. To confugure smart host follow the steps below

1. start->programs->administrative tools->internet service manager

2. It will open the IIS manager a tree. Right click on the Default SMTP Virtual Server. Select properties.

3. Locate delivery tab->Advanced

4. Type SMTP mail server name in smart host textbox.

5. Make sure SMTP service is running.

Sample Stored Procedure which is sending CDONTS mail

CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@CC varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID


Send Email using CDOSYS

CDOSYS provides enhancement on CDONTS mail. CDOSYS can configure programmatically so it is flexible.

Sample Stored procedure sending CDOSYS Mail



IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'cdosysmail_failures' AND type='U')) DROP TABLE [dbo].[cdosysmail_failures]
GO
-- Create new cdosysmail_failures table
CREATE TABLE [dbo].[cdosysmail_failures]
([Date of Failure] datetime,
[Spid] int NULL,
[From] varchar(100) NULL,
[To] varchar(100) NULL,
[Subject] varchar(100) NULL,
[Body] varchar(4000) NULL,
[iMsg] int NULL,
[Hr] int NULL,
[Source of Failure] varchar(255) NULL,
[Description of Failure] varchar(500) NULL,
[Output from Failure] varchar(1000) NULL,
[Comment about Failure] varchar(50) NULL)
GO

IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'sp_send_cdosysmail' AND type='P')) DROP PROCEDURE [dbo].[sp_send_cdosysmail]
GO

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/*********************************************************************

This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)

--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OACreate')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OACreate')
RETURN
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty sendusing')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty sendusing')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', cdoSMTPServerName
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty smtpserver')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty smtpserver')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END

-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Update')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Update')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END

-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty To')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty To')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END

EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty From')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty From')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END

EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Subject')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Subject')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty TextBody')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty TextBody')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod Send')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod Send')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END


-- Do some error handling after each step if you have to.
-- Clean up the objects created.
send_cdosysmail_cleanup:
If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
BEGIN
EXEC @hr=sp_OADestroy @iMsg

-- handle the failure of the destroy if needed
IF @hr <>0
BEGIN
select @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OADestroy')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

-- if sp_OAGetErrorInfo was successful, print errors
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OADestroy')
END

-- else sp_OAGetErrorInfo failed
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
END
ELSE
BEGIN
PRINT ' sp_OADestroy skipped because @iMsg is NULL.'
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, '@iMsg is NULL, sp_OADestroy skipped')
RETURN
END

"Specified SQL server not found" or "Error Locating Server/Instance Specified" On windows vista when connecting to sql server in remote server , .net

"Specified SQL server not found" or "Error Locating Server/Instance Specified" On windows vista when connecting to sql server in remote server

If you are trying to connect a client computer running windows vista or windows server 2008 to a named instance of sql server. In this scenario the connection may fail. If you are using windows data access component 6.0 to connect to named instance of sql server, you will receive the following error message.

[DBNETLIB]Specified SQL server not found.
[DBNETLIB]ConnectionOpen (Connect()).

And if you are using sql native instance to connect with named instance of sql server you will receive the following error message.

[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
[SQL Native Client]Login timeout expired.


In connecting the client sends a UDP request to the Ip address of the named instance. Then sql browser returns a Udp response which contains information about endpoints. However, in the UDP response packet, the source IP address may not be the IP address to which the UDP request packet was sent. If the named instance is a failover cluster instance, the source IP address is the IP address of the physical computer instead of the virtual IP address of the remote server. If the remote server has multiple IP addresses, the source IP address may be any of the IP addresses that are assigned to the remote server.

There are lots of ways to solve this error.

Specify the TCP portname or named pipename in the connection string

Some other ways also there to solve this error

In Windows Firewall with Advanced Security in Control Panel, create an outgoing rule for the application that connects to SQL Server. To do this, follow these steps:

1. Take the control pannel.

2.Double click on administrative tool

3. In the administrative tools double click Windows Firewall with Advanced Security.

4. In Windows Firewall with Advanced Security, click Outbound Rules, and then click New Rule.

5. Click Program, and then click Next.

6. Click This program path, specify the path of the application, and then click Next.

7. Click Allow the connection, and then click Next.

8. Complete the steps of the New Outbound Rule Wizard

In Windows Firewall with Advanced Security in Control Panel, create an incoming rule that allows for traffic from all possible IP addresses of the remote server or from all possible IP addresses that are configured for the failover cluster instance. To do this, follow these steps:

1. In Control Panel, double-click Administrative Tools.

2. In Administrative Tools, double-click Windows Firewall with Advanced Security.

3. In Windows Firewall with Advanced Security, click Inbound Rules, and then click New Rule.

4 Click Custom, and then click Next.

5. Click All programs, and then click Next.

6. In the Protocol type list, click Any, and then click Next.

7. Under Which remote IP addresses does this rule match, click These IP addresses, and then click Add.

8. In the IP Address dialog box, type one of the IP addresses under This IP address or subnet, and then click OK.

9. To add other IP addresses, repeat steps 7 through 8, and then click Next.

10. Click Allow the connection, and then click Next.

11. Complete the steps of the New Inbound Rule Wizard

Unable to Connect to SQL Server. sql server protocol asp.net sqlconnection sql c#.net sms system management services

Unable to Connect to SQL Server


For system management services we require sql server named pipes network support.
Because system management services have to communicate with sql server which stores the system management server database. SMS utilizes names pipes by default.

It is important that the client net library which establishes the connection much match the server netlibrary which accepts the connection. It is important that both client and server must use the same protocol.

If named pipes network support is not installed then system management services may failed to start and produce the following error message.

Unable to connect to SQL Server.

To correct follo the following steps

1. Start the sql client configuration utility.

2. Select net Library tab.

3. Select Named pipes in the default network drop-down combobox.

4. Click done


To check sql setup follow these steps

1. Run Sql server setup and click continue.

2. Select change network support and choose continue.

3. Select the network protocol which you wants to use in case named pipes.

4. The named pipe name dialog box appears. Click continue

5. Select Exit

Error 8645: A time out occurred while waiting for memory resources to execute the query. Re-run the query. , sql server,sql,2005,select,query,asp.net

Error 8645: A time out occurred while waiting for memory resources to execute the query. Re-run the query.

The programmers are getting this error message when there is high workload on the server. And servers are experiencing high memory pressure.

In this error theire are some additional symptoms also.

1. When connecting to server will get the error message as "Login Failed".

2. Will get disconnected from server.

3. CPU usage will be very high.

4. if running "select * from sysprocesses" SPIDs will have a waittype of 0x40 or 0x0040 and a last_waittype of RESOURCE_SEMAPHORE.

5. The System Monitor object SQLServer:Memory Manager displays a non-zero
value for Memory Grants Pending.

6. SQL Profiler displays the event "Execution Warnings" that includes
the "Wait For Memory" or the "Wait For Memory Timeout" text.


Reasons for this error is memory intensive queries are getting qued and are not getting resources before timout period. And after timout period and getting timout. By default query wait period is -1 by setting non-negative number you can improve the query wait time.

Other reasons for this errors are not properly optimised queries, memory allocation for sql server is too small.

Solutions for this error include the following.

1. Optimise the performance of queries using sql profiler.

2. Distrybution statistics should be uptodate.

3. Watch the system monitor trace to see the memory usage of sql server.

4. If you are running SQL Server 7.0, test disabling parallelism for SQL Server 7.0 by turning the max degree of parallelism configuration option off.



Happy Programming

Error converting data type varchar to int

Error converting data type varchar to int

When inserting or updating data into a table the error will as "Error converting data type varchar to int". As the name indicates this error is comming because the programmer have a field which is integer and while querying he passes any data which is not convertable to int

Solutions

The first and best solution is to make sure that the data passing to sql server is copatible with datatype. ie if passing a string to an int field it should be convertible to integer say '0','222' etc

And if trying to copy data from one table to another there is chance to fire this error if either the column mapping is not correct or theire is null in the source field

If theire is null in the source field use isnull function. See the following query

INSERT INTO SrcTbl(id)SELECT ISNULL(id,0) FROM destTBL


HAPPY PROGRAMMING

Msg 157 - An aggregate may not appear in the set list of an UPDATE statement.,sql,server

If you are trying to set one of your columns using an UPDATE statement which uses an aggregate function like SUM,MAX or MIN etc the following error will come.

Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE
statement.

To know this you jist try the following example. Suppose you have two tables called AccountBalance and AccountTransaction respectively. And AccountBalance table have the fields AccountNumber,AccountBalance,LastTransactionDate. And AccountTransaction table have the fields called AccountNumber,TransactionAmount,TransactionDate


The first table contains all the accounts in your company with the current account balance and the last transaction date. The second table contains all the transactions performed on those accounts. You are trying to update the Account Balance and Last Transaction Date columns of your Account Balance table using the following UPDATE statement:

UPDATE A
SET [AccountBalance] = SUM([TransactionAmount]),
[Last TransactionDate] = MAX([TransactionDate])
FROM [dbo].[AccountBalance] A INNER JOIN [dbo].[AccountTransaction] B
ON A.[AccountNumber] = B.[AccountNumber]

To update a table with the aggregate values of another table you have to use a sub-query as can be seen from the following UPDATE statement:

UPDATE A
SET [Account Balance] = B.[Account Balance],
[Last Transaction Date] = B.[Last Transaction Date]
FROM [dbo].[Account Balance] A INNER JOIN
(SELECT [Account Number], SUM([Transaction Amount]) AS [Account Balance],
MAX([Transaction Date]) AS [Last Transaction Date]
FROM [dbo].[Account Transaction]
GROUP BY [Account Number]) B
ON A.[Account Number] = B.[Account Number]

Invalid operator for data type. Operator equals add, type equals text.,sql,asp.net jobs,india jobs,

Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type.
Operator equals add, type equals text.

I got the above error when i tried to concatenate two columns of Text datatype. I can explain this with the help of an example

I have a table called StudentComments with fields StudentID,CommentsOnTeacher,
CommentsOnSubject. If we are running the following se;ect statement will get error

SELECT [StudentID],
[CommentsOnTeacher] + [CommentsOnSubject] AS [AllComments]
FROM [dbo].[StudentComments]

To work around this error you need to CAST the TEXT column into VARCHAR first before concatenating the columns. The following query will avoid this error:

SELECT [StudentID],
CAST(CAST([CommentsOnTeacher] AS VARCHAR(8000)) +
CAST([CommentsOnSubject] AS VARCHAR(8000)) AS TEXT)
AS [AllComments]
FROM [dbo].[StudentComments]

The only drawback with this work around is that you are limiting both TEXT columns to 8000 characters each. One other way to work around this is to do the concatenation in the client application instead of having SQL Server do the concatenation.