Extracting Notes from SQL Server Management Studio

Overview

When working with the Allscripts database, there is sometimes a need to extract a Note for testing or verification purposes. In SQL Server Management Studio, the raw RTF note data can be seen in queries that draw from the Document table. By default, SQL Server Management Studio allows you to copy and paste data from these rows. This is a useful feature, but all data in the Results Pane is truncated after a certain length. This length can be increased in Tools > Options... > Query Results > SQL Server > Results to Grid, but it cannot be increased beyond 65535 characters. Notes often exceed this length, sometimes by quite a large amount. In these cases, only the first 65535 digits are displayed. As a result, it can be difficult to verify a Note's integrity at a glance or to mistakenly think that only the first portion of a note is stored in the database.

Fortunately, the full contents of a Note can be extracted from the database using SQL Server Management Studio. The easiest way is to use the Export Data feature.

Procedure

The following steps should be performed in SQL Server Management Studio after opening the Works database in the Object Explorer.

1. Right-click on the Works database and select Tasks > Export Data…
2. Click next to start the export wizard.
3. Make sure the server name and database name point to the Works database you wish to access, and that the authentication information is the same as what you were using. Click next.
4. For destination, select "Flat File Destination". Enter a file name for the note, and make the file extension ".rtf" if you wish to view the note as it appears, or ".txt" if you simply wish to view the raw Note data. (e.g., to view the note with Document ID 123456, "note123456.rtf" would be a good filename). The other default options should be okay, so click next.
5. Select "Write a query to specify the data to transfer" and click next.
6. Enter the following SQL statment, replacing the Document ID with that of the Note that you wish to verify:
   SELECT [EditableChunk] FROM [Works].[dbo].[Document] WHERE [DocumentID] = 123456
   and click next.
7. The next window should say that the source query is "[Query]". The default options are fine as long as we are only selecting one column and one row. Click next.
8. Make sure the "Run immediately" box is checked and that the "Save SSIS Package" box is not checked. Click next.
9. Click finish to run the operation.
10. Locate the filename you entered in step 4 and open it in Word, WordPad, or another RTF document reader to see it as it appears, or in Notepad to see just the raw Note data.
11. Verify that the contents are as expected.

Privacy

As always, take care when dealing with patient data and use necessary security precautions. When exporting Notes with this method, be sure to delete the files afterwards so they do not remain on the server's filesystem outside of the database.