Category: SQL

SQL Server: Get First and Last Days Of Month

DECLARE @min DATETIME
DECLARE @max DATETIME
DECLARE @now DATETIME
SET @now = GETDATE();

–get first day of last month:

SET @min = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(DATEADD(mm,1,@now))-1),DATEADD(mm,-1,@now)),101) 

–get the last day of last month

SET @max = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(@now)),@now),101) 

–get the first day of current month
SET @min = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(@now)-1),@now),101)  

–get the last day of current month

SET @max = CONVERT(VARCHAR(25),DATEADD(dd

,-(DAY(DATEADD(mm,1,@now))),DATEADD(mm,1,@now)),101) 

SQL Server : Check If Index Exists On Schema

–create function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[IndexExistsOnSchema]
(
      @SCHEMA VARCHAR(50),
      @INDEX  VARCHAR(255)
)
RETURNS INT
AS
BEGIN
      DECLARE @CNT INT
      SET @CNT = 0
      SELECT @CNT = COUNT(*)
      FROM SYS.INDEXES
      WHERE OBJECT_ID = OBJECT_ID(@SCHEMA)
      AND name = @INDEX
      RETURN @CNT
END 
GO
–use function:


IF (SELECT DBO.INDEXEXISTSONSCHEMA(@SCHEMA_NAME, @INDEX_NAME)) > 0
EXEC(‘ALTER INDEX [INDEX_NAME] ON ‘ + @SCHEMA_NAME + ‘ DISABLE’);
–or
IF (SELECT DBO.INDEXEXISTSONSCHEMA(@SCHEMA_NAME,@INDEX_NAME)) > 0
EXEC(‘ALTER INDEX [INDEX_NAME] ON ‘ + @SCHEMA_NAME+ ‘ REBUILD’);

SQL Server: Generic Try/Catch Snippet

BEGIN TRY

— do stuff

END TRY

BEGIN CATCH

                  DECLARE @PROC AS VARCHAR(100)
                  DECLARE @SERVER AS VARCHAR(200)
                  DECLARE @ERROR AS VARCHAR(4000)

                  SELECT @PROC = ERROR_PROCEDURE(),
                      @SERVER = @@SERVERNAME,
                      @ERROR = ERROR_MESSAGE();

END CATCH;

SQL Server: Common Table Expression

Scenario:  Need to write a query based on two tables.  The tables have a one-to-many relationship.  The “bug_comments” table can have many records for one table in the “bug” table.  However, I do not want all of the records in the “bug_comments” table, I only want the most recent record.  Also, I don’t want to write a subquery or use a cursor.

WITH cte (bc_bug, bc_id)
AS
(SELECT bc_bug, MAX(bc_id)
FROM bug_comments
WHERE LOWER(bc_type) = ‘comment’
GROUP BY bc_bug )
SELECT DISTINCT 
b.bg_id AS [ID],
b.bg_short_desc AS [DESCR],
bc.bc_comment AS [COMMENT]
FROM bugs AS b LEFT OUTER JOIN
bug_comments AS bc ON b.bg_id = bc.bc_bug LEFT OUTER JOIN
cte ON cte.bc_bug = b.bg_id AND cte.bc_id = bc.bc_id
WHERE b.bg_status = 1 and
((cte.bc_bug = bugs.bg_id and cte.bc_id = bc.bc_id)
ORDER BY b.bg_id DESC

SQL Server Error: Recover [DBNAME] From Suspect Mode

This error can occur when the error log file does not match the database file or when the .mdb file is detected as being corrupt.

There are multiple ways to fix the problem and the solution depends on whether or not your database has data that is critical for you to keep. For me, I only get this error on my local box where I’m conducting allot of experiments and tests so that there is no need for me to worry about critical data. This makes the restoration task much more simple.

First, I try the following canned stored procedure : sp_resetstatus. If this fails then the following always works:

  1. Stop the SQL Server database & services
  2. Rename or move the “msdblog.ldf” & “msdbdata.mdf” files
  3. Copy “msdblog.ldf & msdbdata.mdf” files from any other working database installation to the same path
  4. Start the SQL Server database