PowerBIAPI - Operation is not supported for selector # - connection details contains parameters" - update ServerName in PowerBI Dataset of the Report

Question

I have a Problem Updating the Server details in the dataset of the uploaded power bi Report. Please Help.

Here I used 2 approaches.

Approach 1

1. Used the below method in Microsoft.PowerBI.Api.V2 UpdateDatasourcesInGroup

            public static void UpdateSqlDatabaseConnectionString(string WorkspaceId, string DatasetId, string Server, string Database)
            {
                var tokenCredentials = GetTokenCredentials();
                using (var pbiClient = new PowerBIClient(new Uri(ApiUrl), tokenCredentials.Item1))
                {
                    Datasource targetDatasource = pbiClient.Datasets.GetDatasourcesInGroup(WorkspaceId, DatasetId).Value.First();
   
                    string currentServer = targetDatasource.ConnectionDetails.Server;
                    string currentDatabase = targetDatasource.ConnectionDetails.Database;
   
                    if (Server.ToLower().Equals(currentServer.ToLower()) && Database.ToLower().Equals(currentDatabase.ToLower()))
                    {
                        Console.WriteLine("New server and database name are the same as the old names");
                        return;
                    }
   
                    DatasourceConnectionDetails connectionDetails = new DatasourceConnectionDetails
                    {
                        Database = Database,
                        Server = Server
                    };
   
                    UpdateDatasourceConnectionRequest updateConnRequest =
                      new UpdateDatasourceConnectionRequest
                      {
                          DatasourceSelector = targetDatasource,
                          ConnectionDetails = connectionDetails
                      };
   
                    UpdateDatasourcesRequest updateDatasourcesRequest = new UpdateDatasourcesRequest(updateConnRequest);
                    pbiClient.Datasets.UpdateDatasourcesInGroup(WorkspaceId, DatasetId, updateDatasourcesRequest);
                }
            }
   

Captured the request in fiddler

Request:

{
  "updateDetails": [
    {
      "connectionDetails": {
        "server": "OldServer",
        "database": "OldDatabase"
      },
      "datasourceSelector": {
        "datasourceType": "Sql",
        "connectionDetails": {
          "server": "NewServer",
          "database": "NewDatabase"
        },
        "gatewayId": "gatewayId",
        "datasourceId": "datasourceId"
      }
    }
  ]
} 

Response:

{"error":{"code":"InvalidRequest","message":"Operation is not supported for selector # - connection details contains parameters"}}

Approach 2

Called the Power BI rest API

                        public static void UpdateServerName_RestAPI(string groupId, string datasetId)
                        {
                            var tokenCredentials = InitPowerBI_New();
                            HttpResponseMessage response;
                            try
                            {
                                var httpClient = new HttpClient();
                
                                // Add AccessToken in header
                                httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", tokenCredentials.Item2);
                
                                var url = $"https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/Default.UpdateDatasources";
                
                                var form = prepareJsonForUpdateServerDetails();
                
                                var content = new StringContent(form, Encoding.UTF8, "application/json");
                
                                response = httpClient.PostAsync(url, content).Result;
                
                                response.EnsureSuccessStatusCode();
                                httpClient.Dispose();
                            }
                            catch (Exception)
                            {
                                
                            }
                        }

Request :

{
   "UpdateDetails":[
      {
         "datasourceSelector":{
            "datasourceType":"Sql",
            "connectionDetails":{
               "server":"OldServer",
               "database":"OldDatabase"
            }
         },
         "connectionDetails":{
            "server":"NewServer",
            "database":"NewDatabase"
         }
      }
   ]
}

Response:

{"error":{"code":"InvalidRequest","message":"Operation is not supported for selector # - connection details contains parameters"}}

Please Help. Thank You

Answer 1

This happens when the report queries connect to a database using a parameter. In this case you cannot update the connection, rather update the parameters or remove the connection from the query.

So, if you see in your query code something like Sql.Database(#"ServerName",... you must use Client.Datasets.UpdateParameters and pass in for details:

     UpdateMashupParameterDetails request = new()
                    {
                        Name = "ServerName",
                        NewValue = "newServerName"
                    };

and similarly for database. Further details https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/update-parameters