Microsoft Dynamics Forums Homepage

Forum Home Forum Home > Microsoft Dynamics GP (Great Plains) > GP - Customization and Integration
  New Posts New Posts RSS Feed - Error 245 in Query Designer
  FAQ FAQ  Forum Search   Register Register  Login Login


Error 245 in Query Designer

 Post Reply Post Reply
Author
Message
jefftoth View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: February 20 2014
Location: Ohio
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote jefftoth Quote  Post ReplyReply Direct Link To This Post Topic: Error 245 in Query Designer
    Posted: February 20 2014 at 7:49am
Be nice. If I've picked the wrong forum to be asking this question, please point me in the right direction.

I'm working on an Dynamics GP 10, Aged Payables Report in SSRS, Report Builder. Finished reports are saved to a SharePoint library for user access. I've successfully built and/or modified another 13 reports in this same library, but am having an issue with this one particular report.

The primary dataset for the report works against table, PM20000. The problem is related to the content of the DOCNUMBR column.

In the Query Designer window of the Dataset Properties window, running the query against the entire content of the table fails consistently, with an error 245, converting a varchar value to int. I don't understand why there is any conversion being called for, since the column is defined as varchar(22) and no explicit conversion is indicated in the SQL. If I modify, the query parameters to exclude Document Numbers with any alpha characters, the query runs just fine.

Regardless, the report generates successfully to the preview window, if you click on Run while in Report Builder, despite the query not running in Query Designer.

However, when you exit Report Builder and attempt to run the report from the SharePoint Library, the parameter window opens, the Loading... message appears momentarily, but then everything stops, which is what I would expect if the query is failing, but there are no error messages generated to let you know what's happening.

Has anyone experienced a similar problem?

I can't see how Dynamics GP is at fault, but I don't know for sure.

Am I right to suspect an underlying problem with Report Builder just deciding to do an implicit data conversion if the first values seen in the Document Number column appear to be numbers?
This doesn't explain why the report runs in Report Builder but not from SharePoint, though.

Thanks,

Jeff Toth
Jeff Toth
Back to Top
Sponsored Links


Back to Top
jefftoth View Drop Down
MicrosoftDynamicsForums.com Member
MicrosoftDynamicsForums.com Member


Joined: February 20 2014
Location: Ohio
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote jefftoth Quote  Post ReplyReply Direct Link To This Post Posted: February 25 2014 at 12:23pm
Referencing Craig Freedman's SQL Server blog article "More on Implicit Conversions", I came up with "Conversions between string types (e.g., CHAR or VARCHAR) AND NUMERIC TYPES (e.g., INT, REAL , or DECIMAL) are especially problematic. SQL Server ranks string types lower than numeric types and will always convert from a string to a numeric type..." Another reference talked about differences in string types in indexes and tables causing implicit conversions resulting in error 245, and gave some rather elaborate fixes that just aren't available to me.

Regardless, the fix to my reporting issue was to eliminate the parameters on Vendor Category and Document Number altogether. Both, at some point, involved values that could be converted to int, and a whole lot more that looked to the system like they might, but wouldn't. This is just screwy logic in SQL Server that causes any data conversion when the table columns and parameter values are both clearly defined as string types.
Jeff Toth
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down


Copyright 2013 microsoftdynamicsforums.com. All rights reserved. MicrosoftDynamicsForums.com is an independent non-Microsoft website.
Email: contact AT microsoftdynamicsforums DOT com