Parsing XML Data from SQL Database Column

There are scenarios when we will have data dumped in to Database Column in XML format.

Now when we are reading those data it does not make sense to present the data in the XML format.

Here are the example where we have XML data in a below format.

declare @Employee as nvarchar(max)

declare @EmployeeXML as XML

declare @cnt int

   SET @EmployeeXML='<Employees>

   <Employee><EmployeeID>Anoj Singh</EmployeeID><EmployeeName>150025</EmployeeName><Department>Delivery</Department></Employee>

   <Employee><EmployeeID>Ashutosh Mund</EmployeeID><EmployeeName>150016</EmployeeName><Department>Sales</Department></Employee>

   <Employee><EmployeeID>Nihil Jain</EmployeeID><EmployeeName>150017</EmployeeName><Department>Finance</Department></Employee>

   <Employee><EmployeeID>Sravan Kumar</EmployeeID><EmployeeName>150018</EmployeeName><Department>Admin</Department></Employee>

   </Employees>'

SELECT b.value('(EmployeeID)[1]', 'NVARCHAR(200)')+','+

       b.value('(EmployeeName)[1]', 'NVARCHAR(200)')+','+

       b.value('(Department)[1]', 'NVARCHAR(200)') as EmployeeData

       FROM  @EmployeeXML.nodes('Employees/Employee') a(b)

       print @Employee

This will display the data as shown below.

There are other way and format also which I will share in subsequent posts.

Installing Complete TFS 2010 in Windows Server 2008 R2 with SharePoint

Installing TFS 2010 in Windows Server 2008 R2 with SharePoint 

(including SSRS, SSIS, and Build Server Configuration)

Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

This is a typical issue faced by every Database guy if they are beginner to SQL DB world. This appears when we try to delete a database user who's owner of the database schema and SQL DB does not allow to delete the user if the user owns a schema.

Resolution to this is, a) Find out the schema or schema's owned by that user.

                                      b) Assign those schema to some other user

                                      c) Delete the user.

This can be done in 2 ways 1. Using SQL Script

                                                   2. Using Management Studio.

Using SQL Script

SELECT name FROM  sys.schemas WHERE principal_id = USER_ID('DBUser')

Run another command to assign the schema to another user

   

ALTER AUTHORIZATION ON SCHEMA::NameofSchema TO DBUser2 

GO

DROP USER DBUser

Using Management Studio

Step 1 : Select the User name and Open Property Window

Step 2 : Select the Schema Property viewed in above screen

Step 3 : Assign the Schema to another user by clicking Search and Browsing appropriate new user.

The most recommended is to assign to "dbo".

Step 4: Now if you see the new user property you can see the Schema is owned by the new user.

Now you can delete the user which you are intend to.

ERROR : Package 'RadLangSvc.Package, RadLangSvc.VS......' failed to load

Package 'RadLangSvc.Package, RadLangSvc.VS, Version=10.0.0.0, culture=neutral, PublicKeyToken=89845dcd8080cc91' Failed to Load

Error

 

Package 'RadLangSvc.Package, RadLangSvc.VS, Version=10.0.0.0, culture=neutral, PublicKeyToken=89845dcd8080cc91' failed to load

 

Solution

1. Open Visual Studio Offline Installer Folder
2. Open WCU folder
3. Open DAC folder
4. Install below .msi:

      i) DACFramework_enu.msi

      ii) DACProjectSystemSetup_enu.msi

      iii) TSqlLanguageService_enu.ms

Starting with FastSearch 2010 for SharePoint Configuration Part 1

The first step towards configuration of Fast Search in SharePoint 2010 environment is to have the SharePoint Enterprise edition activated. This will have a Feature called Fast Search Server 2010 for SharePoint Master Job Provisioning

To check this get into Central Admin -> System Setting -> Manage Farm Feature.

If you do not see this available here following are the steps to follow.

Go to Central Admin -> Upgrade and Migration click on Enable Enterprise Feature

There you can verify whether the Enterprise feature is activated or not.

If not go ahead and activate the Enterprise feature with a valid License key.

Once activated you can see the Fast Search Server 2010 for SharePoint Master Job Provisioning activated.

Finding List of Database used by a SharePoint farm using SQL Query in Configuration Database

There are times when we ignore the clean up process of any database server which are used by various SharePoint farm. So when we remove a farm with out bothering about the Database used then its becomes very difficult to identify the Databases used. At the same time we do not have the SP farm with us to check using Central Admin for PowerShell command. So if you came across this kind of scenario don't worry we have the solutions here.Using following query we can find out the Database names...Step 1: Run the below query in Configuration Database 

===================== Query 1===============================
SELECT Id
    ,BaseClassId
    ,FullName
 FROM Classes
WHERE FullName LIKE 'Microsoft.SharePoint.Administration.SPDatabase,%'
========================================================

This will give a GUID.

Now using that ID execute the below query.

===================== Query 2===============================
DECLARE @returnValue int,
                @RequestGuid uniqueidentifier

EXEC @returnValue = [dbo].[proc_getObjectsByBaseClass]
         @BaseClassId = 'USE THE ID GOT IN ABOVE QUERY',
         @ParentId = NULL,
         @RequestGuid = @RequestGuid OUTPUT

SELECT @RequestGuid as N'@RequestGuid'
SELECT 'Return Value' = @returnValue

This will list out the ID (GUID) assigned for each Database.

===================== Query 3===============================
Using one by one you can get the all the database names in the column named 'Name'

DECLARE @returnValue int,
                @RequestGuid uniqueidentifier

EXEC @returnValue = [dbo].[proc_getObject]
         @Id = 'ID of Last Result',
         @RequestGuid = @RequestGuid OUTPUT

SELECT @RequestGuid as N'@RequestGuid'
SELECT 'Return Value' = @returnValue