Hello,
Have a problem where a merge replication job is failing. I think I know why
I just don't know how to correct (without reinitialising).
By mistake, one of our guys (tracing another problem) deleted the remote
server on the subscriber which we can call serverS (the remote server
deleted in this case was itself - i.e ServerS is the name of the remote
server and this is on ServerS - it was referencing itself)
Now when the publisher serverP starts to synchronise with this subscriber we
get the following error message :-
The subscription could not be found
Source ServerS error number 20021
The remote server on serverS is set up again and looks just like it did
before but I'm guessing that the system is smart enough to know that this is
not the original and will not see the newly recreated remote server?
Any ideas/advice greatly appreciated..
Thanks
David
...forgot to say : the main error message displayed is
'The subscription to publication 'XXXX' is invalid'
rgds
David
"David Wilce" <david.wilce@.btinternet.com> wrote in message
news:1eqdnWxc4p43bdTYnZ2dnUVZ8qSdnZ2d@.bt.com...
> Hello,
> Have a problem where a merge replication job is failing. I think I know
why
> I just don't know how to correct (without reinitialising).
> By mistake, one of our guys (tracing another problem) deleted the remote
> server on the subscriber which we can call serverS (the remote server
> deleted in this case was itself - i.e ServerS is the name of the remote
> server and this is on ServerS - it was referencing itself)
> Now when the publisher serverP starts to synchronise with this subscriber
we
> get the following error message :-
> The subscription could not be found
> Source ServerS error number 20021
> The remote server on serverS is set up again and looks just like it did
> before but I'm guessing that the system is smart enough to know that this
is
> not the original and will not see the newly recreated remote server?
> Any ideas/advice greatly appreciated..
> Thanks
> David
>
|||Hi Dave,
I know we talked about this offline but for anyone else who needs the
solution......:
Try:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks again Paul
Invaluable support as ever!
Regards
Dave
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uItsjMp$GHA.4592@.TK2MSFTNGP03.phx.gbl...
> Hi Dave,
> I know we talked about this offline but for anyone else who needs the
> solution......:
> Try:
> Use Master
> go
> Select @.@.Servername
> This should return your current server name but if it
> returns NULL then try:
> Use Master
> go
> Sp_DropServer 'OldName'
> GO
> Use Master
> go
> Sp_Addserver 'NewName', 'local'
> GO
> Stop and Start SQL Services
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
Showing posts with label job. Show all posts
Showing posts with label job. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
merge agent running a job on a different server.
I have a database working with Merge replication, there could possibly be a
audit database associated with this replicated database. After talking about
it we decided it would be better to have each subscriber database have a copy
of both the replication database and the audit database without setting up
the audit database for replication.
I’m trying to accomplish the following tasks.
1)After merging the data from the subscriber to the publisher the merge
agent would kick off a job to run a BCP command moving the audit data to txt
file. The audit triggers will have the not for replication attribute to
prevent firing these triggers when data is merged.
2)Truncate the subscriber’s server audit data.
3)Do a bulk insert onto the publisher servers audit database.
How can I kick off a job on the subscriber’s server when the merge agent
resides on the publisher’s server? I would really like this to all work off
of one job; I don’t want to create a scheduled job to run on each subscriber
to move this data. Any suggestions?
I’m also trying not to replicate this database, this is a heavily
distributed app and I’m trying to limit the amount of potential issues.
Thank you in advance.
Pauly C
Pauly,
why not use snapshot replication for this - it would seem to do exactly what
you reauire, and synchronization could be initiated at the publisher
immediately after the merge synchronization.
If you really don't want to use replication, then I'd still control the
system from the publisher. Using linked servers you could truncate the audit
table and transfer the table on each subscriber.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thank you once again for you help, I will check out both options.
Thank you,
Pauly C
"Paul Ibison" wrote:
> Pauly,
> why not use snapshot replication for this - it would seem to do exactly what
> you reauire, and synchronization could be initiated at the publisher
> immediately after the merge synchronization.
> If you really don't want to use replication, then I'd still control the
> system from the publisher. Using linked servers you could truncate the audit
> table and transfer the table on each subscriber.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
audit database associated with this replicated database. After talking about
it we decided it would be better to have each subscriber database have a copy
of both the replication database and the audit database without setting up
the audit database for replication.
I’m trying to accomplish the following tasks.
1)After merging the data from the subscriber to the publisher the merge
agent would kick off a job to run a BCP command moving the audit data to txt
file. The audit triggers will have the not for replication attribute to
prevent firing these triggers when data is merged.
2)Truncate the subscriber’s server audit data.
3)Do a bulk insert onto the publisher servers audit database.
How can I kick off a job on the subscriber’s server when the merge agent
resides on the publisher’s server? I would really like this to all work off
of one job; I don’t want to create a scheduled job to run on each subscriber
to move this data. Any suggestions?
I’m also trying not to replicate this database, this is a heavily
distributed app and I’m trying to limit the amount of potential issues.
Thank you in advance.
Pauly C
Pauly,
why not use snapshot replication for this - it would seem to do exactly what
you reauire, and synchronization could be initiated at the publisher
immediately after the merge synchronization.
If you really don't want to use replication, then I'd still control the
system from the publisher. Using linked servers you could truncate the audit
table and transfer the table on each subscriber.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thank you once again for you help, I will check out both options.
Thank you,
Pauly C
"Paul Ibison" wrote:
> Pauly,
> why not use snapshot replication for this - it would seem to do exactly what
> you reauire, and synchronization could be initiated at the publisher
> immediately after the merge synchronization.
> If you really don't want to use replication, then I'd still control the
> system from the publisher. Using linked servers you could truncate the audit
> table and transfer the table on each subscriber.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Friday, March 23, 2012
Mentoring
I graduated several years ago with a CS bachelor degree. My first (and current) job hired me mainly to replace a consultant they had been using to administer and modify their DB.
Basically I taught myself Access to accomplish the above. That was three years ago and looking back the DB is designed and implemented poorly, although it works fine. But I believe our data is going to out grow Access in the next couple years so I thought I'd redo the whole thing, again, using Access as the front end and SQL Server 2005 Express (we are a small company) as the backend. My other motivation is I want to learn SQL Server to expand my future job opportunities. Basically I have no room to grow in my current job. I really have no time table or deadline so I can take my time learining SQL Server.
But I always have doubts as to whether or not I'm doing things right. In an ideal world I would have been hired by a software company and learned the ropes. In reality I was hired by a small, non-technical, company and I learned enough to get the job done.
Basically I want to know if anyone with "real world" experience developing client/server solutions would be interested in guiding me in my effort to redo this DB utilizing SQL Server 2005 Express and Access as the front end. The goal is that I will learn how to use these two technologies in a "real world" environment. I would guess most communication would be done via email. I don't need someone to hold my hand just someone to offer advice as to what techniques to use. An example might be should I use bound controls or ADO? Not how do I use bound controls or ADO.
I feel a bit silly posting this, but I've always wanted someone to learn from and this forum seems to be full of helpful people who have the knowledge I'm seeking.
If you are interested please email me (gwgeller@.yahoo.com). I can provide much more detail if needed.
Thanks,
GGHeck, why settle for one abuser, when you can have dozens of us? I'd suggest that you just plain forget about the "mentor" idea, and simply participate in the forums here.
A mentor is a wonderful thing, and having a mentor is a lot better than not having one. Participating in the forum ratchets that whole experience up several levels, in that you then have a number of people with more experience, scattered around the world so time becomes irrelevant (yes, at least some of us are online almost any hour of almost any day). You can also get more than one opinion, which is almost always a good thing... That way you can consider a few viewpoints and then form your own, rather than simply inheriting the biases and methods of one mentor.
More importantly, you'll learn much more by teaching (helping others) than you possibly can by studying (being helped by a mentor). When people ask questions that you're comfortable answering (even if you need to create a test scenario to validate your initial response), go ahead and answer them. Every time you do this, you'll learn something, and more often than not you'll learn something valuable. If you are wrong (and often even when you're right), someone else will chime in, and you can learn from that too.
There are plenty of opinions around here, and people aren't usually shy about offering them. There are a few moderators that help to police the spam and the really obnoxious folks (we don't mind someone telling you that you're wrong, but they need to be polite about it and had better offer you an explanation instead of just a post saying "you're wrong"), and those moderators are what really set a good forum apart from the free-for-all of Usenet... We actively try to add value to the process by pruning out the garbage and sometimes giving folks a nudge when they've got good ideas but they're hesitant to express them.
-PatP|||Pat,
Thanks for the reply. I debated between just posting my questions and this mentoring idea. It is true what you say about getting multiple perspectives and I considered that. I guess what swayed me towards the mentoring idea was the amount of questions I'd have and the specifics of my own situation. I didn't want to bog the forum down with a lot of posts. I figured a conversation with one person would be more efficient and I wouldn't worry about "stupid" or small questions that I don't feel worthy of a post. So for now I'll see if anyone is willing to do the one on one thing, but if it doesn't work out I'll post away :)|||You have the right attitude.
My advice: Maintain the existing Access system but whenever you have slack time, use it to learn SQL Server and build a new system on that platform. You will grow your skill set, satisfy your growth urges, and in the long run build better systems for the companies that you work for.
You want formal training or a proper mentor, but those are rare luxuries in this industry. If those opportunities arise, take them, but don't expect them. Don't wait on anything else. Take initiative yourself. Ask for help when you need it and learn the technologies that you are interested in.
Good luck!|||Heck, I've long since cornered the market on stupid around here... I'm sure that I'm the undisputed king!
Jump in, nobody bites (well, I guess I do bite, but only when someone asks me to, but that's a whole different story). Dive in, paddle around a bit, have fun. That's pretty much what we're here for!
-PatP|||Mentor tip #1 - go unbound. It will force you to learn ADO and disconnected client server techniques which will stand you in good stead when you move on to .NET (or whatever else).
There are a few moderators that help to police the spam blah blah blah and those moderators are what really set a good forum apart from the free-for-all of Usenet... Aw shucks - thanks Pat :D|||Yeah, but if he's going to go unbound he might as well develop in .Net rather than Access. You lose half the convenience of MSAccess as a front end if you use unbound forms and controls.
Besides, he's trying to learn SQL Server, not front-end development. Learning both at the same time may be a bit much. I'd suggest he stick with his idea of converting the Access database to SQL Server back-end.
But, you should definitely convert the Access front-end to a Microsoft Access Data Project, rather than a classic mdb file.
Going unbound would increase your application performance, but probably not noticably unless you have scores of uses, and since you are currently using straight MS Access I'd guess you have no more than five or six simultaneous users, right?|||That bound control/ADO question was only an example of what questions I might ask. I didn't want ppl thinking I was a total newbie. I think I have the tools I just need to know the best way to use them. After I posted it I knew I should have come up with something different.
My goal wasn't to get into specifics in this post, however Blindman's response relates to some other questions of mine. I want to keep this post limited to the "mentoring" aspect so I'll start another post using Blindman's response. (http://www.dbforums.com/showthread.php?t=1608587)
Thanks to all who have responded so far.|||It could be said that some response boarder on Mental-ing
Like this one
Did you download Express yet?
First thing to do would be to create a data migration plan|||Brett - Do you mean SQL Server 2005 Express? If so yes, I have had it for awhile along with VB 2005 Express. I have completed a couple tutorials on both and actually have start converting some of the Access DB to SQL Server, but only for learning purposes. You are right about a data migration plan but that is beyond the scope of this post.sql
Basically I taught myself Access to accomplish the above. That was three years ago and looking back the DB is designed and implemented poorly, although it works fine. But I believe our data is going to out grow Access in the next couple years so I thought I'd redo the whole thing, again, using Access as the front end and SQL Server 2005 Express (we are a small company) as the backend. My other motivation is I want to learn SQL Server to expand my future job opportunities. Basically I have no room to grow in my current job. I really have no time table or deadline so I can take my time learining SQL Server.
But I always have doubts as to whether or not I'm doing things right. In an ideal world I would have been hired by a software company and learned the ropes. In reality I was hired by a small, non-technical, company and I learned enough to get the job done.
Basically I want to know if anyone with "real world" experience developing client/server solutions would be interested in guiding me in my effort to redo this DB utilizing SQL Server 2005 Express and Access as the front end. The goal is that I will learn how to use these two technologies in a "real world" environment. I would guess most communication would be done via email. I don't need someone to hold my hand just someone to offer advice as to what techniques to use. An example might be should I use bound controls or ADO? Not how do I use bound controls or ADO.
I feel a bit silly posting this, but I've always wanted someone to learn from and this forum seems to be full of helpful people who have the knowledge I'm seeking.
If you are interested please email me (gwgeller@.yahoo.com). I can provide much more detail if needed.
Thanks,
GGHeck, why settle for one abuser, when you can have dozens of us? I'd suggest that you just plain forget about the "mentor" idea, and simply participate in the forums here.
A mentor is a wonderful thing, and having a mentor is a lot better than not having one. Participating in the forum ratchets that whole experience up several levels, in that you then have a number of people with more experience, scattered around the world so time becomes irrelevant (yes, at least some of us are online almost any hour of almost any day). You can also get more than one opinion, which is almost always a good thing... That way you can consider a few viewpoints and then form your own, rather than simply inheriting the biases and methods of one mentor.
More importantly, you'll learn much more by teaching (helping others) than you possibly can by studying (being helped by a mentor). When people ask questions that you're comfortable answering (even if you need to create a test scenario to validate your initial response), go ahead and answer them. Every time you do this, you'll learn something, and more often than not you'll learn something valuable. If you are wrong (and often even when you're right), someone else will chime in, and you can learn from that too.
There are plenty of opinions around here, and people aren't usually shy about offering them. There are a few moderators that help to police the spam and the really obnoxious folks (we don't mind someone telling you that you're wrong, but they need to be polite about it and had better offer you an explanation instead of just a post saying "you're wrong"), and those moderators are what really set a good forum apart from the free-for-all of Usenet... We actively try to add value to the process by pruning out the garbage and sometimes giving folks a nudge when they've got good ideas but they're hesitant to express them.
-PatP|||Pat,
Thanks for the reply. I debated between just posting my questions and this mentoring idea. It is true what you say about getting multiple perspectives and I considered that. I guess what swayed me towards the mentoring idea was the amount of questions I'd have and the specifics of my own situation. I didn't want to bog the forum down with a lot of posts. I figured a conversation with one person would be more efficient and I wouldn't worry about "stupid" or small questions that I don't feel worthy of a post. So for now I'll see if anyone is willing to do the one on one thing, but if it doesn't work out I'll post away :)|||You have the right attitude.
My advice: Maintain the existing Access system but whenever you have slack time, use it to learn SQL Server and build a new system on that platform. You will grow your skill set, satisfy your growth urges, and in the long run build better systems for the companies that you work for.
You want formal training or a proper mentor, but those are rare luxuries in this industry. If those opportunities arise, take them, but don't expect them. Don't wait on anything else. Take initiative yourself. Ask for help when you need it and learn the technologies that you are interested in.
Good luck!|||Heck, I've long since cornered the market on stupid around here... I'm sure that I'm the undisputed king!
Jump in, nobody bites (well, I guess I do bite, but only when someone asks me to, but that's a whole different story). Dive in, paddle around a bit, have fun. That's pretty much what we're here for!
-PatP|||Mentor tip #1 - go unbound. It will force you to learn ADO and disconnected client server techniques which will stand you in good stead when you move on to .NET (or whatever else).
There are a few moderators that help to police the spam blah blah blah and those moderators are what really set a good forum apart from the free-for-all of Usenet... Aw shucks - thanks Pat :D|||Yeah, but if he's going to go unbound he might as well develop in .Net rather than Access. You lose half the convenience of MSAccess as a front end if you use unbound forms and controls.
Besides, he's trying to learn SQL Server, not front-end development. Learning both at the same time may be a bit much. I'd suggest he stick with his idea of converting the Access database to SQL Server back-end.
But, you should definitely convert the Access front-end to a Microsoft Access Data Project, rather than a classic mdb file.
Going unbound would increase your application performance, but probably not noticably unless you have scores of uses, and since you are currently using straight MS Access I'd guess you have no more than five or six simultaneous users, right?|||That bound control/ADO question was only an example of what questions I might ask. I didn't want ppl thinking I was a total newbie. I think I have the tools I just need to know the best way to use them. After I posted it I knew I should have come up with something different.
My goal wasn't to get into specifics in this post, however Blindman's response relates to some other questions of mine. I want to keep this post limited to the "mentoring" aspect so I'll start another post using Blindman's response. (http://www.dbforums.com/showthread.php?t=1608587)
Thanks to all who have responded so far.|||It could be said that some response boarder on Mental-ing
Like this one
Did you download Express yet?
First thing to do would be to create a data migration plan|||Brett - Do you mean SQL Server 2005 Express? If so yes, I have had it for awhile along with VB 2005 Express. I have completed a couple tutorials on both and actually have start converting some of the Access DB to SQL Server, but only for learning purposes. You are right about a data migration plan but that is beyond the scope of this post.sql
Subscribe to:
Posts (Atom)