Myth : NOLOCK as silver bullet for performance

It is general practice to recommend NOLOCK to improve performance of query, though it result in dirty reads. But if dirty reads are fine in context of a code segment NOLOCK is recommended most of time. While looking for Error 605 explanation and remedy stumbled upon this on microsoft site.

Error 601

Severity Level 12

Message Text

Could not continue scan with NOLOCK due to data movement.

Explanation

When scanning with the NOLOCK locking hint or with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan to be deleted. When this happens, Microsoft SQL Server is not able to continue the scan.

Action

This error aborts the query. Either resubmit the query or remove the NOLOCK locking hint.

Database 'XYZ' is Already Open and Can Only Have One User at a Time

Database 'XYZ' is Already Open and Can Only Have One User at a Time

Reason:
The reason you are receiving this error is that your DB is set to single user mode.

Solution:
#1 To correct this error go into Enterprise Manager and right click on the DB 'XYZ' and go to Properties, than go to the options tab and uncheck the Single User check box.

#2 If we wish to from a separate connection

a) find who has what connections

exec sp_who

b) Disconnect the SPID which is using database 'XYZ'

kill 53

c) set database back to multiuser

alter database XYZ set multi_user

The path of the item '/Reports/Salesreport' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath)

One of SSRS report was crashing with the message "must be less than 260 characters". This message is bit confusing as we were having many urls which are bigger than 260 character limit and yet all those reports are rendered successfully.

Even the same report was successful with one set of parameter yet failed with another set of parameter. So, we arrived at conclusion that issue is with parameter value not URL length.

Reason : One or more parameters passed through query string may have restricted characters, which causes this particular issue.

Solution: Encode the parameter values before passing it to report. We used HttpUtility.UrlEncode(parameter.ToString()) while setting the value.

Hope it Helps

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

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.

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.

Reason:
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$.

Solution:
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 http://msdn.microsoft.com/en-us/library/ab4e6cky%28v=vs.80%29.aspx
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.

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.



Reason
This error is from the salesforce.com 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.
 
Solution
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 AM.id = NZ.id

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