DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
JPalatianos



Group memberships:
Premium Members

Joined: 21 Jun 2006
Posts: 305

Points: 3034

Post Posted: Wed May 17, 2017 4:41 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi,
We are migrating our 9.1 DataStage project from Windows to 11.5 running on Red Hat Linux.
We have one project that uses the server name in the queries as follows:
from #$Server#.#$PlanMartDB#.dbo.dly_finance_invst_info_dim
The server variable is defined in administrator as
[NJROS1BD0130\DEV].
This job run fine on our 9.1 Windows project but in our new environment it appears we lose the slash as we get the error
"main_program: [IBM(DataDirect OEM)][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Could not find server 'NJROS1BD0130DEV' in sys.servers. Verify that the correct server name was specified."

I created a test job and hardcoded the server in the sql as
from [NJROS1BD0130\DEV].#$PlanMartDB#.dbo.dly_finance_invst_info_dim and this runs fine

Any thought how I should code the admin Variable on our 11.5 Linux server?

Thanks - - John
JRodriguez



Group memberships:
Premium Members

Joined: 19 Nov 2005
Posts: 397
Location: New York City
Points: 4301

Post Posted: Wed May 17, 2017 4:56 pm Reply with quote    Back to top    

Hi JPalatiano,

Try escaping the special character like ServerName/\DEV that should.allow you to.pass the value as a literal

Hope it help![/b]

_________________
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
Rate this response:  
JPalatianos



Group memberships:
Premium Members

Joined: 21 Jun 2006
Posts: 305

Points: 3034

Post Posted: Wed May 17, 2017 5:50 pm Reply with quote    Back to top    

Hi Julio,
I tried the following in administrator:
[NJROS1BD0130/\DEV]
Now it gives me Could not find server 'NJROS1BD0130/DEV' in sys.servers.

A bit closer but we want it interpreted as 'NJROS1BD0130\DEV

Thanks - - John
Rate this response:  
JRodriguez



Group memberships:
Premium Members

Joined: 19 Nov 2005
Posts: 397
Location: New York City
Points: 4301

Post Posted: Thu May 18, 2017 5:58 am Reply with quote    Back to top    

Hi John,
Sorry, you should use ServerName\\NameInstance to get the desire result
By the way, normally you don't want to qualify the tables in your.SQL statements by adding the server and name instance where your SQL Server database is hosted. Just define the server name and port in your ODBC entry (.odbc.ini) and you should be able run the SQL statements

_________________
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
Rate this response:  
JPalatianos



Group memberships:
Premium Members

Joined: 21 Jun 2006
Posts: 305

Points: 3034

Post Posted: Thu May 18, 2017 6:31 am Reply with quote    Back to top    

Hi Julio,
Thank You!!
I could have sworn that I tried that up front but it appears I was never saving my changes when updating the administrative value. The [NJROS1BD0130\\DEV] ended up working for us.

FYI....
The reason this application has the server name in some of their jobs is they sometimes decide to query the table from the existing server (defined in the odbc.ini file) but sometimes they go after the same table in a Linked Server so they decided to parameterize that piece.
Thanks - - John
Rate this response:  
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours