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


Top 5 Contributors of the Month
Sharon Maxwell
Post New Web Links

SQL SErver 2008 Merge Replication: Alter Trigger cause syntax error on subscriber site

Posted By:      Posted Date: November 02, 2010    Points: 0   Category :Sql Server
 

I have 2 clustered instances running on SQL Server 2008 SE-64 patch level 10.0.2531.0. These is one DB on these 2 instances (compatibility_level=80)under merge replication. now I need to change one trigger to add "NOT FOR REPLICATION". One publisher site all is ok but on subscriber site it causes Error 102 Severity 15 State 1 Incorrect Syntax near 'dbo'.

After tracing the error in profiler, I captured the incorrect syntax as below:

exec('ALTER TRIGGER [dbo].[trgBusinessEntityAllocationUpdate] on [dbo].[BusinessEntityAllocation] 
 [dbo].[BusinessEntityAllocation] 
FOR 
 UPDATE
 NOT FOR REPLICATION
AS
 
SET NOCOUNT ON
BEGIN
......
END
')

obviously, there is an duplicated part of object name. but the script was generated by replication engine. How could it happened? can anyone help?

Thanks

George 


George the DBA


View Complete Post


More Related Resource Links

SQL Server 2008 R2 Merge error in Replication agents checkup job

  

We have a customer with three publications, one distributor/publisher and on subscriber using merge replication.

looking at the history of the job replication agents checkup, every entry shows a message of:

Executed as user: NT AUTHORITY\SYSTEM. The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active. [SQLSTATE 01000] (Message 20554).  The step succeeded.

 

Throughout the day this error still appears as this job runs every 10 minutes but replication works fine. The issue is at some point throughout the night the replication stops with that same message. Sometimes it stops at 6:00:00am, sometimes at 6:40:00am, others at 6:50:00am. All we have to do to fix it is go into the replication monitor and stop synchronizing and then start synchronizing and replication will run all day until it stops next morning - again.

What is causing that error message? I have checked the other logs and there is nothing running at those times. in addition, no one has logged into the server that early (they don't start arriving until 7:15 or later).

What can i do to correct the issue so the message stops happening.

i

A MOF syntax error occurred - SQL Server ENT 2008 R2 on Windows Server 2008 R2

  
Having trouble getting SQL Server 2008 R2 installed on a Windows Server 2008 R2. Getting an MOF syntax error at this point durring the installation "SqlEngineConfigAction_install_confignonrc_Cpu64". Below is the section of the Details.txt file that includes the error. Any help on this would be greatly appreciated.   2010-07-28 10:51:35 Slp: Running: C:\Windows\system32\WBEM\mofcomp.exe "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\xesospkg.mof" 2010-07-28 10:51:35 Slp: Microsoft (R) MOF Compiler Version 6.1.7600.16385 2010-07-28 10:51:35 Slp: Copyright (c) Microsoft Corp. 1997-2006. All rights reserved. 2010-07-28 10:51:35 Slp: Parsing MOF file: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\xesospkg.mof 2010-07-28 10:51:35 Slp: MOF file has been successfully parsed 2010-07-28 10:51:35 Slp: Storing data in the repository... 2010-07-28 10:51:36 Slp: An error occurred while processing item 17 defined on lines 201 - 222 in file C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\xesospkg.mof: 2010-07-28 10:51:36 Slp: Compiler returned error 0x800706beError Number: 0x800706be, Facility: Win32 2010-07-28 10:51:36 Slp: Description: The remote procedure call failed. 2010-07-28 10:51:36 Slp: 2010-07-28 10:51:36 Slp: Sco: Compile operation for mof file C:\Program Files\Microsoft SQL S

SQL Server 2008 merge replication snapshot hangs on filtered articles

  

I have a publication on SQL Server 2008 Standard Edition using merge replication.  When I attempt to generate the initial snapshot, the snapshot agent appears to hang on the step "Setting up the publication for filtered articles."  I get a long (over 4 hour) series of messages: "The process is running and is waiting for a response from the server."  I know something is happening server-side, as SQL Server and the snapshot agent use a lot of memory and max out one core's processing capacity.

This has me confused as the publication is not doing any filtering.

Even more confusing:  I backed up the database and restored it onto my development-test system.  I created the snapshot there, and it took under 10 minutes every time.

Any suggestions for investigating and resolving this?


SQL Server 2008 Merge Replication article triggers not replicating

  

Currently running merge replication on SQL Server 2008 10.0.1600 and having issues with adding and modifying triggers on published articles.  I've searched through the MSDN site and googled it but have not found any definitive information on correct procecdures.

We have a replicated table that had 1 trigger on it when it was added to the publication.  That trigger was replicated.  We have since added a 2nd trigger to the table and it is not being replicated to our other server.  I've checked the Article Properties and Copy User Triggers is set to TRUE.  The only difference is the new trigger has NOT FOR REPLICATION in it, but I thought that would only keep it from being executed if a row is effected by the MERGE process.

We have rerun the snapshot and are syncing continuosly and the trigger does not replicate.  Do I have to drop the tablefrom the publication and then re-add it?  Am I missing something? 

Thanks.


Error opening site using SPSite and SPWeb instances using VS 2010, Win Server 2008 R2

  

Hi I am trying to open my site using SPSite and SPWeb instances and getting an error message as below-

The Web application at http://url could not be found. Verify that you have typed the URL correctly. If the URL should be serving existing content, the system administrator may need to add a new request URL mapping to the intended application.

  CODE is -

     SPSecurity.RunWithElevatedPrivileges(delegate()

                {

                    SPSite siteCOll = new SPSite(siteURL);

                    SPWeb oWeb = siteCOll.OpenWeb("url");

-----

----

I can access site using browser perfectly.

I am running VS as administrator.

I have done permission checking aswell.

 

please help!

Thanks

 

 

 


SQL Server 2008 Error creating trigger

  

I am working in sqlserver 2008 with SP1.  DB mail is setup and works with no issues.  When creating a trigger am getting the following error:

 Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid

Not sure where this error is comming from.  Thanks in advance for any help.  The code is below: 

CREATE Trigger TR_BLCargo ON BLCargo

AFTER

Merge Replication between SQL Server 2005 and SQL Server 2008

  

Hi,

We are planning to implement Merge Replication between SQL Server 2008 "Not R2 Version" (Publisher) and SQL Server 2005 (Subscriber). Will this setup work, or there are some limitations ?


replication error in SQL SERVER 2008

  

Hi,

we got the following error

The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\100\COM' directory. System returned errorcode 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21100)
Get help: http://help/MSSQL_REPL21100

by looking at http://support.microsoft.com/default.aspx/kb/956032, which suggest the issue is related to "Distribution Profile for OLEDB streaming" profile, but in our case, we use default profile for distribution agent.

after we reinitailized subscription, the issue was solved, but it came back after a while.

any input will be appreciated

our environment: windows server 2008 enterprise sp1 sql server 2008 64bit enterpise sp1


MERGE Replication between SQL Server 2005 to SQL Server 2008 R2

  

Hi, i have a publisher with 3 publications with below SQL

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

I want to add a subscriber to the server with below SQL

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)

Now when i am trying to add subscritpion to the 2008 R2 server on the publication server, i am getting below message

TITLE: New Subscription Wizard
------------------------------

For merge publications, the version of the Subscriber must not exceed the version of the Publisher.

------------------------------
BUTTONS:

OK
------------------------------

Is merge replication not possible between the 2?? Please advice. I beliwve Microsoft's new releases should support its older versions...



Merge Repication in sql server 2008 Error

  

Hi all,

i have implemented Merge replication in sql server 2008

after fre days i add new articles to the merge replication next day i found one error (see below)

Error messages:
The merge process was unable to access row metadata at the 'Subscriber'. When troubleshooting,
restart the synchronization with verbose history logging and specify an output file to write to,
or use SQL Profiler to determine the source of the failure.  (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200996)
Get help: http://help/MSSQL_REPL-2147200996
TCP Provider: An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 10054)
Get help: http://help/10054
Communication link failure (Source: MSSQLServer, Error number: 10054)
Get help: http://help/10054
The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199481)
Get help: http://help/MSSQL_REPL-2147199481

How to solve this isseus, please help me in this,

Thanks, Prasad.


SQL Server 2008 R2 Replication: The process could not connect to Subscriber

  

Hi,

We are facing a lot of problem with replication between SQL Server 2008 R2 servers. Let me describe the scenario..

We have 20 branches and one main Server in our organization, all are running SQL Server 2008 R2. The Main server is a Windows 2008 R2 x64 bit computer from which we want to create both way transaction replication (Data inserted in to any branch will be reflected to the main server and from there all other branches will get the same data) to all other branches.  Branch machines are 32 bit XP sp3 or Windows 7. All the machines are under workgroup. Among 20 branches we could successfully establish both way replication on 5 branches only. When we are trying to publish data from main server to say Branch A (with windows 7), it says "The process could not connect to Subscriber 'A'". We can ping to branch A and can login to Branch A from Main server through Management Studio which proves that there is no connectivity issue. Branch A can publish data to Main server without any problem. Even Branch A can subscribe data from another branch Branch B.

I cannot understand why this problem is happening for other 15 branches to subscribe data from Main Server. I have some queries in mind

  1. Is there any upper limit of subscription no?
  2. Is there any problem with windows 7?

Any help in

Error addning replication componet to existing SQL Server 2008 Instance

  

I have attempted to add the replication components to a named instance of SQL server 2008 running on a Windows 2008 server standard without Hyer-V SP2 and I get the following error entry in the Summary file:

Overall summary:
  Final result:                  SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
  Exit code (Decimal):           -2068643839
  Exit facility code:            1203
  Exit error code:               1
  Exit message:                  SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
  Start time:                    2011-05-16 20:59:26
  End time:             

SQL Server 2008 Merge Replication - Snapshot File Location Is Incorrect

  

I've searched far and wide, and so far have not been able to find any reference to this issue.

There is a merge replication setup on a SQL 2008 server, setup to replicate to SQL 2008 Express edition. We have similar setups in SQL 2005 AND SQL 2008 without issues. The setup is that the current merge replication snapshot file location is \\<ServerName>\Repldata\UNC\<SERVER_PUBLICATION>\20101229112828 which represents the file location of the latest snapshot which was just generated due to an obsolete snapshot.

The issue comes in when attempting to replicate against the server. The replication says that the schema script could not be propogated to the subscriber. Under normal circumstances I would say it's due to security issues with being able to read to the above folder, however this is not the case. The additional error that comes up is stating that it could not read the file along with an OS error 3 (The system cannot find the path specified).

What is unique about this, is that the subscriber is trying to download the snapshot from an old and now non-existent snapshot folder. Here is an copy of the error, noting the file location of "20091121130613" which no longer exists and is from an old snapshot.

The

Error upgrading SQL Server 2008

  

i was normally using VS2005 and SQL server 2005...

i upgraded to SQL Server 2008... and then...

i could not build my project anymore... because of this error:



Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
Exception Details: System.IO.FileNotFoundException: The specified module could not be found. (Exception from HRESULT: 0x8007007E)
Source Error: 

Server 2008 R2 -> Failed to decrypt using provider 'RsaProtectedConfigurationProvider'. Error mess

  

So I am using the aspnet_regiis.exe tool to encrypt sections of the web.config in my asp.net applications and services.  I have done this for years successfully on Windows Server 2003 (IIS 6).  However I just moved some apps over to Windows Server 2008 R2 (with IIS 7.5) and I am now receiving the following error at runtime:

"Server was unable to process request. ---> Failed to decrypt using provider 'RsaProtectedConfigurationProvider'. Error message from the provider: Bad Data."

Now I am quite familiar with this process and am stumped at what I have missed this time around.  I have done ALL of the following which typically suffices for the process on the server. PLEASE READ ALL AND DO NOT PROVIDE RESPONSES OR LINKS FOR THINGS I HAVE ALREADY DONE Thanks!!  Wink

  1. Imported the keys to the server using aspnet_regiis -pi  Result: Success
  2. Given FULL permission (using -full switch) using aspnet_regiis -pa to ALL of the following accounts  Result: Success
    1. [SSRS 2008 R2]Report Builder Access error-The remote server returned an error: (401) Unauthorized.

        
      Hello,        I am able to login Web Service URL and report manager using computer name and report builder is also working fine.  http://RPTSRV/reportserver   (RPTSRV is report server name. web service url working fine. all accessible) http://RPTSRV/reports  (report manager working fine and all accessible ,report builder too) It means I am able to access everything locally right? http://rs.test.abc.com/reportserver  (web service url working fine. all accessible)  http://rs.test.abc.com/reports    (Report manager working fine and all accessible accept report builder)(I carefully checked the link and its taking rs.test.abc.com/reportserver.....)     when I click on reportbuilder button in report manager it shows me error dialog box with following error log!! I tried from local and remote computer too but gives the same error both side. I am not sure but I am just thinking is this double hop kerberos security issue? Please help me to solve this problem. I am finding solution from last 4 days. Reporting Service configuratin tool settings Web Serivice URL -  all set to default Report Manager URL - all set to default Environment - Distributed deployment Server1(report_srv) --Windows server 2003, Installed sql server 2008R2 reporting service,

SQL Server Network Interfaces Error 26 - while failing over SQL Server 2008 R2

  
Before I explain the issue I am facing, I would like to say that I did read the post at  http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx but in my case the problem is transient (lasts for approximately two minutes after a failover) so items 1-3 in the list of troubleshooting steps do not seem to make sense. As to 4 and 5, the named instances use non-default ports (2433 and 3433 respectively), so I do not know how to tailor the PortQry command for that. I tried  portqry.exe -n ServerA,2433-p UDP -e 1434 and  portqry.exe -n ServerA,2433 -p UDP -e 2434 and it failed both times with a "Failed to resolve name to IP address" while the server was up and serving database requests The same thing happened when I tried it with the failover server as well. ***The issue I am facing*** I am using Enterprise Library 4.1 to connect to a SQL Server 2008 R2 database with a mirror. My connection string itself is as below (with dummy values for all fields): "Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=DBName;User ID=uid;Password=pwd;Connect Timeout=5" Sample .NET code is as below (with modifications to instantiate all variables locally to provide context): SqlDatabase db = new SqlDatabase(ConnectionString); SqlConnection cn = (SqlConnection)db
Categories: 
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