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
Database name included in SQL in ODBC stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 224
- Joined: Tue Sep 24, 2002 7:32 am
- Location: Denver, CO USA
-
- Premium Member
- Posts: 108
- Joined: Sat Feb 05, 2005 6:52 pm
- Location: US
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.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.
Thanks
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers