Error in Azure Data Factory: The document creation or update failed because of invalid reference to Azure SQL Linked Service

Question

Hello,

I was able to create Linked Service to Azure SQL DB as shown below, and was able to test the connection and run a stored procedure in it successfully using an Azure Data Factor (ADF) activity shown below.

{
    "name": "ls_sql_server_my_experimentations",
    "properties": {
        "description": "Linked service for Azure SQL Server resource, 'my-experimentations'.",
        "annotations": [],
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=my.database.windows.net;Initial Catalog=my-experimentations"
        }
    }
}

But when I try to save the pipeline that contains the activity above (JSON representation below):

{
    "name": "pt_test_sf",
    "properties": {
        "activities": [
            {
                "name": "Stored procedure1",
                "type": "SqlServerStoredProcedure",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "storedProcedureName": "[dbo].[pt_add_new_user]"
                },
                "linkedServiceName": {
                    "referenceName": "ls_sql_server_my_experimentations",
                    "type": "LinkedServiceReference"
                }
            }
        ],
        "annotations": []
    }
}

I keep getting this publishing error below:

Error code: BadRequest
Inner error code: BadRequest
Message: The document creation or update failed because of invalid reference 'ls_sql_server_jbrec_experimentations'.
Resource: /subscriptions/fd7f662f-3c43-4656-ada9-e7266c712d31/resourceGroups/Research-West/providers/Microsoft.DataFactory/factories/de-experimentations/pipelines/pt_test_sf

I have looked and tested everything to make sure that the linked service connects properly and that it can trigger the stored procedure. I am now at loss to figure out why I keep getting the error above and cannot save my pipeline in ADF....

Screenshots below show how the linked service looks in ADF UI.

If anyone has resolved such an issue in the past, I'd greatly appreciate if you could share how. Thank you!

Answer 1

Hi @plartoo-2257 ,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Issue: I was able to create Linked Service to Azure SQL DB as shown below and was able to test the connection and run a stored procedure in it successfully using an Azure Data Factor (ADF) activity shown below.

{
    "name": "ls_sql_server_my_experimentations",
    "properties": {
        "description": "Linked service for Azure SQL Server resource, 'my-experimentations'.",
        "annotations": [],
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=my.database.windows.net;Initial Catalog=my-experimentations"
        }
    }
}

But when I try to save the pipeline that contains the activity above (JSON representation below):

JSONAI ConvertCopy

{
    "name": "pt_test_sf",
    "properties": {
        "activities": [
            {
                "name": "Stored procedure1",
                "type": "SqlServerStoredProcedure",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "storedProcedureName": "[dbo].[pt_add_new_user]"
                },
                "linkedServiceName": {
                    "referenceName": "ls_sql_server_my_experimentations",
                    "type": "LinkedServiceReference"
                }
            }
        ],
        "annotations": []
    }
}

publishing error below:

textAI ConvertCopy

Error code: BadRequest
Inner error code: BadRequest
Message: The document creation or update failed because of invalid reference 'ls_sql_server_jbrec_experimentations'.
Resource: /subscriptions/fd7f662f-3c43-4656-ada9-e7266c712d31/resourceGroups/Research-West/providers/Microsoft.DataFactory/factories/de-experimentations/pipelines/pt_test_sf

I have looked and tested everything to make sure that the linked service connects properly and that it can trigger the stored procedure. I am now at loss to figure out why I keep getting the error above and cannot save my pipeline in ADF....

If anyone has resolved such an issue in the past, I'd greatly appreciate if you could share how. Thank you!

Solution: I tried both of your suggestions above, but none of them worked. Eventually, I created a new linked service (with the SAME configurations as 'ls_sql_server_my_experimentations'), save it first and then use it in the pipeline. That worked. It seems like when 'ls_sql_server_my_experimentations' was published/saved, there must have been an internal bug within Azure that did not save it properly.

All is well now and thank you for your suggestions! :)

P.S. I wonder if ADF publish process creates a dependency graph in the background and save things in that order (i.e. linked services are saved first and then workflow activities second, etc.) Otherwise, a lot of folks will run into a bug/error like what I encountered.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps!

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Thank you, Smaran Thoomu! I tried both of your suggestions above, but none of them worked. Eventually, I created a new linked service (with the SAME configurations as 'ls_sql_server_my_experimentations'), save it first and then use it in the pipeline. That worked. It seems like when 'ls_sql_server_my_experimentations' was published/saved, there must have been an internal bug within Azure that did not save it properly.

All is well now and thank you for your suggestions! :)

P.S. I wonder if ADF publish process creates a dependency graph in the background and save things in that order (i.e. linked services are saved first and then workflow activities second, etc.) Otherwise, a lot of folks will run into a bug/error like what I encountered.