Hello All!
I know I can do this, but not sure of the best way. I have a users table,
with a userID as key. I also have a profile table. When a user logs in,
they can add to thier profile if they want. My question is, What is the bes
t
way to link that user ID so the user ID is filled in on the userID col in th
e
profile table, and the user profile sticks to that ID. Even if the user
comes back to add to it later.
I think I would have to return the value of the user ID, pass that forward
to the profile table, but I'm not sure.
TIA!!!
RudyRudy wrote:
> Hello All!
> I know I can do this, but not sure of the best way. I have a users
> table, with a userID as key. I also have a profile table. When a
> user logs in, they can add to thier profile if they want. My
> question is, What is the best way to link that user ID so the user ID
> is filled in on the userID col in the profile table, and the user
> profile sticks to that ID. Even if the user comes back to add to it
> later.
> I think I would have to return the value of the user ID, pass that
> forward to the profile table, but I'm not sure.
> TIA!!!
>
> Rudy
Create Table MyUsers (
UserID INT IDENTITY NOT NULL PRIMARY KEY,
UserName NVARCHAR(50))
Create Table UserProfiler (
UserID INT NOT NULL REFERENCES MyUsers(UserID),
OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
OptionValue NVARCHAR(30) NOT NULL,
PRIMARY KEY CLUSTERED (UserID, OptionID) )
Not sure of your design, but assuming you had a relationship like the
above, you need to physically insert the UserID into the UserProfile
table. There is not way for SQL Server to know what UserID you want
interted, unless you're talking about a login name (are you?).
For a login name you could use suser_sname() and have it as the default
on the table:
Create Table UserProfile (
UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
OptionValue NVARCHAR(30) NOT NULL,
PRIMARY KEY CLUSTERED (UserID, OptionID) )
and then use:
Insert UserProfile (
OptionID, OptionValue)
Values (
50, N'Profiler Data')
if the user id is something your database stores separately from the
login name, you would need to send the value to SQL Server. So you might
grab the UserID when the user logs into the application and pass it to
the insert statement or pass it to a stored procedure to be inserted
into the profile table.
David Gugick
Imceda Software
www.imceda.com|||Hi David!
Thank you for the quick reply. So I am talking about a login name, and the
user ID is on the same table as the user name.
For a login name you could use suser_sname() and have it as the default
> on the table:
> Create Table UserProfile (
> UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
I don't understand what you mean by the suser_sname as the default. It's
been awhile since I had to work with SQL, and was just learning at that time
.
Now that I need to use SQL a little bit more indepth than just making simple
tables, and passing values back and forth, I'm kinda in the weeds if you kno
w
what I mean. LOL
So now that I have set you up for my ignorance, how does one refrence? I
know about relationships and stuff, sorta. And I know I can use views to hav
e
data update automaticly from other tables. And views can be used just like
tables, right? Would I create a FK between the two tables using the userID?
But that doesn't update or keep the information of the userID the same, does
it?
I though if I could just return a value to what user was logged on, and then
that userID would link with the profile table, andthen the info can be
update. Maybe it would be easier if I had a table for just users who are
logged on?
Am I way off base or what?
Thank you for your time David!
Rudy
"David Gugick" wrote:
> Rudy wrote:
> Create Table MyUsers (
> UserID INT IDENTITY NOT NULL PRIMARY KEY,
> UserName NVARCHAR(50))
> Create Table UserProfiler (
> UserID INT NOT NULL REFERENCES MyUsers(UserID),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
>
> Not sure of your design, but assuming you had a relationship like the
> above, you need to physically insert the UserID into the UserProfile
> table. There is not way for SQL Server to know what UserID you want
> interted, unless you're talking about a login name (are you?).
> For a login name you could use suser_sname() and have it as the default
> on the table:
> Create Table UserProfile (
> UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
> and then use:
> Insert UserProfile (
> OptionID, OptionValue)
> Values (
> 50, N'Profiler Data')
>
> if the user id is something your database stores separately from the
> login name, you would need to send the value to SQL Server. So you might
> grab the UserID when the user logs into the application and pass it to
> the insert statement or pass it to a stored procedure to be inserted
> into the profile table.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Rudy wrote:
> I don't understand what you mean by the suser_sname as the default.
suser_sname() is a function that returns the logged in user name.
My example showed a PK/FK reference. And as I mentioned, the FK value
does not update automatically, it just enforces values based on the
available PK values in the referenced table.
I think you need to spell out in a clear and concise way exactly what
you are trying to do, what all the data means, etc.
David Gugick
Imceda Software
www.imceda.com
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Friday, March 30, 2012
Newbie Question on searching text
I have a bulletin board application (written by a programmer) and want to
allow users to search the database for keywords/phrases. Doing a basic
search eats up the cpu like crazy, and I know there are some strategies on
how to get this done right.
Can someone provide me with an overview of how best to go about this? If
there are good articles/tutorials on this I'd greatly appreciate it also.
Btw, I'm running MS SQL server 2000 running dotnet.
Shabam,
A good place to start to understand SQL Server 2000 Full-text Search (FTS)
is Books Online (BOL) titles: "Full-Text Query Architecture", "Maintaining
Full-Text Indexes", "Using the CONTAINSTABLE and FREETEXTTABLE Rowset-valued
Functions" and especially "Full-Text Search Recommendations". You can also
search the BOL using "full text" (include the double quotes) via the BOL
Search tab for additional titles.
When you state that your "basic search" eats up CPU like crazy, are you
currently using FTS or are you using T-SQL LIKE or some other method? I've
also attached a SQL script file (Full Text Population Example.sql) that
demonstrates all aspects of using SQL FTS on the pubs database table
pub_info.
If you have additional questions, please post them.
Thanks,
John
"Shabam" <blislecp@.hotmail.com> wrote in message
news:UM2dnfovDquwefrcRVn-oA@.adelphia.com...
> I have a bulletin board application (written by a programmer) and want to
> allow users to search the database for keywords/phrases. Doing a basic
> search eats up the cpu like crazy, and I know there are some strategies on
> how to get this done right.
> Can someone provide me with an overview of how best to go about this? If
> there are good articles/tutorials on this I'd greatly appreciate it also.
> Btw, I'm running MS SQL server 2000 running dotnet.
>
begin 666 Full Text Population Example.sql
M#0HM+0T*+2TM(%1O($5N86)L92!T:&4@.4'5B<R!$871A8F%S9 2!F;W(@.1G5L
M;"U497AT#0HM+0T*=7-E('!U8G,-"F=O#0IS<%]F=6QL=&5X=%]S97)V:6-E
M("=C;&5A;E]U<"<-"F=O#0IS<%]F=6QL=&5X=%]D871A8F%S92 G96YA8FQE
M)R M+2 M+3X@.3D]413H@.3VYL>2!R=6X@.=&AI<R!/3D-%('!E<B!D871A8F%S
M92 A(2$-"F=O#0H-"BTM#0HM+2T@.5&\@.0W)E871E+U)E;6]V92!T:&4@.17AI
M<W1I;F<@.1G5L;"U497AT(%1A8FQE($EN9&5X+"!#871A;&]G( T*+2T@.(" @.
M268@.1G5L;"U497AT($EN9&5X(&5X:7-T<RP@.1%)/4"!T:&%T($EN9&5X+ T*
M+2T@.(" @.268@.1G5L;"U497AT($EN9&5X(&1O97,@.;F]T(&5X:7-T+"!#4D5!
M5$4@.=&AA="!);F1E>"X-"BTM#0IU<V4@.<'5B<PT*9V\-"DE&($]"2D5#5%!2
M3U!%4E19("@.@.;V)J96-T7VED*"=P=6)?:6YF;R<I+"=486)L94AA<T%C=&EV
M949U;&QT97AT26YD97@.G*2 ](#$-"D)%1TE.#0H@.(" @.<')I;G0@.)U1A8FQE
M('!U8E]I;F9O(&ES($9U;&PM5&5X="!%;F%B;&5D+"!D<F]P<&EN9R!&=6QL
M+51E>'0@.26YD97@.@.)B!#871A;&]G+BXN)PT*(" @.($5814,@.<W!?9G5L;'1E
M>'1?=&%B;&4@.)W!U8E]I;F9O)RP@.)V1R;W G#0H@.(" @.15A%0R!S<%]F=6QL
M=&5X=%]C871A;&]G("=0=6));F9O)RP@.)V1R;W G#0I%3D0-"D5,4T4@.248@.
M3T)*14-44%)/4$525%D@.*"!O8FIE8W1?:60H)W!U8E]I;F9O)RDL)U1A8FQE
M2&%S06-T:79E1G5L;'1E>'1);F1E>"<I(#T@., T*0D5'24X-"B @.("!P<FEN
M=" G5&%B;&4@.<'5B7VEN9F\@.:7,@.3D]4($9U;&PM5&5X="!%;F%B;&5D+"!C
M<F5A=&EN9R!&5"!#871A;&]G+"!);F1E>" F($%C=&EV871I;F<N+BXG#0H@.
M(" @.15A%0R!S<%]F=6QL=&5X=%]C871A;&]G("=0=6));F9O)RP@.)V-R96%T
M92<-"B @.("!%6$5#('-P7V9U;&QT97AT7W1A8FQE("=P=6)?:6YF;R<L("=C
M<F5A=&4G+" G4'5B26YF;R<L("=54$M#3%]P=6)I;F9O)PT*(" @.($5814,@.
M<W!?9G5L;'1E>'1?8V]L=6UN("=P=6)?:6YF;R<L("=P=6)?:60G+" G861D
M)PT*(" @.($5814,@.<W!?9G5L;'1E>'1?8V]L=6UN("=P=6)?:6YF;R<L("=P
M<E]I;F9O)RP@.)V%D9"<-"B @.("!%6$5#('-P7V9U;&QT97AT7W1A8FQE("=P
M=6)?:6YF;R<L("=A8W1I=F%T92<-"D5.1 T*#0H-"BTM#0HM+2T@.069T97(@.
M16YA8FQI;F<@.)B!!8W1I=F%T:6YG(%1A8FQE<RP@.0V]L=6UN<R F($EN9&5X
M97,@.+2!3=&%R="!&=6QL(%!O<'5L871I;VX-"BTM#0IU<V4@.<'5B<PT*9V\-
M"D)%1TE.#0I3150@.3D]#3U5.5"!/3@.T*1$5#3$%212! 8F5G:6X@.9&%T971I
M;64-"D1%0TQ!4D4@.0&5N9"!D871E=&EM90T*4T54($!B96=I;B ]($-54E)%
M3E1?5$E-15-404U0#0I%6$5#('-P7V9U;&QT97AT7V-A=&%L;V<@.)U!U8DEN
M9F\G+" G<W1A<G1?9G5L;"<@.+2T@.(D9U;&P@.0W)A=VPB#0HM+2!%6$5#( '-P
M7V9U;&QT97AT7V-A=&%L;V<@.)U!U8DEN9F\G+" G<W1A<G1?:6YC<F5M96YT
M86PG("TM("));F-R96UE;G1A;"!#<F%W;"(-"BTM#0HM+2T@.5V%I="!F;W(@.
M8W)A=VP@.=&\@.8V]M<&QE=&4-"BTM#0I$14-,05)%($!S=&%T=7,@.:6YT+"!
M:71E;4-O=6YT(&EN="P@.0&ME>4-O=6YT(&EN="P@.0&EN9&5X4VEZ92!I;G0-
M"E-%3$5#5"! <W1A='5S(#T@.1G5L;%1E>'1#871A;&]G4')O<&5R='DH)U!U
M8DEN9F\G+" G<&]P=6QA=&5S=&%T=7,G*0T*5TA)3$4@.*$!S=&%T=7,@./#X@.
M,"D-"D)%1TE.#0H@.(%=!251&3U(@.1$5,05D@.)S P.C P.C Q)R M+2!W86ET
M(&9O<B Q('-E8V]N9"!B969O<F4@.8VAE8VMI;F<@.1E0@.4&]P=6QA=&5S=&%T
M=7,N+BX-"B @.4T5,14-4($!S=&%T=7,@./2!&=6QL5&5X=$-A=&%L;V=0<F]P
M97)T>2@.G4'5B26YF;R<L("=P;W!U;&%T97-T871U<R<I#0I%3D0-"E-%5"!
M96YD(#T@.0U524D5.5%]424U%4U1!35 -"E=!251&3U(@.1$5,05D@.)S P.C P
M.C$U)R M+2!W86ET(&9O<B Q-2!S96-O;F1S(&EN(&]R9&5R('1O(&=E="!C
M;W)R96-T($94(%!R;W!E<G1Y(&EN9F\N+BX-"E-%5"! :71E;4-O=6YT(#T@.
M1G5L;%1E>'1#871A;&]G4')O<&5R='DH)U!U8DEN9F\G+" G:71E;6-O=6YT
M)RD-"E-%5"! :V5Y0V]U;G0@./2!&=6QL5&5X=$-A=&%L;V=0<F]P97)T>2@.G
M4'5B26YF;R<L("=U;FEQ=65K97EC;W5N="<I#0I3150@.0&EN9 &5X4VEZ92 ]
M($9U;&Q497AT0V%T86QO9U!R;W!E<G1Y*"=0=6));F9O)RP@.) VEN9&5X<VEZ
M92<I#0I04DE.5"!#3TY615)4*&-H87(H,S I+"! 8F5G:6XL(#DI("L@.8VAA
M<B@.P.2D@.*PT*(" @.(" @.0T].5D525"AC:&%R*#,P*2P@.0&5N9"P@..2D@.*R!C
M:&%R*# Y*2 K#0H@.(" @.("!#3TY615)4*&-H87(H,S I+"! 96YD("T@.0&)E
M9VEN+" X*2 K(&-H87(H,#DI("L-"B @.(" @.($-/3E9%4E0H8VAA<B@.S,"DL
M($1!5$5$249&("AH:"P@.0&)E9VEN+"! 96YD*2D@.*R!C:&%R*# Y*2 K#0H@.
M(" @.("!#3TY615)4*&-H87(H,S I+"!$051%1$E&1B H;6DL($!B96=I;BP@.
M0&5N9"DI("L@.8VAA<B@.P.2D@.*PT*(" @.(" @.0T].5D525"AC:&%R*#,P*2P@.
M1$%4141)1D8@.*'-S+"! 8F5G:6XL($!E;F0I*2 K(&-H87(H,#DI("L-"B @.
M(" @.($-/3E9%4E0H=F%R8VAA<B@.Q,"DL($!I=&5M0V]U;G0I("L@.8VAA<B@.P
M.2D@.*PT*(" @.(" @.0T].5D525"AV87)C:&%R*#$P*2P@.0&ME>4-O=6YT*2 K
M(&-H87(H,#DI("L-"B @.(" @.($-/3E9%4E0H=F%R8VAA<B@.Q,"DL($!I;F1E
M>%-I>F4I#0I3150@.3D]#3U5.5"!/1D8-"D5.1 T*9V\-"@.T*+2T-"BTM+2!#
M;VYF:7)M(&%B;W9E(')E<W5L=',@.=VET:#H-"BTM#0I314Q%0U0@.<'5B7VED
M+"!P<E]I;F9O( T*"4923TT@.<'5B7VEN9F\@.5TA%4D4@.0T].5$%)3E,H<')?
8:6YF;RP@.)R)B;V]K*B(G*0T*9V\-"@.T*
`
end
|||can you post your query here?
Also is this an English language search?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Shabam" <blislecp@.hotmail.com> wrote in message
news:UM2dnfovDquwefrcRVn-oA@.adelphia.com...
> I have a bulletin board application (written by a programmer) and want to
> allow users to search the database for keywords/phrases. Doing a basic
> search eats up the cpu like crazy, and I know there are some strategies on
> how to get this done right.
> Can someone provide me with an overview of how best to go about this? If
> there are good articles/tutorials on this I'd greatly appreciate it also.
> Btw, I'm running MS SQL server 2000 running dotnet.
>
sql
allow users to search the database for keywords/phrases. Doing a basic
search eats up the cpu like crazy, and I know there are some strategies on
how to get this done right.
Can someone provide me with an overview of how best to go about this? If
there are good articles/tutorials on this I'd greatly appreciate it also.
Btw, I'm running MS SQL server 2000 running dotnet.
Shabam,
A good place to start to understand SQL Server 2000 Full-text Search (FTS)
is Books Online (BOL) titles: "Full-Text Query Architecture", "Maintaining
Full-Text Indexes", "Using the CONTAINSTABLE and FREETEXTTABLE Rowset-valued
Functions" and especially "Full-Text Search Recommendations". You can also
search the BOL using "full text" (include the double quotes) via the BOL
Search tab for additional titles.
When you state that your "basic search" eats up CPU like crazy, are you
currently using FTS or are you using T-SQL LIKE or some other method? I've
also attached a SQL script file (Full Text Population Example.sql) that
demonstrates all aspects of using SQL FTS on the pubs database table
pub_info.
If you have additional questions, please post them.
Thanks,
John
"Shabam" <blislecp@.hotmail.com> wrote in message
news:UM2dnfovDquwefrcRVn-oA@.adelphia.com...
> I have a bulletin board application (written by a programmer) and want to
> allow users to search the database for keywords/phrases. Doing a basic
> search eats up the cpu like crazy, and I know there are some strategies on
> how to get this done right.
> Can someone provide me with an overview of how best to go about this? If
> there are good articles/tutorials on this I'd greatly appreciate it also.
> Btw, I'm running MS SQL server 2000 running dotnet.
>
begin 666 Full Text Population Example.sql
M#0HM+0T*+2TM(%1O($5N86)L92!T:&4@.4'5B<R!$871A8F%S9 2!F;W(@.1G5L
M;"U497AT#0HM+0T*=7-E('!U8G,-"F=O#0IS<%]F=6QL=&5X=%]S97)V:6-E
M("=C;&5A;E]U<"<-"F=O#0IS<%]F=6QL=&5X=%]D871A8F%S92 G96YA8FQE
M)R M+2 M+3X@.3D]413H@.3VYL>2!R=6X@.=&AI<R!/3D-%('!E<B!D871A8F%S
M92 A(2$-"F=O#0H-"BTM#0HM+2T@.5&\@.0W)E871E+U)E;6]V92!T:&4@.17AI
M<W1I;F<@.1G5L;"U497AT(%1A8FQE($EN9&5X+"!#871A;&]G( T*+2T@.(" @.
M268@.1G5L;"U497AT($EN9&5X(&5X:7-T<RP@.1%)/4"!T:&%T($EN9&5X+ T*
M+2T@.(" @.268@.1G5L;"U497AT($EN9&5X(&1O97,@.;F]T(&5X:7-T+"!#4D5!
M5$4@.=&AA="!);F1E>"X-"BTM#0IU<V4@.<'5B<PT*9V\-"DE&($]"2D5#5%!2
M3U!%4E19("@.@.;V)J96-T7VED*"=P=6)?:6YF;R<I+"=486)L94AA<T%C=&EV
M949U;&QT97AT26YD97@.G*2 ](#$-"D)%1TE.#0H@.(" @.<')I;G0@.)U1A8FQE
M('!U8E]I;F9O(&ES($9U;&PM5&5X="!%;F%B;&5D+"!D<F]P<&EN9R!&=6QL
M+51E>'0@.26YD97@.@.)B!#871A;&]G+BXN)PT*(" @.($5814,@.<W!?9G5L;'1E
M>'1?=&%B;&4@.)W!U8E]I;F9O)RP@.)V1R;W G#0H@.(" @.15A%0R!S<%]F=6QL
M=&5X=%]C871A;&]G("=0=6));F9O)RP@.)V1R;W G#0I%3D0-"D5,4T4@.248@.
M3T)*14-44%)/4$525%D@.*"!O8FIE8W1?:60H)W!U8E]I;F9O)RDL)U1A8FQE
M2&%S06-T:79E1G5L;'1E>'1);F1E>"<I(#T@., T*0D5'24X-"B @.("!P<FEN
M=" G5&%B;&4@.<'5B7VEN9F\@.:7,@.3D]4($9U;&PM5&5X="!%;F%B;&5D+"!C
M<F5A=&EN9R!&5"!#871A;&]G+"!);F1E>" F($%C=&EV871I;F<N+BXG#0H@.
M(" @.15A%0R!S<%]F=6QL=&5X=%]C871A;&]G("=0=6));F9O)RP@.)V-R96%T
M92<-"B @.("!%6$5#('-P7V9U;&QT97AT7W1A8FQE("=P=6)?:6YF;R<L("=C
M<F5A=&4G+" G4'5B26YF;R<L("=54$M#3%]P=6)I;F9O)PT*(" @.($5814,@.
M<W!?9G5L;'1E>'1?8V]L=6UN("=P=6)?:6YF;R<L("=P=6)?:60G+" G861D
M)PT*(" @.($5814,@.<W!?9G5L;'1E>'1?8V]L=6UN("=P=6)?:6YF;R<L("=P
M<E]I;F9O)RP@.)V%D9"<-"B @.("!%6$5#('-P7V9U;&QT97AT7W1A8FQE("=P
M=6)?:6YF;R<L("=A8W1I=F%T92<-"D5.1 T*#0H-"BTM#0HM+2T@.069T97(@.
M16YA8FQI;F<@.)B!!8W1I=F%T:6YG(%1A8FQE<RP@.0V]L=6UN<R F($EN9&5X
M97,@.+2!3=&%R="!&=6QL(%!O<'5L871I;VX-"BTM#0IU<V4@.<'5B<PT*9V\-
M"D)%1TE.#0I3150@.3D]#3U5.5"!/3@.T*1$5#3$%212! 8F5G:6X@.9&%T971I
M;64-"D1%0TQ!4D4@.0&5N9"!D871E=&EM90T*4T54($!B96=I;B ]($-54E)%
M3E1?5$E-15-404U0#0I%6$5#('-P7V9U;&QT97AT7V-A=&%L;V<@.)U!U8DEN
M9F\G+" G<W1A<G1?9G5L;"<@.+2T@.(D9U;&P@.0W)A=VPB#0HM+2!%6$5#( '-P
M7V9U;&QT97AT7V-A=&%L;V<@.)U!U8DEN9F\G+" G<W1A<G1?:6YC<F5M96YT
M86PG("TM("));F-R96UE;G1A;"!#<F%W;"(-"BTM#0HM+2T@.5V%I="!F;W(@.
M8W)A=VP@.=&\@.8V]M<&QE=&4-"BTM#0I$14-,05)%($!S=&%T=7,@.:6YT+"!
M:71E;4-O=6YT(&EN="P@.0&ME>4-O=6YT(&EN="P@.0&EN9&5X4VEZ92!I;G0-
M"E-%3$5#5"! <W1A='5S(#T@.1G5L;%1E>'1#871A;&]G4')O<&5R='DH)U!U
M8DEN9F\G+" G<&]P=6QA=&5S=&%T=7,G*0T*5TA)3$4@.*$!S=&%T=7,@./#X@.
M,"D-"D)%1TE.#0H@.(%=!251&3U(@.1$5,05D@.)S P.C P.C Q)R M+2!W86ET
M(&9O<B Q('-E8V]N9"!B969O<F4@.8VAE8VMI;F<@.1E0@.4&]P=6QA=&5S=&%T
M=7,N+BX-"B @.4T5,14-4($!S=&%T=7,@./2!&=6QL5&5X=$-A=&%L;V=0<F]P
M97)T>2@.G4'5B26YF;R<L("=P;W!U;&%T97-T871U<R<I#0I%3D0-"E-%5"!
M96YD(#T@.0U524D5.5%]424U%4U1!35 -"E=!251&3U(@.1$5,05D@.)S P.C P
M.C$U)R M+2!W86ET(&9O<B Q-2!S96-O;F1S(&EN(&]R9&5R('1O(&=E="!C
M;W)R96-T($94(%!R;W!E<G1Y(&EN9F\N+BX-"E-%5"! :71E;4-O=6YT(#T@.
M1G5L;%1E>'1#871A;&]G4')O<&5R='DH)U!U8DEN9F\G+" G:71E;6-O=6YT
M)RD-"E-%5"! :V5Y0V]U;G0@./2!&=6QL5&5X=$-A=&%L;V=0<F]P97)T>2@.G
M4'5B26YF;R<L("=U;FEQ=65K97EC;W5N="<I#0I3150@.0&EN9 &5X4VEZ92 ]
M($9U;&Q497AT0V%T86QO9U!R;W!E<G1Y*"=0=6));F9O)RP@.) VEN9&5X<VEZ
M92<I#0I04DE.5"!#3TY615)4*&-H87(H,S I+"! 8F5G:6XL(#DI("L@.8VAA
M<B@.P.2D@.*PT*(" @.(" @.0T].5D525"AC:&%R*#,P*2P@.0&5N9"P@..2D@.*R!C
M:&%R*# Y*2 K#0H@.(" @.("!#3TY615)4*&-H87(H,S I+"! 96YD("T@.0&)E
M9VEN+" X*2 K(&-H87(H,#DI("L-"B @.(" @.($-/3E9%4E0H8VAA<B@.S,"DL
M($1!5$5$249&("AH:"P@.0&)E9VEN+"! 96YD*2D@.*R!C:&%R*# Y*2 K#0H@.
M(" @.("!#3TY615)4*&-H87(H,S I+"!$051%1$E&1B H;6DL($!B96=I;BP@.
M0&5N9"DI("L@.8VAA<B@.P.2D@.*PT*(" @.(" @.0T].5D525"AC:&%R*#,P*2P@.
M1$%4141)1D8@.*'-S+"! 8F5G:6XL($!E;F0I*2 K(&-H87(H,#DI("L-"B @.
M(" @.($-/3E9%4E0H=F%R8VAA<B@.Q,"DL($!I=&5M0V]U;G0I("L@.8VAA<B@.P
M.2D@.*PT*(" @.(" @.0T].5D525"AV87)C:&%R*#$P*2P@.0&ME>4-O=6YT*2 K
M(&-H87(H,#DI("L-"B @.(" @.($-/3E9%4E0H=F%R8VAA<B@.Q,"DL($!I;F1E
M>%-I>F4I#0I3150@.3D]#3U5.5"!/1D8-"D5.1 T*9V\-"@.T*+2T-"BTM+2!#
M;VYF:7)M(&%B;W9E(')E<W5L=',@.=VET:#H-"BTM#0I314Q%0U0@.<'5B7VED
M+"!P<E]I;F9O( T*"4923TT@.<'5B7VEN9F\@.5TA%4D4@.0T].5$%)3E,H<')?
8:6YF;RP@.)R)B;V]K*B(G*0T*9V\-"@.T*
`
end
|||can you post your query here?
Also is this an English language search?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Shabam" <blislecp@.hotmail.com> wrote in message
news:UM2dnfovDquwefrcRVn-oA@.adelphia.com...
> I have a bulletin board application (written by a programmer) and want to
> allow users to search the database for keywords/phrases. Doing a basic
> search eats up the cpu like crazy, and I know there are some strategies on
> how to get this done right.
> Can someone provide me with an overview of how best to go about this? If
> there are good articles/tutorials on this I'd greatly appreciate it also.
> Btw, I'm running MS SQL server 2000 running dotnet.
>
sql
Wednesday, March 21, 2012
newbie query question
Hi,
I have a table [myRecipes] with 4 fields; Users can Modify existing recipes or add new recipes and my table stores the new values with a timestamp.
Question: Now that I have some sample data, I need a query or view to pull out each unique recipe in my table, but only return the latest and greatest recipe for each unique recipe.
[foodType] nvarchar
[recipeName] nvarchar
[lastSaved] datetime
[cupsOfSugar] float
Sample data:
foodType recipeName lastSaved cupsOfSugar
cookie, peanutButter, 3/1/2007, 1.0
cookie, peanutButter, 3/5/2007, 1.5
cookie, sugar, 2/28/2007, 5.0
How to:
What would be the query to return the latest and greatest recipes in my db? The resultset should return
cookie, sugar, 2/28/2007, 5
cookie, peanutButter, 3/5/2007, 1.5
...and not the original recipe for peanutButter Cookies with only 1.0 cups of sugar
thanks in advance,
bsierad
select TOP 1 foodType, recipeName, lastSaved, cupsOfSugarFROM myRecipes Order by cupsOfSugar DESC --Returns greatest
UNION
select TOP 1 foodType, recipeName, lastSaved, cupsOfSugar
FROM myRecipes Order by lastSaved -- Returns latest
PS. Best forum for this question is Transact-SQL
|||
Thanks,
but, doesn't this only return one row?
I'm looking for:
For each unique foodType and recipeName, please return all the fields in my table, and, if there are any duplicate records with foodType and recipeName, please only return that record whose lastSaved field is the max for that particular set.
This table basically holds a history of all saved recipes created by the user, but he/she should only ever see the latest and greatest...
PS: The primary key on this table is foodType + recipeName + lastSaved
thanks again in advance,
ben
|||Check my response in TransactSQLnewbie query question
Hi,
I have a table [myRecipes] with 4 fields; Users can Modify existing recipes or add new recipes and my table stores the new values with a timestamp.
Question: Now that I have some sample data, I need a query or view to pull out each unique recipe in my table, but only return the latest and greatest recipe for each foodType / recipeName pair.
Primary Key = foodType + recipeName + lastSaved
[foodType] nvarchar
[recipeName] nvarchar
[lastSaved] datetime
[cupsOfSugar] float
Sample data:
foodType recipeName lastSaved cupsOfSugar
cookie, peanutButter, 3/1/2007, 1.0
cookie, peanutButter, 3/5/2007, 1.5
cookie, sugar, 2/28/2007, 5.0
How to:
What would be the query to return the latest and greatest recipes in my db? The resultset should return
cookie, sugar, 2/28/2007, 5
cookie, peanutButter, 3/5/2007, 1.5
...and not the original recipe for peanutButter Cookies with only 1.0 cups of sugar
thanks in advance,
bsierad
This should get you started. It will provide the lastest of each reciept variation.
|||SELECT
[FoodType],
[RecipeName],
max( [LastSaved] )
FROM [myRecipes]
GROUP BY
[FoodType],
[RecipeName]
ORDER BY
[FoodType],
[RecipeName]This gets you the PK of each qualifying row, and then you could use it as a subquery or a JOIN derived table to get the remaining ingredients.
Thanks, this really helps me out!
this is great...don't have to use a JOIN with this?
I'm under the impression subqueries as input to a parent query can only return one field?
I also did this:
SELECT FoodType,
recipeName,
LastSaved,
cupsOfSugar
FROM myRecipes q
WHERE cast(q.LastSaved as varchar(10)) in
(select MAX(cast(LastSaved as varchar(10)) ) from myRecipes
where FoodType= q.FoodType
and
recipeName= q.recipeName)
thanks again in advance,
bsierad
|||I was thinking as a sub-query in a WHERE clause to return the PK. Also, as a derived table for a JOIN.Newbie problem using Windows Groups and Schema
I created a Windows Group in Active Directory ("Database1Users"). populated it with users, and planned to allow everyone in it to have access to a sql 2005 database.
I went to the sql server, Security (at the general level), Logins, New login, and created the Login "<domain name>\Database1Users". I assigned "Database1" as Default database, selected the database and assigned it to the above user name (which is actually a group name). I also typed in a default schema of "dbo" and gave the user account the role "db_owner" (just learning....) . Pressing OK gave me this error message:
>>>>
The DEFAULT_SCHEMA clause can not be used with a Windows Group or with principals mapped to certificates or an asymmetric keys."
>>>>
Oh..... how am I supposed to map a Windows group to give the users the access they need?
TIA,
barkingdog
See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159533&SiteID=1.
You can map a Windows group, just don't try setting a default schema for it.
Thanks
Laurentiu
Monday, February 20, 2012
Newbee password question
Is there a way to retreive passords for SQL Server authenticated users from
a system table? I'd like to retreive the passwords for a handfull of SQL
Server users and place them in a database table (for application purposes).
I've looked for functions and stored procedures in the Books On Line, but
came up empty.
Thanks,
JoeSQL Server stores passwords using a one-way hash. Although you could
retrieve the hashed data from sysligns, the value is meaningless to your
application.
Hope this helps.
Dan Guzman
SQL Server MVP
"JRE" <nomail@.all> wrote in message
news:%232kk9yiLEHA.268@.TK2MSFTNGP10.phx.gbl...
> Is there a way to retreive passords for SQL Server authenticated users
from
> a system table? I'd like to retreive the passwords for a handfull of SQL
> Server users and place them in a database table (for application
purposes).
> I've looked for functions and stored procedures in the Books On Line, but
> came up empty.
> Thanks,
> Joe
>|||Thanks Dan,
I saw several articles describing adding or changing passwords...but not
fetching them. Oh well...I guess I'll have to think of an alternative
solution to what Im trying to do
Regards,
Joe
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ePv63IlLEHA.1032@.tk2msftngp13.phx.gbl...
> SQL Server stores passwords using a one-way hash. Although you could
> retrieve the hashed data from sysligns, the value is meaningless to your
> application.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JRE" <nomail@.all> wrote in message
> news:%232kk9yiLEHA.268@.TK2MSFTNGP10.phx.gbl...
> from
> purposes).
but[vbcol=seagreen]
>
a system table? I'd like to retreive the passwords for a handfull of SQL
Server users and place them in a database table (for application purposes).
I've looked for functions and stored procedures in the Books On Line, but
came up empty.
Thanks,
JoeSQL Server stores passwords using a one-way hash. Although you could
retrieve the hashed data from sysligns, the value is meaningless to your
application.
Hope this helps.
Dan Guzman
SQL Server MVP
"JRE" <nomail@.all> wrote in message
news:%232kk9yiLEHA.268@.TK2MSFTNGP10.phx.gbl...
> Is there a way to retreive passords for SQL Server authenticated users
from
> a system table? I'd like to retreive the passwords for a handfull of SQL
> Server users and place them in a database table (for application
purposes).
> I've looked for functions and stored procedures in the Books On Line, but
> came up empty.
> Thanks,
> Joe
>|||Thanks Dan,
I saw several articles describing adding or changing passwords...but not
fetching them. Oh well...I guess I'll have to think of an alternative
solution to what Im trying to do
Regards,
Joe
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ePv63IlLEHA.1032@.tk2msftngp13.phx.gbl...
> SQL Server stores passwords using a one-way hash. Although you could
> retrieve the hashed data from sysligns, the value is meaningless to your
> application.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JRE" <nomail@.all> wrote in message
> news:%232kk9yiLEHA.268@.TK2MSFTNGP10.phx.gbl...
> from
> purposes).
but[vbcol=seagreen]
>
Subscribe to:
Posts (Atom)