Linked server from SQL 2005 to SQL 2000

Linked Server from SQL 2005 to SQL 2000 failed with the following error:
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server.The provider supports the interface, but returns a failure code when it is used. [SQLSTATE 42000] (Error 7311) OLE DB provider "SQLNCLI" for linked server returned message "Unspecified error". [SQLSTATE 01000]
(Error 7412) OLE DB provider "SQLNCLI" for linked server returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.". [SQLSTATE 01000] (Error 7412).

Solution to this would be to check whether SP4 is installed on SQL 2000 Server , if not install the same.

If SP4 is already installed and still you receive the error , then please run the below mentioned wrapper procedure in SQL 2000 master database.

create procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as

declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

DMV for User Connections

There are 3 specific DMVs for user connections namely

sys.dm_exec_sessions
sys.dm_exec_connections
sys.dm_exec_requests

Let us each of the same in detail:

sys.dm_exec_sessions
This basically contains one row per session and is identified by session_id,this is equivalent to a SPID in SQL 2000

Also , in SQL 2000 system Session can be identified by SPIDs whose number fall below 50, but in SQL 2005 thats not the case and system sessions can be identified by column is_user_process
and has value zero if its a system session.And the Session_ID recorded in SQL Error log has 's' suffixed like spid43s in error logs

sys.dm_exec_connections
This can be called as a subset of the above DMV where this view contains only User connections filtered from the DMV sys.dm_exec_sessions.Each row in this table is uniquely identified by column connection_id which is a unique identifier.

Also,SQL 2005 supports MARS(Multiple Active Result Sets) where for each result set Ssession_ID is the same however connection_id is different , to identify the connection which resulted the result set can be identified using parent_connection_id column.

And one more nice value which is captured is the protocol used by the client to connect to SQL server which can be TCP/IP,Named Pipes,Shared Memory

sys.dm_exec_requests
This view reports the current active connections and the major information which is present in sysprocesses are present in this view.There exists a column named sql_handle if the value is not null then the query is still executing.

Once the session_ID is available we can find the underlying SQL command running using the DMV sys.dm_exec_sql_text

Use the below command to find the text
declare @sql_handle varbinary(64)
select @sql_handle = sql_handle from sys.dm_exec_requests where session_ID = 25
select * from sys.dm_exec_sql_text(@sql_handle)

What are DMVs

DMVs i.e. Dynamic Management Views expose the memory structures within SQL Server and is used to determine how SQL Server uses res0urces like CPU,Memory,IO,etc.

DMVs can be broadly classified into 2 types Server Level & Component Level where the perfomance related DMVs fall in the server level category

DMVs are views present in the schema sys and can be identified by dm_ prefix, so example of a DMV would be sys.dm_exec_sessions

Lets us see the various DMVs available in the coming posts.