Removing all extended properties from SQL Server

About a year ago, we tried using a tool to get our SQL schema into a souce control system.  It was supposed to work like this: every night the process would run and checkin each object as a file into TFS. 

Well, aside from not working, it also added Extended Properties to all our objects in SQL Server. 

Not really a big deal, except when we would run our database comparison tools between production and development these would cause it so that almost everything would appear out of sync.

I ended up writing my own service to check in all our SQL objects into TFS, but we were left with all these Extended Properties all over the place. 

So I finally got around to cleaning them up using the script below:

select 'EXEC sp_dropextendedproperty
@name = ''' + sys.extended_properties.name + '''
,@level0type = ''schema''
,@level0name = ' + object_schema_name(extended_properties.major_id) + '
,@level1type = ''' +
    CASE WHEN
        xtype = 'U' THEN 'table'
        WHEN
        xtype = 'FN' THEN 'function'
        WHEN
        xtype = 'V' THEN 'view'
        WHEN
        xtype = 'P' THEN 'procedure' end
    + '''
,@level1name = ''' + object_name(extended_properties.major_id) + ''''
from sys.extended_properties
INNER JOIN sysobjects ON sys.extended_properties.major_id  = sysobjects.id
where extended_properties.class_desc = 'OBJECT_OR_COLUMN'
and extended_properties.minor_id = 0
and extended_properties.name like 'VSS%'

This script will produce a record set that you can copy and paste into another query window to run to clean out all the extended properties. 

Note: all the generated extended properties that this tool created on our DB started with “VSS” so that is why I have that extra item in my where clause.  You’ll probably want to remove that.

Advertisements

Getting error reports when a SQL Reporting Subscription Fails

UPDATE 2:

Either this never worked in the first place as I wanted, or MS has changed how they are doing things in SSRS 2008.

I now don’t see any of the errors in the tables I had been looking in, and in fact, the errors don’t show up in any of their logs either.  This is crap.

If you are like me, you don’t like the fact that the first time you realize that a data driven subscriptions in SQL Server Reporting Services has been failing is when someone comes up to you and says “Hey, I did XYZ and I never saw the server kick off an email.” 

Really?  Let me check…. oh, looks like it has been broken for god knows how long.  What the heck?

Well, I wrote a script to notify you when there are errors on the report server.

First, you need to setup Database Mail.  Expand Management, and pick Database Mail.  For my script I used the name “Email Profile” for the “Email Profile” get it?

Then create a trigger on the ExecutionLogStorage table like this:

USE [ReportServer]
GO
/****** Object:  Trigger [dbo].[ExecutionLogStorage_Insert]    Script Date: 05/13/2009 10:20:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ExecutionLogStorage_Insert]
ON [dbo].[ExecutionLogStorage]
AFTER INSERT
AS
BEGIN
    DECLARE @sStatus AS NVARCHAR(32)
    SET @sStatus = (SELECT Status FROM inserted)
    
    IF(@sStatus <> 'rsSuccess')
    BEGIN
        DECLARE @sReportName AS NVARCHAR(425)
        DECLARE @sPath AS NVARCHAR(425)
        DECLARE @ReportId AS UNIQUEIDENTIFIER
        DECLARE @sUserName AS NVARCHAR(260)
        DECLARE @sParameters AS NVARCHAR(4000)
        DECLARE @dTimeEnd AS DATETIME
        DECLARE @iTimeProcessing as int
        
        
        SELECT
          @sReportName = Name,
          @sPath =PATH,
          @ReportId = ReportId,
          @sUserName = username,
          @sParameters = (select CAST(ISNULL(ELS.Parameters,'') AS NVARCHAR(4000)) from ExecutionLogStorage ELS Where LogEntryId = Inserted.LogEntryId),
          @dTimeEnd = TimeEnd,
          @iTimeProcessing = ISNULL(TimeProcessing,0)
        FROM inserted INNER JOIN [Catalog]
        ON inserted.ReportId = CATALOG.[ItemID]
        
        DECLARE @sBody VARCHAR(8000)
        SET @sBody = 'Error with SQL Report '
                    + CAST(@sReportName AS VARCHAR(100))
                    + ' at path '
                    + CAST (@sPath AS VARCHAR(250))
                    + ' with ReportId '
                    + CAST(@ReportId AS VARCHAR(100))
                    + ' and username '
                    + CAST(@sUserName AS VARCHAR(100))
                    + ' Parameters '
                    + CAST(@sParameters  AS VARCHAR(5000))
                    + ' End time '
                    + CAST(@dTimeEnd AS VARCHAR(50))
                    + ' Status '
                    + CAST(@sStatus AS VARCHAR(50))
                    + ' Time Processing '
                    + CAST(@iTimeProcessing AS VARCHAR(50));
        
        DECLARE @sSubject VARCHAR(500)
        SET @sSubject = 'Error With Report ' + @sReportName
        
        EXEC msdb.dbo.sp_send_dbmail
         @profile_name = 'Email Profile',
         @recipients = 'email@address.com',
         @body =  @sBody  ,
         @subject = @sSubject;

    END
    
END

Presto!

 

UPDATE: Added logic to get the status and parameters.

 

Finding if SP Parameters are Nullable

I’m working on something where I wanted to know if some parameters to a SQL Server Stored Procedure were nullable (or, in otherwords, if they had a default value).

The API I was working with provided a way to find info about the SP, but the isNullable value was never accurate.

I tried working with with the MS DAABs which have a way to fetch SP parameter info, but this method also produced faulty information.

Looking into the code of the DAAB, it was internally calling:

Dim command As SqlCommand

SqlCommandBuilder.DeriveParameters(command)

Somewhere I came across some code for the DeriveParameters method (I think from the Mono project maybe?) which showed it calling this system SP:

sp_procedure_params_rowset

Which returns output fields,:

PARAMETER_HASDEFAULT

IS_NULLABLE

But, again, neither of these appear correctly.

However, I finally came across this thread where a MSFT poster indicates that this data is simply not available:

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/900756fd-3980-48e3-ae59-a15d7fc15b4c/

Instead, you need to parse the SP definition to see what the default parameter value is, which is a pain, but at least I know why these other methods kept not working.

 

Avoid Overwriting Modified SPs With Code Generation

One of the challenges of using code generation is to avoid overwriting your custom changes when you re-generate the code later.

This is a trick I am using to avoid overwriting my stored procedures if I update them manually.  The trick is to include a comment indicating that the SP has been code generated, and then to remove that comment if you update the SP.  So lets say that you generate:

CREATE PROCEDURE OrderDetails_Get
@OrderId as int
AS
-- CODE GENERATED
SELECT * FROM
OrderDetails
WHERE
OrderId = @OrderId

and you made some customizations to the SP so it now looks like this:

CREATE PROCEDURE OrderDetails_Get
@OrderId as int
AS
SELECT * FROM
OrderDetails
INNER JOIN
Orders ON
Orders.OrderId = OrderDetails.OrderId
WHERE
OrderId = @OrderId

The following code will help you when generating your SPs to avoid overwriting this change:

IF EXISTS(
  SELECT [definition] AS objectText
  FROM sys.sql_modules
  WHERE [object_id] = OBJECT_ID(N'OrderDetails_Get', 'P')
  AND definition LIKE '%CODE GENERATED%')
BEGIN
   -- overwrite is ok
END

 

SQL Server encountered error 0x80070422 (FIXED)

Today we started getting this error when trying to do full text search with SQL Server 2008.

Msg 30046, Level 16, State 1, Procedure CodeMaster_GetCostCodesByCriteria_FullText, Line 47

SQL Server encountered error 0x80070422 while communicating with full-text filter daemon host (FDHost) process. Make sure that the FDHost process is running. To re-start the FDHost process, run the sp_fulltext_service ‘restart_all_fdhosts’ command or restart the SQL Server instance.

Google has NOTHING on this problem, except 1 other guy reporting it to MS, who tells him to go to some other forum.

Great.

If I find anything I will post it.

UPDATE:

Ok I figured this out.

The problem was that the actual FTS service was disabled (but not just that, so read on).

So go into your services and make sure that this one is running:

SQL Full-text Filter Daemon Launcher (MSSQLSERVER)

But even after I set this up running things weren’t working.  More research led me to find out that there was an issue with the fact that we run our SQL Server under a domain account and the full text search was not running as that same user.

So I configured this service to run as the same user and restarted the service.

Then in SQL Server I ran this:

EXEC sp_fulltext_service 'restart_all_fdhosts'

to restart things.

I also right clicked on the FTS catalog (under databasename/Storage/Full Text Catalog), picked properties, and then selected the option to rebuild catalog.