SQL Syntax Issue

[ManasJena]

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
------------------------------

[VivekGoel]

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.
------------------------------

[ManasJena]

Hi Vivek,

Attached is the exact error.

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

[DaveMorris]

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

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

[ManasJena]

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
------------------------------

[DaveMorris]

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
------------------------------

[ManasJena]

Did as you suggsted. It failed with this error.

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

[DaveMorris]

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
------------------------------

[ManasJena]

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
------------------------------

[jorge.barajas]

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
------------------------------