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.

SQL Server Level Permissions

In SQL 2005, there are various server level permissions which can be provided to any login or role and the same can be provided by the GRANT statement executed from the master database.

If executed at different database this is the error received.

Server: Msg 4621, Level 16, State 10, Line 1
Permissions at the server scope can only be granted when the current database is master

USE master
GRANT CONTROL SERVER TO GaneshJayaraman
GO

Permissions which can be provided are as follows:
ADMINISTER BULK OPERATIONS
ALTER ANY CONNECTION
ALTER ANY CREDENTIAL
ALTER ANY DATABASE
ALTER ANY ENDPOINT
ALTER ANY EVENT NOTIFICATION
ALTER ANY LINKED SERVER
ALTER ANY LOGINALTER RESOURCES
ALTER SERVER STATE
ALTER SETTINGS
ALTER TRACE
AUTHENTICATE SERVER
CONNECT SQL
EXTERNAL ACCESS ASSEMBLY
SHUTDOWN
UNSAFE ASSEMBLY
VIEW ANY DEFINITION
However these all are implicitly provided when CONTROL SERVER permission is available

CREATE ANY DATABASE
When the same permission is provided ALTER ANY DATABASE rights are also provided

CREATE DDL EVENT NOTIFICATION
When the same permission is provided ALTER ANY EVENT NOTIFICATION rights are also provided

CREATE ENDPOINT
When the same permission is provided ALTER ANY ENDPOINT rights are also provided

CREATE TRACE EVENT NOTIFICATION
When the same permission is provided ALTER ANY EVENT NOTIFICATION rights are also provided

VIEW ANY DATABASE
When the same permission is provided VIEW ANY DEFINITION rights are also provided

VIEW SERVER STATE
When the same permission is provided ALTER SERVER STATE rights are also provided

These are all very useful as the user can be given specific permission instead of providing "sa" and is useful when new databases are added, as DB permission exists for all the DBs in the server

Server Settings

Server Settings Dialog has changed a bit in SQL 2005 when compared to SQL 2000,let us view the graphical digram to have a look and feel of how SQL 2005 is different.

SQL 2000 Server Properties from SQL 2000 Enterprise Manager

SQL 2005 Server Properties from Management Studio

Here we can see tab format is changed to side panels format all the side panel menus are almost similar to tabs except for Advanced properties which is present in SQL 2005,which has it got its name renamed from SQL 2005 Server Settings tab.

And the next main new panel introduced is Permissions where various server level permissions are provided to various logins and roles