Sql.Database

Sql.Database

 

This topic applies to the Power Query Formula Language (informally known as "M") which can be used with Power Query and Power BI Desktop to build queries that mashup data. See the list of function categories.

Returns a table of SQL tables, views, and stored functions from the SQL Server database database on server server. The port may be optionally specified with the server, separated by a colon or a comma. An optional record parameter, options, may be specified to control the following options:

     
  •  MaxDegreeOfParallelism : A number that sets the value of the "maxdop” query clause in the generated SQL query.
  •  
  •  CreateNavigationProperties : A logical (true/false) that sets whether to generate navigation properties on the returned values (default is true).
  •  
  •  NavigationPropertyNameGenerator : A function that is used for the creation of names for navigation properties.
  •  
  •  Query : Text that is translated into a SQL query that is run on the server. Multiple queries may be specified but only the first result will be returned.
  •  
  •  CommandTimeout : A duration which controls how long the server-side query is allowed to run before it is canceled. The default value is ten minutes.
  •  
  •  HierarchicalNavigation : A logical (true/false) that sets whether to view the tables grouped by their schema names (default is false).
  •  
The record parameter is specified as [option1 = value1, option2 = value2...] or [Query = "select ..."] for example.

Sql.Database(server as text, database as text, optional options as nullable record) as record  

ArgumentDescription
serverThe name of the SQL Server instance. The port may be optionally specified, separated by a colon or a comma.
databThe database in the SQL Server instance.
optional optionsAn options record to control the behavior of this function.
SettingDescription
MaxDegreeOfParallelism as number or integerSets the value of the "maxdop” query clause in the generated SQL query.
CreateNavigationProperties as logicalSets whether to generate navigation properties on the returned values. Default is true.
NavigationPropertyNameGenerator as functionProvides a function that is used for the creation of names for navigation properties.
Query as textProvide a query text that is run on the server to return values
CommandTimeout as durationSpecifies how long the server-side query is allowed to run before it is cancelled. The default value is 10 minutes.
Sql.Database("localhost", "Northwind", [Query= select * from Customers])  

Show:
© 2016 Microsoft