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.

error:process could not execute 'sp_replcmds' error on Publisher with Remote Distributor

Today the replication failed on our Publisher. We have a transactional replication running from Publisher to the  Subscriber and they  are in two differnt machines, both using  SQL 2008 R2. The exact error we got was

1. Replication failed: process could not execute 'sp_replcmds' error on Publisher with Remote Distributor
2. The database owner SID recorded in the master database differs from the database owner SID recorded in database 'databasename'

Tried  the following step with success.

 --STEP 1

exec sp_changedbowner [domain\user]

This made the [domain\user]  account the owner and mapped it to dbo 

--STEP 2

In SSMS find the replication and expand it
Expand the local publication folder
Select the publication and right click on it and seclet "View Log Reader Agent Status"
Stop the Agent and Restart the agent


Now all ther replications were back in sync.

I ran the follwoing query to see if they are back up running

select * from distribution..MSrepl_transactions order by entry_time

Cleaning up of msdb database

Cleaning up msdb database is one of the neglected yet most important task of a sql administrator. We have some SSIS packages, full back up once a day and transactional backup in every 15 minutes. We use databaseMail extensively therefore cleaning up msdb database is a good practice.

The msdb database was 241 MB and after cleaning up ( running the following scripts) I am able to bring it down to 153 MB. I am keeping a log of the database growth in a table in every 4 hours. I do not think that the size of the msdb has to be this big. So I am leaving it the way it is and then looking at table where the file growth is captured  befor I make the decision if I need to rsize the db to a smaller size.

The scripts that I ran

--removing anything before a month

DECLARE
SET @date = DATEADD(day, -30, CURRENT_TIMESTAMP)
 --delete backup history

--delete old mail items
--especially, if you are sending attachements
EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @date
--delete the log of the sent items
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @date
--delete the SQL Server agent job history log
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @date
EXEC msdb.dbo.sp_delete_backuphistory @date @date datetime

script: List the permissions of a user

I had a  task where I had to list all the permission of a specific user on the database . The following script came to the rescue


SELECT dppriper USER_NAME(dppriper.grantee_principal_id) AS [UserName], .type_desc AS principal_type_desc, .class_desc, OBJECT_NAME (dppriper.major_id) AS object_name, .permission_name, .state_desc AS permission_state_desc
FROM sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id
WHERE
--dppriper.permission_name = 'EXECUTE'
USER_NAME(dppriper.grantee_principal_id) = 'myuser'

 --**replace the myuser with the user we are looking for
--**If we want to find only the stored procedures that the user has perission on comment out the  clause in the above query.

The size of the message body in dbmail

SQL 2008 can take more than 8000 characters in the meassage body of dbmail . While declaring the message body variable just set the size of the varchar to max.
For .e.g.

declare @msg_body varchar(max),           
 @v_subject


This will resolve the issue of the contents getting truncated in the message body of the emailvarchar(100)