May 29, 2013

SQL Database in use: How to find and close existing database connections

This is useful in many situations (SharePoint restores, etc):

In SQL Management Studio:
1. exec sp_who2; 
  This returns a list of all current sessions.  You will normally be interested in the sessions with SPID 50 or higher.
2. kill blockingSPID
  Try to close the connection from the client side, but if that doesn't work, you can kill the session from SQL Server.


Many client applications will quickly try to restore a connection, so to block access:

1. Go to the Properties > Options of the database in question.
2. In the State section, change the Restricted Access from MULTI_USER to RESTRICTED_USER to allow only database owners to connect. 
3. Take the database offline via Tasks > Take offline


May 17, 2013

Identity XSL for SharePoint XSLT Webpart

When you get started with XSL transforms with the SharePoint XSLT Webpart, a visual identity XSL transform is often helpful for getting started.

Here's one I made:

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:msxsl="urn:schemas-microsoft-com:xslt"
exclude-result-prefixes="msxsl"
  xmlns:ddwrt2="urn:frontpage:internal">
<xsl:output method="html" />
<xsl:template match='dsQueryResponse'>
   <fieldset>
   <legend><xsl:text>dsQueryResponse</xsl:text></legend>
   <dl title="dsQueryResponse">
           <xsl:for-each select="@*">
    <dt><xsl:value-of select="name()"/></dt>
      <dd><xsl:value-of select="."/></dd>
  </xsl:for-each>
  </dl>
 


<fieldset>
   <legend><xsl:text>Rows</xsl:text></legend>

    

     <xsl:apply-templates select='Rows/Row'/>
     </fieldset>
    
     </fieldset>

  
  </xsl:template>

 
  <xsl:template match='Row'>
<fieldset>
   <legend><xsl:text>Row</xsl:text></legend>

  <dl>
             <xsl:for-each select="@*">
   <dt><xsl:value-of select="name()"/></dt>   
   <dd><xsl:value-of select="."/></dd>
  </xsl:for-each>
  </dl>
  </fieldset>
    
  </xsl:template>
 

</xsl:stylesheet>

May 16, 2013

"The workbook can not be opened" error in SharePoint 2010 with Excel

I got this error "The workbook can not be opened" in Excel Services or with the Office Excel Web App with SharePoint 2010. 

It occurs when you have a separate service user account for your service applications (which you should do to follow the best practices for permissions in SharePoint).

The fix is simple:
  1. Open Powershell for SharePoint 2010
  2. Run "$webapp = Get-SPWebApplication http://webapplicationurl"
  3. Then "$webapp.GrantAccessToProcessIdentity("domain\serviceusername")"

That's all.

Thanks to this blog for the solution:
http://wellytonian.com/2011/10/excel-workbook-cannot-be-opened-error-%E2%80%93-sharepoint-2010/