Cascade Delete Not Working Correctly?

I have a master table that is updated. The master has
child records that are referenced by a foreign key with
the CASCADE DELETE option.
In the transaction log, an update of the master appears
as a DELETE/INSERT. The primary key is not being updated.
For the child record, the same update appears to
DELETE/INSERT the child.
Occassionaly, when a master record is updated, the child
record is deleted but not inserted.
Can anyone explain why?
TIA,
HarryIn order to do this you will need to enable Cascade Update as well as
Cascade Delete...
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:ec2901c4127b$e58cbde0$a001280a@.phx.gbl...
> I have a master table that is updated. The master has
> child records that are referenced by a foreign key with
> the CASCADE DELETE option.
> In the transaction log, an update of the master appears
> as a DELETE/INSERT. The primary key is not being updated.
> For the child record, the same update appears to
> DELETE/INSERT the child.
> Occassionaly, when a master record is updated, the child
> record is deleted but not inserted.
> Can anyone explain why?
> TIA,
> Harry|||My apologies, I've not made myself clear.
I'm not trying to do this.
Firstly, I'm trying to understand why an update of a
master record results in an delete/insert of the child.
Secondly, why the delete part sometimes fails.
TIA.
>--Original Message--
>In order to do this you will need to enable Cascade
Update as well as
>Cascade Delete...
>--
>James Goodman
>MCSE MCDBA
>http://www.angelfire.com/sports/f1pictures/
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
>news:ec2901c4127b$e58cbde0$a001280a@.phx.gbl...
updated.
>
>.
>|||What exactly are you auditing to see this?
I cannot replicate this on a sample db I have...
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
> My apologies, I've not made myself clear.
> I'm not trying to do this.
> Firstly, I'm trying to understand why an update of a
> master record results in an delete/insert of the child.
> Secondly, why the delete part sometimes fails.
> TIA.
> Update as well as
> message
> updated.|||I'm using the transaction log explorer
tool from Lumigent.
It shows that some updates of the master keep
the child and others do not.
>--Original Message--
>What exactly are you auditing to see this?
>I cannot replicate this on a sample db I have...
>--
>James Goodman
>MCSE MCDBA
>http://www.angelfire.com/sports/f1pictures/
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
> news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
has
with
appears
child
>
>.
>|||"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
> My apologies, I've not made myself clear.
> I'm not trying to do this.
> Firstly, I'm trying to understand why an update of a
> master record results in an delete/insert of the child.
Has the table got triggers associated with it? An Update trigger results in
updates being converted to a delete followed by an insert (so the trigger
can reference the before and after values in the INSERTED and DELETED
tables)
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||The only triggers on the table are SQL Server merge
replication triggers.
Interesting point though. I was not aware of that
behaviour.
>--Original Message--
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
> news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
>Has the table got triggers associated with it? An Update
trigger results in
>updates being converted to a delete followed by an insert
(so the trigger
>can reference the before and after values in the INSERTED
and DELETED
>tables)
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.614 / Virus Database: 393 - Release Date:
05/03/2004
>
>.
>

Capturing ISS Log data

I am quite new to Reporting Services and need some help figuring out why i can't correctly capture the referrrer information in the IIS Logs.

As far as i know when you run the data extraction tool provided with RS it copies the logs into the staging db, from there it then copies all the data into the WSSFact table in the actual db.

I thought that if i entered a URL into the referrer table that when it populates the WSSFact table it would check to see if the referrer URL matches any of the entries in the referrer table and if so enter its id, otherwise enter the id for No referrer.

This seems to work fine for all existing entires in the referrer table but it ignores the new one i have added. Does anyone know if there is something else that i have to do or check?It sounds like you are using IIS tools. I am not aware of the WSSFact table and such. Can you provide a link to the tools you are using?|||

I am using the data extraction tool provided with Reporting Services. The problem is you can't see what this tool does, therefore its difficult to trace the process of how it reads the logs and places the information in the database.

Like i said i am pretty sure it places the log information into a staging database and then from there into the actual one used by to generate the reports.

What i need to know is why it keeps showing as not having any referrer information in the database when you can clearly see there is info in the logs.

Sorry if i wasn't clear the first time.

capacity was exceeded

I am getting the following error when I try to run a
remote query, it was working correctly for a while and
Suddenly I started getting this error below:
cannot create new transaction because capacity was exceeded
Why is this happening?
Thanks,
JohnThat doesn't sound like a SQL Server error to me. So, the error is probably
generated by your application/dev tool. You should check that documentation.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:040d01c3ad4a$c3d558b0$a101280a@.phx.gbl...
> I am getting the following error when I try to run a
> remote query, it was working correctly for a while and
> Suddenly I started getting this error below:
> cannot create new transaction because capacity was exceeded
> Why is this happening?
> Thanks,
> John|||John,
Is this the message? Are you getting it when calling ADO.Con.BeginTrans()?
Microsoft OLE DB Provider for SQL Server error '8004d01d'
Cannot create new transaction because capacity was exceeded
This appears to be an ADO / OLEDB problem. (One post (from a few years ago)
on Google http://tinyurl.com/ven4 refers to the problem being triggered by
calling a class module which had created an implicit transaction.)
Sorry that I am not able to help more, but perhaps this clue will help.
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:040d01c3ad4a$c3d558b0$a101280a@.phx.gbl...
> I am getting the following error when I try to run a
> remote query, it was working correctly for a while and
> Suddenly I started getting this error below:
> cannot create new transaction because capacity was exceeded
> Why is this happening?
> Thanks,
> John

Can't use variable against a partitioned view?

I'm using SQL Server 2000. I have two partitioned tables, Result1 and
Result2, and a partitioned view ResultView. This query is correctly
optimized to use Result1:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
This query scans both tables:
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
This means that I cannot take advantage of partitioning unless all queries
use constants for their predicates?!!! That means the entire application
would have to be build around dynamic SQL instead of simple stored procedure
parameters. Can this be true?
Here are the execution plans:
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = 1
StmtText
-----
|--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
|--Index
Scan(OBJECT:([Toggle].[dbo].[Result1].[IX_Result1_TestID]))
DECLARE @.myid int
SET @.myid = 1
SELECT COUNT(*) FROM dbo.ResultView
where ModelInterfaceID = @.myid
StmtText
-------
|--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
|--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
|--Concatenation
|--Parallelism(Gather Streams)
| |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
| |--Filter(WHERE:(STARTUP EXPR([@.myid]=1)))
| |--Index
Seek(OBJECT:([Toggle].[dbo].[Result1].[PK_Result1]),
SEEK:([Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
|--Filter(WHERE:(STARTUP EXPR([@.myid]=2)))
|--Index
Seek(OBJECT:([Toggle].[dbo].[Result2].[PK_Result2]),
SEEK:([Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
Thanks,
IBThe first execution plan removes the unneeded table reference entirely
because the partition value is known at compile time. However, the second
parameterized query is also efficient. Note the STARTUP EXPR; the
corresponding table is accessed at execution time only if the predicate
(@.myid]=1 or @.myid]=2) is true. Run the query with SET STATISTICS IO ON to
see the actual stats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Itchy Brother" <Itchy Brother@.discussions.microsoft.com> wrote in message
news:8813AC97-916C-4573-9701-5EB74AE1BF71@.microsoft.com...
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored
> procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT:([Toggle].[dbo].[Result1].[IX_Result1_TestID]))
>
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream
> Aggregate(DEFINE:([partialagg1010]=Count(*)))
> | |--Filter(WHERE:(STARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK:([Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream
> Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Filter(WHERE:(STARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK:([Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB|||Itchy,
The query plan has to work for all possible values of the
variable/parameter, because the query plan is cached and reused.
However, as noted by Dan, if run the query and monitor the table reads,
you will see that the irrelevant partition is not accessed.
Gert-Jan
Itchy Brother wrote:
> I'm using SQL Server 2000. I have two partitioned tables, Result1 and
> Result2, and a partitioned view ResultView. This query is correctly
> optimized to use Result1:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> This query scans both tables:
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> This means that I cannot take advantage of partitioning unless all queries
> use constants for their predicates?!!! That means the entire application
> would have to be build around dynamic SQL instead of simple stored procedure
> parameters. Can this be true?
> Here are the execution plans:
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = 1
> StmtText
> -----
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Index
> Scan(OBJECT:([Toggle].[dbo].[Result1].[IX_Result1_TestID]))
> DECLARE @.myid int
> SET @.myid = 1
> SELECT COUNT(*) FROM dbo.ResultView
> where ModelInterfaceID = @.myid
> StmtText
>
> -------
> |--Compute Scalar(DEFINE:([Expr1009]=Convert([globalagg1011])))
> |--Stream Aggregate(DEFINE:([globalagg1011]=SUM([partialagg1010])))
> |--Concatenation
> |--Parallelism(Gather Streams)
> | |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> | |--Filter(WHERE:(STARTUP EXPR([@.myid]=1)))
> | |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result1].[PK_Result1]),
> SEEK:([Result1].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:([partialagg1010]=Count(*)))
> |--Filter(WHERE:(STARTUP EXPR([@.myid]=2)))
> |--Index
> Seek(OBJECT:([Toggle].[dbo].[Result2].[PK_Result2]),
> SEEK:([Result2].[ModelInterfaceID]=[@.myid]) ORDERED FORWARD)
> Thanks,
> IB