Nested Transactions

Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer transaction's statements executing?Use SAVE TRAN in you "inner transaction" instead of BEGIN TRAN. That gives y
ou a savepoint to roll
back to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:uIP6f6FSGHA.4976@.TK2MSFTNGP11.phx.gbl...
Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer
transaction's statements executing?|||You can't. From SQL BOL:
"Naming multiple transactions in a series of nested transactions with a
transaction name has little effect on the transaction. Only the first
(outermost) transaction name is registered with the system. A rollback
to any other name (other than a valid savepoint name) generates an
error. None of the statements executed before the rollback are in fact
rolled back at the time this error occurs. The statements are rolled
back only when the outer transaction is rolled back."|||Hi rmg66
Rollback can only be used to roll back to the outermost begin tran, or to a
savepoint. Note that there really is not concept of nesting transactions in
SQL Server. Please read about transaction control in the Books Online.
begin tran One -- the label here is useless
insert into XX (data) values ('Tran1')
SAVE tran Two -- this does not start a new transaction, it only marks a spot
in the log that we can roll back to
insert into XX (data) values ('Tran2')
rollback tran Two -- roll back to named savepoint
insert into XX (data) values ('Tran1')
Commit tran One -- the label here is useless
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:uIP6f6FSGHA.497
6@.TK2MSFTNGP11.phx.gbl...
Hi All!
I am looking to rollback a nested transaction.
Is this possible? I thought it was, but I keep getting errors.
For example, when I run the following code ..... I get the following result
---
create table XX (data varchar(20))
begin tran One
insert into XX (data) values ('Tran1')
begin tran Two
insert into XX (data) values ('Tran2')
rollback tran Two
insert into XX (data) values ('Tran1')
Commit tran One
go
select * from XX
print @.@.trancount
RESULT ****************************************
********
Msg 6401, Level 16, State 1, Line 8
Cannot roll back Two. No transaction or savepoint of that name was found.
data
--
Tran1
Tran2
Tran1
The result set I want to get is this...
data
--
Tran1
Tran1
How do I roll back a transaction -- inside of another transaction -- and sti
ll keep all of the outer transaction's statements executing?

Nested Transaction!

The following example is given in BOL under the topic 'Nested
Transaction':
---
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
AS
BEGIN TRANSACTION InProc
INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
COMMIT TRANSACTION InProc
GO
/* Start a transaction and execute TransProc */
BEGIN TRANSACTION OutOfProc
GO
EXEC TransProc 1, 'aaa'
GO
/* Roll back the outer transaction,this will roll back TransProc's
nested transaction */
ROLLBACK TRANSACTION OutOfProc
GO
EXECUTE TransProc 3,'bbb'
GO
/* The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit of the inner
transaction from the first EXECUTE statement of TransProc was
overridden by the subsequent rollback. */
SELECT * FROM TestTrans
GO
---
The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
Apart from this, there is another transaction named 'OutOfProc' (which,
if I am not mistaken, isn't a part & parcel of the stored procedure
'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
where or which is the nested transaction? Shouldn't a nested
transaction have 1 transaction under another transaction something like
this (similar to nested If...Else conditions):
---
CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
AS
BEGIN TRANSACTION InProc
--Do Something
BEGIN TRANSACTION InProc1
--Do Something More
BEGIN TRANSACTION InProc2
--Do Something More
COMMIT TRANSACTION InProc
---
Or does the presence of more than 1 SQL statement (the 2 INSERT
queries) within the transaction 'InProc' (in the stored procedure
'TransProc') make it a nested transaction?
Thanks,
ArpanA begin tran must have either a commit/rollback tran. Thus, the TransProc's
Inproc transaction is participating in the OutProc transaction. If
commit/rollback tran OutProc is not explicitly called and the connection to
the server is dropped, the transaction will be forced to rollback.
As stated in bol, if outer most commit/rollback is what really important. It
decides the final commit all or rollback all.
-oj
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
> The following example is given in BOL under the topic 'Nested
> Transaction':
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
> INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
> COMMIT TRANSACTION InProc
> GO
> /* Start a transaction and execute TransProc */
> BEGIN TRANSACTION OutOfProc
> GO
> EXEC TransProc 1, 'aaa'
> GO
> /* Roll back the outer transaction,this will roll back TransProc's
> nested transaction */
> ROLLBACK TRANSACTION OutOfProc
> GO
> EXECUTE TransProc 3,'bbb'
> GO
> /* The following SELECT statement shows only rows 3 and 4 are
> still in the table. This indicates that the commit of the inner
> transaction from the first EXECUTE statement of TransProc was
> overridden by the subsequent rollback. */
> SELECT * FROM TestTrans
> GO
> ---
> The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
> Apart from this, there is another transaction named 'OutOfProc' (which,
> if I am not mistaken, isn't a part & parcel of the stored procedure
> 'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
> the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
> where or which is the nested transaction? Shouldn't a nested
> transaction have 1 transaction under another transaction something like
> this (similar to nested If...Else conditions):
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> --Do Something
> BEGIN TRANSACTION InProc1
> --Do Something More
> BEGIN TRANSACTION InProc2
> --Do Something More
> COMMIT TRANSACTION InProc
> ---
> Or does the presence of more than 1 SQL statement (the 2 INSERT
> queries) within the transaction 'InProc' (in the stored procedure
> 'TransProc') make it a nested transaction?
> Thanks,
> Arpan
>|||Thanks, OJ, for your help. How stupid of me....actually I had
misinterpreted the example given in BOL :-)
Thanks once again,
Regards,
Arpan|||Transactions are not really nested. There can only be one outstanding
transaction context for a connection. The global variable @.@.TRANCOUNT
reports whether or not a transaction context is outstanding, and its value
immediately before a Transact-SQL statement begins executing determines
whether or not a new transaction context is initiated. When @.@.TRANCOUNT =
0, no transaction context is outstanding for the connection. Each time a
BEGIN TRANSACTION is executed, @.@.TRANCOUNT is incremented. Only when
@.@.TRANCOUNT = 0 immediately before a BEGIN TRANSACTION statement is a new
transaction started by that BEGIN TRANSACTION statement. Each sucessive
BEGIN TRANSACTION increments @.@.TRANCOUNT. Each COMMIT TRANSACTION
decrements @.@.TRANCOUNT. Only when @.@.TRANCOUNT = 1 immediately before a
COMMIT TRANSACTION statement are changes made within the transaction made
permanent by that COMMIT TRANSACTION statement. All Transact-SQL statements
that alter the state or schema of the database operate within the context of
a transaction. If a transaction context is not already outstanding, then a
new transaction context is initiated before executing the statement. If
IMPLICIT_TRANSACTIONS is OFF, then changes made by the statement are
committed immediately after the statement completes. This is called
"autocommit" mode. If IMPLICIT_TRANSACTIONS is ON, then an explicit COMMIT
WORK must be issued to commit the transaction. A transaction can span
multiple statements, multiple stored procedure calls, even multiple batches.
If a transaction is outstanding, dynamic SQL executed via either EXEC() or
sp_executesql executes within that transaction context. Since changes made
while a transaction context is outstanding are not made permanent until
they're committed, ROLLBACK backs all of the changes for the entire
transaction context. The only exception is when a savepoint is specified on
a ROLLBACK statement. SAVE TRANSACTION places a marker in the transaction
log that identifies a reference point which can be specified in a ROLLBACK
statement to partially backout changes made while a transaction context is
outstanding. When a ROLLBACK savepoint statement is executed, all changes
made after the save point are backed out, and the transaction context
remains outstanding.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
> The following example is given in BOL under the topic 'Nested
> Transaction':
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> INSERT INTO TestTrans VALUES (@.PriKey, @.CharCol)
> INSERT INTO TestTrans VALUES (@.PriKey + 1, @.CharCol)
> COMMIT TRANSACTION InProc
> GO
> /* Start a transaction and execute TransProc */
> BEGIN TRANSACTION OutOfProc
> GO
> EXEC TransProc 1, 'aaa'
> GO
> /* Roll back the outer transaction,this will roll back TransProc's
> nested transaction */
> ROLLBACK TRANSACTION OutOfProc
> GO
> EXECUTE TransProc 3,'bbb'
> GO
> /* The following SELECT statement shows only rows 3 and 4 are
> still in the table. This indicates that the commit of the inner
> transaction from the first EXECUTE statement of TransProc was
> overridden by the subsequent rollback. */
> SELECT * FROM TestTrans
> GO
> ---
> The stored procedure 'TransProc' has only 1 transaction named 'InProc'.
> Apart from this, there is another transaction named 'OutOfProc' (which,
> if I am not mistaken, isn't a part & parcel of the stored procedure
> 'TransProc' since 'TransProc' ends at the first 'GO' statement) i.e.
> the transactions 'InProc' & 'OutOfProc' are 2 distinct transactions. So
> where or which is the nested transaction? Shouldn't a nested
> transaction have 1 transaction under another transaction something like
> this (similar to nested If...Else conditions):
> ---
> CREATE PROCEDURE TransProc @.PriKey INT, @.CharCol CHAR(3)
> AS
> BEGIN TRANSACTION InProc
> --Do Something
> BEGIN TRANSACTION InProc1
> --Do Something More
> BEGIN TRANSACTION InProc2
> --Do Something More
> COMMIT TRANSACTION InProc
> ---
> Or does the presence of more than 1 SQL statement (the 2 INSERT
> queries) within the transaction 'InProc' (in the stored procedure
> 'TransProc') make it a nested transaction?
> Thanks,
> Arpan
>|||Dude, paragraph breaks :)

> Transactions are not really nested. There can only be one outstanding
> transaction context for a connection.
I don't know that this particularly means that transactions aren't nested.
By your definition IF..THEN statements are not really nested. I don't think
that nested transactions implies any technical innerworkings more than it
just simply implies that you can syntactically do:
BEGIN TRANSACTION
BEGIN TRANSACTION
BEGIN TRANSACTION
COMMIT TRANSACTION
COMMIT TRANSACTION
COMMIT TRANSACTION
It is more or less meaningless to us whether a stack is used or a counter
and whether or not ROLLBACK kills the whole stack or just goes back to the
original point. Either way I still consider them nested because of syntax.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Transactions are not really nested. There can only be one outstanding
> transaction context for a connection. The global variable @.@.TRANCOUNT
> reports whether or not a transaction context is outstanding, and its value
> immediately before a Transact-SQL statement begins executing determines
> whether or not a new transaction context is initiated. When @.@.TRANCOUNT =
> 0, no transaction context is outstanding for the connection. Each time a
> BEGIN TRANSACTION is executed, @.@.TRANCOUNT is incremented. Only when
> @.@.TRANCOUNT = 0 immediately before a BEGIN TRANSACTION statement is a new
> transaction started by that BEGIN TRANSACTION statement. Each sucessive
> BEGIN TRANSACTION increments @.@.TRANCOUNT. Each COMMIT TRANSACTION
> decrements @.@.TRANCOUNT. Only when @.@.TRANCOUNT = 1 immediately before a
> COMMIT TRANSACTION statement are changes made within the transaction made
> permanent by that COMMIT TRANSACTION statement. All Transact-SQL
> statements
> that alter the state or schema of the database operate within the context
> of
> a transaction. If a transaction context is not already outstanding, then
> a
> new transaction context is initiated before executing the statement. If
> IMPLICIT_TRANSACTIONS is OFF, then changes made by the statement are
> committed immediately after the statement completes. This is called
> "autocommit" mode. If IMPLICIT_TRANSACTIONS is ON, then an explicit
> COMMIT
> WORK must be issued to commit the transaction. A transaction can span
> multiple statements, multiple stored procedure calls, even multiple
> batches.
> If a transaction is outstanding, dynamic SQL executed via either EXEC() or
> sp_executesql executes within that transaction context. Since changes
> made
> while a transaction context is outstanding are not made permanent until
> they're committed, ROLLBACK backs all of the changes for the entire
> transaction context. The only exception is when a savepoint is specified
> on
> a ROLLBACK statement. SAVE TRANSACTION places a marker in the transaction
> log that identifies a reference point which can be specified in a ROLLBACK
> statement to partially backout changes made while a transaction context is
> outstanding. When a ROLLBACK savepoint statement is executed, all changes
> made after the save point are backed out, and the transaction context
> remains outstanding.
>
> "Arpan" <arpan_de@.hotmail.com> wrote in message
> news:1123306747.855496.16630@.f14g2000cwb.googlegroups.com...
>|||There is a difference. For example:
IF cond1
THEN IF cond2
THEN IF cond3
THEN stmt1
ELSE stmt2
Which condition does this ELSE go with? Here's another example:
for (int i = 1; i < 10; i++)
for (int j = 1; j < 10; j++)
for (int k = 1; k < 10; k++)
{
Console.WriteLine(i + j + k);
if (i + j + k == 55) break;
}
Which for does the break go with?
One of the common misunderstandings about transactions is that since there m
ust always be a matching commit transaction for every begin transaction, and
that transactions can be "nested," many newbies erroneously assume that a r
ollback only backs out the innermost block of statements--that is, to the po
int of the innermost BEGIN TRANSACTION. The assumption follows from the pat
tern etched in their brain by the second example. A break exits the innermo
st block; therefore, it is logical to assume (erroneously, of course) that a
rollback backs out the innermost transaction.
The important thing to remember is that a transaction context either exists
or it doesn't. It doesn't matter how many begin/commit pairs exist in the b
lock of code bounded by the outermost begin/commit pair, the transaction con
text is initiated by the first BEGIN TRANSACTION and is terminated either by
any ROLLBACK or by the outermost COMMIT.
Notice the pretty paragraph breaks in the above :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message news:#7aRUwomFHA.2484@.TK2
MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
>
> I don't know that this particularly means that transactions aren't nested.
> By your definition IF..THEN statements are not really nested. I don't thi
nk
> that nested transactions implies any technical innerworkings more than it
> just simply implies that you can syntactically do:
>
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
>
> It is more or less meaningless to us whether a stack is used or a counter
> and whether or not ROLLBACK kills the whole stack or just goes back to the
> original point. Either way I still consider them nested because of syntax
.
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>
>|||I like to think of it as:
"Nested transactions are allowed in syntax but not semantics."
I don't know what "real" nested transactions mean, or if there is a formal d
efinition of what nested
transaction semantics means? :
Perhaps a rollback of an inner transaction would allow commit of an outer tr
ansaction? We can do
that with savepoints.
Or the other way: A commit of an inner transaction will still be committed i
f the outer transaction
does a rollback? True, we don't have this in SQL server (which, I believe, B
rian wished for in an
earlier post in some other thread). To some extent, we can work around it wi
th table variables or
opening a new connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%237aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
> I don't know that this particularly means that transactions aren't nested.
By your definition
> IF..THEN statements are not really nested. I don't think that nested tran
sactions implies any
> technical innerworkings more than it just simply implies that you can synt
actically do:
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> It is more or less meaningless to us whether a stack is used or a counter
and whether or not
> ROLLBACK kills the whole stack or just goes back to the original point. E
ither way I still
> consider them nested because of syntax.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>|||But the one break can get us out of several scope operators. I see your
point, but as long as you have to commit them one at a time, I think the
common term nested for transactions is probably going to stick...
And you code still looks pretty good in plain text :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:u9My2XpmFHA.4028@.TK2MSFTNGP10.phx.gbl...
There is a difference. For example:
IF cond1
THEN IF cond2
THEN IF cond3
THEN stmt1
ELSE stmt2
Which condition does this ELSE go with? Here's another example:
for (int i = 1; i < 10; i++)
for (int j = 1; j < 10; j++)
for (int k = 1; k < 10; k++)
{
Console.WriteLine(i + j + k);
if (i + j + k == 55) break;
}
Which for does the break go with?
One of the common misunderstandings about transactions is that since there
must always be a matching commit transaction for every begin transaction,
and that transactions can be "nested," many newbies erroneously assume that
a rollback only backs out the innermost block of statements--that is, to the
point of the innermost BEGIN TRANSACTION. The assumption follows from the
pattern etched in their brain by the second example. A break exits the
innermost block; therefore, it is logical to assume (erroneously, of course)
that a rollback backs out the innermost transaction.
The important thing to remember is that a transaction context either exists
or it doesn't. It doesn't matter how many begin/commit pairs exist in the
block of code bounded by the outermost begin/commit pair, the transaction
context is initiated by the first BEGIN TRANSACTION and is terminated either
by any ROLLBACK or by the outermost COMMIT.
Notice the pretty paragraph breaks in the above :)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:#7aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Dude, paragraph breaks :)
>
> I don't know that this particularly means that transactions aren't nested.
> By your definition IF..THEN statements are not really nested. I don't
> think
> that nested transactions implies any technical innerworkings more than it
> just simply implies that you can syntactically do:
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> BEGIN TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> COMMIT TRANSACTION
> It is more or less meaningless to us whether a stack is used or a counter
> and whether or not ROLLBACK kills the whole stack or just goes back to the
> original point. Either way I still consider them nested because of
> syntax.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:OdCa%23klmFHA.1088@.TK2MSFTNGP14.phx.gbl...
>|||No doubt we could use a more convienient model for how transactions work,
but your statement is great.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uDRWzWqmFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I like to think of it as:
> "Nested transactions are allowed in syntax but not semantics."
>
> I don't know what "real" nested transactions mean, or if there is a formal
> definition of what nested transaction semantics means? :
> Perhaps a rollback of an inner transaction would allow commit of an outer
> transaction? We can do that with savepoints.
> Or the other way: A commit of an inner transaction will still be committed
> if the outer transaction does a rollback? True, we don't have this in SQL
> server (which, I believe, Brian wished for in an earlier post in some
> other thread). To some extent, we can work around it with table variables
> or opening a new connection.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:%237aRUwomFHA.2484@.TK2MSFTNGP15.phx.gbl...
>

Nested stored procedure

I have a report, say for example "Dummy report" which has its dataset to be a
stored procedure SP1. This stored procedure SP1 gets its data from another
stored procedure (SP2) that is nested in it.
When I try to generate "Dummy report" from report designer for the first
time, it fails. What I need to do now is run SP2 individually from SSMS, and
then run the "Dummy report" from designer to see the report output. Every
time I make a change to SP2, I have to do the same thing - run SP2
individually and then generate the report. I am not able to understand why
this is happening. Can anyone help me with this problem? Thanks in advance.
SQL Server 2005 SP1/Windows Server 2003On Mar 9, 12:23 pm, KMP <K...@.discussions.microsoft.com> wrote:
> I have a report, say for example "Dummy report" which has its dataset to be a
> stored procedure SP1. This stored procedure SP1 gets its data from another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS, and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in advance.
> SQL Server 2005 SP1/Windows Server 2003
If I'm understanding you correctly, it has something to do w/why a
stored procedure cannot be initially used when creating a report.
Basically, the report needs to know the dataset format and what data
to expect, etc up front. Hopefully in a future version of SSRS, this
will be corrected.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Have you checked, that your sp1 is working from SSMS ? Just check whether
you have given hints in the query or recompile etc... and check whether it
returns single set of data.
Amarnath, MCTS
"KMP" wrote:
> I have a report, say for example "Dummy report" which has its dataset to be a
> stored procedure SP1. This stored procedure SP1 gets its data from another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS, and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in advance.
> SQL Server 2005 SP1/Windows Server 2003|||If your first stored procedure is creating a temp table that is then filled
by the second stored procedure then you need to add a line to your stored
procedure.
SET FMTONLY OFF
The issue is that when RS is trying to create the field list it calls the
stored procedure with the set fmtonly on (i.e. it doesn't really execute
it). This means it does not create the temp table either (although I have
found I only have this issue with nested stored procedures).
Anyway, add the statement at the top of you calling stored procedure.
The only other issue, sometimes you have to click on the refresh fields
button, it is one of the buttons to the right of the ...
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:21160AC2-8078-4CA8-A70D-494ACB4F3168@.microsoft.com...
>I have a report, say for example "Dummy report" which has its dataset to be
>a
> stored procedure SP1. This stored procedure SP1 gets its data from
> another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS,
> and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in
> advance.
> SQL Server 2005 SP1/Windows Server 2003|||SP1 does not run from SMSS. I tried WITH RECOMPILE option form both stored
procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
the query. Please advise.
By the way SET FMTONLY OFF option did not help either. Thanks for all the
ideas. Still need to find a solution though...
"Amarnath" wrote:
> Have you checked, that your sp1 is working from SSMS ? Just check whether
> you have given hints in the query or recompile etc... and check whether it
> returns single set of data.
> Amarnath, MCTS
>
> "KMP" wrote:
> > I have a report, say for example "Dummy report" which has its dataset to be a
> > stored procedure SP1. This stored procedure SP1 gets its data from another
> > stored procedure (SP2) that is nested in it.
> >
> > When I try to generate "Dummy report" from report designer for the first
> > time, it fails. What I need to do now is run SP2 individually from SSMS, and
> > then run the "Dummy report" from designer to see the report output. Every
> > time I make a change to SP2, I have to do the same thing - run SP2
> > individually and then generate the report. I am not able to understand why
> > this is happening. Can anyone help me with this problem? Thanks in advance.
> >
> > SQL Server 2005 SP1/Windows Server 2003|||Please help!!!
"KMP" wrote:
> SP1 does not run from SMSS. I tried WITH RECOMPILE option form both stored
> procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
> the query. Please advise.
> By the way SET FMTONLY OFF option did not help either. Thanks for all the
> ideas. Still need to find a solution though...
> "Amarnath" wrote:
> > Have you checked, that your sp1 is working from SSMS ? Just check whether
> > you have given hints in the query or recompile etc... and check whether it
> > returns single set of data.
> >
> > Amarnath, MCTS
> >
> >
> > "KMP" wrote:
> >
> > > I have a report, say for example "Dummy report" which has its dataset to be a
> > > stored procedure SP1. This stored procedure SP1 gets its data from another
> > > stored procedure (SP2) that is nested in it.
> > >
> > > When I try to generate "Dummy report" from report designer for the first
> > > time, it fails. What I need to do now is run SP2 individually from SSMS, and
> > > then run the "Dummy report" from designer to see the report output. Every
> > > time I make a change to SP2, I have to do the same thing - run SP2
> > > individually and then generate the report. I am not able to understand why
> > > this is happening. Can anyone help me with this problem? Thanks in advance.
> > >
> > > SQL Server 2005 SP1/Windows Server 2003|||Any help will be greatly appreciated. Thank you!
"KMP" wrote:
> I have a report, say for example "Dummy report" which has its dataset to be a
> stored procedure SP1. This stored procedure SP1 gets its data from another
> stored procedure (SP2) that is nested in it.
> When I try to generate "Dummy report" from report designer for the first
> time, it fails. What I need to do now is run SP2 individually from SSMS, and
> then run the "Dummy report" from designer to see the report output. Every
> time I make a change to SP2, I have to do the same thing - run SP2
> individually and then generate the report. I am not able to understand why
> this is happening. Can anyone help me with this problem? Thanks in advance.
> SQL Server 2005 SP1/Windows Server 2003|||I thought I had answered this one.
Anyway, is the issue that fields are not showing up in RS?
If SP1 calls SP2 then when you run SP1 from RS both should execute. If the
issue is that you are not seeing the new fields then try the below:
Temp tables should work for you. I use them a whole lot. Do the following:
1. Click on the refresh fields button (to the right of the ...)
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP): "The issue with RS is that the rowset of the SP is
defined by calling the SP with SET FMTONLY ON because Temp tables don't get
created if you select from the temp table the metadata from the rowset can't
be returned. This can be worked around by turning FMTONLY OFF in the SP."
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:21B1B8BA-C647-48AE-9A7C-5B1C20080A25@.microsoft.com...
> Any help will be greatly appreciated. Thank you!
> "KMP" wrote:
>> I have a report, say for example "Dummy report" which has its dataset to
>> be a
>> stored procedure SP1. This stored procedure SP1 gets its data from
>> another
>> stored procedure (SP2) that is nested in it.
>> When I try to generate "Dummy report" from report designer for the first
>> time, it fails. What I need to do now is run SP2 individually from SSMS,
>> and
>> then run the "Dummy report" from designer to see the report output. Every
>> time I make a change to SP2, I have to do the same thing - run SP2
>> individually and then generate the report. I am not able to understand
>> why
>> this is happening. Can anyone help me with this problem? Thanks in
>> advance.
>> SQL Server 2005 SP1/Windows Server 2003|||Sorry if I am posting this over and over again. Seems to be more a SQL Server
issue, not RS. But below is my previous post and what happened when I tried
the different ideas:
"SP1 does not run from SMSS. I tried WITH RECOMPILE option for both stored
procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
the query. Please advise.
By the way SET FMTONLY OFF option did not help either."
"Bruce L-C [MVP]" wrote:
> I thought I had answered this one.
> Anyway, is the issue that fields are not showing up in RS?
> If SP1 calls SP2 then when you run SP1 from RS both should execute. If the
> issue is that you are not seeing the new fields then try the below:
> Temp tables should work for you. I use them a whole lot. Do the following:
> 1. Click on the refresh fields button (to the right of the ...)
> 2. Do not use set nocount on
> 3. Do not explicitly drop the temp tables
> 4. Have your last statement be a select
> If none of these work then add Set FMTONLY Off (the below is from Simon
> Sabin a SQL Server MVP): "The issue with RS is that the rowset of the SP is
> defined by calling the SP with SET FMTONLY ON because Temp tables don't get
> created if you select from the temp table the metadata from the rowset can't
> be returned. This can be worked around by turning FMTONLY OFF in the SP."
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:21B1B8BA-C647-48AE-9A7C-5B1C20080A25@.microsoft.com...
> > Any help will be greatly appreciated. Thank you!
> >
> > "KMP" wrote:
> >
> >> I have a report, say for example "Dummy report" which has its dataset to
> >> be a
> >> stored procedure SP1. This stored procedure SP1 gets its data from
> >> another
> >> stored procedure (SP2) that is nested in it.
> >>
> >> When I try to generate "Dummy report" from report designer for the first
> >> time, it fails. What I need to do now is run SP2 individually from SSMS,
> >> and
> >> then run the "Dummy report" from designer to see the report output. Every
> >> time I make a change to SP2, I have to do the same thing - run SP2
> >> individually and then generate the report. I am not able to understand
> >> why
> >> this is happening. Can anyone help me with this problem? Thanks in
> >> advance.
> >>
> >> SQL Server 2005 SP1/Windows Server 2003
>
>|||Ahh, this is not a Reporting Services issue. If you cannot run this from
outside of Reporting Services then all the advice I gave does not help you.
I suggest posting on the SQL Server newsgroups. They will be able to help
you,. First make sure everything works from outside of RS before trying to
create a report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:07013913-3721-4A9C-9B54-C6B2259ED23D@.microsoft.com...
> Sorry if I am posting this over and over again. Seems to be more a SQL
> Server
> issue, not RS. But below is my previous post and what happened when I
> tried
> the different ideas:
> "SP1 does not run from SMSS. I tried WITH RECOMPILE option for both stored
> procedures (SP1 and SP2) still no luck. I am not sure how to give hints in
> the query. Please advise.
> By the way SET FMTONLY OFF option did not help either."
>
> "Bruce L-C [MVP]" wrote:
>> I thought I had answered this one.
>> Anyway, is the issue that fields are not showing up in RS?
>> If SP1 calls SP2 then when you run SP1 from RS both should execute. If
>> the
>> issue is that you are not seeing the new fields then try the below:
>> Temp tables should work for you. I use them a whole lot. Do the
>> following:
>> 1. Click on the refresh fields button (to the right of the ...)
>> 2. Do not use set nocount on
>> 3. Do not explicitly drop the temp tables
>> 4. Have your last statement be a select
>> If none of these work then add Set FMTONLY Off (the below is from Simon
>> Sabin a SQL Server MVP): "The issue with RS is that the rowset of the SP
>> is
>> defined by calling the SP with SET FMTONLY ON because Temp tables don't
>> get
>> created if you select from the temp table the metadata from the rowset
>> can't
>> be returned. This can be worked around by turning FMTONLY OFF in the SP."
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:21B1B8BA-C647-48AE-9A7C-5B1C20080A25@.microsoft.com...
>> > Any help will be greatly appreciated. Thank you!
>> >
>> > "KMP" wrote:
>> >
>> >> I have a report, say for example "Dummy report" which has its dataset
>> >> to
>> >> be a
>> >> stored procedure SP1. This stored procedure SP1 gets its data from
>> >> another
>> >> stored procedure (SP2) that is nested in it.
>> >>
>> >> When I try to generate "Dummy report" from report designer for the
>> >> first
>> >> time, it fails. What I need to do now is run SP2 individually from
>> >> SSMS,
>> >> and
>> >> then run the "Dummy report" from designer to see the report output.
>> >> Every
>> >> time I make a change to SP2, I have to do the same thing - run SP2
>> >> individually and then generate the report. I am not able to understand
>> >> why
>> >> this is happening. Can anyone help me with this problem? Thanks in
>> >> advance.
>> >>
>> >> SQL Server 2005 SP1/Windows Server 2003
>>

Nested loop SQL

Hi
I'm so new in SQL. I have SQL statement that nested loop.
That I don't like.
for example.
I have one table that keep Product Master
----------------
Table T_PRD_MS
----------------
COLUMN I_PRD_TYPE : primary key
I_PRD_ID : primary key
I_MANUF_DATE
I_EXPIRE_DATE
I_VENDOR_ID
I_BRAND_ID

And Other tables that keep Information about this product to readable format eg. T_PRD_TYPE -- > to get product type name
T_PRD_ID -- > to gett product name and detail

When I select data from table T_PRD_MS that in my criteria eg. Product that ID = '111'
I must to copy data to temp table for transaction up date. then I must to work in two steps.
1. select data from T_PRD_MS in criteria to TEMP table
2. use primary key in each row in TEMP table to get data record by record.

EG. sql = select * from T_PRD_MS where I_EXPIRE_DATE
= '20060210'
set SQL as recordset A
If recordset A not eof then
select data from T_PRD_ID where I_PRD_ID = recordset A.fields(0)
and I_PRD_TYPE = recordset A.fields(1)

How can I reduce my job for increase performance?Why not perform a SQL join?

select m.*, i.data
from T_PRD_MS m, T_PRD_ID i
where m.I_EXPIRE_DATE = '20060210'
and m.I_PRD_TYPE = i.I_PRD_TYPE;
and m.I_PRD_ID = i.I_PRD_ID;

nested joins - joining one table to another multiple times

Hi,

I'm having problems constructing a nested join. It's quite complex, so
here's a simplfied example of the problem. Any thoughts on what I'm
doig wrong - or if I've got the whole approach wrong are welcome.

I've two tables :-

one is a contact table contacting name, addresses etc. Three of the
fields represent users - 'created by', 'last modified by' and 'owner'.
They contain usernames - eg. JDOE, BSMITH etc.

The other table contants usernames and new ID codes.

What I want to do is create a new dataset by joining the contacts table
with the user table on all three fields - so the new dataset contains
the ids for the creator, last modifier and owner.

I've tried things similar to:

select c.*, u1.id, u2,id, u3.id
from contact c
left outer join users u1
left outer join users u2
left outer join users u3
on (u3.username = c.owner)
on (u2.username = c.modified)
on (u1.username = c.creator )

But it compains that
"The column prefix 'c' does not match with a table name or alias name
used in the query."

The problem is referencing c (contact) through the whole set of joins.

I would like to do this in some similar format as the query is within a
cursor and post-processing would be very long-winded.

ThanksHi Steve,

this sql-code will work:

select c.*, u1.id, u2.id, u3.id
from contact c
left outer join users u1 ON u1.username = c.creator
left outer join users u2 ON u2.username = c.modified
left outer join users u3 ON u3.username = c.owner

Steve C schrieb:
...
> select c.*, u1.id, u2,id, u3.id
> from contact c
> left outer join users u1
> left outer join users u2
> left outer join users u3
> on (u3.username = c.owner)
> on (u2.username = c.modified)
> on (u1.username = c.creator )
> But it compains that
> "The column prefix 'c' does not match with a table name or alias name
> used in the query."

www.zankl-it.de

Nested Filters

Is it possible to create a complex, nested filter on a table in a report?
For example:
(Statement A AND Statement B) OR (Statement C AND Statement D)Hello Cindy,
Based on my experience, you could done this in another approach:
In the Filter of a table in reporting services, you type the nested filter
in the expression like this:
IIF( (Statement A AND Statement B) OR (Statement C AND Statement D) ,0,1)
Then, if the whole statement is true, the expression will return 0, and if
false, return 1.
Then, you could add "=0" (without quote) in the value column of the filter.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Nested distributed transaction

Hello everybody-

I am trying to understand how to make distributed transactions in MS SQL 2005.

For example, I got two databases A and B and a client, connected to one of them - A. From that client I want to initiate transaction to B, using connection to A.

This functionality is available in Oracle using database links. With all new changes, does it exist in MS SQL 2005?

Thanks,

Alex.

For stating distributed transactions you could (for example):

1. Use statement BEGIN DISTRIBUTED TRANSACTION T-SQL statement

2. If you use .NET you could use TransactionScope class

|||

This is good. Then, in the BEGIN DISTRIBUTED TRAN... I need to explicitely say the Remote Server name to identify the target table. Do I register it with DTS or just as on the client alias?

Sorry for that questions - I am the Oracle specialist and trying to apply the same framework...

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks,

Alex.

|||

For call OtherServer.OtherDB.OtherSchema.OtherTable you need to add linked server for you SQL Server.

You could to do it at SQL Server Management Studio or by stored procedure sp_addlinkedserver

|||

Okay. But what about this part, this is quite important for my application:

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks again,

Alex.

|||It depended from type of your replication. If you use transactional replication, you could try to configure replication filters for ignoring, but it isn't simple task. In common case, for replication distributed and local changes don't have any difference.|||

I suppose I am planning to use peer-to-peer replication - where an object/record can be updated at any site. There I have a situation, where I want to update local record, which will be replicated to all other sites and - in the same transaction - the same record at a specific remote site - and this one I do not want to be replicated.

As I said, I can easily do it in Oracle for Multi-Master replication, but don't see it in the MSSQL2005.

Alex.

Nested distributed transaction

Hello everybody-

I am trying to understand how to make distributed transactions in MS SQL 2005.

For example, I got two databases A and B and a client, connected to one of them - A. From that client I want to initiate transaction to B, using connection to A.

This functionality is available in Oracle using database links. With all new changes, does it exist in MS SQL 2005?

Thanks,

Alex.

For stating distributed transactions you could (for example):

1. Use statement BEGIN DISTRIBUTED TRANSACTION T-SQL statement

2. If you use .NET you could use TransactionScope class

|||

This is good. Then, in the BEGIN DISTRIBUTED TRAN... I need to explicitely say the Remote Server name to identify the target table. Do I register it with DTS or just as on the client alias?

Sorry for that questions - I am the Oracle specialist and trying to apply the same framework...

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks,

Alex.

|||

For call OtherServer.OtherDB.OtherSchema.OtherTable you need to add linked server for you SQL Server.

You could to do it at SQL Server Management Studio or by stored procedure sp_addlinkedserver

|||

Okay. But what about this part, this is quite important for my application:

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks again,

Alex.

|||It depended from type of your replication. If you use transactional replication, you could try to configure replication filters for ignoring, but it isn't simple task. In common case, for replication distributed and local changes don't have any difference.|||

I suppose I am planning to use peer-to-peer replication - where an object/record can be updated at any site. There I have a situation, where I want to update local record, which will be replicated to all other sites and - in the same transaction - the same record at a specific remote site - and this one I do not want to be replicated.

As I said, I can easily do it in Oracle for Multi-Master replication, but don't see it in the MSSQL2005.

Alex.

Needing something and getting something else....

Hi,

Kindly have a look at my example and do the help.

I have a database (MS Access) in which there are 10 Customers.

We have 2 products:

1) Leather Suitcase 2) Plastic Suitcase

2 Customers took Leather Suitcase & Plastic Suitcase ...while
4 Customers took only Leather Suitcase ... and
4 Customers took only Plastic Suitcase

The problem:

When I try to create a report using Crystal Reports XI to get the report to know who purchased only Leather Suitcase, I am getting all the data for 10 Customers showing all the 10 records ...?

How to solve this problem. I need only 6 rows to be shown because only 6 customers purchased the Leather Suitcase.

Please do help.

Sweetie.Use the Select Expert limit your record selection to only the customers that purchased leather

product is equal to Leather

Need VB/VB.NET Help

Hai Folks

I Want a coding for Read the SQl Server Log Files

Now I am Using SQL Server 2000

For Example:
Test is a database Name...
Test_Data.MDF is a Data File Name
Test_Log.LDF is a Log File Name

Now I Create a Table Name Check with Single Field

Now I want to check this Details like I run this VB Coding then Its tell me The Last Transaction of the Test database is Create a Table Name is Check

I hope You can Understand My Request...

In exact Wors I want JobHistory of Some Table...
If its Possible VB.NET than Please give me a Sample Code
Please This is very Very Urgent................Hi,

"Read the SQl Server Log Files"
-Do you want to read the logfile or some table data ?

I really did not understand you explanation. Do you want to track the created tables ? There is no log for that unless you use SQL 2k5 and DDL trigger or read the log file with a third party log viewer.

(Creating a trigger on the system tables is not an option)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for reply...

I want to Read SQL Server Log file....

like SQL Profiler Trace Operation....

Please Reply me|||The SQL Server log file will not give you specific actions taken in the database. The transaction log is different, and it does have a record of every change made in the database, but your best bet there is to purchase a third party application that parses that log. (Something along the lines of Log P.I. is probably what you're looking for.)

Need urgent help to sort this out!

I've made this example and it loads a picture into a database. (MsSql )
Take a look at the code, it works just fine however it leaves a process in sleeping mode "avaiting command" in Enterprise manager under "Management/current Activity/Process Info"
Is it supposed to be like this or is it supposed to be reemoved after .net is finished??
Code snip
_______________________________________________________

Dim connAsNew SqlConnection("Data Source = (local);Initial Catalog = " & "test;User ID = NAME; Password=PASSWORD;")

Dim cmdAsNew SqlCommand("Select * from tab_bild", cnn)

Try

conn.Open()

Dim myDatareaderAs SqlDataReader

myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

DoWhile (myDatareader.Read())

Response.ContentType = myDatareader.Item("PersonImageType")

Response.BinaryWrite(myDatareader.Item("PersonImage"))

Loop

conn.Close()

Response.Write("Picture info succesfully retrieved")

Catch SQLexcAs SqlException

Response.Write("Read failed, Reason: " & SQLexc.ToString())

EndTry

EndSub
________________________________________________________________

Please can someone explain this for me or sort this out for me.
All help is welcome even if its only points me too a direction.

Regards
Tombola

You should move yourconn.Close() into a Finally block.
|||Thanks for the reply Morton!
I suspect that you mean something like this.
----------------

Try

conn.Open()

Dim myDatareaderAs SqlDataReader

myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

DoWhile (myDatareader.Read())

Response.ContentType = myDatareader.Item("PersonImageType")

Response.BinaryWrite(myDatareader.Item("PersonImage"))

Loop

Response.Write("Bild info succesfully retrieved")

Catch SQLexcAs SqlException

Response.Write("Read failed, Reason: " & SQLexc.ToString())

Finally

conn.Close()

EndTry

----------------
However it still leaves a sleeping process, but it will eventually time out, and be killed, I suppose.
I dont think this would bee such a good solution tough, what if the server is short off memory and there is a lot of sleeping processes that just waits to be timed out. The system would eventually freeze I think. Both IIS and MsSql harvests memory if I remember right.
Anyway the question would still be, Should the .net SqlClient leave a sleeping process on the server when the SqlClient is finished with all its doings?
Regards
Tombola|||I believe what you are seeing is a phenomenon of connection pooling. This is a good thing.
You should likely add a cmd.Dispose() after the conn.Close(). AndI would also add a conn.Dispose() after the conn.Close() for goodmeasure. It's a good idea to Dispose any object which implementthe IDisposable interface once you are done with it.

need ur help

hi all,
I was wondering how I can enforce some configurations to sql server
2000, like auditing failure logins for example, but without using the
enterprise manager.
I need to implement a tool that will set some configurations
automatically without human intervention, any idea how can i do
so''
thanx for time and helpQuick answer: You can do the configuration changes in Enterprise Manager
while being connected to the instance with SQL Profiler. That way you can
see what EM is doing on the DB and thereby you can reproduce that. But I'm
not sure if EM does everything by T-SQL or if it partly relies in DMO for
configuration. Question to the experts: In that case, would something show
up in the Profiler log?
Best regards
Nils Loeber
<Eng.Rana@.gmail.com> schrieb im Newsbeitrag
news:1154987176.155861.301430@.i42g2000cwa.googlegroups.com...
> hi all,
> I was wondering how I can enforce some configurations to sql server
> 2000, like auditing failure logins for example, but without using the
> enterprise manager.
> I need to implement a tool that will set some configurations
> automatically without human intervention, any idea how can i do
> so''
> thanx for time and help
>|||thanx for the gr8 help Nils.
i also found while searching the web, something called SQL-DMO, do u
think this may help or no
thanx again
[vbcol=seagreen]
>Nils Loeber wrote:
> Quick answer: You can do the configuration changes in Enterprise Manager
> while being connected to the instance with SQL Profiler. That way you can
> see what EM is doing on the DB and thereby you can reproduce that. But I'm
> not sure if EM does everything by T-SQL or if it partly relies in DMO for
> configuration. Question to the experts: In that case, would something show
> up in the Profiler log?
>
> Best regards
> Nils Loeber
>
> <Eng.Rana@.gmail.com> schrieb im Newsbeitrag
> news:1154987176.155861.301430@.i42g2000cwa.googlegroups.com...|||SQL-DMO is what I was referring to as DMO. Depending on what you want to do,
and with which programming language you want to do it, DMO might also be
well suited for the task.
Best regards
Nils Loeber
<Eng.Rana@.gmail.com> schrieb im Newsbeitrag
news:1155025512.608190.203300@.75g2000cwc.googlegroups.com...
> thanx for the gr8 help Nils.
> i also found while searching the web, something called SQL-DMO, do u
> think this may help or no
> thanx again
>
>

need ur help

hi all,
I was wondering how I can enforce some configurations to sql server
2000, like auditing failure logins for example, but without using the
enterprise manager.
I need to implement a tool that will set some configurations
automatically without human intervention, any idea how can i do
so''
thanx for time and helpQuick answer: You can do the configuration changes in Enterprise Manager
while being connected to the instance with SQL Profiler. That way you can
see what EM is doing on the DB and thereby you can reproduce that. But I'm
not sure if EM does everything by T-SQL or if it partly relies in DMO for
configuration. Question to the experts: In that case, would something show
up in the Profiler log?
Best regards
Nils Loeber
<Eng.Rana@.gmail.com> schrieb im Newsbeitrag
news:1154987176.155861.301430@.i42g2000cwa.googlegroups.com...
> hi all,
> I was wondering how I can enforce some configurations to sql server
> 2000, like auditing failure logins for example, but without using the
> enterprise manager.
> I need to implement a tool that will set some configurations
> automatically without human intervention, any idea how can i do
> so''
> thanx for time and help
>|||thanx for the gr8 help Nils.
i also found while searching the web, something called SQL-DMO, do u
think this may help or no
thanx again
>Nils Loeber wrote:
> Quick answer: You can do the configuration changes in Enterprise Manager
> while being connected to the instance with SQL Profiler. That way you can
> see what EM is doing on the DB and thereby you can reproduce that. But I'm
> not sure if EM does everything by T-SQL or if it partly relies in DMO for
> configuration. Question to the experts: In that case, would something show
> up in the Profiler log?
>
> Best regards
> Nils Loeber
>
> <Eng.Rana@.gmail.com> schrieb im Newsbeitrag
> news:1154987176.155861.301430@.i42g2000cwa.googlegroups.com...
> > hi all,
> >
> > I was wondering how I can enforce some configurations to sql server
> > 2000, like auditing failure logins for example, but without using the
> > enterprise manager.
> >
> > I need to implement a tool that will set some configurations
> > automatically without human intervention, any idea how can i do
> > so''
> >
> > thanx for time and help
> >|||SQL-DMO is what I was referring to as DMO. Depending on what you want to do,
and with which programming language you want to do it, DMO might also be
well suited for the task.
Best regards
Nils Loeber
<Eng.Rana@.gmail.com> schrieb im Newsbeitrag
news:1155025512.608190.203300@.75g2000cwc.googlegroups.com...
> thanx for the gr8 help Nils.
> i also found while searching the web, something called SQL-DMO, do u
> think this may help or no
> thanx again
>
>>Nils Loeber wrote:
>> Quick answer: You can do the configuration changes in Enterprise Manager
>> while being connected to the instance with SQL Profiler. That way you can
>> see what EM is doing on the DB and thereby you can reproduce that. But
>> I'm
>> not sure if EM does everything by T-SQL or if it partly relies in DMO for
>> configuration. Question to the experts: In that case, would something
>> show
>> up in the Profiler log?
>>
>> Best regards
>> Nils Loeber
>>
>> <Eng.Rana@.gmail.com> schrieb im Newsbeitrag
>> news:1154987176.155861.301430@.i42g2000cwa.googlegroups.com...
>> > hi all,
>> >
>> > I was wondering how I can enforce some configurations to sql server
>> > 2000, like auditing failure logins for example, but without using the
>> > enterprise manager.
>> >
>> > I need to implement a tool that will set some configurations
>> > automatically without human intervention, any idea how can i do
>> > so''
>> >
>> > thanx for time and help
>> >
>

Need Update Scripts

Hi All!
I need UPDATE scripts for my data. I know several tool's
which create INSERT Scripts ... but this do not help me.
For example i need scripts like that:
UPDATE [tbl] SET fld1 = 'abc' WHERE idxFld = 1
UPDATE [tbl] SET fld1 = 'def' WHERE idxFld = 2
UPDATE [tbl] SET fld1 = 'ghi' WHERE idxFld = 3
.
.
.
Any assistance or suggestions would be appreciated.
Thanks in Advance
SusanneHi John
thanks for you answer.
I need a software or script which created T-SQL Update
statements for different tables/fields (Must be dynamic !
different tables and fields, n records).
Examples (only for demo):
If you choose the master.dbo.sysusers table, the result
must be like that:
UPDATE sysusers SET name = 'xxx' WHERE uid = 1
UPDATE sysusers SET name = 'yyy' WHERE uid = 2
..
.. (for all data)
..
If you choose the master.dbo.sysobjects table, the result
must be like that:
UPDATE sysobjects SET name = 'abc' WHERE id = 1
..
.. (for all data)
..
I need this scripts to send them via eMail and update a
different server/database ! I can't use standard methods
like "UPDATE ... FROM...", DTS, linked servers or
whatever ... i need "simple Update" commands.
Susanne
>--Original Message--
>Hi
>Without knowing more detail it is hard to know or advise
you on what to do.
>If there are a finite number of idxFld values you could
create a lookup
>table and use the FROM clause in the update statement to
populate the new
>values. If this does not help please post DDL and
example data.
>John
>
>"Susanne" <spam@.hotmail.com> wrote in message
>news:04e001c35a5a$85a5add0$a101280a@.phx.gbl...
>> Hi All!
>> I need UPDATE scripts for my data. I know several
tool's
>> which create INSERT Scripts ... but this do not help
me.
>> For example i need scripts like that:
>> UPDATE [tbl] SET fld1 = 'abc' WHERE idxFld = 1
>> UPDATE [tbl] SET fld1 = 'def' WHERE idxFld = 2
>> UPDATE [tbl] SET fld1 = 'ghi' WHERE idxFld = 3
>> .
>> .
>> .
>> Any assistance or suggestions would be appreciated.
>> Thanks in Advance
>> Susanne
>>
>
>.
>|||Hi Susanne !
I think SQL Scripter is the right software for you.
http://www.sqlscripter.com
Regards
Michael
>--Original Message--
>Hi All!
>I need UPDATE scripts for my data. I know several tool's
>which create INSERT Scripts ... but this do not help me.
>For example i need scripts like that:
>UPDATE [tbl] SET fld1 = 'abc' WHERE idxFld = 1
>UPDATE [tbl] SET fld1 = 'def' WHERE idxFld = 2
>UPDATE [tbl] SET fld1 = 'ghi' WHERE idxFld = 3
>..
>..
>..
>Any assistance or suggestions would be appreciated.
>Thanks in Advance
>Susanne
>
>.
>|||Hi
I guess you could write this yourself using dynamic SQL. As you seem to have
to do this regularly, I have the feeling that what may be required is a
redesign of the database(s) as they are probably not normalised.
John
"Susanne" <spam@.hotmail.com> wrote in message
news:043e01c35a63$ccc48840$a601280a@.phx.gbl...
> Hi John
> thanks for you answer.
> I need a software or script which created T-SQL Update
> statements for different tables/fields (Must be dynamic !
> different tables and fields, n records).
> Examples (only for demo):
> If you choose the master.dbo.sysusers table, the result
> must be like that:
> UPDATE sysusers SET name = 'xxx' WHERE uid = 1
> UPDATE sysusers SET name = 'yyy' WHERE uid = 2
> ..
> .. (for all data)
> ..
> If you choose the master.dbo.sysobjects table, the result
> must be like that:
> UPDATE sysobjects SET name = 'abc' WHERE id = 1
> ..
> .. (for all data)
> ..
>
> I need this scripts to send them via eMail and update a
> different server/database ! I can't use standard methods
> like "UPDATE ... FROM...", DTS, linked servers or
> whatever ... i need "simple Update" commands.
> Susanne
>
>
> >--Original Message--
> >Hi
> >
> >Without knowing more detail it is hard to know or advise
> you on what to do.
> >
> >If there are a finite number of idxFld values you could
> create a lookup
> >table and use the FROM clause in the update statement to
> populate the new
> >values. If this does not help please post DDL and
> example data.
> >
> >John
> >
> >
> >"Susanne" <spam@.hotmail.com> wrote in message
> >news:04e001c35a5a$85a5add0$a101280a@.phx.gbl...
> >> Hi All!
> >> I need UPDATE scripts for my data. I know several
> tool's
> >> which create INSERT Scripts ... but this do not help
> me.
> >>
> >> For example i need scripts like that:
> >> UPDATE [tbl] SET fld1 = 'abc' WHERE idxFld = 1
> >> UPDATE [tbl] SET fld1 = 'def' WHERE idxFld = 2
> >> UPDATE [tbl] SET fld1 = 'ghi' WHERE idxFld = 3
> >> .
> >> .
> >> .
> >>
> >> Any assistance or suggestions would be appreciated.
> >>
> >> Thanks in Advance
> >> Susanne
> >>
> >>
> >
> >
> >.
> >