none
SSRS Oracle Connections fail after a period without issue.

    Question

  • Our report developers have started producing SSRS 2008 reports against an Oracle database.  After a week or two of no issues, the reports fail and we get the following errors in the SSRS logs...

    "Cannot create a conection to data source'[connection name]'"  etc
    "System.Data.OracleClient.OracleException: ORA-12154: TNS:could not resolve service name" ...plus a stack trace mentioning connections and pooling.  

    A restart of the reporting services service fixes the issue - then the cycle seems to repeat.  Its as if some underlying issue builds up over time until a critical point is reached requiring the service reset.

    The DataSource type is "Oracle", the connection string is the recommended;
    "Data Source=[ConnName];Unicode=True"

    The intergoogle doesnt provide many clues - there is little evidence of people tinking with the connection string or if they do the results and discussion provide no clarity.

    One option might be to remove Connection Pooling altogether. but this seems severe so my question is does anyone have any suggestions?  Remember the reports connect without issue so this is not a simple connectivity issue - at least not until some later time after a break point is reached.

    thanks
    Wednesday, September 11, 2013 2:05 AM

Answers

  • Hi,

    I have worked on SSRS reports having Oracle as the data source.

    I have never faced a issue like this;

    Below is the configuration file details:

    # Every line that begins with # is a comment line
    #
    # Create Oracle net service names, or aliases, for each database server 
    # you need to connect to.
    #
    # TNSNames.ora sample entry
    #
    # alias =
    #  (DESCRIPTION =
    #    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mycompany.com)(PORT = 1521))
    #    (CONNECT_DATA =
    #      (SERVER = DEDICATED)
    #      (SERVICE_NAME = orcl)
    #    )
    #  )
    #
    # You can modify the entry below for your own database.
    # <data source alias> = Name to use in the connection string Data Source
    # <hostname or IP> = name or IP of the database server machine
    # <port> = database server machine port to use
    # <database service name> = name of the database service on the server

    OPS =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx.abc.com)(PORT = 1521))

        (CONNECT_DATA =

          (SERVER = DEDICATED)

          (SERVICE_NAME = USERDEV)

        )

      )

    Let me know if you still have the problem.

    Thanks & Regards

    Mark as answered if my post solved your problem


    Milan Das

    Wednesday, September 11, 2013 7:05 AM