Skip to main content

The people before me defined certain variables in TX. One of these is [vDatabase] which is futher used in a stored procedure in ODX:

Select
.
.
.
.

FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_CATALOG = '[vDatabase]'
AND RIGHT(TABLE_NAME,2) NOT IN ('_L','_M','_T', '_R', '_B')
AND TABLE_SCHEMA NOT IN ('dbo','view', 'XLS')
AND COLUMN_NAME IN ('modifyDate','last_update','Registrert_Dato')
END

 

The variable is also used in SQL snippets in ODX, DSA and MDW:

DECLARE @sql NVARCHAR(MAX)
SELECT @sql =
COALESCE(@sql + ' UNION ALL', '') +
.
.
.
.
.
.
FROM %s'+ CHAR(10)
,
TABLE_SCHEMA,
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
TABLE_SCHEMA + '.' + TABLE_NAME,
COLUMN_NAME,
COLUMN_NAME,
COLUMN_NAME,
COLUMN_NAME,
COLUMN_NAME,
COLUMN_NAME,
TABLE_SCHEMA + '.' + ' ' + TABLE_NAME + ']'
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_CATALOG = ' vDatabase]'
AND RIGHT(TABLE_NAME,2) NOT IN ('_L','_M','_T', '_B')
AND TABLE_SCHEMA NOT IN ('dbo','view', 'XLS')

EXEC(@sql)

However when i execute the table there is no data.

I am not able to find the value for rvDatabase] anywhere.

How do i know which values it has?

I cant see it in the pane below either

 

Hi,

 

You can view the value in the place where the variable is used: choose Show Translation in a Custom SQL window and TimeXtender will be able to resolve the value in the script.


@rory.smith 
Thank you for the answer.

This worked fine in a stored procedure but not in Table insert

In table insert i got an error

TABLE_CATALOG = 'ScopeVariableError!'

 


Hi,

from your screenshot I take it this is a Business Unit and a Custom Table inside? In 6xxx running against ODX Server in a DWH Data Area this works fine for me.

I assume your syntax is something like: TABLE_CATALOG = ''vDatabase]’ in the syntax editor? Could it be that a change to the variable has been made and this requires the table to be deployed? It might be quicker to resolve this through a support ticket giving all the software versions involved.

As a general note: I would avoid Custom Table Inserts and use Custom Views and regular Table Inserts as this improves lineage.


Yes, this is in Business Unit. 

I am on TX Legacy: 20.10.45.64

I have redeployed the table a couple of times without resolving the issue.

No need for a ticket, i have solved the issue with a workaround by writing the name of the catalogue

TABLE_CATALOG = 'my.timextenderDB.com'	

 


Reply