how to

Don’t Use sp_depends in SQL Server (it’s Deprecated)

by

SQL Server has a system stored procedure called sp_depends that returns information about dependencies between objects within the current database.

Microsoft has marked this stored procedure as deprecated, which means that it’s in maintenance mode and may be removed in a future version of SQL Server. You should avoid using sp_depends in new development work, and you should modify applications that currently use it to use either sys.dm_sql_referencing_entities() or sys.dm_sql_referenced_entities() instead (depending on whether you need referencing entities, or referenced entities to be returned.

Read more

Don’t Use sys.sql_dependencies in SQL Server (it’s Deprecated)

by

SQL Server has a system catalog view called sys.sql_dependencies that returns information about dependencies between entities.

Microsoft has marked this view as deprecated, which means that it’s in maintenance mode and may be removed in a future version of SQL Server. You should avoid using sys.sql_dependencies in new development work, and you should modify applications that currently use it to use sys.sql_expression_dependencies instead.

Read more

Using Extended Events to Log Deprecated Features Being Used in a SQL Server Instance (T-SQL Example)

by

Extended events is a lightweight performance monitoring system that enables users to collect data needed to monitor and troubleshoot problems in SQL Server.

This article demonstrates how extended events can be used to create a log file that contains all deprecated features that are still being used in an instance of SQL Server. The log records all occurrences since the event session was started.

If you just want a quick count of how many times a deprecated feature has been used since SQL Server was started up, see Quickest Way to Find Deprecated Features Still Being Used in a SQL Server Instance.

But if you need a more detailed log that includes stuff like; the SQL statement used that contains the deprecated feature, the database it was run against, the user that ran it, the time it was run, etc, read on.

Read more

Quickest Way to Find Deprecated Features Still Being Used in a SQL Server Instance (T-SQL Example)

by

In SQL Server, the sys.dm_os_performance_counters system dynamic management view returns the performance counters maintained by the server.

One of the many things you can do with sys.dm_os_performance_counters is return a list of deprecated features in the current instance of SQL Server. You can also use this list to see how many times a deprecated feature has been used since SQL Server was started.

This is quite possibly the quickest way to find out if you’re using a deprecated feature in SQL Server.

The view returns a row per performance counter maintained by the server. The SQLServer:Deprecated Features object provides a counter to monitor the features designated as deprecated. It has a cntr_value column, which provides a usage count that lists the number of times the deprecated feature was encountered since SQL Server last started.

Therefore, by running a query against this view, we can return all deprecated features along with a count of how many times each one was encountered since SQL Server last started.

Read more

How to List the Deprecated Features in a SQL Server Instance using T-SQL

by

In SQL Server, you can use the sys.dm_os_performance_counters system dynamic management view to return a list of the features designated as deprecated in the current instance of SQL Server.

To return just the deprecated features, filter the view to just the SQLServer:Deprecated Features object.

The view also includes a counter that lists the number of times each deprecated feature was encountered since SQL Server last started. This can be helpful for identifying whether or not your application is still using any deprecated features.

Running this on SQL Server requires VIEW SERVER STATE permission.

Read more

How to Find the Optimal Unique Identifier in a Table in SQL Server: sp_special_columns

by

In SQL Server, you can use the sp_special_columns system stored procedure to identify a unique identifier for the table. Specifically, it returns the optimal set of columns that uniquely identify a row in the table. It also returns columns automatically updated when any value in the row is updated by a transaction.

sp_special_columns is equivalent to SQLSpecialColumns in ODBC.

If there are no columns that can uniquely identify the table, the result set is empty.

Read more

How to Fix “Server is not configured for DATA ACCESS” in SQL Server

by

The “Server is not configured for DATA ACCESS” error in SQL Server is a common error when trying to run a distributed query against a server that has its data access setting disabled.

The error will have the name of the server that you’re trying to access. For example, if your server name is SQL01, the error will read something like this:

Msg 7411, Level 16, State 1, Line 1
Server 'SQL01' is not configured for DATA ACCESS.

“Data access” is a setting that enables and disables a linked server for distributed query access.

A common cause of this error is when you try to run OPENQUERY() against the local server. If you really want to run OPENQUERY() against the server, you’ll need to ensure data access is enabled for that server – even if it’s your local server.

Read more

2 Ways to Check if Data Access is Enabled in SQL Server (T-SQL Examples)

by

In SQL Server, there’s a “data access” setting that enables and disables a linked server for distributed query access. If you ever get the “Server is not configured for DATA ACCESS” error, it’s probably because you’re trying to run a distributed query against a linked server that isn’t configured for data access. This can also happen when you try to run OPENQUERY() against your local server.

You can use sp_serveroption to enable or disable data access on a given server. However, you might want to check the existing settings before you start changing them. The following examples show how to do that.

Read more

How to Enable/Disable Data Access in SQL Server (T-SQL Example)

by

SQL Server has a “data access” configuration option that enables and disables a linked server for distributed query access.

If you ever get a “Server is not configured for DATA ACCESS” error, you’ll probably need to enable data access for the linked server you’re trying to run the distributed query against. Conversely, there may also be times where you need to disable data access.

To enable or disable data access, use the sp_serveroption system stored procedure. Execute it against the server that you intend to run distributed queries from. The following example demonstrates how to do this.

Read more