SharePoint – Running a CAML Query in Powershell

June 30, 2014

Whenever you want to retrieve data from a SP list in a fast way. Especially when you are dealing with a large list.

There is no way around the ‘CAML’ queries, for fast results.

This example gives you an idea on how to build the query.

We are running the query to search for a Text string “Attachment.pdf” in a TEXT Field of the List.

if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null)
{
 Add-PSSnapin Microsoft.SharePoint.PowerShell;
}</pre>
$WebUrl = "http://YourSide/sites/SiteCollectionName"

#Get the Web & Lists to upload the file
$web = Get-SPWeb $WebURL

# $WebUrl=$site.OpenWeb()
$list = $web.Lists["Your_BigListName"]

$spQuery = New-Object Microsoft.SharePoint.SPQuery
$spQuery.ViewAttributes = "Scope='Recursive'";
$spQuery.RowLimit = 20

$Ref = "My Attachment.pdf"

$sQry = '<Where><Contains><FieldRef Name="Body"/><Value Type="Note">' + $Ref + '</Value></Contains></Where>'

$caml = $sQry

$spQuery.Query = $caml

do
{
$listItems = $list.GetItems($spQuery)
$spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition
foreach($item in $listItems)
{
Write-Host $item.ID "-" $item.Title "-" $item.body
Echo ""
}
}
while ($spQuery.ListItemCollectionPosition -ne $null)

$Web.dispose

To get to know the CAML query syntax best use the famous CAML builder Tool.

Look here for more interesting Tools !


PowerShell – Accessing DotNet Assemblies

June 28, 2014

As you all know Powershell is built on top on the .Net Framework. Which contains a lot of classes that we could benefit from if we could access them in our scripting environment.

Well we can using the Assembly Reflection method.

There are different ways of accessing .NET Assemblies depending on the version of PS

PS Version 1

[Reflection.Assembly]::Load Loads an assembly
[Reflection.Assembly]::LoadFile Loads the contents of an assembly file on the specified path
[Reflection.Assembly]::LoadFrom Loads an assembly given its file name or path
[Reflection.Assembly]::LoadWithPartialName Loads an assembly from the application directory or from the global assembly cache using a partial name

Example :

[Reflection.Assembly]::LoadFile( `
  'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll')`
  | out-null            

[System.Web.HttpUtility]::UrlEncode("this is a test")

</br>PS Version 2

Using the Add-Type Cmdlet

Add-Type -AssemblyName System.Web
[System.Web.HttpUtility]::UrlEncode("this is a test")

These 2 examples use the URLEncode method in the HttpUtility Class.

Question is now which Classes are available (Public) to PS.

Well you can use this code to access Assembly Information.

[appdomain]::currentdomain.GetAssemblies() | % {$_.gettypes()}
| sort basetype | Out-GridView

And filter it in you the GRID

image

Now filter for Public Classes

image

Great playing field for Scripters !


SharePoint – JQuery Calendar Control

June 27, 2014

In SharePoint out of the box the calendar control is quite basic and rigid.

image

Giving an example here : let’s say you are working in an international environment and you need to change the date Format depending on the user region.

Or you want to change the week start day not being a Sunday but a Monday for example

image

Well the JQuery Calendar control can handle this and much more

Here is how it will look at the end.

image

And this is how to get there :

First you need to get the JQuery libr. installed. But that’s obvious.

Next you need to remove the original calendar image.

Next you need to bind a .Change Event to the input box.

This is only a snippet out of the full code but it should be enough to get you going

<script type="text/javascript">
/* Remove "From" Calendar Image link */
$('#ctl00_m_g_...  ').each(function () {
    $(this).replaceWith($(this).text());
});
/* Remove "To" Calendar Image link */
$('#ctl00_m_g_...   ').each(function () {
    $(this).replaceWith($(this).text());
});

/* Set Date Field "To" based on the Date Field "FROM", Change Event*/
$('#ctl00_m_g_...   ').datepicker(
{
        dateFormat: 'dd/mm/yy',
        firstDay: 1,
        onSelect: function(dateText, inst) 
{
$("[name='ctl00$m$g_...   ']").val(dateText);
    }
});

/* DatePicker "To" Field */
$('#ctl00_m_g_...   ').datepicker(
{
        dateFormat: 'dd/mm/yy',
        firstDay: 1, 
        onSelect: function(dateText, inst) 
{
        $(this).val(dateText);
 }

});
</script>

Enjoy !


Java – Blocked Untrusted Applications

June 27, 2014

Annoyances !

When I tried to access the web Interface of a Switch I got this message ?

image

Easy to fix if you look on the internet, but in practice it is a different story.

You have to make an Exception in the Java Control Panel –> Security

Go to the Windows Control Panel –> Java Control Panel to find what you need

image

But when you open it, there is no setting there to adjust the security settings ? Except Certificates.

image

But if you go look on Google it show you this interface ?

image

Maybe because of a Java version difference ? Yes and No.

An older version of Java that was installed is still there when you look at the Programs a Features. You can have multiple versions installed.

image

OK so where is then the new Control Panel ?

Just go to the Windows Search and type “JAVA”

image

And start it from there, add the Exception List

image

Run it again in your Browser.

image

and voila Smile Application runs fine again.

image


Windows – Trust Relationship between Workstation and Primary Domain Failed

June 26, 2014

There is a fix but not so easy to accomplish remotely of you don’t have the proper tools installed on the machine.

image

Netdom

In order to use the netdom tool you must have Remote Server Administration Tools (RSAT) installed.

Install the Remote Server Administration Tools (RSAT).

  1. Go to Control Panel -> Programs and Features -> Turn Windows features on or off
  2. In the treeview, go to Remote Server Administration Tools -> Role Administration Tools -> AD DS and AD LDS Tools and select AD DS Tools. Click OK.

NETDOM should be located in your SYSTEM32 folder.

To reset the computer’s password:

  • Log into the affected client with a local account with administrative privileges
  • Open an elevated PowerShell or Command prompt
  • Run the Netdom command
    netdom.exe resetpwd /servername.domain /ud:ad\jsmith /pd:*
    • The user specified with the “/ud:” must have rights to change the computer object password
    • The “/pd:*” switch will hide the entered password
  • Reboot

If you like to use Powershell be aware that is is depending on the PS Version installed Sad smile

PowerShell v2 – Test-ComputerSecureChannel

    • Log into the affected client with a local account with administrative privileges Open an elevated PowerShell prompt Load the Active Directory PowerShell module
Import-Module activedirectory

Test the secure channel

Test-ComputerSecureChannel

If the command returns false, run the command with the “-Repair” switch

Test-ComputerSecureChannel -Repair -Credential $(Get-Credential)

verify the secure channel using the Test-ComputerSecureChannel

Test-ComputerSecureChannel

Reboot

PowerShell v3 or higher – Reset-MachineAccountPassword

    • Log into the affected client with a local account with administrative privileges Open an elevated PowerShell prompt Load the Active Directory PowerShell module
Import-Module activedirectory

Test the secure channel

Test-ComputerSecureChannel

If the command returns false, run the Reset-MachineAccountPassword command

Reset-MachineAccountPassword -Credential $(Get-Credential)

verify the secure channel using the Test-ComputerSecureChannel

Test-ComputerSecureChannel

Reboot

Alternative :

Try to download machinepwd: http://www.joeware.net/freetools/tools/machinepwd/index.htm and then run it on the workstation.

If that machinepwd fails:

  1. Reset the computer account in AD
  2. On the workstation, run: machinepwd /fix

If you have User Account Control (UAC) enabled then you must start the the command prompt in “Run as Administrator” mode.

This should force the workstation to re-sync the machine password with AD, and re-establish the trust relationship.

 

Alternatively use NetDom.exe and more

Success !


Windows 7 – Hangs on hibernate and logoff Event ID 1014

June 25, 2014

After a few Windows update I was encountering a very annoying feature Winking smile that I picked up.

When my PC was docked in the network and tried to hibernate or logoff. Frequently I noticed that the logoff or hibernation process did not complete.

The PC ended with the disks spinning and a black screen. Making me to brute force the shutdown ?

After nosing in the event viewer I noticed an event ID 1014

image

After googling a bit I found an article that referred to this one, and claimed that it would solve this very  same isseu

http://social.technet.microsoft.com/wiki/contents/articles/3336.event-id-1014-microsoft-windows-dns-client.aspx

Run the following command in an elevated command prompt in Windows 7:

image

netsh interface tcp set global rss=disabled

netsh interface tcp set global autotuninglevel=disabled

netsh int ip set global taskoffload=disabled

If the above does not work you can try this one : http://support.microsoft.com/kb/953367/en-us

I also experienced closing Skype before Hibernating was helping a lot ?

So I gave it a try and indeed it was gone ! struggled with this for a few months Steaming mad


Display Line Numbers in SSMS Query Window

June 25, 2014

I am always looking where exactly I can activate the line numbers to show up, in the SSMS Query Editor.

image

Just for my own memory.


SharePoint – Database Model Secrets

June 20, 2014

If you are into SharePoint developing at some point in time, you will need to take a dive into the database.

To get some understanding of the SharePoint Data Model, you would need some documentation.

Very hard to find but here is what I ran into :

2.2.1 Simple Data Types

2.2.2 Bit Fields and Flag Structures

2.2.3 Enumerations

2.2.4 Binary Structures

2.2.5 Result Sets

2.2.6 SQL Structures

2.2.7 Tables and Views

2.2.8 XML Structures

Very helpful information Smile

Enjoy!


Oracle – SharePoint UNICODE issue : Chinese Control Characters

June 18, 2014

When you run an English SharePoint version on an English OS. Getting SSRS to deal with Oracle Unicode Characters is a challenge as well !

ORACLE has does not show the Chinese Characters properly in SharePoint.

image

As you can see there is always 1 control Character between each Chinese character.

Oracle and Microsoft are not friends yet Ninja

Solution :

This should work using the CONVERT function between Character Sets. But it did not 😦

Convert between character sets it says, give it a try.

SELECT CONVERT('Ä Ê Í Ó Ø A B C D E','US7ASCII','WE8ISO8859P1') FROM DUAL

By the way you can’t show the Chinese characters on an English OS either using SQLPLus command line.

Because the Character set is defined in the NLS_LANG environment variable

solve it use the UNISTR() and ASCIISTR() functions to display which control characters are in play.

SELECT UNISTR(ASCIISTR(trim(FieldName)) AS Alias FROM ...

Next you can remove them using the REPLACE() function.

SELECT UNISTR(REPLACE(ASCIISTR(trim(FieldName)), ('\FFFD%'),'')) AS Alias FROM ...

If you know a better way, please share here

Tip 1 :

By the way you can’t show the Chinese characters on an English OS either using SQLPLus command line.

Because the Character set is defined in the NLS_LANG environment variable

or using this qry

<br>SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET','NLS_TERRITORY','NLS_LANGUAGE')<br>

Tip 2 :

If you set the NLS_LANG parameter on the client that runs the ODBC using the Oracle driver to

NLS_LANG = AMERICAN_AMERICA.ZHS16GBK

image

It might just work ?!

Tip 3 :

An other option is NOT to use the ODBC connection3 But to use the .NET Oracle provider including the Unicode=True setting

image

Data Source=YourDataSource;Unicode=True

But it say only valid till Oracle 9i

clip_image002

Ps :

There is definetly no UNICODE support if you use the Microsoft Driver for Oracle.

http://support.microsoft.com/kb/244661
… The ODBC Driver for Oracle does not support any of the new Oracle8 data types — Unicode data types, BLOBs, CLOBs, and so on …

Unless you install a later version of the drivers (See MDAC documention)
But then again I read there ara other issues coming your way ? https://community.oracle.com/message/1147185

Work arounds using the ALTER SESSION command http://msdn.microsoft.com/en-us/library/ms714756(v=vs.85).aspx
Or us the .NET version of the MS Driver for Oracle which support UNICODE.

Look here for the MS versions history http://msdn.microsoft.com/en-us/library/ms810810.aspx

Next Solution might be that you tweak the native Oracle Driver to support the code page you are looking for.

This explains it very well ?
http://www.toadworld.com/products/toad-for-oracle/w/toad_for_oracle_wiki/233.unicode-support-in-toad-10.aspx


SSRS – Shared Data Source : Oracle error ORA-03134

June 18, 2014

This error came up setting up a shared Data Source in SharePoint ?!

ORA-03134: Connections to this server version are no longer supported.

For a mix of older versions of Oracle  databases. You need to pick the most appropriate driver.

Solution : Install a lower version of the Instant Client Libr. (just downgrade to the lowest level of Database version)

The good news:

It is possible to access an Oracle 8 Server with Oracle Instant Client 10 (see Metalink Note 207303.1).
The bad news: There is no Oracle Instant Client version 9. The product was introduced with Oracle 10g.

Connections to Oracle 9.0.1 databases are not supported.
Connecting with a 10.2 Instant Client to server version 9.2.0.4 or higher is supported.

or avoid the problem

Don’t use a shared Data Source but a Report Data Source