Pblm with Nested transactions

Friends,

I've a very basic doubt with Nested transactions (across procedures) in SQL Server and i guess the given below sample code illustarates my doubt well more than my words ..

I've a Proc1 like this

create procedure sp_proc1
as
begin
begin tran sp_proc1
insert into tab1 values (1,2)
exec sp_proc2 1
if <Some cdn statement>
rollback tran sp_proc1
else
commit tran sp_proc1
end

and called proc sp_proc2 is like this

create procedure sp_proc2
(
@.val1 int
)
as
begin tran proc2
update tab2 set col1 = 5
IF <some cdn statement>
begin
rollback tran proc2
end
else
begin
commit tran proc2
end

The pblm is when the 1st proc is executed and when the cdn statement in the 2nd proc is sucess, then it results with the error

Failed to retreive execution plan: Cannot roll back proc2. No transaction or savepoint of that name was found.

any suggestions

--SQLPgmrbegin tran proc2
update tab2 set col1 = 5
IF <some cdn statement> << Checking your tab2 but it still in tran !
begin
...

I think you probably are trying to check some thing that you are not commit yet!

Pauzing SQL Queue processing

Hello,

I'm currently looking for a way to decently pauze the processing of messages by activation procedures. When I pauze the processing I want to be sure that the running activation procedures terminated.
I thought I could do this using ALTER QUEUE WITH ACTIVATION (STATUS = OFF) and sys.dm_broker_activated_tasks.
This is what I've tested: I set up one queue with an activation procedure attached to it that receives one message, then waits for one minute, then writes that message into a table. The queues max queue readers is set to 2. Now, I put 5 messages in the queue, 2 procs pop up in sys.dm_broker_activated_tasks. I run the alter queue statement shown above and check sys.dm_broker_activated_tasks again. It is empty so allegedly there are no tasks running. I check the table in which the messages are written by the activation procs and after some time I can see the results of both activation procedures which were according to sys.dm_broker_activated_tasks not running (but of course they were). The rest of the process works as expected, the procs reactivate upon reenabling the activation status and all is well.
Is there another way of checking the running activation procedures that checking sys.dm_broker_activated_tasks?
Is this intended behaviour or just a bug?When you turn off activation, the activation routines stop running on the queue so the dmv's aren't maintained but any processes already running will continue to run until they end themselves. If you really want to prevent messages from being processed, you would be better off making the queue not ready. If you want to keep the queues active and end the activated procedures you will have to kill the activated procedures in addition to turning oss activation.|||Disabling the queue entirely does cause the expected behaviour in dm_broker_activated_tasks. My solution to pauze is now to disable the queue, check dm_broker_activated_tasks and wait for all queue readers to finish, then disable the activation on the queue and reenable the queue itself. Resuming is then as easy as reenabling activation on the queue.

In fact I didn't want to disable the queue because that seems risky to me. Notably, messages will keep on being sent to the disabled queue (because they are delivered whenever an even occurs) but they won't arrive safely in it. Therefore I'm reenabling the queue asap after having disabled it.

Still I think that dm_broker_activated_tasks should remain updated for as long as a queue reader is running.

Thank you,
Kr

R.|||If the queue is disabled, messages will be held in the sys.transmission_queue so there's no loss of reliability and the messages will be transferred to the target queue when it becomes available.

pausing stored procedures

Is there any way to execute a stored procedure, get it's thread id and pause it in someway...particularly in another stored procedure?

If not, I guess I could manually create a mechanism. This would most likely be used for stored procedures that run for long amounts of time in a while loop where i could just check a flag in a record representing the instance of the stored procedure at the top of the loop and do a WAITFOR DELAY within a nexted loop and have the condition set to end when the flag is marked as 'run' as opposed to 'halt.'You can look at the SP. Find a query that it hasn't executed yet and lock a table that is involved in that query.

You could also dedicate a resource table to the SP, code a select or update of that table before every statement then locking that table will pause the SP.|||Does the SQL Profiler read information from a table in the master database or something? I am wondering how it knows what stored procedures are running and if I can use that information in this experiment.

Patterns & Practices

I have looked through the Patterns & Practices information and cannot find
anything on naming conventions for SQL Server stored procedures, tables, and
user-defined functions. Where might this information be found?
Thanks,
MikeHere's one:
http://vyaskn.tripod.com/object_naming.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"memead" <memead@.discussions.microsoft.com> wrote in message
news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
>I have looked through the Patterns & Practices information and cannot find
> anything on naming conventions for SQL Server stored procedures, tables, and
> user-defined functions. Where might this information be found?
> Thanks,
> Mike|||Thank you so much for your timely response.
Mike
"Tibor Karaszi" wrote:
> Here's one:
> http://vyaskn.tripod.com/object_naming.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "memead" <memead@.discussions.microsoft.com> wrote in message
> news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
> >I have looked through the Patterns & Practices information and cannot find
> > anything on naming conventions for SQL Server stored procedures, tables, and
> > user-defined functions. Where might this information be found?
> >
> > Thanks,
> >
> > Mike
>|||Vyas has some GREAT scripts here too: http://vyaskn.tripod.com/code.htm
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:BF3CF558-1162-4057-ABD8-841A9CF541EB@.microsoft.com...
> Here's one:
> http://vyaskn.tripod.com/object_naming.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "memead" <memead@.discussions.microsoft.com> wrote in message
> news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
>>I have looked through the Patterns & Practices information and cannot find
>> anything on naming conventions for SQL Server stored procedures, tables,
>> and
>> user-defined functions. Where might this information be found?
>> Thanks,
>> Mike
>|||Thank you for the information.
"TheSQLGuru" wrote:
> Vyas has some GREAT scripts here too: http://vyaskn.tripod.com/code.htm
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:BF3CF558-1162-4057-ABD8-841A9CF541EB@.microsoft.com...
> > Here's one:
> >
> > http://vyaskn.tripod.com/object_naming.htm
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "memead" <memead@.discussions.microsoft.com> wrote in message
> > news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
> >>I have looked through the Patterns & Practices information and cannot find
> >> anything on naming conventions for SQL Server stored procedures, tables,
> >> and
> >> user-defined functions. Where might this information be found?
> >>
> >> Thanks,
> >>
> >> Mike
> >
>
>sql

Passing SELECTed rows to a calling SP

Hi!
I'm a new T-SQL developer and just hit a roadblock.
I have a scenario that goes like this: I have 2 stored procedures,
spInner and spOuter. spInner has a SELECT statement which would
normally be used by a class using MS Enterprise Library and that output
goes into a DataSet. However, I need to get the output of the SELECT
statement to go into spOuter and that's what I can't seem to figure
out.
I know I may be asked to use functions that return tables in replies to
this post, but I can't do that as some parts of my application have an
EXEC(string) for dynamic SQL, instead of spInner.
Any help appreciated.
Cheers,
N.I.T.I.N.For the results of spInner to be available to spOuter, there are a few
options. I have used temporary tables or persisted the data into a real
table to avoid recompiles and also have a record of the data. If you use the
latter option, you can use a guid column and each row with a guid obtained
from spOuter. This way you can easily pick up the rows you need.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||NiTiN (emailme.nitin@.gmail.com) writes:
> I'm a new T-SQL developer and just hit a roadblock.
> I have a scenario that goes like this: I have 2 stored procedures,
> spInner and spOuter. spInner has a SELECT statement which would
> normally be used by a class using MS Enterprise Library and that output
> goes into a DataSet. However, I need to get the output of the SELECT
> statement to go into spOuter and that's what I can't seem to figure
> out.
> I know I may be asked to use functions that return tables in replies to
> this post, but I can't do that as some parts of my application have an
> EXEC(string) for dynamic SQL, instead of spInner.
I have an article on my web site that discusses possible options. See
http://www.sommarskog.se/share_data.html.
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

Passing SELECTed rows to a calling SP

Hi!
I'm a new T-SQL developer and just hit a roadblock.
I have a scenario that goes like this: I have 2 stored procedures,
spInner and spOuter. spInner has a SELECT statement which would
normally be used by a class using MS Enterprise Library and that output
goes into a DataSet. However, I need to get the output of the SELECT
statement to go into spOuter and that's what I can't seem to figure
out.
I know I may be asked to use functions that return tables in replies to
this post, but I can't do that as some parts of my application have an
EXEC(string) for dynamic SQL, instead of spInner.
Any help appreciated.
Cheers,
N.I.T.I.N.For the results of spInner to be available to spOuter, there are a few
options. I have used temporary tables or persisted the data into a real
table to avoid recompiles and also have a record of the data. If you use the
latter option, you can use a guid column and each row with a guid obtained
from spOuter. This way you can easily pick up the rows you need.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||NiTiN (emailme.nitin@.gmail.com) writes:
> I'm a new T-SQL developer and just hit a roadblock.
> I have a scenario that goes like this: I have 2 stored procedures,
> spInner and spOuter. spInner has a SELECT statement which would
> normally be used by a class using MS Enterprise Library and that output
> goes into a DataSet. However, I need to get the output of the SELECT
> statement to go into spOuter and that's what I can't seem to figure
> out.
> I know I may be asked to use functions that return tables in replies to
> this post, but I can't do that as some parts of my application have an
> EXEC(string) for dynamic SQL, instead of spInner.
I have an article on my web site that discusses possible options. See
http://www.sommarskog.se/share_data.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Passing SELECTed rows to a calling SP

Hi!

I'm a new T-SQL developer and just hit a roadblock.

I have a scenario that goes like this: I have 2 stored procedures,
spInner and spOuter. spInner has a SELECT statement which would
normally be used by a class using MS Enterprise Library and that output
goes into a DataSet. However, I need to get the output of the SELECT
statement to go into spOuter and that's what I can't seem to figure
out.

I know I may be asked to use functions that return tables in replies to
this post, but I can't do that as some parts of my application have an
EXEC(string) for dynamic SQL, instead of spInner.

Any help appreciated.

Cheers,
N.I.T.I.N.For the results of spInner to be available to spOuter, there are a few
options. I have used temporary tables or persisted the data into a real
table to avoid recompiles and also have a record of the data. If you use the
latter option, you can use a guid column and each row with a guid obtained
from spOuter. This way you can easily pick up the rows you need.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||NiTiN (emailme.nitin@.gmail.com) writes:

Quote:

Originally Posted by

I'm a new T-SQL developer and just hit a roadblock.
>
I have a scenario that goes like this: I have 2 stored procedures,
spInner and spOuter. spInner has a SELECT statement which would
normally be used by a class using MS Enterprise Library and that output
goes into a DataSet. However, I need to get the output of the SELECT
statement to go into spOuter and that's what I can't seem to figure
out.
>
I know I may be asked to use functions that return tables in replies to
this post, but I can't do that as some parts of my application have an
EXEC(string) for dynamic SQL, instead of spInner.

I have an article on my web site that discusses possible options. See
http://www.sommarskog.se/share_data.html.

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