Date Formatting in TSQL

Trying to find a quick list of format settings for date selections.

select convert(varchar(10),GetDate(),101) as [MM/DD/YYYY]

The inline select explanation:

Arg 1: The datatype you are converting the data type too
Arg 2: Item being selected which is going to be a datetime object.  Sample uses the GetDate function to make the sample generic.
Arg 3: The data format you need

MM/DD/YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]	11/23/98
MM/DD/YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]	11/23/1998

DD.MM.YY	German	SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]	25.12.05
DD.MM.YYYY	German	SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]	25.12.2005

Thanks to the team at sql server helper.  They have the complete list of Date formats
http://www.sql-server-helper.com/tips/date-formats.aspx

Error Sandboxed service is too busy to handle the request

We have a custom 2010 branding solution in visual studio.  It deploys the Sharepoint branding to each site collection.  Trying to deploy the solution today from the site collection gallery and then from visual studio directly but was getting the dreaded service is busy error.  I have seen this before but was forced to use all three of the presrcibed solutions.  I normally only have had to do either one or two.

A. Change registry settings

A known cause is the presence of the following key in the registry of the servers that are running the sandboxed host service. (An additional symptom of this cause is that the service stops a few seconds after starting.)

HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows NT\RPC

It does not matter what the value of the key is, or even if the key has any value. If the key is present, the user account in which the sandboxed host process runs must be able to read it because the process tries to when it starts up. By default, the user account does not have permission to read it (because, by default, the key is not present). You must give the Users group of the computer permission to read the key.
Note: It is not sufficient to give such permission to the Authenticated Users group, because the sandboxed process removes the Authenticated User token from the the user account in which the sandboxed host process runs. This also makes that account a restricted account which means it is not sufficient to give permission to read the key to that account alone. Since the account is not considered authenticated, doing this would have no effect. However, the account does inherit the permissions of the computer’s User group.
Take the following steps on every server which has the key and which is running the sandboxed host service.

1. Open the registry editor and navigate to the key.
2. Right-click the key and select Permissions.
3. On the dialog that opens, click Add.
4. In the dialog that opens, enter the following in the Enter the object names to select box: computername\Users.
5. Click Check Names.
6. After the name has resolved, click OK.
7. Restart the sandboxed host service on all servers on which it is to run. It cannot hurt to do an iisreset as well.

B. Change host file to point clr.microsoft.com to local machine

You can redirect these attempts by adding the following line to the end of the hosts file located at C:\Windows\System32\drivers\etc:

127.0.0.1 crl.microsoft.com

This must be done on all servers running the sandboxed host service. Then restart the SharePoint 2010 User Code Host service on all these servers. It cannot hurt to do an iisreset as well.

C. Change system configuration files of Sandbox service to skip certificate verification in Code Access Security policies.

1. Open SharePoint 2010 Central Administration
2. Navigate to “Configure service accounts” in Security section
3. Select ‘Windows Service – Microsoft SharePoint Foundation Sandboxed Code Service’ in the dropdown control.
4. Then you will see which account is used for Sandbox Service.
   (Get-SPManagedAccount –Identity "DOMAINUserCodeServiceAccont").Sid.Value

Now, it is time to check and update registry settings for WinTrust.

1. Open the registry editor and navigate to:
   HKEY_USERS{SID you obtained earlier}SOFTWARE/Microsoft/Windows/CurrentVersion/WinTrust/Trust Providers/Software Publishing
2. Change value of “State” key to 0x00023e00.
3. Restart Sandbox Service
4. Perform IIS reset 

Thanks to the pdf sharepoint team  

http://www.pdfshareforms.com/error-sandboxed-too-busy-handle-request/

 and

Rick Kirkham from the Sharepoint dev team
http://blogs.msdn.com/b/sharepointdev/archive/2011/02/08/error-the-sandboxed-code-execution-request-was-refused-because-the-sandboxed-code-host-service-was-too-busy-to-handle-the-request.aspx

Move Inetpub to another Drive

Needed to move inetpub to the d drive

MOVEIISROOT.BAT D

http://blogs.iis.net/thomad/archive/2008/02/10/moving-the-iis7-inetpub-directory-to-a-different-drive.aspx

The MOVEIISROOT.BAT Script:

REM PLEASE BE AWARE: SERVICING (I.E. HOTFIXES AND SERVICE PACKS) WILL STILL REPLACE FILES
REM IN THE ORIGINAL DIRECTORIES. THE LIKELIHOOD THAT FILES IN THE INETPUB DIRECTORIES HAVE
REM TO BE REPLACED BY SERVICING IS LOW BUT FOR THIS REASON DELETING THE ORIGINAL DIRECTORIES
REM IS NOT POSSIBLE.
@echo off
IF "%1" == "" goto err
setlocal
set MOVETO=%1:\
REM simple error handling if drive does not exist or argument is wrong
IF NOT EXIST %MOVETO% goto err
REM Backup IIS config before we start changing config to point to the new path
%windir%\system32\inetsrv\appcmd add backup beforeRootMove

REM Stop all IIS services
iisreset /stop
REM Copy all content
REM /O - copy ACLs
REM /E - copy sub directories including empty ones
REM /I - assume destination is a directory
REM /Q - quiet
REM echo on, because user will be prompted if content already exists.
echo on
xcopy %systemdrive%\inetpub %MOVETO%inetpub /O /E /I /Q
@echo off
REM Move AppPool isolation directory
reg add HKLM\System\CurrentControlSet\Services\WAS\Parameters /v ConfigIsolationPath /t REG_SZ /d %MOVETO%inetpub\temp\appPools /f
REM Move logfile directories
%windir%\system32\inetsrv\appcmd set config -section:system.applicationHost/sites -siteDefaults.traceFailedRequestsLogging.directory:"%MOVETO%inetpub\logs\FailedReqLogFiles"
%windir%\system32\inetsrv\appcmd set config -section:system.applicationHost/sites -siteDefaults.logfile.directory:"%MOVETO%inetpub\logs\logfiles"
%windir%\system32\inetsrv\appcmd set config -section:system.applicationHost/log -centralBinaryLogFile.directory:"%MOVETO%inetpub\logs\logfiles"
%windir%\system32\inetsrv\appcmd set config -section:system.applicationHost/log -centralW3CLogFile.directory:"%MOVETO%inetpub\logs\logfiles"
REM Move config history location, temporary files, the path for the Default Web Site and the custom error locations
%windir%\system32\inetsrv\appcmd set config -section:system.applicationhost/configHistory -path:%MOVETO%inetpub\history
%windir%\system32\inetsrv\appcmd set config -section:system.webServer/asp -cache.disktemplateCacheDirectory:"%MOVETO%inetpub\temp\ASP Compiled Templates"
%windir%\system32\inetsrv\appcmd set config -section:system.webServer/httpCompression -directory:"%MOVETO%inetpub\temp\IIS Temporary Compressed Files"
%windir%\system32\inetsrv\appcmd set vdir "Default Web Site/" -physicalPath:%MOVETO%inetpub\wwwroot
%windir%\system32\inetsrv\appcmd set config -section:httpErrors /[statusCode='401'].prefixLanguageFilePath:%MOVETO%inetpub\custerr
%windir%\system32\inetsrv\appcmd set config -section:httpErrors /[statusCode='403'].prefixLanguageFilePath:%MOVETO%inetpub\custerr
%windir%\system32\inetsrv\appcmd set config -section:httpErrors /[statusCode='404'].prefixLanguageFilePath:%MOVETO%inetpub\custerr
%windir%\system32\inetsrv\appcmd set config -section:httpErrors /[statusCode='405'].prefixLanguageFilePath:%MOVETO%inetpub\custerr
%windir%\system32\inetsrv\appcmd set config -section:httpErrors /[statusCode='406'].prefixLanguageFilePath:%MOVETO%inetpub\custerr
%windir%\system32\inetsrv\appcmd set config -section:httpErrors /[statusCode='412'].prefixLanguageFilePath:%MOVETO%inetpub\custerr
%windir%\system32\inetsrv\appcmd set config -section:httpErrors /[statusCode='500'].prefixLanguageFilePath:%MOVETO%inetpub\custerr
%windir%\system32\inetsrv\appcmd set config -section:httpErrors /[statusCode='501'].prefixLanguageFilePath:%MOVETO%inetpub\custerr
%windir%\system32\inetsrv\appcmd set config -section:httpErrors /[statusCode='502'].prefixLanguageFilePath:%MOVETO%inetpub\custerr
REM Make sure Service Pack and Hotfix Installers know where the IIS root directories are
reg add HKLM\Software\Microsoft\inetstp /v PathWWWRoot /t REG_SZ /d %MOVETO%inetpub\wwwroot /f
reg add HKLM\Software\Microsoft\inetstp /v PathFTPRoot /t REG_SZ /d %MOVETO%inetpub\ftproot /f
REM Do the same for x64 directories
if not "%ProgramFiles(x86)%" == "" reg add HKLM\Software\Wow6432Node\Microsoft\inetstp /v PathWWWRoot /t REG_EXPAND_SZ /d %MOVETO%inetpub\wwwroot /f
if not "%ProgramFiles(x86)%" == "" reg add HKLM\Software\Wow6432Node\Microsoft\inetstp /v PathFTPRoot /t REG_EXPAND_SZ /d %MOVETO%inetpub\ftproot /f
REM Restart all IIS services
iisreset /start
echo.
echo.
echo ===============================================================================
echo Moved IIS7 root directory from %systemdrive%\ to %MOVETO%.
echo.
echo Please verify if the move worked. If so you can delete the %systemdrive%\inetpub directory.
echo If something went wrong you can restore the old settings via
echo     "APPCMD restore backup beforeRootMove"
echo and
echo     "REG delete HKLM\System\CurrentControlSet\Services\WAS\Parameters\ConfigIsolationPath"
echo You also have to reset the PathWWWRoot and PathFTPRoot registry values
echo in HKEY_LOCAL_MACHINE\Software\Microsoft\InetStp.
echo ===============================================================================
echo.
echo.
endlocal
goto success
REM error message if no argument or drive does not exist
:err
echo.
echo New root drive letter required.
echo Here an example how to move the IIS root to the F:\ drive:
echo.
echo MOVEIISROOT.BAT F
echo.
echo.
:success

Install MOSS 2007 on IIS fails because of 32 bit emulation

This occurred on a x64 Windows 2008 R2/SQL 2008 R2 box running 32 bit emulation to support
some older asp.net apps on IIS 7.0.  The MOSS installer failed saying it could not proceed due to
the fact that IIS was running with 32 bit emulation.

Had to jog my memory on how to set and reset the emulation since once MOSS was installed needed to restore 32 emulation. (http://sakafi.wordpress.com/2009/10/19/asp-net-iis-and-32-bit-emulation-mode-in-64-bit-server/)


ASP.NET , IIS and 32 bit emulation mode in 64 bit server

1. Click Start, click Run, type cmd, and then click OK.
2. Type the following command to disable the 32-bit mode:
   cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 0
3. iisreset
4. Install MOSS 2007
5. Reboot
6. Type the following command to disable the 32-bit mode:
   cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 1
7. iisreset
    

Ref : http://support.microsoft.com/kb/894435

Subsite lookup from Parent site column gallery (MOSS 2007)

Customer asked to have a list on a subsite "reuse" a content type (lookup) from a list on the parent site of site collection on a subsite of the same site collection.  You can do this using just the WFE for MOSS 2007.  You need to add the list content type to the site column gallery for the parent site.  This will then be available to all the subsites in this site collection.

Benjamin Niaulin, explains how to do this on MSF

https://www.nothingbutsharepoint.com/sites/eusp/Pages/Create-a-SharePoint-Lookup-Column-to-Another-Site.aspx

It is pretty much the same in MOSS 2007.  The key is to add the type to the site column gallery.  I bumped around a bit trying to get it to work with the subsite lookup until I did this step. 

XPath syntax for SharePoint list form

Had a customer who wanted to modify the standard display for a sharepoint list view.
Using SharePoint Designer, updated the xml using the following XPath syntax.

Customer wanted the contents of the list column 'My Field' which contained an the user's email address which linked to the list entry to create an email message.  This was done by parsing out the email address from the entry which looked like

sample
<a href="listcontent.aspx?id=1">myemail@domain.com</a>

customer wanted
<a href="mailto:myemail@domain.com">myemail@domain.com</a>

XPath syntax created desired results:

<a href="mailto:{substring-before(substring-after(@My_x0020_Field, &quot;sip='&quot;),&quot;'id=&quot;)}?subject={concat(@Title, '-',@My_x0020_Field)}">
<xsl:value-of select="substring-before(substring-after(@My_x0020_Field,'&#34;&gt;'),'&lt;/A')" />
</a>

Legend:
&quot; = "
&#34; = "
&gt; = >
&lt; = <

Calculated fields column in SharePoint list

Getting requests from user to modify the list view of a list. 
So I started looking around for what we had already done in the past.

I did a simple calculation of field and spent time fumbling around with the syntax.
User wanted a the result to display a date in the future based on two other fields.
If either one of the dependant fields was blank then the result should be a blank.

This was the solution for that column

=IF(AND(ISBLANK([FieldDate]),ISBLANK([FieldLengthOfTerm]),"",[FieldDate] + [FieldLengthOfTerm])

Of course, the more features you provide the greate the requests become.  Never fear the
KPI graphic look can be provided based on Alexander Bautz's post:

http://spjsblog.com/2011/11/29/compare-date-with-today-status-indicator-in-list-view/

Anonymous post of Infopath form to MOSS 2007 via WCF service

Customer is looking to do some posting of information via an InfoPath form.  The challenge is they want it hosted so that anyone can post via the form to update document library.  This will require a solution like the one Rob Garrett posted a few years ago.  It will depend on

1.) MOSS Enterprise with InfoPath services
2.) WCF / .Net 3.5 (Minimum)
3.) SP configured to allow anonymous access to the specified document libraries.
4.) InfoPath must use the web based form vice InfoPath native forms.


http://blog.robgarrett.com/2008/07/01/sharepoint-infopath-submission-as-anonymous-user/

Encypting the identity element in web.config

Scott Allen has a good post on this issue

http://odetocode.com/Blogs/scott/archive/2006/05/30/encrypting-identities-in-web-config.aspx

>aspnet_Regiis -pef system.web/identity e:\[path to website]
Encrypting configuration section...
Succeeded!