ODBC in SSRS reporting server

Question

ODBC in SSRS reporting server

Vicki 46

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

2 answers

Your answer

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

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.

Vicki 46 Reputation points

2023-04-14T22:35:37.63+00:00

Hi, Excel was not installed on the server.X is the drive that I mapped to. I removed the drive and connected it to the path, but I still got the same error. Dsn=Excel Files;dbq=\\file\report\excel\file\stglocation.xlsx;defaultdir=\\file\report\excel\file\stglocation.xlsx;driverid=1046;maxbuffersize=2048;pagetimeout=5
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-15T08:51:08.5233333+00:00

Shouldn't there be a double backslash in the beginning of the path?

Answer 2

Anonymous

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

Vicki 46 Reputation points

2023-04-17T13:37:26.43+00:00

I was able to get to the file from the path below from file explore, but it didn't work when use the path below from s connection on SSRS sever. - Thanks Dsn=Excel Files;dbq=\\file\report\excel\file\stglocation.xlsx;defaultdir=\\file\report\excel\file\stglocation.xlsx;driverid=1046;maxbuffersize=2048;pagetimeout=5
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-17T15:31:59.61+00:00

If you had X mapped to \Fileserver\Share, I would expect the UNC path to start with \fileserver\path, but it may be better to talk to your local IT folks.
Vicki 46 Reputation points

2023-04-17T18:38:05.2833333+00:00

Hi, they told me that's the correct UNC path.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-04-17T19:53:35.75+00:00

So if you open a command line window and paste in that path, does the Excel file open? If it does, check the permissions for the service account for Reporting Services. Particularly, is it running on a domain account or a local machine account? I need to make one confession: I don't actually know Reporting Services myself, but I've some experience of general troubleshooting.

Share via

ODBC in SSRS reporting server

2 answers

Your answer