Update Vs SystemUpdate in SharePoint

Hi Guys,

In this post i am going to explain what is the difference between Update and SystemUpdate methods. As you all know, these two methods are used to update/add the item in the sharepoint list like below. Here I am adding some more points on the same..

1.        SPList lstProducts = web.Lists.TryGetList("Products");  
2.        SPListItem prodItem = lstProducts.Items.Add();  
3.        prodItem["ProdName"] = "PCMMigrator";  
4.        prodItem["Category"] = "Sharepoint";  
5.        prodItem.Update();
6.       //prodItem.SystemUpdate();

Update() :

In the above code the Update() method adds the data to the SharePoint Products list. When this is happening it also internally updates the built in fields the "Modified  with the timestamp of the server when it is updating" and "Modified By with the user who logged in" . And also a new version is created for the item (if versioning enabled for the list).

SystemUpdate() :

It updates the data to the SharePoint list without changing the built in Modified and Modified By fields. It will not create any new version for the item. Basically this method does all the updates to the content database directly.

This method also can be written with boolean argument.

SystemUpdate(false) is equivalent to SystemUpdate()
SystemUpdate(true) is equivalent to SystemUpdate() but creates the new version for the item.

The SystemUpdate also triggers all  the list events as the update does.

In Server Object Model we can use these both Update and SystemUpdate methods. But the client object model will not allow the SystemUpdate method as it is changing data in content db directly without server knowing the changes made. If really we want to achieve this in csom, there are some alternative ways like writing a custom webservice and calling it in csom code.

Hope this is useful,

Thanks,
Purna

Error : Internet Explorer Cannot Display this Feed , while opening REST API

Hello,

This is a small post on how to set feed options to view the REST data in Browser (especially in IE).

As we know generally 

To view the list using REST, we use this url

http://sitename:portnumber/_api/web/lists/getbytitle('listname')

To view all items :

http://sitename:portnumber/_api/web/lists/getbytitle('listname')/items

To view a specific item by its Id :

http://sitename:portnumber/_api/web/lists/getbytitle('listname')/items('ItemId')

But sometimes we encounter with error message(shown below) while opening this urls, since the feed settings are disabled on the browser

To resolve and see the data, set these  options in browser

Go to Tools--> Internet Options--> Content --> Feeds and Web Slices provide---> Settings-->Uncheck the  "Turn on feed reading view"(By default it is checked in state)

Now go and refresh the page(or click on atom view), you can see the List in XML Format like below

Note : In this post I have shown the settings of IE10.  For other versions, setting may vary slightly.

Thanks,

Purna

Accessing SQL Data using Powershell

Hi Guys,

In this post I am explaining how we can access sql db and read the data from a table using PowerShell cmdlets.  The syntax and programming for the sql  operations is same like how we use in C#.

Here am having a sql db called "EmployeeInfo"  with a table "EMP".  Basically this script connects to sql , reads table  data and prints to a text file. The logging and tracing info will  be written to another separate text file.

In this script I am referring these three files

• DBDetails.xml - Contains DB details
• Log File PCM_Powershell_SQL_Log_timestamp - file to which the script writes the Log/Execution information
• Result.txt - A text file to which the result would be written

The dbdetails xml file  looks like below

 

 

here is the PowerShell script (refer the comments for each block)

 Write-Host "Started Execution..."-ForegroundColor Green

    #variables
    $LogFilePath = "C:\Logs\"  # LogPath 
    $LogFilePath += "PCM_Powershell_SQL_Log_"+(Get-Date).ToString("MM_dd_yyyy_hh_mm_ss")+".txt" # Log name with current time stamp
    $DBCredPath= "E:\DBDetails\DbDetails.xml"#DB Details xml File
    $outputfilepath="E:\Result.txt";

    #DB variables
    $Connection=$null;
    $DBServerName=$null;
    $DatabaseName=$null;
    $SQLUserName=$null;
    $SQLPassword=$null;

 #Reading DB details from xml
   
Try
{
 
    "Getting DB details from xml at " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append    
 
    [xml]$dbcred = Get-Content $DBCredPath

    $DBServerName=$dbcred.DBCredentials.sqlservername
    $DatabaseName=$dbcred.DBCredentials.databasename
    $SQLUserName=$dbcred.DBCredentials.username
    $SQLPassword=$dbcred.DBCredentials.password

}

Catch
{   
    "Issue in accessing the DbDetails XML File . Failed with error : " + $_.Exception.Message + " at " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append  
    Exit
}

#Getting Data from SQL and Printing to Text file

Try
{
    #connection String With Windows Authentication
    $SQLConnectionString = "server="+$DBServerName+";database="+$DatabaseName+";Integrated Security=True;" 

    #Connection String with username and password
    #$SQLConnectionString = "server="+$DBServerName+";database="+$DatabaseName+";User ID="+$SQLUserName+";Password="+$SQLPassword+";"
    
    #Query
    $sqlQuery="Select * from Emp";

    #Program
    $sqlCon = New-Object Data.SqlClient.SqlConnection
    $sqlCon.ConnectionString = $SQLConnectionString
    $sqlCon.open()
    $sqlCmd = New-Object Data.SqlClient.SqlCommand
    $sqlCmd.Connection = $sqlCon
    $sqlCmd.CommandText = $sqlQuery
    $empResult= $sqlCmd.ExecuteReader()

    #Loading Result to Data Table
    $empDataTable=New-Object System.Data.DataTable
    $empDataTable.Load($empResult);

    #Printing data table values to text file 
    $format = @{Expression={$_.EmpId};Label="Employee ID";width=20},@{Expression={$_.EmpName};Label="EmployeeName"; width=30}  
    $empDataTable | format-table $format | Out-File $outputfilepath

}

Catch
{   
   "Issue in db function and writing to File . Failed with error : " + $_.Exception.Message + " at " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append  
    Exit
}

# Closing and Disposing Connection Object
try
{

    if($sqlCon -ne $null) 
    {
       if($sqlCon.State -eq 'Closed')
       {
          $sqlCon.Dispose();
          "SQL DB Connection Object Disposed " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append          
       }
       else
       {          
           $sqlCon.Close()         
           "SQL DB Connection Closed " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append
             $sqlCon.Dispose();
           "SQL DB Connection Object Disposed " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append
       }
    }

    "Completed execution at " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append
     Write-Host "Completed Execution, Please check the Log at  $LogFilePath"-ForegroundColor Green

 }
Catch
{   
   "Issue in db function and writing to File . Failed with error : " + $_.Exception.Message + " at " + (Get-Date).ToString("MM-dd-yyyy HH:mm:ss") | Out-File $LogFilePath -Append  
    Exit
}

Here is the output Result.txt  file

In the above  I am reading data in command text(query) mode. But we can read

Using stored procedure

$sqlCon.ConnectionString = $SQLConnectionString
$sqlCon.open()
$sqlCmd = new-Object System.Data.SqlClient.SqlCommand("Your Procedure Name", $sqlCon)
$sqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$sqlCmd.ExecuteNonQuery() 
$sqlCon.close()
$sqlCon.dispose()

 

 

 

using stored procedure with a input parameter

 

$sqlCon = New-Object Data.SqlClient.SqlConnection
$sqlCon.ConnectionString = $SQLConnectionString
$sqlCon.open()
$sqlCmd = new-Object System.Data.SqlClient.SqlCommand("Your proc name", $sqlCon)
$sqlCmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$sqlCmd.Parameters.AddWithValue("@paramname","paramvalue")
$sqlCmd.ExecuteNonQuery() 
$sqlCon.close()
$sqlCon.dispose()

Recovering SharePoint database from the Suspect Mode

Hi Guys,

In this post I am explaining how we recover a sharepoint content database from “suspect” mode.  . This is not a sharepoint specific issue, it’s the issue with the sql  server db. 

Possible reasons to go in to Suspect Mode

• Corruptions in the log/datafiles,
• Hardware failure,
• Antivirus programs ,  
• Low disk space on the server or
• Sudden shutdown of server while executing the transactions which were not rollback by server .etc.

Generally the db will be appear like this in suspect mode

Steps to Recover

Run the below commands in db 

EXEC sp_resetstatus 'SharePoint_Config' 

This command removes the suspect flag on the database 

Then set the db to Emergency mode, so that the db becomes read only and accessible to only privileged users like sysadmins 

ALTER DATABASE SharePoint_Config SET EMERGENCY 

Check the database for any inconsistency by running this following query 

DBCC CHECKDB ('SharePoint_Config') 

Then bring the database in to Single User mode by roll backing any transactions are present in the database 

ALTER DATABASE SharePoint_Config SET SINGLE_USER 

WITH ROLLBACK IMMEDIATE

Now take the backup of the database, since the next commands which we are going to execute are “One Way”  , there is no way to get the state back after execution. So for safety take a backup.

DBCC CheckDB (SharePoint_Config, REPAIR_ALLOW_DATA_LOSS) 

Now bring back the database to Multi User Mode

ALTER DATABASE SharePoint_Config SET MULTI_USER 

Refresh the db, now you should be able to see the db in usual mode . Now the users should be able to access the sharepoint site.

If you find any data loss, you can restore the database from the backup which we took in the above step.

You can cross check all the commands here

Hope this is helpful..!!

Thanks

Purna

Content Search WebPart as a Rotating Banner in SharePoint 2013

Hi Guys,

Content Search Web part is a new web part introduced with SharePoint 2013 in Content Rollup Category. This is mainly used to apply different styling to the dynamic generated content. Here in this post I am going to show you how we can utilize this web part as a Rotating Banner (Picture Carousel). I have taken SharePoint Picture Library is the data source for the Banner.

Create aPicture Library and Upload Some Images

Picture Library Name: LandingPageImages

Upload few Images

Add Content Search Web part on a Page from Content Rollup Category

By default it shows some content. Now we need to map our picture library data source to this webpart by changing the query.

Edit the webpart like below

Click on “Change Query” button in the properties tool pane

And Set these values in the Build Your Query Window

Select a Query: Pictures

This is the data source from where we the web part pulls the content

Restrict By app: Specify a URL and give the url of the landing pages image library (eg :http://mysitename/LandingPageImages)

This is to give the scope  to the result set

Restrict By tag : Don’t Restrict by any tag

This means we are not restricting any content from display

And then Click Ok.  In the Edit Panel Say Apply.

If you see below error that means your newly created picture library is not crawled by search engine.

So go and do the incremental crawl.

Steps: Go to Central Administration àManage Service Applicationsà Search Service Applicationà Manage (in the ribbon)—> Content Sources (From left pane) à Select Local SharePoint Sites (If you are not having particular content source already) àSay Start Incremental Call.

Wait until the crawling has completed.  (Wait until the status becomes Idle for the content source).

Now go back to page and see, Our webpart showing the images in this way.

Now change the properties in the edit pane to show the images in a slider mode.

Set the properties like below No of Items to: 5, Control : Slideshow, Item : Large picture

Now you can see, our content search webpart working as the Image slider..!!!

Thanks, 

Purna

Error :Input String Was Not In a Correct Format in SharePoint Custom List Forms Customization in SharePoint 2007

Hi,

This is a small post regarding an issue which is encountered while customizing the list forms in Sharepoint 2007 designer. This is the issue i encountered when i tried to save the data in EditForm.aspx after its customization. 

Here is the error details

Generally, If the form is not updated properly or if it is in conflict state we will encounter this error.

After doing some analysis on the web, I found  that WSS 3.0 uses a hidden filed to detect the conflicts in the versions of the file. The  field name is owshiddenversion, ,this is the culprit. 

Here is the syntax of the field used in the form Remove this below line from the form to resolve the issue

<SharePoint:ItemHiddenVersion runat="server" ControlMode="Edit"/>

After removing this line, the form worked as expected. But note that, there will be no versions created , instead it will override the data  from every update. 

Hope it helps .., 

Thanks, Purna

 

Error : Cannot retrieve properties at this time and Cannot save your changes in content editor web part or html form webpart

Hello,

In Content Editor Web part or HTML Form Web part , when we are trying to edit the content in Source Editor sometimes we will get this error .

The reason for this is while copying the content from word/some other tools, the format also carried along with the copied text. This format cannot be understand by SharePoint. This is the reason it throws the error. Even though your text appeared on the web part sometimes throws the error while saving the web part.

While saving the Source Editor throws “Cannot retrieve properties at this time ” like this

and while saving the web part it throws this error “Cannot save your changes”.

Solution :

• This is the issue with the browser version you are using, I got this error with IE10. I tried by editing the same with IE8, It worked for me. ( as per my observation this is not working with chrome and IE versions above IE8. )
• Recycle the application pool – This is based on the information we get on the logs. But this is not working all the times.

Thanks,
Purna