Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002

    Unanswered: 'create database' command in trigger


    As part of setting up automated replication between two servers, I need an insert trigger on a table in a database on server 1 to run a 'create database xxx' command on server 2. Once I've got that I'm sorted.

    I tried using linked servers but didn't get anywhere. Finally, I tried creating a trigger on server 1 which ran a dts package (the dts package contained the SQL to create the database on server 2). The dts pacakge ran on its own (I ran it using dtsrun), but not as part of the trigger.

    I know that SQL server doesn't support 'create database' commands in triggers, but I would have thought the dts approach would have got around that. Any suggestions? Here's my trigger

    CREATE TRIGGER dblist_trigger
    ON dblist
    commit work
    EXEC master..xp_cmdshell 'dtsrun /s mbuksqltst03 /u sa /s /n createdb'



  2. #2
    Join Date
    May 2002
    Please explain something more about your process

  3. #3
    Join Date
    Jul 2002

    The application I'm working with uses SQL Server and creates databases as part of its operation. So in essence, I'm trying to replicate an entire server rather than just a particular database. I have a script which will create a full set of replication objects for a given database. The problem I have is that when a database is created on the main server, I can't automatically create a blank database on the replicated server to run my replication objects script against.

    I've got the replicated server to maintain a list of databases on the main server (a table called dblist - updated by a trigger on the main server). What I was trying to do was create some sort of trigger which will run a create database command when the dblist table on the replicated server has a row inserted in (indicating a new database has been created on the main server). This syntax works, but not when I use it in a trigger

    declare @sqltxt nvarchar (2000),
    @maxid int,
    @name varchar (256)
    set @maxid=(select max(id) from dblist)
    set @name=(select name from dblist where id=@maxid)
    set @sqltxt=(select 'create database '+ @name)
    EXEC sp_executesql @sqlTxt

    I have even tried putting this sytax in a separate stored procedure and as a T-SQL object in a dts package. But I still can't get it triggered automatically.

    Of course, if there is a more elegant way of setting up the replication, I'm open to suggestions.

    I hope this is some use.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts