Moving Data from the IMAGE Datatype to VARBINARY(MAX) from a SQL Server 2000 Instance

SQL Server logo

I am in the process of writing scripts to move data from a SQL 2000 database into a different database running on SQL 2008.

I had set up a linked server from the SQL 2k server to begin this process, and about 90% of the data could be moved.  One of the last tables to be migrated was one that held file attachments. I ran into a problem when I realized that the SQL 2k table had an IMAGE column, and the corresponding SQL 2k8 table used a VARBINARY(MAX) column.

The SQL2k database has a table with the following definition:

CREATE TABLE [dbo].[tOldTable](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [DocName] [varchar](300) NOT NULL,
 [DocData] [image] NOT NULL,
 [DocContentType] [varchar](300) NOT NULL,
 [User] [varchar](50) NOT NULL,
 [Date] [datetime] NOT NULL,
 [requestId] [int] NOT NULL,
 [requestTypeId] [int] NOT NULL)

The new table, in the SQL2k8 database, has this structure:

CREATE TABLE [dbo].[tNewTable](
 [SupID] [int] IDENTITY(1,1) NOT NULL,
 [ProjectID] [int] NOT NULL,
 [FileName] [varchar](250) NULL,
 [FileExtension] [varchar](25) NULL,
 [MIMEType] [varchar](250) NULL,
 [FileData] [varbinary](max) NULL,
 [Thumbnail] [varbinary](max) NULL,
 [FileSize] [int] NULL,
 [Width] [int] NULL,
 [Height] [int] NULL,
 [FileModificationTimestamp] [datetime] NULL,
 [CreatedBy] [varchar](100) NULL,
 [CreateTimestamp] [datetime] NULL,
 [Removed] [bit] NULL)

At one point, I was planning on using a CURSOR to load the data from the first table into a table variable and explicitly CAST it as VARBINARY(MAX), but then I re-discovered the fact that the IMAGE datatype cannot be stored in a local variable.  My second problem – even if the first step had worked – is that VARBINARY(MAX) is not available on SQL 2000 (it was introduced in SQL 2005), and would cause an error if run from the SQL 2k server.

After refactoring and improving the code, I had removed the CURSOR, but was still uncertain as to how I could move this data.  I decided to JOIN the SQL 2k table with an existing table variable, and directly INSERT the rows like so:

INSERT INTO [SQL2k8].[NewDB].[dbo].[tNewTable]
 ([ProjectID]
 ,[FileName]
 ,[FileExtension]
 ,[MIMEType]
 ,[FileData]
 ,[FileModificationTimestamp]
 ,[CreatedBy]
 ,[CreateTimestamp]
 ,[Removed])
SELECT tv.[ProjectID] AS [ProjectID]
 ,[DocName] AS [FileName]
 ,RIGHT([DocName],3) AS [FileExtension]
 ,[DocContentType] AS [MIMEType]
 ,[DocData] AS [FileData]
 ,[Date] AS [FileModificationTimestamp]
 ,[User] AS [CreatedBy]
 ,[Date] AS [CreateTimestamp]
 ,0 AS [Removed]
 FROM [dbo].[tOldTable] ot
 JOIN @tTableVariable tv ON tv.[requestID] = ot.[requestId]

This was an implicit conversion from IMAGE to VARBINARY(MAX), but since the insertion took place on the SQL 2008 server and the code never mentions the datatype (which would not have been recognized by the SQL 2k instance), no errors resulted. Problem solved!

Incidentally, the IMAGE datatype has been deprecated, and should be converted to VARBINARY(MAX) wherever possible.

Storing Session State for an ASP.NET Site in a SQL Server Database

ASP.NET and related technologies

When using session state in an ASP.NET Web application, you have several options.

If you want to store it in your application database, as opposed to the ASPstate database (created at the command line with “aspnet_regsql -S [server] -E -ssadd -sstype c -d ASPstate”), I have scripted the database objects and stored them in GitHub at this address:

https://github.com/DeepInTheCode/ASPstate

Replace the name of the database throughout the ASPstate.sql file with your application database name and run against the database. After that, the web.config of your Web application must have the appropriate information added:

<configuration>
	<system.web>
	<sessionState mode="SQLServer"
		sqlConnectionString="Integrated Security=SSPI;data
		source=SampleSqlServer;" 
	/>
	</system.web>
</configuration>

(sqlConnectionString must be set to your database with respective authentication information.)

Sending SQL Server Stored Procedure Results as an HTML Table in Email Using Python

Python logo

By modifying the code I used for sending email attachments, I am able to execute a SQL Server stored proc and format the results as an HTML table in an email. Also, I use the sys.exit command if there are no rows returned so that the email is not sent if there is no data returned.

# disabled username / password logon for use in our Exchange environment

######### Setup your stuff here #######################################

host = 'smtp.whatever.com' # specify port, if required, using a colon and port number following the hostname

fromaddr = '[email protected]' # must be a vaild 'from' address in your environment
toaddr  = ['[email protected]'] # list of email addresses
ccaddr  = [''] # list of email addresses
bccaddr  = ['[email protected]'] # list of email addresses
replyto = fromaddr # unless you want a different reply-to

# username = 'username' # not used in our Exchange environment
# password = 'password' # not used in our Exchange environment

msgsubject = 'Email Subject'
htmlmsgtext = "<h2>Report for "  # text with appropriate HTML tags
connstring = 'DRIVER={SQL Server};SERVER=servername;DATABASE=dbname;UID=userid;PWD=password'

######### In normal use nothing changes below this line ###############

import smtplib, pyodbc, sys
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText
from email.Utils import COMMASPACE
from HTMLParser import HTMLParser
import datetime as dt
yesterday = dt.datetime.now() - dt.timedelta(days=1)

date = "'" + yesterday.strftime('%m-%d-%Y') + "'"
htmlmsgtext = htmlmsgtext + date + "</h2>"
conn=pyodbc.connect(connstring)
cursor=conn.cursor()
cursor.execute("exec dbo.sp_RandomStoredProc " + date)
rows = cursor.fetchall()
column_names = [d[0] for d in cursor.description]
cursor.close()
del cursor
if len(rows) == 0:
    sys.exit
htmlmsgtext = htmlmsgtext + '<table style="border:2px solid black">n'
htmlmsgtext = htmlmsgtext + '<tr>n'
for column_name in column_names:
    htmlmsgtext = htmlmsgtext + '<th style="border:1px solid black; text-align:center">' + column_name + '</th>'
htmlmsgtext = htmlmsgtext + 'n</tr>n'
for row in rows:
    for column in row:
        htmlmsgtext = htmlmsgtext + '<td style="border:1px solid black; text-align:center">' + str(column) + '</td>'
    htmlmsgtext = htmlmsgtext + 'n</tr>n'
htmlmsgtext = htmlmsgtext + '</table>n'

# A snippet - class to strip HTML tags for the text version of the email

class MLStripper(HTMLParser):
    def __init__(self):
        self.reset()
        self.fed = []
    def handle_data(self, d):
        self.fed.append(d)
    def get_data(self):
        return ''.join(self.fed)

def strip_tags(html):
    s = MLStripper()
    s.feed(html)
    return s.get_data()

########################################################################

try:
    # Make text version from HTML - First convert tags that produce a line break to carriage returns
    msgtext = htmlmsgtext.replace('</br>',"r").replace('
',"r").replace('
',"r")
    # Then strip all the other tags out
    msgtext = strip_tags(msgtext)

    # necessary mimey stuff
    msg = MIMEMultipart()
    msg.preamble = 'This is a multi-part message in MIME format.n'
    msg.epilogue = ''

    body = MIMEMultipart('alternative')
    body.attach(MIMEText(msgtext))
    body.attach(MIMEText(htmlmsgtext, 'html'))
    msg.attach(body)   

    msg['From'] = fromaddr
    msg['To'] = COMMASPACE.join(toaddr)
    msg['CC'] = COMMASPACE.join(ccaddr)
    msg['Subject'] = msgsubject
    msg['Reply-To'] = replyto

    print 'To addresses follow:'
    print toaddr

    # The actual email sendy bits
    server = smtplib.SMTP(host)
    server.set_debuglevel(False) # set to True for verbose output
    recipients = toaddr + ccaddr + bccaddr
    # Comment this block and uncomment the below try/except block if TLS or user/pass is required.
    server.sendmail(fromaddr, recipients, msg.as_string())
    print 'Email sent.'
    server.quit() # bye bye

    # try:
        # # If TLS is used
        # server.starttls()
        # server.login(username,password)
        # server.sendmail(fromaddr, recipients, msg.as_string())
        # print 'Email sent.'
        # server.quit() # bye bye
    # except:
        # # if tls is set for non-tls servers you would have raised an exception, so....
        # server.login(username,password)
        # server.sendmail(fromaddr, recipients, msg.as_string())
        # print 'Email sent.'
        # server.quit() # bye bye        

except:
    print "Email NOT sent to %s successfully. ERR: %s %s %s " % (str(toaddr), str(sys.exc_info()[0]), str(sys.exc_info()[1]), str(sys.exc_info()[2]) )
    #just in case