Microsoft SQL Server 2005 RTM merge replication issues

We are trying to implement merge replication between 2 servers. Database size is around 11GB.

When I tried to create publication, snaphot agent is failing with following errror.

Error: 14151, Severity: 18, State: 1.
Replication-Replication Snapshot Subsystem: agent NBTENTSQL1X-PayDirectWeb-PayDirectPub-2 failed. The
replication agent had encountered an exception.
Source: Replication
Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
Exception Message: String or binary data would be truncated.
Cannot find the object 'MSmerge_sel_4D388

The table where the snapshot is breaking contains around 169 columns. Can someone please help me get through this error ?

Are you using vertical partitioning to replicate subset of the columns of the table? You may be hitting an known issue in Microsoft SQL Server 2005 where the list of column names for the table is longer than 4K and hence gets truncated in an internal proc.

|||

Thanks Sudarshan, your reply is very helpful. No we are not using vertical partitioning.

If this is a known issue, did Microsoft have any immediate fix for it or we have wait for SQL2K5 SP2 ?

|||Please wait for SQL2K5 SP2. You can install the latest CTPs availalbe and test out that your scenarios works. If not, please reply back and we can take a look further.|||This is a known issue in SQL2K5 SP1 which is fixed in SP2.|||Does the same limitation apply to the complexity of joined tables in the filter? I'm getting an error in the snapshot agent, saying:

String or

binary data would be truncated.

Cannot find

the object 'MSmerge_ctsv_2C675AF5938E4EA1B7D0DCFF9ECAD9EB', because it does not

exist or you do not have permission.

sp_MS_marksystemobject:

Invalid object name '[dbo].[MSmerge_upd_2C675AF5938E4EA1B7

But this only happens when I add too many joined tables to the filter section of the publication.

Dan

Microsoft SQL Server 2005 RTM merge replication issues

We are trying to implement merge replication between 2 servers. Database size is around 11GB.

When I tried to create publication, snaphot agent is failing with following errror.

Error: 14151, Severity: 18, State: 1.
Replication-Replication Snapshot Subsystem: agent NBTENTSQL1X-PayDirectWeb-PayDirectPub-2 failed. The
replication agent had encountered an exception.
Source: Replication
Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
Exception Message: String or binary data would be truncated.
Cannot find the object 'MSmerge_sel_4D388

The table where the snapshot is breaking contains around 169 columns. Can someone please help me get through this error ?

Are you using vertical partitioning to replicate subset of the columns of the table? You may be hitting an known issue in Microsoft SQL Server 2005 where the list of column names for the table is longer than 4K and hence gets truncated in an internal proc.

|||

Thanks Sudarshan, your reply is very helpful. No we are not using vertical partitioning.

If this is a known issue, did Microsoft have any immediate fix for it or we have wait for SQL2K5 SP2 ?

|||Please wait for SQL2K5 SP2. You can install the latest CTPs availalbe and test out that your scenarios works. If not, please reply back and we can take a look further.|||This is a known issue in SQL2K5 SP1 which is fixed in SP2.|||Does the same limitation apply to the complexity of joined tables in the filter? I'm getting an error in the snapshot agent, saying:

String or

binary data would be truncated.

Cannot find

the object 'MSmerge_ctsv_2C675AF5938E4EA1B7D0DCFF9ECAD9EB', because it does not

exist or you do not have permission.

sp_MS_marksystemobject:

Invalid object name '[dbo].[MSmerge_upd_2C675AF5938E4EA1B7

But this only happens when I add too many joined tables to the filter section of the publication.

Dan

Metadata cleanup has deleted metadata at the Subscriber incorrectly?

Hi,

We have Microsoft SQL Server 2005 SP1 based merge replication application. This system consist of 1 publisher/distributor and about 700 subscribers. Publisher is MS SQL Server 2005 standard SP1 and subscribers are mostly MS SQL Server Express SP1s.

We started this system in September last year with migrating data as region based with one area at time. Everything went well until January 2007 when over 500 users were using new system. What is even strange that most errors comes on areas which were moved to new system on week number 2 on 2007 (8.1.2007->)

I have double and cross checked that these publications have exact the same parameteters as working publications.

The error message which comes on subscriber is following:

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199401) Get help: http://help/MSSQL_REPL-2147199401

When this error comes client subscription should not be expired yet. So subscription is expiring incorrectly earlier than expected. After reinit subcriber can syncronize without any errors. For now machines suffering of this error have been different machines so there is no certain information if this error is repeatable on same machine.

Real bad thing is that if user has't syncronized in regular basis there is possibility to that data may be lost because the reinit must be done without upload as error message says.

Regards,

Jouko

We have now installed SP2 to over 650 machines and we are still getting this error. So it seems obvious that SP2 do NOT fix this error. We have now open second time support case to Microsoft about this issue. First time we got answer to apply SP2 to all machines that are involved with replication.

Jouko

metadata cleanup 2147199401

Hi,
I'm getting the following error on SQL Server 2005
Error messages:
The Merge Agent failed after detecting that retention-based metadata cleanup
has deleted metadata at the Subscriber for changes not yet sent to the
Publisher. You must reinitialize the subscription (without upload). (Source:
MSSQL_REPL, Error number: MSSQL_REPL-2147199401)
Get help: http://help/MSSQL_REPL-2147199401
The subscription has been running fine for several months and re
initialising is painful due to the size of the DB and the connection speed.
Has anyone encountered this error or know of a fix?
Thanks
NW
There seems to be a bug with this that Microsoft is in the process of trying
to fix (from what I understand). I suggest you open a support incident with
Microsoft on this.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"NeilWake" <NeilWake@.discussions.microsoft.com> wrote in message
news:A8E78C08-E908-4D41-A03B-A5BE807C7D7C@.microsoft.com...
> Hi,
> I'm getting the following error on SQL Server 2005
> Error messages:
> The Merge Agent failed after detecting that retention-based metadata
> cleanup
> has deleted metadata at the Subscriber for changes not yet sent to the
> Publisher. You must reinitialize the subscription (without upload).
> (Source:
> MSSQL_REPL, Error number: MSSQL_REPL-2147199401)
> Get help: http://help/MSSQL_REPL-2147199401
> The subscription has been running fine for several months and re
> initialising is painful due to the size of the DB and the connection
> speed.
> Has anyone encountered this error or know of a fix?
> Thanks
> NW
>

Meta data??

Hi,
We have about 50 databases all of them are being merge replicated. On a
average , each database has about 150 tables which are published.
We get meta data retention error atleast one per week on a average. We have
two category of users, Users at the publisher server when they log (dialup
or dedicated Network) .Users logging on to the subscriber server -no dial up
always via dedicated network.
We notice meta-deta retention error message only when users connect to the
publisher server, that too when people dial up. Does this indicate any clues
?
Alternatively, to solve this meta-deta retention error ,we plan to drop
publication/delete replication and redo the replication setup process for
the database , where this error happens atleast once in a month. Does this
solve this problem. Is there any other solution ?
Please advise
Thanks,
Soura.
What is the exact error message or error number.
I don't understand how users come into the picture. Are you saying these
users are pulling the subscriptions? Are you using Windows Synchronization
Manager.
Dropping a publication and recreating it will definitely clean up merge
replication meta data, but there should be better ways of doing this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:68164873-E280-4B01-B8C8-54502CC2E4A4@.microsoft.com...
> Hi,
> We have about 50 databases all of them are being merge replicated. On a
> average , each database has about 150 tables which are published.
> We get meta data retention error atleast one per week on a average. We
> have
> two category of users, Users at the publisher server when they log (dialup
> or dedicated Network) .Users logging on to the subscriber server -no dial
> up
> always via dedicated network.
> We notice meta-deta retention error message only when users connect to the
> publisher server, that too when people dial up. Does this indicate any
> clues
> ?
> Alternatively, to solve this meta-deta retention error ,we plan to drop
> publication/delete replication and redo the replication setup process for
> the database , where this error happens atleast once in a month. Does this
> solve this problem. Is there any other solution ?
> Please advise
> Thanks,
> Soura.
>
|||Thanks for you response Mr Cotter. We have provided answers for your further
questions.
1. Error message : The merge process could not perform retention-based meta
data cleanup in database 'database name'".
2. No. We are NOT using Windows Synchronization Manager.
3. The users dialing up are not pulling the subscriptions, since they
connect only to the publiser. We notice the above error message once these
users connect after this replication error, we also notice timeout error.
When users log-out and log-in replication works normally as usual and
timeout message also gets solved.
4. You are right, there should be some better ways cleanup of meta deta,
besides droping and recreating. Eager to know what are they, so that we can
solve this problem.
thanks
Soura.
"Hilary Cotter" wrote:

> What is the exact error message or error number.
> I don't understand how users come into the picture. Are you saying these
> users are pulling the subscriptions? Are you using Windows Synchronization
> Manager.
> Dropping a publication and recreating it will definitely clean up merge
> replication meta data, but there should be better ways of doing this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:68164873-E280-4B01-B8C8-54502CC2E4A4@.microsoft.com...
>
>

Meta Data Cleanup For Merge Repl.

I am wondering specifically when the changes on the Publisher are cleaned
up. I have implemented merge replication with each publication having a
retention time (set in sp_addmergepublication) of 18 months and a
@.max_disretention (set in sp_adddistributiondb) also at 18 months. Also I
have a system where there is 1 Subscription PER Publication. The
Subscribers can be disconnected for long periods of time (I'm hoping for
less than 18 months). Now the changes on the Publisher will be stored on
the Distributor and will wait until a PULL Subscriber connects to Merge the
changes (changes made on both sides). From what I understand the merge
agent controls the meta data and will clean up the stored changes on the
distributor depending on the Retention period of the publication. So at
each merge agent run, anything older than 18 months that whether or not it
has been merged will be cleaned up (the max_disretention gets rid of changes
that have not been applied to subscribers too). Is this basically how the
cleanup of changes works?
When a connection is made I have a script that can be run to reinitialize a
subscription. It basically runs the merge agent, starts the snapshot agent,
sets the subscription to be reinitialized, and then runs the merge agent
again to apply the snapshot. Will Reinitializing a subscription clean up
the changes for a publication, kind of override the retention period?
Any ideas as to when the Change log (change info stored for a merge) is
cleaned up?
thanks,
Nate
Oops, the Distribution DB doesn't do anything during merge replication so
min_distretention and max_distretention don't apply. Just the Retention
period of the Publications. Can anyone confirm that the MetaData is only
cleaned up by the merge agent using the Retention period? And, that the
data is not cleaned up when a subscription is reinitialized?
thanks,
nate

Meta Data Cleanup Error

Hi,
We are Merge replicating about 50 Production databases. We had planned to
monitor the meta deta clean up error during replication.
While we were monitoring the data to be updated during replication in one of
these databases, noticed 4 records to be merge replicated ( 3 insert and 1
update). At this moment we noticed the meta data clean up error. "The merge
process could not perform retention-based meta data cleanup in database
'database name'". The replication was in retrying mode and additionally the
following errors were reported
a) The subscription to publication 'XXXX' is invalid.
b) The process is running and is waiting for a response from one of the
backend connections'
During this process there we no issues in the remaining 49 databases,
replication was proceeding as usual.
In this database where we noticed the the above errors, the replication was
in sync in another 10 minutes, without any manual intervention.
What triggers to raise these error messages.
Please let us know what correction action we need to take for these error
messages
Thanks,
Soura.
Hi,
I got it from One article...
"The most common reason I can think of is that the query timed out. Sometime
retention based cleaup may need to cleanup a lot of rows and that can take
time. Rerunning the merge with a larger value for the querytimeout parameter
should fix this problem."
Hope this will help
Herbert
"SouRa" wrote:

> Hi,
> We are Merge replicating about 50 Production databases. We had planned to
> monitor the meta deta clean up error during replication.
> While we were monitoring the data to be updated during replication in one of
> these databases, noticed 4 records to be merge replicated ( 3 insert and 1
> update). At this moment we noticed the meta data clean up error. "The merge
> process could not perform retention-based meta data cleanup in database
> 'database name'". The replication was in retrying mode and additionally the
> following errors were reported
> a) The subscription to publication 'XXXX' is invalid.
> b) The process is running and is waiting for a response from one of the
> backend connections'
> During this process there we no issues in the remaining 49 databases,
> replication was proceeding as usual.
> In this database where we noticed the the above errors, the replication was
> in sync in another 10 minutes, without any manual intervention.
> What triggers to raise these error messages.
> Please let us know what correction action we need to take for these error
> messages
> Thanks,
> Soura.

meta data cleanup

Hi
I am running a couple of sql 2000 SP3a servers with merge and snapshot
replication. One server acting as publisher and distributor and the rest
subscribers. On one of the server I have got the error below and have tried
most of the suggestions by msdn. This server has not crashed ever before or
any hardware problems. It has been running for a couple of months and no
problems. This has not happened no any of the other servers. Any
suggestions would be greatly appreciated as the only resolution I have left
is to bring up a new instance, setup replication and see if this would
resolve the issue. Thanx
Server: EASTSRV3
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/14/2005 12:57:26 PM]EASTSRV3.ICASData: {call sp_MSgetversion }
Percent Complete: 2
Connecting to Subscriber 'EASTSRV3'
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'HO_Master'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
The merge process is cleaning up meta data in database 'ICASData'.
The merge process could not perform retention-based meta data cleanup in
database 'ICASData'.
Percent Complete: 0
The merge process could not perform retention-based meta data cleanup in
database 'ICASData'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147199467
Message: The merge process could not perform retention-based meta data
cleanup in database 'ICASData'.
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_mergemetadataretentioncleanup(?, ?, ?)}
Percent Complete: 0
Category:SQLSERVER
Source: EASTSRV3
Number: 11
Message: General network error. Check your network documentation.
Message posted via http://www.sqlmonster.com
Jayanthi from MS posted this before:
"The most common reason I can think of is that the query timed out. Sometime
retention based cleaup may need to cleanup a lot of rows and that can take
time. Rerunning the merge with a larger value for the querytimeout parameter
should fix this problem."
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul
Thanx for the reply.
I have played with the agent profiles aswell. From default to 1800. This
has been going on for like two days now no synch at all. I have tried to
reindex also as this was also one of the msdn suggestions.
Message posted via http://www.sqlmonster.com

Message: Missing end comment mark '*/'.

Hi,

I created a web syncronization merge replication ,both servers and clients are sql server 2005 and everything is fine with the settings , but while synchronization and after i received some sch files i got the following message at the subscribers on the initial snapshot operation:

2006-06-27 07:50:14.471 The schema script 'myfile_311.sch' could not be propagated to the subscriber.
2006-06-27 07:50:14.471 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'myfile_311.sch' could not be propagated to the subscriber.
2006-06-27 07:50:14.471 Category:NULL
Source: Microsoft SQL Native Client
Number: 113
Message: Missing end comment mark '*/'.

Any idea ?

Tarek Ghazali

SQL Server MVP

My guess is that myfile_311.sch contains a stored procedure|view|function with something like

/*

...

go

*/

There is a longstanding limitation in our batch parsing logic that will erroroneously recognize 'go's embedded in comments to be batch delimiters. The only workaround for this is to edit the script generated by the snapshot agent to remove the offending comment. We really hope that we can address this issue in the near future.

-Raymond

Message pane in QA

Hi
Is it possible to get the text from the Message tab in QA written to a log
in some way? We are running a lot of scripts to convert and later merge 2 of
our databases, and it would be helpful if I could get the results that are
written to the message tab directly into a logfile instead of having to
manually cut and paste it into a document.
Regards
Steen
Not without executing to file, which probably isn't what you are looking for. Run a Profiler trace
with appropriate events, columns and filters instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:eimZkG0ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi
> Is it possible to get the text from the Message tab in QA written to a log in some way? We are
> running a lot of scripts to convert and later merge 2 of our databases, and it would be helpful if
> I could get the results that are written to the message tab directly into a logfile instead of
> having to manually cut and paste it into a document.
> Regards
> Steen
>
|||Hi
Have you thought about using osql?
John
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eimZkG0ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi
> Is it possible to get the text from the Message tab in QA written to a log
> in some way? We are running a lot of scripts to convert and later merge 2
> of our databases, and it would be helpful if I could get the results that
> are written to the message tab directly into a logfile instead of having
> to manually cut and paste it into a document.
> Regards
> Steen
>
|||I would put the script in a file and use OSQL ie
osql -i c:\myfile.sql -o c:\myoutfile.txt -E
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eimZkG0ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi
> Is it possible to get the text from the Message tab in QA written to a log
> in some way? We are running a lot of scripts to convert and later merge 2
of
> our databases, and it would be helpful if I could get the results that are
> written to the message tab directly into a logfile instead of having to
> manually cut and paste it into a document.
> Regards
> Steen
>

Message pane in QA

Hi
Is it possible to get the text from the Message tab in QA written to a log
in some way? We are running a lot of scripts to convert and later merge 2 of
our databases, and it would be helpful if I could get the results that are
written to the message tab directly into a logfile instead of having to
manually cut and paste it into a document.
Regards
SteenNot without executing to file, which probably isn't what you are looking for
. Run a Profiler trace
with appropriate events, columns and filters instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:eimZkG0ZFHA.2420@.TK2MSFTNGP12.phx
.gbl...
> Hi
> Is it possible to get the text from the Message tab in QA written to a log
in some way? We are
> running a lot of scripts to convert and later merge 2 of our databases, an
d it would be helpful if
> I could get the results that are written to the message tab directly into
a logfile instead of
> having to manually cut and paste it into a document.
> Regards
> Steen
>|||Hi
Have you thought about using osql?
John
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eimZkG0ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi
> Is it possible to get the text from the Message tab in QA written to a log
> in some way? We are running a lot of scripts to convert and later merge 2
> of our databases, and it would be helpful if I could get the results that
> are written to the message tab directly into a logfile instead of having
> to manually cut and paste it into a document.
> Regards
> Steen
>|||I would put the script in a file and use OSQL ie
osql -i c:\myfile.sql -o c:\myoutfile.txt -E
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eimZkG0ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi
> Is it possible to get the text from the Message tab in QA written to a log
> in some way? We are running a lot of scripts to convert and later merge 2
of
> our databases, and it would be helpful if I could get the results that are
> written to the message tab directly into a logfile instead of having to
> manually cut and paste it into a document.
> Regards
> Steen
>

Message pane in QA

Hi
Is it possible to get the text from the Message tab in QA written to a log
in some way? We are running a lot of scripts to convert and later merge 2 of
our databases, and it would be helpful if I could get the results that are
written to the message tab directly into a logfile instead of having to
manually cut and paste it into a document.
Regards
SteenNot without executing to file, which probably isn't what you are looking for. Run a Profiler trace
with appropriate events, columns and filters instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:eimZkG0ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi
> Is it possible to get the text from the Message tab in QA written to a log in some way? We are
> running a lot of scripts to convert and later merge 2 of our databases, and it would be helpful if
> I could get the results that are written to the message tab directly into a logfile instead of
> having to manually cut and paste it into a document.
> Regards
> Steen
>|||Hi
Have you thought about using osql?
John
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eimZkG0ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi
> Is it possible to get the text from the Message tab in QA written to a log
> in some way? We are running a lot of scripts to convert and later merge 2
> of our databases, and it would be helpful if I could get the results that
> are written to the message tab directly into a logfile instead of having
> to manually cut and paste it into a document.
> Regards
> Steen
>|||I would put the script in a file and use OSQL ie
osql -i c:\myfile.sql -o c:\myoutfile.txt -E
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:eimZkG0ZFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi
> Is it possible to get the text from the Message tab in QA written to a log
> in some way? We are running a lot of scripts to convert and later merge 2
of
> our databases, and it would be helpful if I could get the results that are
> written to the message tab directly into a logfile instead of having to
> manually cut and paste it into a document.
> Regards
> Steen
>

Merging two tables

SQL 7
How do I merge two table's records? In other words, merge
T1 into T2. There are records in both tables that are the
same, but where T1 has a record that T2 doesn't have,
T1's record needs to be inserted into T2.
I've been reading Join syntax til my head is spinning.
Thanks,
DonInsert into T2
select * from T1 where T1.PrimaryKey
where T1.PrimaryKey NOT IN (Select PrimaryKey from T2)
Replace PrimaryKey with the unique priamry key from each table.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:7ff101c402df$8f68d030$a101280a@.phx.gbl...
> SQL 7
> How do I merge two table's records? In other words, merge
> T1 into T2. There are records in both tables that are the
> same, but where T1 has a record that T2 doesn't have,
> T1's record needs to be inserted into T2.
> I've been reading Join syntax til my head is spinning.
> Thanks,
> Don
>|||Don,

> How do I merge two table's records? In other words, merge
> T1 into T2. There are records in both tables that are the
> same, but where T1 has a record that T2 doesn't have,
> T1's record needs to be inserted into T2.
insert T2
select * from T1
where not exists (select * from T2 where T1.keycol = T2.keycol)
Linda

Merging two sql server DBs

Hi All,
i want to merge two sql server DBs (SourceDB,DestinationDB).
there could be some common tables between SourceDB and DestinationDB.
if that happens than i want sourceDb to overrite Destination DB table.
i know i can do this using exportWizard/DTS.
but problem with this is my source DB is huge and export wizard takes lot of
time.
i do not think i can do this using deattach\attach DB or using backup restor
e because both of this will overrite the detination database where as i want
to merge the two.
this is a one time job.
any ideas how i can do this.
thanks
siddharthHi
Are both databases's tables identical?
If they are you can create a view that contains data set of these tables
CREATE VIEW v_myview
AS
SELECT dbname1.dbo.table
UNION --or UNION ALL
SELECT dbname2.dbo.table
GO
SELECT * FROM v_myview
> if that happens than i want sourceDb to overrite Destination DB table
SELECT * INTO dbname2.dbo.NewTable FROM dbname1.dbo.table
GO
DROP TABLE dbname2.dbo.table
GO
--Run on dbname2
EXEC sp_rename 'NewTable','TABLE'
"siddharth" <anonymous@.discussions.microsoft.com> wrote in message
news:D9C89088-714B-4A6B-8AF8-10E0351EBF8F@.microsoft.com...
> Hi All,
> i want to merge two sql server DBs (SourceDB,DestinationDB).
> there could be some common tables between SourceDB and DestinationDB.
> if that happens than i want sourceDb to overrite Destination DB table.
> i know i can do this using exportWizard/DTS.
> but problem with this is my source DB is huge and export wizard takes lot
of time.
> i do not think i can do this using deattach\attach DB or using backup
restore because both of this will overrite the detination database where as
i want to merge the two.
> this is a one time job.
> any ideas how i can do this.
> thanks
> siddharth
>|||Hi
It sounds like you have a source and destination database the wrong way arou
nd! if you restored what you currently call the source database onto the des
tination server, you would then only need to more from the destination datab
ase the objects that are no
t in the source database! It would be possible to do this from the system ta
bles. DTS may still be an option to transfer the data or alternatively a DMO
program. If you don't have indexes, primary keys etc a straight forward SEL
ECT INTO statement would be
possible.
Alternatively you may want to look at something like the red gate tools to s
ee if they fulfil your requiremets:
http://www.red-gate.com/sql/summary.htm
John
-- siddharth wrote: --
Hi All,
i want to merge two sql server DBs (SourceDB,DestinationDB).
there could be some common tables between SourceDB and DestinationDB.
if that happens than i want sourceDb to overrite Destination DB table.
i know i can do this using exportWizard/DTS.
but problem with this is my source DB is huge and export wizard takes lot of
time.
i do not think i can do this using deattach\attach DB or using backup restor
e because both of this will overrite the detination database where as i want
to merge the two.
this is a one time job.
any ideas how i can do this.
thanks
siddharth

Merging two reports at runtime..

How do I merge two or more reports at runtime?
For example :
I have reports (or pages) A, B and C. The user chose to print pages A and C
only. I will have to merge A and C and print them as one report.
Thanks in advance for any suggestions/solutions.
-SurendraTake a look at http://blogs.msdn.com/bryanke/articles/71491.aspx. You could
use this code in a custom app, have the users pass report names as
parameters and then use these parameters in your code to print the
corresponding reports accordingly.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Surendra" <spepakay@.hotmail.com> wrote in message
news:%23iEJB7GiEHA.3988@.tk2msftngp13.phx.gbl...
> How do I merge two or more reports at runtime?
> For example :
> I have reports (or pages) A, B and C. The user chose to print pages A and
C
> only. I will have to merge A and C and print them as one report.
> Thanks in advance for any suggestions/solutions.
> -Surendra
>|||Thanks for the suggestion Ravi!!
That was one option I had considered and was looking for a way to accomplish
this via the URL. Maybe I am being too naive and ambitious. Please let me
know if you have any other suggestions.
Thanks again
Surendra
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:OdCg6tIiEHA.3944@.tk2msftngp13.phx.gbl...
> Take a look at http://blogs.msdn.com/bryanke/articles/71491.aspx. You
could
> use this code in a custom app, have the users pass report names as
> parameters and then use these parameters in your code to print the
> corresponding reports accordingly.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Surendra" <spepakay@.hotmail.com> wrote in message
> news:%23iEJB7GiEHA.3988@.tk2msftngp13.phx.gbl...
> > How do I merge two or more reports at runtime?
> >
> > For example :
> >
> > I have reports (or pages) A, B and C. The user chose to print pages A
and
> C
> > only. I will have to merge A and C and print them as one report.
> >
> > Thanks in advance for any suggestions/solutions.
> > -Surendra
> >
> >
>

merging two mssql db

I have a problem that I need help on.

Right now, I have two MSSQL server and I am trying to merge them into one.

The problem is, I do not know the login Id and password for some of the users.

I know the login information are stored under syslogins at the master db.

How do I get the information out and append to the syslogins table of the second mssql db?

Any help would be appreciated.

--alucarrdTry to use linked server and query like this:

insert sysxlogins
(srvid,sid,xstatus,xdate1,xdate2,name,password,dbi d,language)
select srvid,sid,xstatus,xdate1,xdate2,name,password,dbid ,language
from remote.master.dbo.sysxlogins
where name='name'-- if not all of them|||Thank you snail,

I can smile now.

However, I tried with this statement, and now I am getting an error:

Cannot insert duplicate key row in object 'sysxlogins' with unique index 'sysxlogins'.
The statement has been terminated.

I got the linked server set up correctly and I can see the information of the remote server by checking the select statement on the sysxlogins table in the remote server.

However, I ran the script you showed me, and that's the error I got.

This is the script that I ran:

insert sysxlogins(srvid,sid,xstatus,xdate1,xdate2,name,
password,dbid,language)select srvid,sid,xstatus,xdate1,xdate2,
name,password,dbid,language
from remote1.master.dbo.sysxlogins where name<>'sa'

Thank you for helping me out.|||I think the reason I got the error is because the user exist already on the destination machine.

Thank you.

I will just go from here.

Merging two databases into one

I currently have two databases which contain the same database structure
but different data. How do I merge the two databases into one single
database?
*** Sent via Developersdex http://www.developersdex.com ***You can do it using T-SQL queries.
How do you want to merge the data? Just copy all the data from dbA to dbB or
all the data from dbB to dbA. Do you have to resolve any conflicts? that is,
could the same key can have different data in different databases?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Enoch Chum" <enoch.chum@.wcb.ab.ca> wrote in message
news:ucNXVxIlFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I currently have two databases which contain the same database structure
> but different data. How do I merge the two databases into one single
> database?
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Hi,
To add on to Vyas, Incase if you have same table definitions in both
databases and different data then use:-
Insert into DBnameA..Tablename select * from DBnameB..tablename where
condition...
Do the above for all tables.. In your query take care of constraints as well
as identity.
Thanks
Hari
SQL Server MVP
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGmJfFPlFHA.2860@.TK2MSFTNGP15.phx.gbl...
> You can do it using T-SQL queries.
> How do you want to merge the data? Just copy all the data from dbA to dbB
> or all the data from dbB to dbA. Do you have to resolve any conflicts?
> that is, could the same key can have different data in different
> databases?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Enoch Chum" <enoch.chum@.wcb.ab.ca> wrote in message
> news:ucNXVxIlFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I currently have two databases which contain the same database structure
>> but different data. How do I merge the two databases into one single
>> database?
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>

Merging two databases into one

I currently have two databases which contain the same database structure
but different data. How do I merge the two databases into one single
database?
*** Sent via Developersdex http://www.codecomments.com ***
You can do it using T-SQL queries.
How do you want to merge the data? Just copy all the data from dbA to dbB or
all the data from dbB to dbA. Do you have to resolve any conflicts? that is,
could the same key can have different data in different databases?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Enoch Chum" <enoch.chum@.wcb.ab.ca> wrote in message
news:ucNXVxIlFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I currently have two databases which contain the same database structure
> but different data. How do I merge the two databases into one single
> database?
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Hi,
To add on to Vyas, Incase if you have same table definitions in both
databases and different data then use:-
Insert into DBnameA..Tablename select * from DBnameB..tablename where
condition...
Do the above for all tables.. In your query take care of constraints as well
as identity.
Thanks
Hari
SQL Server MVP
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGmJfFPlFHA.2860@.TK2MSFTNGP15.phx.gbl...
> You can do it using T-SQL queries.
> How do you want to merge the data? Just copy all the data from dbA to dbB
> or all the data from dbB to dbA. Do you have to resolve any conflicts?
> that is, could the same key can have different data in different
> databases?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Enoch Chum" <enoch.chum@.wcb.ab.ca> wrote in message
> news:ucNXVxIlFHA.1968@.TK2MSFTNGP14.phx.gbl...
>

Merging two databases into one

I currently have two databases which contain the same database structure
but different data. How do I merge the two databases into one single
database?
*** Sent via Developersdex http://www.codecomments.com ***You can do it using T-SQL queries.
How do you want to merge the data? Just copy all the data from dbA to dbB or
all the data from dbB to dbA. Do you have to resolve any conflicts? that is,
could the same key can have different data in different databases?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Enoch Chum" <enoch.chum@.wcb.ab.ca> wrote in message
news:ucNXVxIlFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I currently have two databases which contain the same database structure
> but different data. How do I merge the two databases into one single
> database?
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Hi,
To add on to Vyas, Incase if you have same table definitions in both
databases and different data then use:-
Insert into DBnameA..Tablename select * from DBnameB..tablename where
condition...
Do the above for all tables.. In your query take care of constraints as well
as identity.
Thanks
Hari
SQL Server MVP
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGmJfFPlFHA.2860@.TK2MSFTNGP15.phx.gbl...
> You can do it using T-SQL queries.
> How do you want to merge the data? Just copy all the data from dbA to dbB
> or all the data from dbB to dbA. Do you have to resolve any conflicts?
> that is, could the same key can have different data in different
> databases?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Enoch Chum" <enoch.chum@.wcb.ab.ca> wrote in message
> news:ucNXVxIlFHA.1968@.TK2MSFTNGP14.phx.gbl...
>