Showing posts with label field. Show all posts
Showing posts with label field. Show all posts

Tuesday, March 27, 2012

Cant Cascade Delete

I have a Sql Server 2005 table with 3 fields, an ID field (primary key), a parent ID field, and Name. The parent ID references the ID field (foreign to primary - many to one) within the same table so that records can reference their parent. I would like to place a cascade delete on the ID field so that when the primary ID is removed it will automatically remove all those records with a parent ID that match. Sql server does not allow me to establish this cascade delete.

I was considering a trigger instead but only know how tio use the AFTER paramter and not an alternative.

Thanks

Hello my friend,

I see that you have a tree-like table. You have records that have a parent record, that can have a parent record that can have a parent record in the same table, and so on. The function at the bottom will help. You will need to change the table name from tblTree and the field names ParentID and PageID to whatever you have called them.

Anyway, the function will return a list of all child records. For example, if PageID 2 had childs 5 and 8, and 5 had 3 childs 67, 68, and 70, the resultset would look like the following: -

2
5
67
68
70
8

All you need to do is run a delete against this returned set as follows, which deletes number 2 and all of its children: -

DELETE FROM tblTree WHERE PageID IN (SELECT PageID FROM dbo.fnGetPages(2))

The function is as follows: -

CREATE FUNCTION dbo.fnGetPages
(
@.PageID AS INT
)

RETURNS @.ChildPageIDs TABLE(PageID INT)

AS

BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM tblTree WHERE ParentID = @.PageID

DECLARE @.TempChildPageIDs TABLE(PageID INT)
INSERT INTO @.TempChildPageIDs (PageID)
SELECT PageID FROM @.ChildPageIDs ORDER BY PageID

DECLARE @.ChildPageID AS INT
SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)

WHILE (@.ChildPageID IS NOT NULL)
BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM dbo.fnGetPages(@.ChildPageID)
DELETE FROM @.TempChildPageIDs WHERE PageID = @.ChildPageID

SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)
END
RETURN
END

If you have any questions on this, please let me know.

Kind regards

Scotty

|||Excellent stuff. Thank you!

Monday, March 19, 2012

CanShrink=True doesnt work

I have added to detail rows to my table and set the canshrink property to
them all to be =True.
I only use one field in each row, and if there is no value in that field you
would think it would shrink the whole row to nothing. It keeps the row
there and takes up the space of the whole row.
Has anyone else seen this or have any idea why CanShrink does not work.?Did you ever find a solution to this? I have the same problem. I have a
matrix control that has the potential to have four rows per group. However,
depending on the group I only want to show some rows and not others. I'm
able to hide the values in the rows, but I can't hide the row.
Thanks!
Ian

Thursday, March 8, 2012

Cannot update replication once live - have to recreate!

I have set up a transactional replication from SQL2000 (W2K) to SQL2005
(W2K3). Once it's running, if I add a new field to the publisher, it doesn't
appear in the subscriber tables. I have tried everything including
re-inititalising the job, stopping and starting agents - nothing works. All I
am left with is deleting then recreating the entire replication job which is
time consuming and annoying.
Somebody out there must have the answer to me inadequacy!
Thanks in advance,
Will, Leeds UK
Will,
I need to understand how you did this. How did you add the new field? Did
you use sp_repladdcolumn?
Also, when you say reinitializing the job I'm not sure what you mean. Do you
mean restart the snapshot agent? If so, this is not reinitialization. If the
subscription is marked for reinitialization, this is something different.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi, basically we replicate live databases onto a separate server for
reporting. Occasionally we modify the live database (add a new field say) and
need the replication job to reflect this change. At the moment, every time we
make a change we have to remove replication, delete the reporting database
copy, then re-add replication. The live databases are on SQL 2000 and
reporting copies on SQL2005.
I haven't tried sp_repladdcolumn yet. I have just been using various actions
from SQL Management Studio.
I am a little confused as to the 'official method' to achieve what I want!
What I do know is that our current method works BUT is very tedious...
Thanks
Will
"Paul Ibison" wrote:

> Will,
> I need to understand how you did this. How did you add the new field? Did
> you use sp_repladdcolumn?
> Also, when you say reinitializing the job I'm not sure what you mean. Do you
> mean restart the snapshot agent? If so, this is not reinitialization. If the
> subscription is marked for reinitialization, this is something different.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

cannot update

Hi
we moved from 2000 to 2003 (version 9)
I do Open Table to see all rows
when I try to change a value say 2 to 3 in int field it pops up dialog and
say
like driver error string or binary data will be truncated
but it is an integer field and the values are like 2 and 3!
is it isolation level related perhaps
what setting is it
we just installed it so it must be default
Sincerely
GSL
Hi
"info@.sites4agents.com" wrote:

> Hi
> we moved from 2000 to 2003 (version 9)
> I do Open Table to see all rows
> when I try to change a value say 2 to 3 in int field it pops up dialog and
> say
> like driver error string or binary data will be truncated
> but it is an integer field and the values are like 2 and 3!
> is it isolation level related perhaps
> what setting is it
> we just installed it so it must be default
> Sincerely
> GSL
This should not be an issue under normal circumstances. Have you service
packed your version of SQL 2005? Are you trying this on a client or the
server itself?
Are there any triggers on the table?
John
|||John
Thanks
No SPs as far as I know (how will I know?)
the table does have text column but then in sql2000 it was updatable
manually
No triggers
I do it on the server
also! when I do it in Query like regular update statement it works!
(so maybe it forces good habits nevertheless I am curious why it happens)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25EACFD4-9897-4D6E-AEA6-E7936186542A@.microsoft.com...
> Hi
> "info@.sites4agents.com" wrote:
>
> This should not be an issue under normal circumstances. Have you service
> packed your version of SQL 2005? Are you trying this on a client or the
> server itself?
> Are there any triggers on the table?
> John
>
|||Hi
"info@.sites4agents.com" wrote:

> John
> Thanks
> No SPs as far as I know (how will I know?)
Select @.@.VERSION will give you the version number also SELECT
SERVERPROPTERY('ProductLevel ') will tell if a service pack has been applied
or is still at the original release (RTM). See
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html for more.

> the table does have text column but then in sql2000 it was updatable
> manually
> No triggers
> I do it on the server
> also! when I do it in Query like regular update statement it works!
> (so maybe it forces good habits nevertheless I am curious why it happens)
The error message you have seems to be a SQL Server error message, if you
have the error number it would be useful. You could post DDL and example data
http://www.aspfaq.com/etiquette.asp?id=5006 to help others re-create this.
Open table in Enterprise Manager was always quirky therefore it is not
something a lot of people will use in SQL 2005 because of that.
John

cannot update

Hi
we moved from 2000 to 2003 (version 9)
I do Open Table to see all rows
when I try to change a value say 2 to 3 in int field it pops up dialog and
say
like driver error string or binary data will be truncated
but it is an integer field and the values are like 2 and 3!
is it isolation level related perhaps
what setting is it
we just installed it so it must be default
Sincerely
GSLHi
"info@.sites4agents.com" wrote:

> Hi
> we moved from 2000 to 2003 (version 9)
> I do Open Table to see all rows
> when I try to change a value say 2 to 3 in int field it pops up dialog and
> say
> like driver error string or binary data will be truncated
> but it is an integer field and the values are like 2 and 3!
> is it isolation level related perhaps
> what setting is it
> we just installed it so it must be default
> Sincerely
> GSL
This should not be an issue under normal circumstances. Have you service
packed your version of SQL 2005? Are you trying this on a client or the
server itself?
Are there any triggers on the table?
John|||John
Thanks
No SPs as far as I know (how will I know?)
the table does have text column but then in sql2000 it was updatable
manually
No triggers
I do it on the server
also! when I do it in Query like regular update statement it works!
(so maybe it forces good habits nevertheless I am curious why it happens)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25EACFD4-9897-4D6E-AEA6-E7936186542A@.microsoft.com...
> Hi
> "info@.sites4agents.com" wrote:
>
> This should not be an issue under normal circumstances. Have you service
> packed your version of SQL 2005? Are you trying this on a client or the
> server itself?
> Are there any triggers on the table?
> John
>|||Hi
"info@.sites4agents.com" wrote:

> John
> Thanks
> No SPs as far as I know (how will I know?)
Select @.@.VERSION will give you the version number also SELECT
SERVERPROPTERY('ProductLevel ') will tell if a service pack has been applied
or is still at the original release (RTM). See
http://sqlserver2000.databases.aspf...-m-running.html for more.

> the table does have text column but then in sql2000 it was updatable
> manually
> No triggers
> I do it on the server
> also! when I do it in Query like regular update statement it works!
> (so maybe it forces good habits nevertheless I am curious why it happens)
The error message you have seems to be a SQL Server error message, if you
have the error number it would be useful. You could post DDL and example dat
a
http://www.aspfaq.com/etiquette.asp?id=5006 to help others re-create this.
Open table in Enterprise Manager was always quirky therefore it is not
something a lot of people will use in SQL 2005 because of that.
John

cannot update

Hi
we moved from 2000 to 2003 (version 9)
I do Open Table to see all rows
when I try to change a value say 2 to 3 in int field it pops up dialog and
say
like driver error string or binary data will be truncated
but it is an integer field and the values are like 2 and 3!
is it isolation level related perhaps
what setting is it
we just installed it so it must be default
Sincerely
GSLHi
"info@.sites4agents.com" wrote:
> Hi
> we moved from 2000 to 2003 (version 9)
> I do Open Table to see all rows
> when I try to change a value say 2 to 3 in int field it pops up dialog and
> say
> like driver error string or binary data will be truncated
> but it is an integer field and the values are like 2 and 3!
> is it isolation level related perhaps
> what setting is it
> we just installed it so it must be default
> Sincerely
> GSL
This should not be an issue under normal circumstances. Have you service
packed your version of SQL 2005? Are you trying this on a client or the
server itself?
Are there any triggers on the table?
John|||John
Thanks
No SPs as far as I know (how will I know?)
the table does have text column but then in sql2000 it was updatable
manually
No triggers
I do it on the server
also! when I do it in Query like regular update statement it works!
(so maybe it forces good habits nevertheless I am curious why it happens)
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25EACFD4-9897-4D6E-AEA6-E7936186542A@.microsoft.com...
> Hi
> "info@.sites4agents.com" wrote:
>> Hi
>> we moved from 2000 to 2003 (version 9)
>> I do Open Table to see all rows
>> when I try to change a value say 2 to 3 in int field it pops up dialog
>> and
>> say
>> like driver error string or binary data will be truncated
>> but it is an integer field and the values are like 2 and 3!
>> is it isolation level related perhaps
>> what setting is it
>> we just installed it so it must be default
>> Sincerely
>> GSL
> This should not be an issue under normal circumstances. Have you service
> packed your version of SQL 2005? Are you trying this on a client or the
> server itself?
> Are there any triggers on the table?
> John
>|||Hi
"info@.sites4agents.com" wrote:
> John
> Thanks
> No SPs as far as I know (how will I know?)
Select @.@.VERSION will give you the version number also SELECT
SERVERPROPTERY('ProductLevel ') will tell if a service pack has been applied
or is still at the original release (RTM). See
http://sqlserver2000.databases.aspfaq.com/how-do-i-know-which-version-of-sql-server-i-m-running.html for more.
> the table does have text column but then in sql2000 it was updatable
> manually
> No triggers
> I do it on the server
> also! when I do it in Query like regular update statement it works!
> (so maybe it forces good habits nevertheless I am curious why it happens)
The error message you have seems to be a SQL Server error message, if you
have the error number it would be useful. You could post DDL and example data
http://www.aspfaq.com/etiquette.asp?id=5006 to help others re-create this.
Open table in Enterprise Manager was always quirky therefore it is not
something a lot of people will use in SQL 2005 because of that.
John