Hi folks!
I have a question - do you know if there is a sql command to merge two files? Basically, I want to merge the header for the xml file with actual xml data (generated via SQL) in another file - is that possible without running any DOS commands?
Something like header.txt+myfile.xml???
Thanks so much!
-Parul
Is it possible to use merge replication without direct connection from suscriber/publisher? I'm trying to find a solution to use files for send/receive merge-replication info from/to server.
^_^,
Thank You.
In my enviornment i have one database with 6 ndf files and 5 ldf files and one mdf file.Actuvally what i am looking is to merge 6 ndf files into one ndf file and 5 ldf files into one ldf file.is it possible to do like this? , i tried using MOVE and TO option while backup is restorating but getting below error messages.
ERROR:
Msg 3176, Level 16, State 1, Line 4
File 'J:NDFabc.ndf' is claimed by 'Finance_data2'(4) and 'Finance_data1'(3). The WITH MOVE clause can be used to relocate one or more files.
How can i apply a BCP files generate by Distributor in the Suscribers.
I donīt know wich parameters BCP.EXE need.
Thanks
I'll start off by saying these questions were submitted to Microsoft and no
one can give us an answer without them doing a source code review. Their
response was "these are internal BCP scripts that are used by SQL"....yes, I
know that....that doesn't help to explain why the system created them. At
this point, I don't want my compary to pay support hours for Microsoft
personnel to learn what their software does.
--------------------
We've had merge publication in place for several years.
Last year, we added some tables/articles to publications.
After the upgrade from SQL Server 2000 SP4 Standard to SQL Server 2005 SP2
Standard, when the snapshot was run, the system created some new files that
had never existed before. Also, and more importantly, these files were only
created for the articles added after the initial snapshot was created.
Articles existing in the intial snapshot did not have these type of files
created.
Examples of these files for a SA_MOVE table that was added to an existing
publication:
MSmerge_contents_SA_MOVE.bcp
MSmerge_contents_SA_MOVE_forall.bcp
MSmerge_rowtrack_SA_MOVE90.bcp
sysmergesubsetfilters_SA_MOVE.bcp
sysmergesubsetfilters_SA_MOVE90.bcp
Also some new bcps created were:
MSmerge_contents90_forall.bcp
MSmerge_genhistory90.bcp
MSmerge_rowtrack90.bcp
sysmergesubsetfilters90.bcp
The system wanted to apply these files before it would start synchronizing
data so they had to be delivered to the subscriber. It appears it was the
sysmergesubsetfilters_ files that it wanted to apply. These are all 0 in
size and we do not have filter on(at least not as far as I know...see the
addmergearticle code below).
New databases created this year that had all of the articles defined in the
publication before the initial snapshot was taken did not get these
additional files created, nor did the system want to try to send files to the
subscriber before syncrhonization could begin.
This is an example of how an article had been cretead under SQL Server 2000
when adding a new database, before an initial snapshot is run:
exec sp_addmergearticle @publication = N'Last', @article = N'SA_MOVE',
@source_owner = N'dbo', @source_object = N'SA_MOVE', @type = N'table',
@description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop',
@creation_script = null, @schema_option = 0x000000003000EFF1,
@article_resolver = null, @subset_filterclause = null, @vertical_partition =
N'false', @destination_owner = N'dbo', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true',
@check_permissions = 0
This is an example of how an article was added under SQL Server 2000 to an
existing publication that already had the initial snapshot run:
exec sp_addmergearticle @publication = N'Last', @article = N'SA_MOVE',
@source_owner = N'dbo', @source_object = N'SA_MOVE', @type = N'table',
@description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop',
@creation_script = null, @schema_option = 0x000000000000EFF1,
@article_resolver = null, @subset_filterclause = null, @vertical_partition =
N'false', @destination_owner = N'dbo', @auto_identity_range = N'false',
@verify_resolver_signature = 0, @allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true', @check_permissions = 0,
@force_invalidate_snapshot = 1
A few questions:
Does anyone have an idea why the system created these files are part of the
2005 upgrade?
What is the real purpose of the files with names such as _forall.bcp ?
Thanks for any help/info on this,
Doug
Hi All!
Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86)
Mar 23 2007 16:15:11
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
40 subsribers.
Adding new article.
On any aubscribe need regenerate snapshot agent agent.
The tables are very big. With filters.
Regenerating process anew copy data in in ...ReplDataame.bcp, during 30-40 minutes for any subscriber!
Full work to small correct of replication flow during 2-3 days....
Is it possible to initialize subscription without copying bcp data?
Hi gurus,
I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).
Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?
Thanks a lot in advance!
I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies View RelatedI have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
I have database on SQL Server 2000 set up with a merge publication.This publication is configured with a number of dynamic filters toreduce the amount of data sent to each client. Each client has ananonymous pull subscription. The merge process can be triggered by thewindows sync manager and my application.To improve performance I have created some helper tables to hold themapping between user login and primary keys of selected entities.For the replicated data to be correct the contents of the helper tablesneeds to be up to date.I need to fire off a stored procedure on the publisher beforereplication starts to verify that this data is up to date. I can notsee any documented way of doing this however I have been experimentingwith some unorthodox systems.Firstly has anyone any ideas?I have been considering adding a trigger to some of the tables used bythe Microsoft replication code - yes I know this is very nasty.My problems arise because executing this stored procedure will causesome data to be updated. In updating data we could create a newgeneration in the database. I must therefore run my stored procedurebefore any the Microsoft code makes any generation checks / updates.Anyone done anything similar, Anyone have any better ideas?Any comments would be gratefully received.
View 1 Replies View RelatedHi,
View 36 Replies View RelatedHi,
I'm using merge replication to maintain a backup copy of my main (publisher)MSDE database. A push subscription periodically (1 per minute) updates the backup DB.
It's intended that if the main db goes down then the backup (subscription) db can be configured as a publisher. This must all be performed via scripting.
The initial configuration of the main publisher and subscription is controlled via scripting, which works fine.
The problems occur when I try to configure the subsciber to become a publisher. A script is executed on the subscriber but fails at the point when it's configuring the publisher detail. The error is something like "unable to configure a publication for a database setup as an anonymous subscription".
I'm guessing that there are subscritpion artifacts added to the database which need to be removed before it can be configured as a new publisher.
Please help,
Jez W
Hi,
We just upgraded 2 sql servers from sql server 2000 sp3a to sql server 2005 build 2153, and merge rep exists between these 2 servers.
However, after sql server upgrade, we had to reinitialize merge replication and now the merge agent is reporting 2 errors
1)
Error messages:
The Merge Agent failed to upgrade triggers, metadata and stored procedures on the Subscriber to versions compatible with SQL Server 2005. Restart synchronization, and if this failure continues to occur reinitialize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199403)
Get help: http://help/MSSQL_REPL-2147199403
Invalid column name 'metadata_select_proc'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207
Invalid column name 'delete_proc'. (Source: MSSQLServer, Error number: 207)
Get help: http://help/207
2)
Command attempted:
{call sp_MSensure_single_instance (N'Merge Agent Name', 4)}
Error messages:
The merge process could not connect to the Publisher 'Server:database'. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199368)
Get help: http://help/MSSQL_REPL-2147199368
Another merge agent for the subscription(s) is running or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)
Get help: http://help/21036
looking inside the sp_MSensure_single_instance stored procedure, it's trying to obtain an exclusive lock on resource 'Merge Agent Name' but fails and returns -1
this is the stored procedure executed by sp_MSensure_single_instance and @retcode = -1 and thus cannot obtain exclusive lock on resource
EXEC @retcode = sys.sp_getapplock @Resource = 'Merge Agent Name',
@LockMode = 'Exclusive',
@LockOwner = 'Session',
@LockTimeout = 0,
@DbPrincipal = 'MSmerge_PAL_role'
so i think it's failing because for some reason MSmerge_PAL_role database role does not have enough rights to obtain exclusive lock on necessary resources
i've tried many things to fix this, including scripting out and dropping all merge rep, turning off replication db option and reapplying replication create scripts on the server, and even manually deleting the MSmerge_PAL_role database role but still doesn't work
can anyone help?
Thanks
Hi,
Transactional replication allows updatable subscriptions where changes at the subscriber are replicated up to the publisher, this can happen via Immediate Updating subscriptions, Queue subscriptions and P2P (new in SQL 2005), all forms of Transactional replication.
Any compared document between merge replication and Transactional with updatable subscribtion ?
Thanks,
Tarek Ghazali
SQL Server MVP
Hi!
I'm about to setup a merge replication Sql server 2005 and Sql server CE on pda's. There are 10 service guys in the field which need to sync their data with the server.
I don't want each guy to have all the data from the db. What I found is that I can use SUSER_NAME() to filter data. However I haven't created unique db users on the server. I only have EmployeedId to identify the users. My question is, must I redesign my office application which feeds the sql server to suit SUSER_NAME() or do I have to create a publication for each user in the field??
Hi..
is it possible to merge 2 columns into 1 to hold data like this... When the user imports the file in particular file they will be
ACT_ID1 Tot_ACT1 ACT_ID2 TOT_ACT2 ...... until 15
BB 1245.45 CT some amount .....
The 2 letter character may change prob for each file.. at leat i know of somethem may change
So while i transfer the data to the production database can is it possible to do some thing like
COL1 BB 1245.45
COL2 CT 12456.12 etc..
Any help will be appreciated.
Regards
Karen
Dear DBforum members
I have a problem Setting up pull subscription from a publishser to a subscriber , but I could set up Push subscription with out any problem between these two server .. can any one help me out.
I run SQL Server 2000 Enterprise edition.
I get the following error
"Publication 'ProdDB' does not exist. The step failed."
where ProdDB is the publication database from which my Push subscription is running.
thanx
Logs
I have a table with a field called SeqId which is not an identity nor a sequence but a kind of autonumber field (max(SeqId) + 1). Now I have to do a MERGE between 2 tables where the one with SeqId is the target.
How can I get the next SeqId for every row added? I tried this:
MERGE dbo.CRM_MNP_ORIGINAL_NRN AS T
USING dbo.seriesnacionales AS S
ON (T.RANGE1 = S.RANGOINI )
WHEN NOT MATCHED THEN
INSERT (SeqId, Range1, Range2, OPERATORCODE, NRN, StartDate, CreateDate)
VALUES((SELECT dbo.FN_GetNextSeqId4CRM_MNP_ORIGINAL_NRN()), S.RangoIni, S.RangoFin, S.IdOperador,
'TEST_M', Convert(DATETIME, FECASIGNA , 103), SYSDATETIME())
WHEN MATCHED THEN
UPDATE SET T.Range1 = S.RangoIni, T.Range2 = S.RangoFin, T.OPERATORCODE = S.IdOperador
OUTPUT $action, Inserted.*, Deleted.*;
where the function just return Max(SeqId) + 1
but I always get the same value for that field.
Hi, I have two databases on MS. SQL Server 200 the first one is themain DB and the second one is a replica of it, both of them indifferent territories .I connect them using merge replication justwhen I need to merge the data.The problem is when I make import data or export data from anylocation (MS access ,MS SQL server ) to the second one (subscriber DB)or the main DB(publisher and distributor ) ,the imported data doesnot replicated to the other DB ???Normal transaction replicated smoothly.I hope you can help me.Thanks in advance.Mahmoud Khebaisa.
View 1 Replies View RelatedI need to come up with a solution to my replication issue. The solution that I come up with involves using one publisher to push transactional and merge replication. The database that is published is the same.
Would this cause any issues?? Can this be done?? I would think that if a table is needed for both, I would have to create a separate publication for each type right??
Is there any documentation anywhere to help prove if this is possible??
I have some problem about Merge Replication.i'm trying to merge Database A to Database B in local Server. So Database i want publisher contain this:
Because only Data in Table change therefore so i choosed it 100% Snapshot was generated after that ( Problem not a valid window user i already figured out )After that. I created Local Subscription in same Server ( Pull subscriptions and Subscription type: Client ),now problem is throw out."The schema script 'vwBuyADT_513.sch' could not be propagated to the subscriber."
I tried research so many time in Google but any information i found isn't useful for this problem.This problem still can ignore,Synchronization still running. But after 4-5 Hours running..This messages throw out: "The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation"
My question is:
Is there anyway solve 2 probem? :
1. "The schema script 'vwBuyADT_513.sch' could not be propagated to the subscriber."
2. "The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation"
I am using SQL Server 2000 SP4 running on a MS Server 2003 with SP2. I am preforming Merge Replication on a continouas basis and the Merge Agent keeps stopping with the error 203 The process could not enumerate changes at the 'Publisher'. There is no additional information available with this error. Does anyone have any sugestions as to why this is happening. I can manually re-start the agent but it will fail again for particular reason.
View 1 Replies View Related
Hello,
I would like to know what alternatives are avaliable to SQL Server Merge Replication. I am also looking for Third Party Tools. Can anyone name a few for me.
Hi, I posted about this before, and set out on my own to get this working, and haven't been able to. I'm trying to get merge replication working with my SQL server 2000, and after 2 weeks I still have nothing. I've gone through multiple 'walkthroughs' which all brought me to the same point. I'm getting down to crunch time, and I'm either going to use this or implement my own merge algorigthm (I'd much rather use this). So here's where I've gotten to:
View 4 Replies View RelatedHi,
I am trying to replicate a production database server,on sql server 2000 at a particular geographic location to a new failover database server on sql server 2000 in a different geographic location via internet. The intention here is to use the failover database during times when the production server is down or busy and the synchronization needs to be sceduled for every 10 min.
It will be of huge help if any expert could give the detailed process involved and any precautions that need to be taken. I also need to keep inmind to use the ever/odd sequence number generator during the replication implementation.
Also, Whether to use row-level or column-level tracking?
thanks
SV
Hello,
I am having problems with my sql merge replication. Whenever a user syncs up to my main database, most of their records are deleted instead of being merged.
Or the records on the main database are inserted and it replaces the whole table with the records on the remote laptops. Is there a way to prevent this from happening? Someone please help me.
Corey
I do a merge replication between Sql server 2000 and SQLCE 2.0
On my SQL2000 I have 4 tables i want to merge (specific columns only ) in 1 table for Merge with my SQLCe ( the table will be use for read only)
Question 1:
What is the best pratice for keep the information update?
Run store procedure before the synch for re-populate the table?:confused: or Make Trigger INSERT, UPDATE, DELETE in the all 4 table?:confused: or a mixte?:confused:
Question 2:
Does someone know about some web site talk about this type of trick?
Thanks
Merging table :
--------Dummy TABLE
create table #Tbl1 (date1 date,WSH varchar(10),ITN int,Executions int)
insert into #Tbl1 (date1 , WSH , ITN , Executions)
select '20130202' ,'ABC', 1 , 100
union all
select '20130203' ,'DEF', 1 , 200
[Code] .....
I want Result like this :
date1 WSH ITNExecutionsMCGPositions
2013-02-02 ABC 1 100 2 500
2013-02-03 DEF1 200 NULL NULL
2013-02-05 NULLNULL NULL 2 600
basically i have data like this
order_key comment
1 A
1 B
1 C
2 B
2 D
the data intends to be like this
order_key comment
1 A,B,C
2 B,D
I have a table that looks like this ...
idtype_codephone_num
11111-111-1111
12222-222-2222
21111-111-1111
32222-222-2222
I want to merge the data to look like this ...
idphone1 phone2
1111-111-1111222-222-2222
2111-111-1111NULL
3NULL222-222-2222
Basically if the type code is 1 one then move the data to column phone1, if the type is 2 then move it to column phone2.
This would be fairly simple if we always have type codes 1 and 2. But sometimes we can have type 1 and not type 2, or we could have type 2 and not type1.
Right now we only have 2 type codes. But, in the future we could be adding a 3rd type. So that would add a 3rd column (phone3).
Below is my code that I have written. I move the data into a temp table then list it. I am thinking of making this a view to my table. It works just fine. My question is, is there a better and more efficient way of doing this?
CREATE TABLE #Contacts (
id INT PRIMARY KEY,
phone1 VARCHAR(15),
phone2 VARCHAR(15)
)
-- Insert the records for type 1
INSERT INTO #Contacts
SELECT id,
phone_num,
NULL
FROM test1
WHERE type_code = '1'
-- Insert the records for type 2, if the id does not exist for type 1
INSERT INTO #Contacts
SELECT id,
NULL,
phone_num
FROM test1
WHERE NOT EXISTS (
SELECT 1
FROM #Contacts
WHERE #Contacts.id = test1.id
)
AND test1.type_code = '2'
-- if the id has both type 1 and 2, update the phone2 column with the data from type 2
UPDATE #Contacts
SET phone2 = test1.phone_num
FROM #contacts
JOIN test1 ON test1.id = #Contacts.id
WHERE type_code = '2'
SELECT id, phone1, phone2
FROM #Contacts
DROP TABLE #Contacts
I'm not even sure this is possible but I'm using MERGE in a process that has 3 source tables (the process steps through each source table sequentially) and I need to delete from the Target database occasionally.
My current code is
sqlMerge = "MERGE " + TableName + " AS target USING @CData AS source" +
" ON target.TotRsp = source.TotRsp AND target.ClientRef = source.ClientRef AND target.dbPatID = source.dbPatID" +
" WHEN MATCHED THEN" +
" UPDATE SET dbPatFirstName = source.dbPatFirstName, dbPatLastName = source.dbPatLastName,
[Code] ....
The Target db data is made up from several different clients and when the MERGE runs it uses TotRsp, ClientRef and dbPatID to uniquely match a source row to the target row and if no match it inserts the source row.
My problem is that when this runs with Source A first, it will delete all merged data from Source B & C. Then when Source B runs it will insert all Source B data but delete all from A & C and so on.
Is there way that that I can include additional clauses into NOT MATCHED BY SOURCE THEN so it knows only to delete when data has come from say Source A. Once the data is in the target table there is no reference to which source table it came from tho.
If there isn't a solution I suppose I could always add an extra column to the target db to indicate which source it came from and then have something like
NOT MATCHED BY SOURCE AND t.Source = 'SourceA'.
That's quite a bit of work my end to do that tho so I'd like to be sure it works.
SELECT
part.num, woitem.qtytarget AS woitemqty,
(SELECT LIST(wo.num, ',')
FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id
WHERE moitem.moid = mo.id) AS wonums, mo."USERID" AS mo_USERID
[Code] ...
Hi ppl,
I have installed SQL Server 2005 x64 Enterprise edition with Service Pack 2 on a Windows Server 2003 x64 Standard Edition with Service Pack 2.
Now I have to configure Merge Replication that will work with SQL Server Compact Edition database on Windows Mobile devices.
Distributor and the Publisher are the same server.
IIS 6.0 is installed on the windows server. I have installed the SQL Server Compact Edition Server tools on the server. However the compact edition server tools are only available for 32bit servers and I have also found out from the article http://support.microsoft.com/default.aspx/kb/912430 that you cannot replicate data from SQL Server 2005 to SQL Server Compact Edition by using the 64-bit version of IIS.
So if this is true does that mean I can not use merge replication on 64 bit server? Does that mean I have to get another 32 bit server with 32 bit IIS on it to make this work or is there another work around. Am i missing something here?
Regards
Nabeel