Undocumented Functionality

Now that we have examined the important documented system stored procedures, let's move on to discuss about the undocumented functionalities provided in SQL Server. Before we begin our discussion, here's a word of caution – while working with undocumented functionality, apart from indicating that we are using undocumented functionality we should always ensure that our code is well documented.

We've heard some people saying that if you are going to use undocumented functionality, when a documented SQL-DMO function is available by writing an extended stored procedure, you should write the extended stored procedure. This is usually not true in our camp; others will differ. When you do version regression testing, you are still going to have to test your extended stored procedure just as much as you have to test your usage of undocumented functionality. It is a whole lot easier to test the SQL function than to pull through the C++ code. As often as not, both will work, but which would you prefer to debug?

In all likelihood, in the next version of SQL Server stored procedures and assemblies exposing the C# and VB.NET coding environments will be available. In such a situation, when given the choice between undocumented functionality in SQL and rolling your own in a VB.NET assembly our resolve will weaken. However, as of now, we aren't there yet.

Documentation of any undocumented functionality used in our code is critical. We will always include the word UNDOCUMENTED in a comments section at the top of our stored procedure that use undocumented functionality. This enables us to easily execute a simple search through the text column of the syscomments table for each database for this word. This will give us a list of all the places that need to undergo regression testing before moving a new stored procedure or patch into production.

We should not shy away from using undocumented functionality in our production and development environment, where we manage the system. As an example, we had described the sp__database$create procedure earlier (refer to the section on Making System Stored Procedures with the 0x80000000 Bit ).

Such usage is responsible and desirable. DBAs shouldn't have much problems in using undocumented functionality in their homemade system stored procedures and on their company's proprietary utility database. However, outside this scope they should be reluctant to use undocumented functionalities. They should be used only if there is no other way of performing the task, and this will happen very rarely.

Finding Undocumented Extended Stored Procedure Functionality

As we mentioned before, we can mine system stored procedures to see ‘how Microsoft does it’. Generating their code through SQL Query Analyzer can easily accomplish this. However, extended stored procedures behave in a different matter. About the only thing that we can see in SQL Query Analyzer is the DLL on which the extended stored procedure relies. So how can we figure out what a procedure does?

We begin by selecting the extended stored procedure we are interested in then we run the following query in the master database:

      SELECT DISTINCT object_name(id) FROM syscomments      WHERE text LIKE '%extended stored procedure name%' 

As we mentioned earlier, syscomments table is where the code definitions of stored procedures, views, functions, table functions, check constraints, computed columns in tables, and defaults live. Here, we are looking for procedures using the particular extended stored procedure we are attempting to mine. Quite often, we will strike out in querying the master database. This is because the functionality of that extended stored procedure may not be used in the master database.

Remember that only the master database can have extended stored procedures. Other databases will make use of these procedures. Particularly, if the extended procedure begins with sp_, run the above query in both msdb database and the distribution database (we have looked at it the section on Mining System Stored Procedures). More often than not, we'll discover that many of the extended stored procedures exist for replication, and are called by a stored procedure in the distribution database.

Windows-Registry Related Stored Procedures

Microsoft routinely reads the registry with probably the most ‘well-known’ undocumented system stored procedures. They all begin with the characters XP_Reg. Here, we'll examine each of them in detail. By default, these procedures are only available to system administrators; we encourage you to keep it that way.

The Windows Registry

Before we start our discussion, let's have a brief look at the registry. The registry is a hierarchical database of relatively unchanging information that controls almost all aspect of our machine's operation and user's environment. It is unforgiving if you mess it up, so use extreme caution while making any changes to it. If you've never explored the registry before, it can be fascinating to see how things are put together.

For beginners, we recommend using the REGEDT32.EXE program. In this program, you can set an option for making the registry read-only – from the Options menu, choose Read Only Mode. We can also use this program for changing the security settings on a registry key or when we want to connect to the registry of a remote machine. REGEDIT.EXE is another program that we can use to edit the registry, its search capabilities are much more robust than REGEDT32.EXE. In Windows XP, these two programs have been merged together into a single application, callable with either name.

The registry is composed of hives, keys, sub-keys, values, and data. In the following figure, we can see the HKEY_LOCAL_MACHINE hive, the SOFTWARE key, the _Test sub-key in the left pane:

In the right pane, we can see the values and their corresponding data settings. Values will generally be one of several different types. The most common are StringValue (REG_SZ) and DWORD Value (REG_DWORD). String values are just that, while the latter are 4-byte integers.

Important

Editing the registry can be dangerous even if you think you know what you are doing. You should not use any of these registry procedures until you have thoroughly tested them on a development machine.

In the course of our discussions, we will be working with a key called Software\_Test in the HKEY_LOCAL_MACHINE hive. If you start the registry editor with the command REGEDT32.EXE, you will be able to make the various keys shown in the previous screenshot.

XP_RegRead

The most commonly used registry-related system stored procedure is XP_RegRead. It has two functions:

• The first is to determine if a registry key exists or doesn't exist

• The second is to read data for a specific value

Important

When you are doubtful about the existence of a key, you should always test its existence before attempting to read data from it.

To determine if a key exists, use the following code:

     CREATE TABLE #keyexist (keyexist INT)     DECLARE @regkey NVARCHAR(1000), @hive NVARCHAR(1000),@keyexist INT     SELECT @hive = 'HKEY_LOCAL_MACHINE'     SELECT @regkey = N'SOFTWARE\_Test'     INSERT INTO #keyexist     EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @regkey     SELECT @keyexist = keyexist FROM #keyexist     IF @keyexist = 1     ... -- continue with your code once you determine the key exists 

If the key exists, the XP_RegRead returns an integer value of 1, else it returns 0.

To read a registry DWORD, STRING, or BINARY value using XP_RegRead, use the following code:

     DECLARE @data SQL_VARIANT, @regvalue NVARCHAR(1000), @rc INT     DECLARE @regkey NVARCHAR(1000), @hive NVARCHAR(1000)     SELECT @hive = N'HKEY_LOCAL_MACHINE'     SELECT @regkey = N'SOFTWARE\_Test'     SELECT @regvalue = N'String Value'     EXECUTE @rc = master.dbo.xp_regread @hive, @regkey, @regvalue,             @data OUTPUT, N'no_output'     SELECT @rc,@data 

MULTI-STRING values store multiple 0 to n strings. Each stored string is known as an item.

To read a MULTI-STRING value using XP_RegRead, we use the following code:

     DECLARE @regvalue NVARCHAR(1000), @rc INT     DECLARE @regkey NVARCHAR(1000), @hive NVARCHAR(1000)     CREATE TABLE #regmultistring       (Item NVARCHAR(1000),        Value NVARCHAR(1000)       )     SELECT @hive = N'HKEY_LOCAL_MACHINE'     SELECT @regkey = N'SOFTWARE\_Test'     SELECT @regvalue = N'Multi'     EXECUTE @rc = master.dbo.xp_regread @hive, @regkey, @regvalue     SELECT Item,Value FROM #regmultistring     DROP TABLE #regmultistring 

This returns a record set of the MULTI-STRING values; here, we have called it Multi.

Usage Notes

While using this code snippet, please keep the following things in mind:

• Acceptable values for the @hive parameter for all XP_Reg procedures are:

               ?    HKEY_LOCAL_MACHINE             ?    HKEY_CURRENT_USER             ?    HKEY_USERS             ?    HKEY_CLASSES_ROOT 

• The XP_RegRead stored procedure will not read a registry value of type REG_EXPAND_SZ (expanded string). An expanded string has an embedded environment variable; an attempt to read such a value will return NULL.

• If you have REG_EXPANDED_SZ or REG_BINARY in the key, it may throw an extra column or two when attempting to populate a temporary table by reading from a REG_MULTI_SZ value. This can be found out only by testing.

• Sometimes when you read a REG_MULTI_SZ value, it may return an extra column. You need to pad your temporary table with this column; its data should be NULL. Only testing can reveal this.

• As a rule of thumb, when you use an NVARCHAR data type in XP_Reg procedures, it will be NVARCHAR(1000). This is what Microsoft does in their usage of XP_RegRead.

XP_RegWrite

The system stored procedure XP_RegWrite writes to the registry. There are quite a few examples in both the master and msdb databases that make use of this procedure. The trick in using this procedure lies in knowing the type of value that will be written:

Key type	Data type
REG_SZ	NVARCHAR
REG_DWORD	INT
REG_BINARY	VARBINARY

Here are some examples of each. It shows us how to make a key with only a default, blank value:

     DECLARE @retcode INT     EXECUTE @retcode = master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',       N'SOFTWARE\ _Test',       N", -- the default, non-named value       N'REG_SZ',              -- the value type       N"                     -- this is the blank data     SELECT @retcode 

For adding or changing a REG_DWORD value:

     DECLARE @intDWORD INT, @retcode INT     SELECT @intDWORD = 0     EXECUTE @retcode = master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',                        N'SOFTWARE\_Test', N'DWORD VALUE', N'REG_DWORD',                        @intDWORD     SELECT @retcode 

For adding or changing a REG_SZ value:

     DECLARE @value_string NVARCHAR(1000),@retcode INT     SELECT @value_string = 'Sample Value'     EXECUTE @retcode = master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',                        N'SOFTWARE\_Test', N'STRING VALUE', N'REG_SZ',                        @value_string     SELECT @retcode 

Adding or changing a REG_BINARY value:

     DECLARE @value_binary VARBINARY(1048),@retcode INT     SELECT @value_binary = 0x63657273     EXECUTE @retcode = master.dbo.xp_regwrite       'HKEY_LOCAL_MACHINE',       N'SOFTWARE\_Test',       N'BINARY VALUE',       'REG_BINARY',       @value_binary     SELECT @retcode 

Usage Notes

Keep the following things in mind while running these scripts:

• The acceptable hive definitions are the same as those for XP_RegRead.

• If the key and/or value are not present, they will be created. If the value exists, it will be changed to the new value and type (if different).

XP_RegEnumKeys

XP_RegEnumKeys is commonly used to seed nested cursors while mapping the keys or sub-keys. We will not use XP_RegEnumKeys for checking the existence of a key, as XP_RegRead works much better here:

     DECLARE @regkey NVARCHAR(1000), @retcode INT     CREATE TABLE #keylist (key_name NVARCHAR(1000))     SET @regkey = 'SOFTWARE'     INSERT INTO #keylist     EXECUTE @retcode = master.dbo.xp_regenumkeys     'HKEY_LOCAL_MACHINE', @regkey     SELECT @retcode     SELECT key_name FROM #keylist     DROP TABLE #keylist 

XP_RegEnumValues

XP_RegEnumValues will usually return a two-column result set for REG_SZ, REG_MULTI_SZ, and REG_DWORD value types. It may return ragged column result sets if we have REG_EXPAND_SZ or REG_BINARY types in the registry. Extensive testing is needed for getting around this potential error.

Here's an example that uses our Software\_Test key (refer to the earlier screenshot):

     CREATE TABLE #values       (value NVARCHAR(1000) NULL,        data NVARCHAR(1000) NULL       )     EXECUTE xp_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE \_Test'     SELECT value,data FROM #values     DROP TABLE #values 

For this example, you m ay get a ragged column. This will be characterized by a column number mismatch error. If you do, delete the REG_EXPAND_SZ and the REG_BINARY values during your testing. Obviously, in a production situation, this would be a concern:

Usage Notes

Here's the usage note:

• Similar to XP_RegRead for REG_MULTI_SZ, the presence of REG_BINARY or REG_EXPAND_SZ value types can cause errors, causing the output of XP_RegEnumValues to have ragged columns. The extra columns would show up as extra value columns. As a result, testing the code is necessary.

XP_RegDeleteKey

Before explaining this procedure, it is worth repeating here to be extremely careful when making changes to the registry.

Important

Rollback isn't available when you are executing these procedures. This is a particularly dangerous procedure, as it can to wipe out your entire registry in very short time.

Except for some parts of the HKEY_LOCAL_MACHINE\System key, backups aren't easily available.

In this example, we will first check for the existence of the key and then try to delete it:

     CREATE TABLE #keyexist (keyexist INT)     DECLARE @regkey NVARCHAR(1000), @keyexist INT, @retcode INT     -- regdelete returns 'Access Denied' message if key does not exist;     -- check before delete     SELECT @regkey = N'SOFTWARE\_Test'     INSERT INTO #keyexist EXECUTE master.dbo.xp_regread                                   'HKEY_LOCAL_MACHINE', @regkey     SELECT @keyexist = keyexist FROM #keyexist     IF @keyexist = 1     BEGIN       EXECUTE @retcode = master.dbo.xp_regdeletekey 'HKEY_LOCAL_MACHINE',                          @regkey       IF @@error <> 0 OR @retcode <> 0       BEGIN         SET @retcode = 1         GOTO FAILURE       END     END     FAILURE:     DROP TABLE #keyexist 

If you try to delete a non existent key, you will get the error message:

     Msg 22001, Level 1, State 22001     RegDeleteKey() returned error 2, 'The system cannot find the file     specified.' 

XP_RegDeleteValue

This stored procedure is used to delete a single value:

     DECLARE @retcode INT     EXECUTE @retcode = master.dbo.xp_RegDeleteValue N'HKEY_LOCAL_MACHINE',                        N'SOFTWARE\_Test', N'Multi'     SELECT @retcode 

Usage Notes

While using this procedure, keep the following point in mind:

• Whether you actually delete a value or not, the return code from the procedure will be zero if the three parameters of hive, key, and value are included with the correct data types

XP_RegAddMultiString

This and XP_RegRemoveMultiString are the least understood of the registry extended stored procedures. Multi-string values, REG_MULTI_SZ, are essentially lists of strings in the registry. Each separate string in a multi-string value is called an item. Implicit to a multi-string value is an item count. The first item will be item 1, the next item 2, and so on. As the name implies, these procedures add and remove items from a multi-string value list.

What you are going to see here is new ground. None of Microsoft's system stored procedures has ever used it. Frankly speaking, we are unsure why Microsoft added it into their code; nevertheless, we've found uses for it in our production scripts.

Here's the only correct example of this procedure that we've ever seen:

     DECLARE @data_item_multi NVARCHAR(1000),@retcode INT     DECLARE @value_multi NVARCHAR(1000)     SELECT @data_item_multi = N'ItemX', @value_multi = N'MULTI'     EXECUTE @retcode = master.dbo.xp_regaddmultistring                        N'HKEY_LOCAL_MACHINE', N'SOFTWARE\_Test',                        @value_multi, @data_item_multi     SELECT @retcode 

Usage Notes

These are the usage notes for this stored procedure:

• When you run XP_RegAddMultiString, you are adding a new item to a registry array value of type REG_MULTI_SZ.

• If the registry value doesn't exist previously, it will be created with item value of 1.

• Subsequent additions to an existing value will increase the item count by 1.

• If you add the same data item again to the same value, it will show up as a second item with the same data. This is particularly important to note when discussing XP_RegRemoveMultiString.

XP_RegRemoveMultiString

Like XP_RegAddMultiString, this is the first time you will be seeing an accurate working demo of XP_RegRemoveMultiString in publication, either print or electronic. The syntax is similar to XP_RegAddMultiString:

     DECLARE @data_item_multi NVARCHAR(1000),@retcode INT     DECLARE @value_multi NVARCHAR(1000)     SELECT @data_item_multi = N'ItemX', @value_multi = N'MULTI'     EXECUTE @retcode = master.dbo.xp_regremovemultistring                        N'HKEY_LOCAL_MACHINE',N'SOFTWARE\_Test',                        @value_multi, @data_item_multi     SELECT @retcode 

Usage Notes

Let's look at the usage notes for this stored procedure:

• If no data string matches the string specified in the @data_time_multi variable, nothing will be deleted and the return code will be zero.

• If there is one or more data items equivalent to the data item specified, all such data items will be deleted, not just the first one.

• When all data items are deleted from a value, the value will not be deleted itself. In order to delete the value, you should use XP_RegDeleteValue.

XP_Instance_Reg Extended Stored Procedures

As you probably know by now, SQL Server 2000 can have multiple instances. This is a change from SQL 7 and earlier versions, which only allowed one instance. In the registry, the default instance of SQL Server is always found under the key:

     HKEY_LOCAL_MACHINE \ Software \Microsoft\MSSQLServer. 

In Windows 2000, the named, non-default instances are found under the key:

     HKEY_LOCAL_MACHINE \Software\Microsoft\Microsoft SQL Server \instance 

where instance is the name of the instance provided at installation time.

In order to handle multiple instances, Microsoft put together the set of XP_Instance_Reg stored procedures. They have a one-to-one correlation with the XP_Reg stored procedures that we have just discussed. What happens is that you always specify the second key parameter as if you were specifying the key parameter for the default instance. However, if you are in a named instance the stored procedure senses this and redirects you from the \Microsoft\MSSQLServer key to the \Microsoft\MicrosoftSQL Server\instance key. You don't have to specify the instance name. You can see that XP_Instance_Reg is used frequently in the msdb database when actions are being taken with the SQL Server Agent.

When the key specified is outside the scope of \Microsoft\MSSQLServer key, the XP_Instance_Reg stored procedures act exactly like their XP_Reg stored procedure equivalents. Interestingly, there is a set of keys in

     HKEY_LOCAL_MACHINE \System \CurrentControlSet \Services 

for both the SQL Server service and the SQL Agent service.

The XP_Instance_Reg procedures will not automatically detect the correct service keys for these keys. A good example of how Microsoft resolves these service names can be found in the msdb procedure sp_get_sqlagent_properties. Here, to build the SQL Agent service key name, the function SERVERPROPERTY('INSTANCENAME') is used.

The File System

A number of undocumented extended stored procedures exist for working through the file system. We will discuss some of them, as they can be quite helpful for getting things in and out of the file system and also, for checking the existence of files, folders, and drives.

Like the registry procedures, it's advisable to make sure that these procedures are only available to system administrators. If you have to open them up to an application for users write wrapper procedures, which can control the input parameters.

XP_AvailableMedia

XP_AvailableMedia returns a result set listing the available drives on the host server for SQL Server. It has a single optional input parameter, TINYINT, that can be interpreted as a bitmap variable for the drive type.

The result set has four values – name, low free, high free, and media type. The combination of the low free and high free columns describes a 64-bit number representing the free space on the disk. If the low free is greater than zero, then it represents that number of bytes. If it is less than zero, then it represents 2³² plus the low free value bytes. This result is then added to 2³² times the high free value to get the total number of bytes on the disk. To make the number more meaningful, in the following example, we have divided the result by 1MB to get the number of megabytes.

The media type appears to be some sort of a TINYINT bitmap. The XP_AvailableMedia can take a single parameter of type TINYINT. The valid values for it are:

Value	Description
1	Both floppy drive and fixed disks are shown in the output
2	Only fixed disks are shown in the output
8	Only CDROM drives are shown in the output
255	This shows all media types in the output

Here is a good example, which shows the output of the procedure. This example will also calculate the available free space for the media:

     SET NOCOUNT ON     CREATE TABLE #availablemedia       (name CHAR(3),        [low free] INT,        [high free] INT,        [media type] TINYINT       )     DECLARE @mediabitmap TINYINT     SELECT @mediabitmap = 255     INSERT #availablemedia     EXECUTE xp_availablemedia @mediabitmap     SELECT name, [low free], [high free],       (CAST((CASE when [low free] >= 0 THEN [high free]       ELSE [high free] + 1 END) AS FLOAT) * 4294967296.0 +       CAST([low free] AS float))/1048576.0 AS 'Available MBytes',     CASE WHEN [Media Type] = 2 THEN 'Fixed Disk'     WHEN [Media Type] = 8 THEN 'CDROM'     WHEN [Media Type] = 1 THEN 'Floppy'     ELS E 'Unknown Media Type' END AS 'Media Type'     FROM #availablemedia     DROP TABLE #availablemedia 

XP_SubDirs

XP_SubDirs returns a single column result set called subdirectory. The values are the immediate subdirectories of the single input parameter, which is the parent directory of the subdirectories.

Here's an example:

     DECLARE @parentdir NVARCHAR(4000)     SELECT @parentdir = N'c:\temp'     EXEC xp_subdirs @parentdir 

XP_DirTree

XP_DirTree returns a two-column result set. The first column is called subdirectory and is of type NVARCHAR. The second column is called depth and is of type INT. The procedure has a required input parameter of type NVARCHAR representing the parent directory and an optional input parameter of type INT representing the depth to return. If the second parameter is specified, then only subdirectories with a depth equal to or less than the specified depth will be returned in the result set. If the second parameter is omitted then all subdirectories of any depth are returned in the result set.

Here is an example that returns the immediate subdirectories of the C:\temp folder:

     CREATE TABLE #dirtree       (subdirectory NVARCHAR(1000),        depth INT       )     INSERT #dirtree     EXECUTE xp_dirtree N'c:\temp',1     SELECT subdirectory, depth FROM #dirtree     DROP TABLE #dirtree 

XP_FixedDrives

XP_FixedDrives is quite similar to XP_AvailableMedia, except that it will list only fixed disks and its precision on available free space is only an integer number of megabytes. It returns a two-column result set. The first column is called drive and is of CHAR(1) data type. The second column is called MBFree and is of type INT. It has no input parameters.

Let's look at an example:

     CREATE TABLE #fixeddrives       (drive CHAR(1),        MBFree INT       )     INSERT #fixeddrives     EXECUTE xp_fixeddrives     SELECT drive, MBFree FROM #fixeddrives     DROP TABLE #fixeddrives 

XP_FileExists

XP_FilesExists is a flexible stored procedure. It is always called with a first parameter containing the path and file name. If the path is omitted from the first parameter and only a file name is given, it will default to the system root folder.

It can be called with a second optional OUTPUT parameter. In this case, it will be a Boolean flag specifying whether the file exists or not. If the second optional OUTPUT parameter is omitted, the procedure returns a single record of three columns. The three columns are File Exists, File is a Directory, and ParentDirectory Exists. Each column is a Boolean flag.

Here are two examples, the first with the optional OUTPUT parameter:

     DECLARE @fileexists INT     EXECUTE xp_fileexist 'c:\windows\explorer.exe', @fileexists OUTPUT     SELECT @fileexists 

This second example doesn't use the optional OUTPUT parameter; use it to test the existence of a folder:

     CREATE TABLE #fileexist       (FileExists BIT,        FileIsDirectory BIT,        ParentDirectoryExists BIT       )     INSERT #fileexist     EXECUTE xp_fileexist 'c:\windows'     SELECT * FROM #fileexist     DROP TABLE #fileexist 

XP_ReadErrorLog

After reading its name, you might think that XP_ReadErrorLog is designed for just reading the SQL Server's error log. This is only partly true; this undocumented stored procedure has the capability to return any text readable file in its entirety. It can return specific rows from the text file that match one of two string search parameters. It is a very flexible and useful stored procedure.

Generally and by default, only system administrators should be allowed to access this procedure. In some instance, you may want to write a wrapper procedure around this that would tightly control the filenames it can read. Since it runs in the SQL Server memory space, it has permissions to read any file that the SQL Server service system account has access to.

The syntax of this procedure is:

     xp_readerrorlog     {@errorLogNumber{,@filename{,@searchstring1{,@searchstring2}}}     } 

The procedure takes four input parameters. They are:

• @errorlognumber TINYINT

  It is optional and accepts numbers between 1 and 99. If omitted, all other parameters should also be omitted, and it will return the current SQL Server error log. If it is added and no other parameters are used, it should be between 1 and 6 and will return the corresponding ERRORLOG.n from the SQL Server's log folder. If the second @filename parameter is specified, it should have a value between 1 and 99.

• @filename

  This is parameter optional and is of type VARCHAR(255). It consists of the fully qualified name of the file that is to be read. You can pass it a non -text file, but the results may be somewhat bizarre and unpredictable.

• @searchstring1

  It is an optional parameter of type VARCHAR(255). If this and @searchstring2 are omitted (or NULL), then every line from the file will be returned in the result set. If the value of this parameter is an empty string ( ''), then it will return all non-blank lines that have a space in them. If it is a string of characters, then it will return all lines that have that string of characters in them. For example, a value of ‘ the’ would be analogous to using the T-SQL comparison function of LIKE ‘%the%’.

• @searchstring2

  This parameter is also optional, and is of type VARCHAR(255). It acts like @searchstring1. If @searchstring1 and @searchstring2, are both specified, then the result set is a Boolean AND of their string matches.

The result produced by this procedure is quite interesting. It consists of two columns – a Line column of data type VARCHAR(256) and a RowContinuation column of type BIT. Normally the RowContinuation bit is 0. If the row from the file is more than 256 characters, it will wrap to the next row and subsequent rows and the RowContinuation bit will flip to 1. You can think of it this way: when the RowContinuation bit is equal to 1, the row appends to the end of the previous row in a continuous chain until you get to a row with a RowContinuation bit of 0.

Here are three examples that make use of this stored procedure:

Example 1 returns the current error log:

     DECLARE @retcode INT     EXECUTE @retcode = xp_readerrorlog 

Example 2 returns the error log file ErrorLog.5 from the log folder

     DECLARE @retcode INT     EXECUTE @retcode = xp_readerrorlog 5 

Example 3 will read the file named hosts from the C:\Temp folder and returns only rows having a space and the character string the in them:

     DECLARE @errorlognumber TINYINT, @retcode INT     DECLARE @file name VARCHAR(255)     DECLARE @searchstring1 VARCHAR(255)     DECLARE @searchstring2 VARCHAR(255)     CREATE TABLE #filerows       (ident INT IDENTITY,        line VARCHAR(256),        continuationRow INT       )     SELECT @errorlognumber = 1     SELECT @filename = 'C:\Temp\hosts'     SELECT @searchstring1 = " -- returns non-blank lines with a space     SELECT @searchstring2= 'the'     -- searches for the three characters 'the' in a line     INSERT #filerows (line, continuationRow)     EXECUTE @retcode = xp_readerrorlog @errorlognumber, @filename,                        @searchstring1, @searchstring2     SELECT line, continuationRow FROM #filerows       ORDER BY ident     DROP TABLE #filerows