Skip to content
+1-888-319-3663

COMMUNITY FORUM

Excel Report Builder Using Wrong ODBC When Publishing

John Ellis asked 1 year ago
Hi There:
A GP end user, for years, has been using Excel Report Builder and has been successfully publishing new Excel reports.
Recently, this company moved into Amazon Web Services.  Ever since then, when the user either publishes new reports or modifies current reports in Excel Report Builder, neither the .xlsx files nor the .odc files will connect to the server.
The default reports created by GP through Reporting Tools Setup do connect successfully in AWS, as they always have.
So, we reviewed and compared the .odc files that he has tried publishing to those that connect to the server successfully.  We used Notepad, to do so.
The successful files use the correct ODBC connection name (Data Source Name) for SQL, while the files that do not connect successfully use the AWS server’s name.
Also, the ODBC for GP of the end user is indeed pointing to the correct Data Source Name.
Sure, the user can simply copy the correct Data Source Name from the working .odc files to the nonworking ones using Notepad. This is the workaround.
But, I’m trying to find a way to allow for any future published reports in Excel Report Builder to connect to the server (i.e. use the correct Data Source Name).
From where is Excel Report Builder pulling the Data Source Name from?  How can this all be fixed?
Thanks!
John Ellis
Answers
Nicole Albertson Staff answered 1 year ago
When Excel Report Builder needs to get the SQL Server name for the Data Source, it runs a “select @@servername”.
You will want to verify that the server name returned by that is correct.  If it isn’t, you can take a look at step 7 in this Microsoft Knowledge Base for more information.
https://support.microsoft.com/en-us/help/869315/error-message-when-you-perform-a-bulk-copy-process-bcp-in-microsoft-dy
 
I am not sure how GP pulls it for their default Refreshable Excel Reports.
 
If that doesn’t resolve it, what type of DataSource are you seeing for the GP ones versus the Excel Report Builder ones?
John Ellis replied 1 year ago

Thank you, Nicole! That is the right answer!

John

Atif answered 4 months ago
Nicole Albertson
I am facing the problem with excel report builder after upgrading from GP2015 to GP2018 R2.
My database server has a new location now but It is still getting data from old SQL server.
How I can update all connection files to connect with new SQL server?

If you would like to submit an answer or comment, please sign in to the eOne portal.