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

return

whats wrong with this SP? I want @.id to contain the row identity of the newly created row as a return value.

ALTER PROCEDURE setCountry
(
@.name varchar( 50 ) = NULL,
@.alt varchar( 24 ) = NULL,
@.code varchar( 3 ) = NULL,
@.id int = null OUT
)
AS
SET NOCOUNT ON
INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @.name, @.alt, @.code )
@.id = @.@.identity
RETURN

INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @.name, @.alt, @.code )select@.id = @.@.identity

couple of things :
if you'd like to return this id back to asp.net you need to return it as an OUTPUT parameter..check out BOL for OUTPUT Parameters in stored procs..

also i'd recommend using SCOPE_IDENTITY() rather than @.@.IDENTITY. check out BOL again for the differences between them.

hth|||Thanks - but what is BOL?|||RETURN @.id

??

personally I'd do it this way

SET NOCOUNT ON
-- do insert
...
SELECT @.@.Identity|||BOL = Books On Line - best reference for sql server 2000. Free Download from microsoft.

hth|||Thanks! - I got the BOL acronym too - duh - Books On Line. I will try it now and actually may use SCOPE_IDENTITY() in place of @.@.identity.|||Atrax, I think the "return" method is better as it won't incur a result set. Although I'd use a OUTPUT param rather than return, I prefer to have that indicate some form of "state of the operation".|||Okay - now I can retrieve the result using ExecuteScalar - or DataReader or both?? Because when I run it in VS I dont see the results of the procedures. I mean it adds the row, but I don't see any output in the OUTPUT window.|||if you just need to return the ID you'd be better off using executescalar().

in vb.net

dim userID as integer
...
'open connection
...
userid=sqlcommand.ExecuteScalar()
...
'close connection

and use OUTPUT parameter to return the output form the stored proc...BOL had some samples no how to do it..

hth

Retuen 1 fild on SqlDataSource1.SelectParameters

ALTER PROCEDURE

dbo.Default_Edit1(

@.ID

int,

@.Family

Nvarchar(100)OutPut

)

AS

/* SET NOCOUNT ON */SELECT@.Family=FamilyFROMTable1where(ID=@.ID)

RETURN

SqlDataSource1.SelectParameters.Add(

"ID",Me.GridView1.DataKeys(CInt(e.CommandArgument)).Item(0))

SqlDataSource1.DeleteCommandType = SqlDataSourceCommandType.StoredProcedure

SqlDataSource1.SelectCommand =

"dbo.Default_Edit1"

SqlDataSource1.SelectParameters(

"Family").Direction = Data.ParameterDirection.Output

Label3.Text = SqlDataSource1.SelectParameters(

"Family").Direction.ToString()

it's have error

I went to return 1 fild

??

Remember that parameters names should be the same in SQL and .Net so use @.ID and @.Famile in both places.

Also do not forget to execute your command before you read output value. Are you sure that you will run your SP as delete command?

SqlDataSource1.SelectParameters.Add("@.ID",Me.GridView1.DataKeys(CInt(e.CommandArgument)).Item(0))

SqlDataSource1.SelectParameters(

"@.Family").Direction = Data.ParameterDirection.Output

Label3.Text = SqlDataSource1.SelectParameters("@.Family").Direction.ToString()

thanks

|||

Its'not Work!

Retrieve package varialbe from SQL configuration table?

Hi,

I have been trying to get a variable in my package to retrieve its data from the SQL configuration table.. so that in future i am able to alter them all easily.

On the SSIS > Package Configurations menu, there seems to only be the option to pass a variable FROM the package TO SQL

whereas what i want to do is infact the opposite...

any help would be much appreciated

regards

Chris

You were on the right track, you just second-guessed yourself.

On SSIS > Package Configurations menu set the following...

-Configuration type: 'SQL Server'

-Connection: 'Name of you SQL Server connection manager'

-Configuration table: 'Name of your SQL Server configuration table'

-Configuration filter: 'The name of the field that identifies the variable(s) you want to bring in to ssis'

This will not pass variables from SSIS to SQL Server but from the way you want it to, SQL Server to SSIS

|||Hi,

cheers for that, it turns out that it was wokring fine all the time!

I was expecting the variables to get updated when the package ran, but instead they seemt o get updated when the package is opened..

very odd

how does this affect the variables when the packages are run from outside of SSIS, i.e. scheduled in SQL Server?

Thanks!
|||

However the package is executed, it will always pull from that SQL Server config table to get the most current information.

So whether you execute the package in BIDS, or from a command line, or from SQL Server Agent, each will poll the table for current information each time. As you've found out, it also polls the table every time you open that package.

|||

Configurations are checked/updated on many occasions:

-Upon first opening up the package.

-In BIDS when you right-click on a package in the solution explorer and click 'reload with upgrade'.

-Run-time. Regardless of how it is executed (sql server agent, dtexec,BIDS).