Replication in SQL: commands

Showing posts with label commands. Show all posts

Wednesday, March 28, 2012

Replication setup via VB.NET

I have a VB.NET program that is executing the SQL commands to setup
replication. I am getting an error (A severe error has occured) on the
sp_addmergepubliction command.
I did setup a profiler and notice one area:
sp_adddistributor was found in the text of this event, the text has been
replaced with this comment for security reasons.
My entire text, before the sp_addmergepulication does:
use master
exec sp_replicationdboption @.dbname='InWare',etc
exec dbo.sp_helpditributor @.rpcsrvname=@.distributor OUTPUT
if @.distributor is null
begin
declare @.xpath varchar (255)
select @.xpath=substring(filename,1,charindex('InWare_data ',filename)-1)
FROM master..sysdatabases where name='inware'
exec sp_adddistributor @.distributor=@.@.servername
exec sp_adddistributiondb @.database='inxsqlDIST',etc
exec sp_adddistpublisher @.publisher=@.@.servername, etc
end
exec sp_replicationdboption @.dbname='InWare',@.optname='merge
publish',@.value='true'
I guess, now that I see Profiler has the line sp_adddistributor
commented out, this could cause the problem. What is commenting it out -
SQL or VB.NET?
I want to use VB (my program) to have the user setup replication so they
don't have to use the enterprise manager, and it allows me to control
the setup.
Thanks.
Darin
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Darin wrote:
> I want to use VB (my program) to have the user setup replication so they
> don't have to use the enterprise manager, and it allows me to control
> the setup.
How about using SQL-DMO through COM Interop?
|||Profiler is removing it because the command contains the keyword "password".
Profiler won't display anything that includes that keyword to eliminate a
security hole. The command is still likely being run, just not displayed in
Profiler.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Darin" <darin_nospam@.nospamever> wrote in message
news:exGVx28KFHA.2596@.TK2MSFTNGP10.phx.gbl...
>I have a VB.NET program that is executing the SQL commands to setup
> replication. I am getting an error (A severe error has occured) on the
> sp_addmergepubliction command.
> I did setup a profiler and notice one area:
> sp_adddistributor was found in the text of this event, the text has been
> replaced with this comment for security reasons.
> My entire text, before the sp_addmergepulication does:
> use master
> exec sp_replicationdboption @.dbname='InWare',etc
> exec dbo.sp_helpditributor @.rpcsrvname=@.distributor OUTPUT
> if @.distributor is null
> begin
> declare @.xpath varchar (255)
> select @.xpath=substring(filename,1,charindex('InWare_data ',filename)-1)
> FROM master..sysdatabases where name='inware'
> exec sp_adddistributor @.distributor=@.@.servername
> exec sp_adddistributiondb @.database='inxsqlDIST',etc
> exec sp_adddistpublisher @.publisher=@.@.servername, etc
> end
> exec sp_replicationdboption @.dbname='InWare',@.optname='merge
> publish',@.value='true'
> I guess, now that I see Profiler has the line sp_adddistributor
> commented out, this could cause the problem. What is commenting it out -
> SQL or VB.NET?
> I want to use VB (my program) to have the user setup replication so they
> don't have to use the enterprise manager, and it allows me to control
> the setup.
> Thanks.
> Darin
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Any idea what could be causing the error:
A severe error occured .... Any results should be discarded
on the exec sp_addreplication @.publication='InWareMerge'
when it works find on QA?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||If you script a publication in EM, it assumes you have the publisher,
distributor, and subscribers already set up for all servers involved in
replication. If that is not the case, then creating a publication will
fail.
GNH
"Darin" <darin_nospam@.nospamever> wrote in message
news:ukmK5P9KFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Any idea what could be causing the error:
> A severe error occured .... Any results should be discarded
> on the exec sp_addreplication @.publication='InWareMerge'
> when it works find on QA?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||My problem is all of my script run from the VB.NET program work fine
until that command.
Whereas if I take all of the script and paste it into a QA window it all
works perfectly.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Wednesday, March 21, 2012

Replication Problem? Very high undistributed commands --transaction replication

Can anybody help me
we have a transactional replication going on from server A to Server B.
(sql server 2005)
The replication working properly till yesterday..but now i dont find
the recent data at subscriber .. synchronization status shows
"Delivering replicated transactions" When i checked replication monitor
there are so many undistributed commands . replicated commands are not
getting applied at the subscriber..
how to resolve this issue?
thanksJust because you have a high number of undistributed commands doesn't
necessarily mean they are not getting distributed. What it means is that
they are waiting to be distributed. This could be a performance problem, or
it could be that there are a lot of text commands slowing the overall
throughput, or it could mean that you have subscribers which are offline.
The most significant thing you can do to improve throughput is to set
subscriptionstreams to something like 8.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> Can anybody help me
> we have a transactional replication going on from server A to Server B.
> (sql server 2005)
> The replication working properly till yesterday..but now i dont find
> the recent data at subscriber .. synchronization status shows
> "Delivering replicated transactions" When i checked replication monitor
> there are so many undistributed commands . replicated commands are not
> getting applied at the subscriber..
> how to resolve this issue?
> thanks
>|||i dont find the subscriptionstreams option in the distribution agent
profile..is there any other way? should i stop the distribution agent
and start again' or should i reinitialize the subscription? or
should take the snapshot by starting snapshot agent?'
there are around 2207484 undistributed commands
plzz help
thanks
Hilary Cotter wrote:
> Just because you have a high number of undistributed commands doesn't
> necessarily mean they are not getting distributed. What it means is that
> they are waiting to be distributed. This could be a performance problem, or
> it could be that there are a lot of text commands slowing the overall
> throughput, or it could mean that you have subscribers which are offline.
> The most significant thing you can do to improve throughput is to set
> subscriptionstreams to something like 8.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> > Can anybody help me
> >
> > we have a transactional replication going on from server A to Server B.
> > (sql server 2005)
> >
> > The replication working properly till yesterday..but now i dont find
> > the recent data at subscriber .. synchronization status shows
> > "Delivering replicated transactions" When i checked replication monitor
> > there are so many undistributed commands . replicated commands are not
> > getting applied at the subscriber..
> >
> > how to resolve this issue?
> >
> > thanks
> >|||You have to add it manually to the distribution agent like
this -SubscriptionStreams 8
You don't have to create another snapshot, you have to figure out why it is
so slow. For example are their triggers hanging off the subscriber tables?
Are their subscribers who have not connected in some time? Are you
replicating text - run profiler on the subscriber to determine what commands
are flowing right now.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Vishal" <rvishal1305@.gmail.com> wrote in message
news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
>i dont find the subscriptionstreams option in the distribution agent
> profile..is there any other way? should i stop the distribution agent
> and start again' or should i reinitialize the subscription? or
> should take the snapshot by starting snapshot agent?'
> there are around 2207484 undistributed commands
> plzz help
> thanks
>
> Hilary Cotter wrote:
>> Just because you have a high number of undistributed commands doesn't
>> necessarily mean they are not getting distributed. What it means is that
>> they are waiting to be distributed. This could be a performance problem,
>> or
>> it could be that there are a lot of text commands slowing the overall
>> throughput, or it could mean that you have subscribers which are offline.
>> The most significant thing you can do to improve throughput is to set
>> subscriptionstreams to something like 8.
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>> "Vishal" <rvishal1305@.gmail.com> wrote in message
>> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
>> > Can anybody help me
>> >
>> > we have a transactional replication going on from server A to Server B.
>> > (sql server 2005)
>> >
>> > The replication working properly till yesterday..but now i dont find
>> > the recent data at subscriber .. synchronization status shows
>> > "Delivering replicated transactions" When i checked replication monitor
>> > there are so many undistributed commands . replicated commands are not
>> > getting applied at the subscriber..
>> >
>> > how to resolve this issue?
>> >
>> > thanks
>> >
>|||thanks for ur reply..
Could you plzz tell me how do i add the SubscriptionStreams manually'
Yes there are 3 triggers on the tables at the subscriber side.which get
fired when there is an insert/update from the publisher to those
tables..
However, as per replication monitor the status is excellent..latency is
0..only thing is replicated transactions are not gettting applied at
the subscriber..the undistributed transaction count keeps on
growing..i dont see new records aded at the subscriber in last 20-22
hrs'
Earlier the synchronization status used to show the message something
like this 'Delivered 18 transactions with 18 commands"..however now it
always shows this message "Delivering replicated transaction"..
could u plzz tell me wat should i do'
thanks
Hilary Cotter wrote:
> You have to add it manually to the distribution agent like
> this -SubscriptionStreams 8
> You don't have to create another snapshot, you have to figure out why it is
> so slow. For example are their triggers hanging off the subscriber tables?
> Are their subscribers who have not connected in some time? Are you
> replicating text - run profiler on the subscriber to determine what commands
> are flowing right now.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Vishal" <rvishal1305@.gmail.com> wrote in message
> news:1161997667.578937.202070@.e3g2000cwe.googlegroups.com...
> >i dont find the subscriptionstreams option in the distribution agent
> > profile..is there any other way? should i stop the distribution agent
> > and start again' or should i reinitialize the subscription? or
> > should take the snapshot by starting snapshot agent?'
> > there are around 2207484 undistributed commands
> >
> > plzz help
> >
> > thanks
> >
> >
> > Hilary Cotter wrote:
> >> Just because you have a high number of undistributed commands doesn't
> >> necessarily mean they are not getting distributed. What it means is that
> >> they are waiting to be distributed. This could be a performance problem,
> >> or
> >> it could be that there are a lot of text commands slowing the overall
> >> throughput, or it could mean that you have subscribers which are offline.
> >> The most significant thing you can do to improve throughput is to set
> >> subscriptionstreams to something like 8.
> >> --
> >> Hilary Cotter
> >> Looking for a SQL Server replication book?
> >> http://www.nwsu.com/0974973602.html
> >>
> >> Looking for a FAQ on Indexing Services/SQL FTS
> >> http://www.indexserverfaq.com
> >>
> >> "Vishal" <rvishal1305@.gmail.com> wrote in message
> >> news:1161991038.050861.131000@.i42g2000cwa.googlegroups.com...
> >> > Can anybody help me
> >> >
> >> > we have a transactional replication going on from server A to Server B.
> >> > (sql server 2005)
> >> >
> >> > The replication working properly till yesterday..but now i dont find
> >> > the recent data at subscriber .. synchronization status shows
> >> > "Delivering replicated transactions" When i checked replication monitor
> >> > there are so many undistributed commands . replicated commands are not
> >> > getting applied at the subscriber..
> >> >
> >> > how to resolve this issue?
> >> >
> >> > thanks
> >> >
> >sql

Wednesday, March 28, 2012

Replication setup via VB.NET

Wednesday, March 21, 2012

Replication Problem? Very high undistributed commands --transaction replication

Replication Problem? Very high undistributed commands --transaction replication

Replication in SQL

Blog Archive

About Me