SQL Error 117 - more than the maximum number of prefixes

 SQL Error 117 : The %S_MSG name '%.*ls' contains more than the maximum number of prefixes. The maximum is %d

The SQL Server error message 117 typically occurs when a table or column name includes more prefixes than SQL Server allows. In SQL Server, you can only have up to three parts in a qualified name: server.database.schema.object. If you exceed this limit, you will encounter error 117.

Example Scenario

Consider the following query:

SELECT * FROM server.database.schema.table.column

In this example, server.database.schema.table.column has five parts, which exceeds the maximum allowed number of four (including the server name).

Explanation

In SQL Server, the fully qualified names can have the following structure:

• server.database.schema.object

For example:

• MyServer.MyDatabase.dbo.MyTable
• MyDatabase.dbo.MyTable
• dbo.MyTable
• MyTable

The error occurs when you try to use more than the allowed number of parts.

Solution

Ensure you are not exceeding the four-part naming convention. Here are a few ways to correct the issue:

Option 1: Correct the Naming Convention

Adjust your query to use the correct number of parts:

-- Correct way with four parts
SELECT * FROM MyServer.MyDatabase.dbo.MyTable

-- Correct way with three parts
SELECT * FROM MyDatabase.dbo.MyTable

-- Correct way with two parts
SELECT * FROM dbo.MyTable

-- Correct way with one part
SELECT * FROM MyTable

Option 2: Simplify Your Query

Sometimes, you may include extra prefixes by mistake. Simplify your query to use the correct qualified names.

Example

If you have the following incorrect query:

SELECT * FROM server.database.schema.table.column

Correct it to:

SELECT column FROM server.database.schema.table

OR

SELECT column FROM database.schema.table

Summary

To resolve SQL Server error 117, ensure your object names do not exceed the four-part naming convention: server.database.schema.object. If you find yourself using more than four parts, revise your query to fit within this structure.

SQL Server UPDLOCK table hints

 To lock rows for update in SQL Server, you can use the WITH (UPDLOCK) hint. This hint is used in the SELECT statement to acquire an update lock on the rows, preventing other transactions from modifying them until the transaction is complete.

Here’s how you can use the WITH (UPDLOCK) hint in SQL Server:

Example: Using WITH (UPDLOCK) in SQL Server

1. Single Table Selection with Update Lock:

BEGIN TRANSACTION;

-- Acquire an update lock on the selected rows
SELECT *
FROM table_name
WITH (UPDLOCK)
WHERE condition;

-- Perform the update
UPDATE table_name
SET column_name = value
WHERE condition;

COMMIT TRANSACTION;

Joining Tables with Update Lock:

BEGIN TRANSACTION;

-- Acquire an update lock on the rows from both tables
SELECT *
FROM table1
JOIN table2 ON table1.id = table2.id
WITH (UPDLOCK)
WHERE table1.condition AND table2.condition;

-- Perform the update
UPDATE table1
SET table1.column_name = value
FROM table1
JOIN table2 ON table1.id = table2.id
WHERE table1.condition AND table2.condition;

COMMIT TRANSACTION;

Explanation

• BEGIN TRANSACTION: Starts a new transaction.
• WITH (UPDLOCK): Acquires an update lock on the selected rows.
• SELECT: Retrieves the rows to be locked.
• UPDATE: Performs the update on the locked rows.
• COMMIT TRANSACTION: Commits the transaction, releasing the locks.

Handling Set Operations

If you are dealing with set operations like UNION, INTERSECT, or EXCEPT, and you need to lock rows for update, you should ensure that the rows are locked before performing the set operation. SQL Server does not support the FOR UPDATE clause with set operations, so you need to handle locking and updating separately.

Example with Set Operations

If you want to lock rows and perform a set operation, consider breaking it into separate steps:

1. Lock rows using WITH (UPDLOCK).
2. Perform the set operation in a subsequent step.

BEGIN TRANSACTION;

-- Step 1: Lock rows in table1
SELECT *
FROM table1
WITH (UPDLOCK)
WHERE condition;

-- Step 2: Perform the set operation (e.g., UNION)
SELECT column1, column2
FROM table1
WHERE condition
UNION
SELECT column1, column2
FROM table2
WHERE condition;

-- Step 3: Update the locked rows
UPDATE table1
SET column_name = value
WHERE condition;

COMMIT TRANSACTION;

In this approach, the rows are locked in the first step, and then the set operation is performed. Finally, the rows are updated. This ensures that the rows are protected from concurrent updates during the transaction