Can a Cognos Environment Variable be used to identify the database against which the report is being run so that some kind of information (such as "test", a watermark, or exclusion of signature) can be properly placed for identifying non-production information
Hi...
I'll explain what we do and you can determine if you want some additional info.
We use Oracle as our DB.
In our FM Model - we create a query against the DUAL table and create a bunch of columns that returns values for various Oracle functions.
We retrieve Oracle Meta Data via sys_context( namespace, parameter, [ length ] ) function
This is sample SQL statement you could use for a Query in FM.
Select
sys_context('userenv','current_schema') AS "current_schema",
sys_context('userenv','current_user') AS "current_user",
sys_context('userenv','current_userid') AS "current_userid",
sys_context('userenv','db_domain') AS "db_domain",
sys_context('userenv','db_name') AS "db_name",
sys_context('userenv','instance_name') AS "instance_name",
sys_context('userenv','server_host') AS "server_host",
sys_context('userenv','service_name') AS "service_name"
from DUAL
db_name and instance_name in our environments return the DB we are connected to
Ex
BIDEV
BITEST
BIUAT
BIPROD
As this is a query in your FM model...you could create a query is your report (or template) that would possibly display
the result of db_name in a singleton query in say... the FOOTER of the report?
is this sort of what you might be looking for?
I really like that idea, RichardP.
And welcome to Cognoise. ;D
Thanks ... this is a great idea. I will create and test it on our box... thanks and yes welcome to Cognoise !!!!
Thanks for the welcome...
But I should confess - the approach of creating a query that returns a series of DB function values is not my own.
I'm sharing a fellow colleagues' implementation that has become a standard in our FM models.
Cheers.
I have this working in an Oracle environment that I am working with, now I want to apply the same logic to a transact MSQL environment. Have you seen type of Query used there ?
Hi...
Sorry just familiar with Oracle.
But did a quick google lookup.
Would this work for SQL Server by creating a SQL Query in your FM model similar to this... ?
select
db_name() as [Current Database]
I tried a few system functions
original_db_name()
schema_name()
host_name()
but it looks like db_name is the one that is the best candidate to return the Environment DB Name.
I saw another post that suggested that if you wanted to use DUAL in SQL server you could fake it out by simply creating the table
CREATE TABLE DUAL
(
DUMMY VARCHAR(1)
)
GO
INSERT INTO DUAL (DUMMY)
VALUES ('X')
GO
You can get a list of all the SQL Server metadata functions here.
https://docs.microsoft.com/en-us/sql/t-sql/functions/metadata-functions-transact-sql (https://docs.microsoft.com/en-us/sql/t-sql/functions/metadata-functions-transact-sql)
You don't really need DUAL in SQL Server. For instance, "select db_name()" will work fine, no from clause is needed.
Agreed "select db_name()" works fine. Thanks for the help everyone.