DSXchange

I imported the table definition for a SQL Server table. I use this table in an ODBC stage. When I look at the SQL, the columns are fully qualified and the SQL looks like this:
select "dbname.dbo.column1","dbname.dbo.column2" from
"dbname.dbo.table_name"

My database names are different in my dev, test, and prod environments. This looks like it will cause problems when I migrate.

What is my best approach to getting the SQL Server database name out of the SQL.

Thanx,

Rod

You can edit the Table Definition after it is imported.

John

Yes, I realize I can edit it but that's an additional step I'd like to avoid.

hi rdsarbo,
i had the same problem for which i used user defined environment variables as parameters and this works good .

thanks,
abhi.

Use plugins (such as OCI or OLE/DB) to avoid this problem.

diamondabhi wrote:hi rdsarbo,
i had the same problem for which i used user defined environment variables as parameters and this works good .

thanks,
abhi.

Anhi - Can you elaborate on the variables and what you did? We are facing the same problem. When importing table defs via ODBC, the entries are fully qualified and editing the entries takes too long.

Thanks

I used to have this problem. I used to delete the derivation column in the columns tab, of the DRS stage/ODBC stage.

Two things can help here:

1) Don't use the 'fully qualified' option when you import the metadata, if available.

2) Don't use the 'Load' option to bring the metadata into the stage.

I realize #1 isn't possible with ODBC but some stages do offer that option. However, you should find that while 'Load' will bring in everything into the Derivation columns in a stage, dragging the metadata from the Repository Browser onto the connecting Link will not. That is the simplest way to solve this problem and keep your generated sql from being 'fully qualified'. Or go back to removing it from every column post load.