Skip to main content
To homepage To homepage

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


Terms and ConditionsCookie settings