I'm getting the following error(s)
Msg 117, Level 15, State 1, Procedure lp_..._data, Line 153
The object name 'abcDBProd.Intermediate.dbo.upld_data' contains more than the maximum number of prefixes. The maximum is 2.
Msg 117, Level 15, State 1, Procedure lp_..._Tables, Line 520
The object name 'ABCDBPROD.Intermediate.dbo.UPLD_data' contains more than the maximum number of prefixes. The maximum is 2.
If I understand correctly the purpose/meaning of the error that I can't have more then 2 prefixes prior to the table name. In the first statement i'm using the name of the Server itself and in the second case the LinkedServer name. The confusion I've got is that i have many tables on this server/databse and they are all connected the same and none of them are causing this error. but this one is.
What do I do? Is it a permission issue or security issue? I'm not sure where to look.
Does it matter if the command calling the table, truncates, inserts or updates the data? Are there restrictions like I can't truncate but I can insert? Why does it work in some cases but not in others?
Linked tables are not allowed in some contexts, such as DROP TABLE and INSERT INTO. If you use a name that includes the server name, then you will get this error.
For instance, this is not allowed:
drop table abcDBProd.Intermediate.dbo.upld_data;
You may be able to work around this with openquery().