Tedious: Bulk load error: While reading current row from host, a premature end-of-message was encountered.

[cdibbs]

While doing what should be a simple bulk load of some 32kb images (only one such image during testing), I get the following error message:

RequestError: While reading current row from host, a premature end-of-message was
encountered--an incoming data stream was interrupted when the server expected to see
more data. The host program may have terminated. Ensure that you are using a supported
client application programming interface (API).

    at Connection.<anonymous> (H:\Projects\csi-photo-import\node_modules\mssql\lib\tedious.js:478:17)
    at Connection.emit (events.js:95:17)
    at Parser.<anonymous> (H:\Projects\csi-photo-import\node_modules\mssql\node_modules\tedious\lib\connection.js:434:15
)
    at Parser.emit (events.js:95:17)
    at Parser.nextToken (H:\Projects\csi-photo-import\node_modules\mssql\node_modules\tedious\lib\token\token-stream-par
ser.js:91:18)
    at Parser.addBuffer (H:\Projects\csi-photo-import\node_modules\mssql\node_modules\tedious\lib\token\token-stream-parser.js:68:17)
    at Connection.sendDataToTokenStreamParser (H:\Projects\csi-photo-import\node_modules\mssql\node_modules\tedious\lib\
connection.js:869:35)
    at Connection.STATE.SENT_CLIENT_REQUEST.events.data (H:\Projects\csi-photo-import\node_modules\mssql\node_modules\tedious\lib\connection.js:273:23)
    at Connection.dispatchEvent (H:\Projects\csi-photo-import\node_modules\mssql\node_modules\tedious\lib\connection.js:732:59)
    at MessageIO.<anonymous> (H:\Projects\csi-photo-import\node_modules\mssql\node_modules\tedious\lib\connection.js:660:22)

Here's my code:

            var req = new sql.Request(conn);
            var table = new sql.Table('#mempictures');
            table.create = true;
            table.temporary = true;
            table.columns.add('uid', sql.VarChar(50), {nullable:false});
            table.columns.add('mempicture', sql.Image, {nullable:false});
            //... then in a loop
                    table.rows.add(keys[i], memberPhotos[keys[i]].BinaryPhotoBuffer); //, memberPhotos[keys[i]].BinaryImage);   

            //... completed with
            var deferred = new q.defer();
            req.bulk(table, function(err, rowct) {
                if (err)
                    deferred.reject(err);

                deferred.resolve(statuses);
            });
            return deferred.promise;

Am I missing something? I feel like this may be related to #197.

[bretcope]

I solved #197 by comparing how the bcp utility encodes nvarchar(max) (via WireShark) with the way Tedious was doing it. That's probably the easiest way to analyze this problem as well.

If you look around the TDS spec, there are lots of one-off notes which say how image, text, and ntext act different or cause different tokens to be included in headers. It doesn't surprise me at all that there are edge cases Tedious doesn't handle. Hopefully you're already aware that the image type has been deprecated for over six years, and is likely going to be removed from SQL Server in the near future (you should be using varbinary instead).

Quite honestly, everyone here works on this library in our free time, and tracking down a problem in a deprecated type doesn't seem like a very rewarding use of my limited free time. Maybe @patriksimek would be interested. If there's a problem with a non-deprecated type (like what happened with nvarchar(max)), then that's a different story. Also, if you do the initial leg-work to figure out what bcp and Tedious are doing differently, we may also be able to provide more assistance.

[cdibbs]

Hmm. I was not aware the Image type had been deprecated, but I am not surprised. My group just inherited a 3rd party DB from elsewhere on campus which uses it.

Anyway, since I am bulk loading into a temporary table, then doing a join/update from there, I think I can get away with a VarBinary in the temporary table followed by a conversion as a work-around.

Thanks for all your hard work on this project!

[bretcope]

Okay great. If you have problems with varbinary, then definitely post that and we'll look into it.

[patriksimek]

I don't think it's worth digging into it. I was able to reproduce the issue but have no idea why this is happening. There is a note in docs:

XMLTYPE is only a valid LONGLEN_TYPE for BulkLoadBCP.

Image is LONGLEN_TYPE as well, so this sounds to me like there is no support for IMAGE in bulk insert. What is more stranger is another note in the docs:

Note that for INSERT BULK operations, XMLTYPE is to be sent as NVARCHAR(N) or NVARCHAR(MAX) data type. An error is produced if XMLTYPE is specified.

So, is XMLTYPE valid in bulk insert or not? It's not clear to me...

[bretcope]

Just another example of how terrible the TDS docs are. My first thought was to check the same things you did and see if there was a note saying whether image is a valid bulk insert type or not, and whether it should be encoded differently than usual, but I came away without a conclusion. I agree it's not worth digging into.

[jianingliu]

I got a couple of this error before. It happens also when you don't have any row inserted in the loop. A typical case is that you have columns added, and then you take some array from other places, and iterate that array to insert row. Sometimes, when the array is empty, no row is inserted, and I'll get this error. So, better to check if the array is empty before execute the bulkload.