Question on a query 1

[kodaksmile]

I have 2 tables - one contains application information that has been pulled in from the extended properties and looks like:

Application information Table:

DBName Application Description
test1 MS_Application Test Application
test1 MS_Description Used for testing
round2 MS_Application Round2 Application
round2 MS_Description used for round2 test

There will be 2 records for each database as above - all fields are varchar.

The second table contains the space information collected nightly for each db and has this structure:
CREATE TABLE [dbo].[dbaSpace](
[LNm] [char](40) NULL,
[Tot] [numeric](10, 4) NULL,
[Used] [numeric](10, 4) NULL,
[Prcnt] [numeric](10, 4) NULL,
[DB] [char](40) NULL,
[Typ] [char](5) NULL,
[ServerName] [varchar](50) NULL,
[EntryDt] [varchar](10) NULL
) ON [PRIMARY]

What I am trying to do is write a query that will pull the data from the description field into 2 different fields based on what is contained in the application column of table 1. (basically I need to see Test Application as the application name and Used for testing as the description)

Desired results:

Servername, dbname, application name, application description, totalAvail(MB), TotalUsed(MB), FreeSpace(MB), Date

I can easily get all but the application name and description. The problem comes in because the real application name and the application description are both located in the description field of the application information table.

I tried:

select ServerName,db as DatabaseName,
(select description from applicationinformation where Application='MS_Application') as Application,
(select description from applicationinformation where application='MS_Description') as description,
sum(tot) as 'TotalAvail(MB)', sum(used) as 'TotalUsed(MB)', sum(tot)-sum(used) as 'FreeSpace(MB)',
entrydt as Date from dbaspace, ApplicationInformation where dbaspace.db in (select distinct applicationinformation.dbname
from applicationinformation)
and entrydt='04/01/2010'
group by ServerName,db, entrydt, application , description
order by entrydt

but it errors out becuase there are multiple rows returned.

any suggestions?

 

[IanWaterman]

YOur code is not workable SQL.

What is relationship between tables applicationinformation and dbaspace. There must be two fields which link these tables.

Ian

 

[kodaksmile]

Is there a way to take the data from sys.extended_properties which has 2 key columns looks like this for each db:
name value
MS_Application DBA Tools
MS_Description Database used by dba

and pull that information into a table in with the following format:

Database Application Description
Test DBA Tools Database used by dba

I am using the following code to populate the application information table but, it is making it unworkable going forward as I need to link this information to space information and can only have 1 row per database. This code creates 2 rows per database.

EXEC sp_MsForEachDB
'insert into BSC_DBA..ApplicationInformation
select ''?'',name, convert(varchar(100),value)
from [?].sys.extended_properties where name in (''MS_Application'',''MS_Description'')'

 

[RobertC22]

Does this give you what you desire?

select ServerName
,db as DatabaseName,
max ( (select description from applicationinformation a1 where a1.dbname = dbs.db and a1.Application='MS_Application') ) as Application,
max ( (select description from applicationinformation a1 where a1.dbname = dbs.db and a1.application='MS_Description') ) as description,
sum(tot) as 'TotalAvail(MB)', sum(used) as 'TotalUsed(MB)', sum(tot)-sum(used) as 'FreeSpace(MB)',
entrydt as Date 
from dbaspace dbs
where dbs.db in (select distinct applicationinformation.dbname from applicationinformation a2)
and entrydt='04/01/2010'
group by ServerName, db, entrydt
order by entrydt

Alternatively, you could capture your extended properties as a single tuple per database in the ApplicationInformation table.
However you will need to amend the table definition.

EXEC sp_MsForEachDB
'insert into BSC_DBA..ApplicationInformation ( dbname, MSApp, MSDesc )
select ''?''
, max ( case when name = ''MS_Application'' then convert(varchar(100),value) else null end )
, max ( case when name = ''MS_Description'' then convert(varchar(100),value) else null end )
from [?].sys.extended_properties where name in (''MS_Application'',''MS_Description'')
group by ''?'' '

------
Robert

 

[kodaksmile]

Thanks Robert - I went with modifying the data captured as that seemed to simplify the issue. Once I removed the group by it worked well - with the group by it returns an error:
ach GROUP BY expression must contain at least one column that is not an outer reference.
Msg 164, Level 15, State 1, Line 1