The type name 'source.unit.data' contains more than the maximum number of prefixes

Question

I have a linked service to a sql server where the database name is source.unit.data. We dont control this sql server, we are only allowed to read data from the source. The problem we have is that for some tables we can copy data from the sql server to our datawarehouse. For 2 tables we get the following error:

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'The type name 'source.unit.data' contains more than the maximum number of prefixes. The maximum is 1.',Source=,''Type=System.Data.SqlClient.SqlException,Message=The type name 'source.unit.data' contains more than the maximum number of prefixes. The maximum is 1.,Source=.Net SqlClient Data Provider,SqlErrorNumber=117,Class=15,ErrorCode=-2146232060,State=2,Errors=[{Class=15,Number=117,State=2,Message=The type name 'source.unit.data' contains more than the maximum number of prefixes. The maximum is 1.,},],'

All the source queries are exactly the same, so i dont think there is a problem with the queries. i would guess it has to do with the linked service databasename. When searching on the internet i found that [] should be added but then our connection is not working anymore.

{
    "name": "Source",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "parameters": {
            "Databasename": {
                "type": "String"
            }
        },
        "annotations": [],
        "type": "SqlServer",
        "typeProperties": {
            "connectionString": "integrated security=False;data source=SourceName;initial catalog=\"@{concat('source.',linkedService().Databasename,'.data')}\";user id=user",
            "encryptedCredential": "-"
        },
        "connectVia": {
            "referenceName": "shir",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Does anyone have any idea?

Answer 1

@Joël

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:

The type name 'source.unit.data' contains more than the maximum number of prefixes.

Solution:

"I figured out the issue. There was a column with the type source.unit.data.sys.geography instead of something like varchar or int. Since we don't need columns with this type, I excluded them from the source select statement, and now copying the data works".

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.

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hi ,

Thanks for reaching out to Microsoft Q&A.

It looks like the issue is with the way the initial catalog is being constructed in the linked service connection string. SQL Server does not support database names with multiple dot-separated prefixes when used in a three-part or four-part naming convention.

Possible Fixes:

1. Modify the Connection String Formatting

Try enclosing it in square brackets ([]) explicitly, like:

"initial catalog=@{concat('[source.',linkedService().Databasename,'.data]')};"

Or escape the dots properly using brackets:

"initial catalog=[source.unit.data];"

Ensure that your linked service connection still works after this change.

2. Use a Different Reference for the Database Name

Since SQL Server expects only one prefix in some contexts, try setting only "unit" as the database name. This assumes that "unit" is the actual database name and not just part of the schema.

"initial catalog=@{linkedService().Databasename};"

Try using: SELECT * FROM [source.unit.data].[TableName]

3. Verify the Database Name in SQL Server

If you do not control the SQL Server, ask the DB admin for confirmation:

• Is "source.unit.data" actually a single database name?
• Is "unit" the schema name instead?
• If this is a synonym, does a different reference exist?

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.

Answer 3

I found the issue. I checked the dataset and found the issue. There was a column with type source.unit.data.sys.geography instead of the likes of varchar, int etc. As we dont need the columns with this type i excluded them from the source select statement and copying the data works now.

Thank you for your help @Vinodh247