Any way to tell what traces are running?

I have a stored proc where I create a SQL trace (using sp_trace_create). I
went onto one of my servers to fire off a trace and noticed that another one
has been running for a few days.
Is there any way to display the traces that are running on a particular
server? I would like to stop the old trace but I can't figure out how
Any help would be appreciated.
Thanks in advance.
TomIt figures - I was looking for this info all day yesterday. Right after
posting this, I finally discovered it.
The following SQL statement displays info on traces running
SELECT * FROM :: fn_trace_getinfo(default)
It seems that when I started the new trace, it was assigned TraceId=1 and
the old trace was bumped to TraceId=2. I then just issued the following to
kill trace 2:
EXEC sp_trace_setstatus 2, 0
EXEC sp_trace_setstatus 2, 2
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:#MUmQPzuDHA.1088@.tk2msftngp13.phx.gbl...
> I have a stored proc where I create a SQL trace (using sp_trace_create).
I
> went onto one of my servers to fire off a trace and noticed that another
one
> has been running for a few days.
> Is there any way to display the traces that are running on a particular
> server? I would like to stop the old trace but I can't figure out how
> Any help would be appreciated.
> Thanks in advance.
> Tom
>|||You can run from QueryAnalyzer statement DBCC TRACESTATUS (-1) , and you will see all traces that have been set
Something like
TraceFlag TraceStatu
1204
Hope it Helps

Any way to suppress ADO error from stored proc?

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
>

Any way to easily add this computed column (divide by zero problem)

I have this query that I would rather not turn into a stored proc.
because the client really has no budget. I was hoping there would be a
way to add this statement inline to the following sql with some kind of
IF statement without writing it all out. The problem is either total
cost or conversions will have some zeros in the table.

SUM([total cost]/[cost per conversion])

Any help would be appreciated - Happy New Year

SELECT [Search Term], SUM([total cost]/[cost per conversion]) as calcw,
SUM([impressions]) AS impress, SUM([Total Cost]) AS totalcost,
SUM([Total Clicks]) AS totalclicks, SUM(Conversions) AS totalconv,
SUM([Cost Per Conversion]) AS costconv FROM csv where [start date]
>='01/01/04' and [end date] <='12/31/04' GROUP BY [Search Term] ORDER
BY [Search Term] ASCDo you just want to ignore the row in the sum if either value is zero? If
so:

SUM([total cost]/NULLIF([cost per conversion],0))

--
David Portas
SQL Server MVP
--|||I would rather the row be included as just returned as zero

Thanks!|||Also I tried that statement and it works well except I think the
calculation comes out incorrect - It needs to be something like the
SUM of total cost divived by the SUM of Conversions.

Thanks|||COALESCE( SUM([total cost]) / NULLIF(SUM([cost per conversion]),0) ,0)

--
David Portas
SQL Server MVP
--|||Thank you very much David - That seems to have done it!
Thanks again...

Any idea why the update won't run?

I have this store proc that ends at the update command. No matter where I
move it to, the whole thing seems to quit there.
CREATE PROCEDURE sp_LPModUser
@.ModFName NVarChar(100),
@.ModLName NVarChar(100),
@.ModLogon NVarChar(100),
@.ModPassword NVarChar(100),
@.ModLPUserID Int,
@.ModPageAccess Int,
@.ModChangePW Int,
@.Database NVarChar(100)
AS
Declare @.OldLogon nvarchar(100)
set @.OldLogon = (Select Login from LPUsers where lpuserid = @.ModLPUserID)
Declare @.Check integer
Set @.Check = (select Count(*) from master.dbo.sysxlogins where name =
@.ModLogon)
if @.Check = 0
Begin
exec sp_addlogin @.ModLogon, @.ModPassword, @.Database
exec sp_grantdbaccess @.ModLogon
exec sp_addrolemember 'db_datareader', @.ModLogon
exec sp_addrolemember 'db_datawriter', @.ModLogon
End
If @.OldLogon != @.ModLogon
Begin
exec sp_revokedbaccess @.OldLogon
exec sp_droplogin @.OldLogon
End
Begin
exec sp_password NULL, @.ModPassword, @.ModLogon
update lpusers set fname = @.ModFName, lname = @.ModLName, login = @.Modlogon,
[password] = @.ModPassword,
PageAccess = @.ModPageAccess, ChangePW = @.ModChangePW where lpuserid =
@.ModLPUserID
print 'test'
endNevermind, I got it.
"Shawn" <shawn.camner@.ccci.org> wrote in message
news:uXUEsGg6FHA.564@.TK2MSFTNGP10.phx.gbl...
>I have this store proc that ends at the update command. No matter where I
>move it to, the whole thing seems to quit there.
> CREATE PROCEDURE sp_LPModUser
> @.ModFName NVarChar(100),
> @.ModLName NVarChar(100),
> @.ModLogon NVarChar(100),
> @.ModPassword NVarChar(100),
> @.ModLPUserID Int,
> @.ModPageAccess Int,
> @.ModChangePW Int,
> @.Database NVarChar(100)
> AS
> Declare @.OldLogon nvarchar(100)
> set @.OldLogon = (Select Login from LPUsers where lpuserid = @.ModLPUserID)
> Declare @.Check integer
> Set @.Check = (select Count(*) from master.dbo.sysxlogins where name =
> @.ModLogon)
> if @.Check = 0
> Begin
> exec sp_addlogin @.ModLogon, @.ModPassword, @.Database
> exec sp_grantdbaccess @.ModLogon
> exec sp_addrolemember 'db_datareader', @.ModLogon
> exec sp_addrolemember 'db_datawriter', @.ModLogon
> End
> If @.OldLogon != @.ModLogon
> Begin
> exec sp_revokedbaccess @.OldLogon
> exec sp_droplogin @.OldLogon
> End
> Begin
> exec sp_password NULL, @.ModPassword, @.ModLogon
> update lpusers set fname = @.ModFName, lname = @.ModLName, login =
> @.Modlogon, [password] = @.ModPassword,
> PageAccess = @.ModPageAccess, ChangePW = @.ModChangePW where lpuserid =
> @.ModLPUserID
> print 'test'
> end
>

Anticipated PK Violation

I am maintaining a stored proc that does the following (pseudo code for simplicity)

INSERT INTO MyTable (CountColumn, ...) VALUES (1, ...)

-- 2627 = Primary key violation
IF (@.@.ERROR = 2627)
UPDATE MyTable SET CountColumn = CountColumn + 1
WHERE ...

Basically, try to create a new record with a PK and a count of 1. If a PK violation occurs, UPDATE the count of existing record.

Pretty simple logic. On some servers this works exactly as expected; the PK violation is caught and handled internally. On other servers, this error percolates out of the stored proc and causes the calling code to receive an error and fail. All servers are SQL Server 2000 SP3a Standard Edition. Any ideas why this happens?

Is there a better (and ideally as fast or faster) way to handle the INSERT/UPDATE issue? I can put the UPDATE first and INSERT if @.@.ROWCOUNT is 0, but that has the slight potential of a race condition where two processes try to INSERT the same PK at the same time.you could try a conditional flow control block like this:

if not exists (select * from mytable where PK = x)
insert ...
else
update ...

the subquery will always be executed, but it'll be quite fast with the search condition on the primary key

hth,
Cristian Babu|||I'd reverse the if...else:

if exists (...) update... else insert...|||I'd reverse the if...else:

if exists (...) update... else insert...

What I did is:

UPDATE
IF @.@.ROWCOUNT = 0 INSERT

I would think that is slightly more efficient.

What is really odd that the original approach (INSERT, IF ERROR THEN UPDATE) worked fine in Query Analyzer on two servers running SQL Server 2000 Personal edition but produces errors on a new installation of SQL Server 2000 Standard edition. Ideally, as a maintenance programmer, I wouldn't have had to touch the stored proc.|||Are the service pack levels identical on all 3 machines?|||Are the service pack levels identical on all 3 machines?

There is:
SQL Server Personal (no SP): The original code doesn't percolate an error
SQL Server Personal (SP3): The original code doesn't percolate an error
SQL Server Standard (SP3): The original code DOES percolate an error

I'm trying to get coworkers to patch the one unpatched server (we should ALWAYS patch our servers). But either way, it doesn't seem to be related to service patck since two systems with SP3 exhibit different behavior.