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.
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
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
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 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
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 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
hi,
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
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 Replies View RelatedHello! 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?
Good morning,
I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.
How do I do this?
I have searched the forum for some ideas, but did not find any.
Thanks in advance...
I need to upload bulk data into a datatable. Some of the source data will already exist in the target table to be updated. How can I INSERT only the new data into this datatable? Thanks.
View 4 Replies View RelatedI am trying to replicate data from a view in the publisher to a table in the subscriber (transaction replication). I do not need the view's base table, or the view itself, replicated to the subscriber. I only want to data from the view to feed a table in the subscriber.
Is this possible?
Running SQL Server 2008 R2 Enterprise.
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.
when i do a snapshot i have it set up to truncate before inserting. As a result I'm getting an error saying that it cant truncate a table reference in an indexed view. What settings should i use to allow for a snapshot in this instance? Should i manually drop the databinding then snap then recreate the databinding? there has to be a better way
View 1 Replies View Relatedplease help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies View RelatedI've a problem.In my MSSql db I've some tables named username.mytable and only onestore procedure named dbo.sp;CREATE TABLE [pippo].[mytable] ([year] [int] NOT NULL ,[month] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [pluto].[mytable] ([year] [int] NOT NULL ,[month] [int] NOT NULL) ON [PRIMARY]GOCREATE PROCEDURE [dbo].[sp]ASselect * from mytableGObut when I try to execute dbo.sp (from pippo or pluto connection) I'vethis error (users pippo and pluto are owner):Server: Msg 208, Level 16, State 1, Procedure sp, Line 4Invalid object name 'mytable'.How can I access to pippo's (or pluto's) data from dbo.sp?thanks!!
View 2 Replies View RelatedWhat is the best approach when transferring all database objects & data from a SQL Server 6.5 database to SQL Server 7.0 database running on different servers.
Thanks in adavance for your tips
Cheers,
Phil
We have decided to use business objects in our new application, which seems to be working well, since they can be used as a binding source for grids, etc.
I am trying to evaluate whether SSIS is a practical solution for our ETL requirements. The problem is, I can't find any examples or references, or even the slightest hint that anyone is using them with business objects. Any attempts to search yield a ton of results which are based on a commercial product called "Business Objects" rather than the design pattern.
It is currently a requirement of our development team that all data access must be done via business objects, rather than communicating directly with the database.
Can anyone provide some more information (besides just suggesting I write a custom connection manager)? Is there anyone who has actually made SSIS work with business objects?
Thanks, Richard
I have recently moved from a Microsoft SQL Server 2000 to a SQL Server
2005.
In the good old Enterprise Manager, when I imported a table from a SQL
server to another, I could choose "Copy objects and data between SQL
Server
databases". When choosen, all primary keys, and default values was
copied.
My question is:
Is it possible to do the same in Microsoft SQL Server Management Studio
with
SQL Server 2005? And how do I do it?
I have tried using the "SELECT * INTO NewTable FROM OldTable"
statement, but
it just drops all information about primary keys and default values.
Best Regards
I want to migrate from one machine to another is it wise to use SSIS "Copy Objects and Data Between SQL Servers" task to accomplish this....migration both data and metadata (in master,model,and msdb ) databases?
View 1 Replies View RelatedCopy objects and data between SQL Server databases
"
Display the Select Objects to Transfer dialog box, where you can specify both objects and data to copy, if both the data source and destination are Microsoft® SQL Server™ databases. The objects you can transfer include tables, views, stored procedures, defaults, rules, constraints, user-defined data types, logins, users, roles, and indexes. You can transfer objects only between multiple instances of SQL Server version 7.0, from an instance of SQL Server 7.0 to an instance of SQL Server 2000, and between multiple instances of SQL Server 2000.
"
can I apply "Copy objects and data between SQL Server databases"
to run in two different sqlserver 2000 ( not an instance ) . what I mean is I have two different sql servers located in two different locations( I am not using an instance installation) can I still run the copy and get an identical database in both servers.
Q2. if I have two sql server 2000 with different collations (one is binary and the other is the default) will I be able to run the copy wizard and still have an identical copy of sql server in both servers.
I personally tried to run the copy wizard and IT NEVER WORKED FOR ME and I really do not know the reason.
Thanks for your input.
ali
I'm currently running SQL Server 2005, and have been exploring the feasibility of grouping users to use a default file group and data file. This would be to group a set of users to have their new tables reside in a specific data file. I only see mechanisms for moving an already existing objects into a file group or to set the entire databases default file group--not a user level association or dynamic assignment of the default for that user, script, etc.
It's not looking good.
I'm looking for confimration that it's not possible or leads on doing so.
Thanx in advance
Hi to every one,
i have the export data from oracle to sql server.
how can i do it.
i tried through the wizard i am able to improt from oracle to sql server. but constratins are defined in oracle table are not comming in slq server.
i want to transfer data from oracle to sql(like tables,other database objects from oracle to sql).
how can i do it. is there any procedure .
could any one send the process.
regards,
kumar
pavan
Associate Oracle DBA
I have not used the copy objects wizard that much. I used it today to copy 4views from my dev box to production. It copied the views, but also wiped outmy data in all the tables that the views are built around!!! Inproduction!!!!Can someone provide me some insight into why this happened?Thanks,Chad
View 6 Replies View RelatedAnyone who has successfully done this. Do reply. Thanks
View 3 Replies View RelatedMy idea is to read in source and destination values from a table and using these values within a ForEach loop dynamically alter the source, destination and mapping on the data flow within the package. My reading on SSIS leads me to believe that these properties are not available for modification at run-time. Has anyone any ideas on how to accomplish this task. I have data in over 200 tables to import every 4 hours so I'd rather have to maintain 1 package rather than 200.
View 9 Replies View RelatedDoes anyone know how to copying database objects and data from Oracle 8 to SQL 2005 ?
View 1 Replies View Related