Home Forums SQL Server 7,2000 General The object name ... contains more than the maximum number of prefixes. The maximum is 2. RE: The object name ... contains more than the maximum number of prefixes. The maximum is 2.

  • The error I got was very similar. I was trying to do the following update and "push" the data across to a linked server:

    update ServerName.AAD.dbo.t_employee

    set ServerName.AAD.dbo.t_employee.work_shift = wa.work_shift

    from t_employee wa

    where ServerName.AAD.dbo.t_employee.id = wa.id

    and wa.id = '105'

    I was getting the following:

    Server: Msg 117, Level 15, State 2, Line 4

    The number name 'ServerName.AAD.dbo.t_employee' contains more than the maximum number of prefixes. The maximum is 3.

    I couldn't find any other threads with my answer. Maybe I'm alone on this but it took me hours to find this solution, so I thought I'd post it here in hopes it helps out the next person searching on this.

    It's a simple, but not obvious, answer. Put the table you are updating in the FROM clause with an alias and update the alias, as follows:

    update la

    set la.work_shift = wa.work_shift

    from ServerName.AAD.dbo.t_employee la, t_employee wa

    where la.id = wa.id

    and wa.id = '105'

    Beautiful, ins't it? That is when you have the answer.