Forum Discussion
Memorable MemberMISSING QUERY FEATURE from Lookup activity
I found why the following T-SQL statement was not being processed correctly:
SELECT MAX(stationId) FROM dbo.EcoCount_StationInstead, if you add an alias, like so:
SELECT MAX(stationId) AS result FROM dbo.EcoCount_Stationthen it works.
This is the kind of stuff that an intelligent engine can auto add transparently and the user will see the activity work properly. Plus, the GUI could pop up an advisory bubble notifying the user of how to specify the T-SQL statement while at the same time telling the user that it automatically did it for him. Not rocket science.
To be perfectly honest, I am so sick and tired of this BS, always trying to guess how we are supposed to enter config or SQL statements in the GUI. I mean it's mindboggling that we are still at this a year after the so-called GA release of Fabric. And this is just basic functionality we are talking about, not landing a rocket on Mars. 🙃
Memorable MemberI just tried the Script activity and it won't do. Look at this, it only gives me access to the default staging DW. But all my data sits in a lakehouse! It is the LH SQL endpoint I need to query, not a DW.
So the Script activity is neither a substitute nor a solution to palliate the shortcomings of the Lookup activity. I guess all that remains is to use a Notebook. Can't wait for the fix.
Hi Element115 ,
Take the SQL analytics endpoint from the lakehouse to create a new cloud connection - use External as an option and run the query against the cloud connection within the pipeline. Just ran and works.
Steps to connect to Lakehouse Endpoint using Script Activity:
1. Copy the SQL Endpoint from Lakehouse.
2. In Script, click on External and then Azure SQL Database, fill the details and use Organizational Account to establish connection.
3. Check the connection
4. Execute it
Note: Support for SQL endpoint for Lakehouse is in our backlog but I don't have a ETA.
Hope this is is helpful. Please let me know incase of further queries.
- Element115
I found why the following T-SQL statement was not being processed correctly:
SELECT MAX(stationId) FROM dbo.EcoCount_StationInstead, if you add an alias, like so:
SELECT MAX(stationId) AS result FROM dbo.EcoCount_Stationthen it works.
This is the kind of stuff that an intelligent engine can auto add transparently and the user will see the activity work properly. Plus, the GUI could pop up an advisory bubble notifying the user of how to specify the T-SQL statement while at the same time telling the user that it automatically did it for him. Not rocket science.
To be perfectly honest, I am so sick and tired of this BS, always trying to guess how we are supposed to enter config or SQL statements in the GUI. I mean it's mindboggling that we are still at this a year after the so-called GA release of Fabric. And this is just basic functionality we are talking about, not landing a rocket on Mars. 🙃
This does not really work for me, it tells me the table does not exist. Any other setting necessary for writing SQL query in the lookup activity?
Hi Element115 ,
It's expected to have alias so as to get the output as key value pair in json.
Thank you- Element115
Yes, once you know that, seems self-evident. There should be a short 'In a Nutshell' ref website where all this is listed, instead of having to read to 10s and 10s of pages of documentation.
- Element115
So I tried what you suggested. Unfortunately, it doesn't work when using the following T-SQL statement:
SELECT MAX(stationId) FROM dbo.EcoCount_Stationand ends with this error:
If I use this statement instead:
SELECT * FROM dbo.EcoCount_Stationit works, but it is the wrong statement to use for the goal at hand.
I checked that the first statement is OK by running it from the SQL endpoint editor and there was no issue.
So there is obviously an issue with the Script activity and T-SQL functions such as MAX(), everything else being equal.
So we are back to square one or a PySpark Notebook (which I haven't tried yet, so perhaps even a Notebook won't work).
And honestly, I am getting so tired of doing the beta testing on my own time with no pay for Microsoft's QA/QC team.
