SQL Server

Troubleshooting problems with views in SQL Server 2016

Troubleshooting problems with views in SQL Server 2016

If you have problems with views in SQL Server 2016, review this troubleshooting
checklist to find potential solutions.

1. Install the latest SQL Server 2016 service pack.

Because many bugs with views in SQL Server 2016 were fixed in SQL Server service
packs, you should install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2016 service pack was
service pack 1. You can download the SQL Server 2016 service pack 1 at here:
https://support.microsoft.com/en-us/kb/3182545

2. When you run log shipping from many databases to one secondary server, the
deadlock may occur in sys.dm_database_encryption_keys dynamic management view.

This is SQL Server 2016 bug. To work around this problem, you can decrease the
number of primary databases that are merged on the secondary server.

3. After you change the computer name, the SQL Server login name is not updated
in the sys.syslogins catalog compatibility view.

This problem occurs when the SQL Server login uses Windows Authentication mode
from a Windows local user. To work around this problem, you can correct the login
name manually by using the ALTER LOGIN statement.

4. You can find out that the query does not use the indexed view in
SQL Server 2016 Enterprise Edition.

This problem occurs when you define an indexed view that involves an ISNULL
function on a nonnullable column. To work around this problem, you should rewrite
the view definition without using the ISNULL function on the nonnullable column,
and then re-create the index.

5. When you update a view in SQL Server 2016 an index corruption may occur.

To work around this problem, you can use DBCC CHECKDB command with the
REPAIR_REBUILD option to try to correct the corruption or restore the latest
backup that does not have the corruption.

6. The SQL Server 2016 session may be blocked unexpectedly when you execute
an ALTER VIEW statement on a view.

This problem occurs when two or more SQL Server sessions execute an ALTER VIEW
statement against the same view at the same time. To work around this problem,
avoid executing an ALTER VIEW statement against the same view at the same time.
To resolve this problem, install the latest SQL Server service pack.

7. When you run a distributed partitioned view query and enable the
“lazy schema validation” option, an access violation may occur.

This is SQL Server 2016 bug. To work around this problem, you can disable the
“lazy schema validation” option for all linked servers that are involved in the
distributed partitioned view query. To resolve this problem, install the latest
SQL Server service pack.

8. When you create a Master Data Services (MDS) subscription view, you may
receive a JavaScript error.

This is SQL Server 2016 bug. This bug was fixed in Cumulative update package 1
for SQL Server 2016. You can download the Cumulative Update package 1 for
SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

9. The error 7330 occurs when you query the view that is created by the Data
Feed Publishing Wizard.

This is the error message text: “Cannot fetch a row from OLE DB provider “SSISOLEDB”
for linked server “Default Linked Server for Integration Services”.” This bug was
fixed in Cumulative update package 1 for SQL Server 2016. You can download the
Cumulative Update package 1 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

10. When you run a query against the sys.sysindexes view of the database, the query
session is terminated.

This problem occurs when a database contains Memory-optimized tables and table types.
In this case, you receive an error message: “A system assertion check has failed.
Check the SQL Server error log for details.” This bug was fixed in Cumulative update
package 1 for SQL Server 2016. You can download the Cumulative Update package 1 for
SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

11. When you query a view that references the identity column of a system-versioned
temporal table, an infinite recompile may occur.

This is SQL Server 2016 bug. This bug was fixed in Cumulative update package 1
for SQL Server 2016. You can download the Cumulative Update package 1 for
SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3164674/cumulative-update-1-for-sql-server-2016

12. The error occurs when you run a query that includes a hint against a view that
references at least one temporal table in a different database.

This is the error message text: “A transport-level error has occurred when receiving
results from the server.” This bug was fixed in Cumulative update package 4 for
SQL Server 2016. You can download the Cumulative Update package 4 for SQL Server 2016
at here:
https://support.microsoft.com/en-us/help/3205052/cumulative-update-4-for-sql-server-2016

13. You cannot run a data cleanse against a view in SQL Server 2016 Data Quality Services.

This is SQL Server 2016 bug. This bug was fixed in Cumulative update package 4
for SQL Server 2016. You can download the Cumulative Update package 4 for
SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/3205052/cumulative-update-4-for-sql-server-2016
To work around this problem, you can use a table instead of a view to perform the cleanse.

14. A memory leak occurs when you query the sys.dm_sql_referenced_entities view.

In this case, you receive the following error message: “There is insufficient
system memory in resource pool ‘default’ to run this query.” This bug was fixed
in Cumulative update package 5 for SQL Server 2016. You can download the Cumulative
Update package 5 for SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/4013105/cumulative-update-5-for-sql-server-2016

15. When you use the sys.column_store_segments catalog view, incorrect values
may be displayed in the column_id column.

This is SQL Server 2016 bug. This bug was fixed in Cumulative update package 5
for SQL Server 2016. You can download the Cumulative Update package 5 for
SQL Server 2016 at here:
https://support.microsoft.com/en-us/help/4013105/cumulative-update-5-for-sql-server-2016

Facebooktwittergoogle_plusredditpinterestlinkedinmail