updating text datatype: sp3

Showing posts with label sp3. Show all posts

Wednesday, March 28, 2012

Problem with big transfer

Problem with big data transfer

Hi,
I have MSSQl 2000 , SP3 with WIn2000
when I transfer from one table to another, 115852 records, le server hangs.
I tried with 70000 records and it worked. even with 90000 records.
With more than 90000 records, the server hangs.
Any idea ? It might be a bug in MSSQLsvr ? any known bugs ?
Thanks for your help
Olivier
How are you transferring the data?
I have used BCP, DTS and T-SQL to "transfer" data from one location to
another. I have successfully transferred more than 10x the data you are
talking about. I used T-SQL for table-table transfer (within the same
server) and DTS for server-server transfer.
Keith
"oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
news:uNrXWVySFHA.3176@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I have MSSQl 2000 , SP3 with WIn2000
> when I transfer from one table to another, 115852 records, le server
> hangs.
> I tried with 70000 records and it worked. even with 90000 records.
> With more than 90000 records, the server hangs.
> Any idea ? It might be a bug in MSSQLsvr ? any known bugs ?
> Thanks for your help
> Olivier
>
>
|||I am transfering using UPDATE Table SET (SELECT ...)
from one table to another table within the same server
That s strange
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eC2qgcySFHA.2324@.TK2MSFTNGP10.phx.gbl...
> How are you transferring the data?
> I have used BCP, DTS and T-SQL to "transfer" data from one location to
> another. I have successfully transferred more than 10x the data you are
> talking about. I used T-SQL for table-table transfer (within the same
> server) and DTS for server-server transfer.
> --
> Keith
>
> "oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
> news:uNrXWVySFHA.3176@.TK2MSFTNGP09.phx.gbl...
>
|||When the "transfer" (UPDATE) statement fails what error do you receive? I
am guessing that the drive with your data or log file on it is full. Do you
receive an error along the lines of "cannot allocate space...full?"
Keith
"oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
news:uM2vJkySFHA.2128@.TK2MSFTNGP14.phx.gbl...
>I am transfering using UPDATE Table SET (SELECT ...)
> from one table to another table within the same server
> That s strange
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eC2qgcySFHA.2324@.TK2MSFTNGP10.phx.gbl...
>
|||First check your source table .... can you retrieve more than 90000 row
from source table ?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eX91hsySFHA.584@.TK2MSFTNGP15.phx.gbl...
> When the "transfer" (UPDATE) statement fails what error do you receive? I
> am guessing that the drive with your data or log file on it is full. Do
you[vbcol=seagreen]
> receive an error along the lines of "cannot allocate space...full?"
> --
> Keith
>
> "oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
> news:uM2vJkySFHA.2128@.TK2MSFTNGP14.phx.gbl...
are
>
|||yes, i run the SQL query SELECT top 90000 and it worked.
i even put the data in two different tables and i managed to transfer them
but when i try to transfer all in one go, it hangs
one of my colleague find that
http://support.microsoft.com/?kbid=892205
can it be the cause ?
"John" <joh@.mailcity.com> wrote in message
news:%23crW6ozSFHA.3980@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> First check your source table .... can you retrieve more than 90000 row
> from source table ?
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eX91hsySFHA.584@.TK2MSFTNGP15.phx.gbl...
I[vbcol=seagreen]
> you
to[vbcol=seagreen]
> are
same[vbcol=seagreen]
server
>
|||SELECT top 90000 * from tablename order by 1 desc
is it working ? I am just woundering like your table is alright or not...
"oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
news:erAp#B0SFHA.3156@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> yes, i run the SQL query SELECT top 90000 and it worked.
> i even put the data in two different tables and i managed to transfer them
> but when i try to transfer all in one go, it hangs
> one of my colleague find that
> http://support.microsoft.com/?kbid=892205
>
> can it be the cause ?
>
>
>
>
> "John" <joh@.mailcity.com> wrote in message
> news:%23crW6ozSFHA.3980@.TK2MSFTNGP12.phx.gbl...
row[vbcol=seagreen]
receive?[vbcol=seagreen]
> I
Do[vbcol=seagreen]
> to
you[vbcol=seagreen]
> same
> server
records.
>
|||this command is working
My server is using Hyper threading, is this ring a bell ?
"John" <joh@.mailcity.com> wrote in message
news:etGP8E0SFHA.3184@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> SELECT top 90000 * from tablename order by 1 desc
> is it working ? I am just woundering like your table is alright or not...
>
> "oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
> news:erAp#B0SFHA.3156@.TK2MSFTNGP15.phx.gbl...
them[vbcol=seagreen]
> row
> receive?
> Do
location
> you
> records.
>
|||Hi
Probably not. Does sp_who2 show increasing CPU and IO whilst the process has
"hung"?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"oLiVieR CheNeSoN" <ocheneson@.hotmail.com> wrote in message
news:%237TiTK0SFHA.612@.TK2MSFTNGP12.phx.gbl...
> this command is working
> My server is using Hyper threading, is this ring a bell ?
>
>
> "John" <joh@.mailcity.com> wrote in message
> news:etGP8E0SFHA.3184@.TK2MSFTNGP14.phx.gbl...
> them
> location
>

Monday, March 12, 2012

Problem when transfering an important number of records.

Monday, February 20, 2012

Problem using DRI with CASCADE action.

(SQL Server 2000, SP3)
Hello all!
I am trying to use ERwin to create a new schema. In the past, because of limitations with
SQL Server's declarative referential integrity (DRI), I typically did all my RI through
triggers. I thought I'd take the plunge and try using DRI with a
Parent-Cascade/Child-Restrict paradigm.
When I generate the script, I get the following:
CREATE TABLE amContainer (
ContainerId int IDENTITY(1, 1),
ParentContainerId int NULL,
TypeContainerId int NOT NULL,
CONSTRAINT XPKamContainer
PRIMARY KEY (ContainerId)
)
go
CREATE TABLE amTypeContainer (
TypeContainerId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
CONSTRAINT XPKamTypeContainer
PRIMARY KEY (TypeContainerId)
)
go
ALTER TABLE amContainer
ADD CONSTRAINT R_3
FOREIGN KEY (TypeContainerId)
REFERENCES amTypeContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amContainer
ADD CONSTRAINT R_1
FOREIGN KEY (ParentContainerId)
REFERENCES amContainer
ON UPDATE CASCADE
go
I'm getting an error on R_1 (that last ALTER TABLE command):
Server: Msg 1785, Level 16, State 1, Line 3
Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' may cause cycles or
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify
other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 3
Could not create constraint. See previous errors.
Is this not possible with DRI? Do I need to resort to triggers to apply what I want?
Thanks for any help you can provide!
John PetersonThis is a multi-part message in MIME format.
--=_NextPart_000_015B_01C3618E.3E6B8530
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You cannot use DRI for cascades on a self-referencing table. For this, =you will need a trigger.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:etENJ7aYDHA.2572@.TK2MSFTNGP09.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I am trying to use ERwin to create a new schema. In the past, because =of limitations with
SQL Server's declarative referential integrity (DRI), I typically did =all my RI through
triggers. I thought I'd take the plunge and try using DRI with a
Parent-Cascade/Child-Restrict paradigm.
When I generate the script, I get the following:
CREATE TABLE amContainer (
ContainerId int IDENTITY(1, 1),
ParentContainerId int NULL,
TypeContainerId int NOT NULL,
CONSTRAINT XPKamContainer
PRIMARY KEY (ContainerId)
)
go
CREATE TABLE amTypeContainer (
TypeContainerId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
CONSTRAINT XPKamTypeContainer
PRIMARY KEY (TypeContainerId)
)
go
ALTER TABLE amContainer
ADD CONSTRAINT R_3
FOREIGN KEY (TypeContainerId)
REFERENCES amTypeContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amContainer
ADD CONSTRAINT R_1
FOREIGN KEY (ParentContainerId)
REFERENCES amContainer
ON UPDATE CASCADE
go
I'm getting an error on R_1 (that last ALTER TABLE command):
Server: Msg 1785, Level 16, State 1, Line 3
Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' may =cause cycles or
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO =ACTION, or modify
other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 3
Could not create constraint. See previous errors.
Is this not possible with DRI? Do I need to resort to triggers to apply =what I want?
Thanks for any help you can provide!
John Peterson
--=_NextPart_000_015B_01C3618E.3E6B8530
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

You cannot use DRI for cascades on a self-referencing table. For this, you will need a =trigger.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:etENJ7aYDHA.2572=@.TK2MSFTNGP09.phx.gbl...(SQL Server 2000, SP3)Hello all!I am trying to use ERwin to =create a new schema. In the past, because of limitations withSQL =Server's declarative referential integrity (DRI), I typically did all my RI throughtriggers. I thought I'd take the plunge and try using =DRI with aParent-Cascade/Child-Restrict paradigm.When I generate the =script, I get the following:CREATE TABLE amContainer ( ContainerId int IDENTITY(1, 1), ParentContainerId int NULL, TypeContainerId int NOT NULL, CONSTRAINT XPKamContainer &=nbsp; PRIMARY KEY (ContainerId))goCREATE TABLE amTypeContainer =( TypeContainerId int IDENTITY(1, 1), Name &nb=sp; varchar(255) NOT NULL, =CONSTRAINT XPKamTypeContainer &nb=sp; PRIMARY KEY (TypeContainerId))goALTER TABLE amContainer ADD CONSTRAINT R_3 = FOREIGN KEY (TypeContainerId) &nbs=p; =; REFERENCES amTypeContainer = &=nbsp; ON DELETE CASCADE &n=bsp; &nb=sp; ON UPDATE CASCADEgoALTER TABLE amContainer ADD CONSTRAINT R_1 = FOREIGN KEY (ParentContainerId) &n=bsp; &nb=sp; REFERENCES amContainer &nbs=p; =; ON UPDATE CASCADEgoI'm getting an error on R_1 (that =last ALTER TABLE command):Server: Msg 1785, Level 16, State 1, Line 3Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' may =cause cycles ormultiple cascade paths. Specify ON DELETE NO ACTION or ON =UPDATE NO ACTION, or modifyother FOREIGN KEY constraints.Server: Msg 1750, =Level 16, State 1, Line 3Could not create constraint. See previous errors.Is this not possible with DRI? Do I need to resort =to triggers to apply what I want?Thanks for any help you can provide!John Peterson

--=_NextPart_000_015B_01C3618E.3E6B8530--|||This is a multi-part message in MIME format.
--=_NextPart_000_002D_01C36176.F30B8700
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_002E_01C36176.F30B8700"
--=_NextPart_001_002E_01C36176.F30B8700
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Tom, I'm still having trouble with other aspects of DRI. Please see the =attached DDL -- this time it's with my R_8 relationship. I don't quite ="grok" why this is problematic. (It's not as clear-cut as a =self-referencing table.)
Any additional help you can provide would be greatly appreciated!
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:%23BK4wDbYDHA.440@.tk2msftngp13.phx.gbl...
Thanks, Tom! Maybe I can "mix and match" and only use triggers where =necessary.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OPg2l$aYDHA.1640@.TK2MSFTNGP10.phx.gbl...
You cannot use DRI for cascades on a self-referencing table. For =this, you will need a trigger.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:etENJ7aYDHA.2572@.TK2MSFTNGP09.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I am trying to use ERwin to create a new schema. In the past, =because of limitations with
SQL Server's declarative referential integrity (DRI), I typically =did all my RI through
triggers. I thought I'd take the plunge and try using DRI with a
Parent-Cascade/Child-Restrict paradigm.
When I generate the script, I get the following:
CREATE TABLE amContainer (
ContainerId int IDENTITY(1, 1),
ParentContainerId int NULL,
TypeContainerId int NOT NULL,
CONSTRAINT XPKamContainer
PRIMARY KEY (ContainerId)
)
go
CREATE TABLE amTypeContainer (
TypeContainerId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
CONSTRAINT XPKamTypeContainer
PRIMARY KEY (TypeContainerId)
)
go
ALTER TABLE amContainer
ADD CONSTRAINT R_3
FOREIGN KEY (TypeContainerId)
REFERENCES amTypeContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amContainer
ADD CONSTRAINT R_1
FOREIGN KEY (ParentContainerId)
REFERENCES amContainer
ON UPDATE CASCADE
go
I'm getting an error on R_1 (that last ALTER TABLE command):
Server: Msg 1785, Level 16, State 1, Line 3
Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' may =cause cycles or
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO =ACTION, or modify
other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 3
Could not create constraint. See previous errors.
Is this not possible with DRI? Do I need to resort to triggers to =apply what I want?
Thanks for any help you can provide!
John Peterson
--=_NextPart_001_002E_01C36176.F30B8700
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Tom, I'm still having trouble with =other aspects of DRI. Please see the attached DDL -- this time it's with my R_8 relationship. I don't quite "grok" why this is problematic. =(It's not as clear-cut as a self-referencing table.)
Any additional help you can provide =would be greatly appreciated!
John Peterson
"John Peterson" wrote in =message news:%23BK4wDbYDHA.4=40@.tk2msftngp13.phx.gbl...
Thanks, Tom! Maybe I can ="mix and match" and only use triggers where necessary.
"Tom Moreau" = wrote in message news:OPg2l$aYDHA.1640=@.TK2MSFTNGP10.phx.gbl...
You cannot use DRI for cascades on =a self-referencing table. For this, you will need a trigger.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"John Peterson" wrote in =message news:etENJ7aYDHA.2572=@.TK2MSFTNGP09.phx.gbl...(SQL Server 2000, SP3)Hello all!I am trying to use ERwin =to create a new schema. In the past, because of limitations =withSQL Server's declarative referential integrity (DRI), I typically did =all my RI throughtriggers. I thought I'd take the plunge and try =using DRI with aParent-Cascade/Child-Restrict paradigm.When I =generate the script, I get the following:CREATE TABLE amContainer ( ContainerId =int IDENTITY(1, 1), ParentContainerId int NULL, TypeContainerId int NOT NULL, CONSTRAINT =XPKamContainer &=nbsp; PRIMARY KEY (ContainerId))goCREATE TABLE =amTypeContainer ( TypeContainerId int IDENTITY(1, 1), =Name &nb=sp; varchar(255) NOT NULL, =CONSTRAINT =XPKamTypeContainer &nb=sp; PRIMARY KEY (TypeContainerId))goALTER TABLE amContainer ADD CONSTRAINT =R_3 = FOREIGN KEY =(TypeContainerId) &nbs=p; =; REFERENCES =amTypeContainer = &=nbsp; ON DELETE =CASCADE &n=bsp; &nb=sp; ON UPDATE CASCADEgoALTER TABLE amContainer ADD CONSTRAINT =R_1 = FOREIGN KEY =(ParentContainerId) &n=bsp; &nb=sp; REFERENCES =amContainer &nbs=p; =; ON UPDATE CASCADEgoI'm getting an error on R_1 (that =last ALTER TABLE command):Server: Msg 1785, Level 16, State 1, =Line 3Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' =may cause cycles ormultiple cascade paths. Specify ON DELETE NO =ACTION or ON UPDATE NO ACTION, or modifyother FOREIGN KEY =constraints.Server: Msg 1750, Level 16, State 1, Line 3Could not create constraint. See =previous errors.Is this not possible with DRI? Do I need to =resort to triggers to apply what I want?Thanks for any help you can provide!John =Peterson

--=_NextPart_001_002E_01C36176.F30B8700--
--=_NextPart_000_002D_01C36176.F30B8700
Content-Type: text/plain;
name="Test.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="Test.sql"
CREATE TABLE amContainer (
ContainerId int IDENTITY(1, 1),
ParentContainerId int NULL,
TypeContainerId int NOT NULL,
CONSTRAINT XPKamContainer
PRIMARY KEY (ContainerId)
)
go
CREATE TABLE amMember (
MemberId int IDENTITY(1, 1),
ObjectId int NOT NULL,
ContainerId int NOT NULL,
TypeMemberAssociationId int NOT NULL,
CONSTRAINT XPKamMember
PRIMARY KEY (MemberId)
)
go
CREATE TABLE amObject (
ObjectId int IDENTITY(1, 1),
NativeObjectId uniqueidentifier NOT NULL,
TypeObjectId int NOT NULL,
CONSTRAINT XPKamObject
PRIMARY KEY (ObjectId)
)
go
CREATE TABLE amRights (
RightsId int IDENTITY(1, 1),
MemberId int NULL,
PartyObjectId int NULL,
Context varchar(255) NULL,
Rights varchar(255) NULL,
CONSTRAINT XPKamRights
PRIMARY KEY (RightsId)
)
go
CREATE TABLE amTypeContainer (
TypeContainerId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
CONSTRAINT XPKamTypeContainer
PRIMARY KEY (TypeContainerId)
)
go
CREATE TABLE amTypeMemberAssociation (
TypeMemberAssociationId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
Description varchar(255) NULL,
CONSTRAINT XPKamTypeMemberAssociation
PRIMARY KEY (TypeMemberAssociationId)
)
go
CREATE TABLE amTypeObject (
TypeObjectId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
TableSchema nvarchar(128) NULL,
TableName nvarchar(128) NULL,
TablePKColumn nvarchar(128) NULL,
CONSTRAINT XPKamTypeObject
PRIMARY KEY (TypeObjectId)
)
go
ALTER TABLE amContainer
ADD CONSTRAINT R_3
FOREIGN KEY (TypeContainerId)
REFERENCES amTypeContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
/*
ALTER TABLE amContainer
ADD CONSTRAINT R_1
FOREIGN KEY (ParentContainerId)
REFERENCES amContainer
ON UPDATE CASCADE
*/
go
ALTER TABLE amMember
ADD CONSTRAINT R_7
FOREIGN KEY (TypeMemberAssociationId)
REFERENCES amTypeMemberAssociation
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amMember
ADD CONSTRAINT R_6
FOREIGN KEY (ContainerId)
REFERENCES amContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amMember
ADD CONSTRAINT R_5
FOREIGN KEY (ObjectId)
REFERENCES amObject
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amObject
ADD CONSTRAINT R_4
FOREIGN KEY (TypeObjectId)
REFERENCES amTypeObject
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amRights
ADD CONSTRAINT R_9
FOREIGN KEY (PartyObjectId)
REFERENCES amObject
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amRights
ADD CONSTRAINT R_8
FOREIGN KEY (MemberId)
REFERENCES amMember
ON DELETE CASCADE
ON UPDATE CASCADE
go
--=_NextPart_000_002D_01C36176.F30B8700--|||This is a multi-part message in MIME format.
--=_NextPart_000_01ED_01C36198.39729640
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
This type of problem is not limited to a self-referencing table. =Rather, it occurs any time there is a circular dependency. Already, =there R_9 establishes a relationship between amRights and amObject. R_5 =establishes the relationship between amObject and amMember. By adding =R_8 - between amRights and amMember - you have completed the circle.
The way to see this is to look at the ER diagram. It looks like you =should remove R_9 and install R_8.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:uFC7lGbYDHA.2448@.TK2MSFTNGP09.phx.gbl...
Tom, I'm still having trouble with other aspects of DRI. Please see the =attached DDL -- this time it's with my R_8 relationship. I don't quite ="grok" why this is problematic. (It's not as clear-cut as a =self-referencing table.)
Any additional help you can provide would be greatly appreciated!
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:%23BK4wDbYDHA.440@.tk2msftngp13.phx.gbl...
Thanks, Tom! Maybe I can "mix and match" and only use triggers where =necessary.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OPg2l$aYDHA.1640@.TK2MSFTNGP10.phx.gbl...
You cannot use DRI for cascades on a self-referencing table. For =this, you will need a trigger.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:etENJ7aYDHA.2572@.TK2MSFTNGP09.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I am trying to use ERwin to create a new schema. In the past, =because of limitations with
SQL Server's declarative referential integrity (DRI), I typically =did all my RI through
triggers. I thought I'd take the plunge and try using DRI with a
Parent-Cascade/Child-Restrict paradigm.
When I generate the script, I get the following:
CREATE TABLE amContainer (
ContainerId int IDENTITY(1, 1),
ParentContainerId int NULL,
TypeContainerId int NOT NULL,
CONSTRAINT XPKamContainer
PRIMARY KEY (ContainerId)
)
go
CREATE TABLE amTypeContainer (
TypeContainerId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
CONSTRAINT XPKamTypeContainer
PRIMARY KEY (TypeContainerId)
)
go
ALTER TABLE amContainer
ADD CONSTRAINT R_3
FOREIGN KEY (TypeContainerId)
REFERENCES amTypeContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amContainer
ADD CONSTRAINT R_1
FOREIGN KEY (ParentContainerId)
REFERENCES amContainer
ON UPDATE CASCADE
go
I'm getting an error on R_1 (that last ALTER TABLE command):
Server: Msg 1785, Level 16, State 1, Line 3
Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' may =cause cycles or
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO =ACTION, or modify
other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 3
Could not create constraint. See previous errors.
Is this not possible with DRI? Do I need to resort to triggers to =apply what I want?
Thanks for any help you can provide!
John Peterson
--=_NextPart_000_01ED_01C36198.39729640
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

This type of problem is not limited to =a self-referencing table. Rather, it occurs any time there is a =circular dependency. Already, there R_9 establishes a relationship between =amRights and amObject. R_5 establishes the relationship between amObject =and amMember. By adding R_8 - between amRights and amMember - you have =completed the circle.
The way to see this is to look at the =ER diagram. It looks like you should remove R_9 and install =R_8.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:uFC7lGbYDHA.2448=@.TK2MSFTNGP09.phx.gbl...
Tom, I'm still having trouble with =other aspects of DRI. Please see the attached DDL -- this time it's with my R_8 relationship. I don't quite "grok" why this is problematic. =(It's not as clear-cut as a self-referencing table.)
Any additional help you can provide =would be greatly appreciated!
John Peterson
"John Peterson" wrote in =message news:%23BK4wDbYDHA.4=40@.tk2msftngp13.phx.gbl...
Thanks, Tom! Maybe I can ="mix and match" and only use triggers where necessary.
"Tom Moreau" = wrote in message news:OPg2l$aYDHA.1640=@.TK2MSFTNGP10.phx.gbl...
You cannot use DRI for cascades on =a self-referencing table. For this, you will need a trigger.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"John Peterson" wrote in =message news:etENJ7aYDHA.2572=@.TK2MSFTNGP09.phx.gbl...(SQL Server 2000, SP3)Hello all!I am trying to use ERwin =to create a new schema. In the past, because of limitations =withSQL Server's declarative referential integrity (DRI), I typically did =all my RI throughtriggers. I thought I'd take the plunge and try =using DRI with aParent-Cascade/Child-Restrict paradigm.When I =generate the script, I get the following:CREATE TABLE amContainer ( ContainerId =int IDENTITY(1, 1), ParentContainerId int NULL, TypeContainerId int NOT NULL, CONSTRAINT =XPKamContainer &=nbsp; PRIMARY KEY (ContainerId))goCREATE TABLE =amTypeContainer ( TypeContainerId int IDENTITY(1, 1), =Name &nb=sp; varchar(255) NOT NULL, =CONSTRAINT =XPKamTypeContainer &nb=sp; PRIMARY KEY (TypeContainerId))goALTER TABLE amContainer ADD CONSTRAINT =R_3 = FOREIGN KEY =(TypeContainerId) &nbs=p; =; REFERENCES =amTypeContainer = &=nbsp; ON DELETE =CASCADE &n=bsp; &nb=sp; ON UPDATE CASCADEgoALTER TABLE amContainer ADD CONSTRAINT =R_1 = FOREIGN KEY =(ParentContainerId) &n=bsp; &nb=sp; REFERENCES =amContainer &nbs=p; =; ON UPDATE CASCADEgoI'm getting an error on R_1 (that =last ALTER TABLE command):Server: Msg 1785, Level 16, State 1, =Line 3Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' =may cause cycles ormultiple cascade paths. Specify ON DELETE NO =ACTION or ON UPDATE NO ACTION, or modifyother FOREIGN KEY =constraints.Server: Msg 1750, Level 16, State 1, Line 3Could not create constraint. See =previous errors.Is this not possible with DRI? Do I need to =resort to triggers to apply what I want?Thanks for any help you can provide!John =Peterson

--=_NextPart_000_01ED_01C36198.39729640--|||This is a multi-part message in MIME format.
--=_NextPart_000_0294_01C3619F.7B4E4FD0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
In that script you posted, just don't run the R_9 and do run the R_8.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:OtZgG6bYDHA.212@.TK2MSFTNGP12.phx.gbl...
Tom, you're exactly right. I wanted the amRights table to either have a =relationship from the amMember table (which as a FK from amObject) or =the amObject table directly. See the attached ERwin diagram. Any =suggestions for how I might specify the RI in this case?
Thanks again for your time and your help!
John Peterson
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:%23FDYInbYDHA.1940@.TK2MSFTNGP10.phx.gbl...
This type of problem is not limited to a self-referencing table. =Rather, it occurs any time there is a circular dependency. Already, =there R_9 establishes a relationship between amRights and amObject. R_5 =establishes the relationship between amObject and amMember. By adding =R_8 - between amRights and amMember - you have completed the circle.
The way to see this is to look at the ER diagram. It looks like you =should remove R_9 and install R_8.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:uFC7lGbYDHA.2448@.TK2MSFTNGP09.phx.gbl...
Tom, I'm still having trouble with other aspects of DRI. Please see =the attached DDL -- this time it's with my R_8 relationship. I don't =quite "grok" why this is problematic. (It's not as clear-cut as a =self-referencing table.)
Any additional help you can provide would be greatly appreciated!
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:%23BK4wDbYDHA.440@.tk2msftngp13.phx.gbl...
Thanks, Tom! Maybe I can "mix and match" and only use triggers =where necessary.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OPg2l$aYDHA.1640@.TK2MSFTNGP10.phx.gbl...
You cannot use DRI for cascades on a self-referencing table. For =this, you will need a trigger.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:etENJ7aYDHA.2572@.TK2MSFTNGP09.phx.gbl...
(SQL Server 2000, SP3)
Hello all!
I am trying to use ERwin to create a new schema. In the past, =because of limitations with
SQL Server's declarative referential integrity (DRI), I typically =did all my RI through
triggers. I thought I'd take the plunge and try using DRI with a
Parent-Cascade/Child-Restrict paradigm.
When I generate the script, I get the following:
CREATE TABLE amContainer (
ContainerId int IDENTITY(1, 1),
ParentContainerId int NULL,
TypeContainerId int NOT NULL,
CONSTRAINT XPKamContainer
PRIMARY KEY (ContainerId)
)
go
CREATE TABLE amTypeContainer (
TypeContainerId int IDENTITY(1, 1),
Name varchar(255) NOT NULL,
CONSTRAINT XPKamTypeContainer
PRIMARY KEY (TypeContainerId)
)
go
ALTER TABLE amContainer
ADD CONSTRAINT R_3
FOREIGN KEY (TypeContainerId)
REFERENCES amTypeContainer
ON DELETE CASCADE
ON UPDATE CASCADE
go
ALTER TABLE amContainer
ADD CONSTRAINT R_1
FOREIGN KEY (ParentContainerId)
REFERENCES amContainer
ON UPDATE CASCADE
go
I'm getting an error on R_1 (that last ALTER TABLE command):
Server: Msg 1785, Level 16, State 1, Line 3
Introducing FOREIGN KEY constraint 'R_1' on table 'amContainer' =may cause cycles or
multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE =NO ACTION, or modify
other FOREIGN KEY constraints.
Server: Msg 1750, Level 16, State 1, Line 3
Could not create constraint. See previous errors.
Is this not possible with DRI? Do I need to resort to triggers to =apply what I want?
Thanks for any help you can provide!
John Peterson
--=_NextPart_000_0294_01C3619F.7B4E4FD0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

In that script you posted, just don't =run the R_9 and do run the R_8.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:OtZgG6bYDHA.212@.T=K2MSFTNGP12.phx.gbl...
Tom, you're exactly right. I =wanted the amRights table to either have a relationship from the amMember table =(which as a FK from amObject) or the amObject table directly. See the attached =ERwin diagram. Any suggestions for how I might specify the RI in this case?
Thanks again for your time and your help!
John Peterson
"Tom Moreau" = wrote in message news:%23FDYInbYDHA.=1940@.TK2MSFTNGP10.phx.gbl...
This type of problem is not limited =to a self-referencing table. Rather, it occurs any time there is a =circular dependency. Already, there R_9 establishes a relationship =between amRights and amObject. R_5 establishes the relationship between =amObject and amMember. By adding R_8 - between amRights and amMember - =you have completed the circle.

The way to see this is to look at =the ER diagram. It looks like you should remove R_9 and install R_8.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"John Peterson" wrote in =message news:uFC7lGbYDHA.2448=@.TK2MSFTNGP09.phx.gbl...
Tom, I'm still having trouble with =other aspects of DRI. Please see the attached DDL -- this time it's =with my R_8 relationship. I don't quite "grok" why this is =problematic. (It's not as clear-cut as a self-referencing table.)

Any additional help you can =provide would be greatly appreciated!

John Peterson

"John Peterson" wrote in =message news:%23BK4wDbYDHA.4=40@.tk2msftngp13.phx.gbl...
Thanks, Tom! Maybe I can ="mix and match" and only use triggers where necessary.
"Tom Moreau" = wrote in message news:OPg2l$aYDHA.1640=@.TK2MSFTNGP10.phx.gbl...
You cannot use DRI for cascades =on a self-referencing table. For this, you will need a trigger.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, =SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"John Peterson" wrote =in message news:etENJ7aYDHA.2572=@.TK2MSFTNGP09.phx.gbl...(SQL Server 2000, SP3)Hello all!I am trying to use =ERwin to create a new schema. In the past, because of limitations =withSQL Server's declarative referential integrity (DRI), I typically did =all my RI throughtriggers. I thought I'd take the plunge and =try using DRI with aParent-Cascade/Child-Restrict paradigm.When =I generate the script, I get the following:CREATE TABLE =amContainer ( ContainerId =int IDENTITY(1, 1), ParentContainerId int NULL, TypeContainerId int NOT NULL, CONSTRAINT =XPKamContainer &=nbsp; PRIMARY KEY (ContainerId))goCREATE TABLE =amTypeContainer ( TypeContainerId int IDENTITY(1, 1), =Name &nb=sp; varchar(255) NOT NULL, =CONSTRAINT =XPKamTypeContainer &nb=sp; PRIMARY KEY (TypeContainerId))goALTER TABLE amContainer ADD CONSTRAINT = =R_3 = FOREIGN KEY =(TypeContainerId) &nbs=p; =; REFERENCES =amTypeContainer = &=nbsp; ON DELETE =CASCADE &n=bsp; &nb=sp; ON UPDATE CASCADEgoALTER TABLE amContainer ADD CONSTRAINT = =R_1 = FOREIGN KEY =(ParentContainerId) &n=bsp; &nb=sp; REFERENCES =amContainer &nbs=p; =; ON UPDATE CASCADEgoI'm getting an error on R_1 =(that last ALTER TABLE command):Server: Msg 1785, Level 16, State 1, =Line 3Introducing FOREIGN KEY constraint 'R_1' on table ='amContainer' may cause cycles ormultiple cascade paths. Specify ON DELETE NO =ACTION or ON UPDATE NO ACTION, or modifyother FOREIGN KEY constraints.Server: Msg 1750, Level 16, State 1, Line =3Could not create constraint. See previous errors.Is this not =possible with DRI? Do I need to resort to triggers to apply what I want?Thanks for any help you can provide!John =Peterson

--=_NextPart_000_0294_01C3619F.7B4E4FD0--

Wednesday, March 28, 2012

Problem with big transfer

Problem with big data transfer

Monday, March 12, 2012

Problem when transfering an important number of records.

Problem when transfering an important number of records.

Monday, February 20, 2012

Problem using DRI with CASCADE action.

updating text datatype

Blog Archive

About Me