Sharepoint BCS (Business Connectivity Services) configuration for SQL Server

Recently we have a request for SharePoint data display, but the data source is SQL Server.So the we leveraged BCS (Business Connectivity Services) to implement this task.

There are several authentication options when configuring an External Content Type to point to a SQL server list (tables, views etc).

Connect with User’s Identity

Connect with Impersonated Windows Identity

Connect with Impersonated Custom Identity

Normally we should choose “Connect with Impersonated Windows Identity”, because the final permission can be controlled at Sharepoint list level. The alias account credentials are kept in the Secure Store Service Application.

I.  SharePoint Central Admin

  1. Central Admin > Manage Service Application. Create a new Secure Store Service Application and edit it. The msdn details step can be referenced from https://technet.microsoft.com/en-CA/library/ee806866.aspx
  2. Create a new target application using the Group type.
  3. Once the Target Application has been created open the associated drop down list and select Set Credentials and enter the Username/Password of the AD alias account.

II. SharePoint Designer

  1. Create new External Content Type
  2. Create new connection and use the Connect with Impersonated Windows Identity option and enter the Secure Store Application ID you just created in Central Admin above
  3. Create a Read List and Read Item operation at the very least
  4. Save the External Content Type
  5. Open up the options menu of the newly created ECT to create an External List

III. SharePoint Central Admin

  1. Once you are finished with the previous step in SharePoint designer it automatically creates your External Content Type
  2. From the Business Data Connectivity Service Application find the new ECT, select the box next to it, and go to Set Object Permissions located on the command ribbon.
  3. Select a user or group with access to the ECT and grant at least Execute permissions to it.
  4. Select Set Metadata Store Permissions located on the command ribbon

The rest steps can be referenced by Connecting to SQL Server Using the External Content Type Feature. Basically it just use Sharepoint designer to connect to the External content type, and create a list to reference the ECT. Please note that all tables/views from SQL Server side need to map the primary key (identity key), and a Sharepoint site needs to be created first for designer to connect with.

There is another article can be referenced as “SharePoint 2010 using BCS with SQL Server database”

Advertisements

SSRS instance name issue and how to change a SQL Server instance name

Recently we are working on installing DPM (a Microsoft product can protect server data during the server upgrading). It required a SSRS to be installed as the reporting server. However, when the SSRS instance name being entered into the installation window, an error popup said instance cannot be found. As this installation actually looking for by WMI tool, so we launch the windows management instrumentation tester (WMI Tester) by run “wbemtest.exe”. Enter the SSRS path as “\\SSRSServerName\root\microsoft\sqlserver\reportserver”, then connected without problem, then run the query: “SELECT * from __namespace”, it list the SSRS instance name, but the weird thing is our instance real name is “SSRSServer_Name”, but the name on the list is “SSRSServer_f5Name”, that’s why the installer cannot find the instance….

After a day, we suddenly realized that SQL Server instance name doesn’t allow “_”, it seems there is no any problem for regular usage even you use the underscore except in some rare case which we met luckily.

So the solution is change the instance name to exclude the underscore then.

STEP 1: running these two queries:

sp_helpserver  
select @@servername

STEP 2: get the network name

  1. Run this in Microsoft SQL Server Management Studio:
    sp_dropserver 'old_name'  
    go  
    sp_addserver 'new_name','local'  
    go
  2. Restart SQL Server service.

Then after that is done run this again, to make sure everything is changed:

sp_helpserver  
select @@servername

DateJS — what should I comment….

On my blog there is a article to introduce how to use date.js which is a powerful lib in the code. And it is a powerful parse date time tool, some of our code segment is like the following.

clearTimeout(timercount);
timercount = 0;

var timeend = new Date(); ;
lasttxtstoptime.value = "";

if (myflag == "theflag") {
    if (lastclickbtn.value == "theValue") {
    timeend = Date.parse(textbox1.value);
    timeend.addSeconds(returnseconds(specialtextbox.value));
   }

However, recently we noticed a very weird bug. When try to parse time like “12:09:21 pm”, it just crashed, so it means this lib can not deal with the noon time with this format (just all time starting with 12:  and end with pm). I did some research and got the root reason. Please see below.

“Date JS was started by Geoffrey McGill in 2007, he abandoned it on May 13th 2008; leaving the Google Code repository stagnant and with many bugs unresolved.

This fork was started improve and maintain DateJS. To keep what is still the most full featured JavaScript Date library alive, maintained, and improved. Currently we’re on track towards a 1.0 release – having fixed almost all the existing bugs and added several new features, improved parsing, and many other changes.”

I just want to say big thanks to those people who still working on this project and would like show my sincerely respects to you guys!  However, maybe no comments to “Geoffrey McGill”, who starting this project and put his uncompleted code with all bugs on the datejs official website. It is very confused for people who looked for the similar libs as when you search from google, the first result returned is his unmaintained “official” website.

The latest code and great version still under maintenance by those fantastic programmers could be found at github as below.

https://github.com/abritinthebay/datejs

How to reference parameter label display instead of the value in SSRS

We have a SSRS report which contains a dataset which has an ID and NAME field, then a parameter was created and set its value to ID from dataset and Label to NAME of dataset. The label can be displayed in preview report and select it, but the selected parameter label display also needs to show on the report, if just use Parameters!Parametername.Value, it just shows the value or ID directly. So the correct way is as below.

Case when the parameter is Single Value :

=Parameters!<ParameterName>.Label

Case when the parameter is Multi Values :

=Parameters!<ParameterName>.Label(0)

OR

=Join(Parameters!<MultivalueParameterName>.Label,", ")

Sharepoint 2013 Sites collection, sites and view

Site Collection

in SharePoint is basically a collection of SharePoint sites that share common features like Content types, Templates, Site columns, permissions, Web Parts etc.

SharePoint Site

basically is a website. SharePoint allows us to create websites of specific types like Personal site, a Team Site, a social media site, a blogs or a Wiki Site etc.

As a SharePoint Site Collection share common features, it facilitates for better administration of our websites. It can be understood by taking a practical example. For example, in my software company, we have multiple departments (Software Development, Finance, Sales, Support etc.). All using SharePoint for different purposes like Document Sharing, Team Collaboration, Blogging and much more. For better administration, we have created a SharePoint Site Collection for each department containing all of its websites. It can be illustrated with the help of a structural diagram as below:
Site Vs Site Collection

From an architecture standpoint, all the content of a site collection must be stored in a single content database. You cannot have a site collection’s content spread out across multiple content databases. Content databases scale with your infrastructure capacity so site collections can share a content database. A site collection can exist in only one content database, but one content database can host the content for multiple site collections. Similarly, any given SharePoint 2013 site can only exist in one site collection, but a site collection can host a multitude of sites. A site cannot exist outside of a site collection.

SharePoint View

With views, you can use filters and styles to show in different ways the information available in your lists and libraries. There’s always a default view activated in your list or library. The automatic default view selected is normally “all items”, but normally got changed by a view contains some meaningful columns instead.

View types in SharePoint

SharePoint view types

Standard view

Standard view type in SharePoint

Calendar View

The calendar in SharePoint was basically a list with a disguise. You can decide which column you want to display on your calendar for a week, a month, a day and so on.

Calendar view type

This also mean that I can turn and list with any information I want into a calendar. As long as I have a date and time column.

Datasheet view

Datasheet view type in SharePoint

Gantt view

Gantt chart picture from wikipedia

The concept of a Gantt chart is similar to a calendar since it’s based on the dates. It shows the progress of different elements in time. It would be very useful for a list of tasks related to a specific project with deadlines for example.

Styles for the Standard view

View styles

Boxed

Boxed Style for SharePoint views

Newsletter

Newsletter style

Shaded

Shaded style

Create and modify the view

In the ribbon at the top under list/library–> “Manage Views”

Manage Views settings

You can modify the view you are currently using by clicking on “Modify View”. Then the same settings you had when you created the view will appear and you will be able to change them. I used this option to change the style of my list. This way, I didn’t have to create a new view for each one, I just had to go back in the view settings and change the style. The view could be public or private as needed.

Avoid Folders

Create a folder sometimes is much easier to manage the documents and also along the same concepts of Windows files system. But when you create a folder, it becomes its own list of items or documents. Therefore, if you create a filter in your library to see, for example, all the documents of 2014, the filter won’t get the elements inside your folders.

My challenge

Recently, we migrated from Sharepoint 2007 to Sharepoint 2013. There are some SSRS reports based on those Sharepoint list. After the migration, there is one report only can display part of the data on the Sharepoint list. he issue is due to new folders created on the list. So as mentioned in last paragraph, those records inside the folder won’t be able to find by SSRS sharepoint connection interface. So the easy solution is change the default view to “All Items”, in this way, SSRS interface has been able to see all the items of the list.

Reason: The SSRS report pulls data only from your “All Items” view in your SharePoint list so make sure “All Items” view is the default view in your list. Even though there is an option to specify the view name in the query builder but that doesn’t work when your are using Web Service for connecting to the SharePoint List.  Also make sure all the columns are present in your list view which you want to show on the reports. One final thing, in the list view setting change the page size from 30 to number of items you are expecting in your report

SQL Server transaction log cannot shrink

This morning, an email alert said one of our Database server disk space is beyond the limit. After the checking, found it is due to one database transaction log was growing to 3 times than regular. So it needs to shrink, however, no matter how I shrink, the transaction log, ldf file still kept its size.
You can reference my previous post to analyses of the shrink log issue, but here is an quick solution.

Solution:
1. Check the status of database
run script as below
select name, log_reuse_wait_desc from sys.databases where name=’mydatabase’

and the value is “LOG_BACKUP”
so it means, this database is full recovery mode and waiting for a transaction log backup.

2. Truncate transaction log
run the script as below
BACKUP LOG mydatabase WITH truncate_only
got the error: “‘truncate_only’ is not a recognized BACKUP option.”
OHOH, seems this command deprecated in SQL Server 2008R2 and later version

then run the following:
BACKUP LOG laddar TO DISK=’NUL:’

it works, and then shrink the database ldf file, shrink successfully!

BTW, there is a trick: the upper process may need to repeat a few times to get the best result. The first round, it may just shrink a little bit, but repeat those steps in second round, then the transaction log size could shrink to 1M.

Caching issue when re-generating pdf files

In our recent project, I met an issue regarding PDF cache, our web application will display the PDFs which generated by another web application, however the provide application could re-generate the PDF due to business process, and the consumer web application always wants to get the latest version of the PDF. The reality is the IIS somehow cache the PDF file, so after the first display, no matter the PDF changed on the back end, the consumer web application always show the first launched pdf which is really annoying.

Solution: Basically, there are quite a few methods to resolve this issue. As the root reason is IE cached the PDF, so you can ask the clients to clear the cache, or turn off IIS cache on the server side (go to ‘Output Caching’, add a new cache rule with file extension ‘.aspx’, tick both ‘User-mode caching’ and ‘Kernel-mode caching’ then under both options, select ‘Prevent all caching’).

But all in all, I prefer a lite solution which won’t involve server/client change. The idea is adding a time stamp at the end of url, so IE browser will treat as a new url so that won’t bother cache to display. The only trick is most likely the pdf link is on a hyperlink field or something like that, but the link will generate during the page load phase, so how to change the time stamp at the time when the client click is an issue.


   <asp:GridView ID="mygridview" runat="server" AutoGenerateColumns="False"
        DataKeyNames="id" Width="100%" SkinID="Professional" CssClass="my-gridview"
        GridLines="None" EnableModelValidation="True">
        <AlternatingRowStyle CssClass="aladdin-gridview-altrow" />
        <Columns>
            <asp:BoundField DataField="id" HeaderText="ID">
                <HeaderStyle HorizontalAlign="Left" />
                <ItemStyle ForeColor="#999999" HorizontalAlign="Left" Width="80px" />
            </asp:BoundField>
           
            <asp:BoundField DataField="DisplayDate" DataFormatString="{0:MMMM d, yyyy}" HeaderText=" My Date"    ItemStyle-HorizontalAlign="Center">
                <HeaderStyle HorizontalAlign="Left" />
                <ItemStyle HorizontalAlign="Left" Width="300px"></ItemStyle>
            </asp:BoundField>

            <asp:BoundField DataField="DisplayValue" HeaderText="Legislature" ItemStyle-HorizontalAlign="Center">
                <HeaderStyle HorizontalAlign="Left" />
                <ItemStyle HorizontalAlign="Left"></ItemStyle>
            </asp:BoundField>
      
            <asp:HyperLinkField  DataNavigateUrlFields="id" Target="_blank" 
                DataNavigateUrlFormatString = "mypdf-fromanotherapp{0}.pdf"
                Text="<img title='Print Session' border='0' src='images/sm-pdf.gif' />"   >
                  <ItemStyle Width="80px" HorizontalAlign="Center" VerticalAlign="Top" />
             </asp:HyperLinkField>
   
        </Columns>
        <HeaderStyle CssClass="my-gridview-header" />
        <SelectedRowStyle CssClass="my-gridview-selectedrow" />
    </asp:GridView>

solution a: direct the link to a new page(display in new window/tab) with query parameters, in the page load event, add time stamp and display the PDF in this new page.
In aspx page, should code like below

<a href="<%# "pdfdisplay.aspx?id=" + DataBinder.Eval(Container.DataItem, ("ID")) %>" target="_blank">%></a>

In aspx.cs, should code like below

protected void Page_Load(object sender, EventArgs e)
{
    string strID = Request.QueryString["id"];
    string newurl = String.Format("{0}mypdf_from_anotherAPP{1}.pdf?dt={2}", sitepath,strID,             DateTime.Now.Ticks.ToString()); //This will result in yourFile.pdf?dt=2839238293
    Response.Redirect(strURL);
}

solution b: use javascript to generate a url with timestamp whenever the link being clicked.

Aspx page:

<script type="text/javascript" language="javascript">
         function openPDFPopup(strOpen) {
             var mydt = new Date();
             timestr = mydt.getTime().toString();
             strOpen =strOpen+ '?t=' + timestr;
             var win = window.open(strOpen, '_blank');
             win.focus();
         }
</script>

            <asp:TemplateField >                     
                <ItemTemplate>
                    <a href="javascript:openPDFPopup('<%# String.Format("{0}mypdf-fromanotherapp{1}.pdf",ConfigurationManager.AppSettings("PDFSite"),Eval("id"))%>')">
                    <img src='images/sm-pdf.gif' border=0px/> </a>                                    
                </ItemTemplate>

                <ItemStyle Width="80px" HorizontalAlign="Center" VerticalAlign="Top" />                
            </asp:TemplateField>