PollingInterval and HistoryVerboseLevel

I have read, and it has been recommended here, that in order to improve
transactional replication performance I should set the PollingInterval and
HistoryVerboseLevel to 0 on both the Log Reader and Distribution Agent jobs.
As we are utilizing scripts to enable and configure replication, I was
wondering if there were any built-in stored procedures that might help me do
this. I was able to use the @.optional_command_line switch on
sp_addsubscription to get these parameters set for the Distribution Agent.
Is there anything similar for the Log Reader Agent? I’d really prefer not to
have the users modify the Log Reader Agent job.
Thanks,
Mark
Mark,
I'd probably investigate sp_update_agent_profile for this. As far as i know
it cant be done pre-emptively.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Don't set the historyverboselevel to 0 for the log reader. Use profiles to
package up your agent properties and then modify the job to use the
different profile. Also have a look at -DefinitionFile to bundle up addition
properties not present in the profile.
You can change the profile used by using sp_update_agent_profile. You can
change the command line arguments by using sp_update_jobstep
Here is an example:
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
SELECT @.JobID = 0x644DDB4D684E324080CB1C265740831E
EXECUTE @.ReturnCode = msdb.dbo.sp_update_jobstep @.job_id = @.JobID,
@.step_id = 2, @.command = N'-Subscriber [DEV-HCOTTER\SQL2000] -SubscriberDB
[pubs] -Publisher [DEV-HCOTTER\SQL2000] -Distributor
[DEV-HCOTTER\SQL2000] -DistributorSecurityMode 1 -PublisherDB
Northwind] -Continuous -OutputverboseLevel 3', @.database_name = N''
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
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
"mrprice" <mrprice@.discussions.microsoft.com> wrote in message
news:14890C83-64E9-4C97-8418-49D5E4405B46@.microsoft.com...
>I have read, and it has been recommended here, that in order to improve
> transactional replication performance I should set the PollingInterval and
> HistoryVerboseLevel to 0 on both the Log Reader and Distribution Agent
> jobs.
> As we are utilizing scripts to enable and configure replication, I was
> wondering if there were any built-in stored procedures that might help me
> do
> this. I was able to use the @.optional_command_line switch on
> sp_addsubscription to get these parameters set for the Distribution Agent.
> Is there anything similar for the Log Reader Agent? I'd really prefer not
> to
> have the users modify the Log Reader Agent job.
> Thanks,
> Mark

Plz, what is the most "correct" method

Hi,

Given 2 tables:

Table1:
id Auto,int,PrimKey
table2id int
txt nvarchar50

Table2:
id Auto,int,PrimKey
order int

The scenario: Table2.order defines how the table1.txt is should be ordered.
Table1.table2id contains the id of the order. This cannot be changed :(

How do I select all Table1.txt values but ordered by their corresponding
values of the table2.order field?

--
Thx,
PipHans

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003Hi

The following should do what you require:

SELECT T1.id, T1.txt
FROM Table1 T1 JOIN Table2 T2 ON T1.Table2Id = T2.id
ORDER BY T2.order
John

"PipHans" <piphans@.hotmail.co> wrote in message
news:3f5f47c2$0$48896$edfadb0f@.dtext02.news.tele.d k...
> Hi,
> Given 2 tables:
> Table1:
> id Auto,int,PrimKey
> table2id int
> txt nvarchar50
> Table2:
> id Auto,int,PrimKey
> order int
> The scenario: Table2.order defines how the table1.txt is should be
ordered.
> Table1.table2id contains the id of the order. This cannot be changed :(
> How do I select all Table1.txt values but ordered by their corresponding
> values of the table2.order field?
> --
> Thx,
> PipHans
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003|||John Bell wrote:
> SELECT T1.id, T1.txt
> FROM Table1 T1 JOIN Table2 T2 ON T1.Table2Id = T2.id
> ORDER BY T2.order

It complains about the from-clause being wrong.
If I add "LEFT JOIN" instead of "JOIN", it runs like it should. (thx btw :)

Can you explain why it didnt run with just "join"?

--
Pip

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003|||Hi

A LEFT JOIN would be all rows in the left hand side table (Table1) with any
matching rows in the right hand table (Table2) therefore an entry in Table2
does not have to exist. If an entry in Table2 does not exist then NULL
values are returned.

JOIN (or INNER JOIN) is where both tables have to contain the joined data.

As you didn't post the offending query or the actual error message, the only
thing I can imaging is some other syntax error:

SELECT T1.id, T1.txt
FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.Table2Id = T2.id
ORDER BY T2.order

Should work OK.

John

"PipHans" <piphans@.hotmail.co> wrote in message
news:3f5f7147$0$48899$edfadb0f@.dtext02.news.tele.d k...
> John Bell wrote:
> > SELECT T1.id, T1.txt
> > FROM Table1 T1 JOIN Table2 T2 ON T1.Table2Id = T2.id
> > ORDER BY T2.order
> It complains about the from-clause being wrong.
> If I add "LEFT JOIN" instead of "JOIN", it runs like it should. (thx btw
:)
> Can you explain why it didnt run with just "join"?
> --
> Pip
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003|||PipHans (piphans@.hotmail.co) writes:
> John Bell wrote:
>> SELECT T1.id, T1.txt
>> FROM Table1 T1 JOIN Table2 T2 ON T1.Table2Id = T2.id
>> ORDER BY T2.order
> It complains about the from-clause being wrong.
> If I add "LEFT JOIN" instead of "JOIN", it runs like it should.
> (thx btw :)
> Can you explain why it didnt run with just "join"?

Since what John suggested is legal syntax in SQL Server (save that
he should have put the last "order" in brackets), I suspect that
you are not using SQL Server, but some other DB engine.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Since what John suggested is legal syntax in SQL Server (save that
> he should have put the last "order" in brackets), I suspect that
> you are not using SQL Server, but some other DB engine.

True, I only tested on Access. - The SQL server cant be reached from home :)

--
/Pip

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003|||John Bell wrote:
> Should work OK.

Yep. It worked on the SQL server at work...I only tested it on access
yesterday.

Thx :)

--
Pip

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003|||PipHans (piphans@.hotmail.co) writes:
> True, I only tested on Access. - The SQL server cant be reached from home

While both Access and SQL Server both claim to run SQL, there are
significant differences between the two - as there is about between
any pair of DBMSs.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp