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!

Cant add fields to table in EM - Odd SP3a prob?

Ok, I think this is a SP3a issue but I wanted to know if anyone else had this same problem.

I am trying to add fields to an existing table but everytime I do, I get a message saying:

'Races' table
- Unable to create index 'IX_Races2'.
ODBC error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state

I have narrowed it down to this. I can add the field if I add it to the bottom of the field list AND make it allow NULL. BUT...if I try to move the column up to another position (no, I don't need it in that position I know), it throws the error.

Also, if I add the field to the bottom of the field list, then try take off the NULL and fill in a default value, it still throws me this error. Someone tell me that this is just a SP3a issue. I have done this kind of thing before and never ran across this problem before.

Thanks,
GregHmm...ok. I have really narrowed it down now. The version that I am running on my server is 8.00.859. The version I have on my development machine is 8.00.760.

I detached the db from my server, attached it on my box, tried the same thing as above in my first post, and I was able to do it without a problem.

Anyone else experience this?

Greg

Cant add bit column to unique index

This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.

Can anyone shed any light on the problem?

Thanks.

You can not create index on BIT data type and that's the reason you can not see column.|||

Where can I find documentation on that? Why is that restriction in place?

|||

JigneshP, you definitely can create a unique index using bit fields. I was able to do it via TSQL in Query Analyzer. Here's the sql I used:

DROP INDEX [dbo].[Material].IX_MaterialCREATE UNIQUE INDEX [IX_Material]ON [dbo].[Material] ([MaterialID], [MyBitField])ON [PRIMARY]GO

The bit field is MyBitField. I then verified the index works by inserting data that duplicated another row except for the bit field.

I'm still looking for someone to tell me why I can't add the bit column to an index via Enterprise Manager.

|||

I found this link (http://sqlserver2000.databases.aspfaq.com/can-i-create-an-index-on-a-bit-column.html) that shows how to do it via Enterprise Manager. You have to do it from the Tasks menu / Manage Index.

|||Oh Thanks ZLA. Sorry about that.

Cannot retrieve data in utf-8 from php

Hi.

I have a IIS server with php3 installed. I have SQL server database
and data stored in unicode format (nvarchar fields). In header of my
php I have the meta: <meta HTTP-EQUIV="content-type"
CONTENT="text/html; charset=UTF-8">.

I have the following problem:

- I trie to do a query using ODBC (version 3.525.1022.0) and SQL
Server odbc driver (version 200.85.1022.00). The query doesn't
retrieve information in utf format and accents and extra characters
aren't showed correctly.

- I trie to di the query usin native OLE DB. I use mssql funtions and
the problem is the same.

If I install php4 and I use "new
COM("ADODB.Connection",NULL,CP_UTF8);", then it works correctly.

Anybody knows if its posible to retrieve data from sql-server in utf-8
format using php3?

I see in other postings that odbc driver only accept utf-8 in version
3.7 or higher? Is it true? If is true, where I can download odbc
driver 3.7?

Thank's in advance.
Miki.Miki (miquelpl2@.hotmail.com) writes:
> I see in other postings that odbc driver only accept utf-8 in version
> 3.7 or higher? Is it true? If is true, where I can download odbc
> driver 3.7?

First, as far as I know, the ODBC driver for SQL Server does not handle
UTF-8 at all, since SQL Server does not support UTF-8, but stores
all Unicode data as UCS-2. See http://support.microsoft.com/?id=232580.

ODBC is part of the MDAC, and with any recently new Windows machine,
you have an MDAC version which includes ODBC 3.7 or later. If you have
some old NT4 platform, you may have to look for something news.

Go to http://www.microsoft.com/downloads/...?displaylang=en,
and pick MDAC. You will have to check the versions that they support
your OS.

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

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