From: SnapDive on

I am trying to come up with tactics for creating a single script to
enable replication between two SQL Server 2008 named instances on the
same computer. The replication exists on a test machine and I would
like to "carbon copy" that replication setup to multiple development
server virtual machines. I already have instance and database
build-out scripts working, it's just stamping out the replication that
is giving me a problem.

All thoughts appreciated.

Thanks.


From: thomarse on
What do you mean by "stamping out"? If you are referring to getting
rid of old replication so you can set up the new, have a look at the
system SP called sp_removeDBreplication.

I'm not sure if that's what you're asking though, can you be a bit
clearer?

From: SnapDive on

I figured out how to create replication between two databases using
pure TSQL so i can run it on multiple environments. I have a
"preparation script" that attempts to remove replication for later
re-application but it is insufficient. Can anyone say how I can edit
this so it will drop things if they exist and now throw an exception
or make errors?



Use Master
go
Create Table #Response ( Response NVarChar(2048) )
Insert #Response Exec sp_removedbreplication 'DataPubd'
Insert #Response Exec sp_removedbreplication 'DataSubbd'
Insert #Response Exec sp_dropdistpublisher 'localhost,1121' ,
@no_checks= 1
Insert #Response Exec sp_dropdistributiondb N'MyNamedDistrib_A'
Insert #Response Exec sp_dropdistributor 0
Go

-- I was trying to insert all results into
-- the temp table hoping that would mask
-- errors, but no dice.

-- Thanks!
From: thomarse on
You will have to look at capturing the output of things like:-

select DATABASEPROPERTYEX('databasename', 'IsPublished')

or capturing the output of the various system help SPs which pertain
to replication such as

sys.sp_helpdistributor
sys.sp_helpreplicationdb
sys.sp_helpsubscriberinfo


On 28 Jan, 17:08, SnapDive <SnapD...(a)community.nospam> wrote:
> I figured out how to create replication between two databases using
> pure TSQL so i can run it on multiple environments. I have a
> "preparation script" that attempts to remove replication for later
> re-application but it is insufficient. Can anyone say how I can edit
> this so it will drop things if they exist and now throw an exception
> or make errors?
>
> Use Master
> go
> Create Table #Response ( Response NVarChar(2048) )
> Insert #Response Exec sp_removedbreplication 'DataPubd'
> Insert #Response Exec sp_removedbreplication 'DataSubbd'
> Insert #Response Exec sp_dropdistpublisher 'localhost,1121' ,
> @no_checks= 1
> Insert #Response Exec sp_dropdistributiondb N'MyNamedDistrib_A'
> Insert #Response Exec sp_dropdistributor 0
> Go
>
> -- I was trying to insert all results into
> -- the temp table hoping that would mask
> -- errors, but no dice.
>
> -- Thanks!