Help with a recursive call

Hi all,

I have a user table and a user_hierarchy table. The hierarchy table list children of the parent users.

ie
create user_hierarchy (
int parent_userid,
int child_userid
)

each parent can have any number of children and each child can in turn have children. so for users 1,2,3,4 you would have::

Parent,Child
1,2
1,3
1,4
1,5
2,3
2,4
3,5

I need a way to get all the children and children's children from a user.
so for user 1, it would give me back 2,3,4,5
for user 2 it would give back 3,4,5 (3 and 4 are direct children) and 5 is a child of 3

I have tried this using Temp tables and Cursors with recursion. The cursors won't work with the recursion and the temp table would be most problematic because this is a web application and I would need to generate unique temp table names for each call. Additionally, I can't seem to find an elegant way to get the children's children without implementing some sort of cursor or stepping algorithm.

Any help would be most appreciated.

Thanks In Advance,

billYou can try something like this:

SELECT INTO #results SELECT child_userid FROM user_hierarchy
WHERE Parent = @.parent
DECLARE @.next_level int
SELECT @.next_level = COUNT (*) FROM user_hierarchy
WHERE parent_userid IN ( SELECT * FROM #results )
AND child_userid NOT IN ( SELECT * FROM #results )

whle @.next_level != 0
Begin
SELECT INTO #results SELECT child_userid FROM user_hierarchy
WHERE parent_userid IN ( SELECT * FROM #results )
AND child_userid NOT IN ( SELECT * FROM #results )
SELECT @.next_level = COUNT (*) FROM user_hierarchy
WHERE parent_userid IN ( SELECT * FROM #results )
AND child_userid NOT IN ( SELECT * FROM #results )
End|||Hi

I have the same kind of problem. I don't know much about SQL.
I have a table parent_child that contains the following fields:

parent_child_id
parent_id
child_id
qty

I need to show the table in hierarchical form.

Parent Child
1 2
1 3
1 4
3 5
3 6

Needs to be displayed as (or as close to):

1 -> 2
-> 3 -> 5
-> 6
-> 4

Any ideas?

Thanks
Trav|||Assuming you have the following table in your database:

user_hierarchy (parent_userid int, child_userid int)

Add the next store proc and function to your database:

1) Function:

CREATE FUNCTION fnChilds (@.parent int, @.comp int, @.lev int)
RETURNS @.T1 TABLE (ParentID int, ChildID int, LevelNo int, DirectParent int)
AS
BEGIN
insert into @.T1 select @.parent,child_userid,@.lev,@.comp from user_hierarchy where parent_userid=@.comp
set @.lev=@.lev+1
declare Crs cursor for
select distinct ChildID from @.T1 order by 1
open Crs
fetch next from Crs INTO @.comp
WHILE @.@.FETCH_STATUS = 0
BEGIN
insert into @.T1 select ParentID, ChildID, LevelNo, DirectParent from dbo.fnChilds(@.parent,@.comp,@.lev)
fetch next from Crs INTO @.comp
END
close Crs
deallocate Crs
return
END

2) Store proc:

CREATE PROCEDURE GetChilds @.parent int AS
declare @.lev int,@.comp int
set @.lev=1
set @.comp=@.parent
select distinct * from dbo.fnChilds(@.parent,@.comp,@.lev) order by LevelNo

In Query Analyzer type:
Exec GetChilds 1 (where 1 is a valid code for a parent)

IONUT

Good look!|||Thanks heaps for that...

How do I display this though using ASP? Is it a matter of just calling the stored procedure or do I have to do something else to make it display the structure on the web?

I ultimately need to have a form that prompts a user for the parent no and then displays the structure for that parent.|||Worked out how to do my last question... Was pretty easy in the end.

However, how can I include the names of the parent and children in my results?

Thanks|||Assuming you have the following table in your database:

users(UserID int,UserName varchar(50))

, change the store proc to this one:

CREATE PROCEDURE GetChilds @.parent int AS
declare @.lev int,@.comp int
set @.lev=1
set @.comp=@.parent
select parent.UserName as ParentName,child.UserName as ChildName,LevelNo,directparent.UserName as DirectParentName
from (select distinct * from dbo.fnChilds(@.parent,@.comp,@.lev)) T
join users as parent on T.ParentID=parent.UserID join users as child on T.ChildID=child.UserID
join users as directparent on T.DirectParent=directparent.UserID
order by LevelNo,child.UserName

IONUT

PS In my opinion the introduction of functions (especially the functions that return a table) is a big step forward for SQLServer.

Beware, at first look, recursivity may seem like a cool thing, because this is exactly what SQL language was not (SQL statement treats all records that they processed as a whole, you can not interfear in the process to make recursive calls). It's true but, on the other hand recursive functions may give you a very strong headache because they are one of the best memory consumption agents, and can easily become a bottleneck for your application if they are used frequently and with large sets of records (to read: with many branches)|||Thanks once again... Now to really test you. Any ideas on how I could display this structure using ASP or something esle in hierarchical form instead of in a table.

It would be easier to read if you could see the branches.

eg.

Assembly 2
Part 1
Part 2
Assembly 1
Part 1

It doesn't have to be done using ASP ... I am just curious if anybody knows a way to show it like this. Even if there is a software package that will display it.

Thanks
Trav|||Just discovered an error... If I have any more than 2 levels I get the following error:

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Is there a way to have an infinite number of levels?

Cheers|||You can't overcome the 32 nested levels limit. Even so, I cant't figure out why it wasn't work afeter level 2?? So, I've changed the store proc and function with another store proc which is not recursive anymore:

Create this store proc in your database:

CREATE PROCEDURE GetChilds1
@.parent int AS
declare @.lvl int,@.cont int
declare @.T1 TABLE (ParentID int, ChildID int, LevelNo int, DirectParent int)
declare @.T2 TABLE (CompID int)
set @.lvl=0
insert into @.T2 values (@.parent)
set @.cont=1
while @.cont<>0
BEGIN
insert into @.T1
select @.parent,child_userid,@.lvl,tbl2.CompID from user_hierarchy tbl1
join @.T2 tbl2 on tbl1.parent_userid=tbl2.CompID
delete from @.T2
insert into @.T2 select distinct ChildID from @.T1 where LevelNo=@.lvl
set @.cont=@.@.rowcount
set @.lvl=@.lvl+1
END
select parent.UserName as ParentName,child.UserName as ChildName,LevelNo,directparent.UserName as DirectParentName
from @.T1 as T join users as parent on T.ParentID=parent.UserID join users as child on T.ChildID=child.UserID
join users as directparent on T.DirectParent=directparent.UserID
order by LevelNo,child.UserName

As for the layout in ASP script, that's your task to handle.

Good luck!

IONUT|||I discovered that I got the error because I added a child that already had the parent as one of its own children.

eg. parent = 4, children = 3, 2, 1

then I created a parent = 3 with children = 4, etc..

Is there an easy way to validate this when I insert records into the parent_child table? I don't have a stored procedure for the insert. I just have an insert statement that inserts a selected parent number into the parent column, and inserts the children as you go but doesn't allow you to insert the same child twice unless the parent number is different.

eg. I select parent = 4 and start adding children.

Parent Child
4 1
4 2
4 3

Sorry I don't know enough about writing stored procedures, functions, etc, and this is the only way I could come up with.|||It's more simple than to create store procedure or function. You simply decalre the primary key for the parent_child table as ParentID,ChildID. Or, if you already have another primary key for that table, you can declare an unique index on those two fields.

IONUT

PS Once you do that the only thing that you have to implement is a error check procedure (in your ASP code), in case that insert statement failed because of duplicates entries. (see the result that the execute method returns, for the command object that you used in vbscript)|||Sorry I don't really understand what you mean? I didn't think I could have a primary key in this parent_child table?

I need to be able to look all the way down a tree structure. Say I am inserting parent no = 6 with child no =3, but child no = 3 already exists as a parent with child no = 6.

It is okay to validate this but it gets tricky if parent no = 3 only contains child no = 4, but child no = 4 is a parent to child no = 6.

This is very confusing I know but I'd really appreciate any suggestions or stored procedures that will resolve this problem.

Thanks
Trav|||You can create a trigger (INSERTS and UPDATES) for your parentchild table, something like this:

if ((select count(*) from parent_child inner join inserted on parent_child.ParentID=inserted.ParentID and parent_child.ChildID=inserted.ChildID)+(select count(*) from parent_child inner join inserted on parent_child.ParentID=inserted.ChildID and parent_child.ChildID=inserted.ParentID))>0
ROLLBACK TRANSACTION
else
COMMIT TRANSACTION

ionut|||Thank... I created this trigger and it wouldn't allow me to do any inserts. I figured it was because the condition would not commit the transaction if the count > 0. So I changed it to count > 1 and this works fine, except I still have the problem that it will allow me to insert a child that is a parent which already contains this same parent some where down the line that I am inserting.

I have the following table:
P C
3 1
3 4
4 6

Then if I try to add item 6 as a parent with a child = item 3, I shouldn't be able to. It shouldn't let me do this because parent = 3 actually already contains item no 6 sitting under its other child no 4.

It doesn't matter how many branches down the tree I go, I shouldn't be able to add a child to a parent if that child already exists as a parent else where, and it contains this same parent I am creating as a child, grandchild, great grand child, etc, somewhere in its tree structure.

Sorry I am not explaining it very well... It is confusing.

Help w/ Data Structure

In a simple sales contact management software that I developed, users "own"
companies that are part of their portfolio. This is denoted by a row in the
UserCompany table. I use a join table because multiple users can own the
same company.
CREATE TABLE UserCompany
(
UserId int,
CompanyId int,
TargetCompany bit
)
The TargetCompany column is used to denote whether or not a certain company
is an important company in the specified user's portfolio. We run many
reports which only include a user's target companies.
I realized though, that if I run one of these reports for a date range that
is in the past, the data will always be based on the target companies
currently in UserCompany. i.e. Because of the data structure, I have no way
of telling if a user's company used to be a target company sometime in the
past.
Can someone suggest how I could modify this data structure to be able to
historically track when companies where denoted as target companies for a
certain user?
So, if Company A was denoted as a target company for User 1, between
01/01/2004, and 12/31/2004, but not later, how would I show that?
Thank YouHow about adding columns for StartOfInterestDate and EndOfInterest date,
denoting when the user had an interest in a particular company. You should
then be able to modify your query to include this within your criteria.
Cheers,
James Goodman
"George Durzi" <gdurzi@.hotmail.com> wrote in message
news:OyQAupkUFHA.580@.TK2MSFTNGP15.phx.gbl...
> In a simple sales contact management software that I developed, users
> "own" companies that are part of their portfolio. This is denoted by a row
> in the UserCompany table. I use a join table because multiple users can
> own the same company.
> CREATE TABLE UserCompany
> (
> UserId int,
> CompanyId int,
> TargetCompany bit
> )
> The TargetCompany column is used to denote whether or not a certain
> company is an important company in the specified user's portfolio. We run
> many reports which only include a user's target companies.
> I realized though, that if I run one of these reports for a date range
> that is in the past, the data will always be based on the target companies
> currently in UserCompany. i.e. Because of the data structure, I have no
> way of telling if a user's company used to be a target company sometime in
> the past.
> Can someone suggest how I could modify this data structure to be able to
> historically track when companies where denoted as target companies for a
> certain user?
> So, if Company A was denoted as a target company for User 1, between
> 01/01/2004, and 12/31/2004, but not later, how would I show that?
> Thank You
>|||Nice idea.
Let's say a company is a target company for 1 yr, then not a target company
for some time, and back to being a target company (phew)
Do you think this would be handled best with multiple rows for the same
User/Company?
"James Goodman" <jamesATnorton-associates.co.ukREMOVE> wrote in message
news:%23PhVr5kUFHA.3344@.TK2MSFTNGP10.phx.gbl...
> How about adding columns for StartOfInterestDate and EndOfInterest date,
> denoting when the user had an interest in a particular company. You should
> then be able to modify your query to include this within your criteria.
> --
> Cheers,
> James Goodman
> "George Durzi" <gdurzi@.hotmail.com> wrote in message
> news:OyQAupkUFHA.580@.TK2MSFTNGP15.phx.gbl...
>|||Yes, sounds like you need a separate log table.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"George Durzi" <gdurzi@.hotmail.com> wrote in message
news:uW46R7kUFHA.1148@.tk2msftngp13.phx.gbl...
> Nice idea.
> Let's say a company is a target company for 1 yr, then not a target
> company for some time, and back to being a target company (phew)
> Do you think this would be handled best with multiple rows for the same
> User/Company?
> "James Goodman" <jamesATnorton-associates.co.ukREMOVE> wrote in message
> news:%23PhVr5kUFHA.3344@.TK2MSFTNGP10.phx.gbl...
>|||Thank you both for your help.
George
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:enaeB$kUFHA.3176@.TK2MSFTNGP12.phx.gbl...
> Yes, sounds like you need a separate log table.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "George Durzi" <gdurzi@.hotmail.com> wrote in message
> news:uW46R7kUFHA.1148@.tk2msftngp13.phx.gbl...
>|||CREATE TABLE UserCompanyHistory
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON UPDATE CASCADE,
company_id INTEGER NOT NULL
REFERENCES Companies(company_id)
ON UPDATE CASCADE,
company_priority INTEGER NOT NULL
CHECK (company_priority > 0),
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
end_date DATETIME, -- null means current\
CHECK(start_date, end_date),
PRIMARY KEY (user_id, company_id, start_date)) ;
Do not use the BIT datatype; use a prioirty number instead. Time is in
durations, so add start and end times to the table. Now for some
views:
CREATE VIEW UserCompany (user_id, company_id, company_priority)
AS SELECT user_id, company_id, user_id, company_priority
FROM UserCompanyHistory
WHERE end_date IS NULL;
CREATE VIEW UserBestCompany (user_id, company_id)
AS SELECT user_id, company_id, user_id
FROM UserCompanyHistory AS H1
WHERE company_priority = 1;
or if you do not maintain a nice ordering in the priority column:
CREATE VIEW UserBestCompany (user_id, company_id)
AS SELECT user_id, company_id, user_id
FROM UserCompanyHistory AS H1 WHERE company_priority
= (SELECT MIN(company_priority)
FROM UserCompanyHistory AS H2
WHERE H1.user_id = H2.user_id _
You will need some triggers to maintain the history table integrity,
but they are not tricky

help Users

Hi I`m New in this please help

when i try to create a user on a SQL Magnament Studio, shows me a error:

TITLE: Microsoft SQL Server Management Studio

Create failed for Login 'mikke'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The MUST_CHANGE option is not supported by this version of Microsoft Windows. (Microsoft SQL Server, Error: 15195)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15195&LinkId=20476

Password policy options are only supported on Windows Server 2003, so I assume you are running SQL Server on something else. Don't use any of the password options when you create the user.|||It may be that this option requires that the SQL Server reside on a domain with Active Directory.|||

No, Active Directory is not required, but Windows Server 2003 is.

http://msdn2.microsoft.com/en-us/library/ms161959.aspx
"When it is running on Microsoft Windows Server 2003 or later versions, SQL Server 2005 can use Windows password policy mechanisms."

|||

If the server is running on Windows XP or Windows 2000, you'll need to uncheck the checkbox in the dialog that forces the user to change their password. That option isn't supported on pre-2003 operating systems.

Hope this helps,
Steve

|||

yes that was, and too when i installed SQL Server Developer Edition i haven`t selected Mixed mode authentication, so now i can create, delete, modify users.

Thanks a lot

help Users

Hi I`m New in this please help

when i try to create a user on a SQL Magnament Studio, shows me a error:

TITLE: Microsoft SQL Server Management Studio

Create failed for Login 'mikke'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The MUST_CHANGE option is not supported by this version of Microsoft Windows. (Microsoft SQL Server, Error: 15195)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15195&LinkId=20476

Password policy options are only supported on Windows Server 2003, so I assume you are running SQL Server on something else. Don't use any of the password options when you create the user.|||It may be that this option requires that the SQL Server reside on a domain with Active Directory.|||

No, Active Directory is not required, but Windows Server 2003 is.

http://msdn2.microsoft.com/en-us/library/ms161959.aspx
"When it is running on Microsoft Windows Server 2003 or later versions, SQL Server 2005 can use Windows password policy mechanisms."

|||

If the server is running on Windows XP or Windows 2000, you'll need to uncheck the checkbox in the dialog that forces the user to change their password. That option isn't supported on pre-2003 operating systems.

Hope this helps,
Steve

|||

yes that was, and too when i installed SQL Server Developer Edition i haven`t selected Mixed mode authentication, so now i can create, delete, modify users.

Thanks a lot

HELP URGENT - Error 17832 after SQL 7 SP4 install and MS03-039 install

I have an application that was developed in an older version of VB (I think
4.0). The users are heavily dependent on this application (and of course,
no source code). Yesterday the users were able to connect to the SQL 7
database. Last night I ran the Service Pack 4 on the SQL 7 machine and
today the users are unable to connect. They just get an error message
(Invalid SQL Server Login). When I look in the error log on the server I
see Error: 17832, Severity: 18, State: 7 Connection opened but invalid
login packet(s) sent. Connection closed.
Also, the latest security patch from Microsoft (MS03-039) was installed on
the server that houses the database. It is running Windows 2000.
Thanks
Any assistance would be greatly appreciated.Connie,
The only reference to 17832 that I could find was in this article:
INF: SQL Communication Errors 17832, 17824, 1608, 232, and 109 (KB Article
109787)
http://tinyurl.com/nfm8
17832 Unable to read login packet(s). [NT only]
This can happen if a client starts to connect, but never successfully
completes the attempt because of a client operating system or application
failure. It could also be caused by the network failing between the time a
connection attempt is initiated, and when it completes.
Of course, several changes were made, all at once, which is always risky.
(We always apply upgrades to a DEV or QA server before moving on to
production, if at all possible.)
This may be due to either SP4 or MS03-039. I see that MS03-039 patches RPC.
Although your application should (ideally) not need that facility I have no
way of knowing.
A more likely possibility: Did you us SP4 to update client software as
well? Some SPs have client and server components. Usually this is not a
problem, but sometimes it has caused grief.
Russell Fields
"Connie" <cfelt@.ga.wa.gov> wrote in message
news:%23oGDh66eDHA.2352@.TK2MSFTNGP09.phx.gbl...
> I have an application that was developed in an older version of VB (I
think
> 4.0). The users are heavily dependent on this application (and of course,
> no source code). Yesterday the users were able to connect to the SQL 7
> database. Last night I ran the Service Pack 4 on the SQL 7 machine and
> today the users are unable to connect. They just get an error message
> (Invalid SQL Server Login). When I look in the error log on the server I
> see Error: 17832, Severity: 18, State: 7 Connection opened but invalid
> login packet(s) sent. Connection closed.
> Also, the latest security patch from Microsoft (MS03-039) was installed on
> the server that houses the database. It is running Windows 2000.
> Thanks
> Any assistance would be greatly appreciated.
>|||You might want to check if the authentication mode got changed. Some of the
SQL service packs try to force Windows authentication or give the sa a
password other than blank. Go to SQL Server properties in SQL enterprise
manager, and check the security tab. See if you are in mixed mode or not.
From a DOS prompt on the server, you might also want to try
osql -U<user> -P<password> -S<servername>
for a user who cannot log on. That might give you a better error message.
--
***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Connie" <cfelt@.ga.wa.gov> wrote in message
news:%23oGDh66eDHA.2352@.TK2MSFTNGP09.phx.gbl...
> I have an application that was developed in an older version of VB (I
think
> 4.0). The users are heavily dependent on this application (and of course,
> no source code). Yesterday the users were able to connect to the SQL 7
> database. Last night I ran the Service Pack 4 on the SQL 7 machine and
> today the users are unable to connect. They just get an error message
> (Invalid SQL Server Login). When I look in the error log on the server I
> see Error: 17832, Severity: 18, State: 7 Connection opened but invalid
> login packet(s) sent. Connection closed.
> Also, the latest security patch from Microsoft (MS03-039) was installed on
> the server that houses the database. It is running Windows 2000.
> Thanks
> Any assistance would be greatly appreciated.
>|||how is this application connecting to the SQL Server, an ODBC like? is that
pointed ata Named Pipe connection, if it is try switching it to a TCP/IP
like,
if not Look at your SQL Client Network Utility, if the default is Named
Pipes or you have a Named Pipes Alias try switching it to TCP/IP.
MS0-039 is has a lot of cross over with MS03-026, both applied a lot of
security to connectivity through Named Pipes.
HtH

Help troubleshooting SQL7 server hang

I have a Compaq ML370 running 2k server sp3 and SQL7 sp3. Very
intermittently the server hang and disconnect all sql users. I can still
ping the server but there is no video or other interaction possible. All
that can be done is to dump it with the switch and then bring it back up.
When it does come back up, there are no fingerprints in the event log what
happened except that "The previous system shutdown yada yada yada was
unexpected."
As much as it sounds like a hardware issue, all diags come back fine and all
the latest drivers and firmware are installed. To me it almost sounds like a
denial of service problem that is crashing the machine. The only
communications with the machine though are via tcp/ip to the sql listener.
My question...
Is it possible that the sql traffic could cause this kind of crash?
Another question...
Can anyone suggest a way to troubleshoot this? I can't seem to force it to
happen because I can't determine what contributes to it.
MikeYou might try doing a black box trace with profiler, that will contain the
last SQL things that were done prior to the hang up... ( from BOL)
Use sp_trace_create with the TRACE_PRODUCE_BLACKBOX option to define a trace
that appends trace information to a blackbox.trc file in the \Data
directory. Once the trace is started, trace information is recorded in the
blackbox.trc file until the size of the file reaches 5 megabytes (MB). The
trace then creates another trace file, blackbox_01.trc, and trace
information is written to the new file. When the size of blackbox_01.trc
reaches 5 MB, the trace reverts to blackbox.trc. Thus, up to 5 MB of trace
information is always available.
"Mike Strout" <m i k e s t r o u t @. h o t m a i l . c o m> wrote in message
news:vgouilr21jpp40@.corp.supernews.com...
> I have a Compaq ML370 running 2k server sp3 and SQL7 sp3. Very
> intermittently the server hang and disconnect all sql users. I can still
> ping the server but there is no video or other interaction possible. All
> that can be done is to dump it with the switch and then bring it back up.
> When it does come back up, there are no fingerprints in the event log what
> happened except that "The previous system shutdown yada yada yada was
> unexpected."
> As much as it sounds like a hardware issue, all diags come back fine and
all
> the latest drivers and firmware are installed. To me it almost sounds like
a
> denial of service problem that is crashing the machine. The only
> communications with the machine though are via tcp/ip to the sql listener.
> My question...
> Is it possible that the sql traffic could cause this kind of crash?
> Another question...
> Can anyone suggest a way to troubleshoot this? I can't seem to force it to
> happen because I can't determine what contributes to it.
> Mike
>|||Use sp_trace_create with the TRACE_PRODUCE_BLACKBOX option to define a trace
that appends trace information to a blackbox.trc file in the \Data
directory. Once the trace is started, trace information is recorded in the
blackbox.trc file until the size of the file reaches 5 megabytes (MB). The
trace then creates another trace file, blackbox_01.trc, and trace
information is written to the new file. When the size of blackbox_01.trc
reaches 5 MB, the trace reverts to blackbox.trc. Thus, up to 5 MB of trace
information is always available.
"Mike Strout" <m i k e s t r o u t @. h o t m a i l . c o m> wrote in message
news:vgouilr21jpp40@.corp.supernews.com...
> I have a Compaq ML370 running 2k server sp3 and SQL7 sp3. Very
> intermittently the server hang and disconnect all sql users. I can still
> ping the server but there is no video or other interaction possible. All
> that can be done is to dump it with the switch and then bring it back up.
> When it does come back up, there are no fingerprints in the event log what
> happened except that "The previous system shutdown yada yada yada was
> unexpected."
> As much as it sounds like a hardware issue, all diags come back fine and
all
> the latest drivers and firmware are installed. To me it almost sounds like
a
> denial of service problem that is crashing the machine. The only
> communications with the machine though are via tcp/ip to the sql listener.
> My question...
> Is it possible that the sql traffic could cause this kind of crash?
> Another question...
> Can anyone suggest a way to troubleshoot this? I can't seem to force it to
> happen because I can't determine what contributes to it.
> Mike
>

Help to store demo users

Hi,
My table stores around 30000 users, out of which around 5-10 are demousers.
Right now we are using the NOT IN Statement to list the original users
like
SELECT * FROM UserTable WHERE UserID NOT IN ( Demo User IDs)
I know this will affect performance. Right now i am planning to modify the
database design in order to avoid the use of NOT IN. i have two options
either to add a astatus field in the current table or to create a new table
for demo users. if i go for the second option, i need to use a UNION ALL
Statement to list all users , now if i am going for the first option, i need
to check the value of status in order to distinguish the different users.
Can anybody suggest the better solution ? Accessing two tables or Accessing
each row and checking the status.
Thanking in advance
LaraLara
We have a user table with 'vis' column which indicates 0 -demo and 1 -active
users
SELECT <column list> FROM Users WHERE vis=1
"Lara" <aneeshattingal@.hotpop.com> wrote in message
news:%23pGBySXUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Hi,
> My table stores around 30000 users, out of which around 5-10 are
demousers.
> Right now we are using the NOT IN Statement to list the original users
> like
> SELECT * FROM UserTable WHERE UserID NOT IN ( Demo User IDs)
> I know this will affect performance. Right now i am planning to modify the
> database design in order to avoid the use of NOT IN. i have two options
> either to add a astatus field in the current table or to create a new
table
> for demo users. if i go for the second option, i need to use a UNION ALL
> Statement to list all users , now if i am going for the first option, i
need
> to check the value of status in order to distinguish the different users.
> Can anybody suggest the better solution ? Accessing two tables or
Accessing
> each row and checking the status.
> Thanking in advance
> Lara
>|||But wiill that be better; or shall i use a seperate table.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OcSy9WXUFHA.616@.TK2MSFTNGP12.phx.gbl...
> Lara
> We have a user table with 'vis' column which indicates 0 -demo and
1 -active
> users
> SELECT <column list> FROM Users WHERE vis=1
>
>
> "Lara" <aneeshattingal@.hotpop.com> wrote in message
> news:%23pGBySXUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> demousers.
the
> table
> need
users.
> Accessing
>|||Lara
Why do you need to separate them? I don't know your business requirements.
Its good to have a status column to store the info.
"Lara" <aneeshattingal@.hotpop.com> wrote in message
news:%23TUzqaXUFHA.752@.TK2MSFTNGP10.phx.gbl...
> But wiill that be better; or shall i use a seperate table.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OcSy9WXUFHA.616@.TK2MSFTNGP12.phx.gbl...
> 1 -active
> the
options
ALL
i
> users.
>|||Separate Table wont help you becasue you would have the need to always
mantain your app or your procedural logic for these two ways. Thats really
odd. The best thing is like Uri says to flag these users as active or demo
suers.
HTH, Jens Suessmeyer.
"Lara" <aneeshattingal@.hotpop.com> schrieb im Newsbeitrag
news:%23TUzqaXUFHA.752@.TK2MSFTNGP10.phx.gbl...
> But wiill that be better; or shall i use a seperate table.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OcSy9WXUFHA.616@.TK2MSFTNGP12.phx.gbl...
> 1 -active
> the
> users.
>|||
> But wiill that be better; or shall i use a seperate table.
I prefer the separate table, in a schema like this:
Users = {ID, Name, ...}
Demo_Users = {ID}
Non_Demo_Users = VIEW ::
select * from Users where ID not in (select ID from Demo_Users)
-- Alex Papadimoulis

>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OcSy9WXUFHA.616@.TK2MSFTNGP12.phx.gbl...
> 1 -active
> the
> users.
>
>

Help to deleting n numbers of records

Hi
One of our users have by mistake created a number of dublicate records in
the database. Now I need to delete the "faulty" records from the table and
just keep one of them. The records are linked to a customer table, so I have
a Recordid column in the table that I can use to group on. E.g. if I in the
table have 10 records with RecordID 1, I need to delete 9 of them and keep
1, 8 records with RecordID 2 I need to delete 7 and keep 1 etc.
I'll have to search the customer table to find the recordID's that is used
to link to the child table, so my plan is to use a cursor to find these and
put them into a variable. I'll then use this to find the record(s) in the
child table, get the number of records and then either delete them one by
one until I've only 1 pr. RecordID left or use SELECT Top x based on the
number of records found with each recordid and then delete all but 1 record.
It's not a huge number of records I need to delete so from a practical
and/or performance point of wiev it's not critical how I do it. It's more
that I'm currious to hear if my approach is the best one or if any of you
have any other ideas?
TIA
Regards
SteenTo answer this properly we'll need to know the keys and constraints in
your tables. Please post DDL (CREATE TABLE) and some sample data
(INSERTs) if you want help with the actual code.
You missed out one critical step from your solution: Add a new unique
constraint so that this can't happen again.
David Portas
SQL Server MVP
--|||Hi
First of all, it's a vendor application, so I can't change anything in the
database or application. I this case it's not a problem though, since it's
ok to create several records as they did, but in this case it's just because
they had some problems with a printer and therefore thay ran a wizard
several times which generated a number of records that where baiscally same.
It's these records thay now want to get deleted.
The 2 tables that's involved is called Lejer and Note.
CREATE TABLE [Lejer] (
[EjendomNr] [EjendomNr] NOT NULL ,
[LejemaalNr] [LejemaalNr] NOT NULL ,
[LejerNr] [LejerNr] NOT NULL ,
[LejerID] [RecordID] IDENTITY (1, 1) NOT NULL ,
....and about 200 more column definitions
CREATE TABLE [Note] (
[NoteID] [RecordID] NOT NULL ,
[Tabelnavn] [TabelNavn] NOT NULL ,
[RecordID] [RecordID] NOT NULL ,
[Dato] [Dato] NOT NULL ,
[NoteType] [KodeId] NOT NULL ,
....and some more column definitions.
The fields that links the tables are Lejer.LejerID and Note.RecordID.
Below is ans example of sample data
Lejer:
Ejendomnr Lejemaalnr Lejernr LejerID
1 1 1 1000
1 2 2 1001
1 3 3 1002
2 1 1 1003
2 2 2 1004
3 1 1 1005
3 2 2 1006
Note
NoteID RecordID NoteType
1 1000 29000
2 1000 29000
3 1000 29000
4 1000 29000
5 1001 29000
6 1001 29000
7 1001 29000
8 1002 29000
9 1002 29000
10 1002 29000
11 1003 29000
12 1003 29000
I'd like to find the notes where the type is e.g. 29000 and are linked a
record in the Lejer table with the Ejendomnr of e.g. 1.
and then delete all notes but 1.
In the above example, it means that if I use Ejendomnr = 1, I'll have the
Note records with NoteID 1 to 10. Out of these I'd like to delete 3 of the
ones with RecordID 1000, 2 of them with RecordID 1001 and 2 of them with
RecordID 1002.
As mentioned in my original post, I could get all the RecordID's into a
cursor and then e.g. count the number of occurences for each of them and
then do a delete n-1 times. I just don't know if that's the smartest way to
do it or if there's a better approach?
Regards
Steen
David Portas wrote:
> To answer this properly we'll need to know the keys and constraints in
> your tables. Please post DDL (CREATE TABLE) and some sample data
> (INSERTs) if you want help with the actual code.
> You missed out one critical step from your solution: Add a new unique
> constraint so that this can't happen again.
> --
> David Portas
> SQL Server MVP|||How about
Delete Note
Where NoteType = '29000'
And NoteId <> (
Select Min(N1.NoteId)
From Note As N1
Where Note.RecordId = N1.RecordId
And N1.NoteType = Note.NoteType
)
Here I'm assuming that a given Note.RecordId must exist in Lejer.LejerId.
However, it is possible that this is not the case and you want to ensure tha
t
the record does exist in the Lejer table then you could add an Exists clause
like so:
Delete Note
Where NoteType = '29000'
And Exists(
Select *
From Lejer As L1
Where L1.LegerId = Note.RecordId
)
And NoteId <> (
Select Min(N1.NoteId)
From Note As N1
Where Note.RecordId = N1.RecordId
And N1.NoteType = Note.NoteType
)
Obviously, you should execute this code carefully to ensure that it is produ
ce
the results you want before you commit against production data.
HTH
Thomas
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OfARdISYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Hi
> First of all, it's a vendor application, so I can't change anything in the
> database or application. I this case it's not a problem though, since it's
ok
> to create several records as they did, but in this case it's just because
they
> had some problems with a printer and therefore thay ran a wizard several t
imes
> which generated a number of records that where baiscally same. It's these
> records thay now want to get deleted.
> The 2 tables that's involved is called Lejer and Note.
>
> CREATE TABLE [Lejer] (
> [EjendomNr] [EjendomNr] NOT NULL ,
> [LejemaalNr] [LejemaalNr] NOT NULL ,
> [LejerNr] [LejerNr] NOT NULL ,
> [LejerID] [RecordID] IDENTITY (1, 1) NOT NULL ,
> .....and about 200 more column definitions
>
> CREATE TABLE [Note] (
> [NoteID] [RecordID] NOT NULL ,
> [Tabelnavn] [TabelNavn] NOT NULL ,
> [RecordID] [RecordID] NOT NULL ,
> [Dato] [Dato] NOT NULL ,
> [NoteType] [KodeId] NOT NULL ,
> ....and some more column definitions.
> The fields that links the tables are Lejer.LejerID and Note.RecordID.
>
> Below is ans example of sample data
> Lejer:
> Ejendomnr Lejemaalnr Lejernr LejerID
> 1 1 1 1000
> 1 2 2 1001
> 1 3 3 1002
> 2 1 1 1003
> 2 2 2 1004
> 3 1 1 1005
> 3 2 2 1006
> Note
> NoteID RecordID NoteType
> 1 1000 29000
> 2 1000 29000
> 3 1000 29000
> 4 1000 29000
> 5 1001 29000
> 6 1001 29000
> 7 1001 29000
> 8 1002 29000
> 9 1002 29000
> 10 1002 29000
> 11 1003 29000
> 12 1003 29000
>
> I'd like to find the notes where the type is e.g. 29000 and are linked a
> record in the Lejer table with the Ejendomnr of e.g. 1.
> and then delete all notes but 1.
> In the above example, it means that if I use Ejendomnr = 1, I'll have the
Note
> records with NoteID 1 to 10. Out of these I'd like to delete 3 of the ones
> with RecordID 1000, 2 of them with RecordID 1001 and 2 of them with Record
ID
> 1002.
> As mentioned in my original post, I could get all the RecordID's into a cu
rsor
> and then e.g. count the number of occurences for each of them and then do
a
> delete n-1 times. I just don't know if that's the smartest way to do it or
if
> there's a better approach?
> Regards
> Steen
>
> David Portas wrote:
>|||Hi Thomas
Thanks for you input. That was another way of doing it than I had in my
mind. I'll try it out in my test db.
Regards
Steen
Thomas Coleman wrote:
> How about
> Delete Note
> Where NoteType = '29000'
> And NoteId <> (
> Select Min(N1.NoteId)
> From Note As N1
> Where Note.RecordId = N1.RecordId
> And N1.NoteType = Note.NoteType
> )
> Here I'm assuming that a given Note.RecordId must exist in
> Lejer.LejerId. However, it is possible that this is not the case and
> you want to ensure that the record does exist in the Lejer table then
> you could add an Exists clause like so:
> Delete Note
> Where NoteType = '29000'
> And Exists(
> Select *
> From Lejer As L1
> Where L1.LegerId = Note.RecordId
> )
> And NoteId <> (
> Select Min(N1.NoteId)
> From Note As N1
> Where Note.RecordId = N1.RecordId
> And N1.NoteType = Note.NoteType
> )
> Obviously, you should execute this code carefully to ensure that it
> is produce the results you want before you commit against production
> data.
> HTH
>
> Thomas
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OfARdISYFHA.2348@.TK2MSFTNGP14.phx.gbl...