Friday, June 24, 2011

Can't consume Oracle Synonyms with a SQL Server linked server endpoint

Generally, I'd prefer to post how you *can* do something, but to save anyone having the same dilemma some time -- SQL Server linked server objects can't consume Oracle Synonyms.  This is due to the same reason you can send a "select top 10 *" query through a linked server even though that's just gibberish to Oracle.  Commands directly to a linked server object rely on SQL Server objects and syntax regardless of the fact that the back end is Oracle (although still magically persist case sensitivity).

If you need to use a synonym in a query against an Oracle linked server, you will have to use openquery.  This isn't pretty for more complex queries/loads.  The syntax for using openquery for this is
  • select * from openquery(LinkedServerName, 'select object from synonym.object')

It is also possible (but bad behavior) to look directly at the Synonym and find the referenced object (easiest in a product like toad, but if you use sqlplus the command is DESC synonymname).  Again, the developer who created the synonym probably created it because he wants you to use it, so unless you are the developer for the target server, you should really check this is okay.

Tuesday, June 14, 2011

The prize for weirdest DPM error goes to...

Problem: In DPM 2010 (and DPM 2007),
1.  An existing SQL server or SharePoint farm begins failing recovery points and throwing VSS errors.   A new attempt to create a DPM protection group for SQL server fails.
or
2.  DPM does not find a SQL server on a server that is running SQL Server.
3.  DPM cannot create a SharePoint replica because of a VSS error on the SharePoint database server.

The DPM server will register the error "The VSS application writer or the VSS provider is in a bad state. Either it was already in a bad state or it entered a bad state during the current operation. (ID: 30111)" when attempting to create a recovery point, or when attempting to manually rebuild the replica.  This error can be caused by a lot of different problems including just needing to restart the VSS services (for SQL server, that's the VSS service itself, the SQL VSS service, and then the DPMRA service).


A check of the VSS writers on the SQL server will not show the SQL Writer in the list.  From an administrative command prompt type "vssadmin list writer" and check for a sql writer.  If it's missing, and you know the agent was installed correctly, you might be running into the same problem I did.

Solution:

I have a hard time believing the reason for this problem, but...this problem is caused by a trailing space at the end of a database name on the SQL server.  It sounds like a joke, but it's not.  This might not be an easy problem to fix (if applications are using that database), so you may need to start old-school SQL Agent backups until the problem can be fixed.  The problem is documented for SQL 2005/DPM 2007, but I "accidentally confirmed" that it is also an issue on DPM 2010 and SQL 2008 R2.  It also affects upstream SharePoint farm replicas.  From what I can find removing the trailing space, is the only solution to the problem.

It's bad behavior to have a trailing space on a database name.  It's certainly not something you would intentionally put into production, but--hypothetically speaking-- if you were, say, doing a test restore of a database, weren't being careful, and accidentally got whitespace in the name, you could take out all other backups for the whole SQL Server (and SharePoint Farm, if SharePoint databases are hosted on the SQL Server).  This seems a bit like getting grounded for life, as a consequence of being 3 minutes past curfew.  Nobody's saying trailing spaces in database names are a good idea, but to essentially give the whole server the silent treatment (including not logging any useful errors) seems a little harsh.

Here's a query to quickly check for trailing spaces in database names on a SQL server.

select name from sys.databases where name like '% '
 

Thursday, June 9, 2011

Query to get non-relative navigation links in a wss 3.0 database

Quick Description:  If non-relative  links are hard-coded in SharePoint navigation, problems will occur if the site is moved (for instance in a migration to the new version).  It is hard to identify and locate those links before they are found when they break.

One Solution:

The query below finds non-relative links embedded in the Quick Launch and Top Bar on WSS 3.0 sites.

Ideally this should be run on a non-production system with a restored copy of the content database, it's very bad behavior to query the live SharePoint database directly.  Replace topsite% with the name of the site under which you want to search.

use [ContentDBofSiteInQuestion]
go

select dbo.webs.title as SiteName, dbo.navnodes.url as NonRelativeURL, dbo.webs.fullurl as location
from dbo.navnodes
join dbo.webs on dbo.navnodes.webid = dbo.webs.id
where dbo.webs.fullurl like '/topsite%'
and dbo.navnodes.url like '%http%'

Stop 0x0000005D on VMware when using WinPE or WAIK

Problem: WinPE images crash with a stop 0x0000005D blue screen error on some VMware virtual machines.
Solution: 64-bit WinPE images cannot execute on VMs defined with a 32-bit operating system within VMware.  To temporarily work around the issue, change the Guest Operating System to a 64-bit version.  Remember to change it back when finished using WinPE on the guest.