Sql ANSI Padding: procedure

显示标签为“procedure”的博文。显示所有博文

2012年3月20日星期二

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 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

2012年3月19日星期一

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

"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 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.

2012年3月11日星期日

any stored procedure to report all the databases' information

Hello everyone,
We're going to migrate our SQL 2000 server from one domain to another. I
need to get a better idea of how our current databases are structured and
utilized. What I want is a report showing each database's:
name
data size
log size
users
if backed up or not
Before I dive into it in my own way, just wondering if there is any stored
procedure that can help collect the above information? I'd appreciate any
help.
Thanks,
Bing
sp_helpdb does most what I wanted.
"bing" wrote:

> Hello everyone,
> We're going to migrate our SQL 2000 server from one domain to another. I
> need to get a better idea of how our current databases are structured and
> utilized. What I want is a report showing each database's:
> name
> data size
> log size
> users
> if backed up or not
> Before I dive into it in my own way, just wondering if there is any stored
> procedure that can help collect the above information? I'd appreciate any
> help.
> Thanks,
> Bing

any stored procedure to report all the databases' information

Hello everyone,
We're going to migrate our SQL 2000 server from one domain to another. I
need to get a better idea of how our current databases are structured and
utilized. What I want is a report showing each database's:
name
data size
log size
users
if backed up or not
Before I dive into it in my own way, just wondering if there is any stored
procedure that can help collect the above information? I'd appreciate any
help.
Thanks,
Bingsp_helpdb does most what I wanted.
"bing" wrote:
> Hello everyone,
> We're going to migrate our SQL 2000 server from one domain to another. I
> need to get a better idea of how our current databases are structured and
> utilized. What I want is a report showing each database's:
> name
> data size
> log size
> users
> if backed up or not
> Before I dive into it in my own way, just wondering if there is any stored
> procedure that can help collect the above information? I'd appreciate any
> help.
> Thanks,
> Bing

any stored procedure to report all the databases' information

Hello everyone,
We're going to migrate our SQL 2000 server from one domain to another. I
need to get a better idea of how our current databases are structured and
utilized. What I want is a report showing each database's:
name
data size
log size
users
if backed up or not
Before I dive into it in my own way, just wondering if there is any stored
procedure that can help collect the above information? I'd appreciate any
help.
Thanks,
Bingsp_helpdb does most what I wanted.
"bing" wrote:

> Hello everyone,
> We're going to migrate our SQL 2000 server from one domain to another. I
> need to get a better idea of how our current databases are structured and
> utilized. What I want is a report showing each database's:
> name
> data size
> log size
> users
> if backed up or not
> Before I dive into it in my own way, just wondering if there is any stored
> procedure that can help collect the above information? I'd appreciate any
> help.
> Thanks,
> Bing

2012年3月8日星期四

any scalable solution?

hi,
I have a table which stores some user details like email, name and Flag.
Now currently we have a stored procedure which runs on the table and selects
all the records from this table where flag=0 processes these records and
sets the flag to 1.
This process is working fine.
But now we want to deploy this service on multiple servers meaning this
procedure should be called on say two different servers. The procedures will
now be modified to say select first top 100 records and process them and
then update the flag to 1. simultaneously if the same proc is called from
another server then the 100 records selected by the first server should not
be selected by the proc called by the second server.
I am sorry if i m not clear.
any pointers will be helpful..
thanks for the replies.
jyo.Maybe one solution is to use a three valued logic.
Thats when you fetch rows for processing update them to a differenct status
code.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:uktaSMlGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> hi,
> I have a table which stores some user details like email, name and Flag.
> Now currently we have a stored procedure which runs on the table and
> selects
> all the records from this table where flag=0 processes these records and
> sets the flag to 1.
> This process is working fine.
> But now we want to deploy this service on multiple servers meaning this
> procedure should be called on say two different servers. The procedures
> will
> now be modified to say select first top 100 records and process them and
> then update the flag to 1. simultaneously if the same proc is called from
> another server then the 100 records selected by the first server should
> not
> be selected by the proc called by the second server.
> I am sorry if i m not clear.
> any pointers will be helpful..
> thanks for the replies.
> jyo.
>|||right this is what i m planning to do ... but in the time interval between
the select and update
in the first procedure call, if the second procedure call is made and the
same records may get selected.. and if locking is done then one of them one
of the call is selected as the deadlock victim...
I am looking for something like a rowlock... which even would not allow to
read the locked rows.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:e9krnQlGFHA.3472@.TK2MSFTNGP09.phx.gbl...
> Maybe one solution is to use a three valued logic.
> Thats when you fetch rows for processing update them to a differenct
status
> code.
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "harshal mistry" <harshal_in@.hotmail.com> wrote in message
> news:uktaSMlGFHA.2732@.TK2MSFTNGP15.phx.gbl...
and
from
>|||You can select using UpdLock table hint. This means you are trying to select
with the intent to update it later. So it will try to acquire IX (intent
exclusive) lock and later it will update it to X (exclusive) as and when u
you do the update. This is the way you can avoid deadlocks.
hth,
--
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:em71SflGFHA.2748@.tk2msftngp13.phx.gbl...
> right this is what i m planning to do ... but in the time interval between
> the select and update
> in the first procedure call, if the second procedure call is made and the
> same records may get selected.. and if locking is done then one of them
one
> of the call is selected as the deadlock victim...
> I am looking for something like a rowlock... which even would not allow to
> read the locked rows.
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:e9krnQlGFHA.3472@.TK2MSFTNGP09.phx.gbl...
> status
Flag.
> and
this
procedures
and
> from
should
>|||even after using updlock table hint it deadlocks the other process ...
harshal.
"avnrao" <avn@.newsgroups.com> wrote in message
news:uAQv6mlGFHA.3964@.TK2MSFTNGP14.phx.gbl...
> You can select using UpdLock table hint. This means you are trying to
select
> with the intent to update it later. So it will try to acquire IX (intent
> exclusive) lock and later it will update it to X (exclusive) as and when u
> you do the update. This is the way you can avoid deadlocks.
> hth,
> --
> Av.
> http://dotnetjunkies.com/WebLog/avnrao
> http://www28.brinkster.com/avdotnet
>
> "harshal mistry" <harshal_in@.hotmail.com> wrote in message
> news:em71SflGFHA.2748@.tk2msftngp13.phx.gbl...
between
the
> one
to
> Flag.
records
> this
> procedures
> and
> should
>|||can you check syslockinfo and sysprocesses table ifnormation..and find out
which one is causing the deadlock.
AFAIK, updlock should avoid the deadlock.
can you post your code..
if you want to resolve deadlock..you can use PSSDiag tool
http://www.microsoft.com/downloads/...&displaylang=en
hth,
--
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:uyUX75lGFHA.1528@.TK2MSFTNGP09.phx.gbl...
> even after using updlock table hint it deadlocks the other process ...
> harshal.
>
> "avnrao" <avn@.newsgroups.com> wrote in message
> news:uAQv6mlGFHA.3964@.TK2MSFTNGP14.phx.gbl...
> select
u
> between
> the
them
allow
> to
and
> records
them
called
>|||Try reading the rows, with a lock ( like updlock or xlock), but also use
readpast, which means to NOT wait on locked rows ( by the other process...
DO this in a transaction which includes the updating of the rows...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:uktaSMlGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> hi,
> I have a table which stores some user details like email, name and Flag.
> Now currently we have a stored procedure which runs on the table and
> selects
> all the records from this table where flag=0 processes these records and
> sets the flag to 1.
> This process is working fine.
> But now we want to deploy this service on multiple servers meaning this
> procedure should be called on say two different servers. The procedures
> will
> now be modified to say select first top 100 records and process them and
> then update the flag to 1. simultaneously if the same proc is called from
> another server then the 100 records selected by the first server should
> not
> be selected by the proc called by the second server.
> I am sorry if i m not clear.
> any pointers will be helpful..
> thanks for the replies.
> jyo.
>|||"harshal mistry" <harshal_in@.hotmail.com> wrote in message
news:uktaSMlGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> hi,
> I have a table which stores some user details like email, name and Flag.
> Now currently we have a stored procedure which runs on the table and
selects
> all the records from this table where flag=0 processes these records and
> sets the flag to 1.
> This process is working fine.
> But now we want to deploy this service on multiple servers meaning this
> procedure should be called on say two different servers. The procedures
will
> now be modified to say select first top 100 records and process them and
> then update the flag to 1. simultaneously if the same proc is called from
> another server then the 100 records selected by the first server should
not
> be selected by the proc called by the second server.
> I am sorry if i m not clear.
> any pointers will be helpful..
> thanks for the replies.
> jyo.
Maybe something like:
Step 1: Update Flag to an in use value depending on the server (Say flag
= -1 for Server 1, flag = -2 for server 2, etc.)
Step 2: Do the processing on records where flag = (-1) for Server 1
Step 3: Update Flag to 1
The key is to mark the records you want first.
In Step 1 only select from the pool where flag = 0.
The way you describe the problem sounds to me as if you may be using a
cursor. If so, you may want to rethink your solution to a something more
set based. If you aren't using a cursor, ignore this.
Good Luck,
Jim.

2012年3月6日星期二

Any reason not to use SET NOCOUNT ON in a stored procedure?

Is there any reason not to use "SET NOCOUNT ON" in a stored procedure?

The seemingly obvious possibility that it must be OFF to use @.@.ROWCOUNT is countered by the BOL entry which says "The @.@.ROWCOUNT function is updated even when SET NOCOUNT is ON."

That entry also states "For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced".

I have always understood that is good practise to have SET NOCOUNT ON is a stored procedure.

So is there any advantage (other than one less line to enter) of not having SET NOCOUNT ON is any stored procedure?

I do not think that the status of the NOCOUNT setting has anything to do with the use of the @.@.rowcount internal; look at this:

Code Snippet

set nocount on

select 1 as [on] union all select 2
select @.@.rowcount as [@.@.rowcount]

set nocount off

select 1 as [off] union all select 2
select @.@.rowcount as [@.@.rowcount]

/*
on
--
1
2

@.@.rowcount
--
2

off
--
1
2

(2 row(s) affected)

@.@.rowcount
--
2

(1 row(s) affected)
*/

I think that we had some old apps that had trouble when multiple ROWCOUNT messages were returned, but I don't really know the details of that. And this trouble had a good deal to do with setting NOCOUNT to ON in those particular applications.

|||

This is just one of those examples of why you should be very specific when you name things.

SET NOCOUNT ON *sounds* like it would suppress the rowcount being taken/stored, but in essense all it does is suppress the message that is sent to the client, so SET ROWCOUNT_MESSAGE OFF would be the best way to name it (or at the very least, the best way to think of it.)

The only advantage of not having it on is if your tools use the message for some purpose. Like if the UI expects to see the message after operations, and actually uses the information, then it is interesting.

|||

Set NOCOUNT ON is used to suppress the feedback message from the Server to Client. It wont affect the @.@.ROWCOUNT global variable.

It is something similar to the oracle's SET FEEDBACK OFF

|||

> So is there any advantage (other than one less line to enter) of not having SET NOCOUNT ON is any stored procedure?

Depend what are you using this sp for. Read the first note.

Updating Data Sources with DataAdapters

http://msdn2.microsoft.com/en-us/library/33y2221y(VS.80).aspx

AMB

|||

>Updating Data Sources with DataAdapters http://msdn2.microsoft.com/en-us/library/33y2221y(VS.80).aspx

Thank you for the refererence! Now a positive reason not to use "SET NOCOUNT ON", it interferes with the edit and update action on a DataAdapter, by causing an exception.

Any other reasons please?

Any query to return the execution time for another query?

Hi,
I prefer a query. Otherwise, a stored procedure will do.
Thank you in advance for enlightening me.
PradeepTry:
declare @.start datetime
set @.start = getdate()
-- do your query here
print datediff (ms, @.star, getdate())
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Pradeep" <pradeep@.tapadiya.net> wrote in message
news:1142815296.712493.28970@.i39g2000cwa.googlegroups.com...
Hi,
I prefer a query. Otherwise, a stored procedure will do.
Thank you in advance for enlightening me.
Pradeep|||Pradeep (pradeep@.tapadiya.net) writes:
> I prefer a query. Otherwise, a stored procedure will do.
> Thank you in advance for enlightening me.
In additions to Tom's suggestion, you can also use SET STATISTICS TIME ON.
However, I prefer using getdate() as Tom's example. After all, what
matters at the end of they day, is the wallclock time for a query.
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 one known SQL to change a sp.....

I know this may sound strange (me2) but a developer is convinced that the
SQL server is changing their stored procedure. Would SQL internally for any
reasons change code of a sp to optimize or anything?
CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
AS
-- Update records in tblDepartment with changed records in tblTempDept
UPDATE dbo.qry_Update_tblDeparment_Step1
SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
FROM dbo.qry_Update_tblDeparment_Step1
GO
--> CHANGES TO not use the view:
CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
AS
-- Update records in tblDepartment with changed records in tblTempDept
UPDATE dbo.tblDepartment
SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
FROM dbo.qry_Update_tblDeparment_Step1
GO
--> For reference here is the view used for the update.
CREATE VIEW dbo.qry_Update_tblDeparment_Step1
AS
SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
FROM dbo.tblTempDept INNER JOIN
dbo.tblDepartment ON dbo.tblTempDept.Dept_No =
dbo.tblDepartment.strDeptName AND
dbo.tblTempDept.Description <>
dbo.tblDepartment.strDescription
No
SQL Server is too 'stupid' to do that. It takes what you give it, stores it,
figures out how to execute it an executes it. Nothing else.
Put a comment line in with a manually incremented version number and see it
that changes. Maybe there is some code in an application that does it.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:e3rlxqeRFHA.3684@.TK2MSFTNGP10.phx.gbl...
>I know this may sound strange (me2) but a developer is convinced that the
>SQL server is changing their stored procedure. Would SQL internally for
>any reasons change code of a sp to optimize or anything?
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.qry_Update_tblDeparment_Step1
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
> --> CHANGES TO not use the view:
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.tblDepartment
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
>
> --> For reference here is the view used for the update.
> CREATE VIEW dbo.qry_Update_tblDeparment_Step1
> AS
> SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
> dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
> FROM dbo.tblTempDept INNER JOIN
> dbo.tblDepartment ON dbo.tblTempDept.Dept_No =
> dbo.tblDepartment.strDeptName AND
> dbo.tblTempDept.Description <>
> dbo.tblDepartment.strDescription
>
>
|||ever investigated database change management?
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"CD" wrote:

> I know this may sound strange (me2) but a developer is convinced that the
> SQL server is changing their stored procedure. Would SQL internally for any
> reasons change code of a sp to optimize or anything?
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.qry_Update_tblDeparment_Step1
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
> --> CHANGES TO not use the view:
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.tblDepartment
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
>
> --> For reference here is the view used for the update.
> CREATE VIEW dbo.qry_Update_tblDeparment_Step1
> AS
> SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
> dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
> FROM dbo.tblTempDept INNER JOIN
> dbo.tblDepartment ON dbo.tblTempDept.Dept_No =
> dbo.tblDepartment.strDeptName AND
> dbo.tblTempDept.Description <>
> dbo.tblDepartment.strDescription
>
>

2012年2月25日星期六

Any one known SQL to change a sp.....

I know this may sound strange (me2) but a developer is convinced that the
SQL server is changing their stored procedure. Would SQL internally for any
reasons change code of a sp to optimize or anything?
CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
AS
-- Update records in tblDepartment with changed records in tblTempDept
UPDATE dbo.qry_Update_tblDeparment_Step1
SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
FROM dbo.qry_Update_tblDeparment_Step1
GO
--> CHANGES TO not use the view:
CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
AS
-- Update records in tblDepartment with changed records in tblTempDept
UPDATE dbo.tblDepartment
SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
FROM dbo.qry_Update_tblDeparment_Step1
GO
--> For reference here is the view used for the update.
CREATE VIEW dbo.qry_Update_tblDeparment_Step1
AS
SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
FROM dbo.tblTempDept INNER JOIN
dbo.tblDepartment ON dbo.tblTempDept.Dept_No = dbo.tblDepartment.strDeptName AND
dbo.tblTempDept.Description <>
dbo.tblDepartment.strDescriptionNo
SQL Server is too 'stupid' to do that. It takes what you give it, stores it,
figures out how to execute it an executes it. Nothing else.
Put a comment line in with a manually incremented version number and see it
that changes. Maybe there is some code in an application that does it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:e3rlxqeRFHA.3684@.TK2MSFTNGP10.phx.gbl...
>I know this may sound strange (me2) but a developer is convinced that the
>SQL server is changing their stored procedure. Would SQL internally for
>any reasons change code of a sp to optimize or anything?
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.qry_Update_tblDeparment_Step1
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
> --> CHANGES TO not use the view:
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.tblDepartment
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
>
> --> For reference here is the view used for the update.
> CREATE VIEW dbo.qry_Update_tblDeparment_Step1
> AS
> SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
> dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
> FROM dbo.tblTempDept INNER JOIN
> dbo.tblDepartment ON dbo.tblTempDept.Dept_No => dbo.tblDepartment.strDeptName AND
> dbo.tblTempDept.Description <>
> dbo.tblDepartment.strDescription
>
>|||ever investigated database change management?
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"CD" wrote:
> I know this may sound strange (me2) but a developer is convinced that the
> SQL server is changing their stored procedure. Would SQL internally for any
> reasons change code of a sp to optimize or anything?
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.qry_Update_tblDeparment_Step1
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
> --> CHANGES TO not use the view:
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.tblDepartment
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
>
> --> For reference here is the view used for the update.
> CREATE VIEW dbo.qry_Update_tblDeparment_Step1
> AS
> SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
> dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
> FROM dbo.tblTempDept INNER JOIN
> dbo.tblDepartment ON dbo.tblTempDept.Dept_No => dbo.tblDepartment.strDeptName AND
> dbo.tblTempDept.Description <>
> dbo.tblDepartment.strDescription
>
>

Any one known SQL to change a sp.....

I know this may sound strange (me2) but a developer is convinced that the
SQL server is changing their stored procedure. Would SQL internally for any
reasons change code of a sp to optimize or anything?
CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
AS
-- Update records in tblDepartment with changed records in tblTempDept
UPDATE dbo.qry_Update_tblDeparment_Step1
SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
FROM dbo.qry_Update_tblDeparment_Step1
GO
--> CHANGES TO not use the view:
CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
AS
-- Update records in tblDepartment with changed records in tblTempDept
UPDATE dbo.tblDepartment
SET strDescription = [qry_Update_tblDeparment_Step1].[Description]
FROM dbo.qry_Update_tblDeparment_Step1
GO
--> For reference here is the view used for the update.
CREATE VIEW dbo.qry_Update_tblDeparment_Step1
AS
SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
FROM dbo.tblTempDept INNER JOIN
dbo.tblDepartment ON dbo.tblTempDept.Dept_No =
dbo.tblDepartment.strDeptName AND
dbo.tblTempDept.Description <>
dbo.tblDepartment.strDescriptionNo
SQL Server is too 'stupid' to do that. It takes what you give it, stores it,
figures out how to execute it an executes it. Nothing else.
Put a comment line in with a manually incremented version number and see it
that changes. Maybe there is some code in an application that does it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:e3rlxqeRFHA.3684@.TK2MSFTNGP10.phx.gbl...
>I know this may sound strange (me2) but a developer is convinced that the
>SQL server is changing their stored procedure. Would SQL internally for
>any reasons change code of a sp to optimize or anything?
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.qry_Update_tblDeparment_Step1
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description
]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
> --> CHANGES TO not use the view:
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.tblDepartment
> SET strDescription = [qry_Update_tblDeparment_Step1].[Description
]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
>
> --> For reference here is the view used for the update.
> CREATE VIEW dbo.qry_Update_tblDeparment_Step1
> AS
> SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
> dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
> FROM dbo.tblTempDept INNER JOIN
> dbo.tblDepartment ON dbo.tblTempDept.Dept_No =
> dbo.tblDepartment.strDeptName AND
> dbo.tblTempDept.Description <>
> dbo.tblDepartment.strDescription
>
>|||ever investigated database change management?
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"CD" wrote:

> I know this may sound strange (me2) but a developer is convinced that the
> SQL server is changing their stored procedure. Would SQL internally for a
ny
> reasons change code of a sp to optimize or anything?
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.qry_Update_tblDeparment_Step1
> SET strDescription = [qry_Update_tblDeparment_Step1].[Descriptio
n]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
> --> CHANGES TO not use the view:
> CREATE PROCEDURE dbo.pr_Update_tblDepartment_Step1
> AS
> -- Update records in tblDepartment with changed records in tblTempDept
> UPDATE dbo.tblDepartment
> SET strDescription = [qry_Update_tblDeparment_Step1].[Descriptio
n]
> FROM dbo.qry_Update_tblDeparment_Step1
> GO
>
> --> For reference here is the view used for the update.
> CREATE VIEW dbo.qry_Update_tblDeparment_Step1
> AS
> SELECT dbo.tblTempDept.Description, dbo.tblDepartment.strDescription,
> dbo.tblTempDept.Dept_No, dbo.tblDepartment.strDeptName
> FROM dbo.tblTempDept INNER JOIN
> dbo.tblDepartment ON dbo.tblTempDept.Dept_No =
> dbo.tblDepartment.strDeptName AND
> dbo.tblTempDept.Description <>
> dbo.tblDepartment.strDescription
>
>

2012年2月23日星期四

Any ideas on how to speed up this sp?

it is working but takes about 3-4 seconds per exec.

CREATE PROCEDURE isp_ap_calc_apt_totals
@.p_comp char(2),
@.p_vend char(6),
@.p_asofdate char(8)
as

if (@.p_asofdate <= '00000000')
begin
set @.p_asofdate = '99999999'
end

delete from XAPAPTTOT
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate

insert into XAPAPTTOT
select apph_comp, apph_vend, apph_type, apph_id, @.p_asofdate,
sum(apph_paymnts),
sum(apph_discts),
sum(apph_adjts),
count(apph_paymnts),
sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) +
b.apt_gross,
0,
max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0'))
from APPHISTF.a join APTRANF.b on b.apt_comp = a.apph_comp and b.apt_vend = a.apph_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id
where ((a.apph_comp = @.p_comp) and (a.apph_vend = @.p_vend) and (a.apph_unpost_dt = 0)
and (str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @.p_asofdate))
or ((a.apph_unpost_dt > 0 and a.apph_unpost_dt <= @.p_asofdate and b.apt_unposted_fg = 1 and b.apt_comp = @.p_comp and b.apt_vend = @.p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
or (((str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @.p_asofdate) and a.apph_unpost_dt > @.p_asofdate and b.apt_comp = @.p_comp and b.apt_vend = @.p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
group by apph_comp, apph_vend, apph_type, apph_id

update XAPAPTTOT
set xapt_last_payck =
(select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp and apph_vend = xapt_vend and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate
GOPost the DDL and the indexes for the tables. Read the hint sticky at the top of the forum. Sample data might help as well, but what kind of volume are we talking about?|||the volume varies(multiple customers)
1,000-10,000 APTRANF and APPHISTF records
the Relationship between the tables is for each APTRANF record
you can have 0 to 9999 APPHISTF records.(generally only 0 or 1)
in unusually instances the APPHISTF might have 2-5 records.
the APPHISTF is a payment history(detail) to the APTRANF(master)
we allow unposting of a payment (apph_unpost_dt) and reissueing a new payment.
not sure about DDL ?|||Read this link here

http://www.dbforums.com/t1196943.html|||Hi,
Since I don't have any idea of your table structures and indexes thereon, I would go with eliminating redundencies in your code to reduce time. Several of the conditions and calculations are repeated and have now been changed to occur once. The code is given below. Hope this helps:

CREATE PROCEDURE isp_ap_calc_apt_totals
@.p_comp char(2),
@.p_vend char(6),
@.p_asofdate char(8)
as

if (@.p_asofdate <= '00000000')
set @.p_asofdate = '99999999'

delete from XAPAPTTOT
where xapt_comp = @.p_comp
and xapt_vend = @.p_vend
and xapt_asof_date = @.p_asofdate

insert into XAPAPTTOT
select apph_comp
,apph_vend
,apph_type
,apph_id
,@.p_asofdate
,sum(apph_paymnts)
,sum(apph_discts)
,sum(apph_adjts)
,count(apph_paymnts)
,sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) + b.apt_gross
,0
,max(str_1)
from (select apph_comp
,apph_vend
,apph_type
,apph_id
,apph_paymnts
,apph_discts
,apph_adjts
,apph_paymnts
,apph_unpost_dt
,str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') str_1
from APPHISTF
where apph_comp = @.p_comp
and apph_vend = @.p_vend) a

inner join

(select apt_gross
,apt_type
,apt_id
,apt_unposted_fg
from APTRANF
where bapt_comp = @.p_comp
and apt_vend = @.p_vend) b

on ( b.apt_type = a.apph_type
and b.apt_id = a.apph_id)

where (a.apph_unpost_dt = 0
and a.str_1 <= @.p_asofdate)
or (a.apph_unpost_dt > 0
and a.apph_unpost_dt <= @.p_asofdate
and b.apt_unposted_fg = 1)
or (a.str_1 <= @.p_asofdate
and a.apph_unpost_dt > @.p_asofdate)

group by apph_comp, apph_vend, apph_type, apph_id

update XAPAPTTOT
set xapt_last_payck = (select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp
and apph_vend = xapt_vend
and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @.p_comp and xapt_vend = @.p_vend and xapt_asof_date = @.p_asofdate
GO

--Scalability Experts.

订阅：博文 (Atom)

2012年3月20日星期二

Any way to suppress ADO error from stored proc?

Any way to pass command line variables to osql

Any way to make a stored procedure process asynchronously?

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

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

Any way to generate SQL Script for application role?

2012年3月19日星期一

Any way to debug using SQL SP

Any way to debug using SQL SP

Any way to debug using SQL SP

Any way around this error

2012年3月11日星期日

any stored procedure to report all the databases' information

any stored procedure to report all the databases' information

any stored procedure to report all the databases' information

2012年3月8日星期四

any scalable solution?

2012年3月6日星期二

Any reason not to use SET NOCOUNT ON in a stored procedure?

Any query to return the execution time for another query?

Any one known SQL to change a sp.....

2012年2月25日星期六

Any one known SQL to change a sp.....

Any one known SQL to change a sp.....

2012年2月23日星期四

Any ideas on how to speed up this sp?

Sql ANSI Padding

Blog Archive

About Me