SQL query error

[MasegoMmotlana]

hi im getting this error please assist

Internal : Could not execute code stage because exception thrown by code stage: 'Consolidated [2]$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.


My query:
"SELECT * FROM ["&[Worksheet Name]&"$] WHERE ["&[Worksheet Name]&"$].[Distribution Channel Entity Number] = "&[Distribution Channel Entity]&" AND ["&[Worksheet Name]&"$].[Broker House Entity Number] = "&[Broker House Entity]&" AND ["&[Worksheet Name]&"$].[Broker Entity Number] = "&[Broker Entity]&" AND ["&[Worksheet Name]&"$].[Employer Number (2)] = "&[Employer Number]&" AND ["&[Worksheet Name]&"$].[Branch Number (2)] = "&[Branch Number]


not sure if there's anything wrong i have done

------------------------------
Masego Mmotlana
------------------------------

[jorge.barajas · Accepted solution]

Hi Masego, 

Here are some suggestions:

1. The error itself seems to indicate that there may be a special character in the SQL query that is not working well, such as a missing or extra "[" or "]". I would look at that first. Use the 'Validate' and 'Evaluate Expression' buttons when you are in the Expression Chooser screen in Blue Prism to make sure the SQL statement validates correctly and looks good to you. 

2. The information you posted, does not provide enough information about what VBO and action you are using.
a) It would also be useful to know the names of your tables, worksheets and columns.
b) What is your data source? Are you trying to retrieve this data from an Excel, Access or SQL Server database (or other)?
c) Have you tried running your query directly on the application (a SQL server query for example) to make sure the SQL statement works when you run it from outside Blue Prism?

3. When I am having an issue like this, I often simplify my SQL statement to make sure I first get something simple to work, then I slowly add things in to work my way to the full statement I want. In the process, I typically find what part of the statement is giving me issues.  Have you tried simplifying your inquiry to something like: 

SELECT * FROM [Worksheet Name] WHERE [Distribution Channel Entity Number] = "[Distribution Channel Entity]

Rename your tables/columns if you can (even temporarily if possible) so they don't have special characters and work with simple names so you can see where the issue is coming from. 

Hope these suggestions help you to try a few things to troubleshoot your SQL statement. 

Best,

------------------------------
Jorge Barajas
Blue Prism
Senior Product Consultant
Austin, Texas
------------------------------

[MasegoMmotlana]

Hello 

 I will run the process first in on SQL application cause I didn't do that.
Data source is Excel
Thank you for your response. I will keep the things suggested in mind.

------------------------------
Masego Mmotlana
------------------------------