My managed ODP.net webapp works locally but when deploying it to a server, it fails with the error:
"TNS:listener does not currently know of service requested in connect descriptor"
From looking around, it seems like this is because it can't get to the tnsnames.ora file.
I have tried the following with no success:
On the server, attempting to run tnsping yields error TNS-03502: Message 3502 not found; No message file for product=NETWORK, facility=TNS
What am I missing?
Try using a connection string that doesn't depend on tnsnames.ora, such as:
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;
Just adding the tns_admin path to web.config or app.config and point it to the folder where you have a tnsnames.ora file should work.
<oracle.manageddataaccess.client>
<version number="*">
<settings>
<setting name="tns_admin" value="E:\oracle11\product\11.2.0\client_1\network\admin" />
</settings>
</version>
</oracle.manageddataaccess.client>
I was after the same exact thing I ended up doing some regex on the TNSNAMES file. Once you've done the regex on the file you should be able to bring that into an object in Powershell or C#
param($tnsnamesPath = 'c:\tns\tnsnames.ora',$username = 'user',$password = 'gotmehere', $connectionName = 'mustard', $query = 'Select sysdate from dual')
$simplySQLPath = (Get-Module -ListAvailable simplySQL).ModuleBase
if($simplySQLPath -and (test-path $tnsnamesPath -PathType Leaf) -and (![string]::IsNullOrEmpty($node)))
{
[System.Reflection.Assembly]::LoadFile("$simplySQLPath\DataReaderToPSObject.dll") | OUT-NULL
Import-Module SimplySql -Force
$parsedTN = (get-content $tnsnamesPath -raw) -replace '(.*\=.*|\n.*\=)(.*|\n.*)\(DESCRIPTION*.\=' ,'Data Source = (DESCRIPTION ='
$splitTN = $parsedTN -split '(?=.*Data Source = \(DESCRIPTION \=)'
$tnsnames = $splitTN |?{$_ -like "*$connectionName*"}
$connstring = "$tnsnames;User Id=$username;Password=$password"
try
{
Open-OracleConnection -ConnectionString $connstring -ConnectionName $connectionName
$result = Invoke-SqlQuery -ConnectionName $connectionName -Query "$SQLQuery"
Close-SqlConnection -ConnectionName $connectionName
}
catch
{
$_.exception
}
}
Else
{
if(!(test-path $tnsnamesPath -PathType Leaf -ErrorAction Ignore))
{
Throw "Check TNSnamesPath: $tnsNamesPath"
}
else
{
Throw "Exeception SIMPLYSQL not found in module Path $($env:PSModulePath)"
}
}
$result
I've blogged about this code here: https://powershellposse.com/2018/03/13/tnsnames-file-parsing/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With