How to Schedule MSDE Backup

Hi guys,
I have an application that runs on MSDE. I need to back up the database on a
dialy basis. The backup file gets written to this folder (C:\backup) and
then I have found a backup software that will automatically write it to a
DVD+RW, overwriting the previous backup file.
To make the schedule easy I will use 31 DVD+RWs, one for each day of the
month. That way I will always have at least a month worth of backups.
I was hoping that somebody could help me with a small SQL script that will
backup my database from Monday thru Saturday, automatically. Also it'd be
great if the backup file would contain a name and the date of that day.
Thanks a lot for your help.
Hi,
MSDE will not come with GUI. So u have to use SQLMAINT.exe. See the below
URL for more info.
http://msdn.microsoft.com/library/de...maint_19ix.asp
Thanks
Hari
SQL Server MVP
"Tom Bombadill" <Genius_poster@.yahoo.com> wrote in message
news:%23D3lQ7zsFHA.2792@.tk2msftngp13.phx.gbl...
> Hi guys,
> I have an application that runs on MSDE. I need to back up the database on
> a dialy basis. The backup file gets written to this folder (C:\backup) and
> then I have found a backup software that will automatically write it to a
> DVD+RW, overwriting the previous backup file.
> To make the schedule easy I will use 31 DVD+RWs, one for each day of the
> month. That way I will always have at least a month worth of backups.
> I was hoping that somebody could help me with a small SQL script that will
> backup my database from Monday thru Saturday, automatically. Also it'd be
> great if the backup file would contain a name and the date of that day.
> Thanks a lot for your help.
>
|||Hi Hari,
Thanks for your attempt to help me. Unfortunately for your suggestion to
work, I have to install SP3a and for some reason I cannot successfully apply
that. I constantly get the "The instance name specified is invalid" error,
even though I did not specify an instance name and there's only one
installaiton of MSDE on my machine.
What do you suggest?
|||hi Tom,
Tom Bombadill wrote:
> Hi Hari,
> Thanks for your attempt to help me. Unfortunately for your suggestion
> to work, I have to install SP3a and for some reason I cannot
> successfully apply that. I constantly get the "The instance name
> specified is invalid" error, even though I did not specify an
> instance name and there's only one installaiton of MSDE on my machine.
> What do you suggest?
you can have a look at a free prj of mine, at the link following my sign,
qhich implements a UI similar to Enterprise Manager where you can define a
SQL Server job to backup your db and of course define your schedules as
desired...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>
> you can have a look at a free prj of mine, at the link following my
> sign, qhich implements a UI similar to Enterprise Manager where you
> can define a SQL Server job to backup your db and of course define
> your schedules as desired...
alternatively, you can prepare a cmd file like
<-->
OSQL -Usa -Pyour_pwd -Q"BACKUP DATABASE pubs TO DISK = 'C:\Pubs.bak' WITH
INIT" >c:\err.txt
<-->
and schedule it via standard OS AT or SCHTASKS, the native Win scheduler...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
You're an angel, thanks a lot!
I downloaded your interface and installed it. I scheduled a backup and will
be testing this thoroughly.
A couple of questions re the interface if you don't mind:
1- I don't want to accumulate backups to the same file, since I will be
taking a fresh full backup on a separate DVD+RW everyday. How do I make sure
the backups do not keep appending to the same file day after day? Do I do
that just by leaving the ADD box unchecked, in the backup properties?
2- How can make it so that the backup file contains the day of the backup?
Thanks again,
|||hi Tom,
Tom Bombadill wrote:
> Andrea,
> You're an angel, thanks a lot!
you are wellcome.. thank you :D

> I downloaded your interface and installed it. I scheduled a backup
> and will be testing this thoroughly.
> A couple of questions re the interface if you don't mind:
> 1- I don't want to accumulate backups to the same file, since I will
> be taking a fresh full backup on a separate DVD+RW everyday. How do I
> make sure the backups do not keep appending to the same file day
> after day? Do I do that just by leaving the ADD box unchecked, in the
> backup properties?
leaving the "add to media" check box unchecked will add the "WITH INIT"
statement to the full backup statement, so that every backup set will be
initialized..

> 2- How can make it so that the backup file contains the day of the
> backup?
can you please expand on this? I'm sorry but I did not understand your
requirement..
and for DbaMgr2k related questions please feel free to contact me directly,
in order not to be OT on this public microsoft NG...
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
[vbcol=seagreen]
What I mean by that is to have the backup file name include the date the
backup was performed. I'll give you an example:
For today, it would be called "SJDB 09072005.bak"
For tomorrow, "SJDB 09082005.bak"
For the day after tomorrow, "SJDB 09092005.bak"
And so on...
I hope that makes sense!
Andrea, once again thank you for your service to the community and sharing
your hard work with others.
|||Also Andrea,
Do you know how long it would take for a full backup to run for a full size
MSDE database (2048 MB)?
I ask because I wanted to know how much time to allocate before I schedule
the copy of the backup file to DVD.
Thanks again,
|||hi Tom,
Tom Bombadill wrote:
> Hi Andrea,
>
> What I mean by that is to have the backup file name include the date
> the backup was performed. I'll give you an example:
> For today, it would be called "SJDB 09072005.bak"
> For tomorrow, "SJDB 09082005.bak"
> For the day after tomorrow, "SJDB 09092005.bak"
> And so on...
> I hope that makes sense!

ok, you have to "edit" the backup statement of the generated job's step ...
assuming you are backing up pubs database to C:\ (replace C:\ with the
existing folder you want to backup to), you can provide the file name to
include the current date casting (
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp )
GETDATE() function result as required..
the final statement will look like:
DECLARE @.FileName nvarchar(25)
SET @.FileName = 'C:\' + 'pubs ' + CONVERT(varchar(10) , GETDATE() , 110 ) +
'.bak'
BACKUP DATABASE [pubs] TO DISK = @.FileName WITH INIT ,
NOUNLOAD ,
NAME = N'pubs BackUp',
NOSKIP ,
STATS = 10,
NOFORMAT
as regard your date format, I'd prefer the standard ISO format, thats to say
YYYYMMDD (CONVERT using 112 style), as this data format , 20050908 (for
today) is more readable and sorts better then 09082005..
you can go further, in the "advanced tab", and specify you want to "output"
the result of the execution (like standard messages and/or errors, if any)
to a text file.. if you want to, specify a file name (a text file) in the
"Transact-SQL script command options" ->output file
this will output, in case of success, something like
<-->
Job 'BackUp DB ['pubs'] - #08/09/2005 12.25.12#' : Step 1, 'BackUp DB
['pubs']' : Began Executing 2005-09-08 12:37:16
53 percent backed up. [SQLSTATE 01000]
99 percent backed up. [SQLSTATE 01000]
Processed 224 pages for database 'pubs', file 'pubs' on file 1. [SQLSTATE
01000]
100 percent backed up. [SQLSTATE 01000]
Processed 1 pages for database 'pubs', file 'pubs_log' on file 1. [SQLSTATE
01000]
BACKUP DATABASE successfully processed 225 pages in 0.242 seconds (7.586
MB/sec). [SQLSTATE 01000]
<-->
or the problem found, if any, you can inspect...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How To Round With Negative Numbers?

I am using a select statement to obtain a result set back with aggregated
data. The problem is that I am seeing column data with 11 to 13 digits
after the decimal point. I tried using the STR function, but then the Order
By clause does not sort properly because there are negative numbers in the
aggregated data... I tried using Round, but that does no good either - it
still ends up displaying too many digits after the decimal point. Right now
I'm just using Query Analyzer to display the data, so I can live with it for
now. But, in the future, my app will be getting a result set back and I
would prefer not to have to go through each row and do a round on it from
the program. Does anyone know how to solve this problem?

Thanks for any help,

BobBob, what is the actual problem? What does how the numbers display
have to do with the sort order? Do you want the negatvie numbers and
the positive values to sort alike? ABS function? Round in select list
but not in the order by?

Perhaps a sample SQL and output would help someone provide the right
solution.

HTH -- Mark D Powell --|||Bob Bryan (RobertGBryanREMOVETHIS@.yahoo.com) writes:
> I am using a select statement to obtain a result set back with
> aggregated data. The problem is that I am seeing column data with 11 to
> 13 digits after the decimal point. I tried using the STR function, but
> then the Order By clause does not sort properly because there are
> negative numbers in the aggregated data... I tried using Round, but
> that does no good either - it still ends up displaying too many digits
> after the decimal point. Right now I'm just using Query Analyzer to
> display the data, so I can live with it for now. But, in the future, my
> app will be getting a result set back and I would prefer not to have to
> go through each row and do a round on it from the program. Does anyone
> know how to solve this problem?

If I could understand the problem, maybe I could solve it. :-)

It sounds as if you are working with floats, which are approxamite
numbers. You can round a value, but you may still see many decimals,
because there may be no exact represenation of the number. You could
convert to decimal, which is a precise type. You could also consider
handling the formatting of the data in the client.

But without knowledge about your data and their data types it's difficult
to say anything more intelligent.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Mark D Powell" <Mark.Powell@.eds.com> wrote in message
news:1106753337.073272.7040@.z14g2000cwz.googlegrou ps.com...
> Bob, what is the actual problem? What does how the numbers display
> have to do with the sort order? Do you want the negatvie numbers and
> the positive values to sort alike? ABS function? Round in select list
> but not in the order by?
> Perhaps a sample SQL and output would help someone provide the right
> solution.
> HTH -- Mark D Powell --

Ok, here is my query:

select [TS Bars Back] as BB, [TS Bars Within] as BW,
str([TS Move %], 6, 4) as "Move %", str([TS Entry %], 6, 4) as "Entry T",
str([TS ATR Profit], 7, 2) as "P Goal", sum([P/L Comm]) as "P/L $",
str(sum(Risk), 7, 2) as "Risk",
str(avg([P/L Avg %]), 7, 2) as "P/L %",
str(sum([P/L %]), 10, 2) as "P/L % Sum",
str(avg([P/L Long]), 7, 4) as "Long $", str(avg([P/L Short]), 7, 4) as
"Short $",
Count([# of trades]) as "# Runs", Sum([# of trades]) as "# Trades",
str(sum([Max Drawdown]), 10, 2) as "Max $ DD"
from [Table1].dbo.SumResults
where Symbol = 'GE_1/1m' and [SE Time] = 3600 and [TS Entry %] = .005 and
[TS ATR Stop] = 0
group by [TS Move %], [TS Entry %], [TS Bars Within], [TS Bars Back], [TS
ATR Profit]
order by [P/L $] desc

The output looks like this:

BB BW Move % Entry T P Goal P/L $ Risk
P/L % P/L % Sum Long $ Short $ # Runs # Trades Max $ DD
30 8 0.0075 0.0050 1.20 1452.6800537109375 0.30
1.82 7.26 1191.44 261.240 1 4
429.07
30 8 0.0075 0.0050 1.30 1452.6800537109375 0.30
1.82 7.26 1191.44 261.240 1 4
429.07

So, you can see that the str function works well to limit the # of decimal
points displayed for the other fields.
However, if I use it for the "P/L $" field, then the sort does not come out
right because the order by sorts based
upon the resulting character string and not the number in the field. I need
to limit the number of digits displayed in
the P/L $ field without affecting the sort order. Anybody know how to do
that?

Bob|||Thank you for the idea of using a decimal field instead of a float. Most of
my columns are floats (or reals). So, I tried doing a cast of the real
column to a decimal and it worked like a charm.

For those interested in the syntax, this is what worked:

sum(cast ([P/L Comm] as decimal(10,3))) as "P/L $",

Thanks again,

Bob

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95EB168E856Yazorman@.127.0.0.1...
> Bob Bryan (RobertGBryanREMOVETHIS@.yahoo.com) writes:
> > I am using a select statement to obtain a result set back with
> > aggregated data. The problem is that I am seeing column data with 11 to
> > 13 digits after the decimal point. I tried using the STR function, but
> > then the Order By clause does not sort properly because there are
> > negative numbers in the aggregated data... I tried using Round, but
> > that does no good either - it still ends up displaying too many digits
> > after the decimal point. Right now I'm just using Query Analyzer to
> > display the data, so I can live with it for now. But, in the future, my
> > app will be getting a result set back and I would prefer not to have to
> > go through each row and do a round on it from the program. Does anyone
> > know how to solve this problem?
> If I could understand the problem, maybe I could solve it. :-)
> It sounds as if you are working with floats, which are approxamite
> numbers. You can round a value, but you may still see many decimals,
> because there may be no exact represenation of the number. You could
> convert to decimal, which is a precise type. You could also consider
> handling the formatting of the data in the client.
> But without knowledge about your data and their data types it's difficult
> to say anything more intelligent.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

How to rollback the database

I am NOT talking about one transaction. I need to go back
a day or more. Either 7 or 2000. Not a stand by server
either.
Is it possible? Please answer.Aziz wrote:
> I am NOT talking about one transaction. I need to go back
> a day or more. Either 7 or 2000. Not a stand by server
> either.
> Is it possible? Please answer.
You can restore a backup and then restore the transaction log to a point
in time. Is that what you are looking for? You'll need a database
backup, plus and transaction logs for this to happen.
--
David G.|||Possible, if you have a backups of the database, that were taken at the
point, to which you want to rollback to.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Aziz" <goaziz@.yahoo.com> wrote in message
news:92fc01c496b9$080bbe40$a501280a@.phx.gbl...
I am NOT talking about one transaction. I need to go back
a day or more. Either 7 or 2000. Not a stand by server
either.
Is it possible? Please answer.|||No. I can not use backupp to restore. Because it is too
big. It takes more than 6 hours to restore.
>--Original Message--
>Possible, if you have a backups of the database, that
were taken at the
>point, to which you want to rollback to.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"Aziz" <goaziz@.yahoo.com> wrote in message
>news:92fc01c496b9$080bbe40$a501280a@.phx.gbl...
>I am NOT talking about one transaction. I need to go back
>a day or more. Either 7 or 2000. Not a stand by server
>either.
>Is it possible? Please answer.
>
>.
>|||There is not functionality in SQL Server where you can tell the product to go back a certain amount
of time. Restore is your option.
However, you can look at the log reading tools available, see my links page for where to find some
of them.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aziz Karim" <anonymous@.discussions.microsoft.com> wrote in message
news:99d601c49749$8f959c10$a601280a@.phx.gbl...
> No. I can not use backupp to restore. Because it is too
> big. It takes more than 6 hours to restore.
>
>>--Original Message--
>>Possible, if you have a backups of the database, that
> were taken at the
>>point, to which you want to rollback to.
>>--
>>HTH,
>>Vyas, MVP (SQL Server)
>>http://vyaskn.tripod.com/
>>
>>"Aziz" <goaziz@.yahoo.com> wrote in message
>>news:92fc01c496b9$080bbe40$a501280a@.phx.gbl...
>>I am NOT talking about one transaction. I need to go back
>>a day or more. Either 7 or 2000. Not a stand by server
>>either.
>>Is it possible? Please answer.
>>
>>.|||There is an option built into Lumigent's Log Explorer
to "back out" transactions.
This may accomplish what you need, assuming you haven't
truncated your log.
Matthew Bando
Matthew.Bando@.remove CSCTGI.com
>--Original Message--
>There is not functionality in SQL Server where you can
tell the product to go back a certain amount
>of time. Restore is your option.
>However, you can look at the log reading tools
available, see my links page for where to find some
>of them.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Aziz Karim" <anonymous@.discussions.microsoft.com> wrote
in message
>news:99d601c49749$8f959c10$a601280a@.phx.gbl...
>> No. I can not use backupp to restore. Because it is too
>> big. It takes more than 6 hours to restore.
>>
>>--Original Message--
>>Possible, if you have a backups of the database, that
>> were taken at the
>>point, to which you want to rollback to.
>>--
>>HTH,
>>Vyas, MVP (SQL Server)
>>http://vyaskn.tripod.com/
>>
>>"Aziz" <goaziz@.yahoo.com> wrote in message
>>news:92fc01c496b9$080bbe40$a501280a@.phx.gbl...
>>I am NOT talking about one transaction. I need to go
back
>>a day or more. Either 7 or 2000. Not a stand by server
>>either.
>>Is it possible? Please answer.
>>
>>.
>
>.
>

How to rollback properly

Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in your
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>

How to rollback properly

Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in you
r
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>

How to rollback properly

Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?
Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in your
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:

> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>

How to roll back the replication?

Hi, all.
I make a db replacated as distributor.
I decided later removing replication.
But, I don't know how to remove rowguid column from all tables.
How can I set back to the initial state of DB before replication?
thank you..You can't that I know of. You will need to alter the tables and drop the columns not needed.

how to roll back

is this the only way to do a rollback in SQL2000 ?
Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
undo the changes. And only applicable for UPDATE & DELETE queries within the
transaction.
just want to understand more how to use rollback and how it works.
tks
pkHi pk.
You can only use rollback within a transaction block. If you commit a
transaction, there's no way to roll it back later.
A quick demo of how to use rollback in tsql is:
declare @.err int
declare @.err = 0
set xact_abort off -- set off or on for auto rollback on any error
set transaction isolation level read committed -- sets isolation (locking)
level
begin transaction
update table1 set column1 = 'a' where columnpk = 123
set @.err = @.err + @.@.error
insert into table2 (column2, column3) values ('1', 123)
set @.err = @.err + @.@.error
if @.@.error != 0
rollback
else
commit
HTH
Regards,
Greg Linwood
SQL Server MVP
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> just want to understand more how to use rollback and how it works.
> tks
> pk
>|||HI Greg,
the "set @.err = @.err + @.@.error" command set the @.@.error to 0, this script ne
ver make rollback.
Use the "if @.err != 0" command!
JBandi
-- Greg Linwood wrote: --
Hi pk.
You can only use rollback within a transaction block. If you commit a
transaction, there's no way to roll it back later.
A quick demo of how to use rollback in tsql is:
declare @.err int
declare @.err = 0
set xact_abort off -- set off or on for auto rollback on any error
set transaction isolation level read committed -- sets isolation (locking)
level
begin transaction
update table1 set column1 = 'a' where columnpk = 123
set @.err = @.err + @.@.error
insert into table2 (column2, column3) values ('1', 123)
set @.err = @.err + @.@.error
if @.@.error != 0
rollback
else
commit
HTH
Regards,
Greg Linwood
SQL Server MVP
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> pk|||Yes - my bad there! Thanks for picking it up (:
Regards,
Greg Linwood
SQL Server MVP
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:C4555093-2AD6-44DE-A43E-CCF5792D49E3@.microsoft.com...
> HI Greg,
> the "set @.err = @.err + @.@.error" command set the @.@.error to 0, this script
never make rollback.
> Use the "if @.err != 0" command!
> JBandi
> -- Greg Linwood wrote: --
> Hi pk.
> You can only use rollback within a transaction block. If you commit a
> transaction, there's no way to roll it back later.
> A quick demo of how to use rollback in tsql is:
> declare @.err int
> declare @.err = 0
> set xact_abort off -- set off or on for auto rollback on any error
> set transaction isolation level read committed -- sets isolation
(locking)
> level
> begin transaction
> update table1 set column1 = 'a' where columnpk = 123
> set @.err = @.err + @.@.error
> insert into table2 (column2, column3) values ('1', 123)
> set @.err = @.err + @.@.error
> if @.@.error != 0
> rollback
> else
> commit
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "pk" <pk@.> wrote in message
news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
TRAN to
within
> the|||In addition to Greg's response and to directly answer your question,
rollbacks affect not only updates and deletes, but inserts also.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> just want to understand more how to use rollback and how it works.
> tks
> pk
>|||Hi all,
I placed the SET XACT_ABORT ON statement introduced by Greg at the beginning
of a sproc, but appearently it did not skip the t-sql errors happended with
in the sproc and rollback. I'm hoping to find a method to skip all error me
ssages and just rollback.
Is there a way to do that?
Thanks,
-Lawrence
"Greg Linwood" wrote:

> Hi pk.
> You can only use rollback within a transaction block. If you commit a
> transaction, there's no way to roll it back later.
> A quick demo of how to use rollback in tsql is:
> declare @.err int
> declare @.err = 0
> set xact_abort off -- set off or on for auto rollback on any error
> set transaction isolation level read committed -- sets isolation (locking)
> level
> begin transaction
> update table1 set column1 = 'a' where columnpk = 123
> set @.err = @.err + @.@.error
> insert into table2 (column2, column3) values ('1', 123)
> set @.err = @.err + @.@.error
> if @.@.error != 0
> rollback
> else
> commit
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> the
>
>

how to roll back

is this the only way to do a rollback in SQL2000 ?
Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
undo the changes. And only applicable for UPDATE & DELETE queries within the
transaction.
just want to understand more how to use rollback and how it works.
tks
pk
Hi pk.
You can only use rollback within a transaction block. If you commit a
transaction, there's no way to roll it back later.
A quick demo of how to use rollback in tsql is:
declare @.err int
declare @.err = 0
set xact_abort off -- set off or on for auto rollback on any error
set transaction isolation level read committed -- sets isolation (locking)
level
begin transaction
update table1 set column1 = 'a' where columnpk = 123
set @.err = @.err + @.@.error
insert into table2 (column2, column3) values ('1', 123)
set @.err = @.err + @.@.error
if @.@.error != 0
rollback
else
commit
HTH
Regards,
Greg Linwood
SQL Server MVP
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> just want to understand more how to use rollback and how it works.
> tks
> pk
>
|||HI Greg,
the "set @.err = @.err + @.@.error" command set the @.@.error to 0, this script never make rollback.
Use the "if @.err != 0" command!
JBandi
-- Greg Linwood wrote: --
Hi pk.
You can only use rollback within a transaction block. If you commit a
transaction, there's no way to roll it back later.
A quick demo of how to use rollback in tsql is:
declare @.err int
declare @.err = 0
set xact_abort off -- set off or on for auto rollback on any error
set transaction isolation level read committed -- sets isolation (locking)
level
begin transaction
update table1 set column1 = 'a' where columnpk = 123
set @.err = @.err + @.@.error
insert into table2 (column2, column3) values ('1', 123)
set @.err = @.err + @.@.error
if @.@.error != 0
rollback
else
commit
HTH
Regards,
Greg Linwood
SQL Server MVP
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> pk
|||Yes - my bad there! Thanks for picking it up (:
Regards,
Greg Linwood
SQL Server MVP
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:C4555093-2AD6-44DE-A43E-CCF5792D49E3@.microsoft.com...
> HI Greg,
> the "set @.err = @.err + @.@.error" command set the @.@.error to 0, this script
never make rollback.
> Use the "if @.err != 0" command!
> JBandi
> -- Greg Linwood wrote: --
> Hi pk.
> You can only use rollback within a transaction block. If you commit a
> transaction, there's no way to roll it back later.
> A quick demo of how to use rollback in tsql is:
> declare @.err int
> declare @.err = 0
> set xact_abort off -- set off or on for auto rollback on any error
> set transaction isolation level read committed -- sets isolation
(locking)
> level
> begin transaction
> update table1 set column1 = 'a' where columnpk = 123
> set @.err = @.err + @.@.error
> insert into table2 (column2, column3) values ('1', 123)
> set @.err = @.err + @.@.error
> if @.@.error != 0
> rollback
> else
> commit
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "pk" <pk@.> wrote in message
news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
TRAN to
within
> the
|||In addition to Greg's response and to directly answer your question,
rollbacks affect not only updates and deletes, but inserts also.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> is this the only way to do a rollback in SQL2000 ?
> Use Query Analyzer, BEGIN TRAN ..... COMMIT TRAN, then ROLLBACK TRAN to
> undo the changes. And only applicable for UPDATE & DELETE queries within
the
> transaction.
> just want to understand more how to use rollback and how it works.
> tks
> pk
>
|||Hi all,
I placed the SET XACT_ABORT ON statement introduced by Greg at the beginning of a sproc, but appearently it did not skip the t-sql errors happended within the sproc and rollback. I'm hoping to find a method to skip all error messages and just rollback.
Is there a way to do that?
Thanks,
-Lawrence
"Greg Linwood" wrote:

> Hi pk.
> You can only use rollback within a transaction block. If you commit a
> transaction, there's no way to roll it back later.
> A quick demo of how to use rollback in tsql is:
> declare @.err int
> declare @.err = 0
> set xact_abort off -- set off or on for auto rollback on any error
> set transaction isolation level read committed -- sets isolation (locking)
> level
> begin transaction
> update table1 set column1 = 'a' where columnpk = 123
> set @.err = @.err + @.@.error
> insert into table2 (column2, column3) values ('1', 123)
> set @.err = @.err + @.@.error
> if @.@.error != 0
> rollback
> else
> commit
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "pk" <pk@.> wrote in message news:epHxmqTFEHA.3424@.tk2msftngp13.phx.gbl...
> the
>
>

How to return error from CLR Stored Procedure

I have a C# stored procedure that I use to run a query, do some processing o
n
the results and send the results back to the caller via
SqlPipe.SendResultsRow(). It looks something like this:
string myConditions = MyFunctionToBuildTheConditionsFromCaller
ProvidedData()
;
SqlCommand cmd = new SqlCommand("SELECT * FROM myTable WHERE" +
myConditions, contextConnection);
dataRecord = MyFunctionToBuildTheDataRecord();
sqlPipe.SendResultsStart(dataRecord);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
// Do some processing
if (someInternalConditonForThisRecordIsMet)
{
sqlPipe.SendResultsRow(dataRecord);
}
}
sqlPipe.SendResultsEnd();
}
Not every record from the original dataset is returned. The caller is using
SqlCommand.ExecuteReader() to retrieve the set of records from the stored
procedure.
Here's the problem...
The caller is a web page that allows the user to specify search criteria.
If the user specifies search criteria that is too broad, the query will
return tens of thousands of records. To prevent this, I want my C# procedur
e
to stop after some number of maximum records and return an error. So that
looks something like this:
if (someInternalConditonForThisRecordIsMet)
{
if (++recordCounter <= maximumRecordsToReturn)
{
sqlPipe.SendResultsRow(dataRecord);
}
else
// Bail out and report an error to the caller
}
I've tried throwing an exception in the 'else' but Sql Server seems to
absorb it.
The caller receives exactly the maximum number of records but doesn't catch
an exception.
else
throw new ApplicationException("Too many records matched search
criteria.");
I could set the function to return an integer indicating if there were too
many results, but I could not find a property/method of the SqlDataReader
that retrieved the value returned from the procedure.
else
break; // break out of while (reader.Read())
// this line is at the ned of the procedure
return recordCounter > maximumRecordsToReturn ? 1 : 0;
I could set an output parameter on the stored procedure, but again, I could
not find a property/method of the SqlDataReader class that could retrieve th
e
output parameter.
How could/should I return this error condition back to the caller?
Steven Hughes - MCSDTry closing the datareader and then getting the return value. The return
value is only available after the operation finishes. While the datareader
is open, the operation is not complete. (I know, that seems odd - but who
are we to question...)
dr.Close();
int i=(int)cmd.Parameters["@.retval"].Value;
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Steven Hughes" <shughes@.noemail.nospam> wrote in message
news:6C5FAECA-1795-480E-92F2-9AB5A5B126E4@.microsoft.com...
>I have a C# stored procedure that I use to run a query, do some processing
>on
> the results and send the results back to the caller via
> SqlPipe.SendResultsRow(). It looks something like this:
> string myConditions =
> MyFunctionToBuildTheConditionsFromCaller
ProvidedData();
> SqlCommand cmd = new SqlCommand("SELECT * FROM myTable WHERE" +
> myConditions, contextConnection);
> dataRecord = MyFunctionToBuildTheDataRecord();
> sqlPipe.SendResultsStart(dataRecord);
> SqlDataReader reader = cmd.ExecuteReader();
> while (reader.Read())
> {
> // Do some processing
> if (someInternalConditonForThisRecordIsMet)
> {
> sqlPipe.SendResultsRow(dataRecord);
> }
> }
> sqlPipe.SendResultsEnd();
> }
> Not every record from the original dataset is returned. The caller is
> using
> SqlCommand.ExecuteReader() to retrieve the set of records from the stored
> procedure.
>
> Here's the problem...
> The caller is a web page that allows the user to specify search criteria.
> If the user specifies search criteria that is too broad, the query will
> return tens of thousands of records. To prevent this, I want my C#
> procedure
> to stop after some number of maximum records and return an error. So that
> looks something like this:
> if (someInternalConditonForThisRecordIsMet)
> {
> if (++recordCounter <= maximumRecordsToReturn)
> {
> sqlPipe.SendResultsRow(dataRecord);
> }
> else
> // Bail out and report an error to the caller
> }
>
> I've tried throwing an exception in the 'else' but Sql Server seems to
> absorb it.
> The caller receives exactly the maximum number of records but doesn't
> catch
> an exception.
> else
> throw new ApplicationException("Too many records matched search
> criteria.");
> I could set the function to return an integer indicating if there were too
> many results, but I could not find a property/method of the SqlDataReader
> that retrieved the value returned from the procedure.
> else
> break; // break out of while (reader.Read())
> // this line is at the ned of the procedure
> return recordCounter > maximumRecordsToReturn ? 1 : 0;
>
> I could set an output parameter on the stored procedure, but again, I
> could
> not find a property/method of the SqlDataReader class that could retrieve
> the
> output parameter.
>
> How could/should I return this error condition back to the caller?
> --
> Steven Hughes - MCSD|||Hi,
Thanks for your post!
From your description, I understand that:
You were using C# to develop the CLR SQL Stored Procedure;
you found when a heavy load query from Web degraded the performance
seriously.
If I have misunderstood, please to let me know.
You may write a common stored procedure to get any query result row count
for appropriate decision by your application.
Also you may write a pagination stored procedure to get a specified count
of records once for appropriate display.
Here is a sample just for reference:
CREATE Procedure proc_getquerybypage
(
@.PageSize int, -- record count of every page
@.PageNumber int, -- current page number
@.QuerySql varchar(1000),--partial query string ,like '* From TABLENAME
order by ID desc'
@.KeyField varchar(500)
)
AS
Begin
Declare @.SqlTable AS varchar(1000)
Declare @.SqlText AS Varchar(1000)
Set @.SqlTable='Select Top '+CAST(@.PageNumber*@.PageSize AS varchar(30))+'
'+@.QuerySql
Set @.SqlText='Select Top '+Cast(@.PageSize AS varchar(30))+' * From '
+'('+@.SqlTable+') As TembTbA '
+'Where '+@.KeyField+' Not In (Select Top
'+CAST((@.PageNumber-1)*@.PageSize AS varchar(30))+' '+@.KeyField+' From '
+'('+@.SqlTable+') AS TempTbB)'
Exec(@.SqlText)
End
GO
You may refer to this article for "CLR Stored Procedure":
http://msdn2.microsoft.com/zh-cn/library/ms131094.aspx
Please note that this is a C#.NET development issue, when you meet such
issue next time, I recommend you post it to
microsoft.public.dotnet.languages.csharp for wider audience and more
professional solution than here.
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Unfortunately, the situation is not that simple. Not every row that is
selected in the stored procedures query is returned to the caller. Complex
logic which includes pulling data from other tables for each record in
question must be performed to determine if the record can be sent back to th
e
caller. This logic must run for all records in the table before a count of
how many records will be returned is known.
Steven Hughes - MCSD
"Charles Wang[MSFT]" wrote:

> Hi,
> Thanks for your post!
> From your description, I understand that:
> You were using C# to develop the CLR SQL Stored Procedure;
> you found when a heavy load query from Web degraded the performance
> seriously.
> If I have misunderstood, please to let me know.
> You may write a common stored procedure to get any query result row count
> for appropriate decision by your application.
> Also you may write a pagination stored procedure to get a specified count
> of records once for appropriate display.
> Here is a sample just for reference:
> CREATE Procedure proc_getquerybypage
> (
> @.PageSize int, -- record count of every page
> @.PageNumber int, -- current page number
> @.QuerySql varchar(1000),--partial query string ,like '* From TABLENAME
> order by ID desc'
> @.KeyField varchar(500)
> )
> AS
> Begin
> Declare @.SqlTable AS varchar(1000)
> Declare @.SqlText AS Varchar(1000)
> Set @.SqlTable='Select Top '+CAST(@.PageNumber*@.PageSize AS varchar(30))+'
> '+@.QuerySql
> Set @.SqlText='Select Top '+Cast(@.PageSize AS varchar(30))+' * From '
> +'('+@.SqlTable+') As TembTbA '
> +'Where '+@.KeyField+' Not In (Select Top
> '+CAST((@.PageNumber-1)*@.PageSize AS varchar(30))+' '+@.KeyField+' From '
> +'('+@.SqlTable+') AS TempTbB)'
> Exec(@.SqlText)
> End
> GO
> You may refer to this article for "CLR Stored Procedure":
> http://msdn2.microsoft.com/zh-cn/library/ms131094.aspx
> Please note that this is a C#.NET development issue, when you meet such
> issue next time, I recommend you post it to
> microsoft.public.dotnet.languages.csharp for wider audience and more
> professional solution than here.
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a w to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others:
> https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||That works!! Thank you.
Too bad I have to read in the resultset before being able to retrieve the
output parameter value though.
Steven Hughes - MCSD
"Arnie Rowland" wrote:

> Try closing the datareader and then getting the return value. The return
> value is only available after the operation finishes. While the datareader
> is open, the operation is not complete. (I know, that seems odd - but who
> are we to question...)
> dr.Close();
> int i=(int)cmd.Parameters["@.retval"].Value;
>
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Steven Hughes" <shughes@.noemail.nospam> wrote in message
> news:6C5FAECA-1795-480E-92F2-9AB5A5B126E4@.microsoft.com...
>
>|||Your stored proc can just return the result code you wish, return a non-zero
value to indicate that something did not go as expected. You can also use
RAISERROR to raise a waring or error to the client. Here is a simple example
for an sp called LimitedSP. colors is a table with some three rows of color
s
colors in it. Using it looks like:
DECLARE @.resultCode int
exec @.resultCode = LimitedSP
SELECT @.resultCode
and returns
color
--
red
green
1
result code of sp was 1, which by convention in this sp means not all rows
were returnd.
Here is the sp itself.
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
// make sp return an Int32, this will the result code from SP
public static Int32 LimitedSP()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("SELECT color FROM colors", conn))
{
SqlPipe pipe = SqlContext.Pipe;
conn.Open();
SqlMetaData[] md = new SqlMetaData[1];
md[0] = new SqlMetaData("color", SqlDbType.NVarChar, SqlMetaData.Max);
SqlDataRecord rec = new SqlDataRecord(md);
pipe.SendResultsStart(rec);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
Int32 limit = 2; // don't return more than four rows
Int32 index = 1;
while (rdr.Read())
{
string color = rdr.GetSqlString(0).Value;
rec.SetSqlString(0, color);
if (index > limit)
{
pipe.SendResultsEnd();
rdr.Close();
// use higher level if you want this to be more than a warning
cmd.CommandText = "RAISERROR('too many rows', 10, 1)";
cmd.ExecuteNonQuery();
return 1; // indicates not all rows returned
}
pipe.SendResultsRow(rec);
index++;
}
pipe.SendResultsEnd();
return 0; // indicates all rows were returned
}
}
}
};
Dan
> Unfortunately, the situation is not that simple. Not every row that
> is selected in the stored procedures query is returned to the caller.
> Complex logic which includes pulling data from other tables for each
> record in question must be performed to determine if the record can be
> sent back to the caller. This logic must run for all records in the
> table before a count of how many records will be returned is known.
> "Charles Wang[MSFT]" wrote:
>|||Interesting... so I use RAISEERROR like I would from a standard stored
procedure then.
Ok. Thank you.
Steven Hughes - MCSD
"Dan Sullivan" wrote:

> Your stored proc can just return the result code you wish, return a non-ze
ro
> value to indicate that something did not go as expected. You can also use
> RAISERROR to raise a waring or error to the client. Here is a simple examp
le
> for an sp called LimitedSP. colors is a table with some three rows of col
ors
> colors in it. Using it looks like:
> DECLARE @.resultCode int
> exec @.resultCode = LimitedSP
> SELECT @.resultCode
> and returns
> color
> --
> red
> green
> --
> 1
> result code of sp was 1, which by convention in this sp means not all rows
> were returnd.
>
> Here is the sp itself.
> public partial class StoredProcedures
> {
> [Microsoft.SqlServer.Server.SqlProcedure]
> // make sp return an Int32, this will the result code from SP
> public static Int32 LimitedSP()
> {
> using (SqlConnection conn = new SqlConnection("context connection=true")
)
> using (SqlCommand cmd = new SqlCommand("SELECT color FROM colors", conn)
)
> {
> SqlPipe pipe = SqlContext.Pipe;
> conn.Open();
> SqlMetaData[] md = new SqlMetaData[1];
> md[0] = new SqlMetaData("color", SqlDbType.NVarChar, SqlMetaData.Max);
> SqlDataRecord rec = new SqlDataRecord(md);
> pipe.SendResultsStart(rec);
> using (SqlDataReader rdr = cmd.ExecuteReader())
> {
> Int32 limit = 2; // don't return more than four rows
> Int32 index = 1;
> while (rdr.Read())
> {
> string color = rdr.GetSqlString(0).Value;
> rec.SetSqlString(0, color);
> if (index > limit)
> {
> pipe.SendResultsEnd();
> rdr.Close();
> // use higher level if you want this to be more than a warning
> cmd.CommandText = "RAISERROR('too many rows', 10, 1)";
> cmd.ExecuteNonQuery();
> return 1; // indicates not all rows returned
> }
> pipe.SendResultsRow(rec);
> index++;
> }
> pipe.SendResultsEnd();
> return 0; // indicates all rows were returned
> }
> }
> }
> };
>
> Dan
>
>
>|||Hi,
I'm glad to see you got the answer you want.
Thanks for using Microsoft Newsgroup.
If you have any other concerns, please don't hesitate to let us know.
Enjoy your day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

How to return a value from SP

Hi all,

How to return a value from a store procedure?

I use a VBA to call a store procedure, but I would like to be able to return the result back to a variable.

Here is an VBA example:

Dim GetDestinationID As Long

Dim conConnection As ADODB.Connection
Dim StrSQL As String

Set conConnection = CurrentProject.Connection

StrSQL = "usp_GetDestinationID " & 2 & ", " & _
GetDestinationID

conConnection.Execute StrSQL, iAffected, adExecuteNoRecords

I would like to return GetDestinationID.

Here is the SP:

CREATE PROCEDURE [dbo].[usp_GetDestinationID]

(
@.intOrderID int,
@.intDestinationID int=0 OUTPUT

)

AS

BEGIN

set @.intDestinationID=(SELECT lv.DestinationID
FROM [Land Voyages] AS lv INNER JOIN [Pickup Booking List] AS pbl
ON lv.LandVoyageID=pbl.LandVoyageID
WHERE pbl.OrderID = @.intOrderID)

END
GO

What is wrong? Can I return a value to a Visual Basic Application from a Store Procedure?

Regardshttp://dbforums.com/t916861.html|||HI all

I found a solution to my question about VBA calling a Stored Procedure and returning a value

VBA:
'-------------------
Private Function GetDestinationID(OrderID As Long) As Long
On Error GoTo GetDestinationID_Err

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd

.ActiveConnection = CurrentProject.Connection
.CommandText = "usp_GetDestinationID"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.intOrderID", adInteger, adParamInput, , OrderID)
.Parameters.Append .CreateParameter("@.intDestinationID", adInteger, adParamOutput)
.Execute
GetDestinationID = .Parameters("@.intDestinationID").Value
End With

WrapUp:


Exit_GetDestinationID:
Set cmd = Nothing
Exit Function

GetDestinationID_Err:
Call LogMsgError(Err.Description, Err.Number, ModuleName$, "GetDestinationID")
Resume Exit_GetDestinationID
End Function

'----------------
T-SQL:

CREATE PROCEDURE dbo.usp_GetDestinationID

(
@.intOrderID int,
@.intDestinationID int=0 OUTPUT
)

AS

SET NOCOUNT ON

BEGIN

SELECT @.intDestinationID=lv.DestinationID
FROM dbo.[Land Voyages] AS lv INNER JOIN dbo.[Pickup Booking List] AS pbl
ON lv.LandVoyageID=pbl.LandVoyageID
WHERE pbl.OrderID = @.intOrderID

END
GO
'-----------

Thanks to Igor for suggestions.

Dani

how to retrieve only the duplicates in a table

There is a table with a single column with 75 rows - 50 unique / 25
duplicates. How would pull back a list of the rows that have/are
duplicates?

This is a question that I got in an interview. I didn't get it,
obviously...

Thanks,

TimSELECT col1, col2, col3, ...
FROM Sometable
GROUP BY col1, col2, col3, ...
HAVING COUNT(*)>1

--
David Portas
----
Please reply only to the newsgroup
--

"TimG" <timgru@.hotmail.com> wrote in message
news:744d8a29.0311072031.75846c64@.posting.google.c om...
> There is a table with a single column with 75 rows - 50 unique / 25
> duplicates. How would pull back a list of the rows that have/are
> duplicates?
> This is a question that I got in an interview. I didn't get it,
> obviously...
> Thanks,
> Tim