Tag Archives: sql

Everything You Need to Know About OfficeWriter: The Whitepaper

Looking for a comprehensive overview of OfficeWriter? You’ve come to the right place. We’ve partnered with Andrew Brust of Blue Badge Insights to give you an inside look at how OfficeWriter can benefit your company’s business intelligence.
OfficeWriter Whitepaper
Microsoft Office and Data: A Love Story
Excel and Word are the Will and Kate of the Office software world, but if you try to use them for data analysis, they’ll morph into Milli-Vanilli. OfficeWriter prevents that from happening.
In this whitepaper, you’ll learn:
  • How OfficeWriter’s API and templates provide bridges between Microsoft Office & databases
  • How OfficeWriter enhances SQL Server Reporting Services
  • How OfficeWriter turns SharePoint lists & libraries into full-fledged Office docs
That is simply the beginning. Get a full overview of OfficeWriter today.

Twitter Roundup: Talking About SSRS

Hello!  Welcome to my first post.  I’m Elise, lover of social media and self-proclaimed coffee addict.  As a newcomer to SoftArtisans, and to the MSFT tech arena in general, I’ve been trying to absorb as much info on the technologies we run on as possible. Since one of OfficeWriter’s main features is its SSRS designer, I decided to tackle this reporting beast first. Luckily, the Twittersphere is rife with helpers. Some of my favorite SSRS-related tweets (and tweeters) are below.  (Click the picture to see the full list.)  If you have any favorite SSRS bloggers, tweeters, or posts I’d love to hear about them!  Send me a tweet or leave a comment in the comments section so I can check it out.

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

         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);
				items => items.Include(
 				item => item["Title"],
 				item => item["Date"],
 				item => item["OtherField"]));


       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

           foreach (ListItem item in contextList)
                 //Create a new row for each ListItem

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


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.
  • Leave “first row has column names” checked, since that is the SP export default
  • Click next (but you already knew that)
  • 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
  • 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
  • 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.
  • Then hit finish. You can schedule the operation or run it now.
  • Great.
  • We’re still not done.
  • 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.
  • 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!