Tag: SQL Server

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

SQL: Maintaining Custom User Data Via Publishing Wizard

We refresh our testing databases periodically with no problems. It is a logical protocol to make sure to alert all users ahead of time so that they may backup any needed custom data that they have entered themselves; however, when I did a data refresh this week something went awry and before I knew it there was a line to my desk, the phone was ringing, and email started piling up.

Fortunately, we did have a backup copy of this database so I was able to query and recover this data. However, I needed a quick and simple way to have the users re-create this data without my involvement. I could set up some type of job to restore the data after each refresh , but the data would only need to exist for a few months and I did not have time to go through the red tape that would be required for this. Given this particular scenario, it was decided to create a SQL script that would do INSERTS for the data.

Creating an INSERT script should not be difficult but it was too timely. Exporting and Importing the data was not ideal. I decided to investigate tools that would create the script for me. And there it was — on my machine all along — the SQL Server Publishing Wizard. I had forgotten all about it. I had just needed to locate it since there was no icon or link; therefore, the entire purpose of this way too-long-winded post :

C:Program FilesMicrosoft SQL Server90ToolsPublishing1.4SqlPubWiz.exe

Yay, now I have a reference and reminder!

Anyway, it is worth mentioning that this tool is way inappropriate for large data. The data needed was a very small portion compared to the millions of rows that exists. I had to query the needed data to insert into a temp table and this made the wizard fast. Ideally, it would have been better to have the ability to query a table for data from within the wizard but I’m sure it is just a matter of time.