Blocking in distribution database...

Hello...
I am noticing that everytime the the Agent history clean
up job runs... it causes contention and blocking in the
distribution database.
Has anyone come across this? If so, how did you resolve
this. I am using SQL Server 2000 and every server in my
topology uses SQL Server 2000 as well.
Please advise...
Thanks,
Brett
are you running datamirror? Datamirror does not clean up efficiently.
Blocking in the distribution database is caused by 2 factors
1) too many push agents running simultaneously. Try to stagger and schedule
them. If you can migrate to pull subscriptions.
2) too much replication metadata pooling in your distribution database due
to subscribers who are frequently off line. If you are using anonymous
subscribers this exacerbates the problem. Consider using named subscribers.
"Brett" <anonymous@.discussions.microsoft.com> wrote in message
news:1ae2001c41e7a$fc3ac590$a101280a@.phx.gbl...
> Hello...
> I am noticing that everytime the the Agent history clean
> up job runs... it causes contention and blocking in the
> distribution database.
> Has anyone come across this? If so, how did you resolve
> this. I am using SQL Server 2000 and every server in my
> topology uses SQL Server 2000 as well.
> Please advise...
> Thanks,
> Brett
>

BlackMail by software manufactureron sql 6.5

we are using a software that runs on sql 6.5 and the manufacturer is causing all types of problems

I tried to trace what he is doing on one few steps

i could not, how has he disabled the trace, can i enable it

how and will it have some repurcussion

i want to throw this vendor out

Plain BlackmailHow is the vendor connecting to your environment? VPN? Disable the VPN account. Also, analyze your logins and see which ones are used by your app. If possible, change the password on the application account (I hope that account is not SA), and by all means change the SA password. But before you do all that make sure you're not breaching your Software License Agreement by monkeying with all this stuff.|||Refer to your other http://www.dbforums.com/t990068.html post about enabling SQL Trace.

Ensure to tighten the security on SQL by using secure password for SA account.|||I mean that when i set up the trace,each operation that we do on the vendors software

I need to track where it effecting on sql tables

Can a vendor disable that tracking , or i have it set wrong or something

I dont have a vpn connection for the vendor open

so he can't enter without our approval

For trace is not user friendly on 6.5|||Ensure you're using correct password to connect SQL database.
There is no setting where you can bypass the Trace on SQL Server.

Bizarre Stored Procedure Behavior

I have dozens of stored procedures, but one of them is suddenly behaving badly.

Basically, if the sp is run as a query (full text), it runs fine and finishes successfully, but if 'executed' (Exec [sp_name]) it fails, saying that one of the referenced tables doesn't exist. The really weird thing is that the table is created earlier in the same procedure and is recognized enough to allow an index to be created on it. But when it comes to an Update statement, forget it. The sp dies saying the object (table) is unrecognized. I have dropped and recreated it, tried everything I can think of.

Has anyone else experienced this? Any ideas what may be causing it?

I'm stumped.

Thanks!you do have the correct names on your command object of the update statement?

bizarre query problem

Below is part of a stored proc.
This code returns an error ONLY if Field.OrderId is included in the query.
It runs ok if the OrderID is not included.
The error recieved is:
Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Running the code without field.OrderID returns..
(0 row(s) affected)
(1 row(s) affected) << ALSO: What does this represent ?
(297 row(s) affected)
CREATE TABLE [OrderRequest_Equipment] (
[OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NULL ,
[Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MoveEarliest] [datetime] NULL ,
[MoveLatest] [datetime] NULL ,
[UnLoadEarliest] [datetime] NULL ,
[UnLoadLatest] [datetime] NULL ,
[EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MoveType] [int] NULL ,
[OffRentID] [int] NULL ,
[Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
DEFAULT (0),
[FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
DEFAULT (0),
[RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
(0),
[RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
(0),
[RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
(0),
[Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[trucking] [smallmoney] NULL ,
[fuel] [smallmoney] NULL ,
[JobNumber] [int] NULL ,
[UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FinishedAsOf] [smalldatetime] NULL ,
CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
(
[OR_EQ_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
----
--
CREATE TABLE [DCCPendingMoves] (
[jobNumber] [int] NOT NULL
) ON [PRIMARY]
GO
THIS IS THE QUERY CAUSING THE PROBLEM...
delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
OrderRequest_Equipment Table
set IDENTITY_INSERT OrderRequest_Equipment on
insert into OrderRequest_Equipment
(OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
FinishedAsOf)
select
OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
FinishedAsOf
from bobmlt.dcc.dbo.DCCPendingMoves_ore
set IDENTITY_INSERT OrderRequest_Equipment off
Thanks in advance for any help on this...
Bob McClellan.> (1 row(s) affected) << ALSO: What does this represent ?
This is a clue. You probably have an INSERT trigger on the table that is
not written correctly to handle multi-row inserts.
Hope this helps.
Dan Guzman
SQL Server MVP
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:emOh9lxBGHA.740@.TK2MSFTNGP12.phx.gbl...
> Below is part of a stored proc.
> This code returns an error ONLY if Field.OrderId is included in the query.
> It runs ok if the OrderID is not included.
> The error recieved is:
> Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.
> Running the code without field.OrderID returns..
> (0 row(s) affected)
>
> (1 row(s) affected) << ALSO: What does this represent ?
>
> (297 row(s) affected)
>
> CREATE TABLE [OrderRequest_Equipment] (
> [OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
> [OrderID] [int] NULL ,
> [Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveEarliest] [datetime] NULL ,
> [MoveLatest] [datetime] NULL ,
> [UnLoadEarliest] [datetime] NULL ,
> [UnLoadLatest] [datetime] NULL ,
> [EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveType] [int] NULL ,
> [OffRentID] [int] NULL ,
> [Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
> DEFAULT (0),
> [FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
> DEFAULT (0),
> [RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
> (0),
> [RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
> (0),
> [RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
> (0),
> [Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [trucking] [smallmoney] NULL ,
> [fuel] [smallmoney] NULL ,
> [JobNumber] [int] NULL ,
> [UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FinishedAsOf] [smalldatetime] NULL ,
> CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
> (
> [OR_EQ_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ----
--
> CREATE TABLE [DCCPendingMoves] (
> [jobNumber] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
> THIS IS THE QUERY CAUSING THE PROBLEM...
> delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
> OrderRequest_Equipment Table
> set IDENTITY_INSERT OrderRequest_Equipment on
> insert into OrderRequest_Equipment
> (OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf)
> select
> OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf
> from bobmlt.dcc.dbo.DCCPendingMoves_ore
> set IDENTITY_INSERT OrderRequest_Equipment off
>
> Thanks in advance for any help on this...
> Bob McClellan.
>|||Seems that you have a trigger defined on the table whch isn=B4t able to
process multiple rows affected.
Triggers are fired on a statement basis NOT on a row basis...
HTH, jens Suessmeyer.|||Awesome...
Thanks guys...
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:emOh9lxBGHA.740@.TK2MSFTNGP12.phx.gbl...
> Below is part of a stored proc.
> This code returns an error ONLY if Field.OrderId is included in the query.
> It runs ok if the OrderID is not included.
> The error recieved is:
> Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.
> Running the code without field.OrderID returns..
> (0 row(s) affected)
>
> (1 row(s) affected) << ALSO: What does this represent ?
>
> (297 row(s) affected)
>
> CREATE TABLE [OrderRequest_Equipment] (
> [OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
> [OrderID] [int] NULL ,
> [Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveEarliest] [datetime] NULL ,
> [MoveLatest] [datetime] NULL ,
> [UnLoadEarliest] [datetime] NULL ,
> [UnLoadLatest] [datetime] NULL ,
> [EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveType] [int] NULL ,
> [OffRentID] [int] NULL ,
> [Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
> DEFAULT (0),
> [FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
> DEFAULT (0),
> [RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
> (0),
> [RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
> (0),
> [RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
> (0),
> [Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [trucking] [smallmoney] NULL ,
> [fuel] [smallmoney] NULL ,
> [JobNumber] [int] NULL ,
> [UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FinishedAsOf] [smalldatetime] NULL ,
> CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
> (
> [OR_EQ_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ----
--
> CREATE TABLE [DCCPendingMoves] (
> [jobNumber] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
> THIS IS THE QUERY CAUSING THE PROBLEM...
> delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
> OrderRequest_Equipment Table
> set IDENTITY_INSERT OrderRequest_Equipment on
> insert into OrderRequest_Equipment
> (OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf)
> select
> OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf
> from bobmlt.dcc.dbo.DCCPendingMoves_ore
> set IDENTITY_INSERT OrderRequest_Equipment off
>
> Thanks in advance for any help on this...
> Bob McClellan.
>