Oracle LAST_DAY function in SQL Server

Today I was working in SQL Server 2008. I need to use a function similar to Oracle's LAST_DAY in my scripts, but I cannot find an equivalent function, so I decided to perform a new one.

This is the script to build the function in SQL Server:

CREATE FUNCTION [dbo].[LAST_DAY] ( @pInputDate    DATETIME )

RETURNS DATETIME

BEGIN

    DECLARE @vOutputDate        DATETIME

    SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +

                       CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01'

    SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))

    RETURN @vOutputDate

END

These are the test scripts in SQL Server and Oracle:

• SELECT dbo.LAST_DAY(getdate())

• SELECT LAST_DAY(TRUNC(SYSDATE)) FROM dual

The result row in both scripts was: 2012-10-31 00:00:00.000

Truncate LOG in a SQL Server 2008 Database

Today I've got a problem with a SQL Server 2008 Database again with the transaction log, which was full and generating errors in the business processes.

In an old entry I posted how to solve the problem, but in SQL Server 2005  Using the scripts for 2005 in SQL Server 2008, it will generate this error:

'TRUNCATE_ONLY' is not a recognized BACKUP option.

So, the solution in a SQL Server 2008 Database is to use this script:

USE ReportServerTempDB -- Database

ALTER DATABASE ReportServerTempDB SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE( ReportServerTempDB_log, 1) -- Parameters are: Logical Name of the file, percent occupied
ALTER DATABASE  ReportServerTempDB  SET RECOVERY FULL WITH NO_WAIT
GO

Like the old one, it has been tested and works fine.

To learn more about this, check this link from Pinal's Dave Blog: Original Page

GRANT SHOWPLAN PERMISSION SQL SERVER 2005

Today I was trying to perform a big "UPDATE" in a SQL SERVER 2005 database between tables from different databases, even when the "UPDATE" works I was was concerned about the large amount of time spent on it's execution.

I've tried a "Display Estimated Execution Plan" (CTRL+L) and showed me the following error:

SHOWPLAN permission denied in database 'BussinessDW'.

After I googled for "GRANTING SHOWPLAN" I found that the following command solves the problem:

GRANT SHOWPLAN TO @user - - @user is the database user to who I want to permit the showplan

However after a little research in MSDN I found that security risks are involved with this sentence.  Because "SHOWPLAN" includes "CONTROL" and "ALTER TRACE" permissions, I decided to revoke the "SHOWPLAN" permission to the user after I've completed the analysis of the execution plan.

To revoke the show plan I used the following command:
 
REVOKE SHOWPLAN TO @user - - @user is the database user to who I want to remove the showplan permission

To learn more about SQL Server permissions, visit  Laurentiu Cristofor's blog

P.S. 2014-04-24: Thanks to the "unknown" user who requested the command to revoke the show plan permission, I forgot to put it in the original entry.