A few months ago Oracle queries stopped working on our reporting services server after a service pack on the reporting server.
I ended up having to grant permissions on Oracle directories to eliminate the first error and then renamed the NLS_LANG registry entry to get rid of the second error. The old value was: AMERICAN_AMERICA.WE8MSWIN1252
Now we are moving some new reports into production which use Oracle stored procedures and they are getting this error.
--- End of inner exception stack trace --- w3wp!processing!8!7/27/2009-09:26:09:: e ERROR: An exception has occurred in data source 'CSUD3_RPTAPL'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'MYDATASET'. ---> System.Data.OracleClient.OracleException: ORA-06550: line 1, column 57: PLS-00553: character set name is not recognized ORA-06550: line 0, column 0: PL/SQL: Compilation unit analysis terminated
I've created a test procedure that simply returns
select 'test' "field1" from dual;
It gets the same error. Tonight I will try setting NLS_LANG back to it's old value and see if that fixes the procedures - although it will break the regular oracle reports. If anyone has a clue what is going on, please enlighten me.
From what I've read, the client should be using the database default if the charset is not specified on the client.