SQL tries to drop tables that don't exist

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

SQL tries to drop tables that don't exist

Author

Topic

michaeldunnjr
Starting Member

3 Posts

Posted - 2008-03-14 : 18:16:49

When I am initializing a transactional replication from SQL 2005 to Oracle 10g, SQL tries to drop tables that don't exist. The properties option for the articles specifically states "if the name is in use:". The name is not in use, yet SQL still tries to drop non-existent tables, which causes the replication to halt. Anyone seen this before, or have any ideas what to do about it?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-14 : 18:45:06

Just don't bother with the snapshot. Manually get the tables in sync, then startup the log reader job.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-14 : 18:46:19

This will help :

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/repl_quickstart_for_oracle.mspx

michaeldunnjr
Starting Member

3 Posts

Posted - 2008-03-14 : 19:06:50

quote:
Originally posted by sodeep

This will help :

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/repl_quickstart_for_oracle.mspx

Unfortunately, that article is for replicating from Oracle to SQL whereas I'm replicating from SQL to Oracle.

quote:
Originally posted by tkizer

Just don't bother with the snapshot. Manually get the tables in sync, then startup the log reader job.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Can you provide a little more detail as far as how to manually sync the data from one db to the other?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-15 : 00:44:53

Well you could export your data via bcp, SSIS, or some other method, and then import it into Oracle or you could try adding a linked server on the SQL Server pointing to the Oracle server and then use something like this:

insert into linkedservername.databasename.ownername.tablename
select * from dbo.tablename s
where not exists
(select * from linkedservername.databasename.ownername.tablename o where s.pkcolumnname = o.pkcolumnname)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

michaeldunnjr
Starting Member

3 Posts

Posted - 2008-03-16 : 19:46:57

quote:
Originally posted by tkizer

Well you could export your data via bcp, SSIS, or some other method, and then import it into Oracle or you could try adding a linked server on the SQL Server pointing to the Oracle server and then use something like this:

insert into linkedservername.databasename.ownername.tablename
select * from dbo.tablename s
where not exists
(select * from linkedservername.databasename.ownername.tablename o where s.pkcolumnname = o.pkcolumnname)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Thank linked server option sounds like a promising suggestion. I just have one other question then, as I'm a bit of a novice to replication. When I've created transactional publications in the past, it always wants to initialize the subscription with a snapshot. Do you know of a way around that?

Thanks again!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-16 : 19:55:27

You can do transactional replication with Backup/restore too which is "initialize with backup"

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-16 : 22:53:10

Backup/restore doesn't work if target db has something you need to keep.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-16 : 23:15:37

Sorry, didn't get what you meant.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-16 : 23:25:13

When set replication, you can replicate tables to db that has different set of tables you want to keep for other processes. If you do backup/restore, you'll destroy those tables.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-16 : 23:35:15

quote:
Originally posted by michaeldunnjr

quote:
Originally posted by tkizer

Well you could export your data via bcp, SSIS, or some other method, and then import it into Oracle or you could try adding a linked server on the SQL Server pointing to the Oracle server and then use something like this:

insert into linkedservername.databasename.ownername.tablename
select * from dbo.tablename s
where not exists
(select * from linkedservername.databasename.ownername.tablename o where s.pkcolumnname = o.pkcolumnname)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Thank linked server option sounds like a promising suggestion. I just have one other question then, as I'm a bit of a novice to replication. When I've created transactional publications in the past, it always wants to initialize the subscription with a snapshot. Do you know of a way around that?

Thanks again!

In my environment, I can't run a snapshot as we need to keep the layout and existing data in the subscriber database. So I always unselect the option to perform a snapshot and I also disable the snapshot job (and sometimes delete it).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources