Anyone here familiar with stored procedures?

Hi there! I would like to return a default status value (-101) if the -100 and 200 conditions are not met. Is there any way I can go about it?

ALTER PROCEDURE spServiceFormGet (@.TicketNo int, @.Name char(50))
AS
IF NOT EXISTS (SELECT SolutionID FROM ServiceForm where SolutionID = @.TicketNo)
RETURN -100
IF EXISTS (SELECT SolutionID, SolvedBy FROM ServiceForm
WHERE SolutionID = @.TicketNo AND SolvedBy LIKE @.Name
OR SolutionID = @.TicketNo AND SolvedBy IS NULL)
SELECT * FROM serviceform where SolutionID=@.TicketNo
RETURN 200
ELSE <-- something like that
RETURN -101 <-- something like that

Thanks,
-Gabian-Gabian,
Do a google search on MSSQL Output Variables.

You should return a @.outputStatus variable here.

ScAndal|||I am not sure I understand what a return code is buying you in this case - Is there more to the proc than you are posting?

If there is no solutionid matching the ticket (first test) than you know there will be no solutionid matching a ticket AND a name...

Why not just execute the select with the (ticketid, name) filter and interogate the result set for records. If the count = 0 - you know there are no solutionid's matching this ticketno and/or name ??|||Thanks for answering guys, I've got it!

-Gabian-sql

Anyone have sp_oacreate and SQL2005 issues?

We use a few in house built COM objects that we call from stored procedures
quite often during the day. They are called from linked servers - so all
COM stuff is run on a dedicated box. Both servers are running SQL 2005 SP2.
The server that does all the COM object (sp_oacreate) work will lockup on a
daily basis and we have to restart sql service to fix. Also, we see SPIDs
on the COM box stuck in Rollback status and have to restart SQL to fix. Has
anyone seen any of these issues while migrating to SQL 2005?
Thanks,
Chuck Lathrope
www.sqlwebpedia.com
Chuck,
This is what you mean: - http://support.microsoft.com/kb/937277/en-us
which talks about sp_OAMethod not sp_OACreate.
Chris
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:120gcwua7do0w$.106toy5xoolnq.dlg@.40tude.net.. .
> We use a few in house built COM objects that we call from stored
> procedures
> quite often during the day. They are called from linked servers - so all
> COM stuff is run on a dedicated box. Both servers are running SQL 2005
> SP2.
> The server that does all the COM object (sp_oacreate) work will lockup on
> a
> daily basis and we have to restart sql service to fix. Also, we see SPIDs
> on the COM box stuck in Rollback status and have to restart SQL to fix.
> Has
> anyone seen any of these issues while migrating to SQL 2005?
> Thanks,
> Chuck Lathrope
> www.sqlwebpedia.com
|||On Thu, 19 Jul 2007 13:02:00 -0600, Chris Wood wrote:

> http://support.microsoft.com/kb/937277/en-us
That looks very promising! I have been searching net on sp_oacreate and
never saw this. Thanks. I will ask my PSS engineer if I can get the hotfix.
-Chuck
|||Hi Chuck,
Since you are on SP2, i doubt this hotfix will help you, as the SP2
build is 3042, and the above issue is fixed in 2232
HTH,
Dinesh
sql

Anyone have sp_oacreate and SQL2005 issues?

We use a few in house built COM objects that we call from stored procedures
quite often during the day. They are called from linked servers - so all
COM stuff is run on a dedicated box. Both servers are running SQL 2005 SP2.
The server that does all the COM object (sp_oacreate) work will lockup on a
daily basis and we have to restart sql service to fix. Also, we see SPIDs
on the COM box stuck in Rollback status and have to restart SQL to fix. Has
anyone seen any of these issues while migrating to SQL 2005?
Thanks,
Chuck Lathrope
www.sqlwebpedia.comChuck,
This is what you mean: - http://support.microsoft.com/kb/937277/en-us
which talks about sp_OAMethod not sp_OACreate.
Chris
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:120gcwua7do0w$.106toy5xoolnq.dlg@.40tude.net...
> We use a few in house built COM objects that we call from stored
> procedures
> quite often during the day. They are called from linked servers - so all
> COM stuff is run on a dedicated box. Both servers are running SQL 2005
> SP2.
> The server that does all the COM object (sp_oacreate) work will lockup on
> a
> daily basis and we have to restart sql service to fix. Also, we see SPIDs
> on the COM box stuck in Rollback status and have to restart SQL to fix.
> Has
> anyone seen any of these issues while migrating to SQL 2005?
> Thanks,
> Chuck Lathrope
> www.sqlwebpedia.com|||On Thu, 19 Jul 2007 13:02:00 -0600, Chris Wood wrote:

> http://support.microsoft.com/kb/937277/en-us
That looks very promising! I have been searching net on sp_oacreate and
never saw this. Thanks. I will ask my PSS engineer if I can get the hotfix.
-Chuck|||Hi Chuck,
Since you are on SP2, i doubt this hotfix will help you, as the SP2
build is 3042, and the above issue is fixed in 2232
HTH,
Dinesh

Anyone have sp_oacreate and SQL2005 issues?

We use a few in house built COM objects that we call from stored procedures
quite often during the day. They are called from linked servers - so all
COM stuff is run on a dedicated box. Both servers are running SQL 2005 SP2.
The server that does all the COM object (sp_oacreate) work will lockup on a
daily basis and we have to restart sql service to fix. Also, we see SPIDs
on the COM box stuck in Rollback status and have to restart SQL to fix. Has
anyone seen any of these issues while migrating to SQL 2005?
Thanks,
Chuck Lathrope
www.sqlwebpedia.comChuck,
This is what you mean: - http://support.microsoft.com/kb/937277/en-us
which talks about sp_OAMethod not sp_OACreate.
Chris
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:120gcwua7do0w$.106toy5xoolnq.dlg@.40tude.net...
> We use a few in house built COM objects that we call from stored
> procedures
> quite often during the day. They are called from linked servers - so all
> COM stuff is run on a dedicated box. Both servers are running SQL 2005
> SP2.
> The server that does all the COM object (sp_oacreate) work will lockup on
> a
> daily basis and we have to restart sql service to fix. Also, we see SPIDs
> on the COM box stuck in Rollback status and have to restart SQL to fix.
> Has
> anyone seen any of these issues while migrating to SQL 2005?
> Thanks,
> Chuck Lathrope
> www.sqlwebpedia.com|||On Thu, 19 Jul 2007 13:02:00 -0600, Chris Wood wrote:
> http://support.microsoft.com/kb/937277/en-us
That looks very promising! I have been searching net on sp_oacreate and
never saw this. Thanks. I will ask my PSS engineer if I can get the hotfix.
-Chuck|||Hi Chuck,
Since you are on SP2, i doubt this hotfix will help you, as the SP2
build is 3042, and the above issue is fixed in 2232
HTH,
Dinesh

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 query EXECUTE perms on stored procs?

I've recently been tasked with duplicating the permissions from one
account to another. We have a development, system test, and production
SQL Server, and approximately 35 databases in each. We use a fine
level of control on this particular account because it's what the
applications use to log in.
I've granted datareader/datawriter to the new account for all the dbs.
Each db, however, has a ton of stored procedures. Not all of them are
executable by the original account. I found a script that grants
access to all user stored procs, which is ok and will save me a few
days worth of point-and-click - but I'd be a happier DBA if I could
find a way to query the execute permissions on the stored procs to copy
them to the new account.
Any ideas? Below is the script I've used so far on the dev server.
SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin
with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs
Thanks,
Bill
A.K.A. PSPDBAThe PERMISSIONS system function can help you identify permission for the
current user:
http://msdn.microsoft.com/library/d...br />
6f78.asp
You could use it in combination with the SETUSER function:
http://msdn.microsoft.com/library/d...br />
6f78.asp
ML
http://milambda.blogspot.com/|||Ok, the sp_helprotect gives me back a list of the execute permissions
for the first user. I don't see how SETUSER is going to help me..|||PSPDBA (williambr@.state.pa.us) writes:
> Ok, the sp_helprotect gives me back a list of the execute permissions
> for the first user. I don't see how SETUSER is going to help me..
I guess the idea is:
SETUSER 'accountunderinvestigation'
go
SELECT name
FROM sysobjects
WHERE xtype = 'P'
AND permissions(id) & 32 = 1
go
SETUSER
That would list all the procedures that the account has permissions to
execute.
I should not that this solution contains three elements that are
deprecated in SQL 2005:
* The SETUSER command (Use EXECUTE AS instead)
* sysobjects (use sys.procedures instead)
* permissions(). (Use fn_my_permissions instead).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Any way to pass command line variables to osql

I want to pass a command line values into an osql run stored procedure.
The commandline values should be are the values to put into the insert stored procedure that writes them to a table.
Is it possible to do this.if ur parameter is positioned 2nd then add this code in your command file.

<varname> is some variable name that you want to use to trap the parameter. lets say empid for instance

SET empid=%2

after this, the place where u wud be calling ur stored procedure use this syntax.

osql -S servername -l 60 -n -E -d dbname
-Q"EXEC sp123 @.Var1 = '%empid%'"

Any way to make a stored procedure process asynchronously?

I'd like to know if there is any way to get a stored procedure to process
asynchronously. Ideally, I would kick off the procedure from within a
trigger. I would not need any return values or need to worry about
transactions.We do it by creating one-time jobs in our system. a bit cumbersome but
works.
Peter
"Random" <cipherlad@.hotmail.com> wrote in message
news:O4nvQqgQGHA.516@.TK2MSFTNGP15.phx.gbl...
> I'd like to know if there is any way to get a stored procedure to process
> asynchronously. Ideally, I would kick off the procedure from within a
> trigger. I would not need any return values or need to worry about
> transactions.
>|||Consider using Service Broker for this (assuming you are on 2005, no version
mentioned in the
OP...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Random" <cipherlad@.hotmail.com> wrote in message news:O4nvQqgQGHA.516@.TK2MSFTNGP15.phx.gbl
..
> I'd like to know if there is any way to get a stored procedure to process
asynchronously.
> Ideally, I would kick off the procedure from within a trigger. I would no
t need any return values
> or need to worry about transactions.
>|||Service Broker would be IDEAL if we were on version 2005. Unfortunately, we
cannot mandate at this time that all our clients move to 2005, so we are on
2000 for this.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23DlND0gQGHA.5808@.TK2MSFTNGP12.phx.gbl...
> Consider using Service Broker for this (assuming you are on 2005, no
> version mentioned in the OP...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Random" <cipherlad@.hotmail.com> wrote in message
> news:O4nvQqgQGHA.516@.TK2MSFTNGP15.phx.gbl...
>|||SQL Server is a database management system, it will typically process things
in sequential order and not asynchronously.
ADO supports the execution stored procedures, SQL, etc. asynchronously, so
perhaps the sequence of events would best be called from the application
side.
http://support.microsoft.com/defaul...kb;en-us;194960
http://msdn.microsoft.com/library/d...nc
2.asp
http://msdn2.microsoft.com/en-us/library/zw97wx20.aspx
Here is an article describing various methods of calling a DTS package from
T-SQL, including the option of calling (starting) a job asynchronously from
a trigger.
http://www.sqldts.com/default.aspx?219
I don't know what the cirsumstances or exact requirements are, but if it is
not important that the stored procedure complete within a specific time
window or within a transaction, then I have in the past implemented a table
that schedules tasks through the insertion of rows. A job can then be
scheduled to poll the table at intervals and execute the procedure calls as
needed. An added benefit is that the table itself is sort of a meta data
history of when the task has been performed.
"Random" <cipherlad@.hotmail.com> wrote in message
news:O4nvQqgQGHA.516@.TK2MSFTNGP15.phx.gbl...
> I'd like to know if there is any way to get a stored procedure to process
> asynchronously. Ideally, I would kick off the procedure from within a
> trigger. I would not need any return values or need to worry about
> transactions.
>|||I haven't ever tried this myself but I've heard of people using jobs to do
this and have the first stored proc manually start a job which kicks off the
second SP. This is obviously a lot less efficient than Service Broker but
it should work.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Random" <cipherlad@.hotmail.com> wrote in message
news:eR33oUhQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Service Broker would be IDEAL if we were on version 2005. Unfortunately,
> we cannot mandate at this time that all our clients move to 2005, so we
> are on 2000 for this.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23DlND0gQGHA.5808@.TK2MSFTNGP12.phx.gbl...
>

Any way to have a cursor variable reference an *existing* cursor?

(SQL Server 2000, SP3a)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name of the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it from stored
procedure B in a cursor variable.
I had hoped that maybe I could somehow divine the "handle" from sp_describe_cursor, but I
can't seem to "set" my cursor variable from any of this information. Something like (once
I retrieve the cursor_handle or something):
set @.MyCursor = cursor @.cursor_handle
Does anyone know of any mechanism I can use to accomplish this?
Thanks!
John PetersonI had hoped that something like this would work:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor = @.Cursor
output
execute('deallocate ' + @.CursorName)
But, alas, I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly something with the
[sp_executesql] line. No amount of massaging will get this to work -- my guess is that
the structure of [sp_executesql] won't permit a cursor variable to be handled. :-(
I'd appreciate any other suggestions!
Regards,
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uxudcE99DHA.3488@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello, all!
> I have an open global cursor that is created dynamically by stored procedure A. I'd
like
> to reference this cursor from stored procedure B. I know the dynamic name of the
cursor,
> but I know of no way to get a "handle" of this cursor so that I can use it from stored
> procedure B in a cursor variable.
> I had hoped that maybe I could somehow divine the "handle" from sp_describe_cursor, but
I
> can't seem to "set" my cursor variable from any of this information. Something like
(once
> I retrieve the cursor_handle or something):
> set @.MyCursor = cursor @.cursor_handle
> Does anyone know of any mechanism I can use to accomplish this?
> Thanks!
> John Peterson
>

Any way to have a cursor variable reference an *existing* cursor?

(SQL Server 2000, SP3a)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure
A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name o
f the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it f
rom stored
procedure B in a cursor variable.
I had hoped that maybe I could somehow divine the "handle" from sp_describe_
cursor, but I
can't seem to "set" my cursor variable from any of this information. Someth
ing like (once
I retrieve the cursor_handle or something):
set @.MyCursor = cursor @.cursor_handle
Does anyone know of any mechanism I can use to accomplish this?
Thanks!
John PetersonI had hoped that something like this would work:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor =
@.Cursor
output
execute('deallocate ' + @.CursorName)
But, alas, I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly some
thing with the
[sp_executesql] line. No amount of massaging will get this to work -- my gu
ess is that
the structure of [sp_executesql] won't permit a cursor variable to be handle
d. :-(
I'd appreciate any other suggestions!
Regards,
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uxudcE99DHA.3488@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello, all!
> I have an open global cursor that is created dynamically by stored procedure A. I
'd
like
> to reference this cursor from stored procedure B. I know the dynamic name of the[
/color]
cursor,
> but I know of no way to get a "handle" of this cursor so that I can use it
from stored
> procedure B in a cursor variable.
> I had hoped that maybe I could somehow divine the "handle" from sp_describe_cursor
, but
I
> can't seem to "set" my cursor variable from any of this information. Something li
ke
(once
> I retrieve the cursor_handle or something):
> set @.MyCursor = cursor @.cursor_handle
> Does anyone know of any mechanism I can use to accomplish this?
> Thanks!
> John Peterson
>

Any way to generate SQL Script for application role?

Is there any way to generate the SQL to create an application role and all t
he associated grants for table and stored procedure access? Or does anyone h
ave a suggestion for how to migrate an application role from one environment
to another? The developers
originally create the role by clicking on things.The easiest way is to script the existing application role permissions
and edit the script for the new environment. See sp_addapprole in SQL
BOL for the syntax for creating a new application role.
--Mary
On Fri, 16 Apr 2004 10:01:10 -0700, Charlotte
<anonymous@.discussions.microsoft.com> wrote:

>Is there any way to generate the SQL to create an application role and all the asso
ciated grants for table and stored procedure access? Or does anyone have a suggestio
n for how to migrate an application role from one environment to another? The develo
per
s originally create the role by clicking on things.sql

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 way to debug using SQL SP

Hi all,
is there any way to debug stored procedure line by line just like we are
debugging VB.Net on VS?
Thanks
Rena.
VS.NET does have a SQL Debugger in it.
From the VS.NET Help file:
==========================
In Visual Studio, you can debug a stored procedure from Server Explorer or a
source window.
To debug a stored procedure from Server Explorer
1.. Establish a connection to a database using Server Explorer. For more
information, see Server Explorer Window.
2.. Expand the <database name> node.
3.. Expand the Stored Procedures node.
4.. Right-click the stored procedure you want to debug and choose Step
Into Stored Procedure from the shortcut menu.
To debug a stored procedure in the text editor
a.. In a source window, right-click the stored procedure and choose Step
Into Stored Procedure from the shortcut menu.
For more information, see Enabling SQL Debugging on SQL 2000 Desktop
Engines.
Visual Studio also enables you to debug extended stored procedures. However,
you cannot debug SQL statements that are executed outside of a stored
procedure.
Rick Sawtell
MCT, MCSD, MCDBA
|||Rena wrote:
> Hi all,
> is there any way to debug stored procedure line by line just like
> we are debugging VB.Net on VS?
> Thanks
> Rena.
You can debug right from Query Analyzer.
David G.
|||Query Analyzer --> Object Browser --> Right click the SP -
-> Debug
regds,
Shrikant Patil
MCDBA

>--Original Message--
>Hi all,
> is there any way to debug stored procedure line by
line just like we are
>debugging VB.Net on VS?
>Thanks
>Rena.
>
>.
>
|||Thx all for the helps~~~
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:OR$tKoDkEHA.1800@.TK2MSFTNGP15.phx.gbl...
> VS.NET does have a SQL Debugger in it.
> From the VS.NET Help file:
> ==========================
> In Visual Studio, you can debug a stored procedure from Server Explorer or
a
> source window.
> To debug a stored procedure from Server Explorer
> 1.. Establish a connection to a database using Server Explorer. For more
> information, see Server Explorer Window.
> 2.. Expand the <database name> node.
> 3.. Expand the Stored Procedures node.
> 4.. Right-click the stored procedure you want to debug and choose Step
> Into Stored Procedure from the shortcut menu.
> To debug a stored procedure in the text editor
> a.. In a source window, right-click the stored procedure and choose Step
> Into Stored Procedure from the shortcut menu.
> For more information, see Enabling SQL Debugging on SQL 2000 Desktop
> Engines.
> Visual Studio also enables you to debug extended stored procedures.
However,
> you cannot debug SQL statements that are executed outside of a stored
> procedure.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Can you set a Break Point and only stop at that point, rather than
continually stepping?
If you are trying to trap a bug in the procedure, it is very useful to have
a breakpoint.
We can set one, but it never stops there, except when we are stepping
through line by line.
Cheers
Harry
"Rick Sawtell" wrote:

> VS.NET does have a SQL Debugger in it.
> From the VS.NET Help file:
> ==========================
> In Visual Studio, you can debug a stored procedure from Server Explorer or a
> source window.
> To debug a stored procedure from Server Explorer
> 1.. Establish a connection to a database using Server Explorer. For more
> information, see Server Explorer Window.
> 2.. Expand the <database name> node.
> 3.. Expand the Stored Procedures node.
> 4.. Right-click the stored procedure you want to debug and choose Step
> Into Stored Procedure from the shortcut menu.
> To debug a stored procedure in the text editor
> a.. In a source window, right-click the stored procedure and choose Step
> Into Stored Procedure from the shortcut menu.
> For more information, see Enabling SQL Debugging on SQL 2000 Desktop
> Engines.
> Visual Studio also enables you to debug extended stored procedures. However,
> you cannot debug SQL statements that are executed outside of a stored
> procedure.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||"Shrikant Patil" wrote:

> Query Analyzer --> Object Browser --> Right click the SP -
> -> Debug
>
> regds,
> Shrikant Patil
> MCDBA
>
> line just like we are
>
|||> "Shrikant Patil" wrote:
> Query Analyzer --> Object Browser --> Right click the SP -
> -> Debug
Hi,
I am a newbie to QA debugger. I noticed that this debugger can only be used
with stored procedures. I have created a following stored proc which in turn
calles an user defined function. Basically my intention is to debug the user
defined function.
create proc dbo.rp_test
(@.ordinal smallint)
AS
BEGIN
SELECT * FROM dbo.MyUserDefFunction( 'Carbon')
END
GO
Eventhough I could place break point at the select * statement, the
execution never stops at the break point.
Can anyone tell me what am I missing here? Or is there anyother better way
to debug MyUserDefFunction()?
Thanks for any help,
Kosu

Any way to debug using SQL SP

Hi all,
is there any way to debug stored procedure line by line just like we are
debugging VB.Net on VS'
Thanks
Rena.VS.NET does have a SQL Debugger in it.
From the VS.NET Help file:
==========================
In Visual Studio, you can debug a stored procedure from Server Explorer or a
source window.
To debug a stored procedure from Server Explorer
1.. Establish a connection to a database using Server Explorer. For more
information, see Server Explorer Window.
2.. Expand the <database name> node.
3.. Expand the Stored Procedures node.
4.. Right-click the stored procedure you want to debug and choose Step
Into Stored Procedure from the shortcut menu.
To debug a stored procedure in the text editor
a.. In a source window, right-click the stored procedure and choose Step
Into Stored Procedure from the shortcut menu.
For more information, see Enabling SQL Debugging on SQL 2000 Desktop
Engines.
Visual Studio also enables you to debug extended stored procedures. However,
you cannot debug SQL statements that are executed outside of a stored
procedure.
Rick Sawtell
MCT, MCSD, MCDBA|||Rena wrote:
> Hi all,
> is there any way to debug stored procedure line by line just like
> we are debugging VB.Net on VS'
> Thanks
> Rena.
You can debug right from Query Analyzer.
David G.|||Query Analyzer --> Object Browser --> Right click the SP -
-> Debug
regds,
Shrikant Patil
MCDBA

>--Original Message--
>Hi all,
> is there any way to debug stored procedure line by
line just like we are
>debugging VB.Net on VS'
>Thanks
>Rena.
>
>.
>|||Thx all for the helps~~~
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:OR$tKoDkEHA.1800@.TK2MSFTNGP15.phx.gbl...
> VS.NET does have a SQL Debugger in it.
> From the VS.NET Help file:
> ==========================
> In Visual Studio, you can debug a stored procedure from Server Explorer or
a
> source window.
> To debug a stored procedure from Server Explorer
> 1.. Establish a connection to a database using Server Explorer. For more
> information, see Server Explorer Window.
> 2.. Expand the <database name> node.
> 3.. Expand the Stored Procedures node.
> 4.. Right-click the stored procedure you want to debug and choose Step
> Into Stored Procedure from the shortcut menu.
> To debug a stored procedure in the text editor
> a.. In a source window, right-click the stored procedure and choose Step
> Into Stored Procedure from the shortcut menu.
> For more information, see Enabling SQL Debugging on SQL 2000 Desktop
> Engines.
> Visual Studio also enables you to debug extended stored procedures.
However,
> you cannot debug SQL statements that are executed outside of a stored
> procedure.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Can you set a Break Point and only stop at that point, rather than
continually stepping?
If you are trying to trap a bug in the procedure, it is very useful to have
a breakpoint.
We can set one, but it never stops there, except when we are stepping
through line by line.
Cheers
Harry
"Rick Sawtell" wrote:

> VS.NET does have a SQL Debugger in it.
> From the VS.NET Help file:
> ==========================
> In Visual Studio, you can debug a stored procedure from Server Explorer or
a
> source window.
> To debug a stored procedure from Server Explorer
> 1.. Establish a connection to a database using Server Explorer. For more
> information, see Server Explorer Window.
> 2.. Expand the <database name> node.
> 3.. Expand the Stored Procedures node.
> 4.. Right-click the stored procedure you want to debug and choose Step
> Into Stored Procedure from the shortcut menu.
> To debug a stored procedure in the text editor
> a.. In a source window, right-click the stored procedure and choose Step
> Into Stored Procedure from the shortcut menu.
> For more information, see Enabling SQL Debugging on SQL 2000 Desktop
> Engines.
> Visual Studio also enables you to debug extended stored procedures. Howeve
r,
> you cannot debug SQL statements that are executed outside of a stored
> procedure.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Shrikant Patil" wrote:

> Query Analyzer --> Object Browser --> Right click the SP -
> -> Debug
>
> regds,
> Shrikant Patil
> MCDBA
>
> line just like we are
>|||> "Shrikant Patil" wrote:
> Query Analyzer --> Object Browser --> Right click the SP -
> -> Debug
Hi,
I am a newbie to QA debugger. I noticed that this debugger can only be used
with stored procedures. I have created a following stored proc which in turn
calles an user defined function. Basically my intention is to debug the user
defined function.
create proc dbo.rp_test
(@.ordinal smallint)
AS
BEGIN
SELECT * FROM dbo.MyUserDefFunction( 'Carbon')
END
GO
Eventhough I could place break point at the select * statement, the
execution never stops at the break point.
Can anyone tell me what am I missing here? Or is there anyother better way
to debug MyUserDefFunction()?
Thanks for any help,
Kosu

Any way to debug using SQL SP

Hi all,
is there any way to debug stored procedure line by line just like we are
debugging VB.Net on VS'
Thanks
Rena.VS.NET does have a SQL Debugger in it.
From the VS.NET Help file:
==========================In Visual Studio, you can debug a stored procedure from Server Explorer or a
source window.
To debug a stored procedure from Server Explorer
1.. Establish a connection to a database using Server Explorer. For more
information, see Server Explorer Window.
2.. Expand the <database name> node.
3.. Expand the Stored Procedures node.
4.. Right-click the stored procedure you want to debug and choose Step
Into Stored Procedure from the shortcut menu.
To debug a stored procedure in the text editor
a.. In a source window, right-click the stored procedure and choose Step
Into Stored Procedure from the shortcut menu.
For more information, see Enabling SQL Debugging on SQL 2000 Desktop
Engines.
Visual Studio also enables you to debug extended stored procedures. However,
you cannot debug SQL statements that are executed outside of a stored
procedure.
Rick Sawtell
MCT, MCSD, MCDBA|||Rena wrote:
> Hi all,
> is there any way to debug stored procedure line by line just like
> we are debugging VB.Net on VS'
> Thanks
> Rena.
You can debug right from Query Analyzer.
--
David G.|||Query Analyzer --> Object Browser --> Right click the SP -
-> Debug
regds,
Shrikant Patil
MCDBA
>--Original Message--
>Hi all,
> is there any way to debug stored procedure line by
line just like we are
>debugging VB.Net on VS'
>Thanks
>Rena.
>
>.
>|||Thx all for the helps~~~ :)
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:OR$tKoDkEHA.1800@.TK2MSFTNGP15.phx.gbl...
> VS.NET does have a SQL Debugger in it.
> From the VS.NET Help file:
> ==========================> In Visual Studio, you can debug a stored procedure from Server Explorer or
a
> source window.
> To debug a stored procedure from Server Explorer
> 1.. Establish a connection to a database using Server Explorer. For more
> information, see Server Explorer Window.
> 2.. Expand the <database name> node.
> 3.. Expand the Stored Procedures node.
> 4.. Right-click the stored procedure you want to debug and choose Step
> Into Stored Procedure from the shortcut menu.
> To debug a stored procedure in the text editor
> a.. In a source window, right-click the stored procedure and choose Step
> Into Stored Procedure from the shortcut menu.
> For more information, see Enabling SQL Debugging on SQL 2000 Desktop
> Engines.
> Visual Studio also enables you to debug extended stored procedures.
However,
> you cannot debug SQL statements that are executed outside of a stored
> procedure.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Can you set a Break Point and only stop at that point, rather than
continually stepping?
If you are trying to trap a bug in the procedure, it is very useful to have
a breakpoint.
We can set one, but it never stops there, except when we are stepping
through line by line.
Cheers
Harry
"Rick Sawtell" wrote:
> VS.NET does have a SQL Debugger in it.
> From the VS.NET Help file:
> ==========================> In Visual Studio, you can debug a stored procedure from Server Explorer or a
> source window.
> To debug a stored procedure from Server Explorer
> 1.. Establish a connection to a database using Server Explorer. For more
> information, see Server Explorer Window.
> 2.. Expand the <database name> node.
> 3.. Expand the Stored Procedures node.
> 4.. Right-click the stored procedure you want to debug and choose Step
> Into Stored Procedure from the shortcut menu.
> To debug a stored procedure in the text editor
> a.. In a source window, right-click the stored procedure and choose Step
> Into Stored Procedure from the shortcut menu.
> For more information, see Enabling SQL Debugging on SQL 2000 Desktop
> Engines.
> Visual Studio also enables you to debug extended stored procedures. However,
> you cannot debug SQL statements that are executed outside of a stored
> procedure.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Shrikant Patil" wrote:
> Query Analyzer --> Object Browser --> Right click the SP -
> -> Debug
>
> regds,
> Shrikant Patil
> MCDBA
>
> >--Original Message--
> >Hi all,
> > is there any way to debug stored procedure line by
> line just like we are
> >debugging VB.Net on VS'
> >
> >Thanks
> >Rena.
> >
> >
> >.
> >
>|||> "Shrikant Patil" wrote:
> Query Analyzer --> Object Browser --> Right click the SP -
> -> Debug
Hi,
I am a newbie to QA debugger. I noticed that this debugger can only be used
with stored procedures. I have created a following stored proc which in turn
calles an user defined function. Basically my intention is to debug the user
defined function.
create proc dbo.rp_test
(@.ordinal smallint)
AS
BEGIN
SELECT * FROM dbo.MyUserDefFunction( 'Carbon')
END
GO
Eventhough I could place break point at the select * statement, the
execution never stops at the break point.
Can anyone tell me what am I missing here? Or is there anyother better way
to debug MyUserDefFunction()?
Thanks for any help,
Kosu

any way to debug stored procedures without buying visual studio?

Hi.

I am under the understanding that having the sql server 2005 db is not
enough (like 2000 was) to debug stored procedures.. that I need to
purchase (costly) visual studio 2005.

Can someone suggest a free or lower cost alternative?

sorry to be so cheap.. its the times I think.Jeff Kish (kishjjrjj@.charter.net) writes:

Quote:

Originally Posted by

I am under the understanding that having the sql server 2005 db is not
enough (like 2000 was) to debug stored procedures.. that I need to
purchase (costly) visual studio 2005.
>
Can someone suggest a free or lower cost alternative?
>
sorry to be so cheap.. its the times I think.

PRINT and SELECT is what I use, even in SQL 2000. I used to use the
T-SQL debugger occasionally, but I gave up on it. There were so many stars
that had to be aligned for it to work.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 2, 3:18 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Jeff Kish (kishjj...@.charter.net) writes:

Quote:

Originally Posted by

I am under the understanding that having the sql server 2005 db is not
enough (like 2000 was) to debug stored procedures.. that I need to
purchase (costly) visual studio 2005.

>

Quote:

Originally Posted by

Can someone suggest a free or lower cost alternative?

>

Quote:

Originally Posted by

sorry to be so cheap.. its the times I think.

>
PRINT and SELECT is what I use, even in SQL 2000. I used to use the
T-SQL debugger occasionally, but I gave up on it. There were so many stars
that had to be aligned for it to work.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

~
T-Sql debugger is some what okay, I used very long back.
I'm very comfortable with Print and Select commands to debug a stored
procedure.

~NR|||On Fri, 2 Feb 2007 10:18:24 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Jeff Kish (kishjjrjj@.charter.net) writes:

Quote:

Originally Posted by

>I am under the understanding that having the sql server 2005 db is not
>enough (like 2000 was) to debug stored procedures.. that I need to
>purchase (costly) visual studio 2005.
>>
>Can someone suggest a free or lower cost alternative?
>>
>sorry to be so cheap.. its the times I think.

>
>PRINT and SELECT is what I use, even in SQL 2000. I used to use the
>T-SQL debugger occasionally, but I gave up on it. There were so many stars
>that had to be aligned for it to work.

Thanks. I'll give it a try.
The debugger had been fairly nice to use when I had triggers and sp's
to debug.

I assume that it is a simple matter to invoke the procedure (I know
how to compile it) to get things to run?

Regards,
Jeff|||Jeff Kish (kishjjrjj@.charter.net) writes:

Quote:

Originally Posted by

The debugger had been fairly nice to use when I had triggers and sp's
to debug.
>
I assume that it is a simple matter to invoke the procedure (I know
how to compile it) to get things to run?

Not sure that I understand the question, just run it from Query Analyzer.

Triggers are a good point for the debugger. If you are not aware of that
there is a trigger, the debugger can make you aware, if you stick to F11.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Any way logging or tracking method for dropped tables

Most of my tables is dropped after they are created. I cannot tell when this
happen. This include stored procedures as well. I checked the maintenance job
and none are dropping tables or stored procedures.
The funny part is my tables and stored procedure which is published out as
articles remain and not dropped as others. Why?
Is there any logs or tracking that can be put in place to find out who
deleted my tables?
Paul,
I'm using SQL Server 2000 so I guess I would need to use the log explorer
tool. Are there any recommendation which I can try first?
Another question which hopefully can narrow down my suspect here is..Do you
think replication dropped the tables?
Cheers,
Philip
"Paul Ibison" wrote:

> If you're on SQL Server 2005 you could set up ddl triggers to log who/what
> is performing the drops. Alternatively a 3rd party log explorer tool will
> have the info in it.
> HTH,
> Paul Ibison
>
>
|||Replication by default will drop tables on the subscriber, but never on the
publisher so i'd look elsewhere. The reason replicated objects haven't been
dropped is probably because once they're published, they have to be removed
from the publication to be allowed to be dropped, so that distinguishes them
from your other objects. (this is a bit of guesswork, but it makes sense).
Lumigent Log Explorer will audit DDL commands to help you figure out who did
the drop (http://lumigent.com/products/le_sql_faq.html#_I_do_not). If it
happens regularly then you could use profiler yourself to monitor for these
drops.
HTH,
Paul Ibison

Any way around this error

I get the error (in red) below when I run this stored procedure (I'm not running in my app but in Query Analyzer) -- Please help me fix this

CREATE PROCEDURE dbo.sp_Employee_GetEmployeeLNameFNameEmpID
(
@.facilityID nvarchar(2),
@.companyID nvarchar(2),
@.deptID nvarchar(20),
@.Period int
)
AS
SELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullName FROM Employee e
WHERE e.DeptID = @.deptID AND e.FacilityID = @.facilityID AND e.CompanyID = @.companyID AND e.EmployeeID <> (SELECT ev.EmployeeID FROM EmployeeEval ev
WHERE PeriodID= @.Period)
GO

Server: Msg 512, Level 16, State 1, Procedure sp_Employee_GetEmployeeLNameFNameEmpID, Line 9
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

WHICH employee do you want from the ev table?
It looks to me like you want a NOT IN instead:
SELECT
e.LastName + ','+ e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e
WHEREe.DeptID = @.deptID
AND e.FacilityID = @.facilityID
AND e.CompanyID =@.companyID
AND e.EmployeeID NOT IN
(SELECT ev.EmployeeID
FROMEmployeeEval ev
WHERE PeriodID= @.Period)
|||

Sweet worked perfectly -- my book showed the IN part but I didn't know about the NOT IN.
Thanks a million.

Any type of Audit mechaninsm in SQL Server?

Does anyone know if there is any type of audit trail mechanism used in SQl Server 2000 or 20005?

In our production environment, some of our stored procedures, views and data in our tables has changed. It would be nice to have a footprint of what was changed by whom and one.

For our code we use source safe but for SQL Server I am not aware of any type of auditing mechanism.

AFAIK, there is no built in or an add-on from MS for auditing proc or schema changes in SQL Server. Either you need to manually control the change process (via custom app or VSS) or use some third party tools.