ODBC in SSRS reporting server

Vicki 46 Reputation points
2023-04-14T21:22:59.1966667+00:00

HI, I created the SSRS report, and the data source is DOBC connected to an excel file from a shared drive location. Everything worked fine from my machine's ODBC. Both mine and the server are 64-bit.   However, when I deployed to the server, I kept getting the connection string error.r: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. Dsn=Excel Files;dbq=x:\file\report\excel\file\stglocation.xlsx;defaultdir=x:\file\report\excel\file\stglocation.xlsx;driverid=1046;maxbuffersize=2048;pagetimeout=5 Can you tell me why I kept getting errors? Thanks

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,826 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2023-04-14T22:03:59.0666667+00:00

    Is Excel installed on the server? (I don't think it is that common to install Excel on servers.)

    Is the drive X available on the server? You talk about a mapped drive, but drive mapping is done on user level, and typically not seen by something that runs as a service. I think you should try a UNC path instead.


  2. AniyaTang-MSFT 12,331 Reputation points Microsoft Vendor
    2023-04-17T01:45:26.5566667+00:00

    Hi @Vicki This seems to have something to do with the way the mapped network drive is accessed. Mapped drives are per user, so it is usually not possible to use mapped drives, you can try using the full UNC path. Best regards, Aniya