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
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Friday, March 30, 2012
Newbie question on Reporting Services
The shop I work in is new to SQL Server and this fall we are installing
Sql Server 2005 with Reporting Services. Apparently the application the
database will support will use Reporting Services. A question is
arising on whether the created report(s) is considered a database
object. That is, if one creates and saves off a report in Test, is this
report migrated to production or is the same object in the same
location simply used by production?
Many thanks in advance.
GerryYou deploy reports to the server. If you have a test server and a
development server, the best is to deploy it to test and then when you are
ready, deploy it to production. Reporting services stores the report
metadata in the database. RS is an asp.net application that heavily uses the
database for its object/metadata storage, scheduling etc.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<datapro01@.yahoo.com> wrote in message
news:1156342425.017659.314250@.74g2000cwt.googlegroups.com...
> The shop I work in is new to SQL Server and this fall we are installing
> Sql Server 2005 with Reporting Services. Apparently the application the
> database will support will use Reporting Services. A question is
> arising on whether the created report(s) is considered a database
> object. That is, if one creates and saves off a report in Test, is this
> report migrated to production or is the same object in the same
> location simply used by production?
>
> Many thanks in advance.
> Gerry
>|||Thanks Bruce
Bruce L-C [MVP] wrote:
> You deploy reports to the server. If you have a test server and a
> development server, the best is to deploy it to test and then when you are
> ready, deploy it to production. Reporting services stores the report
> metadata in the database. RS is an asp.net application that heavily uses the
> database for its object/metadata storage, scheduling etc.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <datapro01@.yahoo.com> wrote in message
> news:1156342425.017659.314250@.74g2000cwt.googlegroups.com...
> > The shop I work in is new to SQL Server and this fall we are installing
> > Sql Server 2005 with Reporting Services. Apparently the application the
> > database will support will use Reporting Services. A question is
> > arising on whether the created report(s) is considered a database
> > object. That is, if one creates and saves off a report in Test, is this
> > report migrated to production or is the same object in the same
> > location simply used by production?
> >
> >
> > Many thanks in advance.
> > Gerry
> >
Sql Server 2005 with Reporting Services. Apparently the application the
database will support will use Reporting Services. A question is
arising on whether the created report(s) is considered a database
object. That is, if one creates and saves off a report in Test, is this
report migrated to production or is the same object in the same
location simply used by production?
Many thanks in advance.
GerryYou deploy reports to the server. If you have a test server and a
development server, the best is to deploy it to test and then when you are
ready, deploy it to production. Reporting services stores the report
metadata in the database. RS is an asp.net application that heavily uses the
database for its object/metadata storage, scheduling etc.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<datapro01@.yahoo.com> wrote in message
news:1156342425.017659.314250@.74g2000cwt.googlegroups.com...
> The shop I work in is new to SQL Server and this fall we are installing
> Sql Server 2005 with Reporting Services. Apparently the application the
> database will support will use Reporting Services. A question is
> arising on whether the created report(s) is considered a database
> object. That is, if one creates and saves off a report in Test, is this
> report migrated to production or is the same object in the same
> location simply used by production?
>
> Many thanks in advance.
> Gerry
>|||Thanks Bruce
Bruce L-C [MVP] wrote:
> You deploy reports to the server. If you have a test server and a
> development server, the best is to deploy it to test and then when you are
> ready, deploy it to production. Reporting services stores the report
> metadata in the database. RS is an asp.net application that heavily uses the
> database for its object/metadata storage, scheduling etc.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <datapro01@.yahoo.com> wrote in message
> news:1156342425.017659.314250@.74g2000cwt.googlegroups.com...
> > The shop I work in is new to SQL Server and this fall we are installing
> > Sql Server 2005 with Reporting Services. Apparently the application the
> > database will support will use Reporting Services. A question is
> > arising on whether the created report(s) is considered a database
> > object. That is, if one creates and saves off a report in Test, is this
> > report migrated to production or is the same object in the same
> > location simply used by production?
> >
> >
> > Many thanks in advance.
> > Gerry
> >
Newbie question on BULK INSERT of text file
A non-SQL application appends messages to a plain text file.
I want to read the rows of this text file into a table.
The text is in a general format and should be considered one column.
The lines are terminated with a typical CR/LF.
I'd like to do more analysis on the text after getting it into a table.
My Problem: BULK INSERT will skip every other line
with the code below. It seems to take the CR/LF as a column
terminator and then skip over the next row to the new CR/LF
to consider the row to be complete.
How do I get rows of plain text from a text file to a table?
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'MyTextFile.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r',
ROWTERMINATOR = '\n'
)try taking out the field terminator parameter and make the row teminator the
combined "\r\n" instead.
If that fails, It's possible you might have to use "\n\r".
"Don Anthony" wrote:
> A non-SQL application appends messages to a plain text file.
> I want to read the rows of this text file into a table.
> The text is in a general format and should be considered one column.
> The lines are terminated with a typical CR/LF.
> I'd like to do more analysis on the text after getting it into a table.
> My Problem: BULK INSERT will skip every other line
> with the code below. It seems to take the CR/LF as a column
> terminator and then skip over the next row to the new CR/LF
> to consider the row to be complete.
> How do I get rows of plain text from a text file to a table?
> Create Table #MyTempFile ( FileLine varchar(250) )
> BULK INSERT #MyTempFile
> FROM 'MyTextFile.txt'
> WITH
> (
> BATCHSIZE = 50,
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r',
> ROWTERMINATOR = '\n'
> )
>|||When I take out the FIELDTERMINATOR line I get the error show below
(tried various combinations of ROWTERMINATOR but get the same error).
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1.
Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.|||You're right. My mistake. But I did get two different versions of bulk
insert ot work
including your original code. Are you sure there are no other stray
caharacters at the end of the lines other than Cr/LF? If you have a text
editor, check the hex display to make sure. Also, is it possible there are
data lines that are more than 250 bytes? Your table definition allows for
varchar(250).
This worked for me:
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r',
ROWTERMINATOR = '\n'
)
Textest.txt contains for records each with cr/lf line terminator:
1234567890
0987654321
abcdefghij
wxyzabcdef
This version worked too:
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r\n'
)
"Don Anthony" wrote:
> When I take out the FIELDTERMINATOR line I get the error show below
> (tried various combinations of ROWTERMINATOR but get the same error).
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1, column 1
.
> Make sure the field terminator and row terminator are specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005: The provider did not give any information about the error.].
> The statement has been terminated.
>|||Your code works perfectly.
My code wasn't working because it wasn't quite what I originally indicated
(I did say I was a newbie...)
The real table definition was
CREATE TABLE #MyTempTable ( FileLine varchar(250), RowID int IDENTITY(1, 1)
)
not
CREATE TABLE #MyTempTable ( FileLine varchar(250) )
The bulk insert apparently threw away every other line after
failing to fit it into the identity column.
Everything works fine after I take out the extra column.
Thanks for your help.
"tthrone" wrote:sql
I want to read the rows of this text file into a table.
The text is in a general format and should be considered one column.
The lines are terminated with a typical CR/LF.
I'd like to do more analysis on the text after getting it into a table.
My Problem: BULK INSERT will skip every other line
with the code below. It seems to take the CR/LF as a column
terminator and then skip over the next row to the new CR/LF
to consider the row to be complete.
How do I get rows of plain text from a text file to a table?
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'MyTextFile.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r',
ROWTERMINATOR = '\n'
)try taking out the field terminator parameter and make the row teminator the
combined "\r\n" instead.
If that fails, It's possible you might have to use "\n\r".
"Don Anthony" wrote:
> A non-SQL application appends messages to a plain text file.
> I want to read the rows of this text file into a table.
> The text is in a general format and should be considered one column.
> The lines are terminated with a typical CR/LF.
> I'd like to do more analysis on the text after getting it into a table.
> My Problem: BULK INSERT will skip every other line
> with the code below. It seems to take the CR/LF as a column
> terminator and then skip over the next row to the new CR/LF
> to consider the row to be complete.
> How do I get rows of plain text from a text file to a table?
> Create Table #MyTempFile ( FileLine varchar(250) )
> BULK INSERT #MyTempFile
> FROM 'MyTextFile.txt'
> WITH
> (
> BATCHSIZE = 50,
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r',
> ROWTERMINATOR = '\n'
> )
>|||When I take out the FIELDTERMINATOR line I get the error show below
(tried various combinations of ROWTERMINATOR but get the same error).
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1.
Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.|||You're right. My mistake. But I did get two different versions of bulk
insert ot work
including your original code. Are you sure there are no other stray
caharacters at the end of the lines other than Cr/LF? If you have a text
editor, check the hex display to make sure. Also, is it possible there are
data lines that are more than 250 bytes? Your table definition allows for
varchar(250).
This worked for me:
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r',
ROWTERMINATOR = '\n'
)
Textest.txt contains for records each with cr/lf line terminator:
1234567890
0987654321
abcdefghij
wxyzabcdef
This version worked too:
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r\n'
)
"Don Anthony" wrote:
> When I take out the FIELDTERMINATOR line I get the error show below
> (tried various combinations of ROWTERMINATOR but get the same error).
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1, column 1
.
> Make sure the field terminator and row terminator are specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005: The provider did not give any information about the error.].
> The statement has been terminated.
>|||Your code works perfectly.
My code wasn't working because it wasn't quite what I originally indicated
(I did say I was a newbie...)
The real table definition was
CREATE TABLE #MyTempTable ( FileLine varchar(250), RowID int IDENTITY(1, 1)
)
not
CREATE TABLE #MyTempTable ( FileLine varchar(250) )
The bulk insert apparently threw away every other line after
failing to fit it into the identity column.
Everything works fine after I take out the extra column.
Thanks for your help.
"tthrone" wrote:sql
Newbie question here - stored procedures
Hi I just installed sql server 2005 for the first time today. Anyways I have a bunch of stored procs from an application i developed on the SQL 2000 server. Anyways I am having a problem with one of my stored procedures.
CREATE PROCEDURE ProvinceRetrieveAdHoc
(@.whereClause VARCHAR(256))
AS
EXEC("SELECT * FROM tblProvince WHERE " + @.whereClause)
RETURN
GO
However this isn't working, I am getting this error:
Incorrect syntax near 'SELECT * FROM tblProvince WHERE '.
Now I am guessing the EXEC statement must have changed, as every stored proc that uses the exec command errors out with this same error. All my other procedures run just fine. Can anyone please tell me how to fix it?
THanks
oh wow nm, i figured it out.. just replaced double quotes with single.sqlWednesday, March 28, 2012
Newbie question : sample of XML Application ?
Hi All,
I am new to XML.
Could anybody please tell me sample of aplication / cases study where we
can make use of XML
(in general or especially in SQL Server 2000)
Thank you for your help,
Krist
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I am not quite sure what you are looking for? Do you need a motivation to
use XML in the database? If you need that, you probably are fine with using
the relational feature and don't need to use XML. Otherwise,
http://msdn.microsoft.com/xml has whitepapers and other information...
Best regards
Michael
"Krist" <Kristanto@.me.com> wrote in message
news:uy4M%23m5KFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I am new to XML.
> Could anybody please tell me sample of aplication / cases study where we
> can make use of XML
> (in general or especially in SQL Server 2000)
> Thank you for your help,
> Krist
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
I am new to XML.
Could anybody please tell me sample of aplication / cases study where we
can make use of XML
(in general or especially in SQL Server 2000)
Thank you for your help,
Krist
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!I am not quite sure what you are looking for? Do you need a motivation to
use XML in the database? If you need that, you probably are fine with using
the relational feature and don't need to use XML. Otherwise,
http://msdn.microsoft.com/xml has whitepapers and other information...
Best regards
Michael
"Krist" <Kristanto@.me.com> wrote in message
news:uy4M%23m5KFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I am new to XML.
> Could anybody please tell me sample of aplication / cases study where we
> can make use of XML
> (in general or especially in SQL Server 2000)
> Thank you for your help,
> Krist
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
Newbie question : sample of XML Application ?
Hi All,
I am new to XML.
Could anybody please tell me sample of aplication / cases study where we
can make use of XML
(in general or especially in SQL Server 2000)
Thank you for your help,
Krist
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I am not quite sure what you are looking for? Do you need a motivation to
use XML in the database? If you need that, you probably are fine with using
the relational feature and don't need to use XML. Otherwise,
http://msdn.microsoft.com/xml has whitepapers and other information...
Best regards
Michael
"Krist" <Kristanto@.me.com> wrote in message
news:uy4M%23m5KFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I am new to XML.
> Could anybody please tell me sample of aplication / cases study where we
> can make use of XML
> (in general or especially in SQL Server 2000)
> Thank you for your help,
> Krist
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
I am new to XML.
Could anybody please tell me sample of aplication / cases study where we
can make use of XML
(in general or especially in SQL Server 2000)
Thank you for your help,
Krist
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I am not quite sure what you are looking for? Do you need a motivation to
use XML in the database? If you need that, you probably are fine with using
the relational feature and don't need to use XML. Otherwise,
http://msdn.microsoft.com/xml has whitepapers and other information...
Best regards
Michael
"Krist" <Kristanto@.me.com> wrote in message
news:uy4M%23m5KFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I am new to XML.
> Could anybody please tell me sample of aplication / cases study where we
> can make use of XML
> (in general or especially in SQL Server 2000)
> Thank you for your help,
> Krist
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Newbie Question - Service Manager / Win 2003
Hi,
I'm running a Microsoft Windows 2003 Server computer, which is using a MS SQL server to feed our office application, OfficeTalk.
Due to the extreme size of the database, we have decided to install an external 1TB drive, and move the database to it.
The difficult bit we're finding is how to make it so that the service loads the new data source.
In other words, how do you use Service Manager to parse an argument to dbsvr.exe in order to change the database location?
Regards,
Matthew.Hi Mathew and welcome to TSDN MSSQL forum,
If you have an agreed maintenance slot where you can take the MSSQL server down, I would suggest do the following:
Back everything up
using enterprise manager detach on the database you want to move
copy the database to the new location
using enterprise manager attach the database by browsing to the new location.
If you get problems, backout by detaching the database in the new location and attach the old one.
Suggest you also try it out on a test DB, northwinds etc before you go for it for real..
Regards Purple
Friday, March 23, 2012
Newbie question
Hi All,
My company has a legacy web application (VBScript/ASP) that includes a
series of forms used to create various reports on SQL Server (or Oracle)
data. Creation of these reports is extremely tedious due to fairly
complicated calculations etc. My question for the group is: Is MS
Reporting Services a valid alternative to the way i'm doing things now? I
need to be able to create new interactive custom reports fairly easily and
link to them via the old web application.
Thanks In Advance,
TeddRS integrates pretty easily with custom web apps particularly for intranet
apps. You can use either URL integration or web services integration. URL
integration is the easiest and fullest featured (you have roll you own a lot
more when using web services). SQL Server reporting is very easy. The main
thing with Oracle is getting all the client apps installed and properly
configured (but that is not too untypical of anything concerning Oracle). I
go against Sybase and SQL Server. Others once up and running have been
successfull with Oracle. With RS 2005 (released in November) my expectation
is Oracle will be easier to go against. It also has a good many improvements
(my two favorites are dynamic sorting and multi-select parameters). I would
suggest getting the 90 day trial and give it a try.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tedd Simms" <tedd.simms.SPAMTRAP@.cognostech.com> wrote in message
news:yf-dnbYtb-kzmF7fRVn-gQ@.novus-tele.net...
> Hi All,
> My company has a legacy web application (VBScript/ASP) that includes a
> series of forms used to create various reports on SQL Server (or Oracle)
> data. Creation of these reports is extremely tedious due to fairly
> complicated calculations etc. My question for the group is: Is MS
> Reporting Services a valid alternative to the way i'm doing things now? I
> need to be able to create new interactive custom reports fairly easily and
> link to them via the old web application.
> Thanks In Advance,
> Tedd
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:etoRWSPfFHA.1948@.TK2MSFTNGP12.phx.gbl...
> RS integrates pretty easily with custom web apps particularly for intranet
> apps. You can use either URL integration or web services integration. URL
> integration is the easiest and fullest featured (you have roll you own a
> lot more when using web services). SQL Server reporting is very easy. The
> main thing with Oracle is getting all the client apps installed and
> properly configured (but that is not too untypical of anything concerning
> Oracle). I go against Sybase and SQL Server. Others once up and running
> have been successfull with Oracle. With RS 2005 (released in November) my
> expectation is Oracle will be easier to go against. It also has a good
> many improvements (my two favorites are dynamic sorting and multi-select
> parameters). I would suggest getting the 90 day trial and give it a try.
Thanks for the response Bruce.
Actually, i'm not too worried about using it with Oracle. I'm trying to
convince my boss to discontinue development and support for it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tedd Simms" <tedd.simms.SPAMTRAP@.cognostech.com> wrote in message
> news:yf-dnbYtb-kzmF7fRVn-gQ@.novus-tele.net...
>> Hi All,
>> My company has a legacy web application (VBScript/ASP) that includes a
>> series of forms used to create various reports on SQL Server (or Oracle)
>> data. Creation of these reports is extremely tedious due to fairly
>> complicated calculations etc. My question for the group is: Is MS
>> Reporting Services a valid alternative to the way i'm doing things now?
>> I need to be able to create new interactive custom reports fairly easily
>> and link to them via the old web application.
>> Thanks In Advance,
>> Tedd
>
My company has a legacy web application (VBScript/ASP) that includes a
series of forms used to create various reports on SQL Server (or Oracle)
data. Creation of these reports is extremely tedious due to fairly
complicated calculations etc. My question for the group is: Is MS
Reporting Services a valid alternative to the way i'm doing things now? I
need to be able to create new interactive custom reports fairly easily and
link to them via the old web application.
Thanks In Advance,
TeddRS integrates pretty easily with custom web apps particularly for intranet
apps. You can use either URL integration or web services integration. URL
integration is the easiest and fullest featured (you have roll you own a lot
more when using web services). SQL Server reporting is very easy. The main
thing with Oracle is getting all the client apps installed and properly
configured (but that is not too untypical of anything concerning Oracle). I
go against Sybase and SQL Server. Others once up and running have been
successfull with Oracle. With RS 2005 (released in November) my expectation
is Oracle will be easier to go against. It also has a good many improvements
(my two favorites are dynamic sorting and multi-select parameters). I would
suggest getting the 90 day trial and give it a try.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tedd Simms" <tedd.simms.SPAMTRAP@.cognostech.com> wrote in message
news:yf-dnbYtb-kzmF7fRVn-gQ@.novus-tele.net...
> Hi All,
> My company has a legacy web application (VBScript/ASP) that includes a
> series of forms used to create various reports on SQL Server (or Oracle)
> data. Creation of these reports is extremely tedious due to fairly
> complicated calculations etc. My question for the group is: Is MS
> Reporting Services a valid alternative to the way i'm doing things now? I
> need to be able to create new interactive custom reports fairly easily and
> link to them via the old web application.
> Thanks In Advance,
> Tedd
>|||"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:etoRWSPfFHA.1948@.TK2MSFTNGP12.phx.gbl...
> RS integrates pretty easily with custom web apps particularly for intranet
> apps. You can use either URL integration or web services integration. URL
> integration is the easiest and fullest featured (you have roll you own a
> lot more when using web services). SQL Server reporting is very easy. The
> main thing with Oracle is getting all the client apps installed and
> properly configured (but that is not too untypical of anything concerning
> Oracle). I go against Sybase and SQL Server. Others once up and running
> have been successfull with Oracle. With RS 2005 (released in November) my
> expectation is Oracle will be easier to go against. It also has a good
> many improvements (my two favorites are dynamic sorting and multi-select
> parameters). I would suggest getting the 90 day trial and give it a try.
Thanks for the response Bruce.
Actually, i'm not too worried about using it with Oracle. I'm trying to
convince my boss to discontinue development and support for it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tedd Simms" <tedd.simms.SPAMTRAP@.cognostech.com> wrote in message
> news:yf-dnbYtb-kzmF7fRVn-gQ@.novus-tele.net...
>> Hi All,
>> My company has a legacy web application (VBScript/ASP) that includes a
>> series of forms used to create various reports on SQL Server (or Oracle)
>> data. Creation of these reports is extremely tedious due to fairly
>> complicated calculations etc. My question for the group is: Is MS
>> Reporting Services a valid alternative to the way i'm doing things now?
>> I need to be able to create new interactive custom reports fairly easily
>> and link to them via the old web application.
>> Thanks In Advance,
>> Tedd
>
Wednesday, March 21, 2012
newbie query help
hello all,i am developing an application which displays available pcs in our lab.all activites are stored in a sql server table.Sql server 2000
Table structure is field name datatype null values
logon varchar(6) canbe null (values = login or logoff)
username varchar(20) canbe null (eg user01,B023)
computername varchar(20) canbenull (if its in lab1 name will be lab1-01,lab1-02..,if its in lab2 name will be lab2-01,lab2-02.)
logontime datetime(8) canbenull (26/11/2002 11:17:52)
table sample is
Logon computername username logontime
Login lab1-01 x 2003-07-09 11:17:00.000
Login lab1-03 y 2003-07-09 11:19:00.000
Logoff lab1-01 x 2003-07-09 11:20:00.000
with these details is it possible to see currently how many machines r occupied in each lab.if so how?i am planning to write an asp page which will display currently available machines,so tat user can go to particular lab .i am a sql newbie .if any 1 can guide me tat wuld be helpful for me .thx in advance
-regards
aravindas far as i can see (im finding it hard to understand exactly what fields will be in your database) all you should need to extract the info is something along the lines of:
select from tableA computername
where (logon = ' ')
presuming the table is called 'tableA'
you might be better off having another field of type boolean which simply shows whether anyone is logged into the machine, maybe 0 for vacant or 1 for in-use.
hope this helps|||assume that a computer is "occupied" when the latest logon for it is "Login"
this query will list the individual machines:
select computername
, logontime as LastLoginTime
from yourtable as TT
where logontime
= ( select max(logontime)
from yourtable
where logontime = TT.logontime )
and logon = 'Login'
order
by computername
this query will count them by lab and list them in order by the fewest occupied to the most:
select left(computername,4) as Lab
, count(*) as OccupiedCount
from yourtable as TT
where logontime
= ( select max(logontime)
from yourtable
where logontime = TT.logontime )
and logon = 'Login'
group
by left(computername,4)
order
by 2
rudy|||How can you tell if they are occupied if all you have is the last logon time? You need a field that indicates their logoff time as well. With that, you can not only tell which machines are occupied (at least one associated logon has a null logoff time), but also other valuable information such as usage patterns.
blindman|||blindman, the logoffs are there, as separate rows
that's why my query checks for when the latest logon for the machine is "Login"
:)|||hello all thx for ur replies ,i didt read ur query.i created a new table with computername,status (which can be login or logoff) ,when old table gets inserted it triggers an event which sets the status of particular machine in new table .now i can query the new table to see the availability...is it a good practice? its my first sql prg .gimme some inputs
-regards
aravind|||Ahh...I see your logoff rows now. I've worked tables that way before, but I prefer storing logoffs as a separate field rather than a separate record. That way each row represents a single session and it makes calculations easier. By using a separate field you don't need the Max subquery, and finding the total amount of time for a particular user across sessions is as simple as sum(LogoffTime - LogonTime).
blindman
Table structure is field name datatype null values
logon varchar(6) canbe null (values = login or logoff)
username varchar(20) canbe null (eg user01,B023)
computername varchar(20) canbenull (if its in lab1 name will be lab1-01,lab1-02..,if its in lab2 name will be lab2-01,lab2-02.)
logontime datetime(8) canbenull (26/11/2002 11:17:52)
table sample is
Logon computername username logontime
Login lab1-01 x 2003-07-09 11:17:00.000
Login lab1-03 y 2003-07-09 11:19:00.000
Logoff lab1-01 x 2003-07-09 11:20:00.000
with these details is it possible to see currently how many machines r occupied in each lab.if so how?i am planning to write an asp page which will display currently available machines,so tat user can go to particular lab .i am a sql newbie .if any 1 can guide me tat wuld be helpful for me .thx in advance
-regards
aravindas far as i can see (im finding it hard to understand exactly what fields will be in your database) all you should need to extract the info is something along the lines of:
select from tableA computername
where (logon = ' ')
presuming the table is called 'tableA'
you might be better off having another field of type boolean which simply shows whether anyone is logged into the machine, maybe 0 for vacant or 1 for in-use.
hope this helps|||assume that a computer is "occupied" when the latest logon for it is "Login"
this query will list the individual machines:
select computername
, logontime as LastLoginTime
from yourtable as TT
where logontime
= ( select max(logontime)
from yourtable
where logontime = TT.logontime )
and logon = 'Login'
order
by computername
this query will count them by lab and list them in order by the fewest occupied to the most:
select left(computername,4) as Lab
, count(*) as OccupiedCount
from yourtable as TT
where logontime
= ( select max(logontime)
from yourtable
where logontime = TT.logontime )
and logon = 'Login'
group
by left(computername,4)
order
by 2
rudy|||How can you tell if they are occupied if all you have is the last logon time? You need a field that indicates their logoff time as well. With that, you can not only tell which machines are occupied (at least one associated logon has a null logoff time), but also other valuable information such as usage patterns.
blindman|||blindman, the logoffs are there, as separate rows
that's why my query checks for when the latest logon for the machine is "Login"
:)|||hello all thx for ur replies ,i didt read ur query.i created a new table with computername,status (which can be login or logoff) ,when old table gets inserted it triggers an event which sets the status of particular machine in new table .now i can query the new table to see the availability...is it a good practice? its my first sql prg .gimme some inputs
-regards
aravind|||Ahh...I see your logoff rows now. I've worked tables that way before, but I prefer storing logoffs as a separate field rather than a separate record. That way each row represents a single session and it makes calculations easier. By using a separate field you don't need the Max subquery, and finding the total amount of time for a particular user across sessions is as simple as sum(LogoffTime - LogonTime).
blindman
Monday, March 19, 2012
Newbie needing help with joining 2 fields
I have a sql 2005 dev ed running an application developed in vs.net 2005 C# I have several gridviews which have a field call first name and a field called lastname I need to put both the firstname and lastname in the same cell or colum row. I do not now how to join these. Can some one help me with a SQL query string that will do this for me.You could do SELECT FirstName + ' ' + LastName FROM YourTable|||SELECT FirstName + ' ' + LastName AS FullName
FROM Employees
FROM Employees
Friday, March 9, 2012
Newbie Cant connect - Trusted Server issue.
Hello,
I'm creating an application that will rely on a central SQL Server database
for its help file. I'm a beginner with SQL Server really, and I keep
getting the following error;
"Login failed for use 'whateverUser'. Reason: Not associated with a trusted
SQL Server connection"
I can't seem to find a solution in the help files. Any direction would be
greatly appreciated.
Thanks!Jozef (SPAM_O_NATOR@.telus.net) writes:
> I'm creating an application that will rely on a central SQL Server
> database for its help file. I'm a beginner with SQL Server really, and
> I keep getting the following error;
> "Login failed for use 'whateverUser'. Reason: Not associated with a
> trusted SQL Server connection"
> I can't seem to find a solution in the help files. Any direction would be
> greatly appreciated.
You can log into SQL Server in two ways: Windows authentication, and
SQL Server authentication. The former is always available, for SQL Server
authentication to be available, the server must be enabled for this,
which by default it is not.
With Windows authentication, SQL Server accepts your Windows user,
if it has been granted access to SQL Server, and if SQL Server can
somehow validate it. With SQL Server authentication, you provide a
username and password that SQL Server itself validates.
Windows authentication is generally more secure, and also more convient,
since it does not require any extra username/password. However, it does
not always work well, if user and server are not in the same domain -
including when there is no domain at all.
Since you replaced the actual username with 'whateverUser', I don't know
which form of authentication you are trying to use. But if you are not
using Windows authentication, I recommend that you switch to this, by
replacing UserID and Password in the connection string with
Trusted_connection=Yes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, that's the most definitive answer I've been able to find.
I'd like to use Windows authentication if I can but this has given me enough
info to be able to play with both.
Thanks again!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9679C13AF3F52Yazorman@.127.0.0.1...
> Jozef (SPAM_O_NATOR@.telus.net) writes:
>> I'm creating an application that will rely on a central SQL Server
>> database for its help file. I'm a beginner with SQL Server really, and
>> I keep getting the following error;
>>
>> "Login failed for use 'whateverUser'. Reason: Not associated with a
>> trusted SQL Server connection"
>>
>> I can't seem to find a solution in the help files. Any direction would
>> be
>> greatly appreciated.
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
Newbie Can't connect - Trusted Server issue.
Hello,
I'm creating an application that will rely on a central SQL Server database
for its help file. I'm a beginner with SQL Server really, and I keep
getting the following error;
"Login failed for use 'whateverUser'. Reason: Not associated with a trusted
SQL Server connection"
I can't seem to find a solution in the help files. Any direction would be
greatly appreciated.
Thanks!
Jozef (SPAM_O_NATOR@.telus.net) writes:
> I'm creating an application that will rely on a central SQL Server
> database for its help file. I'm a beginner with SQL Server really, and
> I keep getting the following error;
> "Login failed for use 'whateverUser'. Reason: Not associated with a
> trusted SQL Server connection"
> I can't seem to find a solution in the help files. Any direction would be
> greatly appreciated.
You can log into SQL Server in two ways: Windows authentication, and
SQL Server authentication. The former is always available, for SQL Server
authentication to be available, the server must be enabled for this,
which by default it is not.
With Windows authentication, SQL Server accepts your Windows user,
if it has been granted access to SQL Server, and if SQL Server can
somehow validate it. With SQL Server authentication, you provide a
username and password that SQL Server itself validates.
Windows authentication is generally more secure, and also more convient,
since it does not require any extra username/password. However, it does
not always work well, if user and server are not in the same domain -
including when there is no domain at all.
Since you replaced the actual username with 'whateverUser', I don't know
which form of authentication you are trying to use. But if you are not
using Windows authentication, I recommend that you switch to this, by
replacing UserID and Password in the connection string with
Trusted_connection=Yes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||You also might want to verify the connection string you are using is
completely correct. http://www.connectionstrings.com has a good collection
of all the various formats to use.
If you're just getting started in programming, you also might want to check
out www.asp.net and www.devx.com. They have some great articles and samples
to get you started, in addition to books, etc.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Erland Sommarskog" wrote:
> Jozef (SPAM_O_NATOR@.telus.net) writes:
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
|||Thanks Erland, that's the most definitive answer I've been able to find.
I'd like to use Windows authentication if I can but this has given me enough
info to be able to play with both.
Thanks again!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9679C13AF3F52Yazorman@.127.0.0.1...
> Jozef (SPAM_O_NATOR@.telus.net) writes:
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
I'm creating an application that will rely on a central SQL Server database
for its help file. I'm a beginner with SQL Server really, and I keep
getting the following error;
"Login failed for use 'whateverUser'. Reason: Not associated with a trusted
SQL Server connection"
I can't seem to find a solution in the help files. Any direction would be
greatly appreciated.
Thanks!
Jozef (SPAM_O_NATOR@.telus.net) writes:
> I'm creating an application that will rely on a central SQL Server
> database for its help file. I'm a beginner with SQL Server really, and
> I keep getting the following error;
> "Login failed for use 'whateverUser'. Reason: Not associated with a
> trusted SQL Server connection"
> I can't seem to find a solution in the help files. Any direction would be
> greatly appreciated.
You can log into SQL Server in two ways: Windows authentication, and
SQL Server authentication. The former is always available, for SQL Server
authentication to be available, the server must be enabled for this,
which by default it is not.
With Windows authentication, SQL Server accepts your Windows user,
if it has been granted access to SQL Server, and if SQL Server can
somehow validate it. With SQL Server authentication, you provide a
username and password that SQL Server itself validates.
Windows authentication is generally more secure, and also more convient,
since it does not require any extra username/password. However, it does
not always work well, if user and server are not in the same domain -
including when there is no domain at all.
Since you replaced the actual username with 'whateverUser', I don't know
which form of authentication you are trying to use. But if you are not
using Windows authentication, I recommend that you switch to this, by
replacing UserID and Password in the connection string with
Trusted_connection=Yes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||You also might want to verify the connection string you are using is
completely correct. http://www.connectionstrings.com has a good collection
of all the various formats to use.
If you're just getting started in programming, you also might want to check
out www.asp.net and www.devx.com. They have some great articles and samples
to get you started, in addition to books, etc.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Erland Sommarskog" wrote:
> Jozef (SPAM_O_NATOR@.telus.net) writes:
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
|||Thanks Erland, that's the most definitive answer I've been able to find.
I'd like to use Windows authentication if I can but this has given me enough
info to be able to play with both.
Thanks again!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9679C13AF3F52Yazorman@.127.0.0.1...
> Jozef (SPAM_O_NATOR@.telus.net) writes:
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
Newbie Can't connect - Trusted Server issue.
Hello,
I'm creating an application that will rely on a central SQL Server database
for its help file. I'm a beginner with SQL Server really, and I keep
getting the following error;
"Login failed for use 'whateverUser'. Reason: Not associated with a trusted
SQL Server connection"
I can't seem to find a solution in the help files. Any direction would be
greatly appreciated.
Thanks!Jozef (SPAM_O_NATOR@.telus.net) writes:
> I'm creating an application that will rely on a central SQL Server
> database for its help file. I'm a beginner with SQL Server really, and
> I keep getting the following error;
> "Login failed for use 'whateverUser'. Reason: Not associated with a
> trusted SQL Server connection"
> I can't seem to find a solution in the help files. Any direction would be
> greatly appreciated.
You can log into SQL Server in two ways: Windows authentication, and
SQL Server authentication. The former is always available, for SQL Server
authentication to be available, the server must be enabled for this,
which by default it is not.
With Windows authentication, SQL Server accepts your Windows user,
if it has been granted access to SQL Server, and if SQL Server can
somehow validate it. With SQL Server authentication, you provide a
username and password that SQL Server itself validates.
Windows authentication is generally more secure, and also more convient,
since it does not require any extra username/password. However, it does
not always work well, if user and server are not in the same domain -
including when there is no domain at all.
Since you replaced the actual username with 'whateverUser', I don't know
which form of authentication you are trying to use. But if you are not
using Windows authentication, I recommend that you switch to this, by
replacing UserID and Password in the connection string with
Trusted_connection=Yes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You also might want to verify the connection string you are using is
completely correct. http://www.connectionstrings.com has a good collection
of all the various formats to use.
If you're just getting started in programming, you also might want to check
out www.asp.net and www.devx.com. They have some great articles and samples
to get you started, in addition to books, etc.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Erland Sommarskog" wrote:
> Jozef (SPAM_O_NATOR@.telus.net) writes:
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Thanks Erland, that's the most definitive answer I've been able to find.
I'd like to use Windows authentication if I can but this has given me enough
info to be able to play with both.
Thanks again!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9679C13AF3F52Yazorman@.127.0.0.1...
> Jozef (SPAM_O_NATOR@.telus.net) writes:
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
I'm creating an application that will rely on a central SQL Server database
for its help file. I'm a beginner with SQL Server really, and I keep
getting the following error;
"Login failed for use 'whateverUser'. Reason: Not associated with a trusted
SQL Server connection"
I can't seem to find a solution in the help files. Any direction would be
greatly appreciated.
Thanks!Jozef (SPAM_O_NATOR@.telus.net) writes:
> I'm creating an application that will rely on a central SQL Server
> database for its help file. I'm a beginner with SQL Server really, and
> I keep getting the following error;
> "Login failed for use 'whateverUser'. Reason: Not associated with a
> trusted SQL Server connection"
> I can't seem to find a solution in the help files. Any direction would be
> greatly appreciated.
You can log into SQL Server in two ways: Windows authentication, and
SQL Server authentication. The former is always available, for SQL Server
authentication to be available, the server must be enabled for this,
which by default it is not.
With Windows authentication, SQL Server accepts your Windows user,
if it has been granted access to SQL Server, and if SQL Server can
somehow validate it. With SQL Server authentication, you provide a
username and password that SQL Server itself validates.
Windows authentication is generally more secure, and also more convient,
since it does not require any extra username/password. However, it does
not always work well, if user and server are not in the same domain -
including when there is no domain at all.
Since you replaced the actual username with 'whateverUser', I don't know
which form of authentication you are trying to use. But if you are not
using Windows authentication, I recommend that you switch to this, by
replacing UserID and Password in the connection string with
Trusted_connection=Yes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You also might want to verify the connection string you are using is
completely correct. http://www.connectionstrings.com has a good collection
of all the various formats to use.
If you're just getting started in programming, you also might want to check
out www.asp.net and www.devx.com. They have some great articles and samples
to get you started, in addition to books, etc.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Erland Sommarskog" wrote:
> Jozef (SPAM_O_NATOR@.telus.net) writes:
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Thanks Erland, that's the most definitive answer I've been able to find.
I'd like to use Windows authentication if I can but this has given me enough
info to be able to play with both.
Thanks again!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9679C13AF3F52Yazorman@.127.0.0.1...
> Jozef (SPAM_O_NATOR@.telus.net) writes:
> You can log into SQL Server in two ways: Windows authentication, and
> SQL Server authentication. The former is always available, for SQL Server
> authentication to be available, the server must be enabled for this,
> which by default it is not.
> With Windows authentication, SQL Server accepts your Windows user,
> if it has been granted access to SQL Server, and if SQL Server can
> somehow validate it. With SQL Server authentication, you provide a
> username and password that SQL Server itself validates.
> Windows authentication is generally more secure, and also more convient,
> since it does not require any extra username/password. However, it does
> not always work well, if user and server are not in the same domain -
> including when there is no domain at all.
> Since you replaced the actual username with 'whateverUser', I don't know
> which form of authentication you are trying to use. But if you are not
> using Windows authentication, I recommend that you switch to this, by
> replacing UserID and Password in the connection string with
> Trusted_connection=Yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
Wednesday, March 7, 2012
newbie :Backup database from within an VB application
Hello everybody
I need to take backup of the database at the end of everyday from within an VB application.I need to have an interface(VB) to take a back up as the targeted user is not well versed with mssqlserver environment. How do i go abt doin' this.
Thankx in advance :pYou'll need to use SQLDMO, which you can include in your VB Project. Under 'Project->Reference' include Microsoft SQLDMO Object Library, if you are using a version of SQL Server prior to 7.0 the library will be Microsoft SQLOLE Object Library.
For examples:
Using SQL-DMO To Backup and Verify a Database (http://www.asp101.com/articles/carvin/sqldmobackup/default.asp)
15 Seconds : Code Samples : Backing Up A Database (http://www.15seconds.com/howto/pg000945.htm)
Introduction to DMO (http://www.swynk.com/friends/warren/dmointro.asp)
I need to take backup of the database at the end of everyday from within an VB application.I need to have an interface(VB) to take a back up as the targeted user is not well versed with mssqlserver environment. How do i go abt doin' this.
Thankx in advance :pYou'll need to use SQLDMO, which you can include in your VB Project. Under 'Project->Reference' include Microsoft SQLDMO Object Library, if you are using a version of SQL Server prior to 7.0 the library will be Microsoft SQLOLE Object Library.
For examples:
Using SQL-DMO To Backup and Verify a Database (http://www.asp101.com/articles/carvin/sqldmobackup/default.asp)
15 Seconds : Code Samples : Backing Up A Database (http://www.15seconds.com/howto/pg000945.htm)
Introduction to DMO (http://www.swynk.com/friends/warren/dmointro.asp)
Newbie : How to make Trigger for inserting/updating fields?
Hi All,
I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update from
application, however since I new to SQLServer (used to use Access) I want to
insert/update these fields via trigger, the ModifyID should contain the user
name logged on to the SQLServer and the time he modify or insert the row.
Could this two insert/update containing in one trigger? Any help would be
highly appreciated. TIA.
HendrickCREATE TRIGGER YourTriggerName
ON YourTable
FOR INSERT, UPDATE
AS
BEGIN
UPDATE YourTable
SET
UpdateTime = GETDATE(),
UpdateID = USER_ID()
WHERE YourTable.PK IN
(
SELECT PK
FROM INSERTED
)
END
... Replace 'PK' with the primary key column(s) from your table.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> from
> application, however since I new to SQLServer (used to use Access) I want
> to
> insert/update these fields via trigger, the ModifyID should contain the
> user
> name logged on to the SQLServer and the time he modify or insert the row.
> Could this two insert/update containing in one trigger? Any help would be
> highly appreciated. TIA.
> Hendrick
>|||Great. Thanks for the prompt reply.
Hendrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> CREATE TRIGGER YourTriggerName
> ON YourTable
> FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE YourTable
> SET
> UpdateTime = GETDATE(),
> UpdateID = USER_ID()
> WHERE YourTable.PK IN
> (
> SELECT PK
> FROM INSERTED
> )
> END
>
> ... Replace 'PK' with the primary key column(s) from your table.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> > Hi All,
> >
> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> > from
> > application, however since I new to SQLServer (used to use Access) I
want
> > to
> > insert/update these fields via trigger, the ModifyID should contain the
> > user
> > name logged on to the SQLServer and the time he modify or insert the
row.
> > Could this two insert/update containing in one trigger? Any help would
be
> > highly appreciated. TIA.
> >
> > Hendrick
> >
> >
>|||Hendrick,
Also see 'Multirow Considerations' in the SQL Server Books Online.
HTH
Jerry
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Great. Thanks for the prompt reply.
> Hendrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
> row.
>> > Could this two insert/update containing in one trigger? Any help would
> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>|||Hey Jerry,
Did a search on Multirow Considerations in my book online, didn't get
anything back.
(SQL SERVER 2000 sp4). Do you have a more specific indication of what we
should be aware of here? Always wanna learn more ;-)
Thanks,
Bob
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Hendrick,
> Also see 'Multirow Considerations' in the SQL Server Books Online.
> HTH
> Jerry
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>> Great. Thanks for the prompt reply.
>> Hendrick
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I
>> > update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
>> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain
>> > the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
>> row.
>> > Could this two insert/update containing in one trigger? Any help would
>> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>>
>|||Bob,
Here is the 2005 link http://msdn2.microsoft.com/en-us/library/ms250511.
Are you using the latest SQL Server Books Online? Here is the link to
upgrade if not:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
HTH
Jerry
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:eWEpoEezFHA.3588@.tk2msftngp13.phx.gbl...
> Hey Jerry,
> Did a search on Multirow Considerations in my book online, didn't get
> anything back.
> (SQL SERVER 2000 sp4). Do you have a more specific indication of what we
> should be aware of here? Always wanna learn more ;-)
> Thanks,
> Bob
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
>> Hendrick,
>> Also see 'Multirow Considerations' in the SQL Server Books Online.
>> HTH
>> Jerry
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>> Great. Thanks for the prompt reply.
>> Hendrick
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I
>> > update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
>> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain
>> > the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
>> row.
>> > Could this two insert/update containing in one trigger? Any help
>> > would
>> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>>
>>
>
I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update from
application, however since I new to SQLServer (used to use Access) I want to
insert/update these fields via trigger, the ModifyID should contain the user
name logged on to the SQLServer and the time he modify or insert the row.
Could this two insert/update containing in one trigger? Any help would be
highly appreciated. TIA.
HendrickCREATE TRIGGER YourTriggerName
ON YourTable
FOR INSERT, UPDATE
AS
BEGIN
UPDATE YourTable
SET
UpdateTime = GETDATE(),
UpdateID = USER_ID()
WHERE YourTable.PK IN
(
SELECT PK
FROM INSERTED
)
END
... Replace 'PK' with the primary key column(s) from your table.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> from
> application, however since I new to SQLServer (used to use Access) I want
> to
> insert/update these fields via trigger, the ModifyID should contain the
> user
> name logged on to the SQLServer and the time he modify or insert the row.
> Could this two insert/update containing in one trigger? Any help would be
> highly appreciated. TIA.
> Hendrick
>|||Great. Thanks for the prompt reply.
Hendrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> CREATE TRIGGER YourTriggerName
> ON YourTable
> FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE YourTable
> SET
> UpdateTime = GETDATE(),
> UpdateID = USER_ID()
> WHERE YourTable.PK IN
> (
> SELECT PK
> FROM INSERTED
> )
> END
>
> ... Replace 'PK' with the primary key column(s) from your table.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
> > Hi All,
> >
> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
> > from
> > application, however since I new to SQLServer (used to use Access) I
want
> > to
> > insert/update these fields via trigger, the ModifyID should contain the
> > user
> > name logged on to the SQLServer and the time he modify or insert the
row.
> > Could this two insert/update containing in one trigger? Any help would
be
> > highly appreciated. TIA.
> >
> > Hendrick
> >
> >
>|||Hendrick,
Also see 'Multirow Considerations' in the SQL Server Books Online.
HTH
Jerry
"Hendrick" <hbouty@.not.gmail.com> wrote in message
news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
> Great. Thanks for the prompt reply.
> Hendrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
> row.
>> > Could this two insert/update containing in one trigger? Any help would
> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>|||Hey Jerry,
Did a search on Multirow Considerations in my book online, didn't get
anything back.
(SQL SERVER 2000 sp4). Do you have a more specific indication of what we
should be aware of here? Always wanna learn more ;-)
Thanks,
Bob
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Hendrick,
> Also see 'Multirow Considerations' in the SQL Server Books Online.
> HTH
> Jerry
> "Hendrick" <hbouty@.not.gmail.com> wrote in message
> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>> Great. Thanks for the prompt reply.
>> Hendrick
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I
>> > update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
>> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain
>> > the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
>> row.
>> > Could this two insert/update containing in one trigger? Any help would
>> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>>
>|||Bob,
Here is the 2005 link http://msdn2.microsoft.com/en-us/library/ms250511.
Are you using the latest SQL Server Books Online? Here is the link to
upgrade if not:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.mspx
HTH
Jerry
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:eWEpoEezFHA.3588@.tk2msftngp13.phx.gbl...
> Hey Jerry,
> Did a search on Multirow Considerations in my book online, didn't get
> anything back.
> (SQL SERVER 2000 sp4). Do you have a more specific indication of what we
> should be aware of here? Always wanna learn more ;-)
> Thanks,
> Bob
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:unJ3T5dzFHA.2884@.TK2MSFTNGP09.phx.gbl...
>> Hendrick,
>> Also see 'Multirow Considerations' in the SQL Server Books Online.
>> HTH
>> Jerry
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:O4b8RddzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>> Great. Thanks for the prompt reply.
>> Hendrick
>> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
>> news:#VvKdYdzFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> CREATE TRIGGER YourTriggerName
>> ON YourTable
>> FOR INSERT, UPDATE
>> AS
>> BEGIN
>> UPDATE YourTable
>> SET
>> UpdateTime = GETDATE(),
>> UpdateID = USER_ID()
>> WHERE YourTable.PK IN
>> (
>> SELECT PK
>> FROM INSERTED
>> )
>> END
>>
>> ... Replace 'PK' with the primary key column(s) from your table.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.datamanipulation.net
>> --
>>
>> "Hendrick" <hbouty@.not.gmail.com> wrote in message
>> news:uZe0ITdzFHA.4032@.TK2MSFTNGP15.phx.gbl...
>> > Hi All,
>> >
>> > I have fields ModifyID, ModifyTime, UpdateID, UpdateTime which I
>> > update
>> > from
>> > application, however since I new to SQLServer (used to use Access) I
>> want
>> > to
>> > insert/update these fields via trigger, the ModifyID should contain
>> > the
>> > user
>> > name logged on to the SQLServer and the time he modify or insert the
>> row.
>> > Could this two insert/update containing in one trigger? Any help
>> > would
>> be
>> > highly appreciated. TIA.
>> >
>> > Hendrick
>> >
>> >
>>
>>
>>
>
Monday, February 20, 2012
Newbee Question
Dear All,
What options are available for viewing the report in VB 6.0 application.
Best Regards
VJI sould think so, but not duone it myself. As it's just a cause of
calling an object.
Not sure if you'll beable to use the WebServices and SOAP side.|||You can use SOAP or the Web Service to view the report. One of the Service
Packs for SQL 2000 RS ( if I remember correctly) Also includes some controls
for viewing the reports, ( but I suspect they are DotNet versions.)
A simple thing to do might be simply to open a Windows window with IIS and
pass the URL to the report...Like you would include any web window in VB 6
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ss> wrote in message news:usvjyWAaFHA.2756@.tk2msftngp13.phx.gbl...
> Dear All,
> What options are available for viewing the report in VB 6.0 application.
> Best Regards
> VJ
What options are available for viewing the report in VB 6.0 application.
Best Regards
VJI sould think so, but not duone it myself. As it's just a cause of
calling an object.
Not sure if you'll beable to use the WebServices and SOAP side.|||You can use SOAP or the Web Service to view the report. One of the Service
Packs for SQL 2000 RS ( if I remember correctly) Also includes some controls
for viewing the reports, ( but I suspect they are DotNet versions.)
A simple thing to do might be simply to open a Windows window with IIS and
pass the URL to the report...Like you would include any web window in VB 6
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ss> wrote in message news:usvjyWAaFHA.2756@.tk2msftngp13.phx.gbl...
> Dear All,
> What options are available for viewing the report in VB 6.0 application.
> Best Regards
> VJ
Subscribe to:
Posts (Atom)