We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?
View 4 Replieswhen I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
Thanks,
Larry
I get this error in my Agent after starten to synchronise.
I see the access mdb been created and then deleted and renamed to
name_old.mdb
Do you have some help?
Richard
Is there a way to force the snapshot to use a format file when bcp'ng the data to the subscriber?
The publisher and subscriber schemas are slightly different. Mainly added columns to the subscriber.
We have a post snapshot script that updates these additional columns on the subscriber.
I'd like to force the snapshot to use a format file, so it doesn't error when initially loading the data to the subscriber.
Is this possible?
I've been struggling to figure out how to set the "Log on as" login for a SQL Server agent that isn't a member of the Administrators group.
Is this even possible? No matter what policy settings I assign the the user, I get an access denied error when I try to start the SQL Server Agent service. If that user is part of the administrators group, there are no errors.
I have no problems with other services and "log on as" logins...
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)...
Had some big problems with my SQL Server 2008 R2 SP1 during the last maintenance running on Windows Server 2008 R2 Enterprise ( upgraded from Standard). I'm getting the following error,
"SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online."
I did a google search and found others with the problem but the resolution isn't working for me. I ran the following commands, heck I ran them multiple times. No errors come back running these commands. But the same error keeps happening, I even restart the service and then the server again.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
The msdb got too big for the disk it was on. I had maintenance plans to clear out the all of histories but I didn't think of the mail items. When I finally saw this problem I tried clearing them out using the stored procedures only with it finally failing because the transaction log got full. Since we didn't have a lot of jobs scheduled I decided to just recreate the msdb. So I scheduled a maintenance window, download SQL Server Service Pack 3.
my steps are as follows:
->diff backup of msdb
->stopped the service and started it again with: NET START MSSQLSERVER /T3608
->detached msdb with: SQLCMD -E -SP-SRVR-SQL-01 -dmaster -Q"EXEC sp_detach_db msdb"
->moved it off the drive
->restarted the service without any startup flags
->recreated msdb with: SQLCMD -E -SP-SRVR-SQL-01 -i"D:Microsoft SQL ->ServerMSSQL10_50.MSSQLSERVERMSSQLInstallinstmsdb.sql" -o"D:Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLInstallinstmsdb62715.out"
I looked at the log file and there were no actual error messages so I applied service pack 3 and restarted server after it installed, was actually prompted to restart. In my tests I was never prompted to restart the server.Now I never started the agent service after recreating the msdb database since I knew it wasn't on the same version. I just went directly to the latest SP. Our applications are running fine but now I cant do scheduled backups.
I'm trying to create a job that will copy a backup file from a mapped network drive to a local folder however it keeps failing due to a privilege.
The command is:
copy 10.10.10.5f$ est.nightly H:MSSQLBackup est.nightly
I can run the command successfully from the command prompt, however using the same command in a SQL Server job it fails with "Access is denied".
How do I configure the mapped network drive to allow the Sql Server Agent to get access to it?
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
View 3 Replies View RelatedHello,I'm getting the following error message when I try add a row using aStored Procedure."The identity range managed by replication is full and must be updatedby a replication agent".I read up on the subject and have tried the following solutionsaccording to MSDN without any luck.(http://support.Microsoft.com/kb/304706 )sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/library/aa239401(SQL.80).aspx ) has no effectFor Testing:I've reloaded everything from scratch, created the pulications from byrunning the sql scripts generated,created replication snapshots andstarted the agents.I've checked the current Identity values in the Agent Table:DBCC CHECKIDENT ('Agent', NORESEED)Checking identity information: current identity value '18606', currentcolumn value '18606'.I check the Table to make sure there will be no conflicts with theprimary key:SELECT AgentID FROM Agent ORDER BY AgentID DESC18603 is the largest AgentID in the table.Using the Table Article Properties in the Publications PropertiesDialog, I can see values of:Range Size at Publisher: 100,000Range Size at Subscribers: 100New range @ percentage: 80In my mind this means that the Publisher will assign a new range whenthe Current Indentity value goes over 80,000?The Identity range for this table cannot be exhausted! I'm not surewhat to try next.Please! any insight will be of great help!Regards,Bm
View 1 Replies View RelatedHi All,
I would appreciate any help here at all. I am pulling my hair out!
I am unable to start the snapshot agent or log reader agent from within SQL management studio. it fails on executing step 2 with unknown username or bad password.
I have checked all account details and they are correctly using my domain admin account. I can connect to SQL using teh same account and it also has sysadmin permissions.
If i copy the step 2 paramters and start from the cmd prompt (again logged in using the same domain account) they both start fine.
Any ideas would be gratefully received.
I am testing peer to peer replication in our environment. I simulated a three node peer to peer topology and a local distributor.
For some wierd reason I cannot get the Log Reader Agent and snapshot agent to start. The domain account under which SQL Server Agent runs has administrator previlage on the box. I also use a domain account for SQL Server Service. (none of the passwords changed).
This is the error I am getting - "Executed as user: abc. A required privilege is not held by the client. The step failed"
Any ideas???
Also this domain account is a member of
SQLServer2005MSSQLUsers$ServerName$MSSQLSERVER
SQLServer2005MSAgentUser$ServerName$MSSQLSERVER
Hi all,
View 1 Replies View RelatedI seem to be having trouble with the distribution agent not doing what I want it to do. I have set up a few publications to run all the time but when I want to schedule a publication to run once a day this doesn't happen it defaults to run all the time.
Can the distrubition agent run some publications continusly and some publications scheduled?
Can anyone shed some light on this problem.
Please, i need some help with the replication i had configured.
I have two publications to the same subscriber, one with data filters and other not.
The one without data filters i have test manualy. But now after i have created the other one, the log reader says that '145 transactions with 347 commands' were delivered, but the distribution agents says that 'no replicated transactions are avaiable'.
I know that have many things to be replicated, but doesn´t work.
The Sql server agent is working on the distributor.
There is something that can be causing this error ?????
Please, if someone can help me i apologize
Thanks in regards
Sandra Lino
Hello,
What happens to these REPLication agents if SQL Agent is stopped and started:
Snapshot Agents
Merge Agents
Misc Maintenance Agents
Can the agent be stopped and started with no thought to the status of replication, or should the replication state be modified in some way before any change to the status of the Agent?
(I know REPL depends on agent, so wuestion is, can REPL simply resume or not when agent is re-started.)
Thanks.
MichaelGG
Hi Guys,
Ive accidently gone and deleted a snapshot agent off a working publication (tables distribution.dbo.mssnapshot_agents, msdb.dbo.sysjobs etc).
Is there a workaround to create a new snapshot agent for the publication without having to break or recreate the publication?
I have tried adding a snapshot and only to fail, can anyone help? thanks a million
---*** Tried adding a snapshot agent ***---
use publishing_database
go
-- Add snapshot job
exec sp_addpublication_snapshot @publication = N'publication_name',
@frequency_type = 4,
@frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 8,
@frequency_subday_interval = 1,
@active_start_date = 20040101,
@active_end_date = 20040101,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959
GO
-- RESULT:
Server: Msg 14101, Level 11, State 1, Procedure sp_MSaddpub_snapshot, Line 73
The publication 'publication_name' already has a Snapshot Agent defined.
During testing, the log for tempdb filled up.
That caused the replication agents to get an error and stop.
Is there an easy way to have the agents wait a minute or 2 then try to restart?
Thanks
John
Hi all,
after creating the replication job (works) other agent jobs hang up. after restart the agent, the jobs work one time. t
can it be a problem with dieffernt agent profiles/accounts
many thanks
oliver
Unable to start log reader agent after being manually stopped.
Any ideas.
I have setup a replication and after 24 hours this are the error messages I have got:Last action; The database 'supersite' on server 'solomon' does not exist. Full error message: Could not locate entry in sysdatabases for database 'superiste'. No entry found with that name.Make sure that the name is entered correctly. Can anyone out there help me out?
View 1 Replies View RelatedI seem to have an orphaned Distribution Agent. There is no associated Publication and the agent is sending errors. The errors would be legitimate if only I had an associated publication. I also do not have an associated Snapshot Agent.
How can I get rid of this bogus agent? I already deleted the associated job and rebooted SQL and SQLAgent but it still persists.
Any help would be appreciated.
Colleen
What does this error message mean ?
The process could not bulk copy out of table '[dbo].[syncobj_0x3732313945323046]'.
I ran transaction replication successfully last night. Then it was scheduled to run again in the morning, but failed .... any ideas ?
SQL2K SP2
I have been seeing this issue for several weeks now. The distribution agent will become idle even though there is activity occurring within the replicated database. If I manually start the distribution agent, it will begin retrieving the transactions. I thought that this might be due to inactivity but I have modified the inactivity threshold and I am not receiving an actual inactive agent warning. There are no warnings -- it simply sits there and does not resume sending transactions.
Any thoughts? This is a transactional replication with a distribution agent that is run at the subscriber.
Thanks,
MV
Hi
We are repeatedly having the trouble of our publisher database becoming unresponsive during network problems.
We have publisher in carribean, distribution agent in canada and subscribers all over. The problem comes when publisher cant deliver to distributor due to network outages.
We are planning on moving the distributor to the carribean ultimately but that will take a while, and our application repeatedly needs to be restarted due to these freezes.
Does anyone know of this problem & possible ways to fix it in the short term (short of changing our replication topology).
is this a bug in replication?
ps.database is merge & transactional pubs. (some tables merge/some trans)
help much apprec,
des
Hi,For some magical reason, my database replication has suddenly stoppedworking. I modified the publication but the Distribution agent willnot run; the snapshot does not get applied to the subscriber, andchanges made at the publisher do not get replicated.My configuration is: Transactional Replication with a 'Pull'Subscription. The Subscriber is Updateable with Immediate UpdatesOnly. All created via the SQL Server Enterprise Manager. Both thePublisher and Subscriber are on the same server. I'm running SQLServer 2000 (8.00.760 SP3), on Windows 2000 Server (5.0 (2195)).I manually run the snapshop agent. It runs fine with the status'Succeeded' and the Last Action is 'A Snapshot of X article(s) wasgenerated'.When I look at the distribution agent, things start to get a bitconfusing in ( <SERVERNAME>/Replication Monitor/ Publishers/<SERVERNAME?>/ Publication/ Subscription), the status staysconstantly at 'Never Started'. All other columns are either blank or0. When I right click over the subscription, both Start Synchronisingand Stop Synchronising are NOT greyed out. If I click on either StartSynchronising or Stop Synchronising (absolutely) nothing happens(except the mouse turns 'busy' for a fraction of a second). There areno error messages.I added -output c:sqlout.txt -outputverboselevel 3 to thedistribution agent command. C:sqlout.txt did not get created.It worked fine up to a couple of days ago. I'm not sure exactly whatcaused the problem, as I made a few changes at the same time. Thechanges included: adding a new transactional publication, adding morearticles to an existing transaction publication, and adding a mergepublication. I've tried to go back to the set up before I made thechanges, even disabling replication and starting again from scratch.Still the problem persists.The owner of the distribution agent has dbo permissions in both thepublishing and subscribing database. Under 'Publisher and DistributorProperties' I've set the 'Replication Agent Connection to Publisher'and 'Agent Connection to the Subscriber' both to Use SQL ServerAuthentication, both with the same account that has dbo permissions inboth publishing and subscribing database.Has anybody come across this problem before? Can anybody make anysuggestions? Thanks in advance for your help.RegardsDamian HelmeIS ManagerINTEC Engineering (UK) Ltd.
View 1 Replies View RelatedWhy would you schedule a Snapshot Agent to run daily, weekly, monthly, etc. for Transactional replication when the transactions are replicated once the change is committed to the Publication database?
Does this help to replicate DDL against the Subscriber?
Thanks,
Thomas LeBlanc
I can't believe this. MSDE was a great product and I'm sure that SQL Express is even better. The difference is that I can't migrate any of my company's applications to it because they rely on a Replication architecture (requiring Replication Publication). And SQL Agent, how are you supposed to keep the database in good shape if you can't run Maintenance Plans at scheduled intervals?
I know, there are third party products out there to compensate for the lack of the SQL Agent, but they don't solve the replication problem. Besides, the Agent still exists in other editions - it's not like it has been dropped completely. Why do we have to lose functionality and features? We have already lost the use of a second CPU and memory > 1GB. Why do they need to keep taking stuff out? Why don't they just limit access to the new feature-set?
I would rather lose the new features such as Report Server and Full Text Indexing and get the SQL Agent and Replication Publication back. I can almost understand that with a free product, they don't want to keep piliing new features in just to give them away - even though I'm sure the sale of the larger editions more than covers the R&D investment in their construction. Why can't we chose? Agent or Report Server, Replication or Full Text Indexing? I don't need some of these new features (others might), I just want the features that I used to have. Perhaps SQL 2005 setup can be made to choose 2 out of the 4 features?
Whats more, I can't even say that I'll just stick with MSDE, as it now has a limited support life. Microsoft announced that any SQL Server version prior to 2005 SP1 will NOT be supported on Vista or Longhorn Server (nor does it support anything less than Windows 2000 SP4 or Windows XP SP2 where MSDE does).
OK - let me get this right. We had something that has been taken away in the new version. Perhaps I don't deserve the new version if I don't pay for it - fine - I'll stick to the old one I already have. BUT wait... I can't use that on the new platforms coming out.
Are my entry-level customers going to pay for the Workgroup Edition (add AUD$1200 to my entry level edition application of AUD$1500) to replace the Replication Publisher and provide a Maintenance plan? Is anyone going to pay for the re-architecting and re-construction of my application to work around these new limitations?
I guess that will be me. Is this what happens for being loyal to MS products?
Please - give back the SQL Agent and Replication publication.
Joe
When you create a transactional Replication.Is there a way to disable to snapshot agent scheduler. I believe that there is a way to disble it when you create a publisher.
Help ME!!!!!!!!!!!!!!!
The Agent has never been run occurred on Snapshot Agent Status-Replicationhow do I make it run successfully??
View 6 Replies View RelatedHi There
I have a publisher, remote distributor and subscriber all running SS2000.
Under replication monitor on the distributor, under replication alerts i have enabled the "replication agent failure" alert.
All i need to know is should this alert trigger if the distribution agent that runs on the subscriber not the distributor fails?
I have add occurences of the distribution agent failing and the alert is not triggerred, is this because it only triggers for the agents that run on the distributor, ie: snapshot and log reader agents?
Thanx
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).
Is any has the same problem ?
Hi,
I am trying to replicate a merge replication between two databases for 2 SQL Server 7 SP2 on Windows NT sp6a. I make a publication for my Server1 and set the properties for the publication (Choose Server1 as the Distributor, Merge publication, all subscribers will be running SQl Server etc, Publish All Data, No filter...blablabla finish).
The problem is that the SnapShot Agent firstly prepares all tables for Merge Replication in status of Snapshot ("Preparing table '[dbo].[Table]' for merge Replication") ... SQL Server Snapshot Agent expired when it arrives at a table which has 1,800,000 records.... it's a very important table for my business after having made the majority of it before. Following this error, it starts again, and always starts again while being expired and returned with this table.
Is it that the SnapshotAgent has a limit for Records? What can I look at to correct the problem.
Any help appreciated,