Function in database disappears

  • There is a function in our database that seems to just disappear. I run the script to create it and it is there. I then run my application that uses the function (it is used in a stored procedure) and it works. At some point afterwards it just goes away.

    The way our product is set up, we have a database for each of our customers. We have 300 or so databases that are identical to the one this is occuring in and we are not having any issues with them. I have never seen this in the past either. This is a new customer and the database was created within the last 2 months but we have a few other customers that came on board at about the same time and they arent having any issues.

    Thanks in adavnce for the help.

  • if not too much time has passed, the default trace would have the DROP FUNCTION event, complete with whodunnit information; you might have an automated process, or someone may just be dropping it accidentally. the default trace captrues a rolling list of the last 100meg of changes, so it can scroll by quickly if a indexing event occurs, or a server that is busy with DDL changes is happening.

    SSMS has a built in report you cna use easily, just select the database in question and it will auto filter the results:

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank you very much. That is what I was looking for.

    It turns out that a stored procedure not related to the function had a drop statement in it for the function. I have no clue how it got there! Very suspicious.

  • not suspicious; it sounds like a missing GO statement between drop create procedure and drop/create function in someones script.

    it happens to us all at some point

    IF OBJECT_ID('[dbo].[sp_Bad]') IS NOT NULL DROP PROCEDURE [dbo].[sp_Bad]

    GO

    CREATE PROCEDURE [dbo].[sp_Bad] AS PRINT 1

    --Implied GO statment here is missing

    IF OBJECT_ID('[dbo].[sp_Bad2]') IS NOT NULL DROP PROCEDURE [dbo].[sp_Bad2]

    GO

    CREATE PROCEDURE [dbo].[sp_Bad2] AS PRINT 1

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply