Client Management Suite

I'm in a new role involving generating reports from Altiris using SQL, and am trying to learn the database structure.

I've found some queries that should generate a list of tables, etc., and made sure I typed them in without errors (copy & paste is not an option here ...it's a long story)

If I go to Reports - (folder) - New SQL Report, use the Edit button and input my script, I always get the result

"This datasource is not in a runnable state."

I found a reference on one of the forums to be sure I'm using the right database, so I start the query with use Symantec_CMDB, and still get the same result.

Even if that "use" statement is the only line, it fails with that error. Evidently, something very fundamental is the issue, but can anyone point out what?

Many thanks.

Jim

Hi Perkinsj,

In the SMP, go to Settings > Notification Server > Database Settings:

There you'll find your SQL Server and Database Name.

In the raw SQL reports the error "This datasource is not in a runnable state" means you SQL select statement is not correct. Can you please inform your SQL query?

Regards. 

Here's the script I found on https://support.symantec.com/en_US/article.HOWTO1107.html

select
[table_name] as  [Table Name],
[column_name] as [Column Name],
case [data_type]
  when 'varchar' then [data_type] + '(' + cast([character_maximum_length]  as varchar) + ')'
  when 'nvarchar' then [data_type] + '(' + cast([character_maximum_length]  as nvarchar) + ')'
  else [data_type]
end as [Data Type],
case [is_nullable]
  when 'No' then 'No'
  else 'Yes'
end as [Nullable],
isnull([column_default], '') as [Default Value],
isnull(cast([numeric_precision] as nvarchar),'') as [Precision],
isnull(cast([numeric_precision_radix] as nvarchar),'') as [Precision Radix],
isnull([collation_name],'') as [Collation Name]
from information_schema.[columns]
where [table_catalog]   like '%'
    and [table_schema]  like 'dbo'
    and [table_name]     like '%'
    and [column_name]  like '%'
    and [data_type]        like '%'
order by [table_name], [ordinal_position]

I checked the server and database names, and they're correct.

Assuming the script is correct, I wondered if my role doesn't have the right permissions, but I was able to confirm that it does.

Hi Perkinsj

In my console, this script worked perfectly. As long as the app credential is DB owner, I think this would not be an issue. I think, maybe, there is a single dot there that is making this script not runnable.

If you are wondering how to make a new report using SQL, you can edit all the default reports for an idea. But like I always say to new Altiris guys, most of the time you are going to use the machine Guid on table joins, there is not mystery there.

Regards.