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
EXEC master..xp_cmdshell 'dtsrun /s mbuksqltst03 /u sa /s /n createdb'
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),
@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.