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.


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.


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.

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

Hope it helps.

Thursday, August 09, 2012

Login failed for user 'domain\machinename$'

We faced an error "Login failed for user 'DOMAIN\MACHINENAME$'" while for Web Application setup we were having SQL Server on one machine and IIS server on another.

NETWORK SERVICE and LocalSystem will authenticate themselves always as the correpsonding account locally (builtin\network service and builtin\system) but both will authenticate as the machine account
 if connecting to remote system.

it means that a process running as NETWORK SERVICE or as LocalSystem has accessed a remote resource, has authenticated itself as the machine account and was denied authorization.

When access is denied to a machine account, then access must be granted to the machine account. If the server refuses to login 'DOMAIN\MACHINE$', then you must grant login rights to 'DOMAIN\MACHINE$' not to NETWORK SERVICE. Granting access to NETWORK SERVICE would allow a local process running as NETWORK SERVICE to connect, not a remote one, since the remote one will authenticate as, you guessed, DOMAIN\MACHINE$.

Create login in SQL Server for client machine .
Make required "User Mapping" for databases to whatever we wish to access

List of all probable reasons and solutions
For us this option worked : If SQL Server is running on a remote computer and the Web server is running IIS 6.0, give the Web server's machine account login privileges on the remote computer. The machine account is referenced as DOMAIN\MACHINENAME$.

Hope it helps.

Friday, August 03, 2012

SalesForce Query is either selecting too many fields or the filter conditions are too complicated.

I get the following error when I try to replicate the custom object: Query is either selecting too many fields or the filter conditions are too complicated.

Even a simple query like Select * from SalesForce...Object was also returning same error.

This error is from the server and occurs when the table has too many formula fields for the salesforce server to return query results. Internally at the salesforce server, when a query occurs the select list is modified and the calculated fields are replaced with their formulas. Then the sf server tries to execute it and determines that the query is too complex.
You could try using a feature of DBAmp called Column Subsets which allow you to replicate subsets of columns of a table and then glue them together locally. This effectively decreases the number of formula fields in the query. See Column Subset Views in Chapter 2 of the DBAmp doc.

exec sf_replicate 'SALESFORCE','Object_ColumnSubsetAM'
exec sf_replicate 'SALESFORCE','Object_ColumnSubsetNZ'

and then use following to construct the table

Select column1,column2,column3 into Object
from Object_ColumnSubsetAM AM
inner Join Object_ColumnSubsetNZ NZ on =

Alternatively, you can just construct multiple select statements yourself with subsets of the columns.

Monday, May 28, 2012

Error: The GetBytes function can only be used on columns of type Text, NText, or Image

On running a simple statement like

Select * from TableABC

and following error was encountered.

An error occurred while executing batch. Error message is: Invalid attempt to GetBytes on column 'XYZ'.
Error: The GetBytes function can only be used on columns of type Text, NText, or Image.

After we checked the version of SSMS we noticed that connecting to the 2008 instance with a 2005 SSMS client was the issue. Essentially it means that SSMS doesn't understand one of the column data types coming back to it like datetime2

Connected with SSMS 2008 and it all worked again.