Code Project

Link Unit

Tuesday, October 23, 2012

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

 One of our database was in recovery mode because of transaction log size exceeded the free space available and transaction was incomplete.

We performed the following statements to get it in working state though not recommended approach.

ALTER DATABASE DB_name SET EMERGENCY;
GO
ALTER DATABASE DB_name SET SINGLE_USER;
Go
DBCC CHECKDB (DB_name, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
ALTER DATABASE DB_name SET ONLINE
go
ALTER DATABASE DB_name SET MULTI_USER;

Hope it helps

Monday, October 08, 2012

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider


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

Source:
http://www.sqlservercentral.com/Forums/Topic491682-149-1.aspx

Troubleshooting depends on how linked server is used i.e by using OPENQUERY or by four part name.
If openquery works but four part name queries do not, it could be that the ODBC driver is running out of memory.

Solution:
In SQL server enterprise manager go to "Server objects" - "Linked servers" - "providers". Then enable "Allow inprocess"

Hope it helps.