Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to check
for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.comTry using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html#dynamic-sql
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:
> I'm trying to apply the following code into a proc I have and wanted to check
> for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
> then tried placing a return status code variable after the execute stmt and
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com

Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to chec
k
for the success of the alter database stmt. @.@.error didn't trip to <> 0 an
d
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.comTry using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-hand...tml#dynamic-sql
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:

> I'm trying to apply the following code into a proc I have and wanted to ch
eck
> for the success of the alter database stmt. @.@.error didn't trip to <> 0
and
> then tried placing a return status code variable after the execute stmt an
d
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com

Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to check
for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.com
Try using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handl...ml#dynamic-sql
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:

> I'm trying to apply the following code into a proc I have and wanted to check
> for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
> then tried placing a return status code variable after the execute stmt and
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com
sql

Retrieve Last Record SQL Stmt

Is there a way to get the last record from the duplicate records. To give an
example, I have table that has records as follows:
BranchPO
branch PO POLine Amount POsuf
555 147586 10 399.00 2
555 147586 10 .00 1
555 147586 10 27.00 0
I can't use POSuf in my criteria. branch, PO and POline are the only fields
that I can set my query. These being duplicate records, how do I get the
last record which is
branch PO POLine Amount POsuf
555 147586 10 399.00 2
If I use Progress GL language, I can use the following stmt to get the last
record.
Find Last BranchPO where branch = 555 and po = 147586 and poline = 10
no-error.
My result will be the last created record
branch PO POLine Amount
555 147586 10 399.00
Please help.
Thank you so much
Does this table have a key? In SQL tables are unordered and there is no
reliable way to retrieve rows in the order in which they were inserted
unless you persist that information as data in the table. If the last
inserted row is important to you then your best bet is to recreate the data
or to add a key in the correct order and then use that to drive your query.
If the insertion order is not so important and you just need to remove
duplicates then you can GROUP BY branch, po, poline.
David Portas
SQL Server MVP
|||Thank you so very much for your suggestion.
Insertion order is not important b'cos I'm dumping the data into SQL server
from the ERP system.
I will try by Removing the duplicates with GROUP BY branch, po, poline. I
feel positive that this works.
Thank you
"David Portas" wrote:

> Does this table have a key? In SQL tables are unordered and there is no
> reliable way to retrieve rows in the order in which they were inserted
> unless you persist that information as data in the table. If the last
> inserted row is important to you then your best bet is to recreate the data
> or to add a key in the correct order and then use that to drive your query.
> If the insertion order is not so important and you just need to remove
> duplicates then you can GROUP BY branch, po, poline.
> --
> David Portas
> SQL Server MVP
> --
>
>

Retrieve Last Record SQL Stmt

Is there a way to get the last record from the duplicate records. To give a
n
example, I have table that has records as follows:
BranchPO
branch PO POLine Amount POsuf
555 147586 10 399.00 2
555 147586 10 .00 1
555 147586 10 27.00 0
I can't use POSuf in my criteria. branch, PO and POline are the only fields
that I can set my query. These being duplicate records, how do I get the
last record which is
branch PO POLine Amount POsuf
555 147586 10 399.00 2
If I use Progress GL language, I can use the following stmt to get the last
record.
Find Last BranchPO where branch = 555 and po = 147586 and poline = 10
no-error.
My result will be the last created record
branch PO POLine Amount
555 147586 10 399.00
Please help.
Thank you so muchDoes this table have a key? In SQL tables are unordered and there is no
reliable way to retrieve rows in the order in which they were inserted
unless you persist that information as data in the table. If the last
inserted row is important to you then your best bet is to recreate the data
or to add a key in the correct order and then use that to drive your query.
If the insertion order is not so important and you just need to remove
duplicates then you can GROUP BY branch, po, poline.
David Portas
SQL Server MVP
--|||Thank you so very much for your suggestion.
Insertion order is not important b'cos I'm dumping the data into SQL server
from the ERP system.
I will try by Removing the duplicates with GROUP BY branch, po, poline. I
feel positive that this works.
Thank you
"David Portas" wrote:

> Does this table have a key? In SQL tables are unordered and there is no
> reliable way to retrieve rows in the order in which they were inserted
> unless you persist that information as data in the table. If the last
> inserted row is important to you then your best bet is to recreate the dat
a
> or to add a key in the correct order and then use that to drive your query
.
> If the insertion order is not so important and you just need to remove
> duplicates then you can GROUP BY branch, po, poline.
> --
> David Portas
> SQL Server MVP
> --
>
>

Retrieve Last Record SQL Stmt

Is there a way to get the last record from the duplicate records. To give an
example, I have table that has records as follows:
BranchPO
branch PO POLine Amount POsuf
555 147586 10 399.00 2
555 147586 10 .00 1
555 147586 10 27.00 0
I can't use POSuf in my criteria. branch, PO and POline are the only fields
that I can set my query. These being duplicate records, how do I get the
last record which is
branch PO POLine Amount POsuf
555 147586 10 399.00 2
If I use Progress GL language, I can use the following stmt to get the last
record.
Find Last BranchPO where branch = 555 and po = 147586 and poline = 10
no-error.
My result will be the last created record
branch PO POLine Amount
555 147586 10 399.00
Please help.
Thank you so muchDoes this table have a key? In SQL tables are unordered and there is no
reliable way to retrieve rows in the order in which they were inserted
unless you persist that information as data in the table. If the last
inserted row is important to you then your best bet is to recreate the data
or to add a key in the correct order and then use that to drive your query.
If the insertion order is not so important and you just need to remove
duplicates then you can GROUP BY branch, po, poline.
--
David Portas
SQL Server MVP
--|||Thank you so very much for your suggestion.
Insertion order is not important b'cos I'm dumping the data into SQL server
from the ERP system.
I will try by Removing the duplicates with GROUP BY branch, po, poline. I
feel positive that this works.
Thank you
"David Portas" wrote:
> Does this table have a key? In SQL tables are unordered and there is no
> reliable way to retrieve rows in the order in which they were inserted
> unless you persist that information as data in the table. If the last
> inserted row is important to you then your best bet is to recreate the data
> or to add a key in the correct order and then use that to drive your query.
> If the insertion order is not so important and you just need to remove
> duplicates then you can GROUP BY branch, po, poline.
> --
> David Portas
> SQL Server MVP
> --
>
>