sql - The object name contains more than the maximum number of prefixes. The maximum is 2 -

i facing similar issue many others error message "the object name contains more maximum number of prefixes. maximum 2."

my scenario is,

create procedure dbo.[usp_procedure] begin  declare @loadtime datetme2(7) set @loadtime = (select loadtime [linkedservername].[databasename].[schemaname].[tablename] tablename = 'xxxxxx')  insert [currentserver].[schemaname].[tablename] select column1, column2,   .... tablename join tablename1 on .... ... ...  end 

the table containing loadtime present in linked server/database , important value utilized further in clause of procedure via @loadtime. query select loadtime .... works fine when try on present server individually. when run part of procedure, procedure fails above mentioned error. linked server connection set , working fine. , procedure needs run on present server, removes scope of creating procedure on linked server. hope scenario articulate. replies welcome. in advance.

the line :

insert [currentserver].[schemaname].[tablename] 

contains server name no database name. should use

insert [mydatabase].[schemaname].[tablename] 

or

insert [currentserver].[mydatabase].[schemaname].[tablename] 

or even

insert [schemaname].[tablename] 

if inserting in same database stored procedure