none
An error has occurred during report processing. Cannot create a connection to datasource 'AS400PROD'. ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    Question

  •      Does it make any sence that the modeling section would allow you to see the table columns on the AS400 database via the ODBC connection and allow you to layout the columns on the report but when you run the report (from the same Report Builder screen), you get the error above that you have no connection?  The initial connection to the AS400 database was established already but for some reason when you press the "RUN" button the named error pops up.  Can anyone explain why this might happen?  ODBC connections to Oracle do not pose a similar problem.  To suggest it may be the ODBC connection to AS400 does not explain why I am able to see and model the AS400 columns in the Report Builder via an established connection to AS400.

    Thanks,

    Les

     

    Friday, July 15, 2011 4:32 PM

Answers

All replies

  • Hi Les,

    Thanks for your question.

    Based on my research and past experiences, for one database account in SQL Server, it has the permission of viewing metadata and the permission of select, if one account only has the permission of viewing metadata, by using this account we can view the column names, however, when we selected the data, it will be an error.

    Please check that if it is a similar feature in AS400, if the issue caused by this reason, you should grant the select permission to this account.

    Hope it helps you. If your issue still exists, please feel free let me know.

    Thanks,
    Sharp Wang


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    Monday, July 18, 2011 6:59 AM
  • Thank you Mr Wang,

    You may be correct relating to the use of SSRS Report Builder but this does not explain why I am able to use the same ODBC connection and successfully connect to AS400 using that driver and have permission to properly query and receive data from a query via Toad.  The error only happens in SSRS Report Builder.  If I am correct to understand your logic, are you suggesting the permissions are on the side of AS400?  It only makes sence that the problem is local to the SSRS Report Builder product since I am able to properly query this data from Toad using that same connection.

    Do you have any other thoughts regarding this?  Your assistance is much appreciated.

    Les

     


    Les Draper
    Tuesday, July 26, 2011 1:50 PM
  • Additional clues...

    The PC connections to the AS400 seem to work correctly and I believe they are 32 bit.  I believe the SQL Server connections by which the AS400 connection is made via SQL Server is 64 bit.  It is that connection we seem to have trouble with.  Both are ODBC but I was told they may actually be different connections.  What could be different about 32 bit and 64 bit connections that would look at AS400 permissions differently?  Is it possible that if there is indeed a level of security provided by AS400 that the 32 bit connection is not looking at level of permissions in AS400 to execute a query where the 64 bit connection does look at that level of security and enforces it?

    Les

     

     


    Les Draper
    Tuesday, July 26, 2011 2:16 PM
  • Hi Les,

    Based on your previous two replies, I had researched this issue in depth. As you said, there are some differences between the ODBC for 32 bit and the ODBC for 64 bit.

    For example, you create a user DSN for the 32-bit driver "Microsoft Access Driver (*.mdb)." This driver does not have a corresponding 64-bit version. The SQLDataSources function that is called in a 64-bit application returns this 32-bit user DSN. However, if you make a connection through this 32-bit user DSN, you receive the error message that is mentioned earlier in this section.

    To work around this issue: use the appropriate version of the ODBC Administrator tool.

    There is one relational article: The 32-bit version of the ODBC Administrator tool and the 64-bit version of the ODBC Administrator tool display both the 32-bit user DSNs and the 64-bit user DSNs in a 64-bit version of the Windows operating system, for more information, please see it.

    Hope it helps you. If the issue still exists, please let me know.

    Thanks,
    Sharp Wang


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    Monday, August 01, 2011 6:07 AM