August 21, 2009 at 5:18 am
Hi All,
Could you all please help me with this little tool/script i am trying to finish. Got a very tight deadline .
I am trying to write a script/tool that would create the same object in another schema on 2 0r databases when its created in a base database. In other words need a way to replicate new object creation on one database to other databases on same server.
Scenario:
db1 - Base database (which developers wud use)
db2 - another db on the same server as db1
db3 - another db on the same server as db1
Once Developers create objects (like, tables, procs, views ) on db1 and I need these objects to be created on db2 and db3 automatically.
I was thinking of using a DDL Trigger, capture the event data and execute the same sql on the other databases. However I am unable to execute the same sql on db2 and db3.
the other part to it is I need the object to be created in different schemas on db2 and db3 .
I am pasting the code which i could work, but cant figure out the way to end with the required result.
use db1
go
Create trigger [newobjects]
on database
for
create_procedure,
create_table,
create_function,
create_view,
create_schema,
create_assembly
as
set nocount on
declare @data xml
set @data = EVENTDATA()
insert into dbo.changelog(databasename, eventtype,
objectname, objecttype, sqlcommand, loginname)
values(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
Declare @Database_name varchar(70)
Declare @cmd nvarchar(max)
Declare @stmt nvarchar(max)
Declare Database_cursor cursor for
select name from
master..sysdatabases
where name in
(
'db2','db3'
)order by name
Open Database_cursor
Get_row:
Fetch next from Database_cursor into @Database_name while (@@fetch_status <> -1)BEGIN
print ' Database: '+@Database_name
set @Database_name='['+@Database_name+']'
--print ' new Database: '+@Database_name
Select @cmd='use '+@Database_name+'; '+@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')+';'
print @cmd
Execute @cmd;
fetch next from Database_cursor into @Database_name
END
close Database_cursor
deallocate Database_cursor
GO
=======================================================================================
Visit my technical reference; you might find some of your issues already documented.
September 2, 2009 at 6:03 am
Could anybody please help me on this, I haven't figured this out yet.
=======================================================================================
Visit my technical reference; you might find some of your issues already documented.
September 13, 2009 at 4:05 am
Hi
-- AS THAT SO
--=============================================================================
-- Create and populate a Tally table
--=============================================================================
--===== Conditionally drop and create the table/Primary Key
use master
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
CREATE TABLE dbo.Tally
(N INT,
CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)) --===== Create and preset a loop counter
DECLARE @Counter INT
SET @Counter = 1 --===== Populate the table using the loop and couner
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
WHILE @Counter <= 11000
BEGIN
INSERT INTO dbo.Tally
(N)
VALUES (@Counter)
SET @Counter = @Counter + 1
END --===== Display the total duration
SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds duration'
DECLARE @nm TABLE(num int)
INSERT INTO @nm
select n from tally
use model
IF OBJECT_ID('dbo.Tally1') IS NOT NULL
DROP TABLE dbo.Tally1
CREATE TABLE dbo.Tally1(N int)
INSERT INTO dbo.Tally1
select num from @nm
September 13, 2009 at 9:33 pm
setiv (9/13/2009)
Hi-- AS THAT SO
--=============================================================================
-- Create and populate a Tally table
--=============================================================================
--===== Conditionally drop and create the table/Primary Key
use master
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
CREATE TABLE dbo.Tally
(N INT,
CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)) --===== Create and preset a loop counter
DECLARE @Counter INT
SET @Counter = 1 --===== Populate the table using the loop and couner
DECLARE @StartTime DATETIME
SET @StartTime=GETDATE()
WHILE @Counter <= 11000
BEGIN
INSERT INTO dbo.Tally
(N)
VALUES (@Counter)
SET @Counter = @Counter + 1
END --===== Display the total duration
SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds duration'
DECLARE @nm TABLE(num int)
INSERT INTO @nm
select n from tally
use model
IF OBJECT_ID('dbo.Tally1') IS NOT NULL
DROP TABLE dbo.Tally1
CREATE TABLE dbo.Tally1(N int)
INSERT INTO dbo.Tally1
select num from @nm
Couple of things wrong here. First, this doesn't answer the OP's question on how to automate the creation of a database object from one database to two or three others. Yes, anything you create in the model database will be created in subsequent databases that are created, but it doesn't propagate objects to existing databases. Second, you are using an inefficient method to populate a tally table. Here is a good article to start with, The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url].
Also, keep an eye out for an article I wrote that is being republished here on SSC next week.
September 14, 2009 at 8:39 am
Suleman, what kind of objects are you trying to create in these databases? Are you trying to auto-create everything the devs create in the base database, or just some of the things? Does it include tables, or just code? Does it include data in tables?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 14, 2009 at 11:44 pm
Whats the error that you are getting.
I tried a small procedure and found that USE Database does not change the execution context to that database specified in the USE Database. When we use dynamic sql, after executing the sql the execution comes back to the database where the procedure is called. Hope this helps...
"Keep Trying"
September 15, 2009 at 11:13 pm
Hi There,
I am looking at copying over any new tables, views, functions and Stored procedures developers create on a base database to 3 other databases on the same server.
Later depending on what type of table it would be I would have to include this article in the existing replication setups.
Regards,
Suleman
=======================================================================================
Visit my technical reference; you might find some of your issues already documented.
September 15, 2009 at 11:15 pm
Chirag (9/14/2009)
Whats the error that you are getting.I tried a small procedure and found that USE Database does not change the execution context to that database specified in the USE Database. When we use dynamic sql, after executing the sql the execution comes back to the database where the procedure is called. Hope this helps...
Hi Chirag,
This is exactly what happened to me, I am trying to use the dynamic sql query mentioned in the script and it doesn't seem to work. As per my understanding it should work though.
Any help in this please.
Regards,
Suleman
=======================================================================================
Visit my technical reference; you might find some of your issues already documented.
September 16, 2009 at 3:06 am
Did you try the copy database option in SQL:
Using the copy database wizard
Create a package to copy the database on the same server with different DB name
Use the copy using SMO option (don't use the attach and detach option)
Save the package and schedule it to run every second/minute/hour/day.
This would replicate the changes made in the base database in to the db2.
Similarly create another package to move the same data from base db in to db3.
SQL jobs (packages) would be visible in SQL agent and can be run whenever sync of db’s is needed.
Let me know if it works out.
September 16, 2009 at 4:21 am
sqlwars (9/16/2009)
Did you try the copy database option in SQL:Using the copy database wizard
Create a package to copy the database on the same server with different DB name
Use the copy using SMO option (don't use the attach and detach option)
Save the package and schedule it to run every second/minute/hour/day.
This would replicate the changes made in the base database in to the db2.
Similarly create another package to move the same data from base db in to db3.
SQL jobs (packages) would be visible in SQL agent and can be run whenever sync of db’s is needed.
Let me know if it works out.
Thats a very good way of copying the database, however my requirement is not that.
I already have 3 copies from the base database, which are being used for reporting and other apps. The base database is the actual dev database which is already replicating data to the other 3 dbs.
My requirement is to automatically copy all the new objects created on base Dev database to these 3 existing copies of database on the same server .
Hope I made it clear.
=======================================================================================
Visit my technical reference; you might find some of your issues already documented.
September 17, 2009 at 7:17 am
The only way I can think of to automate what you're doing is to create a DDL log, probably using DDL triggers, and then use dynamic SQL and some string functions or a CLR regex, to create and run the desired scripts in the other databases.
But that won't get you data in the tables, it will just create the structure. That's why I asked about that. Does that matter? Or will empty tables be what you're looking for?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 17, 2009 at 7:46 am
GSquared (9/17/2009)
The only way I can think of to automate what you're doing is to create a DDL log, probably using DDL triggers, and then use dynamic SQL and some string functions or a CLR regex, to create and run the desired scripts in the other databases.But that won't get you data in the tables, it will just create the structure. That's why I asked about that. Does that matter? Or will empty tables be what you're looking for?
Hi GSquared,
Exactly!!! I only need the new objects definitions to be copied over to the other databases, i.e., empty tables. I am using DDL trigger to capture the events in the script I posted, however I am unable to get the dynamic SQL script applied across to the other databases.
Regards,
Suleman
=======================================================================================
Visit my technical reference; you might find some of your issues already documented.
September 17, 2009 at 11:48 am
It'll have to be a job that runs in those databases, since create/alter commands don't play well with three-part-names.
Create a job that queries the DDL log for new objects, parses the create/alter scripts out of the log XML, replaces the schema name as appropriate, runs the script, and then marks the script as having been run. You'll need one log column for each database/schema you want them to run in, or you'll need to denormalize and add an XML column that lists which databases and schemas each script has been run in.
If you schedule a job like to run every five minutes or so, it should handle what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 17, 2009 at 1:39 pm
If you are trying to implement tested changes from a development/QA environment to "n" databases, see my thread 474053 www.sqlservercentral.com/Forums/FindPost474053.aspx which is part of Topic Migrating from Dev to Prod
We are a 24 x 7 SaaS (Software-as-a-Service) company that has hundreds of customer databases. Each "release" needs to be rolled out within a very small "maintenance window". All changes to every customer database needs to be valid and consistent and needs to match the application logic. So a system such as proposed here would be pure chaos.
Additionally, since when is untested code automatically moved from a development system to a production system?
So, as described in the thread noted above, I'd take code from the source code control system. If you don't have one, then be prepared to suffer the consequences.
September 19, 2009 at 5:41 am
------------------------ In more detail -------------------------------
/*
use model
IF OBJECT_ID('mytest') IS NOT NULL
DROP TABLE dbo.mytest
CREATE TABLE dbo.mytest
(name varchar(50),number int,type char(10),low int,high int,status int)
INSERT INTO dbo.mytest
select * from master..spt_values
*/
exec
('
use model
select * from master..spt_values
except
select * from mytest
insert into dbo.mytest
select * from master..spt_values
except
select * from mytest
')
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply