Fetching ntext from MS SQL Server

This took me a while to figure out, so I wanted to write that down.

Look at the following problem: MS Dynamics CRM 2011 only allows 128 characters for the name of a certificate. You need the certificate for a so-called “Internet-Facing Deployment” of CRM 2011. Why the heck a certificate must have a name longer than 128 characters? It’s an annoying problem that leads to a greater question: How do you read ‘ntext’ fields off a SQL Server table?

This is a half-important question. It might be important, since ntext is a powerful type. It might become unimportant at any time, since MS has announced to discontinue this type. In MS SQL Server 2008 R2, it was still included.

The question lead to the following code, which reads the complete content of an ntext-typed field and writes it to a text file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Data.SqlTypes;

namespace EditNText
{
    class Program
    {
        static void Main(string[] args)
        {

            string connectionString = string.Format("Persist Security Info=false;Integrated Security=SSPI;Initial Catalog={2};Server={3}", "ENOMOS\\harald.vogt", "Seven348", "MSCRM_CONFIG", "vsrvenomos00018");
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();

            SqlCommand sql = conn.CreateCommand();


            string cmdStrSize = @"
SELECT length = DATALENGTH({1})
FROM {0}
";

            string cmdStr = @"
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR({1}) 
   FROM {0}
READTEXT {0}.{1} @ptrval {2} {3}
SELECT @ptrval
-- PRINT @ptrval   -- Both PRINT and SELECT are working!
";


            string tableName = "ConfigurationMetadata";
            string fieldName = "ConfigurationMetadataXml";
            string outFileName = "out.ntext";
            StreamWriter wri = new StreamWriter(outFileName);


            sql.CommandText = string.Format(cmdStrSize, tableName, fieldName);
            SqlDataReader r1 = sql.ExecuteReader();
            r1.Read();
            int dataSize = r1.GetInt32(0) / 2;
            r1.Close();


            Console.WriteLine("Reading a 'ntext' field from MS SQL Server into a file.");
            Console.WriteLine("Data size: " + dataSize);
            Console.WriteLine("Output file: " + outFileName);

            int pos = 0;
            int chunkSize = 4000;
            bool active = true;
            while (active)
            {
                try
                {
                    chunkSize = Math.Max(4000, dataSize - pos);
                    sql.CommandText = string.Format(cmdStr, tableName, fieldName, pos, chunkSize);
                    SqlDataReader reader = sql.ExecuteReader();
                    reader.Read();

                    string result = reader.GetString(0);
                    
                    wri.Write(result);
                    pos += result.Length;
                    Console.Write(result.Length + "   " + pos + "\r");

                    if (pos == dataSize)
                    {
                        active = false;
                    }
                    reader.Close();
                }
                catch (Exception e)
                {
                    Console.WriteLine();
                    Console.WriteLine(e.Message);
                    active = false;
                }
            }
            wri.Close();


            Console.WriteLine("Finished writing to file. Total size: " + pos);
            Console.ReadLine();

        }
    }
}

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Fetching ntext from MS SQL Server

  1. source says:

    Dude! This website is cool. How do you make it look like this .

  2. Thank you, but it’s just wordpress…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s