September 19, 2009 at 10:21 pm
A wild guess, but check out ssma..... it might help you in migrating the objects.
its a free tool by ms
September 19, 2009 at 11:51 pm
Much seems wild when you collide(face) for the first time
Have You it in view of?
/*use master --model --msdb
-- master starttable
-- model currenttable
-- msdb previoscurrenttable
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
*/
use model
declare @mytest table
(name varchar(50),number int,type char(10),low int,high int,status int)
insert into @mytest
select * from mytest
use master
select * from @mytest
except
select * from mytest
use msdb
select * from @mytest
except
select * from mytest
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 @mytest
September 21, 2009 at 4:19 am
hi
without bracket if i try to execute your code i am getting this Error
Msg 203, Level 16, State 2, Procedure newobjects, Line 55
The name 'use [Test3]; create Table Emp1 (eid bigint,Fname varchar(500),Lname varchar(500));' is not a valid identifier.
So u replace
set @Database_name='['+@Database_name+']'
Execute @cmd;
with
Execute (@cmd);
it's work
you are writing script not Store procedure
so keep script in brackets
September 21, 2009 at 6:31 am
Meanwhile I shall not understand in what here put.
Originally worked stably.
Originally the question was a breadboard model of the program,
Instead of about a ready product.
Some features of work of a breadboard model are found out.
On seen sql2008 with c# is the program with the built - in intelligence.
Try the following variant:
/* --Initial installation--
use master
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,x_time datetime)
INSERT INTO dbo.mytest
select * ,getdate() from master..spt_values
declare @mytest table
(name varchar(50),number int,type char(10),low int,high int,status int,x_time datetime)
insert into @mytest
select * from mytest
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,
x_time datetime)
INSERT INTO dbo.mytest
select * from @mytest
use msdb
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,
x_time datetime)
IF OBJECT_ID('myresult') IS NOT NULL
DROP TABLE dbo.myresult
CREATE TABLE dbo.myresult
(name varchar(50),number int,type char(10),low int,high int,status int,
x_time datetime,y_time datetime)
*/
use model
declare @mytest table
(name varchar(50),number int,type char(10),low int,high int,status int,
x_time datetime)
insert into @mytest
select*from mytest
use msdb
insert into myresult
select *,getdate() from
(select*from @mytest
except
select*from mytest
union
select*from mytest
except
select*from @mytest)result
drop table mytest
create table mytest
(name varchar(50),number int,type char(10),low int,high int,status int,
x_time datetime)
insert into mytest
select * from @mytest
select*from myresult
September 21, 2009 at 6:31 am
OK, I don't think i would ever implement something "automatic" like this, because I strongly beleive that scripts have to be tested more than once to make sure they are appropriate.
regardless, this was kind of interesting, so here's a prototype model.
below is a database trigger... here i assume that ONLY if you create a new table, you are going to run the same script on two other databases...in order to use a USE DBNAME command, you've got to do it with dynamic sql, so I'm building the string up, and executing it for two imaginary servers.
for procs, it'd be very similar.
if the other databases are on other servers, i don't know how you'd do this, AFAIK you cannot run a DDL command on a linked server....I'd do the work as .NET app instead.
I don't know what you plan for ALTER TABLE, because ALTER TABLES, especially when dropping columns, might fail due to constraints and stuff. I'm completely ignoring schema names here, if you need schema names, that might add an extra layer of complexity.
anyway, here's a suggestion for everyone to process and pick apart:
CREATE TRIGGER [ReturnTBEventData]
on DATABASE
FOR
CREATE_TABLE --, DROP_TABLE, ALTER_TABLE
AS
declare @eventData XML,
@uname nvarchar(50),
@oname nvarchar(100),
@otext varchar(max),
@etype nvarchar(100),
@edate datetime,
@sql varchar(max),
@vbCrLf CHAR(2)
SET @vbCrLf =CHAR(13) + CHAR(10)
SET @eventData = eventdata()
SELECT
--adapting a DDL l\Audit Log example for this purpose.
@edate=GETDATE(),
--username for reference
@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
--this is the object name
@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
--this is the command that triggered the CREATE TABLE event...
@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'VARCHAR(MAX)'),
@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
if @oname is not null --object name has to exist
begin
SET @sql = 'USE DEVELOPMENTSERVER' + @vbCrLf
SET @sql = @sql + 'IF NOT EXISTS(SELECT * FROM sys.objects where name= ''' + @oname + ''' ) ' + @vbCrLf
SET @sql = @sql + 'BEGIN ' + @vbCrLf
SET @sql = @sql + @oname + @vbCrLf
SET @sql = @sql + 'END --IF Exists ' + @vbCrLf
EXEC(@sql)
SET @sql = 'USE REPORTSERVER' + @vbCrLf
SET @sql = @sql + 'IF NOT EXISTS(SELECT * FROM sys.objects where name= ''' + @oname + ''' ) ' + @vbCrLf
SET @sql = @sql + 'BEGIN ' + @vbCrLf
SET @sql = @sql + @oname + @vbCrLf
SET @sql = @sql + 'END --IF Exists ' + @vbCrLf
EXEC(@sql)
end
GO
ENABLE TRIGGER [ReturnTBEventData] ON DATABASE
Lowell
September 21, 2009 at 6:48 am
Originally the question was a breadboard model of the program,
Instead of about a ready product.
Some features of work of a breadboard model are found out.
On seen sql2008 with c# is the program with the built - in intelligence.
September 21, 2009 at 6:55 am
setiv (9/21/2009)
Originally the question was a breadboard model of the program,Instead of about a ready product.
Some features of work of a breadboard model are found out.
On seen sql2008 with c# is the program with the built - in intelligence.
setiv, the original request had nothing to do with a breadboard of model. Your suggestions using the model database will not help the OP with his problem of propagating new objects from an existing database to three additional databases.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply