Post reply

How to be informed about a database creation in SQL 2000

  • October 14, 2008 at 1:37 am

    #94072

    Hello,

    I need to be informed by a mail if a database in created in one of my SQL 2000 server,

    It's possible to create a trigger or else to do this task in SQL 2000 ?

    Thanks for reply,

  • October 14, 2008 at 2:12 am

    #884457

    You send the alert mail using the job.

  • October 14, 2008 at 2:34 am

    #884467

    Yes,

    I think the sql agent can do that but the think is how to create the trigger or else in SQL 2000 to initiate the alarm on database creation ?

  • October 14, 2008 at 2:37 am

    #884469

    There's no DDL triggers on SQL 2000.

    Set up a job that checks for new DBs (maybe against a table that stored the ones that you know are there) and schedult the job every couple hours. Job can check and mail you if there's a new DB

    Is it common that people create databases you don't know about? Dev environment I hope?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 14, 2008 at 2:48 am

    #884476

    It's a particular application in production that log the network information in a new database when the size is up to 5Go.

    My issue is to know when these database are created.

    If some one have an example about a script or else to do this, i'll apreciate your help.

  • October 14, 2008 at 3:46 am

    #884497

    is this what you want?

    select name as 'DB Name',convert(varchar(50),crdate) as 'Date Created',cmptlevel as 'Compatibility Level',

    filename as 'File Path',@@servername as 'Server Name'

    from master..sysdatabases where convert(char(8),crdate,112) = convert(char(8),getdate(),112)

    "-=Still Learning=-"

    Lester Policarpio

  • October 14, 2008 at 4:56 am

    #884523

    I created a table named dbo.historique that contain the name of the databases.

    Now, i need to create an error then the difference is <> '0' between the table dbo.historique and master.dbo.sysdatabases :

    if ((SELECT count(name) FROM master.dbo.sysdatabases) - (select count(*) from dbo.Historique )) <> '0'

    begin

    raiserror('New DataBase Created',16,1)

    return (1)

    end

    But it's not running ...

  • October 14, 2008 at 3:20 pm

    #884927

    Try this:

    if ((SELECT count(name) FROM master.dbo.sysdatabases) - (select count(*) from dbo.Historique )) <> '0'

    begin

    raiserror('New DataBase Created',16,1)

    end

    MJ

Viewing 8 posts - 1 through 7 (of 7 total)

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