SQL を使用したデータのクエリ

  • [アーティクル]

Microsoft Dataverse ビジネス レイヤーは、SQL データ接続をエミュレートする表形式データ ストリーム (TDS) エンドポイントを提供します。 SQL 接続では、ターゲット Dataverse 環境のテーブル データへの読み取り専用アクセスが提供されるため、Dataverse データ テーブルに対して SQL クエリを実行できます。 データのカスタムビューは提供されていません。 Dataverse エンドポイントの SQL 接続は、データアクセスに Dataverse セキュリティ モデルを使用しています。 ユーザーがアクセスできるすべての Dataverse テーブルのデータを取得できます。

注意

SQL Server Management Studio と .NET ライブラリ経由の SQL データ接続のみがプレビュー段階にあります。 Power BI は、一般公開されています。

前提条件

TDS エンドポイントを有効にするの設定を、ご使用の環境で有効にする必要があります。 既定ではこの設定は有効です。 詳細情報: 機能設定の管理

アプリケーション サポート

Power BIと SQL Server Management Studio の TDS (SQL) エンドポイント アプリケーションのサポートについては次で説明します。

SQL Server Management Studio (プレビュー)

また、SQL Server Management Studio (SSMS) バージョン 18.12.1 以降を Dataverse エンドポイント SQL 接続で使用することもできます。 SSMS と SQL データ接続の使用例を図に示します。

拡張アカウント テーブル。

セキュリティおよび認証

Microsoft Entra ID 認証のみサポートしています。 SQL 認証と Windows 認証はサポートされていません。 次の図は、SSMS で SQL 接続にサインインする方法の例を示しています。 サーバー名が組織のアドレス URL であることに注意してください。

接続ダイアログ。

注意

SSMS などのクライアント アプリケーションから TDS エンドポイントを使用するには、ポート 1433 または 5558、あるいはその両方を有効にする必要があります。 5558 番ポートだけを有効化した場合、SSMS の サーバーに接続する ダイアログでユーザーがそのポート番号をサーバー名に追加する必要があります - 例: myorgname.crm.dynamics.com,5558。

エンドポイント暗号化に関する情報: 転送中のデータ保護

テーブル データ クエリの例

これは、SSMS で構成されるクエリのいくつかの例です。 最初の画像は、エイリアスと結果の順序を使用した簡単なクエリを示しています。

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

エイリアスと順序を使用した簡単なクエリ。

次のクエリは、JOIN を示しています。

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

JOIN を使用した別のクエリ。

Power BI (一般提供)

Power Apps (https://make.powerapps.com) の Power BI で分析 オプション (データ > テーブル > Power BI で分析) を使用すると、Dataverse コネクタを使用して Power BI Desktop でデータを分析できます。 詳細: Power BI Desktop でのテーブル データの表示

注意

この機能を有効にするには、機能設定を管理する の TDS エンドポイント設定を参照してください。 有効にすると、Power Apps のコマンドバーで Power BI で分析 ボタンが表示されます。

サポートされている操作とデータ型

データを変更しようとする操作 (つまり、INSERT、UPDATE) は、この読み取り専用の SQL データ接続では機能しません。 Dataverse エンドポイントでサポートされている SQL 操作の詳細なリストについては、Dataverse SQL がいかに Transact-SQL と異なる点 を参照してください。

以下の Dataverse データ型は SQL 接続では対応していません: binary、image、sql_variant、varbinary、virtual、HierarchyId、managedproperty、file、xml、partylist、timestamp、choices。 また、現時点では、テーブル タイプ「virtual」および「audit」はサポートされていません。

ヒント

代わりに、partylist 属性は、次の例のように activityparty テーブルに結合して問い合わせを行うことができます。

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

検索列タイプの動作

Dataverse 検索列は、結果セットの <lookup> ID と <lookup> 名で表されます。

選択列タイプの動作

Dataverse 選択列は、結果セットの <choice> 名と <choice> ラベルで表されます。

ヒント

選択列のラベルを変更した後、テーブルのカスタマイズを公開する必要があります。

注意

クエリに多数の選択ラベルを含めると、パフォーマンスに大きな影響を与えます。 可能であれば、10 未満のラベルを使用することをお勧めします。 選択ラベルはローカライズされているため、ローカライズされた文字列を返すとコストが高くなります。

報告済みの SQL バージョン

Dataverse TDS エンドポイントは、Dataverse ビジネス ロジックを介して Microsoft SQL Server 読み取り専用クエリ機能をエミュレートします。 Dataverse は select @@version に対して現在の SQL Azure バージョン 12.0.2000.8 を返します。

操作ガイド

TDS エンドポイント を通じてデータを取得する場合、使用する必要がある主要なクエリ パターンがいくつかあります。 次のセクションで説明します。これらのクエリ パターンは、結果セットのパフォーマンスとサイズを管理します。

必要な列のみ

クエリを作成するときは、必要な列のみを返します。 この手法は、クエリの実行と、結果をクライアント アプリケーションに戻すことの両方に役立ちます。 一般に、クエリを 100 列未満に保つことをお勧めします。

列の選択

選択列が 2 つの列にフラット化され、使いやすくなりました。 ただし、選択列の値部分に対して集計とフィルターを実行することが重要です。 値の部分はインデックスを持つことができ、ベース テーブルに格納されます。 ただし、ラベル部分 ('choicecolumn' 名) は個別に保存されるため、取得にコストがかかり、インデックスを作成できません。 多数の選択ラベル列を使用すると、クエリの実行速度が低下する可能性があります。

上位 X を使用

データのテーブル全体を返そうとしないように、クエリで top 句を使用することが重要です。 たとえば、Select Top 1000 accountid,name From account Where revenue > 50000 を使用すると、結果が最初の 1000 アカウントに制限されます。

NOLOCK は使用しないでください

クエリを作成する際は、テーブル ヒント NOLOCK を使用しないでください。 このヒントにより、Dataverse によるクエリの最適化が妨げられます。

制限

Dataverse TDS エンドポイント には、厳密な最大サイズ制限がなくなりました。 代わりに、5 分間の固定タイムアウトがあります。 データ ストリーミングの導入により、固定の 5 分間のタイムアウト内で完了できる限り多くのデータを取得できます。 5 分以上かかる大規模なデータクエリには、Azure Synapse Link for Dataverse やデータフローなどのデータ統合ツールの使用を検討してください。 詳細情報: データのインポートとエクスポート

ヒント

返却されるデータのサイズを許容範囲内に収めるため、複数行のテキスト列や選択肢の列は可能な限り少なくしてください。

クエリ結果で返される日付は、協定世界時 (UTC) としてフォーマットされます。 以前は、日付は現地時間で返されていました。

SQL を使ってデータを取得しても、RetrieveMultipleRequest や RetrieveRequest のメッセージに登録されているプラグインがトリガーされません。 そのため、このようなプラグインによって通常実行されるクエリや結果の書き換えは、SQL クエリでは有効になりません。

TDS エンドポイントの実行を使用したクエリは、サービス保護 API 制限の下で実行されます。

TDS エンドポイント はエラスティック テーブルでは使用できません。 詳細情報: エラスティック テーブル

接続に関する問題のトラブルシューティング

いくつかの既知のエラー状態とその解決方法を見てみましょう。

注意

SSMS などのクライアント アプリケーションから TDS エンドポイントを使用するには、ポート 1433 または 5558、あるいはその両方を有効にする必要があります。 5558 番ポートだけを有効化した場合、SSMS の サーバーに接続する ダイアログでユーザーがそのポート番号をサーバー名に追加する必要があります - 例: myorgname.crm.dynamics.com,5558。

認証

Microsoft Entra ID 認証のみが Dataverse エンドポイント SQL 接続でサポートされています。 推奨される認証メカニズムは、多要素認証 (MFA) を備えた 「Microsoft Entra ID – ユニバーサル」 です。 しかしながら、「Microsoft Entra ID– パスワード」 は、MFA が構成されていない場合に機能します。 他の形式の認証を使用しようとすると、次のようなエラーが表示されます。

  • Microsoft Entra ID – 統合型 認証使用時にエラーが返されます。

「ログインに失敗しました: HTTP 要求はクライアント認証スキーム 「匿名」 で禁止されています。 RequestId: TDS;81d8a4f7-0d49-4d21-8f50-04364bddd370;2 Time: 2020-12-17T01:10:59.8628578Z (.Net SqlClient データ プロバイダー)」

  • SQL サーバー 認証の使用時にエラーが返される。

「ログイン エラー: この要求は認証されていません。 RequestId: TDS;918aa372-ccc4-438a-813e-91b086355343;1 Time: 2020-12-17T01:13:14.4986739Z (.Net SqlClient データ プロバイダー)」

  • Windows 認証使用時にエラーが返される。

「ログイン エラー: この要求は認証されていません。 RequestId: TDS;fda17c60-93f7-4d5a-ad79-7ddfbb917979;1 Time: 2020-12-17T01:15:01.0497703Z (.Net SqlClient データ プロバイダー)」

ブロックされたポート

ブロックされたポート エラーは、次のようになります。

エラー メッセージ。

解決策は、クライアントからの TCP ポート 1433 または 5558 のブロックが解除されていることを確認することです。 次のいずれかの方法で、ポートのブロックを解除してください。

PowerShell を使用して TDS エンドポイントとの接続を検証します

  1. PowerShell コマンド ウィンドウを開きます。
  2. 接続テストのコマンドを実行します。
    Test-NetConnection -ComputerName <environment>.crm.dynamics.com -port 1433

接続が成功すると、「TcpTestSucceeded : True」という行が返されます。

場合によっては、トラフィックが IP レベルで直接ブロックされる場合があります。 IP アドレスも機能していることを検証するには、上記のドメイン テスト接続から返された IP アドレスを取得し、ComputerName パラメーター値を IP アドレスに置き換えます。

  1. 上記のコマンドから返されたアドレスを "RemoteAddress" とします
  2. Test-NetConnection -ComputerName <RemoteAddress> -port 1433 を実行します

このコマンドは「TcpTestSucceeded: True」を返す必要があります。

TDS エンドポイントへの Telnet セッションを確立します

  1. Microsoft Windows コンピュータで、telnet をインストール/有効にします。
    1. 開始を選択します。
    2. コントロール パネルを選択します。
    3. プログラムと機能を選択します。
    4. Windows の機能の有効化または無効化を選択します。
    5. Telnet クライアント オプションを選択します。
    6. OK を選択します。 インストールを確認するダイアログ ボックスが表示されます。 telnet コマンドが利用可能になりました。
  2. [コマンド] ウィンドウで telnet コマンドを実行します。
    telnet <environmentname>.crm.dynamics.com 1433

接続に成功すると、アクティブな telnet セッションになります。 失敗した場合は、次のエラーが表示されます:

「<environmentname>.crm.dynamics.com への接続について、 ポート: 1433 でホストへの接続を開くことができませんでした: 接続に失敗しました」。

このエラーは、ポートがクライアントでブロックされていることを意味します。

参照

Dataverse SQL と Transact-SQL の違い 仮想テーブル (エンティティ) の使用を開始する
FetchXML を使用してデータのクエリを実行する
サービス保護の API 制限

注意

ドキュメントの言語設定についてお聞かせください。 簡単な調査を行います。 (この調査は英語です)

この調査には約 7 分かかります。 個人データは収集されません (プライバシー ステートメント)。