Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server " ". The provider supports the interface, but returns a failure code when it is used.

I was trying to run a job from a SQL Server 2008 R2 64bit Test machine which connects to a 32bit SQL Server 2000 Production machine through a Linked Server. When I tried running the query after creating the Linked Server, it throwed up the following error message.

OLE DB provider "SQLNCLI10" for linked server "FOCUS" returned message "Unspecified error".

OLE DB provider "SQLNCLI10" for linked server "FOCUS" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "FOCUS". The provider supports the interface, but returns a failure code when it is used.

Since I had ran this query directly on PROD, I was pretty sure that the query works fine. Then I found the link from MS site while googling.

http://support.microsoft.com/kb/906954

Then I found that it basically needs a stored procedure (sp_tables_info_rowset_64) to fix the issue between 32bit and 64bit. The complete procedure is available in multiple sites. But the fix only requires few lines of it. The code is listed below.

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

go

MvM

Restore nightly backups script

I am used to getting requests from users to restore Test databases with that of PROD. This could be ad-hoc or on schedule. The below script takes inputs as DB Name, Prod Backup location and backupfile name and restores the Test db. I have made modifications to suit my requirement.

----------------------------------------------------------------------------------------

-- Restore database from a backup file

-- NOTE: If the database gets locked in Single user mode, execute

-- the below command on it and it will restore the Multiuser mode:

-- ALTER DATABASE yourdatabasename SET MULTI_USER

-- Modify the @databaseName, @restoreDirectory & @backupFileName as per your requirement.

---------------------------------------------------------------------------------------

USE master

GO

-- Declare the variables

DECLARE @backupFileName varchar(100),

@restoreDirectory varchar(100),

@databaseDataFilename varchar(100),

@databaseLogFilename varchar(100),

@databaseDataFile varchar(100),

@databaseLogFile varchar(100),

@databaseName varchar(100),

@execSql nvarchar(1000)

-- Set the name of the database to restore

SET @databaseName = 'YOUR_DATABASE_NAME'

-- Set the path to the directory containing the database backup file.

-- IMPORTANT:: Make sure there is a \ character at the end of your directory path as below;

SET @restoreDirectory ='LOCATION OF BACKUPFILE' -- such as '\\NETWORK\BKUP\DB\'

-- Create the backup file name based on the restore directory, the database name and today's date & time.

-- The formatting has been done as per my backup file requirement.

SET @backupFileName = @restoreDirectory + @databaseName +'_backup_'+convert(varchar,getdate(), 112)+'.bak'

PRINT @backupFileName

-- Get the data file and its path

SELECT @databaseDataFile =RTRIM([Name]),

@databaseDataFilename =RTRIM([Filename])

FROM master.dbo.sysaltfiles AS files

INNER JOIN master.dbo.sysfilegroups AS groups

ON files.groupID = groups.groupID

WHERE dbid =

(

SELECT dbid

FROM master.dbo.sysdatabases

WHERE [Name] = @databaseName

)

-- Get the log file and its path

SELECT @databaseLogFile = rtrim([Name]),

@databaseLogFilename = rtrim([Filename])

FROM master.dbo.sysaltfiles AS files

WHERE dbid =

(

SELECT dbid

FROM master.dbo.sysdatabases

WHERE [Name] = @databaseName

)

AND

groupID = 0

PRINT 'Killing active connections to the "'+ @databaseName + '" database'

-- Create the sql to kill the active database connections

SET @execSql = ' '

SELECT @execSql = @execSql +'kill '+convert(char(10), spid)+ ' '

FROM master.dbo.sysprocesses

WHERE db_name(dbid)= @databaseName

AND dbid<> 0

AND spid <> @@spid

-- EXEC (@execSql)

PRINT 'Restoring "'+ @databaseName +'" database from "'+ @backupFileName + '" with '

PRINT ' DATA file "'+ @databaseDataFile +'" located at "'+ @databaseDataFilename +'"'

PRINT ' LOG file "'+ @databaseLogFile +'" located at "'+ @databaseLogFilename + '"'

SET @execSql = 'restore database [' + @databaseName + ']

from disk = ''' + @backupFileName + '''

with

file = 1,

move ''' + @databaseDataFile +''' to '+''''+ @databaseDataFilename + ''',

move ''' + @databaseLogFile +''' to '+''''+ @databaseLogFilename + ''',

norewind,

nounload,

replace'

PRINT @execsql

EXEC sp_executesql @execSql

---------------End of Restore script---------------------------------------

MvM

Biggest Tables in a database - SQL Server 2000

The below mentioned script is only for SQL Server 2000 and I already have posted a simpler script to find the same from SQL 2005/2008.

declare @id int

declare @type character(2)

declare @pages int

declare @dbname  sysname

declare @dbsize dec(15,0)

declare @bytesperpage dec(15,0)

declare @pagesperMB dec(15,0)

create table #spt_space

( objid int null, rows int null, reserved dec(15) null,

data dec(15) null, indexp dec(15) null, unused dec(15) null)

set nocount on

-- Create a cursor to loop through the user tables

declare c_tables cursor for

select id

from sysobjects

where xtype = 'U'

open c_tables

fetch next from c_tables into @id

while @@fetch_status= 0

begin

/* Code from sp_spaceused */

insert into #spt_space(objid, reserved)

select objid= @id, sum(reserved)

from sysindexes

where indid in (0, 1, 255) and id = @id

select @pages =sum(dpages)

from sysindexes where indid < 2and id = @id

select @pages = @pages +isnull(sum(used), 0)

from sysindexes where indid = 255 and id = @id

update #spt_space

set data = @pages

where objid= @id

/* index: sum(used) where indid in (0, 1, 255) - data */

update #spt_space

set indexp =(selectsum(used)

from sysindexes where indid in(0, 1, 255) and id = @id)- data

where objid= @id

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */

update #spt_space

set unused = reserved-(selectsum(used)

from sysindexes where indid in(0, 1, 255) and id = @id)

where objid= @id

update #spt_space

set rows= i.rows

from sysindexes i

where i.indid < 2 and i.id = @id and objid= @id

fetch next from c_tables into @id

end

select top 25

Table_Name = (select left (name,25) from sysobjects where id =objid),

rows = convert(char(11), rows),

reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0)+' '+'KB'),

data_KB = ltrim(str(data * d.low / 1024.,15,0)+' '+'KB'),

index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0)+' '+'KB'),

unused_KB = ltrim(str(unused * d.low / 1024.,15,0)+' '+'KB')

from #spt_space, master.dbo.spt_values d

where d.number = 1

and d.type= 'E'

order by reserved desc

drop table #spt_space

close c_tables

deallocate c_tables

MvM

Database Restore History

USE msdb;

GO

Select top 20

DBRestored= destination_database_name,

SourceFile= physical_name,

RestoreDate= restore_date

from RestoreHistory h

inner join BackupSet b

on h.backup_set_id = b.backup_set_id

inner join BackupFile f

on f.backup_set_id = b.backup_set_id

order by RestoreDate desc

GO

MvM