Data Conversions

taken from http://msdn.microsoft.com/en-us/library/ms187928.aspx

Error:The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

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

Here is the sp that caused the error

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PROC_ARCHIVE_EOM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PROC_ARCHIVE_EOM]
GO
/** ***** Version *****
* $Revision:  $
* $Date: $
* $Author: $
* $Archive: $
*
* Sample use: exec PROC_ARCHIVE_EOM
* Comments : sp to perform the monthly archive of the transactions table.
* */
CREATE PROC PROC_ARCHIVE_EOM
AS
DECLARE @myCheckdate varchar(10)
SELECT @myCheckdate = convert(varchar(10),SuccessDate,101) from dbo.JobsRunlog where JobID = 10
--SELECT @myCheckdate
If @myCheckdate = convert(varchar(10),GETDATE(),101)
BEGIN

BEGIN TRAN TRAN1

---insert into the archive table
INSERT INTO Table_Archive
SELECT p.* FROM Patient_Table p WITH (nolock)
INNER JOIN clients c
ON p.hospital = c.hospitalcode
WHERE c.active = 1
AND
 year(RCVD) = year(getdate()) and month(RCVD)< month(getdate()))

IF @@error <> 0 GOTO  Error_Handler


COMMIT TRAN TRAN1

END


Error_Handler:
RAISERROR('error occurred while archiving the patient table',16,1)
ROLLBACK TRAN TRAN1




Added @@tranCount check to avoid this error

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PROC_ARCHIVE_EOM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PROC_ARCHIVE_EOM]
GO

CREATE PROC PROC_ARCHIVE_EOM
AS
DECLARE @myCheckdate varchar(10)
SELECT @myCheckdate = convert(varchar(10),SuccessDate,101) from data2link.statusUpdates.dbo.JobsRunlog where JobID = 10
--SELECT @myCheckdate
If @myCheckdate = convert(varchar(10),GETDATE(),101)
BEGIN
BEGIN TRAN TRAN1

---insert into the archive table
INSERT INTO Table_Archive
SELECT p.* FROM Patient_Table p WITH (nolock)
INNER JOIN clients c
ON p.hospital = c.hospitalcode
WHERE c.active = 1
AND
year(RCVD) = year(getdate()) and month(RCVD)< month(getdate()))


IF @@error <> 0 GOTO Error_Handler


IF @@TRANCOUNT > 0

 COMMIT TRAN TRAN1


END


Error_Handler:
RAISERROR('error occurred while archiving the patient table',16,1)
IF @@TRANCOUNT > 0
ROLLBACK TRAN TRAN1

To find which processID the current SQLServer is running on

• Fire up the SQL Server configuration Manager
• Double Click on the SQL Server Services
• On the right hand side panel find the SQL SERVER
• Right Click on it , go to properties and the Process ID is listed on the Services tab.