In my quest to check out every last bit of obscure Power Query functionality, this week I looked into the ContextInfo option on the Sql.Database and Sql.Databases M functions. This option allows you to set CONTEXT_INFO in SQL Server (see here for a good article explaining what this is) and here’s an example of how to use it:
let Source = Sql.Database( "localhost", "AdventureWorksDW2017", [ Query = "SELECT * FROM DIMDATE", ContextInfo = Text.ToBinary( "Hello" ) ] ) in Source
This Power Query query runs a simple SQL SELECT statement against the SQL Server Adventure Works DW 2017 database. Note that since you need to pass a binary value to the ContextInfo option, in this example I had to use the Text.ToBinary function to convert my text to binary.
Here’s what happens in SQL Server when this Power Query query is run:
Here’s a simple example of how to retrieve this data on the SQL Server side:
SELECT session_id, login_time, program_name, context_info FROM sys.dm_exec_sessions WHERE session_id=57
I’ll leave it to the SQL Server experts to decide what this can be used for and no doubt to complain that it would be more useful to support SESSION_CONTEXT too – although I’ve heard that might already be used for something, so I need to do more research here…