Query in SQL Report Builder says its failed

Kinetic ERP
1

This is the code in the Query:
=“SELECT T1.XXX_PODesc_c, T1.PartNum
FROM Part_” + Parameters!TableGuid.Value + “T1”

When I run it it gives the following error: For more information about this error navigate to the report server on the local server machine, or enable remote errors
----------------------------
Query execution failed for dataset ‘DataSet2’. (rsErrorExecutingCommand)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)

I dont get what I’m doing wrong, can someone help me?
If I need to supply more information then I can

2 
="SELECT T1.XXX_PODesc_c, T1.PartNum
FROM Part_" + Parameters!TableGuid.Value + " T1"

Trying putting a space before the T1.

And welcome to EpiUsers!

1 Like
3

Hey Mark,
Thanks!

I did what you proposed and it still gives the same error

4

Ian, paste that in chatgpt or something else you trust and tell it to make it all one line with each word separated by space

5

Then copy the response and put it back in your query and try again.

6

And we’re sure that this field exists?

XXX_PODesc_c

In Part? That XXX_ looks sus.

1 Like
7

Paste this in GPT or whatever and tell it to make it one line of SQL with each word separated by a space. Also, I am with Mark, does this field actually exist “T1.XXX_PODesc_c”?

8

Hey Taylor and Mark, The real name of the field is nds_PODesc_c and its a custom field. I will try the Chatgpt route

1 Like
9

Oh I see, no worries you were purposely hiding the characters, no worries.

10

ChatGPT Generates this:
=“SELECT T1.nds_PODesc_c, T1.PartNum FROM Part_” + Parameters!TableGuid.Value + " T1"
in a single line. I tried it but it still gives off the same error

11

Are you on prem or cloud?

12

Since you’re on-prem, you can use SSMS to check the fields.

First, there should be a PART_UD table and you should see values in PART_UD.nds.PODesc_c. If so, the next thing is to make sure it’s making it to the SSRS report database.

If you run the report in Generate Mode only and set the archive period to a week, you should be able to find Part_{GUID}, where the GUID is the report ID you can find in the System Monitor. Check to see if the field exists there and has data.

1 Like
13

Ian,

There are also SSRS server logs that may have more detail on your SQL server or wherever you’re hosting your SSRS from.

The path should be something like this:

Look around in program files and/or program files x86 till you see something like that.

C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles

14

Also like mark said, you can use SQL server management studio (SSMS) and run that query directly in there and use the GUID that you get from your system monitor and see if the query gives you any errors in SSMS.

15

I recently ran across this same error. It turned out there were 2 shared data sources listed in the report. One was pointing to the test server and the other the live server. We removed the reference to the other server and it ran fine. I’ve found packaging reports in the solution workbench is the best way to transfer between instances and then you don’t get 2 shared data sources in the report.
Also, the log files @utaylor mentioned are really helpful - good reference to keep in the toolbox!

3 Likes
16

yeah it was odd to me that they had dataset2 in the error, it’s hard to know what exactly the problem is without being there.