SSRS error

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
wanderingsmith
Posts: 7
Joined: Thu 22 Feb 2018 15:28

SSRS error

Post by wanderingsmith » Thu 22 Feb 2018 15:38

I created a 64bit user DSN (I also created a system one, but it doesn't appear in dropdowns when I go into my report writers).
Using Crystal, it works beautifully (THANK YOU!!).

However, when I try to get SSRS (2012r2) to create an ODBC datasource,
- I can can pick the same DSN that works in Crystal, hit test in that (ODBC) window and it succeeds,
- but then when I OK and go back to the datasource window and test, it fails:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
** However, the first time, I only tested the first and thought all was well, so I went and created a query.
- and if I hit Run inside the Query designer window, it returns data!
- But when I then go to actually run the report, it fails.
Cannot create a connection to datasource
Any ideas?

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: SSRS error

Post by Stellar » Fri 23 Feb 2018 12:52

Try specifying the name without spaces for the ODBC data source, and specify it in the Connection string.
Example of setting Connection string using DSN name:
DSN=DNS_NAME

wanderingsmith
Posts: 7
Joined: Thu 22 Feb 2018 15:28

Re: SSRS error

Post by wanderingsmith » Fri 23 Feb 2018 13:10

My DSN is 3 letters, no spaces or other funny business in the string. If that is what you mean.

If I go to the Connection properties and click Advanced, the Driver field is blank.. not sure if I tried this before but I just typed in the driver name, which resulted in the source string being:
Dsn=xxx;Driver=Devart ODBC Driver for PostgreSQL
..and nope. same error

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: SSRS error

Post by Stellar » Fri 23 Feb 2018 15:52

We investigated the issue "Cannot create a connection to datasource" when retrieving data from the Data Source in SSRS. If DNS is specified in Connection string, then Data Source does not work correctly. Unfortunately, we cannot influence this behavior, you can check this by testing the work of Data Source using any other standard ODBC driver.
You can try specifying a full connection string:
Driver=Devart ODBC Driver for SQL Server;Server=SERVER_NAME;Port=1433;Database=DATABASE_NAME;Uid=USER_NAME;Pwd=PASSWORD;

wanderingsmith
Posts: 7
Joined: Thu 22 Feb 2018 15:28

Re: SSRS error

Post by wanderingsmith » Fri 23 Feb 2018 16:08

no joy
I used
Driver=Devart ODBC Driver for PostgreSQL;Server=myIP;Port=myportnum;Database=mydb;Uid=myuser;Pwd=mypw;
That is the driver name that shows up when I create a DSN. I tried yours first and it didn't work either.

Do I have to use any special characters for the IP address for the server? or can it be just
Server=192.168.0.1;Port
I also need to convert to Unicode (which the DSN does, but if we are doing a manual string...). Would that be
Driver=Devart ODBC Driver for PostgreSQL;Server=myIP;Port=myportnum;Database=mydb;Uid=myuser;Pwd=mypw;AutoTranslate=Yes;

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: SSRS error

Post by Stellar » Tue 27 Feb 2018 12:57

Unfortunately, we could not any issues in the operation of Devart ODBC Driver for PostgreSQL in SSRS, using the following connection string:

Driver=Devart ODBC Driver for PostgreSQL;Server=SERVER_IP;Port=SERVER_PORT;Database=DATABASE_NAME;Uid=USER_NAME;Pwd=USER_PASSWORD;;Schema=SCHEMA_NAME;Use Unicode=True;

A screenshot of the Data Source parameters is as follows: https://files.fm/u/btp39va8#/view/Data+Source.jpg

wanderingsmith
Posts: 7
Joined: Thu 22 Feb 2018 15:28

Re: SSRS error

Post by wanderingsmith » Tue 27 Feb 2018 13:26

drat. I guess that means you don't support 2012r2. Thanks for trying.

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: SSRS error

Post by Stellar » Tue 27 Feb 2018 15:51

Devart ODBC Driver for PostgreSQL supports Microsoft SQL Server 2012 SP2 Report Builder.
Please tell us the build number of the Microsoft SQL Server Report Builder in which you are trying to create Data Source.

wanderingsmith
Posts: 7
Joined: Thu 22 Feb 2018 15:28

Re: SSRS error

Post by wanderingsmith » Tue 27 Feb 2018 15:52

report builder 3.0 microsoft server 11.0.5058.0

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: SSRS error

Post by Stellar » Fri 02 Mar 2018 15:35

We tested the compatibility of Devart ODBC Driver for PostgreSQL and Report Builder 3.0 (11.0.5058.0), but unfortunately, we could not find any issues with the driver.

The screenshots show how to create a test report in Report Builder using Devart ODBC Driver for PostgreSQL:
https://files.fm/u/btp39va8#/view/Report1.jpg
https://files.fm/u/btp39va8#/view/Report2.jpg
https://files.fm/u/btp39va8#/view/Report3.jpg
https://files.fm/u/btp39va8#/view/Report4.jpg

Please specify at what stage you get an error.

wanderingsmith
Posts: 7
Joined: Thu 22 Feb 2018 15:28

Re: SSRS error

Post by wanderingsmith » Fri 02 Mar 2018 15:47

I will send you email with screencaps

Stellar
Devart Team
Posts: 496
Joined: Tue 03 Oct 2017 11:00

Re: SSRS error

Post by Stellar » Mon 05 Mar 2018 15:23

Please try to create Data Source using system DSN x86 for Devart ODBC Driver for PostgreSQL.
If it does not help solving the issue, we are ready to solve it using a remote connection to your computer. For this, you can use a convenient tool (TeamViewer, etc.). Please tell us the parameters of the remote session and the time that is convenient for you. In turn, we will be able to use this session from 9 a.m. to 5 p.m. (our time zone is UTC+02:00).

wanderingsmith
Posts: 7
Joined: Thu 22 Feb 2018 15:28

Re: SSRS error

Post by wanderingsmith » Tue 06 Mar 2018 13:59

I have a teamviewer ID if you are on for now

Post Reply