SQL Server 2012 :: Merge Statement Using SCD2 Failing?

Mar 18, 2015

I have created mergse statement using SCD2.where I am inserting the data if my BBxkey is not matching with target and updating the rows if the bbxkey is matching and rowchecksum is different.

Working of Store procedure

There are 2 scenario covered in this procedure on the basis of that ETL happening.

There are 2 columns deriving from source table at run time, one is BBxkey which is nothing but a combination of one or more column or part of column and another column is a Rowchecksum column which is nothing but a Hashvalue of all the column of the tables for a row.

Merge case 1:-WHEN NOT MATCH THEN INSERT

If source BBxkey is not there in Archive table that means if BBxKey is null then those records are new and it will directly inserted into Archive table.

Merge case 2:-WHEN MATCH THEN UPDATE

If Source.BBxkey=Target.BBxkey && Source.Rowchecksum<>Target.Rowchecksum then this means source records are available in Archive table but data has been changed, in this case it will update the old record with latestversion 0 and insert the new record with latestversion 1.

my sp failing when source having more than 1 same bbxkey.

error [Execute SQL Task] Error: Executing the query "EXEC dbo.ETL_STAGE_ARCHIVE ?" failed with the following error: "The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.".

Sample store procedure

DECLARE @Merge_Out TABLE (Action_Taken varchar(8),
TimeIn datetime,
BBXKey varchar(100),
RowChecksum nvarchar(4000),Col001 varchar(8000),Col002 varchar(8000),
Col003 varchar(8000),Col004 varchar(8000),Col005 varchar(8000),

[code].....

How Can I avoid such failure of my sp.

I want to handle those case where S.bbxkey=T.bbxkey && s.rowchecksum=t.rowchecksum

View 2 Replies


ADVERTISEMENT

SQL Server 2012 :: Insert Not Working On Merge Statement

Oct 7, 2015

In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.

Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type

[Code] ....

Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?

View 6 Replies View Related

SQL Server 2012 :: Convert Hardcoded SP Into Dynamic Merge Statement

Feb 13, 2015

I am working on to convert my static Store procedure to Dynamic.

I have created a Store procedure with Merge statement which is inserting new record and updating existing record.

This SP I will use in SSIS Insted of Data Flow Task I will run in Execute SQL Task.

Now my biggest problem is I dont know how to convert static code toi dynamic

Below is my Store procedure code.

As you can see my Source Query

I have a filemaster table as shown below which consist of Input filename,Source table ,Destination table and BBX expression.

Input_FilenameSourceTableName DestinationTableName BBxKeyDerExpr
CCTFB ImportBBxCctfb ArchiveBBxCctfb SUBSTRING(Col001,1,6)
CEMXR ImportBBxCemxr ArchiveBBxCemxr SUBSTRING(Col001,1,10)

In my source query I want to change the value of Source table ,Destination table and BBX expression dynamically on the basis of input file.

Purpose of making dynamic is that I have created separate sp for all the input, my clients want to have sungle dynamic sp which will execute on the basis of input file.this input file name I wil get fromm variable which i have created in SSIS Package.

Lets consider @File_name is the variable in package which store the file name

if file name is CCTFB then my query should take the Source table ,Destination table and BBX expression value from file master table.

Like that I have 100 of source query and evry query have diffrent number of columns. How can I change the column number in uodate and insert statement dynamically on run time.

CAST(SUBSTRING(Col001,1,6) + SUBSTRING(Col002,1,10) AS varchar(100)) :-It creates a key for comparing, this value i can take it from filemaster
HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002,Col003,Col004) AS varchar(max))) -here numberv of column need to be changed .
(SUBSTRING(SOURCE.Col001,1,6) + SUBSTRING(SOURCE.Col002,1,10)) this condition also i can take it from file master.

[Code] ....

I am able to get inserted and updated rowcount, but not able to get the matching records count.

View 0 Replies View Related

SQL Server 2012 :: MERGE Statement Attempted To UPDATE Or DELETE Same Row More Than Once

Mar 12, 2015

I have created a Dynamic Merge statement SCD2 Store procedure , which insert the records if no matches and if bbxkey matches from source table to destination table thne it updates old record as lateteverion 0 and insert new record with latest version 1.

I am getting below error when I ahve more than 1 bbxkey in my source table. How can I ignore this.

BBXkey is nothing but I am deriving by combining 2 columns.

Msg 8672, Level 16, State 1, Line 6

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

View 4 Replies View Related

SQL Server 2012 :: Using Merge Statement Output Records To Rebuild Table On Specific Date

Mar 5, 2015

I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.

Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.

View 2 Replies View Related

SQL 2012 :: SSIS Package With Merge Statement Locking DB

Dec 11, 2014

We have a SSIS package which loads the data from csv files to DB. It only loads the new entries ie if the row already exists in the tables than it doesn't insert it. For this we load the CSV to temp tables for respective schemas and than those are compared with base tables of respective schemas and inserted new rows. For this we use Merge statement.

View 1 Replies View Related

SQL Server Mobile 2005 Merge Replication Failing Over SSL, But Can Browse Through Pocket IE.

Jan 28, 2008

Hi All, this system is scheduled to go live in near to a day... but i have hit an issue when switching on SSL. Any help would be greatly appreciated.

Device Env:
Windows Mobile 5.0 + Messaging and Security Feature Pack
SQL Server Mobile 2005.

Merge replication works fine without SSL. After running the Configure Web Sync wizzard to setup SSL merge replication no longer works. Note i checked using pocket IE by browsing to https://mycompany.com/pub/sqlcesa30.dll?diag and all worked fine. There was no prompt saying the cert was bad etc.

The certificate is a:
Equifax secure global ebusiness CA-1 cert.
Signature algorithm md5RSA
public key RSA(1024 bits)
thumbprint algorithm sha1

Stacktrace given is:
- ex {"A request to send data to the computer running IIS has failed. For more information, see HRESULT."} System.Data.SqlServerCe.SqlCeException
- Errors {System.Data.SqlServerCe.SqlCeErrorCollection} System.Data.SqlServerCe.SqlCeErrorCollection
Count 1 Integer
Item In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. System.Data.SqlServerCe.SqlCeError
HResult -2147012858 Integer
InnerException Nothing System.Exception
Message "A request to send data to the computer running IIS has failed. For more information, see HRESULT." String
NativeError 28037 Integer
Source "Microsoft SQL Server 2005 Mobile Edition" String
StackTrace "at System.Data.SqlServerCe.NativeMethods.CheckHRESULT()
at System.Data.SqlServerCe.SqlCeReplication.Synchronize()
at MyApp.Helper.Replication.SynchronizeInit()
at MyApp.CallSynchronize()
at System.Windows.Forms.Control.OnGotFocus()
at System.Windows.Forms.Control.WnProc()
at System.Windows.Forms.ContainerControl.WnProc()
at System.Windows.Forms.Form.WnProc()
at System.Windows.Forms.Control._InternalWnProc()
at Microsoft.AGL.Forms.EVL.EnterMainLoop()
at System.Windows.Forms.Application.Run()
at MyApp.Program.Main()

View 2 Replies View Related

SQL SERVER 2005 REPLICATION (MERGE AGENT FAILING : PUSH SUBSCRIPTION)

Mar 27, 2006

ERROR MESSAGE:

"Message: Incorrect syntax near 'NoteID'."

Here is a snipet from the output file:

2006-03-27 13:01:49.820 Skipping file 'NotesHistory_21.sch' because it has already been delivered for a previous article or by a previously interrupted snapshot.
2006-03-27 13:01:49.867 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET ANSI_NULLS ON

2006-03-27 13:01:49.898 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:49.945 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET QUOTED_IDENTIFIER ON

2006-03-27 13:01:50.008 [17%] OLE DB Subscriber 'LUZERNV4-PDOSQL4': CREATE TABLE [dbo].[MSmerge_conflict_EFILE_MERGE_NotesHistory](
[NoteID] [int] NOT NULL,
[CaseRefNum] [int] NOT NULL,
[KeyField] [varchar](500) NOT NULL,
[KeyValue] [varchar](500) NOT NULL,
[Note] [varchar](800) NULL,
[NoteField] [varchar](100) NULL,
[ScanDocID] [int] NULL,
[PDF_ID] [int] NULL,
[RescanCode] [int] NULL,
[RescanNote] [varchar](100) NULL,
[AuditID] [bigint] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL
)


ꜽśã¹? êœ?Å›B 27 13:01î–”Å›è›ªç —êœ?Å›é”?Ä€Percent Complete: 17
2006-03-27 13:01:50.086 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET ANSI_NULLS ON


2006-03-27 13:01:50.117 Preparing table 'NotesHistory' for merge replication
2006-03-27 13:01:50.164 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET QUOTED_IDENTIFIER ON


2006-03-27 13:01:50.195 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.258 OLE DB Subscriber 'LUZERNV4-PDOSQL4': if object_id('MSmerge_conflicts_info') is not NULL
alter table [MSmerge_conflict_EFILE_MERGE_NotesHistory] add origin_datasource_id uniqueidentifier NULL

2006-03-27 13:01:50.351 OLE DB Subscriber 'LUZERNV4-PDOSQL4': if object_id('MSmerge_conflicts_info') is not NULL
create nonclustered index [ncMSmerge_conflict_EFILE_MERGE_NotesHistory] on [MSmerge_conflict_EFILE_MERGE_NotesHistory] ([rowguid], origin_datasource_id)

2006-03-27 13:01:50.383 OLE DB Subscriber 'LUZERNV4-PDOSQL4': exec sp_MSsetconflicttable @article = N'NotesHistory', @conflict_table = N'MSmerge_conflict_EFILE_MERGE_NotesHistory'

2006-03-27 13:01:50.430 OLE DB Subscriber 'LUZERNV4-PDOSQL4': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.476 [17%] The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.Percent Complete: 17
2006-03-27 13:01:50.50é”?Ä€
2006-03-27 13:01:50.539 Applied script 'NotesHistory_21.cft'
2006-03-27 13:01:50.586 OLE DB Subscriber 'LUZERNV4-PDOSQL4': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.617 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.711 Percent Complete: 17
2006-03-27 13:01:50.742 {call sp_MSsetconflicttable (N'NotesHistory', N'MSmerge_conflict_EFILE_MERGE_NotesHistory', N'LARGOS00-SDI', N'EFILE', N'EFILE_MERGE')}
2006-03-27 13:01:50.773 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.867 Percent Complete: 0
2006-03-27 13:01:50.898 Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_MSsetconflicttable (N'NotesHistory', N'MSmerge_conflict_EFILE_MERGE_NotesHistory', N'LARGOS00-SDI', N'EFILE', N'EFILE_MERGE')}
2006-03-27 13:01:50.961 Percent Complete: 0
2006-03-27 13:01:50.992 Category:SQLSERVER
Source: LUZERNV4-PDOSQL4
Number: 102
Message: Incorrect syntax near 'NoteID'.
2006-03-27 13:01:51.039 Percent Complete: 0
2006-03-27 13:01:51.070 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
2006-03-27 13:01:51.133 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4'
2006-03-27 13:01:51.180 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4'
2006-03-27 13:01:51.226 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4'
2006-03-27 13:01:51.258 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4'
2006-03-27 13:01:51.305 Disconnecting from OLE DB Publisher 'LARGOS00-SDI'
2006-03-27 13:01:51.336 Disconnecting from OLE DB Publisher 'LARGOS00-SDI'
2006-03-27 13:01:51.383 Disconnecting from OLE DB Publisher 'LARGOS00-SDI'
2006-03-27 13:01:51.414 Disconnecting from OLE DB Publisher 'LARGOS00-SDI'
2006-03-27 13:01:51.445 Disconnecting from OLE DB Distributor 'LARGOS00-SDI'
2006-03-27 13:01:51.492 Disconnecting from OLE DB Distributor 'LARGOS00-SDI'
2006-03-27 13:01:51.523 The merge process will restart after waiting 60 second(s)...

View 1 Replies View Related

Merge Replication Failing With Named Pipes Provider, Error: 40 - Could Not Open A Connection To SQL Server

Sep 28, 2007



Hi,

I have merge replication configured over VPN and the replication is failing to connect to the subscriber through Named pipes with the error Named Pipes Provider, error: 40 - Could not open a connection to SQL Server ......

Actually , I am having issues with Named pipes connection from publisher to subcriber. I have enabled the protocols and also enabled the remote connections using both tcp/ip and named pipes. I have created the client network alias using named pipes

Also, due to security reasons only few ports have been released on the subscriber. Please provide me some information on the following.
1) Does any port specifically be released for listening on named pipes? i am able to connect using tcpip with out any issues as 1433 has been released.
2) How to configure the replciation to use tcpip and not named pipes (guess by default it will use named pipes)?




View 1 Replies View Related

SQL Server 2012 :: UPDATE Trigger Failing

Jan 30, 2015

We have an UPDATE trigger that is failing. This seems like a basic task - we want to write a record to a separate tracking table when our main transaction table is updated for any reason. Our assumption is that we have a reference to the data from the "inserted" record that was just updated. The scenario here is that we are running a batch process which READS several thousand records in our transaction table each evening.

We then mark each individual record as processed on the transaction table and expect that the UPDATE trigger will successfully fire (it is not). The version of our trigger listed below shows our attempt to deal with the fact that TransactionID does NOT exist from "inserted." We also have a version of this trigger that deals with INSERTS - it works flawlessly.

ON [dbo].[FPS_Transaction]
AFTER UPDATE
AS declare @trxId uniqueidentifier;
BEGIN TRY
SET NOCOUNT ON

[code]...

View 6 Replies View Related

Merge Client Failing

Aug 31, 2006

Hi

We have sql2005 merge replication happening - it is using replisapi.dll over the net.

One of the clients has been working fine, until yesterday afternoon - we are running sql2005 sp1 at subscriber and distributer

The message is as follows....


Please help !

Error messages:


The process could not read the request message due to OS error 10054. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147014842)
Get help: http://help/MSSQL_REPL-2147014842

The format of a message during Web synchronization was invalid. Ensure that replication components are properly configured at the Web server. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199374)
Get help: http://help/MSSQL_REPL-2147199374

The subscription to publication 'yarraman main' could not be verified. Ensure that all Merge Agent command line parameters are specified correctly and that the subscription is correctly configured. If the Publisher no longer has information about this subscription, drop and recreate the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201019)
Get help: http://help/MSSQL_REPL-2147201019

View 1 Replies View Related

SQL Server 2012 :: Import XML Returns 0 Rows Affected Failing

Nov 29, 2013

I am trying to do a bulk import of data from XML into sQL.

My query returns no errors but no data gets imported.

Here is my XML

?xml version="1.0" encoding="utf-8"?>
<status>
<connection_status>successful</connection_status>
<operation_status>successful</operation_status>
<CustomerDeposits>

[code]....

View 2 Replies View Related

SQL 2012 :: Log-shipping Secondary Server Is Out Of Sync And LSRestore Job Failing

Jun 3, 2015

I checked the server and found that LS restore job failing and Backup and copy jobs running fine without any issue. and also observed that Copy folder the trn file existing on secondary server. i try to restore trn file im getting the error. and observed that last log backup file that it restored at the secondary database on May2nd,2015.

2015-06-02 12:25:00.72*** Error: The log in this backup set begins at LSN 761571000000022500001, which is too recent to apply to the database. An earlier log backup that includes LSN 721381000002384200001 can be restored.

From Restore job histort details below.

Message
2015-06-02 12:25:00.72*** Error: The file 'xxxx\_20150530104503.trn' is too recent to apply to the secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) ***
2015-06-02 12:25:00.72*** Error: The log in this backup set begins at LSN 761571000000022500001, which is too recent to apply to the database. An earlier log backup that includes LSN 721381000002384200001 can be restored.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2015-06-02 12:25:00.73Searching for an older log backup file. Secondary Database: 'database'
2015-06-02 12:25:00.73*** Error: Could not find a log backup file that could be applied to secondary database 'database'.(Microsoft.SqlServer.Management.LogShipping) ***
2015-06-02 12:25:00.74Deleting old log backup files. Primary Database: 'database'

View 3 Replies View Related

Integrity Job Failing On Merge Replication Database

Jan 2, 2002

Hi everbody,

Integare check job failing on replication(merge) database.Can you tell me how to schedule the this job.
Thanks
Mark

View 1 Replies View Related

Merge Replication Failing After Recreating The Publication

Jun 7, 2006

I am having a problem with merge replication after recreating a publication. It is a simple two-way replication between two servers allowing applications to update data at both ends, i.e. 1 publisher, 1 subscriber for all tables except some junk ones. The problem started after I did the following:

1) I dropped and recreated the publication to allow for some changes to the database schema. These caused problems so I dropped the subscription and publication and restored the databases at each end from backups taken before we started.

2) When I created the publication again, it wouldn't let me use the same name as it thought the publication already existed. It let me use a different name instead.

3) Most things work OK but one of the tables is not replicating inserts from the subscriber to the publisher. There are no errors and no conflicts, other similar tables replicate OK. Inserts go across the other way OK.

4) I am getting replication conflicts on another table that says 'Unable to synchronize the row because the row was updated by a different process outside of replication'

I believe the problem is to do with the original publication details still being in the restored databases, so am looking to drop the publication & subscription, remove the remnants of the old publication, fix the data and recreate pub & sub. What do I need to do to get rid of the old replication data in the database?

Any help much appreciated.



View 4 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

SQL Server 2012 :: Get Next ID With A MERGE?

Jan 7, 2014

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.

View 2 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

SQL 2012 :: Server Merge Replication

Oct 20, 2014

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"

View 7 Replies View Related

SQL Server 2012 :: Merge Data From Two Tables

Feb 3, 2014

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

View 2 Replies View Related

SQL Server 2012 :: Merge Multiple Rows Into One?

Nov 26, 2014

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

View 3 Replies View Related

SQL Server 2012 :: How To Merge Multiple Rows Into One Row

Dec 24, 2014

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

View 2 Replies View Related

SQL 2012 :: Complex Filter For Server Merge Replication

Jul 26, 2015

We have a table in an SQL Server 2012 database that stores tree-like structures. Simplified for the purpose of my question, it has the following format:

Id int identity,
ParentId int,
GroupId int

Each record of the table represents an object identified by Id. An object may or may not have a parent in the same table, such that object.ParentId = parentObject.Id. A root object has ParentId = NULL. There are multiple root objects, so the table in fact stores multiple trees. What’s important is that the tree depth is not fixed, i.e. theoretically there can be any number of ancestor generations for an object. GroupId is a property of a root object; in theory none of the children of a root object has to have GroupId <> NULL; it can be assumed that any child has the same GroupId value as its root object.

A sample table having two roots (one grandparent and one parent), one non-root parent/child and 4 child roots:

Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 NULL non-root parent/child
3 2 NULL child
4 2 NULL child
5 NULL 300 root parent
6 5 NULL child
7 5 NULL child

The table is not normalised, i.e. there’s no separate {root_object : group} table. However I don’t think normalising the table would solve the problem.

Now the problem. We need to set up merge replication from the table above (Master table) to the table of the same format in another DB. We need to replicate only those rows of the Master table that have a certain fixed GroupId value, e.g. 200 in the example above. If we ensure that GroupId in all descendant objects of a root object has the same value in the table as the root object itself that would be trivial. The table would look like this:

Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 200 non-root parent/child
3 2 200 child
4 2 200 child
5 NULL 300 root parent
6 5 300 child
7 5 300 child

And the filter would look like this:

WHERE GroupId = 200

However out of performance considerations, we would like to avoid if possible filling GroupId for the descendant objects, because as it must be clear from the above, GroupId for a descendant object is quite easily deducible via a stored procedure or UDF (just need to go up the tree until ParentId = NULL). The problem is, I don’t know how to achieve this in a merge replication filter: it would only allow WHERE conditions and joins. I’ve have not had much luck with joins for merge replication in general, but here we have more complex algorithm, because the number of tree levels can be different for every object. And merge replication would not allow using UDF…

View 2 Replies View Related

SQL Server 2012 :: Merge Column Values Into Rows?

Aug 12, 2015

I need to merge column values (#Status.Status) based on OrderID onto #Orders.NewStausCombined field separated by commas .

CREATE TABLE #Status
(
ID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
Status VARCHAR(20)
)
INSERT INTO #Status ( OrderID, Status )

[code].....

View 3 Replies View Related

DB Engine :: Can't Use The MERGE Statement / How To Design WHERE Condition For Insert Statement

Nov 5, 2015

I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.

View 2 Replies View Related

SQL Server 2008 :: MERGE Statement - Cannot Filter Set Of Rows In Target Table

Feb 10, 2015

I have a table:

declare tableName table
(
uniqueid int identity(1,1),
id int,
starttime datetime2(0),
endtime datetime2(0),
parameter int
)

A stored procedure has new set of values for a given id. Sometimes the startime and endtime are the same, in which case I update the value of parameter. Sometimes I add a new time range (insert statement), and sometimes I delete a time range (delete statement).

I had a question on merge, with insert, delete and update and I got that resolved. However I have a different question regarding performance of the merge statement.

If my target table has hundreds of millions of records and I want to delete/update/insert a handful of records, will SQL server scan the entire target table? I can't have:

merge ( select * from tableName where id = 10 ) as target
using ...

and I can't have:

merge tableName as target
using [my query] as source on
source.id = target.id and
source.starttime = target.startime and
source.endtime = target.endtime
where target.id = 10
...

This means I cannot filter the set of rows in the target table to a handful of records where id = 10.

View 1 Replies View Related

SQL Server 2008 :: Merge Statement When Source Table Supplies Duplicates

Apr 26, 2015

With merge/insert statements ...Is DISTINCT best way to handle problem of source table containing duplicate rows, along with WHERE NOT IN statement? the source dataset is large and having to do DISTINCT and further filtering is taxing on the ETL.

DDL
source table
CREATE TABLE [dbo].[source](
[Product_ID] [INT] NOT NULL,
[ProductCode] [VARCHAR](20) NULL,
[ProductName] [VARCHAR](100) NULL,
[ProductColor] [VARCHAR](20) NULL,

[code]....

View 0 Replies View Related

SQL Server 2012 :: Merge Multiple Rows From Single Table

Nov 17, 2014

I have resulting rows from a query similar to the following:

The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:

SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,

[Code] ....

I basically want to merge the like Employee ID's together into a single row like the following:

I know I have done this before and it is probably pretty simple.

View 4 Replies View Related

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

Jul 5, 2015

This index is not unique

ix_report_history_creative_id

Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.

The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161

The statement has been terminated.

Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).

The statement has been terminated.

View 6 Replies View Related

SQL Server 2008 :: Merge Statement Takes Several Times Longer To Execute Than Equivalent Update

Jun 20, 2013

Problem Summary: Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?

I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look very similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.

Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.

Server Configuration:

SQL Server 2008 R2 SP1, Enterprise Edition
3 x Quad-Core Xeon Processor
Max Degree of Parallelism = 8
148 GB RAM

SQL Code:

Target Table:
USE TPS;
IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL
DROP TABLE dbo.ParticipantResponse;

[code]....

View 9 Replies View Related

SQL Server 2012 :: How To Merge Two Tables Data On Behalf Of ID In Comma Separated Manner

Jul 2, 2015

i want to combine upper two tables data like below result sets. Means they should be grouped by bsns_id and its description should be comma separated taken from 2nd table. In sql server 2012.

This is the image path :

[URL]

View 3 Replies View Related

SQL 2012 :: Log Shipping Failing All Of A Sudden

Nov 21, 2013

I have several databases on a 2012 instance that are mirrored to a second server, and log ship to a third server for reporting purposes.Recently, for two of the databases, the log shipping has been failing at least once per day, and sometimes more often.

i have deleted and recreated both the mirroring and log shipped databases on several occasions, but the problem is still happening.The log shipping restore jobs don't get marked as failed in the job history, but if you expand the history you can see errors such as this:

Date21/11/2013 13:00:01
LogJob History (LSRestore_OAK-DCSQLDB1_OtherCarrierLive)
Step ID1
ServerCHERRY
Job NameLSRestore_OAK-DCSQLDB1_OtherCarrierLive
Step NameLog shipping restore log job step.
Duration00:00:30
Sql Severity0

[code]....

Restoring a new backup of the database cures the problem for anywhere between 15minutes and 12 hours, but it always seems to re-occur.I have run DBCC on the source databases with no errors reported, and five other databases have the log shipping working without errors.

View 9 Replies View Related

Transact SQL :: UPDATE Statement Failing On Unique Key Constraint

Jul 9, 2015

I'm trying to update rows in a simple table that has a UNIQUE KEY CONSTRAINT defined on one of its columns. Here is the DDL for the table:

CREATE TABLE [dbo].[SEC_USER](
 [SEC_USER_ID] [int] IDENTITY(1,1) NOT NULL,
 [USER_CODE] [varchar](100) NOT NULL,
 [USER_NAME] [varchar](128) NOT NULL,
 [EMP_CODE] [varchar](6) NOT NULL,

[Code] ....

When trying to execute the UPDATE statement the query fails with a constraint violation error:

Violation of UNIQUE KEY constraint 'UQ__SEC_USER__A039F1EE62FE8444'. Cannot insert duplicate key in object 'dbo.SEC_USER'. The duplicate key value is (34337).

What has  me baffled is that I'm not doing any insert. Also, the value that it's referencing - 34337 - doesn't exist in the table at all. I'd rather not drop the constraint.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved