tsqlt ways to test stored procedures producing files as output

Question

I have a stored procedure which created a file using BCP. I need to validate the data in the file with a predefined expected resultset. Is there a way this can be tested using TSQLT?

Answer 1

Using tSQLt Tests to confirm bcp.exe reading in data is usually possible.

Using tSQLt Tests to confirm bcp.exe writing out data, on the other hand, is generally quite problematic.

The reason is that tSQLt Tests are run inside SQL Transactions so that they can clean up any modifications you've made at the end of each test. This basically rules out any tSQLt Tests involving external processes (bcp.exe, isql.exe, osql.exe, sqlcmd.exe, etc.) accessing any tables that your tSQLt Tests (or Setup procedures) modify because those tables will have ROW/PAGE/TABLE locks held on them for the duration of the current tSQLt Test. External processes will be connecting back into the database using a different SQL Connection and so will be blocked by the ROW/PAGE/TABLE locks that the tSQLt Transaction holds. bcp.exe, in particular, will essentially wait forever for the locks to be released which will never happen because the tSQLt Test is waiting forever for bcp.exe to return.

So, there are three possibilities here:

1. Only use bcp.exe to export pre-existing data in tables that you do not modify at all during the tSQLt Test. (This includes ##Temp Tables.)

2. Don't use bcp.exe (, isql.exe, osql.exe, sqlcmd.exe, etc.) to export data at all. Prefer to use SQL CLR Procedures. Written properly (as in, given @table parameters) these will run in the current SQL connection and so will not be obstructed by current ROW/PAGE/TABLE locks.

3. Alternatively to 2, if you don't like writing SQL CLR Assemblies, try using OLE Automation Procedures to export your data. Again these will run in the current SQL Connection so, written properly, will not be obstructed by current ROW/PAGE/TABLE locks. Be aware that this opens up more security holes on your SQL Server as you need to enable OLE Automation Procedures via sp_configure... but if you're using bcp.exe from SQL then you've already enabled xp_cmdshell on your SQL Server.

To enable OLE Automation Procedures execute the following:

sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'Show Advanced Options', 0;
GO
RECONFIGURE;
GO

An SQL stored procedure to write an XML variable out to a file might look like this:

create procedure [dbo].[sp_ExportXmlDataToFile]
    @XmlData xml,
    @XmlFilename nvarchar(255)
as begin
    declare @OleAutomationObjectHandle int, @FileHandle int;
    declare @HResult int, @Source nvarchar(255), @Description nvarchar(255);

    --REF: OpenTextFile Method, https://msdn.microsoft.com/en-us/library/aa265347(v=vs.60).aspx
    declare @IOMode_ForReading int = 1, @IOMode_ForWriting int = 2, @IOMode_ForAppending int = 8;
    declare @Create_OpenExisting int = 0, @Create_CreateNew int = 1;
    declare @FileFormat_ASCII int = 0, @FileFormat_Unicode int = -1, @FileFormat_Default int = -2; --<<--NOTE: Negative numbers

    execute @HResult = sys.sp_OACreate N'Scripting.FileSystemObject', @OleAutomationObjectHandle out
    if (@HResult <> 0)
    begin
        exec sys.sp_OAGetErrorInfo @OleAutomationObjectHandle, @Source out, @Description out
        raiserror(N'Error Creating COM Component (Scripting.FileSystemObject) 0x%x, %s, %s', 16, 1, @HResult, @Source, @Description) 
    end
    else
    begin
        execute @HResult = sys.sp_OAMethod @OleAutomationObjectHandle, N'OpenTextFile', @FileHandle out, @XmlFilename, @IOMode_ForWriting, @Create_CreateNew, @FileFormat_Default
        if (@HResult <> 0)
        begin
            exec sys.sp_OAGetErrorInfo @OleAutomationObjectHandle, @Source out, @Description out
            raiserror(N'Error calling COM Component (Scripting.FileSystemObject.OpenTextFile) 0x%x, %s, %s', 16, 1, @HResult, @Source, @Description) 
        end
        else
        begin
            declare @Text nvarchar(max) = cast(@XmlData as nvarchar(max))
            execute sys.sp_OAMethod @FileHandle, N'Write', null, @Text
            execute sys.sp_OADestroy @FileHandle
        end
        execute sys.sp_OADestroy @OleAutomationObjectHandle
    end
end
go

Then, a tSQLt Test that uses it might look something like this:

create procedure [SomethingOrOther_Tests].[Test That sp_LoadXmlFile Inserts Data]
as begin
    -- Assemble
    declare @Expected uniqueidentifier = newid();
    declare @XmlFilename nvarchar(255) = N'C:\Temp\SomethingOrOther_Tests.Test That sp_LoadXmlFile Inserts Data.xml';
    declare @XmlData xml =
N'<S11:Envelope xmlns:S11="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <S11:Header/>
    <S11:Body/>
</S11:Envelope>';
    exec [dbo].[sp_ExportXmlDataToFile] @XmlData=@XmlData, @XmlFilename=@XmlFilename

    -- Act
    exec [dbo].[sp_LoadXmlFile] @ImportGUID=@Expected, @XmlFilename=@XmlFilename

    -- Assert
    declare @Actual uniqueidentifier = (select ImportGUID from dbo.tImportTable where ImportGUID=@Expected)
    exec [tSQLt].[AssertEquals] @Expected, @Actual, 'Did not find expected ImportGUID'
end
go

Answer 2

The other option might be to split the responsibilities. One function or stored procedure to generate the required results and a second one to call the first and export the data.

You could write one or more tests to validate that the GetMyData procedure was building the correct results.

You would then need to write tests against the ExportMyData stored procedure to prove that it:

a) calls GetMyData - using `tSQLt.SpyProcedure' which you can configure to return a single row of dummy data for export - but more importantly use to confirm that this sproc was actually called

b) actually creates a file - you could do something like this at the end of the test:

declare @tbl table (FileExists int, IsDirectory int, ParentDirectoryExists int)

insert @tbl
exec xp_fileexist 'c:\temp\greg.txt';

declare @expected int = 1;
declare @actual int = (select FileExists from @tbl)

exec tSQLt.AssertEquals @expected, @actual, 'Expected file "c:\temp\greg.txt" not found';

The only thing that this won't allow you to do is test that the output of GetMyData did actually get written to the resulting file. For that, as suggested earlier you might need to use BULK INSERT. At least if you divide the responsibilities as I suggest you can use more conventional methods to test the actual output and your BULK INSERT test only need to validate the one dummy row generated by `tSQLt.SpyProcedure'