Tag: functions

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

Posted on by Ian

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.

Continue reading →

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

Posted on by Ian

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.

Continue reading →

Find Dependencies in SQL Server: sql_expression_dependencies

Posted on by Ian

In SQL Server, you can use the sys.sql_expression_dependencies system catalog view to return all dependencies on a user-defined entity in the current database. This includes dependences between natively compiled, scalar user-defined functions and other SQL Server modules.

You can use this view to:

  • Return entities that depend on a given entity
  • Return entities on which a given entity depends

So for example, you could use it to return all objects that reference a specific table. You could also use it to return all objects that a specific stored procedure references within its code.

Continue reading →

Example of SQL Server’s sys.dm_sql_referenced_entities() Returning an Entity that References a Linked Server

Posted on by Ian

One of the things about the sys.dm_sql_referenced_entities() system dynamic management function is that you can use it on cross-database and cross-server entities.

This means you can find referenced entities that are in a different database and even on a different server.

This article provides an example of sys.dm_sql_referenced_entities() returning a stored procedure that queries a database on a linked server.

Continue reading →

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

Posted on by Ian

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.

Continue reading →

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

Posted on by Ian

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.

Continue reading →

How to Use the ‘sp_server_info’ Stored Procedure in SQL Server

Posted on by Ian

In SQL Server the sp_server_info system stored procedure returns a list of attribute names and matching values for SQL Server, the database gateway, or the underlying data source. It returns a subset of the information provided by SQLGetInfo in ODBC.

Basically, it allows you to see information about SQL Server.

Continue reading →