How do you setup replication from SQL Server 2000 to a oracle database? We want to push changes from sql sever to oracle, evertime sql server table is changed. I have a working ODBC connection. When I try setting up a push to the oracle side I can not find the subscriber In the list. Can someone point me in a direction, with a detailed way of doing this.
View 1 RepliesHi,
I need to implement SQL Reporting Services but do not wish to use the production database for running the reports against. I intend to set a seperate server for reporting services. The data only needs to be refreshed on a daily basis, during slient hours. What would be the best way to copy data from the production to the reporting services server?
Thanks
Replicating from SQL Server 2005 to Oracle 9i Release 2. The source table has NOT NULL specified for all columns. The table contains multiple rows with blank values in one of the NOT NULL columns. When I attempt to replicate this table to Oracle, I'm unable to replicate the rows containing the blank values.
I've followed the directions in the Oracle Subscribers page in Books Online: http://msdn2.microsoft.com/en-us/library/ms151738.aspx
Modify the generated create table script, removing the NOT NULL attribute from any character columns that may have associated empty strings, and supply the modified script as a custom create script for the article using the @creation_script parameter of sp_addarticle
Here is the segment of the Publication Script referencing sp_addarticle:
exec sp_addarticle
@publication = N'mike_hayes_test_replication2',
@article = N'mike_hayes_test_replication',
@source_owner = N'bmssa',
@source_object = N'mike_hayes_test_replication',
@type = N'logbased',
@description = N'',
@creation_script = 'CREATE TABLE [bmssa].[mike_hayes_test_replication2]( [EXCELCOLUMN] [int] NULL, [TABLENAME] [varchar](40) NULL, [FIELDNUM] [int] NULL , [REFTABLEID] [int] NULL , [MODULETYPE] [int] NULL , [DATAAREAID] [varchar](3) NULL, [RECVERSION] [int] NULL, [RECID] [int] NULL, [BRAND] [varchar](20) NULL )',
@pre_creation_cmd = N'drop',
@schema_option = 0x00,
@identityrangemanagementoption = N'manual',
@destination_table = N'mike_hayes_test_replication2',
@destination_owner = N'bmssa',
@vertical_partition = N'false'
GO
I added the @creation_script value, and changed the schema_option to 0x00.
The problem is that when replication occurs, the table that is created has NOT NULL specified for all columns. I expected that the @creation_script would be executed, (as per the instructions), but that doesn't appear to be the case.
Any ideas?
Thanks for your help.
Mike Hayes
Client is running X- version of application and corresponding database size is huge. Now client's vendor is releasing Y-version of same application with many database schema changes (like new tables added, new columns added, renamed existing columns and etc) To upgrade to the Y-version, vendor is suggesting to my client that down the system and do the upgrade for application/database to Y-version. We are sure that this process will take days together to upgrade to the Y-version. My client is not ready to down the system for that long. So we are trying to find the solution with minimal down time.The approach we are thinking is,Â
1) Create the replicated database to another server (server2) from production server(server1) using golden gate with X-version
2) Create new tables/schema updated tables from Y-version database on same server1. Here for  Updated schema tables we are planning to use the name <table_name_Y_version> as the same table name exists in X-version.
3)With above 2 steps, golden gate replicate the changes from production to server1 and server1 will have the new Y-version table schema (with different concatenate name ' _Y_version'). BTW , there is no affect for the production
4) At this stage we are planning to find best approach, to fill the '<table_name>_Y_version' from X-version tables. two challenges here a) all data needs to be moved to Y-version tables b) they have to sync data in real time.
we thought of going to
a) ssis package to pump the data to Y-version tables, but real time data will not sync.
b) trigger based technique, previous experience said, lot of load
c) thinking about sql replication.
Hi!
View 36 Replies View RelatedI am using Sql2000 and have 6 servers. On this 6 servers 4 servers have the same database
MY QUESTION is i need a script or advice that will help to do this:
Every time data chages in 1 of the 4 servers that have the same database, I want all changes to happen in the other 3 so that they must always have the same information
Hi everybody,
View 13 Replies View RelatedI have to extract data from 5 different oracle databasee with same schema.This will be scheduled job.Can someone guide me.
View 1 Replies View RelatedI have to extract data from 5 different oracle databases with same schema.This will be scheduled job.Can someone guide me.
View 1 Replies View Related
Please guide me urgently how to extract data in SSIS from 10 identical oracle database into 1 sql server database.
There is a table which list all the 10 databases.
Hi,
I am very new to SQL Server. Plenty of SQL knowledge but the whoe SQL server enviornment is new.
I am working with SQL Server 2005. My task is to generate reports without affecting our live database. I have setup a second server and installed SQL Server 2005 on that too. My thought was that maybe I could mirror or replicate the table I require over to this new server and run my queries from here. Is this easy to do ?
I read that mirroring might not work as it is solely for back up /fall over purposes and that data on the mirrored server would not be accessible.
I have also been looking at SSIS but at the moment this is all a bit like double dutch to me ! Can anyone point me in the right direction, preferably somewhere beginner friendly ie not overly complicated !!
Thanks a mill,
Sophie
Hello,
We have four mobile devices that are set up for merge replication via the web. We are not receiving errors but some of the data is not coming over to the devices. If we manually add a record that record will come over, but there is data that is on the server that isn't on the devices. If we run the snapshot for each device (We're using host_name as a filter) nothing happens. If we do validation check we get errors. If we reinitialize all devices it works but the next days data (sql job populates data to the publisher db at night) isn't on the device after syncing the next morning. Any help would be appreciated.
John
My company is migrating all their servers to a new data center and I get to specify what we need for the db servers.
We've got a 22 prod servers (mainly physical) with a couple of TB of data on sql 2000 to 2012.
We expect to move to sql2014, and consolidate and virtualise where ever possible.
But I'd like start with specifying an overall architecture for this: some Best Practices to guide the build at a server and an installation level
I want to replicate to an archive database. This means that the subscriber will have data that has been removed from the publisher. In my reading, I haven't seen any discussion of this specific scenario.
Here's what I imagine the solution might be:
EXEC sp_addpublication_snapshot
@publication = N'My_Publication',
@frequency_type = 1 -- only create the snapshot once
GO
...
EXEC sp_addarticle
@publication = N'My_Publication',
@article = N'My_Table',
@source_owner = N'dbo',
@source_object = N'My_Table',
@del_cmd = 'NONE',
GO
I set the publication snapshot to only execute once, that would be during the maintenance window when it is initially installed. Then, on the tables that will contain archived data, I specify that deletes aren't replicated.
Here's my concern: aren't there times when you need to resync?
If you could push a new snapshot that dropped the tables on the subscriber and built the thing up from scratch, then things would sync-up just fine. But in this scenario if you drop the subscriber tables then you've just lost your archive.
What's the best way to handle this?
Thanks,
-=michael=-
Replicating partial database
Hi there, actually I have a set of tables from my database model on SQL SERVER 2005 that I want to replicate (using merge replication) to a different server with SQL SERVER 2005 EXPRESS, Everything seems to be ok until I try so replicate just a portion of my model. I€™ve got some parent tables to replicate but I€™m not going to replicate some of their children, when I try to move them (just the parents and SOME children) to the other server using subscription, I have to erase some foreign keys (the children€™s foreign keys I wasn€™t supposed to migrate) in the target server to get my database migrated.
What Can I do to solve this problem?
Hi,I need to set up a generic table in Oracle that allows users to enterdata that can later be retrieved in reports.I was thinking of a 3 column table in this structure:DATE,ITEM,VALUEfiguring that you can store anything in this way because ITEM can beanything and you can have multiple instances of a particular value byhaving a new date.Does that seem reasonable?In any case, what are my user front end options? I was thinking aMicrosoft Access database but its not ideal because not everyone herehas Access?Can you do it with Excel? Or what other options are there as a simpleuser front end to enter the data?Thanks for any advice.Kim
View 1 Replies View RelatedHi,
Below and far below are my SQL command and raw data. In the data, each lot has 13 records, for example, T6L09641 has 13 records. What I am working on is to average the Total, Large, Middle and Small for each lot as a representative instead of listing every record. So, I wrote a SQL express and put it in my Access and run it. However, I got an error message "ORA-00905:missing keyword(#905)". I double checked it again and found nothing stange. Someone can help me to modify it?
SELECT LINE_ID, LOT_ID, AVG(TSIZE_QTY_PA)/10000 Total, AVG(LSIZE_QTY_PA)/10000 Large, AVG(MSIZE_QTY_PA)/10000 Min, AVG(SSIZE_QTY_PA)/10000 Small, RCV_TIME Time FROM IWMES.DIMES_PPTU_PT_IPCRST Where (LINE_ID='L401') GROUP BY LOT_ID HAVING RCV_TIME BETWEEN 12/10/2006 00:00 AND 12/13/2006 00:00 ORDER BY LOT_ID
Raw Data
http://photo.pchome.com.tw/vitaminb6/116597990115
What I wished
http://photo.pchome.com.tw/vitaminb6/116599056886/
Does anyone know how to copying database objects and data from Oracle 8 to SQL 2005 ?
View 1 Replies View Relatedhi there,
After replicating a database, the stored procedures in that same database are not able to edit by other users than service account (sys admin users), When the normal users try to edit any of the stored procedures, sql server 2005 is throwing the following error...
Msg 21050, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 12Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.
any suggessions are most welcome.
by
CHINNASAMY
Are there any known problems with replicating structure changes (New table, etc...), as well as data in MSSQL Server 7.0?
View 2 Replies View RelatedWe have a database that I would like to replicate on another server but am unable to use regular replication via publish/subscribe due to the fact that the production database has no primary keys on tables, only clustered indexes. The backup db needs to be as close to real-time synchronized as possible and will be in fairly active use most of the time. Has anyone had success in developing such a system? How did you do it and what are the pitfalls? Any advice would be greatly appreciated. Thank you.
W.
I have SQL Server running on my internal LAN. I want to have a second SQL Server running on a hosted (shared) website. I then would like these servers to talk to each other. At some scheduled time I need to publish data to the web, and I then need to subscribe to data input on the web by various clients. My internal LAN can see the Internet via our cable modem.
What is the best way to do this? What software will I need to run. I'm looking for the big picture.
Thanks for the help everyone.
Hi, need some help.
Basically I need to replicate 2 tables from a Visual Fox Pro application to SQL server 2005
From an Intranet to a hosted Center. (Different domains)
Is this possible?
Any suggestions?
Thanks in advance
I need to find a solution for having the same database objects on two servers without the data, and be able to synchronize them on demand.
Whenever a table changes (alter, create, delete) I need to be able to replicate those changes to the destination server without affecting its data.
If I add, alter or delete a stored procedure, or other objects such as functions, etc. I need to be able to see those changes on the destination.
any ideas.
I have a SQL 2005 database that I am using with a website. This basic website will be sold to other companies and ran on their servers with different URLs. Since, All of these databases will store different data, I'm not sure how I can make updates to original database and replicate those structure changes to the other DBs without changing the data also. Is there a way to automate the replication of structural DB changes without replicating the data along with it?
Thanks,
Kirk
how to fetch data from oracle database in sql server 2014
example:
oracle schema :t1
sql server :t2
now am in t2 sql server database
now am executing below query
select * from t1.tablename ;
In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.
Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.
I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.
Am I missing an obvious workaround?
In 2007 we plan on moving many of our SQL Servers (2000 & 2005) to a SAN. We will also be investigating the best way to replicate data from our production SAN to a Disaster Recovery SAN. I would like to know if people rely on built-in SQL Server replication / log shipping or third party products like DoubleTake or MirrorView. What's the advantage, if any, to using a third-party product?
Thanks, Dave
Hello,
I've been searching for information on this for awhile unsuccessully. I am using SQL Server 2005 Enterprise Edititon and transactional replication with separate publisher, distributor and subscribers. I need to encrypt data on our publisher and then I need to replicate that data out (i.e., encrypted data, not encrypted transmission). Since the keys are server specific, I imaging there might be some difficulty in doing this, but I figure I can't be the only one with this problem. Does anyone have a resource or know what must be done to get this accomplished?
Thanks,
Aaron Lowe
Hi,
I have a table in SQL Server 2005 which has [Id] and [Name] as its columns.
I also have a Oracle database which has a similar table.
What I want to do is as follows:
In a SSIS package, I want to pick up details from SQL Server and update the Oracle table. And then should be done without using a linked server connection.
Can someone guide me as to how I can specify a update statement in the destination dataflow.
Thanks.
Looking for sample ETL package to extract data from SQL Sever Database and load into Oracle Database using SQL SERVER INTEGRATION SERVICES 2008. The requirement is for full load and incremental load both.
View 5 Replies View Relatedhi,
is it possible to replicate data from one table to another which have different names,different schemas and different column names.
Im trying this using transactional replication.
i achieved this for different schema, different table names.but it is failing for different column names.
till now i haven't got a proper reply for my problem.
regards
Baji Prasad.B
Hello! I'm new to these forums.
I have been trying to replicate from Sql Server to Postgres using Enterprise Manager and the Postgres ANSI ODBC driver version 8.02.02.
With enterprise manager, I was able to copy tables over when I used the option to "DROP existing table and re-create it," but columns of data types datetime and decimal were copied as varchar.
So I tried drop the tables manually in Postgres and then create them with the proper data types (timestamp without time zone, and numeric). I then tried changing the article option to "Keep the existing table unchanged" and I get the following error from the Distribution agent: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
So I then tried using the article snapshot option "Delete all data in the existing table" and I got the same error.
Anyone has any luck replicating in this fashion? Or should I maybe go about it differently?