How to get the status?

I have a table with columns c1, c2, c3, c4.

if all nulls or blanks. Status = 0
if c1 assigned but no c2, c3, and c4, then status = 1
if c2 assigned but no c3 and c4, then status = 2
if c3 .. then ..
if c4 .. then ..

I want to have one SQL to get the status like (ignored checking for
blanks here for demo)

SELECT Status = (if not c4 is null then 4
else not c3 is null then 3
else not c2 is null then 2
else not c1 is null then 1
else 0)
FROM mytable.

Thought of using CASE ... WHEN ... but it is only on one colum.

Any better idea.

Thanks

JohnI'm not sure why you believe that CASE can only reference a single
column (admittedly, Books Online shows only single-column examples):

select case
when coalesce(c1,c2,c3,c4) is null then 0
when c1 is not null and c2 is null and c3 is null and c4 is null then
1
when c1 is null and c2 is not null and c3 is null and c4 is null then
2
/* Add other combinations here */
else null end as 'Status'
from
(select null as 'c1', null as 'c2', null as 'c3', null as 'c4'
union all
select 1, null, null, null
union all
select null, 2, null, null) dt

Simonsql

how to get the status of update sucessing?

Dear Experts,

I have a stored procudure which can be successful called from application and update against tables, the question is how do I get the status which can indicate how many records were updated or no any record was updated in the action?

Thanks in advance.

Hi,

@.@.ERROR returns the error number for the last T-SQL statement executed while
@.@.ROWCOUNT returns the number of rows affected by the last SQL statement.

how to get the status of a sql job

Hi Gurus,
can any one help me to get the job status of a sql job.
My requirement is as under
ordinary user (not SA) will query pass job name as argument to stored procedure and sp has to give me the job status.
Thanks in advanceYou can use SP_HELP_JOB and a user who is not a member of the sysadmin fixed role can use sp_help_job to view only the jobs he/she owns.

Execute permissions default to the public role in the msdb database.

How to get status of sp_executesql

Hello

The basic requirement is to import data from excel sheet and save it to a table. The excel filename and sheet name is passed in as a parameter to a stored procedure. I am using sp_executesql to execute the statement.

First I delete all the records from the table and then copy the data from excel into the table. I want to do this as a transaction so that if there is any error in the import process, I want to rollback the transaction otherwise commit it. sp_executesql returns 0 for success or 1 for failure.

In my case, even if there is any syntax error, I get the status of 0. Also the @.@.ROWCOUNT contains some positive number greater than 0 even if some error occurs.

BEGIN TRANSACTION T1

GO

DELETE FROM SORTGROUP

GO

DECLARE @.Statement as NVARCHAR(1024)

DECLARE @.TableName as VARCHAR(32)

DECLARE @.ExcelFile AS VARCHAR(512)

DECLARE @.ParamNameList as VARCHAR(1024)

DECLARE @.SheetName as VARCHAR(64)

DECLARE @.Status as INT

DECLARE @.ErrorCode as INT

DECLARE @.RowCount AS INT

SET @.TableName = 'SORTGROUP'

SET @.ExcelFile = 'E:\PROJECTS\RALEIGH\TEST.XLS'

SET @.ParamNameList = '*'

SET @.SheetName = 'SORTGROUPDATA'

EXEC master..xp_sprintf @.Statement OUTPUT, 'INSERT %s SELECT %s FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 5.0;DATABASE=%s'', ''SELECT %s FROM [%s$]'')', @.TableName, @.ParamNameList, @.ExcelFile, @.ParamNameList, @.SheetName

SET @.Status = -1

exec @.Status=sp_executesql @.Statement

SELECT @.ErrorCode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT

IF @.Status = -1

ROLLBACK TRANSACTION T1

ELSE COMMIT TRANSACTION T1

PRINT @.ErrorCode

PRINT @.RowCount

PRINT @.Status

Is there any reason why you're NOT using DTS to do this? The work flow management will handle all of that for you

How to get status for every individual Task in a package

Hi,

I have a package in which there are four tasks. I have to get the status of those four tasks and finally send a mail. I am unable to get the status for individual task.

Has anyone tried this? Please help.

Thanks in advance.

Hi,

Create 'OnError' and 'OnPostExecute' event handlers for each task. In the 'OnError' event, set the error message to a variable. In the 'OnPostExecute' event, check the variable. If is it empty set the status as 'Success' or else set it as 'Failure' with the error message. In that event itself, u write the status to a file in append mode (first task should open the file in overwrite mode).

Hope it would help.

How to get Recovery Model of a SQL 2000 Database?

Hi ,

Can somebody help with an SQL Statement to list the Recovery models for
all the Databases in a server. I am trying to use the status column from sysdatabases..but i am not able to get the right statement.

Any help/references asap...

Thanks in advance.

Sasi.For SQL 2000

select name, databasepropertyex(name, 'Recovery') as RecoveryModel from master.dbo.sysdatabases order by name

For SQL 2005...

select name, recovery_model, recovery_model_desc from master.sys.databases

Good luck with it...

how to get online/offline status

Hi,

i try get information about the online or offline status of a database
within ms-sql srv 2000.

I can look it up within server manager, but I'm looking for a way to get
this information from the command line or a select statement.

Is there a command or a sql statement that tells me the status of a
database ? There's some information in master.dbo.sysdatabases, but it
looks very cryptic.

Any help appreciated, thanks,
stefan"Stefan Behrens" <stefan.news@.gmx.net> wrote in message
news:Xns94DB71E9836ADstefannewsgmxnet@.195.20.224.1 16...
> Hi,
> i try get information about the online or offline status of a database
> within ms-sql srv 2000.
> I can look it up within server manager, but I'm looking for a way to get
> this information from the command line or a select statement.
> Is there a command or a sql statement that tells me the status of a
> database ? There's some information in master.dbo.sysdatabases, but it
> looks very cryptic.
> Any help appreciated, thanks,
> stefan

select databasepropertyex('master', 'status')

Simon