Usar SQL para consultar datos

[Este tema es documentación preliminar y está sujeto a modificaciones.]

La capa empresarial de Microsoft Dataverse proporciona un punto de conexión de flujo de datos tabulares (TDS) que emula una conexión de datos SQL. La conexión SQL proporciona acceso de solo lectura a los datos de la tabla del entorno de destino de Dataverse, lo que le permite ejecutar consultas SQL en las tablas de datos de Dataverse. No se han proporcionado vistas personalizadas de los datos. La conexión SQL del punto de conexión de Dataverse usa el modelo de seguridad de Dataverse para el acceso a los datos. Se pueden obtener datos para todas las tablas de Dataverse a las que tiene acceso un usuario.

Nota

Solo la conexión de datos SQL a través de SQL Server Management Studio y las bibliotecas .NET está en versión preliminar. Power BI está disponible en general.

Requisitos previos

La opción Habilitar punto de conexión TDS debe estar habilitada en el entorno. Está habilitada de forma predeterminada. Más información: Administrar la configuración de las características

Soporte de aplicaciones

A continuación se describe la compatibilidad de aplicaciones de punto de conexión TDS (SQL) para Power BI y SQL Server Management Studio.

SQL Server Management Studio (versión preliminar)

Nota

Se ha encontrado un problema de compatibilidad con la compilación de SQL Server Management Studio 19.0.1. Se está investigando una solución. Hasta que la solución esté disponible, use la compilación 18.12.1 de SQL Server Management Studio. Esta nota se actualizará una vez que haya una solución disponible.

También puede usar Estudio de administración de SQL Server (SSMS) versión 18.12.1 o posterior con la conexión SQL del punto de conexión de Dataverse. A continuación se proporcionan ejemplos de uso de SSMS con la conexión de datos SQL.

Tabla expandida de cuentas.

Seguridad y autenticación

Sólo se admite la autenticación Microsoft Entra ID. No se admiten la autenticación de SQL ni la autenticación de Windows. A continuación se muestra un ejemplo de cómo iniciar sesión en la conexión SQL en SSMS. Observe que el nombre del servidor es la dirección URL de la organización.

Diálogo de conexión.

Nota

Los puertos 1433 y/o 5558 deben estar habilitados para usar el extremo de TDS desde una aplicación cliente como SSMS. Si solo habilita el puerto 5558, el usuario debe agregar ese número de puerto al nombre del servidor en el cuadro de diálogo Conectar al servidor de SSMS, por ejemplo: myorgname.crm.dynamics.com,5558.

Información sobre el cifrado de puntos de conexión: Protección de datos en tránsito

Consultas de datos de tabla de ejemplo

A continuación hay un par de consultas de ejemplo compuestas en SSMS. La primera imagen muestra una consulta simple usando alias y ordenamiento de resultados.

select top 5 a.name as [VIP customer], a.address1_postalcode as [ZIP code] from account a order by a.address1_postalcode desc

Consulta simple usando alias y ordenando.

La siguiente consulta muestra un JOIN.

select name, fullname from account a inner join contact c on a.primarycontactid = c.contactid

Otra consulta usando un JOIN.

Power BI (disponibilidad general)

Puede usar la opción Analizar en Power BI (Datos > Tablas > Analizar en Power BI) en Power Apps (https://make.powerapps.com) para usar el conector de Dataverse para analizar datos en Power BI Desktop. Más información: Ver datos de tabla en Power BI Desktop

Nota

Para habilitar esta característica, consulte la configuración de punto de conexión TDS en Administrar la configuración de características. Una vez habilitada, debería ver un botón Analizar en Power BI en la barra de comandos de Power Apps.

Tipos de datos y operaciones compatibles

Cualquier operación que trate de modificar datos (es decir, INSERT, UPDATE) no funcionará con esta conexión de datos SQL de solo lectura. Para obtener una lista detallada de las operaciones SQL admitidas en punto de conexión de Dataverse, consulte Cómo se diferencia el SQL de Dataverse de Transact-SQL.

Los siguientes tipos de datos de Dataverse no son compatibles con la conexión SQL: binary, image, sql_variant, varbinary, virtual, HierarchyId, managedproperty, file, xml, partylist, timestamp, choices. Además, los tipos de tablas 'virtual' y 'audit' no son compatibles en este momento.

Sugerencia

Los atributos partylist pueden consultarse combinando con la tabla activityparty, como se muestra a continuación.

select act.activityid, act.subject, string_agg([to].partyidname, ', ')
from activitypointer as act
left outer join activityparty as [to] on act.activityid = [to].activityid and [to].participationtypemask = 2
group by act.activityid, act.subject

Comportamientos de tipo de columna de búsqueda

Las columnas de búsqueda de Dataverse se representan como <lookup>id y <lookup>nombre en un conjunto de resultados.

Comportamientos de tipo de columna de elección

Las columnas de elección de Dataverse se representan como <choice>Nombre y <choice>Etiqueta en un conjunto de resultados.

Sugerencia

Después de realizar cambios en las etiquetas de una columna de opciones, es necesario que se publiquen las personalizaciones de la tabla.

Nota

Incluir una gran cantidad de etiquetas de elección en su consulta tendrá un impacto significativo en el rendimiento. Es mejor usar menos de 10 etiquetas si es posible. Debido a que las etiquetas de elección están localizadas, la cadena localizada cuesta más devolverla.

Versión SQL con informe

El punto de conexión Dataverse TDS emula las capacidades de consulta de solo lectura de Microsoft SQL Server sobre la lógica de negocios de Dataverse. Dataverse devuelve la versión actual de SQL Azure 12.0.2000.8 para select @@version.

Guía de rendimiento

Al recuperar datos a través del punto de conexión de TDS, hay algunos patrones de consulta clave que deben usarse. Estos patrones de consulta, que se describen a continuación, administrarán el rendimiento y el tamaño de los conjuntos de resultados.

Solo las columnas necesarias

Al crear una consulta, devuelva solo las columnas necesarias. Esto ayuda tanto a la ejecución de la consulta como a la transferencia de los resultados a la aplicación cliente. En general, se recomienda mantener una consulta de menos de 100 columnas.

Columnas de elección

Las columnas de elección se han aplanado en dos columnas, lo que ayuda a la usabilidad. Sin embargo, es importante hacer agregados y filtros según la parte de valor de la columna de elección. La parte de valor puede tener índices y se almacena en la tabla base. Sin embargo, la parte de etiqueta (nombre 'choicecolumn') se almacena por separado, lo que cuesta más recuperarla y no se puede indexar. El uso de un número significativo de columnas de etiquetas de elección puede generar una consulta muy lenta.

Usar X superior

Es muy importante usar una cláusula superior en sus consultas para evitar intentar devolver toda la tabla de datos. Por ejemplo, use Select Top 1000 accountid,name From account Where revenue > 50000 limita los resultados a las primeras 1000 cuentas.

No usar NOLOCK

Al crear consultas, no utilice la sugerencia de tabla NOLOCK. Esto evitará que Dataverse optimice las consultas.

Limitaciones

Hay un límite de tamaño máximo de 80 MB para los resultados de consulta devueltos por el punto de conexión de Dataverse. Considere el uso de herramientas de integración de datos como Azure Synapse Link for Dataverse y flujos de datos para consultas de datos grandes que devuelven más de 80 MB de datos. Más información: Importación y exportación de datos

Sugerencia

Para ayudar a mantener el tamaño de los datos devueltos dentro de límites aceptables, use la menor cantidad posible de columnas de texto de varias líneas y columnas de opciones.

Las fechas devueltas en los resultados de la consulta tienen el formato de hora universal coordinada (UTC). Anteriormente, las fechas se devolvían en la hora local.

La consulta de datos mediante SQL no activa ningún complemento registrado en los mensajes de RetrieveMultipleRequest ni de RetrieveRequest. Por lo tanto, cualquier reescritura de la consulta o resultados que normalmente se realizarían con dicho complemento no tendrá efecto para una consulta SQL.

Las consultas que utilizan el punto de conexión TDS se ejecutan bajo los límites de la API de protección del servicio.

El TDS punto de conexión no se puede usar con tablas elásticas. Más información: Tablas elásticas

Solución de problemas de conexión

A continuación, se muestran algunas condiciones de error conocidas y cómo resolverlas.

Nota

Los puertos 1433 y/o 5558 deben estar habilitados para usar el extremo de TDS desde una aplicación cliente como SSMS. Si solo habilita el puerto 5558, el usuario debe agregar ese número de puerto al nombre del servidor en el cuadro de diálogo Conectar al servidor de SSMS, por ejemplo: myorgname.crm.dynamics.com,5558.

Autenticación

Solo se admite la autenticación de Microsoft Entra ID en la conexión SQL del punto acceso de Dataverse. El mecanismo de autenticación preferido es "Microsoft Entra ID - Universal" con autenticación multifactor (MFA). Sin embargo, "Microsoft Entra ID - Contraseña" funcionará si MFA no está configurado. Si intenta utilizar otras formas de autenticación, verá errores como los siguientes.

  • Error devuelto al usar la autenticación Microsoft Entra ID - Integrada.

"Error de inicio de sesión: se prohibió la solicitud HTTP con el esquema de autenticación de cliente 'Anónimo'. RequestId: TDS;81d8a4f7-0d49-4d21-8f50-04364bddd370;2 Time: 2020-12-17T01:10:59.8628578Z (.Net SqlClient Data Provider)"

  • Error devuelto al usar la autenticación de SQL Server.

“Error de inicio de sesión. Solicitud no autenticada. RequestId: TDS;918aa372-ccc4-438a-813e-91b086355343;1 Time: 2020-12-17T01:13:14.4986739Z (.Net SqlClient Data Provider)"

  • Error devuelto al usar la autenticación de Windows.

“Error de inicio de sesión. Solicitud no autenticada. RequestId: TDS;fda17c60-93f7-4d5a-ad79-7ddfbb917979;1 Time: 2020-12-17T01:15:01.0497703Z (.Net SqlClient Data Provider)"

Puertos bloqueados

Un error de puerto bloqueado puede tener la siguiente apariencia.

Mensaje de error.

La solución es verificar que los puertos TCP 1433 o 5558 del cliente estén desbloqueados. Utilice uno de los siguientes métodos para hacer lo que se describe a continuación.

Use PowerShell para validar la conexión con punto de conexión TDS

  1. Abra una ventana de comandos de PowerShell.
  2. Ejecute el comando Test-connection.
    Test-NetConnection -ComputerName <environment>.crm.dynamics.com -port 1433

Si la conexión se realiza correctamente, se devolverá la línea "TcpTestSucceeded : True".

En algunos casos, el tráfico puede bloquearse directamente a nivel de IP. Para validar que la dirección IP también funciona, tome la dirección IP devuelta de la conexión de prueba de dominio anterior y reemplace el valor del parámetro ComputerName con la dirección IP.

  1. Tome la dirección devuelta del comando anterior como "RemoteAddress"
  2. Ejecute Test-NetConnection -ComputerName <RemoteAddress> -puerto 1433

Esto debería devolver "TcpTestSucceeded: True"

Establezca una sesión de telnet con el punto de conexión TDS

  1. En ordenador Microsoft Windows,, instale o habilite Telnet.
    1. Elija Inicio.
    2. Seleccione Panel de control.
    3. Elija Programas y características.
    4. Seleccione Activar o desactivar las características de Windows.
    5. Elija la opción Cliente Telnet.
    6. Seleccione Aceptar. Aparece un cuadro de diálogo para confirmar la instalación. El comando de Telnet debería estar disponible ahora.
  2. Ejecute un comando de Telnet en una ventana de comandos.
    telnet <environmentname>.crm.dynamics.com 1433

Si se establece correctamente la conexión, estará en una sesión de Telnet activa. De lo contrario, recibirá el error:

"Conectando a <environmentname>.crm.dynamics.com… No se pudo abrir la conexión para el host, en el puerto 1433: error de conexión".

Esto significa que el puerto se ha bloqueado en el cliente.

Consulte también

En qué se diferencia Dataverse SQL de Transact-SQL Introducción a las tablas virtuales (entidades)
Usar FetchXML para crear una consulta
Límites de la API de protección de servicio

Nota

¿Puede indicarnos sus preferencias de idioma de documentación? Realice una breve encuesta. (tenga en cuenta que esta encuesta está en inglés)

La encuesta durará unos siete minutos. No se recopilan datos personales (declaración de privacidad).