CODEWITHSUNDEEP
sqlsql-server
Maris Markur
Maris Markur

Reputation: 11

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

I am facing a similar issue like many others with error message "The object name contains more than the maximum number of prefixes. The maximum is 2."

My scenario is,

CREATE PROCEDURE DBO.[USP_PROCEDURE]
AS
BEGIN

DECLARE @LOADTIME DATETME2(7)
SET @LOADTIME = (SELECT LOADTIME FROM [LINKEDSERVERNAME].[DATABASENAME].[SCHEMANAME].[TABLENAME] WHERE TABLENAME = 'XXXXXX')

INSERT INTO [CURRENTSERVER].[SCHEMANAME].[TABLENAME]
SELECT COLUMN1, COLUMN2,  
.... FROM TABLENAME JOIN TABLENAME1 ON ....
...
...

END

The table containing loadtime is present in Linked Server/Database and it is important value which is utilized further in where clause of my procedure via @LOADTIME. The query SELECT LOADTIME .... works fine when i try on present server individually. But when i run it as part of procedure, my procedure fails with above mentioned error. Linked server connection is set up and working fine. And my procedure needs to run on present server, which removes the scope of creating procedure on linked server. Hope my scenario is articulate. All replies are welcome. Thanks in advance.

Upvotes: 1

Views: 10249

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131344

The line :

INSERT INTO [CURRENTSERVER].[SCHEMANAME].[TABLENAME]

Contains a server name but no database name. You should use 

INSERT INTO [MYDATABASE].[SCHEMANAME].[TABLENAME]

or 

INSERT INTO [CURRENTSERVER].[MYDATABASE].[SCHEMANAME].[TABLENAME]

or even

INSERT INTO [SCHEMANAME].[TABLENAME]

if you are inserting in the same database as the stored procedure

Upvotes: 1

Related Questions