Max function

Hi,

The question is about the way max function works in SQL. I have to implement a database design where in i have to write a stored procedure which will perform an insert in the table/tables. This table/tables will in the long run have a large number of records, i have been instructed to create another table (lets call it tableA) which will have the primary keys of all the other tables and their respective maximum values being used. While performing an insert into one of these tables, i will have to probe the tableA and find out the currently used id for that table ( in which i have to insert a new row) and use the value for insertion.

According to me, we can use a max clause in the stored procedure and achieve the following or use an Indentity column to do the same.

I will need insights into how max function works when it tells me the number of records (does it traverse thru the entire table or has the number of rows stored somewhere? I just need the number of rows in the table and nothing else but would like to know the max function working)
Also how will identity column perform under these situations.

Any information required, i will be glad to give.

VihangJust use the identity field, it's what it's designed for. If you need a table which reflects the maximum id field in each for some reason (but hopefully you don't any more), then use a view which queries the max(id) field from each table, and a 'union all' to show the lot. Something like this:

create view maxids as
select 'table1' as tablename, max(id) as maxid
from table1
union all
select 'table2' as tablename, max(id) as maxid

from table2
union all
...etc

I hope this helps...

Rob

--
Rob Farley
President - Adelaide SQL Server User Group
rob_farley@.hotmail.com (Email &

Msgr)
http://msmvps.com/blogs/robfarley|||Hi Rob,

Thank you for your prompt input. I appreciate it, I have understood what you have suggested.

I need to understand how does the max function work? does it go thru all the rows to calculate the maximum of a column?.

Regards,
Vihang|||Not when there's a useful index it can use. As these 'id' fields will be your primary keys (if not, they should be!), then the system can very easily find out what the maximum value is. Being identity columns, the system is designed to be able to very quickly report what the largest value is.|||Hi Rob,

Thanks for your insights, I now know how it really works.

Regards,
Vihang

Max degree of recursion

Hi Everyone
As I understand it the maximum call stack level of recursive stored
procedures is 32. Is there at way around this limitation?
I want to make a procedure that traverses a tree structure in my database to
discover if a given node is above the input parameter. Ex:
CREATE TABLE Nodes
(
NodeID int,
ParentNodeID int
)
DECLARE @.MyNodeID int
DECLARE @.ParentNodeID int
DECLARE @.IsParent bit
SET @.MyNodeID = 42
SET @.ParentNodeID = 13
EXEC spIs_Parent @.MyNode, @.ParentNodeID, Is
... What is the best way to implement such a procedure?
--
Yours sincerely
Thomas Kejser
M.Sc, MCDBADo it the sloppy way without using recursion.
Do you have the code?
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)

Matrix Help

I need some help, this is my first matrix report. My stored procedure
pulls data in the following format.
Internal_User user_id expense_user_id Amount
category Period month year
Doe, John 99 1234567 385.7900
Airfare May-2007 5 2007
ETC.
I have the matrix set up currently with Row Groups on Internal_user
and Category, and column group on Period. This looks good, what I need
though is a total by internal user for all the categories. Then I
need a category total in the last column. When I try to do a subtotal
on the Internal_User rowgroup it says total, but only spits out the
first category value.
Heres what it looks like
Jan-2007
Feb-2007 Mar-2007 Apr-2007 May-2007
Doe, John
AirFare
385.79
Entertainment 1587.22
556.2 537.39 1996.30
Taxi 100.00
100.00 200.00
Total 1587.22
556.2 537.39 1996.61 358.79
I want the total like for Jan-2007 for example to be 1687.22. I
appreciate any help.Disregard...found the answer.

Matrix dataset filter out null

I have a dataset which has a ContractNo column which can take a null value. I
cannot carry out the filter on the stored procedure which generates my
dataset.
This dataset is used in a matrix. I need to filter out all rows with a null
ContractNo. I thought I could do this on the matrix properties filter tab
with an expression like:
Fields!ContractNo.Value<>Nothing
However there doeas not appear to be a <> (or not equal to) operator in the
drop-down list in RS2000.
How do I achieve this filter?
What is the != operator?
TIA,
Mageno_manDont know if this will help, but the way I have used to get around it is:
Fields!ContractNo.Value<>Nothing in the formula and make the operator the =and then the value "=true"
Expression Operator
Value
Fields!ContractNo.Value<>Nothing ==true
I hope this makes sense.
"magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
news:75AD1157-EBF6-4E95-8A46-A568A0B5933C@.microsoft.com...
>I have a dataset which has a ContractNo column which can take a null value.
>I
> cannot carry out the filter on the stored procedure which generates my
> dataset.
> This dataset is used in a matrix. I need to filter out all rows with a
> null
> ContractNo. I thought I could do this on the matrix properties filter tab
> with an expression like:
> Fields!ContractNo.Value<>Nothing
> However there doeas not appear to be a <> (or not equal to) operator in
> the
> drop-down list in RS2000.
> How do I achieve this filter?
> What is the != operator?
> TIA,
> Mageno_man|||!= is 'does not equal to'
does it solve your problem? :)
"magendo_man" wrote:
> I have a dataset which has a ContractNo column which can take a null value. I
> cannot carry out the filter on the stored procedure which generates my
> dataset.
> This dataset is used in a matrix. I need to filter out all rows with a null
> ContractNo. I thought I could do this on the matrix properties filter tab
> with an expression like:
> Fields!ContractNo.Value<>Nothing
> However there doeas not appear to be a <> (or not equal to) operator in the
> drop-down list in RS2000.
> How do I achieve this filter?
> What is the != operator?
> TIA,
> Mageno_man|||Had to put an equals sign in expression, i.e expression is
=Fields!ContractNo.Value<>Nothing
"Ben Watts" wrote:
> Dont know if this will help, but the way I have used to get around it is:
> Fields!ContractNo.Value<>Nothing in the formula and make the operator the => and then the value "=true"
> Expression Operator
> Value
> Fields!ContractNo.Value<>Nothing => =true
> I hope this makes sense.
>
> "magendo_man" <magendoman@.discussions.microsoft.com> wrote in message
> news:75AD1157-EBF6-4E95-8A46-A568A0B5933C@.microsoft.com...
> >I have a dataset which has a ContractNo column which can take a null value.
> >I
> > cannot carry out the filter on the stored procedure which generates my
> > dataset.
> >
> > This dataset is used in a matrix. I need to filter out all rows with a
> > null
> > ContractNo. I thought I could do this on the matrix properties filter tab
> > with an expression like:
> >
> > Fields!ContractNo.Value<>Nothing
> >
> > However there doeas not appear to be a <> (or not equal to) operator in
> > the
> > drop-down list in RS2000.
> >
> > How do I achieve this filter?
> >
> > What is the != operator?
> >
> > TIA,
> > Mageno_man
>
>

Matching Collation and Sort

I am attempting to match the following collation and sort. I cannot seem to
make the right choices to prevent collation conflicts in my stored
procedures. Can ANYONE please give me some insight on what options to select
in order to match this in SQL 2000 Service pack 3? The server that produced
the info below (by running sp_helpsort) is a SQL 2000 SP3 server as well.
The closest I've been able to come at this point is to match everything
except the accent-sensative selection.
THANKS IN ADVANCE!
Skip
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode DataYou can run select serverproperty('Collation') to get the server collation
and select databasepropertyex('dbname','Collation') to get the database
collation. Have you moved a database from one server to another with a
different collation ?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>I am attempting to match the following collation and sort. I cannot seem to
>make the right choices to prevent collation conflicts in my stored
>procedures. Can ANYONE please give me some insight on what options to
>select in order to match this in SQL 2000 Service pack 3? The server that
>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>as well.
> The closest I've been able to come at this point is to match everything
> except the accent-sensative selection.
> THANKS IN ADVANCE!
> Skip
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
>|||Thanks Jasper. My issue is not identifying the collation. The issue is
matching the collation on a new sql box to which I will be moving the
database to. Notice the vagaries in the collation properties at the bottom
of my original note. There is no predefined collation in SQL that matches
this one nor can you get it right by using the collation designer. I know,
I've rebuilt the master on that thing numerous times now. The closest I've
been able to come is this:
Current Production Server:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
1252 for non-Unicode Data
New (Soon to be I hope) Production Server
Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive,
width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
1252 for non-Unicode Data
Notice the accent-sensitivity and the SQL Server Sort order differences.
Also, when you use the collation designer there is no way to specify the SQL
Server Sort order either.
This is the problem I'm trying to address...Skip
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> You can run select serverproperty('Collation') to get the server collation
> and select databasepropertyex('dbname','Collation') to get the database
> collation. Have you moved a database from one server to another with a
> different collation ?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server that
>>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>>as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
>> 1252 for non-Unicode Data
>>
>|||I just installed a named instance and the output of sp_helpsort matches your
current production server and the collation I chose was
SQL_Latin1_General_CP1_CI_AS (the default sql collation during install). The
descriptive collation description during install was (under the SQL
Collations bit) "Dictionary order,case-insensitive,for use with the 1252
Character set"
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skip B" <skip@.theborlands.com> wrote in message
news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>
> Thanks Jasper. My issue is not identifying the collation. The issue is
> matching the collation on a new sql box to which I will be moving the
> database to. Notice the vagaries in the collation properties at the bottom
> of my original note. There is no predefined collation in SQL that matches
> this one nor can you get it right by using the collation designer. I know,
> I've rebuilt the master on that thing numerous times now. The closest I've
> been able to come is this:
>
> Current Production Server:
> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
> 1252 for non-Unicode Data
> New (Soon to be I hope) Production Server
> Latin1-General, case-insensitive, accent-insensitive,
> kanatype-insensitive,
> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
> 1252 for non-Unicode Data
> Notice the accent-sensitivity and the SQL Server Sort order differences.
> Also, when you use the collation designer there is no way to specify the
> SQL Server Sort order either.
> This is the problem I'm trying to address...Skip
>
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get the
>> database collation. Have you moved a database from one server to another
>> with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server that
>>produced the info below (by running sp_helpsort) is a SQL 2000 SP3 server
>>as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
>> Sort Order 52 on Code Page 1252 for non-Unicode Data
>>
>>
>|||Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>I just installed a named instance and the output of sp_helpsort matches
>your current production server and the collation I chose was
>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>The descriptive collation description during install was (under the SQL
>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>Character set"
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Skip B" <skip@.theborlands.com> wrote in message
> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Jasper. My issue is not identifying the collation. The issue is
>> matching the collation on a new sql box to which I will be moving the
>> database to. Notice the vagaries in the collation properties at the
>> bottom of my original note. There is no predefined collation in SQL that
>> matches this one nor can you get it right by using the collation
>> designer. I know, I've rebuilt the master on that thing numerous times
>> now. The closest I've been able to come is this:
>>
>> Current Production Server:
>> Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page
>> 1252 for non-Unicode Data
>> New (Soon to be I hope) Production Server
>> Latin1-General, case-insensitive, accent-insensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page
>> 1252 for non-Unicode Data
>> Notice the accent-sensitivity and the SQL Server Sort order differences.
>> Also, when you use the collation designer there is no way to specify the
>> SQL Server Sort order either.
>> This is the problem I'm trying to address...Skip
>>
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get the
>> database collation. Have you moved a database from one server to another
>> with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot seem
>>to make the right choices to prevent collation conflicts in my stored
>>procedures. Can ANYONE please give me some insight on what options to
>>select in order to match this in SQL 2000 Service pack 3? The server
>>that produced the info below (by running sp_helpsort) is a SQL 2000 SP3
>>server as well.
>> The closest I've been able to come at this point is to match everything
>> except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
>> Sort Order 52 on Code Page 1252 for non-Unicode Data
>>
>>
>>
>|||Done deal. Thanks for your help, Jasper...Skip
"Skip B" <skip@.theborlands.com> wrote in message
news:unFmOjcUFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Great. I'm rebuilding the master now. I'll let you know shortly. Thanks!
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OKFG0WcUFHA.628@.TK2MSFTNGP09.phx.gbl...
>>I just installed a named instance and the output of sp_helpsort matches
>>your current production server and the collation I chose was
>>SQL_Latin1_General_CP1_CI_AS (the default sql collation during install).
>>The descriptive collation description during install was (under the SQL
>>Collations bit) "Dictionary order,case-insensitive,for use with the 1252
>>Character set"
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:eWw1aLcUFHA.2444@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Jasper. My issue is not identifying the collation. The issue is
>> matching the collation on a new sql box to which I will be moving the
>> database to. Notice the vagaries in the collation properties at the
>> bottom of my original note. There is no predefined collation in SQL that
>> matches this one nor can you get it right by using the collation
>> designer. I know, I've rebuilt the master on that thing numerous times
>> now. The closest I've been able to come is this:
>>
>> Current Production Server:
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code
>> Page
>> 1252 for non-Unicode Data
>> New (Soon to be I hope) Production Server
>> Latin1-General, case-insensitive, accent-insensitive,
>> kanatype-insensitive,
>> width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code
>> Page
>> 1252 for non-Unicode Data
>> Notice the accent-sensitivity and the SQL Server Sort order differences.
>> Also, when you use the collation designer there is no way to specify the
>> SQL Server Sort order either.
>> This is the problem I'm trying to address...Skip
>>
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:ejT%23BFcUFHA.1896@.TK2MSFTNGP14.phx.gbl...
>> You can run select serverproperty('Collation') to get the server
>> collation and select databasepropertyex('dbname','Collation') to get
>> the database collation. Have you moved a database from one server to
>> another with a different collation ?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Skip B" <skip@.theborlands.com> wrote in message
>> news:uA9$M9aUFHA.2124@.TK2MSFTNGP14.phx.gbl...
>>I am attempting to match the following collation and sort. I cannot
>>seem to make the right choices to prevent collation conflicts in my
>>stored procedures. Can ANYONE please give me some insight on what
>>options to select in order to match this in SQL 2000 Service pack 3?
>>The server that produced the info below (by running sp_helpsort) is a
>>SQL 2000 SP3 server as well.
>> The closest I've been able to come at this point is to match
>> everything except the accent-sensative selection.
>> THANKS IN ADVANCE!
>> Skip
>> Latin1-General, case-insensitive, accent-sensitive,
>> kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
>> Sort Order 52 on Code Page 1252 for non-Unicode Data
>>
>>
>>
>>
>

Master-Detail w/Gridview-DetailsView Stored Procedure Problem

I am attempting to setup a Master-Details with GridView/DetailsView but I can't seem to find any information on using a stored procedure that requires parameters with the SqlDataSource control.

SelectCommandType specifies that you are using a stored proc. SelectCommand specifies the name of the proc, but I haven't found any information on how to pass a parameter to the stored procedure.

Is it even possible or do I have to forget about using the DetailsView control altogether?

you should use an sql parameter like this in your sql data source:

here a dropdown list value is taken as a parameter:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:urConnString %>"

SelectCommand="urSP"SelectCommandType="StoredProcedure"EnableCaching="True">

<SelectParameters>

<asp:ControlParameterControlID="dropdown"Name="urparamname"PropertyName="SelectedValue"

Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

hope this helps.

|||

Thanks Raj, but I have already done that. My code, as you suggested, looks like this:

<asp:SqlDataSource ID="DetailsViewSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ForexturtleConnectionString %>"
DeleteCommand="swsp_DeleteCompany" DeleteCommandType="StoredProcedure" InsertCommand="swsp_InsertCompany"
InsertCommandType="StoredProcedure"SelectCommand="swsp_GetCompany" SelectCommandType="StoredProcedure"
UpdateCommand="swsp_UpdateCompany" UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="swCompanyId" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>

The stored procedure (swsp_GetCompany) requires the record ID as a parameter to grab the correct record. It works great against the database. GridView1 had previously selected the correct record, but I don't see how it supplies swCompanyId to DetailsViewSqlDataSource:

<asp:SqlDataSource ID="GridViewSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ForexturtleConnectionString %>"
SelectCommand="swsp_GetCompanies" SelectCommandType="StoredProcedure"></asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="swCompanyId" DataSourceID="GridViewSqlDataSource">
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="swCompanyId" HeaderText="ID" InsertVisible="False" ReadOnly="True"
SortExpression="swCompanyId" />
<asp:BoundField DataField="swName" HeaderText="Company" SortExpression="swName" />
</Columns>
</asp:GridView>

I still get an error that says, "Procedure or function 'swsp_GetCompany' expects parameter '@.id', which was not supplied."

|||

I am not sure as how to pass the value from a gridview to detailsview as a parameter...

one thing u can check is the name of the parameter... if u have used "@.id" in ur SP, then try to use the same name from .aspx page also...but here u r passing 'swcompanyID' which is not expected by the SP...

<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="swCompanyId" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>

hope this helps you...

|||

Thanks so much for your help! As it turned out, I modified the name of the parameter in the stored procedure from @.id to @.swCompanyId and everything worked great. Thanks again for your help.

Master-Detail Searching, best approach?

Hello, I'm relatively new to using full-text search. I'm working on a =
document management program.
Basically my data it stored in two tables, [Documents], and =
[DocDetails], with a one-to-many relationship. [Documents] contains a =
record for each document stored, and [DocDetails] has many records =
concerning a document, each with an associated text field.
I'm really after the ability to search on a query '"jane" and "doe"', =
where I would be able to pull from [Documents] all documents that have =
matching records in [DocDetails]. My problem comes in how to query =
[DocDetails], some records would have "jane" in them, and some records =
would have "doe" in them, never would you see one record in [DocDetails] =
having both terms in the same text field. (I think this kinda negates =
using a strategy where the query has an "and" or "or" in it.
Maybe there is a way around this, I just haven't thought of. Right now, =
the only thing I can this is to split up my queries into only one word, =
and join the results.
If someone could give me a nudge in the right direction I'd appreciate =
it!
Thanks!,
--Michael
Raterus,
The below code was posted recently in regards to a "Parent - Child"
relationship, and should also work with your "Master - Detail" searching.
Tables:
contact(contact_id, name)
role(role_id, description)
contact_role (contact_id, role_id)
How can I find all contact where the search string is in the contact name
and or roles description? This is the quick and simplest way. You can
rewrite this query using outer-joins. The code is not tested. So expect some
syntax errors.
select * from contact where contactid in
(select contactid from contact_role where contactid in (select key from
containstable(contact,name,'search string') or
roleid in (select key from containstable(role,description,'search
string'))
You should be able to alter the above code to fit your tables. If you have
an alternative approach or additional questions, please post them here..
Regards,
John
"Raterus" <raterus@.spam.org> wrote in message
news:OI#yuv1NEHA.556@.TK2MSFTNGP10.phx.gbl...
Hello, I'm relatively new to using full-text search. I'm working on a
document management program.
Basically my data it stored in two tables, [Documents], and [DocDetails],
with a one-to-many relationship. [Documents] contains a record for each
document stored, and [DocDetails] has many records concerning a document,
each with an associated text field.
I'm really after the ability to search on a query '"jane" and "doe"', where
I would be able to pull from [Documents] all documents that have matching
records in [DocDetails]. My problem comes in how to query [DocDetails],
some records would have "jane" in them, and some records would have "doe" in
them, never would you see one record in [DocDetails] having both terms in
the same text field. (I think this kinda negates using a strategy where the
query has an "and" or "or" in it.
Maybe there is a way around this, I just haven't thought of. Right now, the
only thing I can this is to split up my queries into only one word, and join
the results.
If someone could give me a nudge in the right direction I'd appreciate it!
Thanks!,
--Michael
|||Actually that was the first post I read before I posted, it helped =
initally. I've adapted it a little bit and have come up with this. =
It's a mess, I know it :-) It is working how I want it to though, the =
only problem I forsee, is how do I have different queries for a =
different number of search terms. Using this strategy I'll have to have =
a different query if they search for "jane doe john brown" rather than =
just "jane doe" This is all for an asp.net web application, so it =
wouldn't be that difficult for me to create my own query on the fly and =
send it over, but if there is a better way, I'd love to hear it!
select * from documents where docID in
(
select t1.docID
from (
select docID
from docdetails
where ddID in
(
select [key]
from containstable(docdetails,value,'"jane"')
)
) as t1 inner join=20
(
select docID
from docdetails
where ddID in
(
select [key]
from containstable(docdetails,value,'"doe"')
)
) as t2 on t1.docID =3D t2.docID
)
"John Kane" <jt-kane@.comcast.net> wrote in message =
news:%235axQM2NEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Raterus,
> The below code was posted recently in regards to a "Parent - Child"
> relationship, and should also work with your "Master - Detail" =
searching.
>=20
> Tables:
> contact(contact_id, name)
> role(role_id, description)
> contact_role (contact_id, role_id)
>=20
> How can I find all contact where the search string is in the contact =
name
> and or roles description? This is the quick and simplest way. You can
> rewrite this query using outer-joins. The code is not tested. So =
expect some
> syntax errors.
>=20
> select * from contact where contactid in
> (select contactid from contact_role where contactid in (select key =
from
> containstable(contact,name,'search string') or
> roleid in (select key from containstable(role,description,'search
> string'))
>=20
> You should be able to alter the above code to fit your tables. If you =
have
> an alternative approach or additional questions, please post them =
here..
>=20
> Regards,
> John
>=20
>=20
>=20
>=20
> "Raterus" <raterus@.spam.org> wrote in message
> news:OI#yuv1NEHA.556@.TK2MSFTNGP10.phx.gbl...
> Hello, I'm relatively new to using full-text search. I'm working on a
> document management program.
>=20
> Basically my data it stored in two tables, [Documents], and =
[DocDetails],
> with a one-to-many relationship. [Documents] contains a record for =
each
> document stored, and [DocDetails] has many records concerning a =
document,
> each with an associated text field.
>=20
> I'm really after the ability to search on a query '"jane" and "doe"', =
where
> I would be able to pull from [Documents] all documents that have =
matching
> records in [DocDetails]. My problem comes in how to query =
[DocDetails],
> some records would have "jane" in them, and some records would have =
"doe" in
> them, never would you see one record in [DocDetails] having both terms =
in
> the same text field. (I think this kinda negates using a strategy =
where the
> query has an "and" or "or" in it.
>=20
> Maybe there is a way around this, I just haven't thought of. Right =
now, the
> only thing I can this is to split up my queries into only one word, =
and join
> the results.
>=20
> If someone could give me a nudge in the right direction I'd appreciate =
it!
>=20
> Thanks!,
> --Michael
>=20
>

master.dbo.xp_fixeddrives

SQL Server 2000
master.dbo.xp_fixeddrives stored procedure is reporting different values
when executed by 'sa' and a regular user.
Results:
(When executed by 'sa')
drive MB free
-- --
C 18432
D 118784
E 16384
(3 row(s) affected)
(When executed by 'UserA')
drive MB free
-- --
C 8192
D 68608
E 3072
(3 row(s) affected)
Is there any reason for this difference?
Thanks,
--payyans
sorry this might be stating the obvious but,
ensure that UserA and sa are logged on to the same server.
"payyans" wrote:

> SQL Server 2000
> --
> master.dbo.xp_fixeddrives stored procedure is reporting different values
> when executed by 'sa' and a regular user.
> Results:
> (When executed by 'sa')
> drive MB free
> -- --
> C 18432
> D 118784
> E 16384
> (3 row(s) affected)
> (When executed by 'UserA')
> drive MB free
> -- --
> C 8192
> D 68608
> E 3072
> (3 row(s) affected)
> Is there any reason for this difference?
> Thanks,
> --payyans
>

master.dbo.xp_fixeddrives

SQL Server 2000
--
master.dbo.xp_fixeddrives stored procedure is reporting different values
when executed by 'sa' and a regular user.
Results:
(When executed by 'sa')
drive MB free
-- --
C 18432
D 118784
E 16384
(3 row(s) affected)
(When executed by 'UserA')
drive MB free
-- --
C 8192
D 68608
E 3072
(3 row(s) affected)
Is there any reason for this difference?
Thanks,
--payyanssorry this might be stating the obvious but,
ensure that UserA and sa are logged on to the same server.
"payyans" wrote:

> SQL Server 2000
> --
> master.dbo.xp_fixeddrives stored procedure is reporting different values
> when executed by 'sa' and a regular user.
> Results:
> (When executed by 'sa')
> drive MB free
> -- --
> C 18432
> D 118784
> E 16384
> (3 row(s) affected)
> (When executed by 'UserA')
> drive MB free
> -- --
> C 8192
> D 68608
> E 3072
> (3 row(s) affected)
> Is there any reason for this difference?
> Thanks,
> --payyans
>