Skip to main content
Solved

Value for Project variable


Forum|alt.badge.img

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?

rory.smith
TimeXtender Xpert
Forum|alt.badge.img+7
  • TimeXtender Xpert
  • 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
  • Problem Solver
  • 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
  • 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
  • Problem Solver
  • 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