SharePoint list rollup - url references to child lists

Asked to create a view which rolls up list libraries on subsites to a central parent site list.
The view needed to list each item link relative to its list and not the parent list url.

This required use of the XPath functions substring-before/after.  XPath does not define a function
lastIndexOf or IndexOf.  Would be nice to have these!

Problem was that though the parent list did provide the url path back to the child list it did not include
the item ID.  This means you have to parse the url and build the url reference in Xpath.

Sample:

URL = fullurl/Lists/LibraryName/Filename

<xsl:variable name="tempurl" select="substring-after(@fileRef,'Lists/')"/>
<xsl:variable name="directory" select="substring-before($tempurl,'/')"/>
<xsl:variable name="url" select="substring-before(@fileRef,'Lists/')"/>

<xsl:variable name="tasklink" select="concat($url,'Lists/',$directory,'/DispForm.aspx?ID=',@ID)" />

<a href="{$tasklink}"><xsl:value-of select="@Title"/></a>


SharePoint Designer, XPath, LastIndexOf,@FileRef

An Unexpected error has occurred (MOSS 2007)/FIPS issue

Finished an install of MOSS on a box and tried to bring up Central Admin.
 I received this most informative message! 

An Unexpected error has occurred

Step 1:
First thing to do was to get the actual error message.
 Tweak the web.config in WSS
 1.) Modify the web.config and set debug="false".

Note: Some blogs mention the work around
<configuration>
    <runtime>
        <enforceFIPSPolicy enabled=”0” />
        <!-- or maybe ="false" -->
    </runtime>
</configuration>
The issue here is that if you need FIPS compliance then you can't just turn off the policy.


Step 2:
Now I was getting the actual error - Unable to validate data.  The application log listed Event ID 1309/Event Code 3005.  The key was the validation error.  This lead me to a common error in our environment.

 Issue:
Group Policy enforced on our servers sets fips policy to 1
(HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\fipsalgorithmpolicy)

MOSS 2007 is built on 2.0 of Framework which uses the RijndaelManaged implementation of the AES algorithm when it processes view state data.  The RM implementation has not been certified by the NIST as compliant with the Federal Information Processing Standard (FIPS).  Therefore the FIPS algorithm is not part of the Windows Platform FIPS validated crytographic algorithms.  Enforcing FIPS means that state data in MOSS can't be decypted properly.

Solution:

Modify the Machine.config file
1.) Open machine config at %installdir%/microsoft.net/Framework/v2.0.50727/Config
2.)Locate <system.web>
3.)Add following block
<machineKey validationKey="AutoGenerate,IsolateApps" decryptionKey="AutoGenerate,IsolateApps" validation="3DES" decryption="3DES"/>
4.)Save machine.config
5.)iisreset

BrownField Code Adventure

Yippee, I have inherited an application which the customer has asked me to "tweak".  This is an .Net C# application which began life in VS 2005 on IIS 6.  We need to get it too VS 2010 SP1 IIS 7.5.
Here is what I have experienced so far:

1.) This was a website solution vice a web application which is my preference when working with these types of applications.  I tried to copy and paste the files over and was reminded of this pit fall.

• Designer pages which contains all the globals for the controls is not present in a website solution.
• Markup Header files use the CodeFile vice CodeBehind keyword
• Namespace was not aligned with the web project name but can be fixed.  That means patch each class with the new namespace for the application.

2.) String handling using the namespace Microsoft.VisualBasic.Strings!  What happened? String is part of the System namespace most classes inherit the string from the System namespace.

Here is what I found:
return Path.Combine(TraceLogsPath, "TraceLog." + Strings.Format(DateTime.Now, "yyyy.MM.dd") + ".xml");

What is SQL Client Connectivity SDK in SQL 2008 R2 Installer

During an install I was wondering what this option did?

SQL Client Connectivity SDK and Client Tools SDK provided the Wrapper objects for COM components.

Client Tools SDK Provide wrappers classes for DTSRuntime and similar components that you can use to Execute a .dtsx (SSIS) package on demand. Most of the People install Integration Service to perform similar task.

Also SQL Client Connectivity SDK plays a key role in providing Microsoft.SQLServer.Diganostics.STrace library used by Client Tools.

Now, you can use this Wrapper classes to run a SSIS package from within your WCF/WebService/Windows Service or any other .Net App without calling a SQL Agent Job.

there are probably more uses to this library and would be great if someone added to my comment.

Thanks Pawan Gonnakuti
http://connect.microsoft.com/SQLServer/feedback/details/381445/sql-client-connectivity-sdk-and-client-tools-sdk-documentation

Cannot Open User Default Database (error 4064) - SQL database lock out recovery

In my haste to recreate a database, I neglected to set my account back to the default master.
Now each time I attempt to login, SQL Server issues error 4064 as it attempts to connect to a
catalog which is not longer there!

Fortunately, Laurentiu Cristofor at http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/b32c862a-799c-43c4-a731-c4811078c8bd/

Had the solution:

C:\> sqlcmd -E -d master
1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master
2> GO

Brownfield coding - Silverlight 4 Toolkit installation vs. Visual Studio 2010 SP1

Had an opportunity to patch some Silverlight 4 code which we have installed.  Did not have the environment set up and tried to install Silverlight Tools 4 .exe.("Single Install" for Silverlight Developer runtime and the tools.)

Each time I ran the exe it failed telling me that their was a conflict with my current install.  I snooped around and found several tips guides based around RC version software.  Did finally hit upon the issue which was Visual Studio 2010 SP1.  It conflicts with the version 4 install.  So I removed SP1 from the box and reran the install without issue. 

Visual Studio 2010 sp1 full iso

Needed a full copy of the sp1 iso for a disconnected network.

Microsoft has this link set up for just such a need.

http://go.microsoft.com/fwlink/?LinkId=210710

Getting access to locked SQL Server - add a user to the SQL Server sysadmin role

Had a few developer's who left and did not give access to development databases.  I could not
access the databases since I had not be designated as one of the sysadmins on their local dbs. 

I found this script from Microsoft which will add a local system admin account to the SQL sysadmin group.  It worked great for me.


@echo off
rem
rem ****************************************************************************
rem
rem    Copyright (c) Microsoft Corporation. All rights reserved.
rem    This code is licensed under the Microsoft Public License.
rem    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
rem    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
rem    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
rem    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
rem
rem ****************************************************************************
rem
rem CMD script to add a user to the SQL Server sysadmin role
rem
rem Input:  %1 specifies the instance name to be modified. Defaults to SQLEXPRESS.
rem         %2 specifies the principal identity to be added (in the form "<domain>\<user>").
rem            If omitted, the script will request elevation and add the current user (pre-elevation) to the sysadmin role.
rem            If provided explicitly, the script is assumed to be running elevated already.
rem
rem Method: 1) restart the SQL service with the '-m' option, which allows a single connection from a box admin
rem            (the box admin is temporarily added to the sysadmin role with this start option)
rem         2) connect to the SQL instance and add the user to the sysadmin role
rem         3) restart the SQL service for normal connections
rem
rem Output: Messages indicating success/failure.
rem         Note that if elevation is done by this script, a new command process window is created: the output of this
rem         window is not directly accessible to the caller.
rem
rem
setlocal
set sqlresult=N/A
if .%1 == . (set /P sqlinstance=Enter SQL instance name, or default to SQLEXPRESS: ) else (set sqlinstance=%1)
if .%sqlinstance% == . (set sqlinstance=SQLEXPRESS)
if /I %sqlinstance% == MSSQLSERVER (set sqlservice=MSSQLSERVER) else (set sqlservice=MSSQL$%sqlinstance%)
if .%2 == . (set sqllogin="%USERDOMAIN%\%USERNAME%") else (set sqllogin=%2)
rem remove enclosing quotes
for %%i in (%sqllogin%) do set sqllogin=%%~i
@echo Adding '%sqllogin%' to the 'sysadmin' role on SQL Server instance '%sqlinstance%'.
@echo Verify the '%sqlservice%' service exists ...
set srvstate=0
for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
if .%srvstate% == .0 goto existerror
rem
rem elevate if <domain/user> was defaulted
rem
if NOT .%2 == . goto continue
echo new ActiveXObject("Shell.Application").ShellExecute("cmd.exe", "/D /Q /C pushd \""+WScript.Arguments(0)+"\" & \""+WScript.Arguments(1)+"\" %sqlinstance% \""+WScript.Arguments(2)+"\"", "", "runas"); >"%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js"
call "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js" "%cd%" %0 "%sqllogin%"
del "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js"
goto :EOF
:continue
rem
rem determine if the SQL service is running
rem
set srvstarted=0
set srvstate=0
for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
if .%srvstate% == .0 goto queryerror
rem
rem if required, stop the SQL service
rem
if .%srvstate% == .1 goto startm
set srvstarted=1
@echo Stop the '%sqlservice%' service ...
net stop %sqlservice%
if errorlevel 1 goto stoperror
:startm
rem
rem start the SQL service with the '-m' option (single admin connection) and wait until its STATE is '4' (STARTED)
rem also use trace flags as follows:
rem     3659 - log all errors to errorlog
rem     4010 - enable shared memory only (lpc:)
rem     4022 - do not start autoprocs
rem
@echo Start the '%sqlservice%' service in maintenance mode ...
sc start %sqlservice% -m -T3659 -T4010 -T4022 >nul
if errorlevel 1 goto startmerror
:checkstate1
set srvstate=0
for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
if .%srvstate% == .0 goto queryerror
if .%srvstate% == .1 goto startmerror
if NOT .%srvstate% == .4 goto checkstate1
rem
rem add the specified user to the sysadmin role
rem access tempdb to avoid a misleading shutdown error
rem
@echo Add '%sqllogin%' to the 'sysadmin' role ...
for /F "usebackq tokens=1,3" %%i in (`sqlcmd -S np:\\.\pipe\SQLLocal\%sqlinstance% -E -Q "create table #foo (bar int); declare @rc int; execute @rc = sp_addsrvrolemember '$(sqllogin)', 'sysadmin'; print 'RETURN_CODE : '+CAST(@rc as char)"`) do if .%%i == .RETURN_CODE set sqlresult=%%j
rem
rem stop the SQL service
rem
@echo Stop the '%sqlservice%' service ...
net stop %sqlservice%
if errorlevel 1 goto stoperror
if .%srvstarted% == .0 goto exit
rem
rem start the SQL service for normal connections
rem
net start %sqlservice%
if errorlevel 1 goto starterror
goto exit
rem
rem handle unexpected errors
rem
:existerror
sc query %sqlservice%
@echo '%sqlservice%' service is invalid
goto exit
:queryerror
@echo 'sc query %sqlservice%' failed
goto exit
:stoperror
@echo 'net stop %sqlservice%' failed
goto exit
:startmerror
@echo 'sc start %sqlservice% -m' failed
goto exit
:starterror
@echo 'net start %sqlservice%' failed
goto exit
:exit
if .%sqlresult% == .0 (@echo '%sqllogin%' was successfully added to the 'sysadmin' role.) else (@echo '%sqllogin%' was NOT added to the 'sysadmin' role: SQL return code is %sqlresult%.)
endlocal
pause

Could not load file or assembly or one of its dependencies. Access is denied

I was moving an older .Net 2.0 web service to a new IIS box.  I set up everything but each time I loaded the application I got this annoying error!  Cursory search will reveal a miriad of potential issues.

Here are the usual culprits.

1. IUSR_USR or impersonated account does not have access to Temporary ASP.NET in Framework.
2. Account does not have access to the inetpub/wwwroot or wherever the website is hosted.
3. Checked impersonation.

This was the issue.  I was using an expired service account which had come over with the web.config file.  Mapped the Authentication > ASP.NET Impersonation to the valid service account and that fixed it!

URL Encoding Reference

http://www.w3schools.com/tags/ref_urlencode.asp

URL Encoding Reference

ASCII Character	URL-encoding
space	%20
!	%21
"	%22
#	%23
$	%24
%	%25
&	%26
'	%27
(	%28
)	%29
*	%2A
+	%2B
,	%2C
-	%2D
.	%2E
/	%2F
0	%30
1	%31
2	%32
3	%33
4	%34
5	%35
6	%36
7	%37
8	%38
9	%39
:	%3A
;	%3B
<	%3C
=	%3D
>	%3E
?	%3F
@	%40
A	%41
B	%42
C	%43
D	%44
E	%45
F	%46
G	%47
H	%48
I	%49
J	%4A
K	%4B
L	%4C
M	%4D
N	%4E
O	%4F
P	%50
Q	%51
R	%52
S	%53
T	%54
U	%55
V	%56
W	%57
X	%58
Y	%59
Z	%5A
[	%5B
\	%5C
]	%5D
^	%5E
_	%5F
`	%60
a	%61
b	%62
c	%63
d	%64
e	%65
f	%66
g	%67
h	%68
i	%69
j	%6A
k	%6B
l	%6C
m	%6D
n	%6E
o	%6F
p	%70
q	%71
r	%72
s	%73
t	%74
u	%75
v	%76
w	%77
x	%78
y	%79
z	%7A
{	%7B
|	%7C
}	%7D
~	%7E
	%7F
`	%80
	%81
‚	%82
ƒ	%83
„	%84
…	%85
†	%86
‡	%87
ˆ	%88
‰	%89
Š	%8A
‹	%8B
Œ	%8C
	%8D
Ž	%8E
	%8F
	%90
‘	%91
’	%92
“	%93
”	%94
•	%95
–	%96
—	%97
˜	%98
™	%99
š	%9A
›	%9B
œ	%9C
	%9D
ž	%9E
Ÿ	%9F
	%A0
¡	%A1
¢	%A2
£	%A3
¤	%A4
¥	%A5
¦	%A6
§	%A7
¨	%A8
©	%A9
ª	%AA
«	%AB
¬	%AC
­	%AD
®	%AE
¯	%AF
°	%B0
±	%B1
²	%B2
³	%B3
´	%B4
µ	%B5
¶	%B6
·	%B7
¸	%B8
¹	%B9
º	%BA
»	%BB
¼	%BC
½	%BD
¾	%BE
¿	%BF
À	%C0
Á	%C1
Â	%C2
Ã	%C3
Ä	%C4
Å	%C5
Æ	%C6
Ç	%C7
È	%C8
É	%C9
Ê	%CA
Ë	%CB
Ì	%CC
Í	%CD
Î	%CE
Ï	%CF
Ð	%D0
Ñ	%D1
Ò	%D2
Ó	%D3
Ô	%D4
Õ	%D5
Ö	%D6
×	%D7
Ø	%D8
Ù	%D9
Ú	%DA
Û	%DB
Ü	%DC
Ý	%DD
Þ	%DE
ß	%DF
à	%E0
á	%E1
â	%E2
ã	%E3
ä	%E4
å	%E5
æ	%E6
ç	%E7
è	%E8
é	%E9
ê	%EA
ë	%EB
ì	%EC
í	%ED
î	%EE
ï	%EF
ð	%F0
ñ	%F1
ò	%F2
ó	%F3
ô	%F4
õ	%F5
ö	%F6
÷	%F7
ø	%F8
ù	%F9
ú	%FA
û	%FB
ü	%FC
ý	%FD
þ	%FE
ÿ	%FF

---

URL Encoding Reference

The ASCII device control characters -%1f were originally designed to control hardware devices. Control characters have nothing to do inside a URL.

ASCII Character	Description	URL-encoding
NUL	null character
SOH	start of header	%01
STX	start of text	%02
ETX	end of text	%03
EOT	end of transmission	%04
ENQ	enquiry	%05
ACK	acknowledge	%06
BEL	bell (ring)	%07
BS	backspace	%08
HT	horizontal tab	%09
LF	line feed	%0A
VT	vertical tab	%0B
FF	form feed	%0C
CR	carriage return	%0D
SO	shift out	%0E
SI	shift in	%0F
DLE	data link escape	%10
DC1	device control 1	%11
DC2	device control 2	%12
DC3	device control 3	%13
DC4	device control 4	%14
NAK	negative acknowledge	%15
SYN	synchronize	%16
ETB	end transmission block	%17
CAN	cancel	%18
EM	end of medium	%19
SUB	substitute	%1A
ESC	escape	%1B
FS	file separator	%1C
GS	group separator	%1D
RS	record separator	%1E
US	unit separator	%1F

SharePoint Designer CAML - ddwrt:FormatDate with different FormatFlags

I was asked today again about changing the date formats on a list view.  The user had SP Designer open and I had to wrack my brain on the format codes for straight dates with no time attached. 

Using the following namespace attribute

xmlns:ddwrt=http://schemas.microsoft.com/WebParts/v2/DataView/runtime

Not what we wanted:

ddwrt:FormatDate(string(@EventDate), 1033, 5) => MM/DD/YYYY HH:MM AM/PM

What we wanted was:

ddwrt:FormatDate(string(@EventDate), 1033, 1) => MM/DD/YYYY

Here is the format chart

Thanks to http://panvega.wordpress.com/2008/12/08/ddwrtformatdate-with-different-formatflags/

404 error with WCF querystring using email address in Visual Studio.

Tyring to build out a RESTful call which would pass an email as an argument.
I was working in Visual Studio 2010.

Example:
http://mysite.com/TheService.svc/Name/bob.smith@white.com/

Error:
404 resource not found


Issue appears to be with the internal web server, "Cassini", in visual studio.
I deployed this to IIS and the service worked fine.  This is a known bug with Cassini.
It appears to not be able to handle periods (.=%2E).

Credit to Stack Overflow.

http://stackoverflow.com/questions/8491159/email-address-as-part-of-a-wcf-data-service-querystring