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