Dec 8, 2014

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



Dec 4, 2014

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()

Oct 15, 2014

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

Oct 9, 2014

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

Sep 10, 2014

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

 

Jun 19, 2014

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

Jun 13, 2014

How to Clear SharePoint Designer Cache


Hello guys,

This post gives  you the information on how to clear the cache which is created by SharePoint Designer. Generally when you log in to the designer you see the info of already opened sites/pages . This is due to the cache which is created by designer when you login/open the site.

Follow these steps to clear the cache.
 
Close the designer and delete the files from below folders(Open these folders in windows explorer)
 
  • %APPDATA%\Microsoft\Web Server Extensions\Cache
  • %USERPROFILE%\AppData\Local\Microsoft\WebsiteCache
If you want to clear the cache frequently ,write a batch file with these commands to run with single click.

Thanks,
Purna
 

 

Jun 8, 2014

SharePoint List Export to Excel in Java Script Client Object Model using RPC (Owssvr.dll)

Hello guys,

Here I am explaining how to implement the OOB 'Export to Excel' feature of SharePoint list using RPC (Remote Procedure Calls) with Javascript CSOM code.

What is RPC :

This is a protocol from Windows SharePoint Services(WSS) , helps in exchanging the data between client and the server which runs with WSS. As all of you know the foundation for Sharepoint Server is came from WSS.
So for this kind of functionalities we can make use of this RPC. Here more about RPC from msdn.

Here I am using this below rpc url to generate the excel report of a list

 "/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={" + listId + "}" + "&View={" + viewId + "}&CacheControl=1";           

The parameters are List GUID and the View GUID.

Get List GUID :

Go to your list settings page: List Settingsà in Url you will find

http:// purnaexperiments:1234/_layouts/15/listedit.aspx?List=%7BCEB4F6D3%2D06AC%2D43FF%2DA95E%2DA158144585A3%7D

Replace  %7B with “{“,
             %2D with “–“ and
             %7D with “}”

After this the you get the GUID like ={CEB4F6D3-06AC-43FF-A95E-A158144585A3}.

Get View Guid :

Go to List Viewà Click Modify this ViewàYour url will look like this follow url


Copy the value after the string ‘View’ in above url and

Replace  %7B with “{“,
             %2D with “–“ and
             %7D with “}”

After this the you get the View GUID like ={ E42BBCD5-8A0F-4092-8B3D-52D9EF805AAB}.

In below code I am getting the LIST GUID and View GUID from their names. 

You can use this function directly to generate the report with List Title and View Title

Code :
  1.   <input id="btnGenerate" onclick=" GeneratePCMReport('MyCustomList', 'All Items’);" value="Generate Excel Report" /> 
  2. // Here the list name is 'MyCustomList' and the view name is ‘All Items’ 

  3.     <script type="text/javascript">    
  4.         var exporturl = null;
  5.         var listPages = null;
  6.         var view = null;
  7.         var web = null;
  8.         function GeneratePCMReport(listName, viewTitle)         
  9.         { 
  10.             if (navigator.appName == "Microsoft Internet Explorer") 
  11.                {
  12.                 var context = new SP.ClientContext.get_current();
  13.                 web = context.get_web();
  14.                 context.load(web);
  15.                 listPages = context.get_web().get_lists().getByTitle(listName);
  16.                 context.load(listPages);
  17.                 view = listPages.get_views().getByTitle(viewTitle);
  18.                 context.load(view);
  19.                 context.executeQueryAsync(getlistInfoSuccess, getlistInfoFailed);
  20.                 }
  21.             else {
  22.                 alert("Please use Internet Explorer to generate the Excel Report");
  23.             }
  24.         } 
  25.         function getlistInfoSuccess(sender, args)
  26.         {
  27.             var listId = listPages.get_id();
  28.             var viewId = view.get_id();
  29.             exporturl = web.get_url() + "/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={" + listId + "}" + "&View={" + viewId + "}&CacheControl=1";          
  30.             window.location.href = exporturl;
  31.         }
  32.         function getlistInfoFailed(sender, args) 
  33.          {
  34.             alert('Issue in getting the Report, Please try later')
  35.          } 
  36.     </script>

Note : As you know the oob  'Export to Excel' works only in IE, the above code also works only in IE.
Since we are calling the same oob function through csom code. That is the reason i have included a condition (navigator.appName == "Microsoft Internet Explorer") in above code to check the browser type.


Thanks,
Purna