SQL Error 119 - Must pass parameter number 2 and subsequent parameters

 Msg 119, Level 15, State 1, Line 1

Must pass parameter number 2 and subsequent parameters as '@name = value'.  After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Reason : If we want to use variable names while passing parameters to stored procedure then we must have to use variable in all the parameters.

Suppose we have created a simple stored procedure uspDisplayData in SQL Server

CREATE PROCEDURE uspDisplayData(
     @Param1 AS VARCHAR(100),
     @Param2 AS INT
)
AS
SELECT @Param1, @Param2

Now if we will execute this stored procedure:

EXECUTE dbo.uspDisplayData @Param1 = 'Sql Server',2

We will get error message like:

Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Solution:

Correct ways to pass the parameters are:

EXECUTE dbo.uspDisplayData 'Sql Server',2

OR

EXECUTE dbo.uspDisplayData @Param1 = 'Sql Server',@Param2 = 2

This will result correct result and execute without error. 

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

Msg 117, Level 15, State 2, Line 4
The object name 'Object Name' contains more than the maximum number of prefixes.  The maximum is 2.

Lets see the below example. Trying Select the records.

select * from [SANTANA\MSSQLSERVER17].Rohit.dbo.Employee.Employeeid
where EmployeeID=101

Output:

Msg 117, Level 15, State 1, Line 2
The object name 'SANTANA\MSSQLSERVER17.Rohit.dbo.Employee.Employeeid' contains more than the maximum number of prefixes. The maximum is 3.

In the above example Employeeid is an invalid selection results in the error.

This error usually comes up when you are trying to perform a SELECT..INTO across a linked server. The problem is that a 4 part name cannot be used as the “INTO” parameter. This will fail.
The trick is to create the table on the remote server, then perform an INSERT INTO.