Hi,
The task: we have to two databases. Both evolved from one root in ancient
times. Right now they differ quite a lot. We want to take them both back to
one. To prepare it we have to make some kind of report (no matter what kind
–
standalone program, excel sheet, Visio diagram) to aid “uniformization”.
One
DB is Oracle, other SQL Server. Both have hundreds of tables with tenths of
fields. Any ideas?
PS. Is it the right Group for this question? ;)
Regards,
MaKcheck out this link..
http://www.dbbalance.com/download.htm
I haven't tested this tool by myself. Is this what u wanted?
hope this helps
--
"MaK" wrote:
> Hi,
> The task: we have to two databases. Both evolved from one root in ancient
> times. Right now they differ quite a lot. We want to take them both back t
o
> one. To prepare it we have to make some kind of report (no matter what kin
d –
> standalone program, excel sheet, Visio diagram) to aid “uniformization
. One
> DB is Oracle, other SQL Server. Both have hundreds of tables with tenths o
f
> fields. Any ideas?
> PS. Is it the right Group for this question? ;)
> Regards,
> MaK
Showing posts with label back. Show all posts
Showing posts with label back. Show all posts
Thursday, March 22, 2012
Sunday, February 19, 2012
Commit Nested Transaction
How do I commit a nested transaction while rolling back the outer transaction
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)
You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)
|||What about if one of the action's is a call to another stored procedure. The
action of that call will be rolled back right? Or is just if the action is a
statement in the same procedure?
"Andrew J. Kelly" wrote:
> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you issue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>
>
|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:
>What about if one of the action's is a call to another stored procedure. The
>action of that call will be rolled back right? Or is just if the action is a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
[vbcol=seagreen]
(snip)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...[vbcol=seagreen]
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)
You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)
|||What about if one of the action's is a call to another stored procedure. The
action of that call will be rolled back right? Or is just if the action is a
statement in the same procedure?
"Andrew J. Kelly" wrote:
> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you issue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>
>
|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:
>What about if one of the action's is a call to another stored procedure. The
>action of that call will be rolled back right? Or is just if the action is a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
[vbcol=seagreen]
(snip)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...[vbcol=seagreen]
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:
Commit Nested Transaction
How do I commit a nested transaction while rolling back the outer transaction
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)|||What about if one of the action's is a call to another stored procedure. The
action of that call will be rolled back right? Or is just if the action is a
statement in the same procedure?
"Andrew J. Kelly" wrote:
> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you issue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> > How do I commit a nested transaction while rolling back the outer
> > transaction
> >
> > BEGIN MASTER TRANSACTION
> >
> > Action 1
> >
> > BEGIN NESTED TRANSACTION
> > Action2
> > COMMIT NESTED TRANSACTION
> >
> > ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> > action
> > 2 committed.)
>
>|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:
>What about if one of the action's is a call to another stored procedure. The
>action of that call will be rolled back right? Or is just if the action is a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
>>A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:
>>Atomicity
>>A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed.
(snip)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:
>> You can't. If you roll back an outer transaction all the nested ones
>> roll
>> back as well. If you used a SAVE TRAN you can roll it back but any work
>> done inside that (even nested trans ) are rolled back as well. If you
>> issue
>> a plain ROLLBACK (not associated with a nested tran) then everything is
>> rolled back.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>> > How do I commit a nested transaction while rolling back the outer
>> > transaction
>> >
>> > BEGIN MASTER TRANSACTION
>> >
>> > Action 1
>> >
>> > BEGIN NESTED TRANSACTION
>> > Action2
>> > COMMIT NESTED TRANSACTION
>> >
>> > ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
>> > action
>> > 2 committed.)
>>
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)|||What about if one of the action's is a call to another stored procedure. The
action of that call will be rolled back right? Or is just if the action is a
statement in the same procedure?
"Andrew J. Kelly" wrote:
> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you issue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> > How do I commit a nested transaction while rolling back the outer
> > transaction
> >
> > BEGIN MASTER TRANSACTION
> >
> > Action 1
> >
> > BEGIN NESTED TRANSACTION
> > Action2
> > COMMIT NESTED TRANSACTION
> >
> > ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> > action
> > 2 committed.)
>
>|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:
>What about if one of the action's is a call to another stored procedure. The
>action of that call will be rolled back right? Or is just if the action is a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
>>A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:
>>Atomicity
>>A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed.
(snip)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:
>> You can't. If you roll back an outer transaction all the nested ones
>> roll
>> back as well. If you used a SAVE TRAN you can roll it back but any work
>> done inside that (even nested trans ) are rolled back as well. If you
>> issue
>> a plain ROLLBACK (not associated with a nested tran) then everything is
>> rolled back.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>> > How do I commit a nested transaction while rolling back the outer
>> > transaction
>> >
>> > BEGIN MASTER TRANSACTION
>> >
>> > Action 1
>> >
>> > BEGIN NESTED TRANSACTION
>> > Action2
>> > COMMIT NESTED TRANSACTION
>> >
>> > ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
>> > action
>> > 2 committed.)
>>
Commit Nested Transaction
How do I commit a nested transaction while rolling back the outer transactio
n
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)|||What about if one of the action's is a call to another stored procedure. Th
e
action of that call will be rolled back right? Or is just if the action is
a
statement in the same procedure?
"Andrew J. Kelly" wrote:
> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you iss
ue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>
>|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:
>What about if one of the action's is a call to another stored procedure. T
he
>action of that call will be rolled back right? Or is just if the action is
a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
[vbcol=seagreen]
(snip)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...[vbcol=seagreen]
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:
>
n
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)|||What about if one of the action's is a call to another stored procedure. Th
e
action of that call will be rolled back right? Or is just if the action is
a
statement in the same procedure?
"Andrew J. Kelly" wrote:
> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you iss
ue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>
>|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:
>What about if one of the action's is a call to another stored procedure. T
he
>action of that call will be rolled back right? Or is just if the action is
a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
[vbcol=seagreen]
(snip)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...[vbcol=seagreen]
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:
>
Commit Help
I might not be explaining this correct. I have a table right now that has 7
rows. I want to delete 1 of the rows. Then I want to go back and do the
rollback to undo the transaction of the delete. If it works the way I
understand, I should do a select statement and see the 7 rows. Delete the 1
I want. Do a select statement and see 6 rows. Then do the rollback. Then
another select statement and see the original 7 rows again. I can't do
something that gives me errors because I know I will get none. I know the
delete I am doing works. The issue is doing a delete and then a rollback to
obtain the information that I started with before I did the delete.
I can't do a stored procedure. I also can't do an else if program that
gives errors. All I want is a basic way to delete a row. Then view the
table to make sure it deleted, then go back and do the rollback command and
then view the table again to make sure that the original information is
there before I did the original delete.
Message posted via http://www.webservertalk.comNot sure I understand what you want, but, use QueryAnalyzer, then execute
the SQL exacyl like you described but wrapped in a begin transaction like so
begin transaction
select ...
delete...
select...
rollback...
end
JIM
"tina miller via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:b2387b603d094ec684efe5f85f1c30ef@.SQ
webservertalk.com...
>I might not be explaining this correct. I have a table right now that has 7
> rows. I want to delete 1 of the rows. Then I want to go back and do the
> rollback to undo the transaction of the delete. If it works the way I
> understand, I should do a select statement and see the 7 rows. Delete the
> 1
> I want. Do a select statement and see 6 rows. Then do the rollback. Then
> another select statement and see the original 7 rows again. I can't do
> something that gives me errors because I know I will get none. I know the
> delete I am doing works. The issue is doing a delete and then a rollback
> to
> obtain the information that I started with before I did the delete.
> I can't do a stored procedure. I also can't do an else if program that
> gives errors. All I want is a basic way to delete a row. Then view the
> table to make sure it deleted, then go back and do the rollback command
> and
> then view the table again to make sure that the original information is
> there before I did the original delete.
> --
> Message posted via http://www.webservertalk.com|||Read your previous post.
AMB
"tina miller via webservertalk.com" wrote:
> I might not be explaining this correct. I have a table right now that has
7
> rows. I want to delete 1 of the rows. Then I want to go back and do the
> rollback to undo the transaction of the delete. If it works the way I
> understand, I should do a select statement and see the 7 rows. Delete the
1
> I want. Do a select statement and see 6 rows. Then do the rollback. Then
> another select statement and see the original 7 rows again. I can't do
> something that gives me errors because I know I will get none. I know the
> delete I am doing works. The issue is doing a delete and then a rollback t
o
> obtain the information that I started with before I did the delete.
> I can't do a stored procedure. I also can't do an else if program that
> gives errors. All I want is a basic way to delete a row. Then view the
> table to make sure it deleted, then go back and do the rollback command an
d
> then view the table again to make sure that the original information is
> there before I did the original delete.
> --
> Message posted via http://www.webservertalk.com
>
rows. I want to delete 1 of the rows. Then I want to go back and do the
rollback to undo the transaction of the delete. If it works the way I
understand, I should do a select statement and see the 7 rows. Delete the 1
I want. Do a select statement and see 6 rows. Then do the rollback. Then
another select statement and see the original 7 rows again. I can't do
something that gives me errors because I know I will get none. I know the
delete I am doing works. The issue is doing a delete and then a rollback to
obtain the information that I started with before I did the delete.
I can't do a stored procedure. I also can't do an else if program that
gives errors. All I want is a basic way to delete a row. Then view the
table to make sure it deleted, then go back and do the rollback command and
then view the table again to make sure that the original information is
there before I did the original delete.
Message posted via http://www.webservertalk.comNot sure I understand what you want, but, use QueryAnalyzer, then execute
the SQL exacyl like you described but wrapped in a begin transaction like so
begin transaction
select ...
delete...
select...
rollback...
end
JIM
"tina miller via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:b2387b603d094ec684efe5f85f1c30ef@.SQ
webservertalk.com...
>I might not be explaining this correct. I have a table right now that has 7
> rows. I want to delete 1 of the rows. Then I want to go back and do the
> rollback to undo the transaction of the delete. If it works the way I
> understand, I should do a select statement and see the 7 rows. Delete the
> 1
> I want. Do a select statement and see 6 rows. Then do the rollback. Then
> another select statement and see the original 7 rows again. I can't do
> something that gives me errors because I know I will get none. I know the
> delete I am doing works. The issue is doing a delete and then a rollback
> to
> obtain the information that I started with before I did the delete.
> I can't do a stored procedure. I also can't do an else if program that
> gives errors. All I want is a basic way to delete a row. Then view the
> table to make sure it deleted, then go back and do the rollback command
> and
> then view the table again to make sure that the original information is
> there before I did the original delete.
> --
> Message posted via http://www.webservertalk.com|||Read your previous post.
AMB
"tina miller via webservertalk.com" wrote:
> I might not be explaining this correct. I have a table right now that has
7
> rows. I want to delete 1 of the rows. Then I want to go back and do the
> rollback to undo the transaction of the delete. If it works the way I
> understand, I should do a select statement and see the 7 rows. Delete the
1
> I want. Do a select statement and see 6 rows. Then do the rollback. Then
> another select statement and see the original 7 rows again. I can't do
> something that gives me errors because I know I will get none. I know the
> delete I am doing works. The issue is doing a delete and then a rollback t
o
> obtain the information that I started with before I did the delete.
> I can't do a stored procedure. I also can't do an else if program that
> gives errors. All I want is a basic way to delete a row. Then view the
> table to make sure it deleted, then go back and do the rollback command an
d
> then view the table again to make sure that the original information is
> there before I did the original delete.
> --
> Message posted via http://www.webservertalk.com
>
Thursday, February 16, 2012
CommandType changes back to text ALMOST EVERYTIME
When I open a report definition, go to a dataset and open it, the CommandType ALWAYS goes to "Text". I don't want "Text", I want "Stored Procedure". I NEED "Stored Procedure".
I have also found that the CommandText for a different dataset will change even though I have not opened it.
I guess the only solution I have is to open the RDL files, search for the dataset entries and then fix the XML code. This is a real pain in the buttocks when I am working with 300+ reports.
Is there a fix for this bug? Or are you going to FORCE everyone to use "Text"?
I am trying to finish this project so that we can release it to our customers but now I have to wonder why we decided to go with Reporting Services, especially now that we have to do double edits on each and every report.
Steven Broomhead
Optimum Solutions, Inc.
This happened to me with MDX queries.
One thing to try would be to determine why it is happening for some reports and not others. For me it was a carriage return within the RDL file where the query was. Removing this solved the issue.
Try doing a diff on reports that are and aren't experiencing this problem.
Also try using this product instead of visual studio.
http://www.fyireporting.com/
Another thing would be to try using the exec statement before the stored procedure and surrounding the stored proc in brackets, or if you are already doing this try removing the statement.
regards,
Andrew
|||Andrew,So far it has happened with every report.
If I access any dataset within a report, the CommandType for that dataset will change to Text. It happens every time. If I don't go back and reset it to StoredProcedure, the deployed report expects the call to be a text string not an SP name.
Personally, I like the StoredProcedure CommandType. The Text command with the embedded parameters can get hard to read and debug.
I was hopeful that someone would reply that it was a bug and that there was a hotfix available. I searched but have not had any success to date.
The fyiReporting program looks interesting but our developers need to deploy to a common development server not their local PC. That and the fact I would have to sell my boss on buying software that provides a similar tool to studio (from our MSDN Universal subscription, costing $$$$) because studio has a bug. I don't want that conversation
Steven or anyone,
Has anyone found a fix for this problem! As Steven explains it happens with every report I work on. Even after applying Visual Studio SP1 this bug still exists. It does not leave a warm feeling that this post goes back to Oct 2006 without any MS response.
Scott
CommandType changes back to text ALMOST EVERYTIME
When I open a report definition, go to a dataset and open it, the CommandType ALWAYS goes to "Text". I don't want "Text", I want "Stored Procedure". I NEED "Stored Procedure".
I have also found that the CommandText for a different dataset will change even though I have not opened it.
I guess the only solution I have is to open the RDL files, search for the dataset entries and then fix the XML code. This is a real pain in the buttocks when I am working with 300+ reports.
Is there a fix for this bug? Or are you going to FORCE everyone to use "Text"?
I am trying to finish this project so that we can release it to our customers but now I have to wonder why we decided to go with Reporting Services, especially now that we have to do double edits on each and every report.
Steven Broomhead
Optimum Solutions, Inc.
This happened to me with MDX queries.
One thing to try would be to determine why it is happening for some reports and not others. For me it was a carriage return within the RDL file where the query was. Removing this solved the issue.
Try doing a diff on reports that are and aren't experiencing this problem.
Also try using this product instead of visual studio.
http://www.fyireporting.com/
Another thing would be to try using the exec statement before the stored procedure and surrounding the stored proc in brackets, or if you are already doing this try removing the statement.
regards,
Andrew
|||Andrew,So far it has happened with every report.
If I access any dataset within a report, the CommandType for that dataset will change to Text. It happens every time. If I don't go back and reset it to StoredProcedure, the deployed report expects the call to be a text string not an SP name.
Personally, I like the StoredProcedure CommandType. The Text command with the embedded parameters can get hard to read and debug.
I was hopeful that someone would reply that it was a bug and that there was a hotfix available. I searched but have not had any success to date.
The fyiReporting program looks interesting but our developers need to deploy to a common development server not their local PC. That and the fact I would have to sell my boss on buying software that provides a similar tool to studio (from our MSDN Universal subscription, costing $$$$) because studio has a bug. I don't want that conversation
Steven or anyone,
Has anyone found a fix for this problem! As Steven explains it happens with every report I work on. Even after applying Visual Studio SP1 this bug still exists. It does not leave a warm feeling that this post goes back to Oct 2006 without any MS response.
Scott
Sunday, February 12, 2012
Command Line SQL Server Shutdown??
I need to write a script to run at a specific time to shutdown a
SQL Server. I also need to start the SQL Server back up.
Does anyone know the command line that could be used in a BAT script
to shutdown and then restart a SQL Server v.7
Please reply via email and post
Thanks in Advance,
JohnJohn,
You can use NET STOP and NET START, as in
net stop MSSQLServer
net start MSSQLServer
Please note that if sqlserveragent is dependent, then stop that service
first before stopping SQLServer service.These commands are documented in
BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"John" <jkimb@.kimberlyconsulting.com> wrote in message
news:pan.2004.05.07.21.19.25.367752@.kimberlyconsulting.com...
> I need to write a script to run at a specific time to shutdown a
> SQL Server. I also need to start the SQL Server back up.
> Does anyone know the command line that could be used in a BAT script
> to shutdown and then restart a SQL Server v.7
> Please reply via email and post
> Thanks in Advance,
> John
>|||"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:uEvi3qHNEHA.2064@.TK2MSFTNGP12.phx.gbl...
> John,
> You can use NET STOP and NET START, as in
> net stop MSSQLServer
> net start MSSQLServer
> Please note that if sqlserveragent is dependent, then stop that service
> first before stopping SQLServer service.These commands are documented in
> BooksOnLine.
In that case net stop mssqlserver /y also stops dependent services
SQL Server. I also need to start the SQL Server back up.
Does anyone know the command line that could be used in a BAT script
to shutdown and then restart a SQL Server v.7
Please reply via email and post
Thanks in Advance,
JohnJohn,
You can use NET STOP and NET START, as in
net stop MSSQLServer
net start MSSQLServer
Please note that if sqlserveragent is dependent, then stop that service
first before stopping SQLServer service.These commands are documented in
BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"John" <jkimb@.kimberlyconsulting.com> wrote in message
news:pan.2004.05.07.21.19.25.367752@.kimberlyconsulting.com...
> I need to write a script to run at a specific time to shutdown a
> SQL Server. I also need to start the SQL Server back up.
> Does anyone know the command line that could be used in a BAT script
> to shutdown and then restart a SQL Server v.7
> Please reply via email and post
> Thanks in Advance,
> John
>|||"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:uEvi3qHNEHA.2064@.TK2MSFTNGP12.phx.gbl...
> John,
> You can use NET STOP and NET START, as in
> net stop MSSQLServer
> net start MSSQLServer
> Please note that if sqlserveragent is dependent, then stop that service
> first before stopping SQLServer service.These commands are documented in
> BooksOnLine.
In that case net stop mssqlserver /y also stops dependent services
Command Line SQL Server Shutdown??
I need to write a script to run at a specific time to shutdown a
SQL Server. I also need to start the SQL Server back up.
Does anyone know the command line that could be used in a BAT script
to shutdown and then restart a SQL Server v.7
Please reply via email and post
Thanks in Advance,
John
John,
You can use NET STOP and NET START, as in
net stop MSSQLServer
net start MSSQLServer
Please note that if sqlserveragent is dependent, then stop that service
first before stopping SQLServer service.These commands are documented in
BooksOnLine.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"John" <jkimb@.kimberlyconsulting.com> wrote in message
news:pan.2004.05.07.21.19.25.367752@.kimberlyconsul ting.com...
> I need to write a script to run at a specific time to shutdown a
> SQL Server. I also need to start the SQL Server back up.
> Does anyone know the command line that could be used in a BAT script
> to shutdown and then restart a SQL Server v.7
> Please reply via email and post
> Thanks in Advance,
> John
>
|||"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:uEvi3qHNEHA.2064@.TK2MSFTNGP12.phx.gbl...
> John,
> You can use NET STOP and NET START, as in
> net stop MSSQLServer
> net start MSSQLServer
> Please note that if sqlserveragent is dependent, then stop that service
> first before stopping SQLServer service.These commands are documented in
> BooksOnLine.
In that case net stop mssqlserver /y also stops dependent services
SQL Server. I also need to start the SQL Server back up.
Does anyone know the command line that could be used in a BAT script
to shutdown and then restart a SQL Server v.7
Please reply via email and post
Thanks in Advance,
John
John,
You can use NET STOP and NET START, as in
net stop MSSQLServer
net start MSSQLServer
Please note that if sqlserveragent is dependent, then stop that service
first before stopping SQLServer service.These commands are documented in
BooksOnLine.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"John" <jkimb@.kimberlyconsulting.com> wrote in message
news:pan.2004.05.07.21.19.25.367752@.kimberlyconsul ting.com...
> I need to write a script to run at a specific time to shutdown a
> SQL Server. I also need to start the SQL Server back up.
> Does anyone know the command line that could be used in a BAT script
> to shutdown and then restart a SQL Server v.7
> Please reply via email and post
> Thanks in Advance,
> John
>
|||"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:uEvi3qHNEHA.2064@.TK2MSFTNGP12.phx.gbl...
> John,
> You can use NET STOP and NET START, as in
> net stop MSSQLServer
> net start MSSQLServer
> Please note that if sqlserveragent is dependent, then stop that service
> first before stopping SQLServer service.These commands are documented in
> BooksOnLine.
In that case net stop mssqlserver /y also stops dependent services
Subscribe to:
Posts (Atom)