Database name included in SQL in ODBC stage

rdsarbo · Post by **rdsarbo** » Mon Apr 25, 2005 4:02 pm

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

ds_developer · Post by **ds_developer** » Mon Apr 25, 2005 4:47 pm

You can edit the Table Definition after it is imported.

John

rdsarbo · Post by **rdsarbo** » Tue Apr 26, 2005 6:39 am

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

diamondabhi · Post by **diamondabhi** » Tue Apr 26, 2005 7:41 am

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

thanks,
abhi.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Apr 26, 2005 7:44 am

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

cnguyen · Post by **cnguyen** » Mon Feb 05, 2007 12:18 am

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

DSguru2B · Post by **DSguru2B** » Mon Feb 05, 2007 12:26 am

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

chulett · Post by **chulett** » Mon Feb 05, 2007 7:31 am

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.