topic RE: SQL Syntax Issue in Product Forum

SQL Syntax Issue

ManasJena — Thu, 27 Feb 2020 22:31:00 GMT

Hi All,

I am trying to use OLE DB and execute below Query. It fails with error saying " SQL Syntac Error:Error Occurred while executing a MDX statement"

"SELECT * FROM _SYS_BIC.ZB.CROSS- APPLICATION.GLOBAL.DIM/DIM_GB_SOURCE_SYSTEM"

Any suggestion ?

------------------------------
Manas Jena
Sr Analyst
ZimmerBiomet
America/New_York
------------------------------

RE: SQL Syntax Issue

vivek.kumar.goel — Fri, 28 Feb 2020 05:36:00 GMT

At the first look, the SQL Syntax doesn't seem to be right. "/" ?
Can you post the exact error ?

------------------------------
Vivek Goel
"If you like this post, please press the "Recommend" Button.
------------------------------

RE: SQL Syntax Issue

ManasJena — Fri, 28 Feb 2020 13:26:00 GMT

Hi Vivek,

Attached is the exact error.

------------------------------
Manas Jena
Sr Analyst
ZimmerBiomet
America/New_York
------------------------------

RE: SQL Syntax Issue

david.l.morris — Fri, 28 Feb 2020 13:28:00 GMT

There's a space after the hyphen "- ".

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

RE: SQL Syntax Issue

ManasJena — Fri, 28 Feb 2020 13:38:00 GMT

Hi Dave,

I still see same error after taking out that space you mentioned.

Internal : Could not execute code stage because exception thrown by code stage: sql syntax error: Error occurred while executing a MDX statement. The error message is: Syntax Error : SELECT * FROM _SYS_BIC.ZB.CROSS-<<<

attached is SQL Query image.

------------------------------
Manas Jena
Sr Analyst
ZimmerBiomet
America/New_York
------------------------------

RE: SQL Syntax Issue

david.l.morris — Fri, 28 Feb 2020 13:40:00 GMT

Try putting the same value inside a Text Data Item and then put the Data Item as the input to the OLEDB object.

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

RE: SQL Syntax Issue

ManasJena — Fri, 28 Feb 2020 14:00:00 GMT

Did as you suggsted. It failed with this error.

------------------------------
Manas Jena
Sr Analyst
ZimmerBiomet
America/New_York
------------------------------

RE: SQL Syntax Issue

david.l.morris — Fri, 28 Feb 2020 14:03:00 GMT

What's the first character in the value you put in the data item? You removed the quotation marks at the beginning?

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

RE: SQL Syntax Issue

ManasJena — Fri, 28 Feb 2020 14:18:00 GMT

I removed the quote .Below is how the query looks like.The same query works when I run directly from a DB client.

SELECT * FROM "_SYS_BIC"."ZB.CROSS-APPLICATION.GLOBAL.DIM/DIM_GB_SOURCE_SYSTEM"

------------------------------
Manas Jena
Sr Analyst
ZimmerBiomet
America/New_York
------------------------------

RE: SQL Syntax Issue

jorge.barajas — Fri, 28 Feb 2020 17:37:00 GMT

Try using a calculation stage to set the command text into a data input. Then use the data input as your SQL statement.
Also try executing a SELECT statement with another table just so you can get it to work. Sometimes I find that simplifying the issue and approaching it in steps helps.

Example: 1. Get a simple SELECT statement to return data with a small test table with a 1 word name (select * from myTestTable) once you get that to work you have established that you have connectivity, that you are getting data back from the database.
2. Rename that test table using some of the characters of the real table name until you can identify the one that is causing your issue

3. Format your statement so the select statement can be addressed with the real name but is formatted in the way OLE DB and BP accept the statement.

I hope this troubleshooting helps. If you can't rename the original table, make a test table with simple test data that you can modify and test with.

Regards

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

RE: SQL Syntax Issue

ManasJena — Fri, 28 Feb 2020 18:52:00 GMT

Thanks ! Jorge,

Below is how I have tried .It seems issue is with Provider it shows that MDX in error message.

Thanks!

Regards,

Manas Jena

Robotic Process Automation COE

Warsaw, Indiana

574-372-4013(Office)

574-306-8685 (Mobile)

manas.jena@zimmerbiomet.com

www.zimmerbiomet.com

---CONFIDENTIALITY NOTICE---

This communication and any attachements hereto may contain confidential information. Unauthorized use or disclosure to additional parties is prohibited. If you are not an intended recipient, kindly notify the sender and destroy all copies in your possession.

------Original Message------

Try using a calculation stage to set the command text into a data input. Then use the data input as your SQL statement.
Also try executing a SELECT statement with another table just so you can get it to work. Sometimes I find that simplifying the issue and approaching it in steps helps.

Example: 1. Get a simple SELECT statement to return data with a small test table with a 1 word name (select * from myTestTable) once you get that to work you have established that you have connectivity, that you are getting data back from the database.
2. Rename that test table using some of the characters of the real table name until you can identify the one that is causing your issue

3. Format your statement so the select statement can be addressed with the real name but is formatted in the way OLE DB and BP accept the statement.

I hope this troubleshooting helps. If you can't rename the original table, make a test table with simple test data that you can modify and test with.

Regards

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