Offline|Online database

Hi,
Is there any method to get an historical log where appears the database status (Online|offline) ?
Regards.Not really. You can check the SQL Server errorlog, which will tell you at least when a database is brought online.

Odd Sql Problem

If I run this sql statement:

SELECT order_numb, stat = case
when status = 'N/S' then 'zN/S'
when status = 'ready' then '0Ready'
else cast('a' + status as varchar(13))
end
FROM rptCS_Sched

I get a result like this:

04017.0023 zN/S
04023.0559 0Ready
04127.0311 aJan 21 2005
etc

So far so good...

Now I want to remove the left hand letter in that status column,
and I try nesting the query, like this:

select order_numb, right(max(stat),len(max(stat))-1) as Status from (
SELECT order_numb, stat = case
when status = 'N/S' then 'zN/S'
when status = 'ready' then '0Ready'
else cast('a' + status as varchar(13))
end
FROM rptCS_Sched
) s
group by order_numb

Now I get:
04017.0023 N/S
04023.0559 Ready
04127.0311 an 21 2005

For some reason, it removes 2 characters when there is a date contained in the string. Any body have any ideas?Post the Table DDL|||CREATE TABLE [dbo].[rptCS_Sched] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Order_Numb] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[product] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tons] [real] NOT NULL ,
[status] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[bill_to_numb] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cust_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[request_date] [smalldatetime] NULL ,
[LoadNum] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ItemStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchedID] [bigint] NULL ,
[Sched_Ord_Status] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO|||Not for me...the whole thing doesn't make sense though...

Do you have a real world example?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE [dbo].[myrptCS_Sched] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Order_Numb] [varchar] (10) ,
[status] [varchar] (13)
)
GO

INSERT INTO myrptCS_Sched (Order_Numb, status)
SELECT '04017.0023', 'N/S' UNION ALL
SELECT '04023.0559', 'Ready' UNION ALL
SELECT '04127.0311', 'Jan 21 2005'
GO

SELECT order_numb
, RIGHT(MAX(stat),LEN(MAX(stat))-1) as Status
FROM (
SELECT order_numb
, stat = CASE WHEN status = 'N/S' THEN 'zN/S'
WHEN status = 'ready' THEN '0Ready'
ELSE cast('a' + status as varchar(13))
END
FROM myrptCS_Sched
) s
GROUP BY order_numb
GO

SET NOCOUNT OFF
DROP TABLE myrptCS_Sched
GO|||The table is a base recordset for a report. It gets truncated and repopulated hourly. The table shows the production status of each item on an order, so an order number can occur multiple times on the report (once for each item).
For each item, there is one of three statuses(?):
N/S = Not Scheduled
Ready = Product is already in inventory
Date = The date that production is scheduled for the item.

Now I've been asked to show an order summary on the report, and let the users drill into the detail only if they need to. I decided to add a column to the table (Sched_Ord_Status), and go back and populate that column after the rest of the table has been populated. I need it to show the "worst case" status for the order. N/S being worst, and Ready being best. If everything on the order is Ready, except for one item, which is N/S, then the order status is N/S.|||If it helps at all, the initial sql I posted is part of this statement:

update rptCs_Sched set sched_ord_status = d.Status
from rptcs_sched inner join (
select order_numb, right(max(stat),len(max(stat))-1) as Status from (
SELECT order_numb, stat = case
when status = 'N/S' then 'zN/S'
when status = 'ready' then '0Ready'
else cast('a' + status as varchar(13))
end
FROM rptCS_Sched
) s
group by order_numb) d on rptcs_sched.order_numb = d.order_numb|||Well did you cut and paste my code to see that the sympton you describe is not happening with the code I posted?

Noe how about the DDL for the other table and some DML to populate it with some sample data like I did...

Odd scheduled job behavior

I've scheduled a job to run on a certain schedule, but the Last Run Status date comes back very oddly, a couple years out of synch, the other jobs scheduled report back just fine.
Anyone seen this behavior?
Edward R Hunter, Data Application Designer
comScore Networks, Inc....haven't seen that one, but you could always check the sysjobhistory table in msdb (if you have the rights) to see what the rundate column has stored.

What version of SQL Server are you on?