Skip to main content
Solved

Value for Project variable


Forum|alt.badge.img
  • Problem Solver
  • 116 replies

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 [vDatabase] anywhere.

How do i know which values it has?

I cant see it in the pane below either

 

Best answer by rory.smith

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.

View original
Did this topic help you find an answer to your question?

4 replies

rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • TimeXtender Xpert
  • 686 replies
  • June 18, 2024

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.


Forum|alt.badge.img
  • Author
  • Problem Solver
  • 116 replies
  • June 20, 2024

@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!'

 


rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • TimeXtender Xpert
  • 686 replies
  • Answer
  • June 21, 2024

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.


Forum|alt.badge.img
  • Author
  • Problem Solver
  • 116 replies
  • June 21, 2024

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings