Help with a SQL query

I'm trying to draft a SQL Query for the following:
A bookstore that sells books - when a user selects a book to view it's
details, he should see a section that says "People who bought this book also
bought...with a list of various titles" - this is a common feature of sites
such as Barnes & Noble and Amazon.
How does the SQL Query work to get this information extracted out of the
database.
I have a Products table (with fields related to each product - Product ID,
Product Title, Product Category)
I have an Orders table (with fields related to each order - Order ID,
Persons ID, ...)
I have an Order Details table (with fields related to each item in the
order - Order ID, Product ID)RB wrote:
> I'm trying to draft a SQL Query for the following:
> A bookstore that sells books - when a user selects a book to view it's
> details, he should see a section that says "People who bought this book also
> bought...with a list of various titles" - this is a common feature of sites
> such as Barnes & Noble and Amazon.
> How does the SQL Query work to get this information extracted out of the
> database.
> I have a Products table (with fields related to each product - Product ID,
> Product Title, Product Category)
> I have an Orders table (with fields related to each order - Order ID,
> Persons ID, ...)
> I have an Order Details table (with fields related to each item in the
> order - Order ID, Product ID)
>
Assuming that you have the productID from the webpage, which will be
represented as @.PRODUCTID.
select P.productID, P.title
from Products P, OrderDetails OD
where P.productID = OD.productID and OD.PersonID IN (
select O.personID
from OrderDetails OD, Orders O
where OD.productID = @.PRODUCTID AND OD.OrderID = O.OrderID
)
Without the exact DB schema this is the best I could come up.
You will need to tweak it a little because you will get repeats etc.
It should be straight forward.

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 with a query

Hi,
I have a table tScores with 3 fields: user, score and stage.
What I want is to select the 2 best scores (highest) of each user.
Ex.
tScores
--
A, 20, 1
A, 10, 2
A, 30, 3
A, 40, 4
A, 10, 5
B, 20, 1
B, 30, 2
B, 10, 3
C, 10, 1
The result would be:
A, 40, 4
A, 30, 3
B, 30, 2
B, 20, 1
C, 10, 1
Thanks in advance,
John LewisJohn Lewis wrote:
> Hi,
>
> I have a table tScores with 3 fields: user, score and stage.
> What I want is to select the 2 best scores (highest) of each user.
>
> Ex.
> tScores
> --
> A, 20, 1
> A, 10, 2
> A, 30, 3
> A, 40, 4
> A, 10, 5
> B, 20, 1
> B, 30, 2
> B, 10, 3
> C, 10, 1
>
> The result would be:
>
> A, 40, 4
> A, 30, 3
> B, 30, 2
> B, 20, 1
> C, 10, 1
>
> Thanks in advance,
>
> John Lewis
How do you want to handle the case where the user has the same highest
score more than once (or more than twice)? I'll assume you want all
tied scores even if that means you return more than two rows per user:
SELECT [user], score, stage
FROM tScores AS S
WHERE 2 >
(SELECT COUNT(*)
FROM tScores
WHERE [user] = S.[user]
AND score > S.score) ;
User is a bad choice for a column name because it's a reserved word and
because it is too vague (user_name, user_login, user_id ?)
David Portas
SQL Server MVP
--|||Hello David,
I'll offer this as an alternative for SqlServer 2005:
drop table #tests
go
create table #tests
(
[user] char(1) not null,
score tinyint not null,
stage tinyint not null
)
go
insert into #tests values('A', 20, 1)
insert into #tests values('A', 10, 2)
insert into #tests values('A', 30, 3)
insert into #tests values('A', 40, 4)
insert into #tests values('A', 10, 5)
insert into #tests values('B', 20, 1)
insert into #tests values('B', 30, 2)
insert into #tests values('B', 10, 3)
insert into #tests values('C', 10, 1)
go
;with tests([user],score,stage,rank) as
(
select [user],score,stage,
rank() over (partition by [user] order by score desc) as rank
from #tests
)
select [user],score,stage
from tests
where rank < 3
order by [user],score desc
go
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||"John Lewis" <julugu@.gmail.com> wrote in message
news:1132057809.298727.286240@.o13g2000cwo.googlegroups.com...
> Hi,
>
> I have a table tScores with 3 fields: user, score and stage.
> What I want is to select the 2 best scores (highest) of each user.
>
<snip />

> Thanks in advance,
>
> John Lewis
>
Multipost (over in microsoft.public.access.queries, under "Easy
query? Not to me.").
Sincerely,
Chris O.|||Sorry to post in another group, but I thought it was
sqlserver.queries.
Thanks to all for the answers.

Help with a connection string

I have a two computers, in one of them I install the SQL Server 2005 express with server name USER\SQLEXPRESS and ip 192.168.3.15.The data base name is Prueba.mdf

In the other computer I have VB 2005 express , and I want to connect with Prueba.mdf in the the other pc. Can you help me?

Hi

Try the below connection string settings

User ID=sa; ( if you configured your own, then replace user name and password acc)

password=sa;

Data Source=USER\SQLEXPRESS;

database=Prueba;

Connect Timeout=1800;

Regards,

Dhinesh Kumar

|||If you want to connect to a user instance, you should use one of the strings at www.connectionstrings.com , look for SQL Server 2005 and user instances, there are some strings which will help you.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Help Using a field value in a url jump statement

I have a report that uses the table control, the last column has id's for
dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
any of the other Columns in a row I would like to be able to grab the id from
the last column in the row and insert it into the expression for the Jump to
url feature.
this url works fine
="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
"535d58cc-a1b3-da11-9864-001320020c86" & "');"
but this one does not allow for the pointer hand to show up when cusror is
over the row
="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
Fields!res_dealCompanyid.Value & "');"
Do I need to convert Fields!res_dealCompanyid.Value to a string?
Can I assign it to a variable and then use it in the Jump to URL?
I do not know what else to try
MikeI got this to work, my problem was that the dealCompanyid value was not a
string so I had to do a Fields!res_dealcompanyid.Value.tostring() then it
worked fine.
Parameters!CRMServer.Value just holds a Server info
="javascript:void(window.open('http://" & Parameters!CRMServer.Value &
"/EE2/DealCompany.aspx?id=" & Fields!res_dealcompanyid.Value.tostring() & "',
'_blank'))"
"Hotwheels" wrote:
> I have a report that uses the table control, the last column has id's for
> dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
> any of the other Columns in a row I would like to be able to grab the id from
> the last column in the row and insert it into the expression for the Jump to
> url feature.
> this url works fine
> ="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
> "535d58cc-a1b3-da11-9864-001320020c86" & "');"
> but this one does not allow for the pointer hand to show up when cusror is
> over the row
> ="javascript:window.navigate('http://sandbox:82/EE2/DealCompany.aspx?id=" &
> Fields!res_dealCompanyid.Value & "');"
> Do I need to convert Fields!res_dealCompanyid.Value to a string?
> Can I assign it to a variable and then use it in the Jump to URL?
> I do not know what else to try
> Mike

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!

Hi,
I can access my database. I get the message ......cant access...in single
user mode. How can I reset this?
ThanksThe first thing I would try is from EM, right-click on the
database>properties>Options tab, uncheck Restrict Access if it's checked.
"Chris" wrote:
> Hi,
> I can access my database. I get the message ......cant access...in single
> user mode. How can I reset this?
> Thanks|||Thanks!
"Jack" wrote:
> The first thing I would try is from EM, right-click on the
> database>properties>Options tab, uncheck Restrict Access if it's checked.
> "Chris" wrote:
> > Hi,
> > I can access my database. I get the message ......cant access...in single
> > user mode. How can I reset this?
> >
> > Thanks

Help to Create job

i want to give to my user permission to create job , add step to job etc.
i can't do this when my user do not use sa user but i don't want to give him the sa password so how can i do this
eytanb@.mekorot.co.ilBoth SPs execute permissions are default to the public role and ensure that user does have permission on MSDB database and required privilege on underlying user database.

Help stop my spinning wheels on user sql

Can query sysprocesses to get user information .Can find the top
blocker
but have spun wheels for a long long time unable to find sql run by top
blocker.
Any body done this before or has directions to scripts that can do this
Your input is highly appreciated
MassaTry:
select
p1.*
from
dbo.sysprocesses p1
where exists
(
select
*
from
dbo.sysprocesses p2
where
p2.blocked = p1.spid
)
and p1.blocked in (0, p1.spid)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<mngong@.gmail.com> wrote in message
news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
Can query sysprocesses to get user information .Can find the top
blocker
but have spun wheels for a long long time unable to find sql run by top
blocker.
Any body done this before or has directions to scripts that can do this
Your input is highly appreciated
Massa|||Thanks Dr Tom
Needed the complete sql that has been my bete noire.
at the time the blocking occured
Tom Moreau wrote:
> Try:
> select
> p1.*
> from
> dbo.sysprocesses p1
> where exists
> (
> select
> *
> from
> dbo.sysprocesses p2
> where
> p2.blocked = p1.spid
> )
> and p1.blocked in (0, p1.spid)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <mngong@.gmail.com> wrote in message
> news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
> Can query sysprocesses to get user information .Can find the top
> blocker
> but have spun wheels for a long long time unable to find sql run by top
> blocker.
> Any body done this before or has directions to scripts that can do this
> Your input is highly appreciated
> Massa|||In that case, you'd pretty much need to be running the profiler at the time
it occurred.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<mngong@.gmail.com> wrote in message
news:1153234728.918733.70910@.35g2000cwc.googlegroups.com...
Thanks Dr Tom
Needed the complete sql that has been my bete noire.
at the time the blocking occured
Tom Moreau wrote:
> Try:
> select
> p1.*
> from
> dbo.sysprocesses p1
> where exists
> (
> select
> *
> from
> dbo.sysprocesses p2
> where
> p2.blocked = p1.spid
> )
> and p1.blocked in (0, p1.spid)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <mngong@.gmail.com> wrote in message
> news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
> Can query sysprocesses to get user information .Can find the top
> blocker
> but have spun wheels for a long long time unable to find sql run by top
> blocker.
> Any body done this before or has directions to scripts that can do this
> Your input is highly appreciated
> Massa|||I can't tell if you're using SQL Server 2005 here, but if you are, you might
be interested in the 'blocked process threshold' parameter of sp_configure.
On our testing servers I have a trace going that continually monitors for
these problem areas. What gets returned is an XML block which details the
blocking and blocked processes - very nice indeed. If you're on SQL Server
2000, the sp_blocker_pss80 scripts would be worth looking at
(http://support.microsoft.com/?id=271509).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Help stop my spinning wheels on user sql

Can query sysprocesses to get user information .Can find the top
blocker
but have spun wheels for a long long time unable to find sql run by top
blocker.
Any body done this before or has directions to scripts that can do this
Your input is highly appreciated
MassaTry:
select
p1.*
from
dbo.sysprocesses p1
where exists
(
select
*
from
dbo.sysprocesses p2
where
p2.blocked = p1.spid
)
and p1.blocked in (0, p1.spid)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<mngong@.gmail.com> wrote in message
news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
Can query sysprocesses to get user information .Can find the top
blocker
but have spun wheels for a long long time unable to find sql run by top
blocker.
Any body done this before or has directions to scripts that can do this
Your input is highly appreciated
Massa|||Thanks Dr Tom
Needed the complete sql that has been my bete noire.
at the time the blocking occured
Tom Moreau wrote:
> Try:
> select
> p1.*
> from
> dbo.sysprocesses p1
> where exists
> (
> select
> *
> from
> dbo.sysprocesses p2
> where
> p2.blocked = p1.spid
> )
> and p1.blocked in (0, p1.spid)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <mngong@.gmail.com> wrote in message
> news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
> Can query sysprocesses to get user information .Can find the top
> blocker
> but have spun wheels for a long long time unable to find sql run by top
> blocker.
> Any body done this before or has directions to scripts that can do this
> Your input is highly appreciated
> Massa|||In that case, you'd pretty much need to be running the profiler at the time
it occurred.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<mngong@.gmail.com> wrote in message
news:1153234728.918733.70910@.35g2000cwc.googlegroups.com...
Thanks Dr Tom
Needed the complete sql that has been my bete noire.
at the time the blocking occured
Tom Moreau wrote:
> Try:
> select
> p1.*
> from
> dbo.sysprocesses p1
> where exists
> (
> select
> *
> from
> dbo.sysprocesses p2
> where
> p2.blocked = p1.spid
> )
> and p1.blocked in (0, p1.spid)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <mngong@.gmail.com> wrote in message
> news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
> Can query sysprocesses to get user information .Can find the top
> blocker
> but have spun wheels for a long long time unable to find sql run by top
> blocker.
> Any body done this before or has directions to scripts that can do this
> Your input is highly appreciated
> Massa|||I can't tell if you're using SQL Server 2005 here, but if you are, you might
be interested in the 'blocked process threshold' parameter of sp_configure.
On our testing servers I have a trace going that continually monitors for
these problem areas. What gets returned is an XML block which details the
blocking and blocked processes - very nice indeed. If you're on SQL Server
2000, the sp_blocker_pss80 scripts would be worth looking at
(http://support.microsoft.com/?id=271509).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

help setting up and using SQLXML web service ?

Hi,
I have an app that connects to a remote SQL database using 1433. It
connecting using a locked down user then runs a stored procedure passing it
XML.
Roger Walter pointed me in the direction of SOAP, SQLXML web service.
I installed:
SoapToolkit 2.0
SoapToolkit 3.0
SQLXML 3.0 sp2
Now this is done i don't see any amendments to IIS (restarted) . What should
i see ?
How do i talk to the webservice ?
What should be install in services ?
Was hoping i can just pass user/pass/xml to webservice to run stored
procedures in same way i am at the moment just with slighty connection
changes.
Please help.
ScottI found this:
1.. Download SqlXml 3.0 (this test was done with Sp2 Beta 1).
2.. Run the installation program. You will need to also install the SOAP
toolkit if you want to use the SqlXml SOAP features.
3.. Open the IIS Virtual Directory Management tool under Start ->
Programs -> SqlXml 3.0 -> Configure IIS.
4.. Create a new virtual directory. Enter the information on the General
tab and the Security tab. When you go to the Data Source tab you should be
able to browse the databases on the server. If you can't then you need to
check your security settings.
5.. Turn on Allow sql= queries so that you can test your setup. Make sure
you turn this off when you are done testing.
I am finding however that i cant browse to my index page in the new virual
directory. This index page is basic aspx for login, no sql connection
required.

help setting up and using SQLXML web service ?

Hi,
I have an app that connects to a remote SQL database using 1433. It
connecting using a locked down user then runs a stored procedure passing it
XML.
Roger Walter pointed me in the direction of SOAP, SQLXML web service.
I installed:
SoapToolkit 2.0
SoapToolkit 3.0
SQLXML 3.0 sp2
Now this is done i don't see any amendments to IIS (restarted) . What should
i see ?
How do i talk to the webservice ?
What should be install in services ?
Was hoping i can just pass user/pass/xml to webservice to run stored
procedures in same way i am at the moment just with slighty connection
changes.
Please help.
ScottI found this:
1.. Download SqlXml 3.0 (this test was done with Sp2 Beta 1).
2.. Run the installation program. You will need to also install the SOAP
toolkit if you want to use the SqlXml SOAP features.
3.. Open the IIS Virtual Directory Management tool under Start ->
Programs -> SqlXml 3.0 -> Configure IIS.
4.. Create a new virtual directory. Enter the information on the General
tab and the Security tab. When you go to the Data Source tab you should be
able to browse the databases on the server. If you can't then you need to
check your security settings.
5.. Turn on Allow sql= queries so that you can test your setup. Make sure
you turn this off when you are done testing.
I am finding however that i cant browse to my index page in the new virual
directory. This index page is basic aspx for login, no sql connection
required.

Help select data type

Hi Everyone,

I have a table in my database that stores info to process a 'Parts Rush' request, so a user can get there part expedited. I want to have a field in my table named 'Status'.

The four status' are:

Pending

Approved

Declined

Coachback

The question is should I use integers to represent the status (ie. 1 = Pending) or should I set that field to varchar and store the actual word?

I'm just trying to develop my SQL skills and what to exercise good practices. I am using SQL Server 2005 Express.

Thank You

-Sam

I recommend setting the status field to an int, and having a separate table linking the ints to the status strings - sure you'll have to join, but I believe it's a better practice than flat-out throwing a string in the main table.|||

I agree with making it an integer type over a varchar. In addition, you might want to consider making it either a TINYINT or a SMALLINT to save space if you know that you will never have 255 different specific statuses for TINYINT or 32767 different status for SMALLINT. Use of either of these datatypes will save you some space and over the long pull might slightly improve IO performance.

However, you also need to weigh some compatibility factors. The INTEGER datatype is certainly more universally available in other DBMS implementations. You may not want to use TINYINT or SMALLINT to avoid a few cross-platform issues. I think the more universal type would be the INT type.

Help required!

Hi all

I am creating a web services application in Visual Studio Dot net
which links to a MS SQL database.

I need to create a form which lets the user pick his field of
choice(doesnt matter what it is..) using drop down boxes,radio buttons
etc and query the database according to what he has picked and
retrieve only those columns and rows.

The thing is, I do not want to hardcode the queries using
OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
on the fly, once the user picks his fields from the form.

I'd greatly appreciate any help.

Thanks,
mSeems you are looking to provide ad-hoc reporting capabilities. To avoid
hard-coding queries you would have to access the SQL Server meta-data to
retrieve information about tables and to provide options for users to pick
tables and columns. You can start by looking at the information schema views
in the SQL Server documentation. Here is one example of a query that returns
tables and columns:

SELECT c.table_catalog AS 'Database Name',
c.table_schema AS 'Owner',
c.table_name AS 'Table Name',
c.column_name AS 'Column Name',
c.data_type AS 'Column Data Type'
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON t.table_catalog = c.table_catalog
AND t.table_schema = c.table_schema
AND t.table_name = c.table_name
WHERE t.table_type = 'BASE TABLE'

Since the table and column names may not mean much to users, you may want to
build a mapping table with descriptive names. That involves some hard-coding
and maintenance (best stored in a table that you can join easily) to update
when a new table/column is added or changed, but greatly improves the user
experience.

Next you would allow selection of columns and placing filters (the WHERE
clause of the query). A good idea is to enforce a requirement to have at
least one condition. Users tend to forget about adding filters and could
possibly dump the whole table out. At this point it is very important to
check the column type and perform verification on any parameters entered.

Last is to build a dynamic query and return the results to the user. A good
reading for dynamic queries is Erland Sommarskog's article here:
http://www.sommarskog.se/dynamic_sql.html. Pay attention to the SQL
injection section as this is very common for ad-hoc query systems.

This is a very simplified approach to what you are looking for. It can get a
lot more complex if you need to provide special operations like summary
queries, or if you decide to provide capabilities to join multiple tables.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On 27 Feb 2007 14:40:23 -0800, madhumita.iyengar@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all
>
>I am creating a web services application in Visual Studio Dot net
>which links to a MS SQL database.
>
>I need to create a form which lets the user pick his field of
>choice(doesnt matter what it is..) using drop down boxes,radio buttons
>etc and query the database according to what he has picked and
>retrieve only those columns and rows.
>
>The thing is, I do not want to hardcode the queries using
>OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
>on the fly, once the user picks his fields from the form.
>
>I'd greatly appreciate any help.
>
>Thanks,
>m

In addition to Plamen's suggestions, you might look into the report
builder part of Sql Server Reporting Services. The programmer sets up
the appropriate data model, and the user configures the report. There
is something of a learning curve for the user but designing a report
can be fairly trivial.|||On 27 Feb 2007 14:40:23 -0800, madhumita.iyengar@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all
>
>I am creating a web services application in Visual Studio Dot net
>which links to a MS SQL database.
>
>I need to create a form which lets the user pick his field of
>choice(doesnt matter what it is..) using drop down boxes,radio buttons
>etc and query the database according to what he has picked and
>retrieve only those columns and rows.
>
>The thing is, I do not want to hardcode the queries using
>OleDbDataAdapter or SqlDataAdapter, but want the query to be generated
>on the fly, once the user picks his fields from the form.
>
>I'd greatly appreciate any help.
>
>Thanks,
>m

And you'll likely get more replies if you're specific in your post,
rather than just Help Required!.

help required

i have created registration form which consist of 6 webform, for each user i had given option save and come back later (say on webform3), when userlogin for the second time without complete registration i.e, on the 6th webform i had given save option then only it enters into database. the problem is when user login in for the second time he should be redirected to same page(webform3)

so i need help from urside

I beleive you are saving this information in database table. i don't now what table structure you are using in your application. but i think you are saving that information in user table then just create a web user control and place it on the top of the each page. that user control will check with database that on which page access last time and redirect the request by using response.redirect/server.transfer.

|||

This tutorial video (one of the many excellent tutorials available at www.asp.net) will demonstrate this for you.

http://asp.net/learn/videos/view.aspx?tabid=63&id=47