.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Merge Statement Error

Posted By:      Posted Date: September 03, 2010    Points: 0   Category :Sql Server
I'm getting the error: Attempting to set a non-NULL-able column's value to NULL. And I can't figure why the error is occurring. Code for replication in SSMS using temp tables: Yes, it's somewhat redundant, it's all dummy tables mimicing our actual setup, this is one of several similar merge statements in an SP, however only this one throws any errors. As for testing: the first pass works fine, but on a second run of just the section marked for testing it will throw the above error when it hits the update section, specifically the phoneNumberOrder seems to be causing it. Interestingly, if you comment out that block and try rerunning, it will throw the same error but it now appears to stem from the INSERT statement's phoneNumberOrder block. Commenting that out will allow the statement to run just fine. The two problem sections are indicated by comments. Any insight is greatly appreciated. -------------------------------------------------Run Once CREATE TABLE #User_PhoneNumbers(     [phoneNumberID] [bigint] IDENTITY(1,1) NOT NULL,     [userID] [bigint] NOT NULL,     [phoneNumberOrder] [int] NOT NULL,     [countryCode] [nvarchar](6) NOT NULL,     [areaCode] [nvarchar](3) NOT NULL,     [localCode] [nvarchar](3) NOT NULL,     [lineNumber] [nvarchar](4) NOT NULL,   

View Complete Post

More Related Resource Links

error in merge replication

Hi ALL,      I have merge replication configured between republishers( sql 2008) and subscribers( sql 2000)...i am getting the following error while trying to sync   The merge process could not update the list of subscriptions. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201010) Get help: http://help/MSSQL_REPL-2147201010 Cannot insert duplicate key row in object 'dbo.sysmergesubscriptions' with unique index 'uc1sysmergesubscriptions'. (Source: MSSQLServer, Error number: 2601) Get help: http://help/2601 The subscription could not be created. (Source: MSSQLServer, Error number: 14057)   Any suggestions y i am getting this error TIA

multiple executions of MERGE statement: Help with suitable TRANSACTION ISOLATION LEVEL

Folks, I am reasonably new to SQL Server. I am using SQL Server 2008 (no SP) on Windows XP. I am using the MERGE statement within a TSQL procedure to update a master/detail table pair (Master/Child), in which the MERGE inserts into the MASTER if a record based on the primary key doesn't exist and does, effectively nothing, if it does (well, it does an UPDATE set PK=PK so the record is passed on to the OUTPUT statement for insertion into the CHILD). Regardless as to the situation, the CHILD record has a record created when the MASTER exists or doesn't exist. Now, this code works fine with the standard TRANSACTION LEVELS. But I don't know what to do when I am running two instances of the same MERGE statement at the same time. One execution could create a record in the MASTER which the other process might try and create 5 minutes later. I really don't know what SET TRANSACTION ISOLATION LEVEL to use to allow both processes to run at the same time. I have looked at: ALTER DATABASE $(usedbname) SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE $(usedbname) SET ALLOW_SNAPSHOT_ISOLATION ON; But these don't seem to work with their associated TSQL calls. I know this is a complex issue, but as a new SQL Server user, I didn't know where else to go. regards Simon                              

Is this a bug in MERGE statement with DELETE/INSERT?

This looks to me like a bug with MERGE statement. This does not work on SQL 2008 and 2008 R2. Note that the first commneted statement works properly but the second does not. Is there any explanation why not, other than a bug? Duplication script below. use tempdb go /* IF object_id('dbo.Test', 'U') IS NOT NULL DROP TABLE dbo.Test go IF object_id('dbo.Src', 'U') IS NOT NULL DROP TABLE dbo.Src go */ IF object_id('dbo.Test', 'U') IS NULL BEGIN     CREATE TABLE dbo.Test     (         intID int NOT NULL IDENTITY PRIMARY KEY         ,sysID int NOT NULL         ,ioID int NOT NULL         ,Code nvarchar(10) NOT NULL     )     INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A')       CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test] ON [dbo].[Test]     (           [sysID] ASC,           [ioID] ASC     )     WHERE ([ioID] IN ((1)))     WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

Merge replication error

Hi ALL,  I have merge replication configured on sql server 2008. After two weeks snapshot agent ran again and when merge agent is running it throwing error you must rerun snapshot because current snapshot files are obsolete i dnt change anything on publication or snapshot folder location but still i am getting error   Any suggestion...TIA

statement with SQL_C_WCHAR returns 22001 string data, right truncation error

I am trying to execute the following code  value = L"Test13Digit++"; SQLPrepare(stmt, L"delete Entries where [type] = ?", SQL_NTS); SQLBindParameter(stmt, paramNumber, SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WVARCHAR, 100, 0, (SQLPOINTER) value.c_str(), 0, &strLen); SQLExecute(stmt); the table entries is as follows: Entries ([type] nvarchar(100), [key] nvarchar(100), [version] bigint, [data] varbinary(8000), CONSTRAINT PK_Entries PRIMARY KEY([type], [key]))   I keep getting the 22001 - string data, right truncation error. I have tried all different versions of WCHAR types but does not seem to help.

Merge statment error message




I'm trying the below query , but cannot work out how to overcome the error message. Any help please . Thanks Rob




Insert statement giving me syntax error


Good evening,


I have a MS Access table with the "ModelID" column set as AutoNumber and primary key. The ModelID is set as Field Size --> Long Integer, New Values --> Increment, Format --> General Number, Indexed --> Yes (No Duplicates).

Here is the code i am trying to execute, and is giving me an exception -> insert into syntax error:

string strInsert = "INSERT INTO tbl_Models (fName, lName, Image, Answer1, Answer2, Answer3, Answer4, Answer5, Votes, ImageHead, Age, Program, StudentID, PhoneNumber, EMail) VALUES (@fName, @lName, @Image, @Answer1, @Answer2, @Answer3, @Answer4, @Answer5, @Votes, @ImageHead, @Age, @Program, @StudentID, @PhoneNumber, @EMail)";
        OleDbCommand cmdInsert = new OleDbCommand(strInsert, myCon);

        cmdInsert.Parameters.AddWithValue("@fName", txtFirstName.Text.ToString());
        cmdInsert.Parameters.AddWithValue("@lName", txtLastName.Text.ToString());
        cmdInsert.Parameters.AddWithValue("@Image", FileUploadBody.FileName.ToString() + sRandomBody);
        cmdInsert.Parameters.AddWithValue("@Answer1", txtWhyBeAModel.Text.ToString());
        cmdInsert.Parameters.AddWithValue("@Answer2", "");
        cmdInsert.Parameters.AddWithValue("@Answer3", "

problem with merge statement query and parallelism plan


I have the query below which is using a parallel query plan, I am not quite sure why its doing so, I think its to do with the design of the query but apart from adding indexes and specifying maxdop 1, I was wondering if there is anything else I can do in order to rewrite the query to be more efficient and stop the use of the parallel plan.


Thanks in advance.

MERGE tblPersonProfile merge_target
USING #newrecs	sourc ON merge_target.PersonURN = sourc.PersonURN AND [AddressURN]=AddressURN AND MatchCode =[ProfileMatchCode] 
INSERT (PersonURN, AddressURN, ProfileMatchCode)VALUES (sourc.PersonURN, sourc.AddressURN, sourc.MatchCode)

How to view merge replication error (sql 2005)


We're using 2005 merge replication and we found that some records deletion cannot synchronize from publisher table to other subscriber table. (This table have very small size(<1000 records). We want to view those replication system tables or any logs for diagnostics this serious problem. Can you suggest any replication system table or logs that can indicate the reasons or hints of records deletion outsync?

Thanks and regards,

Scope statement using Tail function has count error


My time hierarchy (TimeHier) has a QUADWEEK level with 28 members. When I select the last 13 members with the Tail function, I get the appropriate members:

Tail([Time].[TimeHier].[QUADWEEK].Members, 13))

However, if I use that same function inside a Scope statement, it only applies to the last 12 members. I have to increase the count by 1, to 14 to make it work inside the scope. I'm baffled.

Here's the full code:




Merge Replication: Web Sync error 28011 with Basic Authentication on WinCE 6.0



I configured merge replication using SQL Server 2005 SP3 running on Windows Server 2008 R2 x64 with IIS 7.

My client is a .NET Compact 3.5 (RTM) application running on an embedded controller (Beckhoff CX1000) with Windows CE 6.0 installed. I am using SQL Compact 3.5 SP1 on the client. The preconfigured Win CE only had .NET Compact 2.0 installed, so I installed version 3.5 on the CompactFlash card attached to the controller.

Syncing over HTTP works perfecly using anonymous authentication.

I then executed the Web Sync Config Wizard and configured Basic Authentication without SSL. For authentication I use a local account even though my server is a member of our domain. The same account is used for access to the publication and distribution DBs (all located on the same server running IIS).

When I ran my client trying to sync, all I get is the 28011 error (Authentication failed).

The strange thing is that a test application on my desktop machine using .NET 3.5 (not Compact) can sync without problems. I even wrote a little test application for the embedded controller using the .NET HttpWebRequest object to connect to the server and I get the expected "Microsoft SQL Server Compact Server Agent" message.

I dug a little deeper into the issue and analyzed the HTTP packet getting sent to the server (using Wireshark). App

Error Message: String or binary data would be truncated. The statement has been terminated




I have the following problem.

When I edit a field in the SQL Server Management Studio Express then I get the following error:

Error Source: .Net SqlClient Data Provider
Error Message: String or binary data would be truncated.

The statement has been terminated


When I use the SQL update commando, it works fine.

Fore debugging we need to change the enabled field several times.

Is there a solution fore this?



The Table Layout

USE [Project]


/****** Object: Table [dbo].[Proces] Script Date: 06/02/2008 10:01:20 ******/

Merge Replication - Error - Query processor ran out of stack space


Hello everyone,

I have a merge publication configured that has been running fine with no issues for many weeks.  Here is my setup... Publisher - SQL 2008 SP1, Distributor - SQL 2008 R2, Subscriber - SQL 2008 SP1.

On Wednesday and Thursday of last week, I created a few hundred foreign keys referencing a table (user information for auditing).  Then, on Friday evening, I started to receive this message:



Msg 8621, Level 17, State 1, Procedure MSmerge_sel_sp_18DB84E1A521483601D2A6D477F6483D, Line 85
The query processor ran out of stack space during query optimization. Please simplify the query.



I have traced it down, this command gets executed:


exec MSmerge_sel_sp_18DB84E1A521483601D2A6D477F6483D @maxschemaguidforarticle = 'F990B1EE-D52F-4DCA-8EEF-C1783DA47A0F', @type = 6


Which executes this:



Need help with MERGE statement.





there a way to return the last CustId when INSERT but NOT in

Server Error Page: Some part of your SQL statement is nested too deeply.


Hello.  I've come across this error page with a Sharepoint Server 2007.  I've seen a few forums that say that Microsoft was going to fix this issue in its next major version, but I couldn't find out what version that was.  Is the 2007 version without a fix for this?  Is there a patch I could get, or is this part of a bigger issue?

I should mention, I've received this error page after simply editing html code in a web part.  There was no editing of sql statements.  Thanks in advance.

Merge Replication Error...



Please find the error while merge replication at Pull type from Subscriptions...

Using SQL server 2008 r2

Subscriptions type:Pull Merge type

Briefing: We have created new table at publisher and trying to sync at Subscribers using pull Mege subscriptions, it got sync perfectly for rest of the systems but in one system i was having issue,,,,


2010-10-28 21:50:26.329 [26%] [0 sec remaining] Preparing table 'FullSheet' for merge replication
2010-10-28 21:50:46.923 [66%] [10 sec remaining] Bulk copying data into table 'sysmergesubsetfilters'
2010-10-28 21:50:48.267 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 a
utput file to which to write.
2010-10-28 21:50:48.595 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 t
hich to write.
2010-10-28 21:50:

Merge error handling


Need help handling  errors while running a merge statement.

How do I make the merge behave as if it has  a try-catch block surrounding each row processed

( If an error is encountered, catch that error and log it (or any other processing)  then continue processing the next rows)?

What is the default behavior of the Merge statement, does it rollback the whole transaction when an error occurs or it continues processing the rows?

Can the  output clause be used to check for errors?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend