However, apart from possible performance differences between the two (which you should test yourself – Henk van der Valk wrote a good post on this for SSAS MD and most of what he said is relevant for Tabular) there’s one less-than-obvious difference between these two options: the OLE DB connector does not appear to support query folding right now whereas the ODBC connector does. The server/instance name syntax used in the server option is the same for all SQL Server connection strings. Remember also that the OLE DB Provider for SQL Server was un-deprecated in October 2017. The documentation for the Odbc.DataSource and OleDb.DataSource M functions has more detail on how these connectors can be used and how connection string properties can be set.
Sql server connection string format how to#
For example here’s the UI for a new ODBC connection in SSDT: Here Mudassar Ahmed Khan has explained with an example, how to set Connection String with SQL Server Authentication in AppSettings.json in. 'Data SourceMySqlServerMSSQL1 ' You can also set the DataSource property of the SqlConnectionStringBuilder to the instance name when building a connection string. What are the alternatives then? Well you can use the OLE DB and ODBC connectors instead:īoth of these connectors do allow you to set your own connection string properties. To connect to a named instance of SQL Server, use the server nameinstance name syntax.
Connection strings are provider/driver specific so one first needs to decide on a client API before formulating the proper string can be created. While your average Power BI user is unlikely to even notice this, for SSAS Tabular developers it could be a big problem: complete control over the connection string is often necessary in enterprise BI scenarios. SQL Server can be accessed using several technologies, each of which has different connection string particulars. It turns out that the restriction on using your own connection string properties in the built-in SQL Server connector is a deliberate design decision on the part of the Power Query team because, behind the scenes, they use different providers in different circumstances to optimise performance, and because allowing arbitrary connection string properties might make maintaining backwards compatibility difficult in the future. This format is used in the connection string to specify the Data Source.
…and which are documented in the Sql.Databases() M function here. You want to connect to a named instance of a SQL Server or Microsoft Data Engine. It may not be immediately obvious, but you cannot set your own connection string properties when connecting to SQL Server using the built-in SQL Server connector from either Power BI or a modern data source in Azure SSAS/SSAS Tabular 2017:Īll you can do is configure the options that are available in the UI, which in the current version of SSDT looks like this:
0 kommentar(er)
