If you encounter error Msg 491, Level 16 “A correlation name must be specified for the bulk rowset in the from clause” in SQL Server, it’s probably because you’re trying to read a file without using a correlation name.

When you use the OPENROWSET() function with the BULK option, you must provide a correlation name (also known as a range variable or alias) in the FROM clause.

To fix this error, simply provide a correlation name/alias for your query.

Example of the Error

Here’s an example of code that causes this error.

SELECT BulkColumn FROM OPENROWSET (
    BULK '/var/opt/mssql/bak/pets.json', 
    SINGLE_CLOB
    );

Result:

Msg 491, Level 16, State 1, Line 4
A correlation name must be specified for the bulk rowset in the from clause.

The error occurred because I forgot to include the correlation name.

The Solution

As mentioned, to fix this problem, all we need to do is provide a correlation name (also known as a range variable or alias).

SELECT BulkColumn FROM OPENROWSET (
    BULK '/var/opt/mssql/bak/pets.json', 
    SINGLE_CLOB
    ) AS MyAlias;

Result:

+--------------+
| BulkColumn   |
|--------------|
| { 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "id" : 1, "name" : "Fetch", "sex" : "Male" },
            { "id" : 2, "name" : "Fluffy", "sex" : "Male" },
            { "id" : 3, "name" : "Wag", "sex" : "Female" }
        ]
    }
}              |
+--------------+