Tag Archives: sql server 2008

How to Migrate Lists from MOSS 2007 to SQL Server 2008 R2

These list migrations have been tested on MOSS2007 to SQL 2005 and 2008 R2.

How to migrate non-Lookup items:

The lists can be migrated as templates, as explained here:

1. Create the tables in SQL. A simple script to do so:

use MYDB
CREATE TABLE myTable
(
ID int PRIMARY KEY,
name varchar(50),
someData varchar(50),

)

2. Once the templates are converted and installed, and the tables are set up, a client object program has to be used to push them into SQL.  Unfortuantely, SharePoint’s client OM does not work in MOSS, so you’ll have to transfer the templates first. For my program I chose a console application. Here’s a simplified script for a non-specific list:

using System.Collections.Generic;
using Microsoft.SharePoint;
using System.Data.SqlClient;
using Microsoft.SharePoint.Client;
using SP = Microsoft.SharePoint.Client;
using System.Data.Sql;
//Note that I cut out the first half of the references since they're all standard system assemblies
namespace ListToSQL
class Program
 {
string connString = "Data Source=ucla2010db;Initial Catalog=REACTOR;Integrated Security=SSPI";

	static void Main(string[] args)
 	{
		Program prog = new Program();
 		prog.GetListItems();
 	}  

         private void GetListItems()
         {

	  string SPSite = "http://MySite/siteName";

	  using (SP.ClientContext context = new SP.ClientContext(SPSite))
 	  {
 			SP.Web web = context.Web;
 			SP.List myList = web.Lists.GetByTitle("MyList");
 			SP.CamlQuery qry = new SP.CamlQuery();

			//No need to actually filter the query. We want all the items
 			qry.ViewXml = @"";

 			SP.ListItemCollection listColl = myList.GetItems(qry);
 			context.Load(listColl,
				items => items.Include(
 				item => item["Title"],
 				item => item["Date"],
 				item => item["OtherField"]));
 				context.ExecuteQuery();

 			BulkUpdateSQL(BuildTable(listColl),"myDataTableName");
            }
        }

       private DataTable BuildTable(SP.ListItemCollection contextList)
      {
           DataTable dt = new DataTable();

           //Select any ListItem at all and loop through the field names
           ListItem colItem = contextList[0];

           foreach (string name in colItem.FieldValues.Keys)
           {
	       //Make the field names into column names
                 dt.Columns.Add(name);
           }

           foreach (ListItem item in contextList)
           {
                 //Create a new row for each ListItem
                 dt.Rows.Add(dt.NewRow());

                 foreach (string name in item.FieldValues.Keys)
                 {
	            //Add each FieldValue item to the row
                      dt.Rows[dt.Rows.Count - 1][name] = item[name];
                 }
           }

         return dt;
       }

       private void BulkUpdatePI(DataTable dt, string destName)
       {
             using (SqlConnection conn = new SqlConnection(connString))
             {
                 conn.Open();
                 using (SqlBulkCopy copy = new SqlBulkCopy(conn))
                 {
 /* Note that you can use ColumnMappings [i.e. "copy.ColumnMappings.Add("Title", "name")"]
  * With either ordinal or string-based mapping,
  * But every column you don't map will get ignored if you explicitly map
  */
                        copy.DestinationTableName = destName;
                        copy.WriteToServer(dt);
                 }
          }
}

 

TADA! Lists in SQL!

But what about… LOOKUPLISTS!?

The only way to do this from MOSS2007 and maintain the lookup info is to export the list to Excel:

    1. In the Data View list view there is an option to export to excel.
    2. Save the file in a location accessible by your DB
    3. From there, go to the DB you want the data in
    4. Right click the management folder and select “Import Data”
    5. In the Import Data Wizard, select “Microsoft Excel” from the dropdown
      • This will prompt you to enter the file name for the excel file. Do that.
    6. Leave “first row has column names” checked, since that is the SP export default
    7. Click next (but you already knew that)
    8. Select the following:
      • Destination:

Whatever type of DB your MOSS server is, if you are  unsure, use SQL Native Client.

    • Server Name: The name of your MOSS server. If you are unsure, try “MOSS.”
    • Authentication: Most likely windows
    • Database: The database you wanto to import the excel file into.
    • And… Next
  1. Now you can copy the data or write a specific query. I tend to just copy all the data so it’s in SQL if I need it
  2. On the “Select Source Tables and Views” screen you will see bizarrely named source options. Select the first (possibly only) option. You can change the mappings or preview the table from this screen.
  3. Then hit finish. You can schedule the operation or run it now.
  4. Great.
  5. We’re still not done.
  6. From there you can go to your 2010 server and basically perform the same import operation, only add the MOSS Excel table into your new database.
  7. This doesn’t actually create primary key lookups, it only imports the data as-is, but now you have it, so you can manipulate it as needed!