November 29, 2012 at 7:55 am
Hi all,
Trying to query a table that has sys.configuration data thrown in it to see if anything has been changed.
I have a table of:
CREATE TABLE [tempserverConfig](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [nvarchar](255) NOT NULL,
[Instance] [nvarchar](255) NULL,
[Name] [nvarchar](255) NOT NULL,
[Value] [int] NOT NULL,
[ValueInUse] [int] NOT NULL,
[Date] [date] NOT NULL,
GO
The idea would be to compare the most recent date entry with the last date entry and compare the results and list out any changes.
I think i have done it, but its lots of selects in wheres (seem to remember that was a no no..) and the way i get the second to last date isnt very good.. Could anyone suggest ways to improve it.
Query
select Name, value, ValueinUse from tempserverconfig
where date =
(
select top (1) date from tempserverconfig
where date IN (
select
top (2) [date] from tempServerconfig
group by date order by date desc
) order by date asc
)
EXCEPT
select Name, value, ValueinUse from tempserverconfig
where date in (select max(date) from tempServerconfig )
Thanks for any advice..
November 29, 2012 at 7:58 am
Could anyone suggest ways to improve it.
May be.
You have done a great job by specifying table DDL and query you have!
Adding some sample data (as insert script) and expected results (based on your sample data), would make more people to understand of what you have and what you exactly want.
November 29, 2012 at 8:09 am
One idea:
;with cteConfig as (
select *,
Number = dense_rank() over ( order by [Date] )
from tempserverConfig
)
select *
from cteConfig c inner join
cteConfig prev on
prev.Number + 1 = c.Number and
prev.ServerName = c.ServerName and
prev.Instance = c.Instance and
prev.Name = c.Name and
prev.Value = c.Value
If the table is kept small performance will not be a problem.
November 29, 2012 at 8:34 am
Hi and thanks for the quick replies!
Eugene Elutin
Ill write a script for putting data in there. Thanks I should have done that when i orginally posted :/
weberharter
Thank you for the suggestion.. The table will have 60 odd instances of SQL sys.config table put into it so 60x68? per month.. Im never sure what size is big for a table.. im guessing it wouldnt be considered a big table?
Thank you again for the help!
November 29, 2012 at 8:50 am
Bit better build and some data!
TABLE for data
create table dbo.tempserverconfig (
id int identity (1,1)constraint PK_tempconfig_id primary key clustered,
servername sql_variant,
instance sql_variant,
[name]sql_variant,
value sql_variant,
value_in_use sql_variant,
[date] date
)
Insert data
insert into dbo.tempserverconfig (servername, instance, date, name, value, value_in_use)
select
SERVERPROPERTY('ServerName') ,
SERVERPROPERTY('InstanceName') ,
'2012-11-27',
name,
value,
value_in_use
from master.sys.configurations
GO
insert into dbo.tempserverconfig (servername, instance, date, name, value, value_in_use)
select
SERVERPROPERTY('ServerName') ,
SERVERPROPERTY('InstanceName') ,
'2012-11-28',
name,
value,
value_in_use
from master.sys.configurations
GO
insert into dbo.tempserverconfig (servername, instance, date, name, value, value_in_use)
select
SERVERPROPERTY('ServerName') ,
SERVERPROPERTY('InstanceName') ,
'2012-11-29',
name,
value,
value_in_use
from master.sys.configurations
GO
Compare the last 2 dates for changes
select Name, value, Value_in_Use from tempserverconfig
where date =
(
select top (1) date from tempserverconfig
where date IN (
select
top (2) [date] from tempServerconfig
group by date order by date desc
) order by date asc
)
EXCEPT
select Name, value, Value_in_Use from tempserverconfig
where date in (select max(date) from tempServerconfig )
NOTE i remade the tables and changed some data types and col names as im getting the data from a SSIS script.
November 29, 2012 at 8:57 am
This might work, although it didn't return any rows on my server:
WITH Partitioned AS (-- Partition into separate items
SELECT
ServerName
,Instance
,Name
,Value
,ROW_NUMBER() OVER (PARTITION BY ServerName, Instance, Name ORDER BY Date DESC) RowNo
FROM
tempserverConfig
)
,MostRecent AS (-- Get the two most recent events for each item
SELECT
ServerName
,Instance
,Name
,Value
FROM
Partitioned
WHERE
RowNo <= 2
)
SELECT
ServerName
,Instance
,Name
FROM
MostRecent
GROUP BY
ServerName
,Instance
,Name
HAVING
MIN(Value) < MAX(Value)
Edit: changed the greater than to a less than. Still doesn't return any rows, though.
John
November 29, 2012 at 9:10 am
decided to help myself out and change some data between the inserts so hopefully! it should show something.. as nothing will have changed otherwise and a blank screen is maybe not to proof of concept 😀
I just turn off show advanced options then insert
turn it on and insert
turn it off and insert.
so hopefully a compare will show advanced option settings changing!
EXEC sp_configure 'show advanced option', '0';
go
reconfigure
go
insert into dbo.tempserverconfig (servername, instance, date, name, value, value_in_use)
select
SERVERPROPERTY('ServerName') ,
SERVERPROPERTY('InstanceName') ,
'2012-11-27',
name,
value,
value_in_use
from master.sys.configurations
GO
EXEC sp_configure 'show advanced option', '1';
go
reconfigure
go
insert into dbo.tempserverconfig (servername, instance, date, name, value, value_in_use)
select
SERVERPROPERTY('ServerName') ,
SERVERPROPERTY('InstanceName') ,
'2012-11-28',
name,
value,
value_in_use
from master.sys.configurations
GO
EXEC sp_configure 'show advanced option', '0';
go
reconfigure
go
insert into dbo.tempserverconfig (servername, instance, date, name, value, value_in_use)
select
SERVERPROPERTY('ServerName') ,
SERVERPROPERTY('InstanceName') ,
'2012-11-29',
name,
value,
value_in_use
from master.sys.configurations
GO
November 29, 2012 at 9:12 am
Thanks John!
that's showing the change to advanced settings for me
so many ways to do things.. All look a lot cleaner and nicer than mine!
Thank you 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply