Hi,
I have a stored procedure that can insert multiple rows but it does them in
a loop so the inserts happen one at a time. There is a unique constraint on
the table and sometimes the insert violates this and the constraint
violation gets returned as an error. What I want to do is to trap the error
report it in another table and continue processing all of the inserts and
not have the unique constraint error returned to the calling application.
The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
procedure processing all the inserts and then I just do a RETURN(0) at the
end. Unfortunately this still returns the unique constraint violation even
though I am doing the RETURN(0). There doesn't seem to be any way to keep
the unique constraint from going through to ADO and reporting back an error.
The procedure has now been changed to check for the unique problem before
performing the insert but that seems ineffecient so I would rather just
suppress the error if possible. Thanks in advance for any ideas.
Wayne AntinoreHi Wayne.
Unfortunately, T-SQL offers no way to either suppress errors or provide
run-time inspection of full error messages.
You will have to implement exception management in the client to ignore
error message passed back up to ADO if you really want to take the approach
you've described.
Many people take the approach you've described & perform the distinct query
before performing the insert, but even this is not fool-proof in a high
concurrency environment.
fwiw - this has been bitched about for years & we've got some better error
handling tools coming in the next version of SQL Server. For now though,
you're stuck with this problem as you've described.
Regards,
Greg Linwood
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>|||Wayne
Look at SET XACT_ABORT on BOL
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>|||Hi Uri.
I think this won't help Wayne.
SET XACT_ABORT ON will attempt to rollback an entire transaction on any
error. The way I read Wayne's post, he wants his transaction to continue
processing other rows, even after a constraint violation, so it would have
the opposite effect from what he's after.
Regards,
Greg Linwood
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> Wayne
> Look at SET XACT_ABORT on BOL
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > I have a stored procedure that can insert multiple rows but it does them
> in
> > a loop so the inserts happen one at a time. There is a unique
constraint
> on
> > the table and sometimes the insert violates this and the constraint
> > violation gets returned as an error. What I want to do is to trap the
> error
> > report it in another table and continue processing all of the inserts
and
> > not have the unique constraint error returned to the calling
application.
> > The procedure was changed to check @.@.ERROR and use a CONTINUE to keep
the
> > procedure processing all the inserts and then I just do a RETURN(0) at
the
> > end. Unfortunately this still returns the unique constraint violation
> even
> > though I am doing the RETURN(0). There doesn't seem to be any way to
keep
> > the unique constraint from going through to ADO and reporting back an
> error.
> > The procedure has now been changed to check for the unique problem
before
> > performing the insert but that seems ineffecient so I would rather just
> > suppress the error if possible. Thanks in advance for any ideas.
> >
> > Wayne Antinore
> >
> >
>|||Hi,Greg
>The way I read Wayne's post, he wants his transaction to continue
>processing other rows, even after a constraint violation, so it would have
>the opposite effect from what he's after.
So ,he can use
This is an example from BOL
CREATE TABLE t1 (a int PRIMARY KEY)
CREATE TABLE t2 (a int REFERENCES t1(a))
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (4)
INSERT INTO t1 VALUES (6)
GO
SET XACT_ABORT OFF
GO
BEGIN TRAN
INSERT INTO t2 VALUES (1)
INSERT INTO t2 VALUES (2) /* Foreign key error */
INSERT INTO t2 VALUES (3)
COMMIT TRAN
GO
/* Select shows only keys 1 and 3 added.
Key 2 insert failed and was rolled back, but
XACT_ABORT was OFF and rest of transaction
succeeded.
*/
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> Hi Uri.
> I think this won't help Wayne.
> SET XACT_ABORT ON will attempt to rollback an entire transaction on any
> error. The way I read Wayne's post, he wants his transaction to continue
> processing other rows, even after a constraint violation, so it would have
> the opposite effect from what he's after.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > Wayne
> > Look at SET XACT_ABORT on BOL
> >
> > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > > I have a stored procedure that can insert multiple rows but it does
them
> > in
> > > a loop so the inserts happen one at a time. There is a unique
> constraint
> > on
> > > the table and sometimes the insert violates this and the constraint
> > > violation gets returned as an error. What I want to do is to trap the
> > error
> > > report it in another table and continue processing all of the inserts
> and
> > > not have the unique constraint error returned to the calling
> application.
> > > The procedure was changed to check @.@.ERROR and use a CONTINUE to keep
> the
> > > procedure processing all the inserts and then I just do a RETURN(0) at
> the
> > > end. Unfortunately this still returns the unique constraint violation
> > even
> > > though I am doing the RETURN(0). There doesn't seem to be any way to
> keep
> > > the unique constraint from going through to ADO and reporting back an
> > error.
> > > The procedure has now been changed to check for the unique problem
> before
> > > performing the insert but that seems ineffecient so I would rather
just
> > > suppress the error if possible. Thanks in advance for any ideas.
> > >
> > > Wayne Antinore
> > >
> > >
> >
> >
>|||Hi Uri.
I now see you intended to set it OFF rather than ON.
However - Wayne's problem still remains that errors will be thrown through
ADO to his client, regardless of how XACT_ABORT is set.
Whether you set XACT_ABORT either ON / OFF won't solve his problem..
Regards,
Greg Linwood
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OtZIISNuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> Hi,Greg
> >The way I read Wayne's post, he wants his transaction to continue
> >processing other rows, even after a constraint violation, so it would
have
> >the opposite effect from what he's after.
>
> So ,he can use
> This is an example from BOL
> CREATE TABLE t1 (a int PRIMARY KEY)
> CREATE TABLE t2 (a int REFERENCES t1(a))
> GO
> INSERT INTO t1 VALUES (1)
> INSERT INTO t1 VALUES (3)
> INSERT INTO t1 VALUES (4)
> INSERT INTO t1 VALUES (6)
> GO
> SET XACT_ABORT OFF
> GO
> BEGIN TRAN
> INSERT INTO t2 VALUES (1)
> INSERT INTO t2 VALUES (2) /* Foreign key error */
> INSERT INTO t2 VALUES (3)
> COMMIT TRAN
> GO
> /* Select shows only keys 1 and 3 added.
> Key 2 insert failed and was rolled back, but
> XACT_ABORT was OFF and rest of transaction
> succeeded.
> */
> "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
> message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > Hi Uri.
> >
> > I think this won't help Wayne.
> >
> > SET XACT_ABORT ON will attempt to rollback an entire transaction on any
> > error. The way I read Wayne's post, he wants his transaction to continue
> > processing other rows, even after a constraint violation, so it would
have
> > the opposite effect from what he's after.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > > Wayne
> > > Look at SET XACT_ABORT on BOL
> > >
> > > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > Hi,
> > > > I have a stored procedure that can insert multiple rows but it does
> them
> > > in
> > > > a loop so the inserts happen one at a time. There is a unique
> > constraint
> > > on
> > > > the table and sometimes the insert violates this and the constraint
> > > > violation gets returned as an error. What I want to do is to trap
the
> > > error
> > > > report it in another table and continue processing all of the
inserts
> > and
> > > > not have the unique constraint error returned to the calling
> > application.
> > > > The procedure was changed to check @.@.ERROR and use a CONTINUE to
keep
> > the
> > > > procedure processing all the inserts and then I just do a RETURN(0)
at
> > the
> > > > end. Unfortunately this still returns the unique constraint
violation
> > > even
> > > > though I am doing the RETURN(0). There doesn't seem to be any way
to
> > keep
> > > > the unique constraint from going through to ADO and reporting back
an
> > > error.
> > > > The procedure has now been changed to check for the unique problem
> > before
> > > > performing the insert but that seems ineffecient so I would rather
> just
> > > > suppress the error if possible. Thanks in advance for any ideas.
> > > >
> > > > Wayne Antinore
> > > >
> > > >
> > >
> > >
> >
> >
>|||THere is no way to suppress the Constraint violation error from the stored
procedure ( on the back end.).
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>|||Greg
I hope it will be possible with "Yukon" to use begin try catch ( I don't
remember exactly what is a syntax)
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:#2qpMoNuDHA.2508@.TK2MSFTNGP12.phx.gbl...
> Hi Uri.
> I now see you intended to set it OFF rather than ON.
> However - Wayne's problem still remains that errors will be thrown through
> ADO to his client, regardless of how XACT_ABORT is set.
> Whether you set XACT_ABORT either ON / OFF won't solve his problem..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OtZIISNuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > Hi,Greg
> > >The way I read Wayne's post, he wants his transaction to continue
> > >processing other rows, even after a constraint violation, so it would
> have
> > >the opposite effect from what he's after.
> >
> >
> > So ,he can use
> > This is an example from BOL
> >
> > CREATE TABLE t1 (a int PRIMARY KEY)
> > CREATE TABLE t2 (a int REFERENCES t1(a))
> > GO
> > INSERT INTO t1 VALUES (1)
> > INSERT INTO t1 VALUES (3)
> > INSERT INTO t1 VALUES (4)
> > INSERT INTO t1 VALUES (6)
> > GO
> > SET XACT_ABORT OFF
> > GO
> > BEGIN TRAN
> > INSERT INTO t2 VALUES (1)
> > INSERT INTO t2 VALUES (2) /* Foreign key error */
> > INSERT INTO t2 VALUES (3)
> > COMMIT TRAN
> > GO
> >
> > /* Select shows only keys 1 and 3 added.
> > Key 2 insert failed and was rolled back, but
> > XACT_ABORT was OFF and rest of transaction
> > succeeded.
> > */
> >
> > "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote
in
> > message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > Hi Uri.
> > >
> > > I think this won't help Wayne.
> > >
> > > SET XACT_ABORT ON will attempt to rollback an entire transaction on
any
> > > error. The way I read Wayne's post, he wants his transaction to
continue
> > > processing other rows, even after a constraint violation, so it would
> have
> > > the opposite effect from what he's after.
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > > > Wayne
> > > > Look at SET XACT_ABORT on BOL
> > > >
> > > > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > > > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > Hi,
> > > > > I have a stored procedure that can insert multiple rows but it
does
> > them
> > > > in
> > > > > a loop so the inserts happen one at a time. There is a unique
> > > constraint
> > > > on
> > > > > the table and sometimes the insert violates this and the
constraint
> > > > > violation gets returned as an error. What I want to do is to trap
> the
> > > > error
> > > > > report it in another table and continue processing all of the
> inserts
> > > and
> > > > > not have the unique constraint error returned to the calling
> > > application.
> > > > > The procedure was changed to check @.@.ERROR and use a CONTINUE to
> keep
> > > the
> > > > > procedure processing all the inserts and then I just do a
RETURN(0)
> at
> > > the
> > > > > end. Unfortunately this still returns the unique constraint
> violation
> > > > even
> > > > > though I am doing the RETURN(0). There doesn't seem to be any way
> to
> > > keep
> > > > > the unique constraint from going through to ADO and reporting back
> an
> > > > error.
> > > > > The procedure has now been changed to check for the unique problem
> > > before
> > > > > performing the insert but that seems ineffecient so I would rather
> > just
> > > > > suppress the error if possible. Thanks in advance for any ideas.
> > > > >
> > > > > Wayne Antinore
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yep - Try / Catch will be good in Yukon.
I'm not sure if it allows error suppression, but I sure hope so!
Regards,
Greg Linwood
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#yR3yTOuDHA.1596@.TK2MSFTNGP10.phx.gbl...
> Greg
> I hope it will be possible with "Yukon" to use begin try catch ( I don't
> remember exactly what is a syntax)
>
>
> "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
> message news:#2qpMoNuDHA.2508@.TK2MSFTNGP12.phx.gbl...
> > Hi Uri.
> >
> > I now see you intended to set it OFF rather than ON.
> >
> > However - Wayne's problem still remains that errors will be thrown
through
> > ADO to his client, regardless of how XACT_ABORT is set.
> >
> > Whether you set XACT_ABORT either ON / OFF won't solve his problem..
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:OtZIISNuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > Hi,Greg
> > > >The way I read Wayne's post, he wants his transaction to continue
> > > >processing other rows, even after a constraint violation, so it would
> > have
> > > >the opposite effect from what he's after.
> > >
> > >
> > > So ,he can use
> > > This is an example from BOL
> > >
> > > CREATE TABLE t1 (a int PRIMARY KEY)
> > > CREATE TABLE t2 (a int REFERENCES t1(a))
> > > GO
> > > INSERT INTO t1 VALUES (1)
> > > INSERT INTO t1 VALUES (3)
> > > INSERT INTO t1 VALUES (4)
> > > INSERT INTO t1 VALUES (6)
> > > GO
> > > SET XACT_ABORT OFF
> > > GO
> > > BEGIN TRAN
> > > INSERT INTO t2 VALUES (1)
> > > INSERT INTO t2 VALUES (2) /* Foreign key error */
> > > INSERT INTO t2 VALUES (3)
> > > COMMIT TRAN
> > > GO
> > >
> > > /* Select shows only keys 1 and 3 added.
> > > Key 2 insert failed and was rolled back, but
> > > XACT_ABORT was OFF and rest of transaction
> > > succeeded.
> > > */
> > >
> > > "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote
> in
> > > message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > Hi Uri.
> > > >
> > > > I think this won't help Wayne.
> > > >
> > > > SET XACT_ABORT ON will attempt to rollback an entire transaction on
> any
> > > > error. The way I read Wayne's post, he wants his transaction to
> continue
> > > > processing other rows, even after a constraint violation, so it
would
> > have
> > > > the opposite effect from what he's after.
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > > > > Wayne
> > > > > Look at SET XACT_ABORT on BOL
> > > > >
> > > > > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > > > > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > > Hi,
> > > > > > I have a stored procedure that can insert multiple rows but it
> does
> > > them
> > > > > in
> > > > > > a loop so the inserts happen one at a time. There is a unique
> > > > constraint
> > > > > on
> > > > > > the table and sometimes the insert violates this and the
> constraint
> > > > > > violation gets returned as an error. What I want to do is to
trap
> > the
> > > > > error
> > > > > > report it in another table and continue processing all of the
> > inserts
> > > > and
> > > > > > not have the unique constraint error returned to the calling
> > > > application.
> > > > > > The procedure was changed to check @.@.ERROR and use a CONTINUE to
> > keep
> > > > the
> > > > > > procedure processing all the inserts and then I just do a
> RETURN(0)
> > at
> > > > the
> > > > > > end. Unfortunately this still returns the unique constraint
> > violation
> > > > > even
> > > > > > though I am doing the RETURN(0). There doesn't seem to be any
way
> > to
> > > > keep
> > > > > > the unique constraint from going through to ADO and reporting
back
> > an
> > > > > error.
> > > > > > The procedure has now been changed to check for the unique
problem
> > > > before
> > > > > > performing the insert but that seems ineffecient so I would
rather
> > > just
> > > > > > suppress the error if possible. Thanks in advance for any
ideas.
> > > > > >
> > > > > > Wayne Antinore
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks Uri, Greg, and Wayne for your replies. I didn't think there was any
way to do this currently but I'm glad you confirmed it for me. This will be
a very useful feature in Yukon
Thanks again,
Wayne
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>
2012年3月20日星期二
2012年3月11日星期日
Any tool to give me some live metrics
Id like a nice front end that I can drop to get the server I want or even
combine multiple perfmon sessions for servers in one look and feel.
I also want to look at event logs, current OS /SQL info.. Current
applications running...etc..
Right now, if i have to go to the logs, i have to go to the manage
properties of My computer.. and connect to the server..
Also open Perfmon and select the server.. or even worse, log on to the
server itself to look at current running processes.. I want to make our job
easier by having this one console that i can just drop down on the server I
want and it gives me most of the live info..Something with a web interface
would also be better..
You could check http://www.imceda.com/Product.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eznApwkBFHA.3616@.TK2MSFTNGP11.phx.gbl...
> Id like a nice front end that I can drop to get the server I want or even
> combine multiple perfmon sessions for servers in one look and feel.
> I also want to look at event logs, current OS /SQL info.. Current
> applications running...etc..
> Right now, if i have to go to the logs, i have to go to the manage
> properties of My computer.. and connect to the server..
> Also open Perfmon and select the server.. or even worse, log on to the
> server itself to look at current running processes.. I want to make our
job
> easier by having this one console that i can just drop down on the server
I
> want and it gives me most of the live info..Something with a web interface
> would also be better..
>
|||Hello Hassan,
Check out http://www.quest.com/Quest_Central_f...agnostics.asp. We have a lot of other SQL Server tools which you can see at http://www.quest.com/Quest_Central_for_SQL_Server.
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> Id like a nice front end that I can drop to get the server I want or
> even combine multiple perfmon sessions for servers in one look and
> feel.
> I also want to look at event logs, current OS /SQL info.. Current
> applications running...etc..
> Right now, if i have to go to the logs, i have to go to the manage
> properties of My computer.. and connect to the server..
> Also open Perfmon and select the server.. or even worse, log on to the
> server itself to look at current running processes.. I want to make
> our job
> easier by having this one console that i can just drop down on the
> server I
> want and it gives me most of the live info..Something with a web
> interface
> would also be better..
combine multiple perfmon sessions for servers in one look and feel.
I also want to look at event logs, current OS /SQL info.. Current
applications running...etc..
Right now, if i have to go to the logs, i have to go to the manage
properties of My computer.. and connect to the server..
Also open Perfmon and select the server.. or even worse, log on to the
server itself to look at current running processes.. I want to make our job
easier by having this one console that i can just drop down on the server I
want and it gives me most of the live info..Something with a web interface
would also be better..
You could check http://www.imceda.com/Product.htm.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eznApwkBFHA.3616@.TK2MSFTNGP11.phx.gbl...
> Id like a nice front end that I can drop to get the server I want or even
> combine multiple perfmon sessions for servers in one look and feel.
> I also want to look at event logs, current OS /SQL info.. Current
> applications running...etc..
> Right now, if i have to go to the logs, i have to go to the manage
> properties of My computer.. and connect to the server..
> Also open Perfmon and select the server.. or even worse, log on to the
> server itself to look at current running processes.. I want to make our
job
> easier by having this one console that i can just drop down on the server
I
> want and it gives me most of the live info..Something with a web interface
> would also be better..
>
|||Hello Hassan,
Check out http://www.quest.com/Quest_Central_f...agnostics.asp. We have a lot of other SQL Server tools which you can see at http://www.quest.com/Quest_Central_for_SQL_Server.
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> Id like a nice front end that I can drop to get the server I want or
> even combine multiple perfmon sessions for servers in one look and
> feel.
> I also want to look at event logs, current OS /SQL info.. Current
> applications running...etc..
> Right now, if i have to go to the logs, i have to go to the manage
> properties of My computer.. and connect to the server..
> Also open Perfmon and select the server.. or even worse, log on to the
> server itself to look at current running processes.. I want to make
> our job
> easier by having this one console that i can just drop down on the
> server I
> want and it gives me most of the live info..Something with a web
> interface
> would also be better..
2012年3月6日星期二
Any reason not to use -ve pkey's?
I have an auto-increment table that I'm thinking of added a "well known"
member to, "(multiple accounts)". The easiest way to do this would be to add
it with a pkey of -1. I could do this by temporarily turning off identity,
adding it manually, and then turning the identity back on.
Any reason I should not do this?
Maury>>>Any reason I should not do this?
None at all. It's application driven - and it's not limited to identities,
either.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:C8766965-D775-4B73-9D21-6C89E025F994@.microsoft.com...
I have an auto-increment table that I'm thinking of added a "well known"
member to, "(multiple accounts)". The easiest way to do this would be to add
it with a pkey of -1. I could do this by temporarily turning off identity,
adding it manually, and then turning the identity back on.
Any reason I should not do this?
Maury|||"Tom Moreau" wrote:
> None at all. It's application driven - and it's not limited to identities
,
> either.
So I guessed, but I wanted to be sure. Thanks!
Maury|||On 4 Jun, 20:19, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> "Tom Moreau" wrote:
> So I guessed, but I wanted to be sure. Thanks!
> Maury
Tom is right. My only doubt is just why you would want to do this? A
surrogate key should be just that. You shouldn't care what its value
is because the value itself should not be relevant outside the
database. If you ascribe a some significance to values in an IDENTITY
column then you will probably have problems later on.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
member to, "(multiple accounts)". The easiest way to do this would be to add
it with a pkey of -1. I could do this by temporarily turning off identity,
adding it manually, and then turning the identity back on.
Any reason I should not do this?
Maury>>>Any reason I should not do this?
None at all. It's application driven - and it's not limited to identities,
either.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:C8766965-D775-4B73-9D21-6C89E025F994@.microsoft.com...
I have an auto-increment table that I'm thinking of added a "well known"
member to, "(multiple accounts)". The easiest way to do this would be to add
it with a pkey of -1. I could do this by temporarily turning off identity,
adding it manually, and then turning the identity back on.
Any reason I should not do this?
Maury|||"Tom Moreau" wrote:
> None at all. It's application driven - and it's not limited to identities
,
> either.
So I guessed, but I wanted to be sure. Thanks!
Maury|||On 4 Jun, 20:19, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> "Tom Moreau" wrote:
> So I guessed, but I wanted to be sure. Thanks!
> Maury
Tom is right. My only doubt is just why you would want to do this? A
surrogate key should be just that. You shouldn't care what its value
is because the value itself should not be relevant outside the
database. If you ascribe a some significance to values in an IDENTITY
column then you will probably have problems later on.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Any reason not to use -ve pkey's?
I have an auto-increment table that I'm thinking of added a "well known"
member to, "(multiple accounts)". The easiest way to do this would be to add
it with a pkey of -1. I could do this by temporarily turning off identity,
adding it manually, and then turning the identity back on.
Any reason I should not do this?
Maury
>>>Any reason I should not do this?
None at all. It's application driven - and it's not limited to identities,
either.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:C8766965-D775-4B73-9D21-6C89E025F994@.microsoft.com...
I have an auto-increment table that I'm thinking of added a "well known"
member to, "(multiple accounts)". The easiest way to do this would be to add
it with a pkey of -1. I could do this by temporarily turning off identity,
adding it manually, and then turning the identity back on.
Any reason I should not do this?
Maury
|||"Tom Moreau" wrote:
> None at all. It's application driven - and it's not limited to identities,
> either.
So I guessed, but I wanted to be sure. Thanks!
Maury
|||On 4 Jun, 20:19, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> "Tom Moreau" wrote:
> So I guessed, but I wanted to be sure. Thanks!
> Maury
Tom is right. My only doubt is just why you would want to do this? A
surrogate key should be just that. You shouldn't care what its value
is because the value itself should not be relevant outside the
database. If you ascribe a some significance to values in an IDENTITY
column then you will probably have problems later on.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
member to, "(multiple accounts)". The easiest way to do this would be to add
it with a pkey of -1. I could do this by temporarily turning off identity,
adding it manually, and then turning the identity back on.
Any reason I should not do this?
Maury
>>>Any reason I should not do this?
None at all. It's application driven - and it's not limited to identities,
either.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:C8766965-D775-4B73-9D21-6C89E025F994@.microsoft.com...
I have an auto-increment table that I'm thinking of added a "well known"
member to, "(multiple accounts)". The easiest way to do this would be to add
it with a pkey of -1. I could do this by temporarily turning off identity,
adding it manually, and then turning the identity back on.
Any reason I should not do this?
Maury
|||"Tom Moreau" wrote:
> None at all. It's application driven - and it's not limited to identities,
> either.
So I guessed, but I wanted to be sure. Thanks!
Maury
|||On 4 Jun, 20:19, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> "Tom Moreau" wrote:
> So I guessed, but I wanted to be sure. Thanks!
> Maury
Tom is right. My only doubt is just why you would want to do this? A
surrogate key should be just that. You shouldn't care what its value
is because the value itself should not be relevant outside the
database. If you ascribe a some significance to values in an IDENTITY
column then you will probably have problems later on.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
Any reason not to use -ve pkey's?
I have an auto-increment table that I'm thinking of added a "well known"
member to, "(multiple accounts)". The easiest way to do this would be to add
it with a pkey of -1. I could do this by temporarily turning off identity,
adding it manually, and then turning the identity back on.
Any reason I should not do this?
Maury>>Any reason I should not do this?
None at all. It's application driven - and it's not limited to identities,
either.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:C8766965-D775-4B73-9D21-6C89E025F994@.microsoft.com...
I have an auto-increment table that I'm thinking of added a "well known"
member to, "(multiple accounts)". The easiest way to do this would be to add
it with a pkey of -1. I could do this by temporarily turning off identity,
adding it manually, and then turning the identity back on.
Any reason I should not do this?
Maury|||"Tom Moreau" wrote:
> None at all. It's application driven - and it's not limited to identities,
> either.
So I guessed, but I wanted to be sure. Thanks!
Maury|||On 4 Jun, 20:19, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> "Tom Moreau" wrote:
> > None at all. It's application driven - and it's not limited to identities,
> > either.
> So I guessed, but I wanted to be sure. Thanks!
> Maury
Tom is right. My only doubt is just why you would want to do this? A
surrogate key should be just that. You shouldn't care what its value
is because the value itself should not be relevant outside the
database. If you ascribe a some significance to values in an IDENTITY
column then you will probably have problems later on.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
member to, "(multiple accounts)". The easiest way to do this would be to add
it with a pkey of -1. I could do this by temporarily turning off identity,
adding it manually, and then turning the identity back on.
Any reason I should not do this?
Maury>>Any reason I should not do this?
None at all. It's application driven - and it's not limited to identities,
either.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:C8766965-D775-4B73-9D21-6C89E025F994@.microsoft.com...
I have an auto-increment table that I'm thinking of added a "well known"
member to, "(multiple accounts)". The easiest way to do this would be to add
it with a pkey of -1. I could do this by temporarily turning off identity,
adding it manually, and then turning the identity back on.
Any reason I should not do this?
Maury|||"Tom Moreau" wrote:
> None at all. It's application driven - and it's not limited to identities,
> either.
So I guessed, but I wanted to be sure. Thanks!
Maury|||On 4 Jun, 20:19, Maury Markowitz
<MauryMarkow...@.discussions.microsoft.com> wrote:
> "Tom Moreau" wrote:
> > None at all. It's application driven - and it's not limited to identities,
> > either.
> So I guessed, but I wanted to be sure. Thanks!
> Maury
Tom is right. My only doubt is just why you would want to do this? A
surrogate key should be just that. You shouldn't care what its value
is because the value itself should not be relevant outside the
database. If you ascribe a some significance to values in an IDENTITY
column then you will probably have problems later on.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
any pros/ cons to having multiple Publications
sql2k sp3
Im going to start Replicating 40 or so tables. Is there
any good/ bad to each table going into its own Publication?
TIA, ChrisR
ideally you will want to group your publications into logical units for
administrative, functional, and performance reasons.
To make life simpler for yourself you should have a single publication. This
eases the administrative burden. However sometimes you will want to
replicate different tables on different schedules, ie some transactions are
required to be replicated real time, others must only be replicated once per
day.
You will get better performance if you group your articles that have dri
relationships into the same publications, and then create multiple
publications and use the independent_agent option on each publication. This
will create multiple distribution agents replicating to the same subscriber
db.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:01ef01c496a0$ed8c86c0$a401280a@.phx.gbl...
> sql2k sp3
> Im going to start Replicating 40 or so tables. Is there
> any good/ bad to each table going into its own Publication?
> TIA, ChrisR
|||> You will get better performance if you group your articles that have dri
> relationships into the same publications, and then create multiple
> publications and use the independent_agent option on each publication.
This
> will create multiple distribution agents replicating to the same
subscriber
> db.
Im going to be replicating to a denormalized db for reporting purposes only.
(Thanks to you ;-) ) Therefore, Im not going to include the dri as it will
be enforced on the Publisher. That being the case, theres really no logical
grouping I can use. What about just putting the really big tables into
they're own Publications and using the independent_agent option you
mentioned?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OvJMuQtlEHA.896@.TK2MSFTNGP12.phx.gbl...
> ideally you will want to group your publications into logical units for
> administrative, functional, and performance reasons.
> To make life simpler for yourself you should have a single publication.
This
> eases the administrative burden. However sometimes you will want to
> replicate different tables on different schedules, ie some transactions
are
> required to be replicated real time, others must only be replicated once
per
> day.
> You will get better performance if you group your articles that have dri
> relationships into the same publications, and then create multiple
> publications and use the independent_agent option on each publication.
This
> will create multiple distribution agents replicating to the same
subscriber
> db.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:01ef01c496a0$ed8c86c0$a401280a@.phx.gbl...
>
|||separate them according to activity. I.e. if you have 10 really volatile
tables you are publisher 20 ones that are modified a couple of time an hour,
and 50 tables which are fairly static you could do 5 or 10 separate
publications, each with one or two of the volatile tables, 4 to 2 of the
less volatile tables, and 1 to 2 of the static tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:uj$UMhulEHA.2020@.TK2MSFTNGP09.phx.gbl...
> This
> subscriber
> Im going to be replicating to a denormalized db for reporting purposes
only.
> (Thanks to you ;-) ) Therefore, Im not going to include the dri as it
will
> be enforced on the Publisher. That being the case, theres really no
logical
> grouping I can use. What about just putting the really big tables into
> they're own Publications and using the independent_agent option you
> mentioned?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OvJMuQtlEHA.896@.TK2MSFTNGP12.phx.gbl...
> This
> are
> per
> This
> subscriber
>
Im going to start Replicating 40 or so tables. Is there
any good/ bad to each table going into its own Publication?
TIA, ChrisR
ideally you will want to group your publications into logical units for
administrative, functional, and performance reasons.
To make life simpler for yourself you should have a single publication. This
eases the administrative burden. However sometimes you will want to
replicate different tables on different schedules, ie some transactions are
required to be replicated real time, others must only be replicated once per
day.
You will get better performance if you group your articles that have dri
relationships into the same publications, and then create multiple
publications and use the independent_agent option on each publication. This
will create multiple distribution agents replicating to the same subscriber
db.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:01ef01c496a0$ed8c86c0$a401280a@.phx.gbl...
> sql2k sp3
> Im going to start Replicating 40 or so tables. Is there
> any good/ bad to each table going into its own Publication?
> TIA, ChrisR
|||> You will get better performance if you group your articles that have dri
> relationships into the same publications, and then create multiple
> publications and use the independent_agent option on each publication.
This
> will create multiple distribution agents replicating to the same
subscriber
> db.
Im going to be replicating to a denormalized db for reporting purposes only.
(Thanks to you ;-) ) Therefore, Im not going to include the dri as it will
be enforced on the Publisher. That being the case, theres really no logical
grouping I can use. What about just putting the really big tables into
they're own Publications and using the independent_agent option you
mentioned?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OvJMuQtlEHA.896@.TK2MSFTNGP12.phx.gbl...
> ideally you will want to group your publications into logical units for
> administrative, functional, and performance reasons.
> To make life simpler for yourself you should have a single publication.
This
> eases the administrative burden. However sometimes you will want to
> replicate different tables on different schedules, ie some transactions
are
> required to be replicated real time, others must only be replicated once
per
> day.
> You will get better performance if you group your articles that have dri
> relationships into the same publications, and then create multiple
> publications and use the independent_agent option on each publication.
This
> will create multiple distribution agents replicating to the same
subscriber
> db.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
> news:01ef01c496a0$ed8c86c0$a401280a@.phx.gbl...
>
|||separate them according to activity. I.e. if you have 10 really volatile
tables you are publisher 20 ones that are modified a couple of time an hour,
and 50 tables which are fairly static you could do 5 or 10 separate
publications, each with one or two of the volatile tables, 4 to 2 of the
less volatile tables, and 1 to 2 of the static tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <chris@.noemail.com> wrote in message
news:uj$UMhulEHA.2020@.TK2MSFTNGP09.phx.gbl...
> This
> subscriber
> Im going to be replicating to a denormalized db for reporting purposes
only.
> (Thanks to you ;-) ) Therefore, Im not going to include the dri as it
will
> be enforced on the Publisher. That being the case, theres really no
logical
> grouping I can use. What about just putting the really big tables into
> they're own Publications and using the independent_agent option you
> mentioned?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OvJMuQtlEHA.896@.TK2MSFTNGP12.phx.gbl...
> This
> are
> per
> This
> subscriber
>
Any problem for using a single data file?
Hi there,
Is there any problem using 1 data file with restricted file growth
set to 20GB? I've heard that it's better to have multiple data files with
2GB each. Is that true?
Thanks!
Alex
That was true for Win95/98 and FAT file partitions. If you are using
Win2000 or higher and NTFS, the 20 GB single file is just fine.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>
|||Unless you're splitting filegroups up in order to put them on different
physical devices, there is, IMO, little benefit in creating multiple data
files. All it will accomplish is creating more of a maintenance headache
for you.
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>
|||Hello Alex
It depends on what you are trying to achieve. There is no set requirement
or recommendation either way. However creating a lot of small files for a
database could lead to additional maintenance chores. From performance
standpoint, there should really be no difference either way unless you
achieve stripping with multiple database files across several disk
controllers and drives. However for a database of about 20GB in size, this
striping may only give you small performance benefit.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Thanks for the information. I'm really appreciated.
alex
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uBkGIFFkEHA.1348@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> That was true for Win95/98 and FAT file partitions. If you are using
> Win2000 or higher and NTFS, the 20 GB single file is just fine.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with
>
|||Got it. Thanks!
alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ej7SNGFkEHA.704@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Unless you're splitting filegroups up in order to put them on different
> physical devices, there is, IMO, little benefit in creating multiple data
> files. All it will accomplish is creating more of a maintenance headache
> for you.
>
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with
>
|||Thanks!
alex
"Pankaj Agarwal [MSFT]" <pankaja@.online.microsoft.com> wrote in message
news:ecnxtZHkEHA.2516@.cpmsftngxa10.phx.gbl...
> Hello Alex
> It depends on what you are trying to achieve. There is no set requirement
> or recommendation either way. However creating a lot of small files for a
> database could lead to additional maintenance chores. From performance
> standpoint, there should really be no difference either way unless you
> achieve stripping with multiple database files across several disk
> controllers and drives. However for a database of about 20GB in size, this
> striping may only give you small performance benefit.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
Is there any problem using 1 data file with restricted file growth
set to 20GB? I've heard that it's better to have multiple data files with
2GB each. Is that true?
Thanks!
Alex
That was true for Win95/98 and FAT file partitions. If you are using
Win2000 or higher and NTFS, the 20 GB single file is just fine.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>
|||Unless you're splitting filegroups up in order to put them on different
physical devices, there is, IMO, little benefit in creating multiple data
files. All it will accomplish is creating more of a maintenance headache
for you.
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>
|||Hello Alex
It depends on what you are trying to achieve. There is no set requirement
or recommendation either way. However creating a lot of small files for a
database could lead to additional maintenance chores. From performance
standpoint, there should really be no difference either way unless you
achieve stripping with multiple database files across several disk
controllers and drives. However for a database of about 20GB in size, this
striping may only give you small performance benefit.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Thanks for the information. I'm really appreciated.
alex
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uBkGIFFkEHA.1348@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> That was true for Win95/98 and FAT file partitions. If you are using
> Win2000 or higher and NTFS, the 20 GB single file is just fine.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with
>
|||Got it. Thanks!
alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ej7SNGFkEHA.704@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Unless you're splitting filegroups up in order to put them on different
> physical devices, there is, IMO, little benefit in creating multiple data
> files. All it will accomplish is creating more of a maintenance headache
> for you.
>
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with
>
|||Thanks!
alex
"Pankaj Agarwal [MSFT]" <pankaja@.online.microsoft.com> wrote in message
news:ecnxtZHkEHA.2516@.cpmsftngxa10.phx.gbl...
> Hello Alex
> It depends on what you are trying to achieve. There is no set requirement
> or recommendation either way. However creating a lot of small files for a
> database could lead to additional maintenance chores. From performance
> standpoint, there should really be no difference either way unless you
> achieve stripping with multiple database files across several disk
> controllers and drives. However for a database of about 20GB in size, this
> striping may only give you small performance benefit.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
Any problem for using a single data file?
Hi there,
Is there any problem using 1 data file with restricted file growth
set to 20GB? I've heard that it's better to have multiple data files with
2GB each. Is that true?
Thanks!
AlexThat was true for Win95/98 and FAT file partitions. If you are using
Win2000 or higher and NTFS, the 20 GB single file is just fine.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Unless you're splitting filegroups up in order to put them on different
physical devices, there is, IMO, little benefit in creating multiple data
files. All it will accomplish is creating more of a maintenance headache
for you.
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Hello Alex
It depends on what you are trying to achieve. There is no set requirement
or recommendation either way. However creating a lot of small files for a
database could lead to additional maintenance chores. From performance
standpoint, there should really be no difference either way unless you
achieve stripping with multiple database files across several disk
controllers and drives. However for a database of about 20GB in size, this
striping may only give you small performance benefit.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks for the information. I'm really appreciated.
alex
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uBkGIFFkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> That was true for Win95/98 and FAT file partitions. If you are using
> Win2000 or higher and NTFS, the 20 GB single file is just fine.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with[vbcol=seagreen]
>|||Got it. Thanks!
alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ej7SNGFkEHA.704@.TK2MSFTNGP09.phx.gbl...
> Unless you're splitting filegroups up in order to put them on different
> physical devices, there is, IMO, little benefit in creating multiple data
> files. All it will accomplish is creating more of a maintenance headache
> for you.
>
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with[vbcol=seagreen]
>|||Thanks!
alex
"Pankaj Agarwal [MSFT]" <pankaja@.online.microsoft.com> wrote in message
news:ecnxtZHkEHA.2516@.cpmsftngxa10.phx.gbl...
> Hello Alex
> It depends on what you are trying to achieve. There is no set requirement
> or recommendation either way. However creating a lot of small files for a
> database could lead to additional maintenance chores. From performance
> standpoint, there should really be no difference either way unless you
> achieve stripping with multiple database files across several disk
> controllers and drives. However for a database of about 20GB in size, this
> striping may only give you small performance benefit.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
Is there any problem using 1 data file with restricted file growth
set to 20GB? I've heard that it's better to have multiple data files with
2GB each. Is that true?
Thanks!
AlexThat was true for Win95/98 and FAT file partitions. If you are using
Win2000 or higher and NTFS, the 20 GB single file is just fine.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Unless you're splitting filegroups up in order to put them on different
physical devices, there is, IMO, little benefit in creating multiple data
files. All it will accomplish is creating more of a maintenance headache
for you.
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Hello Alex
It depends on what you are trying to achieve. There is no set requirement
or recommendation either way. However creating a lot of small files for a
database could lead to additional maintenance chores. From performance
standpoint, there should really be no difference either way unless you
achieve stripping with multiple database files across several disk
controllers and drives. However for a database of about 20GB in size, this
striping may only give you small performance benefit.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks for the information. I'm really appreciated.
alex
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uBkGIFFkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> That was true for Win95/98 and FAT file partitions. If you are using
> Win2000 or higher and NTFS, the 20 GB single file is just fine.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with[vbcol=seagreen]
>|||Got it. Thanks!
alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ej7SNGFkEHA.704@.TK2MSFTNGP09.phx.gbl...
> Unless you're splitting filegroups up in order to put them on different
> physical devices, there is, IMO, little benefit in creating multiple data
> files. All it will accomplish is creating more of a maintenance headache
> for you.
>
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with[vbcol=seagreen]
>|||Thanks!
alex
"Pankaj Agarwal [MSFT]" <pankaja@.online.microsoft.com> wrote in message
news:ecnxtZHkEHA.2516@.cpmsftngxa10.phx.gbl...
> Hello Alex
> It depends on what you are trying to achieve. There is no set requirement
> or recommendation either way. However creating a lot of small files for a
> database could lead to additional maintenance chores. From performance
> standpoint, there should really be no difference either way unless you
> achieve stripping with multiple database files across several disk
> controllers and drives. However for a database of about 20GB in size, this
> striping may only give you small performance benefit.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
Any problem for using a single data file?
Hi there,
Is there any problem using 1 data file with restricted file growth
set to 20GB? I've heard that it's better to have multiple data files with
2GB each. Is that true?
Thanks!
AlexThat was true for Win95/98 and FAT file partitions. If you are using
Win2000 or higher and NTFS, the 20 GB single file is just fine.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Unless you're splitting filegroups up in order to put them on different
physical devices, there is, IMO, little benefit in creating multiple data
files. All it will accomplish is creating more of a maintenance headache
for you.
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Hello Alex
It depends on what you are trying to achieve. There is no set requirement
or recommendation either way. However creating a lot of small files for a
database could lead to additional maintenance chores. From performance
standpoint, there should really be no difference either way unless you
achieve stripping with multiple database files across several disk
controllers and drives. However for a database of about 20GB in size, this
striping may only give you small performance benefit.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks for the information. I'm really appreciated.
alex
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uBkGIFFkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> That was true for Win95/98 and FAT file partitions. If you are using
> Win2000 or higher and NTFS, the 20 GB single file is just fine.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> > Hi there,
> >
> > Is there any problem using 1 data file with restricted file
growth
> > set to 20GB? I've heard that it's better to have multiple data files
with
> > 2GB each. Is that true?
> >
> > Thanks!
> >
> > Alex
> >
> >
>|||Got it. Thanks!
alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ej7SNGFkEHA.704@.TK2MSFTNGP09.phx.gbl...
> Unless you're splitting filegroups up in order to put them on different
> physical devices, there is, IMO, little benefit in creating multiple data
> files. All it will accomplish is creating more of a maintenance headache
> for you.
>
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> > Hi there,
> >
> > Is there any problem using 1 data file with restricted file
growth
> > set to 20GB? I've heard that it's better to have multiple data files
with
> > 2GB each. Is that true?
> >
> > Thanks!
> >
> > Alex
> >
> >
>|||Thanks!
alex
"Pankaj Agarwal [MSFT]" <pankaja@.online.microsoft.com> wrote in message
news:ecnxtZHkEHA.2516@.cpmsftngxa10.phx.gbl...
> Hello Alex
> It depends on what you are trying to achieve. There is no set requirement
> or recommendation either way. However creating a lot of small files for a
> database could lead to additional maintenance chores. From performance
> standpoint, there should really be no difference either way unless you
> achieve stripping with multiple database files across several disk
> controllers and drives. However for a database of about 20GB in size, this
> striping may only give you small performance benefit.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
Is there any problem using 1 data file with restricted file growth
set to 20GB? I've heard that it's better to have multiple data files with
2GB each. Is that true?
Thanks!
AlexThat was true for Win95/98 and FAT file partitions. If you are using
Win2000 or higher and NTFS, the 20 GB single file is just fine.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Unless you're splitting filegroups up in order to put them on different
physical devices, there is, IMO, little benefit in creating multiple data
files. All it will accomplish is creating more of a maintenance headache
for you.
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Hello Alex
It depends on what you are trying to achieve. There is no set requirement
or recommendation either way. However creating a lot of small files for a
database could lead to additional maintenance chores. From performance
standpoint, there should really be no difference either way unless you
achieve stripping with multiple database files across several disk
controllers and drives. However for a database of about 20GB in size, this
striping may only give you small performance benefit.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks for the information. I'm really appreciated.
alex
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uBkGIFFkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> That was true for Win95/98 and FAT file partitions. If you are using
> Win2000 or higher and NTFS, the 20 GB single file is just fine.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> > Hi there,
> >
> > Is there any problem using 1 data file with restricted file
growth
> > set to 20GB? I've heard that it's better to have multiple data files
with
> > 2GB each. Is that true?
> >
> > Thanks!
> >
> > Alex
> >
> >
>|||Got it. Thanks!
alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ej7SNGFkEHA.704@.TK2MSFTNGP09.phx.gbl...
> Unless you're splitting filegroups up in order to put them on different
> physical devices, there is, IMO, little benefit in creating multiple data
> files. All it will accomplish is creating more of a maintenance headache
> for you.
>
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> > Hi there,
> >
> > Is there any problem using 1 data file with restricted file
growth
> > set to 20GB? I've heard that it's better to have multiple data files
with
> > 2GB each. Is that true?
> >
> > Thanks!
> >
> > Alex
> >
> >
>|||Thanks!
alex
"Pankaj Agarwal [MSFT]" <pankaja@.online.microsoft.com> wrote in message
news:ecnxtZHkEHA.2516@.cpmsftngxa10.phx.gbl...
> Hello Alex
> It depends on what you are trying to achieve. There is no set requirement
> or recommendation either way. However creating a lot of small files for a
> database could lead to additional maintenance chores. From performance
> standpoint, there should really be no difference either way unless you
> achieve stripping with multiple database files across several disk
> controllers and drives. However for a database of about 20GB in size, this
> striping may only give you small performance benefit.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
2012年2月25日星期六
Any load testing tool to test queries
Guys,
Is there any tool that can test the scalability of Analysis Services?
Basically we want to fire multiple queries to the Analysis Services and check if there is any performance degradation.
Is there any utility or tool that I can use?
Rgds
Hari
SQL Performance monitor works with SSAS too.|||The Community samples site has an ASLoadSim tool listed which might be worth a look http://www.codeplex.com/SQLSrvAnalysisSrvcs
2012年2月11日星期六
Answers needed for these multiple choice questions
Question 1) Can you use the functions nvl and decode as well as the CASE statement interchangeably?
A Only NVL with DECODE and vice versa
B Only Decode with CASE statement and vice versa.
C NVL with Decode or CASE statement as well as Decode and Case Statement interchangeably
D All three can be used interchangeably
Question 2) An inner join
A Is preferred if it meets business requirements
B Requires a (+) sign in the sql syntax for Oracle
C Should never be used on dimensional metrics
D A and B
E A and C only
Question 3) You can use a Sub-select in a query after
A The FROM clause
B The IN clause
C The '=' operator
D All of the above
E All of the above and more
Question 4) Many to Many relationships:
A Result in double-counting
B May be resolved in different ways but using a relationship table is always preferred.
C Should be avoided as far as possible for simplicity
D All of the above
E A and C only
Question 5) Views:
A Often minimize re-work during the development or integration phase
B Can be used to implement security
C Can be materialized in recent versions of Oracle
D None of the above
E All of the above
ThanksWhy do you want us to GIVE you these answers? Why don't you study and do the homework yourself?|||I sure did a sincere attempt as far as homework goes also with a mixed success. For example, when I tried this on my database , I found that Question 1) could be D, Question 3) could be E and so on. And however, I may not be the strongest in logical reasoning, and hence seeking suggestions because second opinions and suggestions are valuable as they are. Atleast I will have a chance to correct myself in the chance that I may be wrong in what I thought or found. It is possible.
Thanks
A Only NVL with DECODE and vice versa
B Only Decode with CASE statement and vice versa.
C NVL with Decode or CASE statement as well as Decode and Case Statement interchangeably
D All three can be used interchangeably
Question 2) An inner join
A Is preferred if it meets business requirements
B Requires a (+) sign in the sql syntax for Oracle
C Should never be used on dimensional metrics
D A and B
E A and C only
Question 3) You can use a Sub-select in a query after
A The FROM clause
B The IN clause
C The '=' operator
D All of the above
E All of the above and more
Question 4) Many to Many relationships:
A Result in double-counting
B May be resolved in different ways but using a relationship table is always preferred.
C Should be avoided as far as possible for simplicity
D All of the above
E A and C only
Question 5) Views:
A Often minimize re-work during the development or integration phase
B Can be used to implement security
C Can be materialized in recent versions of Oracle
D None of the above
E All of the above
ThanksWhy do you want us to GIVE you these answers? Why don't you study and do the homework yourself?|||I sure did a sincere attempt as far as homework goes also with a mixed success. For example, when I tried this on my database , I found that Question 1) could be D, Question 3) could be E and so on. And however, I may not be the strongest in logical reasoning, and hence seeking suggestions because second opinions and suggestions are valuable as they are. Atleast I will have a chance to correct myself in the chance that I may be wrong in what I thought or found. It is possible.
Thanks
Answer: Report Model/Builder
In a database there are 2 tables, one is student and the other is activities. A student can have multiple activities. There is a one to many relationship between student and activities based upon student_id. Not every student has an activity.
In report builder I would like all of the students to appear regardless of whether they have an activity or not. By default, only those students who have activities appear.
In report builder I would like all of the students to appear regardless of whether they have an activity or not. By default, only those students who have activities appear.
This appears:
Student Activity
1 Track
2 Track
4 Volleyball
I would like to see:
Student Activity
1 Track
2 Track
3 NULL
4 Volleyball
What settings need to be tweaked to make this happen?
Thanks for your assistance.Hi
What query are you using to generate this report? I created 2 tables: student, activity. Not all students have activities. If I use the following query:
select * from student
left outer join activity on student.i = activity.i
where i = student id in both the tables,
I get the list of students, activities (even for students who dont have activities mapped on the activity table)
The Left Outer Join operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.
Is this what you were looking for?
Thanks
|||Within Report Designer you can create a query and specify any type of join you need. This is IN REPORT BUILDER, where you are not able to specifiy the type of join you would like to use. The query is generated from the report model and dragging fields into the design area.Thanks for your response though.|||Hi Chris
Can you try this out and let me know if this works?
Create a report model and within the data source view, right click and add a NEW NAMED QUERY, here you can specify a query with joins. Once this query is created, create the report model. This should also list the query that you have created, apart from the 2 tables.
Now from Report Builder, select this datasource and drag/drop your query.
Remove the columns that you dont want (I just had student id and activity)
and run report.
This will give you all records, even the students who dont have activities listed.
If you are just using a report model, I think you need to edit that and add a query into that, so you can use it in your report builder to get the required data.
Thanks.|||
Actually, you don't need to create a named query to do this. You can do it in the Report Model. In your example, you need to set Cardinality=OptionalMany on the role from Student to activity. This tells RB that not all students have activities (so don’t do an inner join or you might lose a few).
Answer: Report Model/Builder
In a database there are 2 tables, one is student and the other is activities. A student can have multiple activities. There is a one to many relationship between student and activities based upon student_id. Not every student has an activity.
In report builder I would like all of the students to appear regardless of whether they have an activity or not. By default, only those students who have activities appear.
In report builder I would like all of the students to appear regardless of whether they have an activity or not. By default, only those students who have activities appear.
This appears:
Student Activity
1 Track
2 Track
4 Volleyball
I would like to see:
Student Activity
1 Track
2 Track
3 NULL
4 Volleyball
What settings need to be tweaked to make this happen?
Thanks for your assistance.Hi
What query are you using to generate this report? I created 2 tables: student, activity. Not all students have activities. If I use the following query:
select * from student
left outer join activity on student.i = activity.i
where i = student id in both the tables,
I get the list of students, activities (even for students who dont have activities mapped on the activity table)
The Left Outer Join operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.
Is this what you were looking for?
Thanks
|||Within Report Designer you can create a query and specify any type of join you need. This is IN REPORT BUILDER, where you are not able to specifiy the type of join you would like to use. The query is generated from the report model and dragging fields into the design area.Thanks for your response though.|||Hi Chris
Can you try this out and let me know if this works?
Create a report model and within the data source view, right click and add a NEW NAMED QUERY, here you can specify a query with joins. Once this query is created, create the report model. This should also list the query that you have created, apart from the 2 tables.
Now from Report Builder, select this datasource and drag/drop your query.
Remove the columns that you dont want (I just had student id and activity)
and run report.
This will give you all records, even the students who dont have activities listed.
If you are just using a report model, I think you need to edit that and add a query into that, so you can use it in your report builder to get the required data.
Thanks.|||
Actually, you don't need to create a named query to do this. You can do it in the Report Model. In your example, you need to set Cardinality=OptionalMany on the role from Student to activity. This tells RB that not all students have activities (so don’t do an inner join or you might lose a few).
2012年2月9日星期四
ANSI SQL Join syntax - multiple tables
I'm used to using the old-style JOIN syntax and performing the joins in the WHERE clause. I'm trying to figure out the ANSI syntax though for doing them in the FROM clause, and am curious if someone can help me out.
Here's the query I want:
SELECT *
FROM tableA A , tableB B , tableC C
WHERE A.col1 = B.col1
AND B.col2 = C.col2
Here is my converted ANSI (SQL-92) syntax:
SELECT *
FROM (tableA A JOIN tableB B ON A.col1 = B.col1 ) JOIN tableC C ON B.col2 = C.col2
Question:
Is my ANSI version correct (equivalent to the first query)?
Is that the only way to do it? I don't really like having to string multiple joins out with parenthesis like that.
Thanks.Sure...I would say INNER JOIN though (no that it matters)
You do know about outer joins though...right?
SELECT *
FROM tableA A
JOIN tableB B
ON A.col1 = B.col1
JOIN tableC C
ON B.col2 = C.col2|||the parentheses are definitely necessary in microsoft access, and nowhere else that i know of
i'm not sure if the parentheses will be ignored in sql server (this is the sql server forum, after all), because i've never coded them in sql server, because i know they aren't necessary
so if in doubt, leave them out, just remember to code them back in if you use microsoft access
rudy
http://r937.com/|||Originally posted by r937
the parentheses are definitely necessary in microsoft access, and nowhere else that i know of
i'm not sure if the parentheses will be ignored in sql server (this is the sql server forum, after all), because i've never coded them in sql server, because i know they aren't necessary
so if in doubt, leave them out, just remember to code them back in if you use microsoft access
rudy
http://r937.com/
Since when?
I always take them out in Access...same rules apply..esp when you duild your own sql in access...never use the qbe grid...(well maybe to select the columns...after that, it's painful...plus you can't union unless you go to the sql window...|||since when? since access 97
i dunno about later versions, perhaps the parentheses on 3+ table joins are no longer required, but they definitely were in access 97, and i ain't upgrading because i see no reason to
rudy|||I'm gonna check it out right now...didn't think so..
We're talking like SELECT a INNER join B on a.id = b.id inner join c on b.id = c.id...
right?|||yes, three tables or more, access 97 requires that you parenthesize them two at a time
select foo, bar
from (
a inner join b on a.id = b.id
)
inner join c on b.id = c.id
rudy|||Yup spent waaaay to much time with 2k...I remeber in 6.5 I think it was that way...
but for joins only...not in the predicates...
AND, Access still throws in waaaaaaaay too many parens...
You can get away with
SELECT *
FROM ((Table1 a
INNER JOIN table2 b
ON a.id = b.id)
INNER JOIN table3 c
ON b.id = c.id)
my bad...
Here's the query I want:
SELECT *
FROM tableA A , tableB B , tableC C
WHERE A.col1 = B.col1
AND B.col2 = C.col2
Here is my converted ANSI (SQL-92) syntax:
SELECT *
FROM (tableA A JOIN tableB B ON A.col1 = B.col1 ) JOIN tableC C ON B.col2 = C.col2
Question:
Is my ANSI version correct (equivalent to the first query)?
Is that the only way to do it? I don't really like having to string multiple joins out with parenthesis like that.
Thanks.Sure...I would say INNER JOIN though (no that it matters)
You do know about outer joins though...right?
SELECT *
FROM tableA A
JOIN tableB B
ON A.col1 = B.col1
JOIN tableC C
ON B.col2 = C.col2|||the parentheses are definitely necessary in microsoft access, and nowhere else that i know of
i'm not sure if the parentheses will be ignored in sql server (this is the sql server forum, after all), because i've never coded them in sql server, because i know they aren't necessary
so if in doubt, leave them out, just remember to code them back in if you use microsoft access
rudy
http://r937.com/|||Originally posted by r937
the parentheses are definitely necessary in microsoft access, and nowhere else that i know of
i'm not sure if the parentheses will be ignored in sql server (this is the sql server forum, after all), because i've never coded them in sql server, because i know they aren't necessary
so if in doubt, leave them out, just remember to code them back in if you use microsoft access
rudy
http://r937.com/
Since when?
I always take them out in Access...same rules apply..esp when you duild your own sql in access...never use the qbe grid...(well maybe to select the columns...after that, it's painful...plus you can't union unless you go to the sql window...|||since when? since access 97
i dunno about later versions, perhaps the parentheses on 3+ table joins are no longer required, but they definitely were in access 97, and i ain't upgrading because i see no reason to
rudy|||I'm gonna check it out right now...didn't think so..
We're talking like SELECT a INNER join B on a.id = b.id inner join c on b.id = c.id...
right?|||yes, three tables or more, access 97 requires that you parenthesize them two at a time
select foo, bar
from (
a inner join b on a.id = b.id
)
inner join c on b.id = c.id
rudy|||Yup spent waaaay to much time with 2k...I remeber in 6.5 I think it was that way...
but for joins only...not in the predicates...
AND, Access still throws in waaaaaaaay too many parens...
You can get away with
SELECT *
FROM ((Table1 a
INNER JOIN table2 b
ON a.id = b.id)
INNER JOIN table3 c
ON b.id = c.id)
my bad...
订阅：
博文 (Atom)