The Rollback Transaction request has no corresponding Begin Transaction

Sometimes while using transaction in SQL Sesrver you get the following error:


The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.


The main reason for this error is that there is no Transaction is running but you had issued Rollback or Commit transaction.

When my Database is last accessed

The below script uses sys.dm_db_index_usage_stats to get timestamps for the last read and write operations on a database. 


SELECT  DB_NAME(database_id) AS DatabaseName, MAX(CASE WHEN ISNULL(last_user_seek,'1900-01-01') >= ISNULL(last_user_scan,'1900-01-01') AND ISNULL(last_user_seek,'1900-01-01') >= ISNULL(last_user_lookup,'1900-01-01') THEN ISNULL(last_user_seek,'1900-01-01')  WHEN ISNULL(last_user_scan,'1900-01-01') >= ISNULL(last_user_lookup,'1900-01-01') THEN ISNULL(last_user_scan,'1900-01-01')   ELSE ISNULL(last_user_lookup,'1900-01-01') END) AS LastReadOperation, MAX(last_user_update) AS LastWriteOperation  
FROM sys.dm_db_index_usage_stats GROUP BY DB_NAME(database_id) 

Msg 157 - An aggregate may not appear in the set list of an UPDATE

If you are trying to update a column using 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 understand this you try the following example. Suppose you have two tables called ProductMaster and StockTransaction respectively. And ProductMaster table have the fields ProductID, ProductName, Stock, LastTransactionDate. And StockTransaction table have the fields called ProductID, Quantity, TransactionDate


The first table contains all the Products with the current Stock and the last transaction date. The second table contains all the transactions performed on those Products. You are trying to update the Stock and Last Transaction Date columns of your ProductMaster table using the following UPDATE statement:


UPDATE A
SET [Stock] = SUM([Quantity]),
[Last TransactionDate] = MAX([TransactionDate])
FROM [dbo].[ProductMaster] A INNER JOIN [dbo].[StockTransaction] B
ON A.[ProductID] = B.[ProductID]


To update a table with the aggregate values of another table you should use a sub-query:


UPDATE A
SET [Stock] = B.[Quantity],
A.[Last Transaction Date] = B.[Last Transaction Date]
FROM [dbo].[ProductMaster] A INNER JOIN 
(SELECT [ProductID], SUM([Quantity]) AS [Quantity],
MAX([Transaction Date]) AS [Last Transaction Date]
FROM [dbo].[StockTransaction]
GROUP BY [ProductID]) B
ON A.[ProductId] = B.[ProductID]

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


Ref: http://sqlerrormessages.blogspot.in/

advantages and disadvantages of Indexed views

Advantages of indexed views

• To Improve the performance of select queries
• Index data on columns on multiple tables
• The data from multiple tables is denormalized and helps in fastening the queries that fetch data
• Avoid Complex joins at run time and make the joined data readyly available
• Joins that are frequently used can be converted into indexed views and thus reducing the query resopnse time of multiple queries
• Advantage of having Higher Disk Space can be converted into having high query performance using Indexed views

Disadvantages of indexed views

• Increases the disc space used by the database as the views are created with physical data
• Slows down the performance of the insert,update,delete statements on the tables used in indexed views.