My Techy Thoughts

SharePoint 2013 Strange Issue - Method Not Found

noreply@blogger.com (Sridhar Babu Kolapalli) — Tue, 21 Oct 2014 03:27:00 -0700

In this post I want to share about the an Issue which I faced today when I was demonstrating certain features in SharePoint 2013.

SharePoint 2013 was setup on Azure VM (A4 - 8 Cores with 14 GB) for the Development environment. Since it is for Dev, I went ahead with the Standalone installation. Successfully Installed and was using from many days with no issues. Today suddenly I was facing the following issue:

Method not found: 'Void Microsoft.SharePoint.WorkflowServices.IWorkflowService.Upgrade(System.Object, System.String, Microso
ft.SharePoint.Upgrade.SPLog)

I am getting the same issue even while adding Add-PSSnapIn "Microsoft.SharePoint.Powershell" in SharePoint management Shall.

Looked at the SP Logs & Event viewer where I can see many issues point to Cache Host is null or Cache Host is not present in the farm. Since its Standalone installation and configured on a workgroup machine I was ignoring this error and continued looking at the errors with no Luck.

The only change I can remember is that installing Worflow Manager 1.0 on the server. And showing SharePoint 2010 workflows in SharePoint Designer 2013.

Finally Looked at the Cumulative Updates on the server and decided to go with the SP1 installation. Unfortunately SP1 installation failed with no error message, so went ahead and restarted the machine and then re-initiated the installation which took good amount of time to complete.

I will suggest to look at the blog for speeding the SP1 installation as it consuming lot of time. After the installation I am able to run the PS Config Wizard successfully.

I hope this helps the SP Guys who are facing the similar issues.

LinkedIn is down today

noreply@blogger.com (Sridhar Babu Kolapalli) — Wed, 20 Mar 2013 05:12:00 -0700

When I try to access the LinkedIn today, it was down may be because of some issue or may be because of the maintenance activity.

The point I want to make it here is how they presented the error message as shown here:

This picture engages the user that they are working on issue. If you observe the picture you can even identify the error id as well :)

Installing & Configuring the Analysis Service in SharePoint Mode

noreply@blogger.com (Sridhar Babu Kolapalli) — Mon, 11 Feb 2013 02:43:00 -0800

Microsoft SQL Server PowerPivot extends SharePoint and Excel Services to add server side process for the PowerPivot workbooks published to SharePoint.

In this blog I am going to explain on how to install and configure Analysis Service in SharePoint Mode. First lets look at the software requirements:

SQL Server 2012 Service Pack1
SharePoint 2013 Enterprise Edition
PowerPivot add-in for SharePoint 2013

SQL Server PowerPivot for SharePoint is only available with SQL Server 2012 Service Pack-1. This can be installed on a server running SharePoint 2013 or even on a different server with no dependencies on SharePoint 2013.

I am going to add one more virtual server "VMServer20" to the existing SharePoint farm explained in my previous post "Configuring Office Web Apps 2013 with SharePoint 2013" as shown below:

Here are the basic steps for this installation & configurations:

1. Installing PowerPivot for SharePoint
2. Configuring the Analysis Service in SharePoint Mode
3. Installing PowerPivot on SharePoint Servers
4. Configure PowerPivot for SharePoint
5. Verifying with a Sample

Step-1: Installing PowerPivot for SharePoint:

Run SQL Server Setup on the Server where you want to install SQL Server PowerPivot for SharePoint, in my case it is VMServer21. Make sure that the server is added to the Domain and run the setup as an administrator and also make sure you logged in with the Setup Account:

Click on the New SQL Server stand-alone installation or add features to an existing installation

Click OK to continue (make sure all the supported rules are passed)

Provide the license key and Click Next to continue

Select the I accept the license terms and Click Next to continue

Click Next to Continue, in case of any warnings for Windows Firewall rule, you can fix it ever after installing. It also might vary based on which environment we are setting up this server (like DMZ).

Here is the important step for this installation, Select SQL Server PowerPivot for SharePoint and Click Next to proceed further.

In this step Optionally you can select Add SQL Server database relation service engine to this installation in case if you want add this server as a content database to the SharePoint farm.

Click Next to proceed. If required you can change the default directory path.

Click Next to Proceed further.

This is another important step in this installation, make sure that the powerpivot instance is available on the server as its mandatory to install only on named instance called "POWERPIVOT".

Click Next to proceed.

Click Next to Proceed.

Provide the domain account on which you want to run this service and Click Next to continue.

If you want to run the instance in Mixed Mode, provide the password for the SA account. Add the current user to the administrators list. Click Next to proceed.

Add the current user to the Administrators list and click Next to continue.

Click Next to proceed.

Click Next to Proceed.

Click Next to Proceed with the Installation.

With this we completed installing SQL Server PowerPivot for SharePoint.

Step-2: Configuring the Analysis Service in SharePoint Mode:

Configure the Excel Services Domain Account which is configured in the SharePoint as part of the Administrator in the Analysis Service. In order to add this account, Open Management Studio & connect to the Analysis Service (VMServer20\PowerPivot) -> Right Click and select properties and Add the account as shown below:

Step-3: Installing PowerPivot Add-In for SharePoint:

Download and install Powerpivot Add-In for SharePoint on all the Web Front End Servers as well as on App Servers. First start with installing on the Central Admin Server by running spPowerPivot.msi

Click Next to Continue.

Accept the License Terms and Click Next to Continue.

Click Next to Continue.

Click Install to initiate the installation.

Click Finish to complete the installation of PowerPivot Add-In for SharePoint.

Step-4: Configuring PowePivot for SharePoint

Open the PowerPivot for SharePoint 2013 Configuration tool in Central Admin Server as shown below:

Select Configure or Repair Powerpivot and Click OK to continue.

Provide the Default Domain Account, Content Database and PowerPivot database instance. Click on Validate to verify the configurations.

Click Run to provision the PowerPivot Service Application in the Farm.

With this we completed the configurations. Now we are ready to verify the same.
Now you can install the PowerPivot Add-In on all the other servers in the SharePoint farm.

Step-5: Verifying the Configurations

You can verify the configurations by Browsing to the Central Admin -> Manage Service Applications, You will see new Service Application called "Default PowerPivot Service Application" as shown in the following image:

Click on Excel Service and Select Manage from the ribbon.

Select Data Model Settings and you will see the PowerPivot instance of the Analysis Service.

Now lets open a sample excel workbook which is published to excel and try slicing the data.

With this we completed Installing, Configuring & Validating the SQL Server PowerPivot for SharePoint 2013.

Let me know your comments!!!

Configuring Office Web Apps 2013 with SharePoint 2013

noreply@blogger.com (Sridhar Babu Kolapalli) — Thu, 24 Jan 2013 05:20:00 -0800

Here is my first blog in this year which I want to provide my hands on experience on how to configure Office Web Apps 2013 with SharePoint 2013 on Premises.

As we know that the Office Web Apps is now a separate product which allows users to view and optionally update office documents using a supported browsers. This not only works on desktop but also supported on mobile devices likes Windows Phones, iPhone and iPads too.

Office Web Apps 2013 comes with improved features and architecture which allows Office Web Apps to provide view capability for Exchange Server 2013 & Lync 2013. In blog post I will explain how we can install & configure the same for SharePoint 2013 on premises environment especially for the developers & SharePoint Administrators.

Here is how my existing SharePoint Farm which I use for development purpose and to simulate the QA/Prod environments:

In this blog I am assuming that the SharePoint farm is available to configure Office Web Apps (OWA). Here are the Simple 9 steps to configure the same:

Step-1: Preparing Server with Prerequisite Software

There are certain prerequisites for installing OWA and make sure the following prerequisites are installed on the server in our case it is VMServer21.

Step-2: Preparing Server with Prerequisite Configurations

There are certain features to be enabled for OWA installtion, we can enable that using following PowerShell script in the Windows PowerShell Command prompt. Make sure that the you are opening PowerShell prompt as administrator.

For Windows Server 2008 R2

Import the necessary module before by executing the following command:

Import-Module ServerManager

And then run the following command:

Add-WindowsFeature Web-Server,Web-WebServer,Web-Common-Http,Web-Static-Content,
Web-App-Dev,Web-Asp-Net,Web-Net-Ext,Web-ISAPI-Ext,Web-ISAPI-Filter,Web-Includes,
Web-Security,Web-Windows-Auth,Web-Filtering,Web-Stat-Compression,
Web-Dyn-Compression,Web-Mgmt-Console,Ink-Handwriting,IH-Ink-Support

For Windows Server 2012

Run the following command:

Add-WindowsFeature Web-Server,Web-Mgmt-Tools,Web-Mgmt-Console,Web-WebServer,
Web-Common-Http,Web-Default-Doc,Web-Static-Content,Web-Performance,
Web-Stat-Compression,Web-Dyn-Compression,Web-Security,Web-Filtering,
Web-Windows-Auth,Web-App-Dev,Web-Net-Ext45,Web-Asp-Net45,Web-ISAPI-Ext,
Web-ISAPI-Filter,Web-Includes,InkandHandwritingServices

After completion of executing the command restart the machine.

Step-3: Installing Office Web Apps Server

Download Office Web App Server from Microsoft Download Center and you can extract the files from the image using any image extract softwares like MagicISO.

After extracting the files from the image, run Setup.exe.

Step-4: Installing Language Packs for Office Web Apps Server

Download necessary language packs from the Microsoft Download Center and run the WebAppsServerLP_en-us_x64.exe

Step-5: Creating Office Web Apps Server Farm

Planning Office Web Apps Server farm is very important based on how we are going to leverage, based on that we have to plan for load balancer, Number of servers, Certificates and so on. In this blog post we are going to see how to configure single server farm on HTTP.

Before configuring make sure that there no applications running on a default port as it is going to be used by OWA. To create a OWA single server farm execute the following PowerShell Command:

New-OfficeWebAppsFarm –InternalURL "http://vmserver21" –AllowHttp -EditingEnabled

Lets see what are these parameters means:

-InternalURL - is the name of the server that runs OWA, in our example it is vmserver21. In case of multiple server farm we have to provide the DNS configured in load balancer.

-AllowHttp - configures the farm to support HTTP.

-EditingEnabled - enables editing in office web apps especially when you want to edit the document from SharePoint.

Here is how it looks after running the command:

Step-6: Verifying Office Web Apps Server Farm

You can validate whether Office Web Apps Server Farm by executing the following URL:

http://vmserver21/hosting/discovery

and gives an xml output as shown below:

In case if you are not getting XML output as shown in the above picture and getting error related registering httpmodules, execute the following command to register ASP.Net framework 4.0 with IIS:

%systemroot%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regiis.exe -iru

Following image shows the output of this command:

After completing this command don't forget to reset IIS by executing IISRESET command in the same command prompt.

With these steps we completed installing OWA 2013 and Configuring it successfully. Now lets see how to configure SharePoint to use OWA.

Step-7: Configuring SharePoint 2013 to use OWA

We can configure SharePoint to use OWA by executing following Powershell Commandlet in Central Admin Server:

New-SPWOPIBinding -ServerName vmserver21.global.com -AllowHTTP

Following screenshot shows the output of the commandlet:

Office Web Apps Server uses the concept of zones to determine which URL (internal or external) and which protocol (HTTP or HTTPS) to use when it communicates with the host, which in this case isSharePoint 2013. By default, SharePoint Server 2013 uses the internal-https zone. Verify that this is the current zone by running the following command:

Get-SPWOPIZone

In case if the output shows other than internal-http as we are using http protocol to communicate with OWA Server, you can set it by executing the following commandlet:

Set-SPWOPIZone "internal-http"

Step-8: Configuring SharePoint 2013 Security Token Service to allow OAuth Over HTTP protocol

To use Office Web Apps withSharePoint 2013 over HTTP in a test environment, you must set AllowOAuthOverHttp to True, Otherwise Office Web Apps will not work.

$config = (Get-SPSecurityTokenServiceConfig)
$config.AllowOAuthOverHttp = $true

$config.Update()

The output of these commands is as shown below:

Step-9: Final Step is Testing OWA is Working as expected

The final step is to test the OWA is working as expected, in order to do this step open the existing site in a browser and upload couple of office documents and following image demonstrates the same:

You can test by clicking on the document which opens within the browser and don't forget to look at the URL as the SharePoint uses WOPI interface (/_layouts/15/WopiFrame.aspx) to render the document

With this we successfully completed the configuration of SharePoint 2013 to Use Office Web Apps.

SQL Server 2012 - Plan Guides

noreply@blogger.com (Sridhar Babu Kolapalli) — Thu, 11 Oct 2012 06:34:00 -0700

You will be wondering what is this new item called "Plan Guides" in Programmability section of a database in SSMS 2012 as shown below:

SQL Server 2012 comes with many features, Plan Guides is one among them.

Plan Guides will let you optimize the performance of the query without modifying the actual query. In most of the real time scenarios the queries might be embedded in the application code itself and we will not have flexibility to modify/optimize the query within the application, this feature definitely helpful in these kind of scenarios.

There are three types of Plan Guides:
SQL Plan Guide
     This matches the queries which are executed in standalone statements.

Object Plan Guide
     This matches the queries which are executed within the contest of User Defined Functions, Stored Procedures and Triggers.

Template Plan Guide
     This matches the standalone queries which can be parametrized to a specified form.

Note that the Plan Guide can be created either by using Management Studio or Transact SQL. I will Transact SQL for all the examples.

Lets take an example to see how SQL Plan Guide works.Let starts with exploring the sp_create_plan_guide procedure.

sp_create_plan_guide procedure is used to create a plan guide for associating the hints to the queries. This built in procedure takes following parameters:

@name -> To provide the name for the Plan Guide
@stmt -> SQL Query Statement where you want to associate the hints
@type -> To Specify the type of the Plan Guide (SQL | Object| Template)
@module_or_batch -> To Specify the object name from the SQL Statement
@params -> To specify query parameters (it is must in case of Type is Template)
@hints -> To specify the hint Option

Now lets create a table and load with dummy data using the following script:

Create Table dbo.Employee
(
    Empno int Identity(1,1) Primary Key,
    EmpName varchar(200) Not Null,
    DOB DateTime Not Null,
    Salary float
)

-- Load Dummy Data for Testing
INSERT INTO dbo.Employee (EmpName, DOB, Salary)
SELECT TOP 10000
    Enames.FullName,
     CAST(RAND(CHECKSUM(NEWID()))*3653.0+CAST(CAST('01-Jan-1950' as DateTime) - CAST('01-Jan-1900' as DateTime) AS INT) AS DATETIME) AS Date,
     CAST(RAND(CHECKSUM(NEWID())) * 10000 as Float)
FROM sys.all_columns T1
CROSS JOIN sys.all_columns T2
CROSS APPLY (
    SELECT EName AS 'data()'
    FROM (
        SELECT TOP (CAST(RAND(CHECKSUM(NEWID())) * T1.column_id * T2.column_id as INT) %2 + 1) EName
        FROM (
        SELECT EName = CAST('Sridhar ' AS VARCHAR(200)) UNION ALL
        SELECT 'Kolapalli ' UNION ALL
        SELECT 'David ' UNION ALL
        SELECT 'Robert ' UNION ALL
        SELECT 'Rajesh ' UNION ALL
        SELECT 'Naresh ' UNION ALL
        SELECT 'Babu ' UNION ALL
        SELECT 'Thomas ' UNION ALL
        SELECT 'Rich ' UNION ALL
        SELECT 'Anthony' UNION ALL
        SELECT 'Richard'
        ) ENames ORDER BY NEWID()) u2
    FOR XML PATH('')
) Enames(FullName)

-- Create a Index on Employee Name
Create Index Indx_Emp_Name on dbo.Employee(EmpName)

Lets execute a sample query and see how is the execution plan for the same query:

SELECT count(*) from dbo.Employee E1 INNER JOIN dbo.Employee E2 on E1.EmpName = E2.EmpName

If you closely observe the execution plan for this query, it uses Merge Join.

Now lets see how we can use the Plan Guides to add the hint to the query without modifying the underlying query. Lets create the plan guide with the following TSQL snippet:

EXEC sp_create_plan_guide
    @name = N'SriSampleGuide1',
    @stmt = N'SELECT count(*) from dbo.Employee E1 INNER JOIN dbo.Employee E2 on E1.EmpName = E2.EmpName',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (TABLE HINT( E1, FORCESEEK))'

This plan guides adds table hint to the query statement mentioned as one of the parameter. Now let try executing the same and see the execution plan, it uses the Table Hint provided in the plan guide.

SELECT count(*) from dbo.Employee E1 INNER JOIN dbo.Employee E2 on E1.EmpName = E2.EmpName

The execution plan for this query looks as:

Note that this sample is only to show how we leverage use plan guides.

Plan Guide is one of the good feature in SQL Server 2012 which helps in optimizing the query without modifying the underlying query. I am sure every application developer appreciate this feature.

Note that creating plan guide either on Object/SQL/template removes the query plan from the plan cache which might impact the overall query performance and also the performance depends on the plan guide too. I will suggest to use plan guide cautiously.

SQL Server 2012 - FileStream Enhancements

noreply@blogger.com (Sridhar Babu Kolapalli) — Wed, 5 Sep 2012 04:33:00 -0700

This post explains the FileStream Concept and its Enhancements in SQL Server 2012 with an example. At a high level this post covers following topics:

What is FileStream
How to Configure FileStream
An Example to leverage FileStream
FileStream Enhancements in SQL Server 2012

First we will look at what is FileStream. In Short FileStream is the ability to store unstructured data like documents, images on the file system but still be managed by SQL server, which means these files are part of Database Managment activities like Backup & Restore.

By Default FileStream is Disabled for database server instance. This feature can be configured during the SQL Server Installation or even post Installation. Following steps explain on how we can enable & configure FileStream.

Open SQL Server Configuration Manager by Clicking Start -> All Programs -> Microsoft SQL Server 2012 -> Configuration Tools Folder -> SQL Server Configuration Manager

In the SQL Server Configuration Manager console highlight the SQL Server Services as shown in the picture

Select the SQL Server instances in the right pane for which you want to enable FILESTREAM, Right click on the server and select properties as shown in the picture:

In the property window select FileStream Tab as shown below

Check the FILESTREAM For Transact-SQL Access to enable FileStream and then select Enable FILESTREAM For File I/O Access so that we can read and write data from Windows. And also type the name of the Windows share name "FileStreamShare" or you can even leave it to default value as shown below

If there is a need for the remote clients to access the data then we need to enable Allow Remote Clients Access To FILESTREAM Data otherwise leave it to default. Now go ahead click Apply followed by OK button.

With this we completed enabling FileStream on a SQL Server Instance. Now we will see in the following steps on we can enable this feature for a particular database.

Let us start with creating a Database called "FileStreamSample" using following script which is generated in SSMS (SQL Server Management Studio).

To execute the script, Open SSMS by Clicking Start -> All Programs -> Microsoft SQL Server 2012 -> SQL Server Management Studio and then copy & paste following code. Now execute the code either by pressing F5 key in the keyboard or by clicking on execute icon.

CREATE DATABASE [FileStreamSample]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'FileStreamSample', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileStreamSample.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FileStreamSample_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileStreamSample_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [FileStreamSample] SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE [FileStreamSample] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [FileStreamSample] SET ANSI_NULLS OFF
GO
ALTER DATABASE [FileStreamSample] SET ANSI_PADDING OFF
GO
ALTER DATABASE [FileStreamSample] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [FileStreamSample] SET ARITHABORT OFF
GO
ALTER DATABASE [FileStreamSample] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [FileStreamSample] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [FileStreamSample] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [FileStreamSample] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [FileStreamSample] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [FileStreamSample] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [FileStreamSample] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [FileStreamSample] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [FileStreamSample] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [FileStreamSample] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [FileStreamSample] SET DISABLE_BROKER
GO
ALTER DATABASE [FileStreamSample] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [FileStreamSample] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [FileStreamSample] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [FileStreamSample] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [FileStreamSample] SET READ_WRITE
GO
ALTER DATABASE [FileStreamSample] SET RECOVERY FULL
GO
ALTER DATABASE [FileStreamSample] SET MULTI_USER
GO
ALTER DATABASE [FileStreamSample] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [FileStreamSample] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [FileStreamSample]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [FileStreamSample] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

Once Database is created, Select the database and click on "New Query" Icon as shown below:

Now we have to configure the file stream access level for the database "FileStreamSample" by executing the following Transact-SQL code.

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

There are three access level options for FileStream as described below:

0 -> No FileStream Support
1 -> Enable FileStream for Transact-SQL access only
2 -> Enable FileStream for Transact-SQL as well as Win32 Streaming access

In our example we are going with the last option as to provide support for Win32 Streaming.

Now In Query Editor, type the above Transact-SQL code and then click on Execute as shown below:

The final step is to configuring File Group & Database Files to store FileStream Data. Execute the following commands in the Query Editor:

Use Master
ALTER DATABASE [FileStreamSample]
ADD FILEGROUP [FileStreamSampleFG] CONTAINS FILESTREAM
GO
ALTER DATABASE [FileStreamSample]
ADD FILE (
NAME = N'FileStreamSample_FilestreamFile',
FILENAME = N'C:\FileStreamSample_FilestreamFile'
) TO FILEGROUP
[FileStreamSampleFG]
GO

We can also create the File Group and Files using Managment Studio Interface itself but its always easy to go with Transact-SQL Script.

Now lets create a table using following SQL Script:

Create Table [FSExample]
(
    [fid] uniqueidentifier ROWGUIDCOL NOT NULL Unique,
    [sid] [int] NOT NULL Unique,
    [fcontent] [varbinary](max) FileStream NULL
)

There are certain point we need to keep in mind while designing tables containing FileStream. Here are few important point:

1. FileStream columns must be accompanied by a corresponding uniqueidentifier ROWGUID column.
2. These kind of table must also be accompanied by a unique index
3. ROWGUIDCOL is required if we need to access FileStream data using Win32 APIs
4. FileStream File Groups and Containers should reside on volumes other than the OS

All the DML operations are pretty much same as any other table. Following are some of the examples:

Insert Into FSExample values (newid(), 1, CAST('my first content' as varbinary(max)))

Select * from FSExample

Update FSExample
Set fcontent = CAST('Updated content' as varbinary(max))
WHERE sid = 1

Select * from FSExample

Delete FROM FSExample WHERE sid=1

Select * from FSExample

While executing these commands keep an eye on folder "C:\FileStreamSample_FilestreamFile", you will notice news files after insert operations.

FileStream Enhancements in SQL Server 2012

There are many enhancements to FileStream in SQL Server 2012. Some of them are:

Ability to support multiple containers & file groups
Ability to specify the max size for the containers
Ability to add multiple storage devices for scalability

You can refer to my previous post on GC related improvements @ SQL Server FileStream GC Made Easy

SharePoint 2013 - Key Changes in Service Applications

noreply@blogger.com (Sridhar Babu Kolapalli) — Sun, 2 Sep 2012 22:36:00 -0700

There are few key changes to Service Applications with SharePoint 2013. Lets see what are these changes:

Web Analytics Service Application

       Web Analytics Service Application is introduced in SharePoint 2010 and it is no more available as a Service Application in SharePoint 2013. It is fully integrated with Search Service Application.

Office Web Application

      Office Web Application is also introduced with SharePoint 2010 and it is no longer a Service Application in SharePoint 2013 and it is not even comes as a Separate Installable for SharePoint.

App Management Service Application

      App Management Service Application is a new feature in SharePoint 2013, this feature is very similar to Mobile App Store/Market place. This feature allows you to purchase an SharePoint Applications directly from Microsoft Market Place and the licensing for these Applications are managed by the App Management Service.

Machine Translation Service Application

      Machine Translation Service is a new service application introduced with SharePoint 2013 Preview. This Service App provides automatic machine translation of files or all the files within a Document Library or even Site. When the Machine Translation Service application processes a translation request, it forwards the request to a cloud-hosted machine translation service, where the actual translation work is performed.
The Machine Translation Service application processes translation requests asynchronously and synchronously. Asynchronous translation requests are processed when the translation timer job executes.

Machine Translation Service also includes a client object model (CSOM) that enables access to the Machine Translation Service API for online, on-premises, and mobile development.

More details about this Service App is available at MSDN Article.

Work Management Service Application

     The Work Management Service Application provides functionality to aggregate tasks to a central location.
    - Users get the possibility to view and track their to-dos and tasks.
    - Tasks can be cached to a users personal site.
    - Tasks can be aggregated from Exchange, Project Server and SharePoint.

SQL Server FileStream GC Made Easy

noreply@blogger.com (Sridhar Babu Kolapalli) — Mon, 27 Aug 2012 02:20:00 -0700

In this post I want to explain FileStream System Procedures added in SQL Server 2012. Before jumping into this topic we will spend couple of mins to understand what is FileStream and the issues we might encounter with SQL Server 2008 R2 version?

FileStream is the ability to store unstructured data like documents, images on the file system but still be managed by SQL server.
Here are some of the advantages:

Store and retrieve BLOBs together with relational data in a single data store
Unstructured Data is included in backup and restore operations
Crud operations on BLOBs and relation database occurs on the same DB Transactions
NTFS File stytem is faster compared to SQL Server especially with the File Operations
No limit on the file size but the applications can enforce this limit
.Net APIs like SQLFileStream can be leveraged to interact with FileStream Objects

By default this feature is disabled and can be enabled either by using SQL Statements or by Management Studio. I guess I am going out of this topic, lets come back.

I am sure who ever leveraged FileStream feature in SQL Server 2008 or 2008 R2 might faced issues or might be looking for a better way for initiating Garbage Collection. There is an interesting blog on FileStream Garbage Collection and how it works.

With SQL Server 2012 we have new set of procedures which helps us to initiate the FileStream Garbage Collection:

sp_filestream_force_garbage_collection:
- This system procedure forces the garbage collector to run and delete unneeded files. The FileStream garbage collector runs automatically but if you want to call explicitly you can do it by using this procedure.

sp_kill_filestream_non_transacted_handles:
- This system procedure helps you to close the non transaction FileStream handlers.

These two System Procedures are very useful for any Database team leveraging FileStream Feature.

Visual Representation of SQL Joins

noreply@blogger.com (Sridhar Babu Kolapalli) — Tue, 14 Aug 2012 09:09:00 -0700

When I am browsing through net today, I found an interesting article on Visual Representation of SQL Joins. I always draw this image in most of my SQL Trainings to explain Joins. I am so surprised to see this image :)

Venn Diagrams are the easiest way to explain different types of joins like Inner, Left, Right and so on.

It also helps us to easily understand the Set Operations like Union, Intersect and Minus in any Relational Database Queries.

SharePoint Server 2013 Installation and Configuration

noreply@blogger.com (Sridhar Babu Kolapalli) — Tue, 14 Aug 2012 07:54:00 -0700

In this article I will show how to install & configure SharePoint 2013 Preview.

We can install SharePoint either on a Physical Server or an Virtual Box, In either of the case we have to make sure at least we have minimum Hardware & Software needs for SP 2013 as provided in the technet article.

In this article I am going to explain on how we can create a SharePoint 2013 Virtual Image for development/evaluation purpose only. We need the following software's to setup:

1. Oracle Virtual Box or VM Player
2. Windows Server 2012 Preview Download
3. SQL Server 2012 Enterprise Edition Download
4. SharePoint 2013 Preview Download

While I am writing this blog on how I setup my virtual server using Oracle Virtual Box, I found another blog "SharePoint 2013 Development Image" where the blogger explained in much granular level. I will suggest to refer to this on how to setup SharePoint 2013.

I should have found this blog even before I capture the images during my setup which might saved lots of time for me :)

The only difference I did during my SharePoint setup is installing Pres-Requisites manually one by one as my environment is in Isolated network with no access to Internet (for some security reasons). I might have done this though the command prompt.

Following are some of the screen shots:

1.SharePoint 2013 Central Administration Screen Shot

2. SharePoint 2013 Site Collection Screen Shot

I found one interesting spelling mistake in the setting page as shown below:

Once I evaluate certain feature, I will share it in this blog.

Windows Server 2012 - A Quick View

noreply@blogger.com (Sridhar Babu Kolapalli) — Tue, 14 Aug 2012 07:13:00 -0700

When I am evaluating windows 8 especially its Metro UI, I was wondering how will be the Windows Server UI looks like. Recently I get a chance to Setup Windows Server 2012 Release Candidate for evaluating SQL Server 2012 features and SharePoint 2013 features.

Here are the some of the screen shots on how Windows Server 2012 looks, A complete different experience compared to previous editions.

1. Login Screen

2. Landing Screen

3. After installing other software's likeSQL Server 2012 & SharePoint 2013, you are going to see many more tiles for easy access as shown in the following image:

4. Server Manager Screen - Now it helps to do most of the admin operations from here itself.

I liked the way Microsoft moving towards Metro UI for all their Apps including Windows Server!

Tracking Web Page Load and Site Speed Using Google Analytics

noreply@blogger.com (Sridhar Babu Kolapalli) — Thu, 19 May 2011 21:29:00 -0700

Recently I came across one of the interesting feature "Track Page Load Time" in Google Analytics APIs.

This feature provides the metrics on the Page Load Time within the Site Speed Report. This gives us a good opportunity to identifying slow loading pages and fix them for improving the Search Engine Ranking as load time is one of the factor in the page ranking.

Following is the code snippet for capturing the Page Load Time:

<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-XXXXX-X']);
_gaq.push(['_trackPageview'],['_trackPageLoadTime']);

(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>

Note that by default Site Speed Reports are not enabled. The function call _gaq.push['_trackPageLoadTime']) enables this report.

Windows 2008 R2 as a Virtual Server in Sun Virtual Box

noreply@blogger.com (Sridhar Babu Kolapalli) — Wed, 12 Jan 2011 23:17:00 -0800

Recently I am trying to setup Windows 2008 R2 as a Virtual Server using Sun Virtual Box on a Sony VAIO machine running Windows-7 64 bit version.

When ever I start the Virtual Server its keep on throwing an error as shown below:

"VT-x/AMD-V hardware acceleration has been enabled, but is not operational. You 64-bit guest will fail to detect a 64-bit CPU and will not be able to boot.

Please ensure that you have enabled vt-x/AMD-V properly in the BIOS of your host computer."

Then I restarted my machine and went into BIOS setting. Within the BIOS setting goto Intel Virtualization Technology (2nd Tab in BIOS setting), by default it is disable. We have to enable for this setting for making Virtualization to work.

I am using Sony VAIO model number PCG-71312L.

Silverlight 4 Training

noreply@blogger.com (Sridhar Babu Kolapalli) — Tue, 4 Jan 2011 20:11:00 -0800

Silverlight 4 training is available for download from Microsoft download site.

This training contains lots of Videos, Hands on labs which helps us to build the business applications.

Google Analytics on SharePoint

noreply@blogger.com (Sridhar Babu Kolapalli) — Tue, 28 Dec 2010 03:02:00 -0800

Seeing the title we might feel that what is the big deal in using Google Analytics on SharePoint content management sites, yes we can get the page views by just embedding few lines of code provided by Google Analytics site.

In the Current implementation we went with a different approach for providing flexibility to Content Authors to manage the Custom Variables, Events and so on...

At a high level following image shows you the flow:

At a high level we used following components in SharePoint which are the inputs to Google Analytics APIs

1. Custom Site Columns

There a set of site columns created for the publishing pages. Few of them are grouped with the group name called "MetaTagGroup". All the columns which are part of this group will be converted to Metatags.

We used a delegate control in SharePoint for generating these meta tags. For more info you can refer to the article on Meta tag generator

Following image shows the set of custom Site Columns mapped to MetaTagGroup

Now the content author has a flexibility to define the values for these Site Columns or Publishing page attribute which inturn converted Meta Tags and then passed to Google Analytics variables as explained in the next step.

2. Additional javascript layer on Google Analytics APIs

An additional javascript functions are created to call respective Google Analytics APIs. And also this javascript contains an array to map Meta tag name with the GA variables, Scope and their slots as shown below:

var arrGAVariables = [
{'GAVariable':'Segment-Type', 'MetaTagName':'Segment-Type', 'Scope':3, 'Slot':3},
{'GAVariable':'Target-Audience', 'MetaTagName':'Target-Audience', 'Scope':1, 'Slot':1},
{'GAVariable':'Business-Technology', 'MetaTagName':'Business-Technology', 'Scope':3, 'Slot':4}
];

Please feel free to contact me to get more insights on this implementation.

Work around for Disable offline folder in Outlook Client

noreply@blogger.com (Sridhar Babu Kolapalli) — Tue, 28 Dec 2010 01:30:00 -0800

Recently when I upgraded from outlook 2003 to outlook 2007 at work place (as part of Windows 7 upgradation - I know its too late to go with Win7, we cant help it as we have to adhere to corporate standards but not yet personal laptop :)), I am not able to make the existing pst file as the default for downloading the emails to local pst.

Following are the steps to change the default data file

1. Open Outlook Client -> File Menu -> Data File Management...

Before this step I added the existing outlook 2003 pst file using File Menu -> Open -> Outlook Data File (browse to the existing pst file).

2. Select the respective file in the data files popup window and click on "Set as Default" button.

When we select this option, it promts us to create a new outlook data file or disable the offline folder before setting as default data file.

Now to disable the offline use we can with the steps provided at the msdn article @

http://office.microsoft.com/en-us/outlook-help/disable-an-offline-folder-file-ost-HP005242264.aspx

Now in the last step you will come to know that the "Disable Offline Use" button is always disable with out allowing us to change.

Basicalling this complete issue happens when your pst file is in the "Outlook 97-2002 Personal Folders File" format. The easiest work arond for this problem is by creating a new pst file in Outlook Client 2007 and import the content from the existing pst file. It took around 10 mins for 1.5 GB pst file.

After importing you can make it as a default data file for downloading the emails to local pst file.

Sql Server 2008 Standard Edition Installation on Win 2008 R2 Issue

noreply@blogger.com (Sridhar Babu Kolapalli) — Thu, 29 Apr 2010 09:49:00 -0700

In the process of setting up Project Server 2010 and SharePoint 2010 on Windows Server 2008 R2 faced a strange issue as shown in the following image.

This instillation error occurred even though after verifying the Software and Hardware requirements as well as prerequisites for SQL Server 2008 installation with the initial steps of the installation.

Error indicates that there is a network error while reading the file Sql21D7.tmp.msi. When I checked, this file is not available in the specified path. I even tried to install the Database engine with Full Text Search and Replication and also even tried installing only the Database engine, but still installation failed with the same error.

I verified all the installation documents as well googled for this error with no luck.

Finally, copied and pasted one of the msi file in the installation location (indicated in the error message) and renamed the file to Sql21D7.tmp.msi. After this change we are able to install the SQL Server 2008 successfully.

Right now I am not sure where it will impact, i will post my findings after working on the server for next few days...

New Features in SQL Server 2008 R2

noreply@blogger.com (Sridhar Babu Kolapalli) — Fri, 12 Mar 2010 03:47:00 -0800

Last two day I was in Microsoft SQL Server 2008 R2 HOL Session in Microsoft, India where we came to know the features and enhancements in SQL Server 2008 R2. Following are the feature set at a high level:

1. Data Tier Application Project - Nice way of managing database projects and deployment approach...

2. Master Data Services (Master Data Management) - Initial introduction of MDM within SQL server from MS

3. StreamInsight

4. PowerPivot - Self Analysis Service with in Excel 2010

5. Complex Event Processing (CEP)

6. Visual Studio 2010 feature for T-SQL

7. Reporting Services Enhancements

Hope I will get some time to evaluate these features and to use in our Design and architecture...

SharePoint 2010 Beta Installation Issue cum Resolution

noreply@blogger.com (Sridhar Babu Kolapalli) — Tue, 8 Dec 2009 05:45:00 -0800

Recently when I am installing SharePoint 2010 Beta on one of our sandbox we faced following issue:

Microsoft.Office.Server.UserProfiles.UserProfileException: The request channel timed out while waiting for a reply after 00:00:19.9340000. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding. The time allotted to this operation may have been a portion of a longer timeout. ---> System.TimeoutException: The request channel timed out while waiting for a reply after 00:00:19.9340000. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding. The time allotted to this operation may have been a portion of a longer timeout. ---> System.TimeoutException: The HTTP request to 'https://myserver:32844/d31735f8a1b34f3a8263707181c7e298/ProfilePropertyService.svc' has exceeded the allotted timeout of 00:00:19.9990000. The time allotted to this operation may have been a portion of a longer timeout. ---> System.Net.WebException: The operation has timed out

After trying all possible approaches, following steps resolved the issue:

Step:1
Open the Channel.Config file available @
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebClients\Profile

Step:2
Replace the "sendTimeout="00:00:20" to "sendTimeout="00:01:20"

Step:3
Reset the IIS (Option as the wizard will restart the service)

Step:4
Rerun the wizard, you can successfully complete the SharePoint 2010 installation.

Text Parsing in SQL Server

noreply@blogger.com (Sridhar Babu Kolapalli) — Wed, 25 Nov 2009 03:17:00 -0800

There are lots scenarios in database procedure we may required to parse the test. Most common scenario is to parse the comma separated text.

For example if we want to get the details of few employees, we will send those employee ids separated by a delimiter as a parameter to the stored procedure.

Till SQL Server 2000 we will be parsing the text character by character and then we will split according to the delimiter.

Following is the general split function which we can use in any version of SQL Server:

Function Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[FNSplitString]
(
-- Add the parameters for the function here
@string nvarchar(4000),
@delimiter nvarchar(10)
)
RETURNS
@StringValueTable TABLE
(
-- Add the column definitions for the TABLE variable here
Pos int IDENTITY(1,1),
StringValue nvarchar(4000)
)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @delimiterPos int
DECLARE @StringValue nvarchar(4000)

SET @string = @string + @delimiter

-- Loop thru all the characters
WHILE(LEN(@string)>0)
BEGIN

SET @delimiterPos = CHARINDEX(@delimiter,@string)

SET @StringValue = SUBSTRING(@string,0,@delimiterPos)

-- Insert the record into table variable
INSERT INTO @StringValueTable VALUES (@StringValue)

SET @string = SUBSTRING(@string,@delimiterPos+1,LEN(@string))

-- End the loop if there no more elements
IF @delimiterPos = 0
BEGIN
Break
END

END

RETURN
END

Test Sample:

SELECT * FROM [dbo].[FNSplitString]('a,bc,def,ghij,klmno,pqrstu,vw,x,yz',',')

With the new feature "CTE" in SQL Server 2005 and above we can implement the same very easily without any explicit looping mentioned in the above code.

Following is the split function using CTE:

Function Code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[FNSplitString_New]
(
-- Add the parameters for the function here
@string nvarchar(4000),
@delimiter nvarchar(1)
)
RETURNS
@StringValueTable TABLE
(
-- Add the column definitions for the TABLE variable here
Pos int IDENTITY(1,1),
StringValue nvarchar(4000)
)
AS
BEGIN

Declare @SourceString nvarchar(MAX)

SET @SourceString = ',' + @string + ',';

-- Query using CTE
WITH CTE (Indx, Position) AS
(
SELECT CHARINDEX(@delimiter, @SourceString) Indx, 1 AS Position
UNION ALL
SELECT CHARINDEX(@delimiter, @SourceString, Indx+1) , Position + 1
FROM CTE WHERE CHARINDEX(@delimiter, @SourceString , Indx+1) <> 0
)
INSERT INTO @StringValueTable(StringValue)
SELECT SUBSTRING(@SourceString, B.Indx+1, A.Indx - B.Indx - 1) Val
FROM CTE A INNER JOIN CTE B ON A.Position = B.Position + 1
OPTION (MAXRECURSION 5000)

-- In the above query recursive option is limited to 5000
-- By default it is 100, which means we cannot loop recursively more than 100 times

RETURN
END

Test Sample:

SELECT * FROM [dbo].[FNSplitString_New]('a,bc,def,ghij,klmno,pqrstu,vw,x,yz',',')

In most of our applications we are using first approach as we are reusing from long time. Now we started taking the power of new features like CTE in our regular design and development.

We can also think about passing the 'n' parameter values to a procedure as an xml. And by using XML query capability we can get the individual values as part of the query itself.

SQL Server Table Variables in Transactions

noreply@blogger.com (Sridhar Babu Kolapalli) — Tue, 17 Nov 2009 08:47:00 -0800

Today in one of the forum I found an intersting question to execute few SQL Statments as not part of the enclosed the transaction.

Let us consider we have a following Scenario:
We have lots of statements participating in the transaction as

Begin Transaction

SQL Inerts Statement 1
Log a message in Log Table
SQL Update Statement 1
Log a message in Log Table
SQL Delete Statement 1

Commit Transaction / Rollback Transaction

In the above block if the transaction is rollbacked then we will be loosing the enteries in Log table too. But if we need the data available in the Log tables without participating in the transaction...

Now the Question is How we can acheive it...?

Immediately after see the question, most of the people thinks about using inner transaction. we have to remember that:

"Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed."

So the Inner/Nested Transaction are ruled out... Now we have to think that is there any way we can get the solution...?

Yes, we can do that by using Local Table Variables. One of the importatnt behaviour of the Local Table Variable will not have any impact of the transaction. Which means irrespective of the Commit/Rollback there will not be any change in the local table variables.

Following the sample demomstrating the same:

-- Create a Sample Table to test
Create Table tblSample(col1 varchar(10))

-- declare log table variable
Declare @Tbl table(Col1 varchar(10))

--
Begin TRansaction

-- Insert Data into table variable
INSERT INTO @Tbl values ('A11')

-- Insert data into actual table
INSERT INTO tblSample
SELECT * FROM
(
Select 'A' Col1
UNION ALL
Select 'B' Col1
) T

-- Insert data into table variable
INSERT INTO @Tbl values ('B11')

rollback Transaction
--commit Transaction

SELECT * from @Tbl
SELECT * from tblSample

Run the above block once with rollback and second time with commit transaction, you will find the same data in the log table @tbl.

Hope this provides some additonal info on local table variables and how we can use in these scenarios.

FusionCharts Free are completely free and open source

noreply@blogger.com (Sridhar Babu Kolapalli) — Mon, 7 Sep 2009 20:36:00 -0700

We can use FusionCharts Free for displaying any graphical info from SharePoint as explained in the previous posts.

Now FusionCharts Free are completely free and open-source with source code.
Please have a look into it:

FusionCharts

XML DML INSERT operation in SQL Server

noreply@blogger.com (Sridhar Babu Kolapalli) — Tue, 18 Aug 2009 08:56:00 -0700

Recently in one of our project the requirement is to insert element within existing XML which is a column(XML Data type) in a table. In other words we have to insert dynamic xml in another xml. I hear the similar requirement in most of the forums...

We can use one of the XML DML operation "INSERT" - Inserts one or more nodes identified by Expression1 as child nodes or siblings of the node identified by Expression2.

Using this Insert operation we can insert only a static nodes as shown below:

-- Declare Local Variables
DECLARE @MainXML XML
DECLARE @NewXML XML

-- Sample XML with employee information
SET @MainXML = '<employees>
<employee>
<fname>John</fname>
<lname>V</lname>
<desig>SSE</desig>
</employee>
<employee>
<fname>Pascal</fname>
<lname>J</lname>
<desig>SE</desig>
</employee>
<employee>
<fname>Naveen</fname>
<lname>M</lname>
<desig>Engineer</desig>
</employee>
</employees>'

-- Inserting static xml
SET @MainXML.modify('insert (<employee><fname>Test</fname></employee>) as last into (/employees)[1]')

-- To verify the reault
select @MainXML

Now if we want to insert an XML which is available within the local variable @NewXML:

-- Assigning the sample xml to the local variable
SET @NewXML = '<employee><fname>Test</fname></employee>'

Now if we try to insert this xml from the local variable using sql:variable:

SET @MainXML.modify(' insert {sql:variable("@NewXML")} as last into (/employees)[1]')

We will get an error message indicating:

XQuery [modify()]: An expression was expected

In SQL Server 2005 the work around will be:

1. Dynamic Query
2. Combination of the XML DML Operations

Here I am not concentrating on the Dynamic Query approach as it is pretty simple to implement and it may not be a best approach when compared to other.

Following set of queries using DML operation, we can insert XML data from a local variable:

-- Appending both the Main XML and New XML to insert into Main XML with a root node
SET @MainXML = '<root>' + CAST(@MainXML AS VARCHAR(MAX)) + '<new>' + CAST(@NewXML AS VARCHAR(MAX)) + '</new>' + '</root>'

-- Moving employee element from new element to main xml as part of employees element
SET @MainXML.modify(' insert (//new/employee) as last into (/employees)[1]')

-- Deleting the new element
SET @MainXML.modify(' delete (//new)')

-- Steps to move employee element to the end and to delete the root element
SET @MainXML.modify(' insert (//root/employees) after (/root)[1] ')
SET @MainXML.modify(' delete (/root)')

-- Selecting the xml for final verification
SELECT @MainXML

In Summary following is the sample Script in SQL Server 2005 to insert a dynamic XML into another XML:

DECLARE @MainXML XML
DECLARE @NewXML XML

SET @MainXML = '<employees>
<employee>
<fname>John</fname>
<lname>V</lname>
<desig>SSE</desig>
</employee>
<employee>
<fname>Pascal</fname>
<lname>J</lname>
<desig>SE</desig>
</employee>
<employee>
<fname>Naveen</fname>
<lname>M</lname>
<desig>Engineer</desig>
</employee>
</employees>'

SET @NewXML = '<employee><fname>Test</fname></employee>'

SET @MainXML = '<root>' + CAST(@MainXML AS VARCHAR(MAX)) + '<new>' + CAST(@NewXML AS VARCHAR(MAX)) + '</new>' + '</root>'

SET @MainXML.modify(' insert (//new/employee) as last into (/employees)[1]')

SET @MainXML.modify(' delete (//new)')

SET @MainXML.modify(' insert (//root/employees) after (/root)[1] ')
SET @MainXML.modify(' delete (/root)')

SELECT @MainXML

These many steps are not required for SQL Server 2008 as it supports to insert the dynamic XML directly into another XML. Following is the sample script for SQL Server 2008:

DECLARE @MainXML XML
DECLARE @NewXML XML

SET @MainXML = '<employees>
<employee>
<fname>John</fname>
<lname>V</lname>
<desig>SSE</desig>
</employee>
<employee>
<fname>Pascal</fname>
<lname>J</lname>
<desig>SE</desig>
</employee>
<employee>
<fname>Naveen</fname>
<lname>M</lname>
<desig>Engineer</desig>
</employee>
</employees>'

SET @NewXML = '<employee><fname>Test</fname></employee>'

-- One statement in SQL Server 2008 to do all the operation what we mentioned above for SQL Server 2005
SET @MainXML.modify(' insert {sql:variable("@NewXML")} as last into (/employees)[1]')

select @MainXML

This we implemented in most of our applications, hope it will be useful to most of the folks...

An Introduction to Infopath 2010 - Part-3 - Creating Cascading Dropdowns

noreply@blogger.com (Sridhar Babu Kolapalli) — Thu, 13 Aug 2009 21:21:00 -0700

In continuation to my previous posts

An Introduction to Infopath 2010 - Part-1 - An Overview

An Introduction to Infopath 2010 - Part-2 - Creating Basic Forms

In this post we will see how easily we can create Cascading Dropdown controls in Infopath 2010 Desinger tool.

Most of the times in our applications Cascading Dropdown (When there is change event for one of the combo box should reflect the values in another combo box. For example when we change the Country in the country Combobox, then the states should reflect in the State Combobox for the selected country) is a common requirement. Right now in SharePoint we are achieving this requirement by using JavaScript or a JQuery.

Infopath 2010 made our job more easy by providing this functionality out-of-the-box. Now we will see a sample for creating a form to demonstrate the Cascading Dropdown functionality.

Step-1: Creating a Sample Form

Create a sample form as shown in the following picture with two dropdown list controls for Country and State.

Step-2: Creating an XML file

For this example we are considering xml as a data source. Following is the xml format for storing the Country and State data:

<?xml version="1.0" encoding="UTF-8"?>
<root>
<country displaytext="India" displayvalue="India">
     <state displaytext="Karnataka" displayvalue="Karnataka"/>
     <state displaytext="Andhra" displayvalue="AP"/>
     <state displaytext="Maharastra" displayvalue="Mumbai"/>
</country>
<country displaytext="US" displayvalue="US">
     <state displaytext="Alabama" displayvalue="1"/>
     <state displaytext="Alaska" displayvalue="2"/>
     <state displaytext="Arizona" displayvalue="3"/>
     <state displaytext="Arkansas" displayvalue="4"/>
     <state displaytext="California" displayvalue="5"/>
     <state displaytext="Colorado" displayvalue="6"/>
     <state displaytext="Connecticut" displayvalue="7"/>
     <state displaytext="Delaware" displayvalue="8"/>
     <state displaytext="Dist Columbia" displayvalue="9"/>
     <state displaytext="Florida" displayvalue="10"/>
     <state displaytext="Georgia" displayvalue="11"/>
     <state displaytext="Hawaii" displayvalue="12"/>
     <state displaytext="Idaho" displayvalue="13"/>
</country>
<country displaytext="China" displayvalue="China">
     <state displaytext="Anhui" displayvalue="1"/>
     <state displaytext="Beijing" displayvalue="2"/>
     <state displaytext="Fujian" displayvalue="3"/>
     <state displaytext="Gansu" displayvalue="4"/>
</country>
</root>

Save this xml content in a file and name the file as "Country_State_List.xml"

Step-3: Adding XML as Resource File to Infopath form

Now we will add the file what we created in the Step-2 as a resource file to Infopath form. To add a resource file, Select Data -> Resourse Files as shown in the picture:

Step-4: Creating Data Source

Now we will create a new data source, in our case data source is an xml file which is available as a resource file. To add a data source, Select Data -> From Other Source -> From XML file. While selecting XML file, we have to select it from the Resource Files. Following picture shows the steps to follow:

After this step, a data connection with the name is available in the form as shown below. This is just a verify the data connection.

If the new data connection is not available as show in the picture, please follow the above steps again.

Step-5: Mapping Data Source to Field Controls

Final step is to map the data source to the corresponding combobox (Country and State).

Map the Country Combobox as shown in the following image:

Similarly Map the State Combobox as shown in the following image:

Finally we have to add the filter for the State Combobox to display only the States Associated to the select Country in the Country Combobox. Following image shows how to add the filter (with a black circle on the field):

yup, we are done with the cascading dropdowns setup with a xml datasource. Now we can preview the form by selecting Home -> Preview tab. Following image shows the preview of the same:

Please note that we can use same sample for any other data source like MS-Access, SQL Server or Web Service.

An Introduction to Infopath 2010 - Part-2

noreply@blogger.com (Sridhar Babu Kolapalli) — Wed, 12 Aug 2009 21:23:00 -0700

In continuation to the previous post - An Introduction to Infopath 2010 - Part-1, this post explains on how we can create/design a basic forms using InfoPath 2010.

To design an InfoPath form we will be using InfoPath Designer 2010.

Designer comes with a set of predefined templates for easy-to designing the forms. All these templates are grouped into different buckets like Popular Form Templates, Advanced Form Templates, InfoPath 2007 Form Templates, Template Parts.

Following screen shot shows the set of templates available in the designer:

This window in the office terms is called as Office Out Space, which allows us to open an existing forms, close the form or even to print a form. All the office 2010 applications contains Out Space to perform these operations with an additional tab for setting the options within that application (which can be Infopath/Excel/Word/PowerPoint etc).

Following table provides the set of templates available with the designer with the brief description about their usage:

Popular Form Templates

SharePoint List

Template used to view and edit items in the existing SharePoint List.

SharePoint Document Library

Template to collect and save the data in a SharePoint Document Library.

Blank

A Blank template for designing the form. It can be used for the browser enabled forms.

E-mail

Teamplate for designing the email submitted forms.

Blank (InfoPath Editor)

A Blank template for designing the form and it can be used only with the InfoPath editor while submiting the form.

Advanced Form Templates

Database

Template to design a form to collect data from databases like MS-Access, SQL Server.

Web Service

Template to design a form for query data from a Web Service and even to submit to a Web Service.

XML or Schema

Template to design a form based on an xml file or an a schema file.

Data Connection File

Template to design a form to query a SharePoint Data Source which is defined in a Data Connection File.

Convert Word or Excel Form

Template to design a form based on Microsoft Word document or Microsoft Excel.

Document Information Panel

Form used to edit the properties of the documents stored in a Document Library.

InfoPath 2007 Form Templates
These templates are for the InfoPath 2007.

Blank

A Blank template for designing the form. It can be used for the browser enabled forms.

Blank (InfoPath Editor)

A Blank template for designing the form and it can be used only with the InfoPath editor while submiting the form.

Template Parts
These templates are used to design a template part that can be reused in multiple forms.

Blank

XML or Schema

We will start with the Blank Template from the Popular Form Templates category to create a simple form.

In the coming posts we will see how we can add the validation rules and different submit options to the form.