#689 INvalid packet type 0x0 (if called procedure selects from table type)

v1.3
open
nobody
None
1
2013-03-07
2013-01-18
m.hilpert
No

This happen with older 1.2.2 driver as with new 1.3.0 driver (accessing SQL Server 2008 R2 database on windows 64 Bit):

A stored procedure is called which does sum SQL commands and returns a ResultSet with a finaly SELECT statement. This works flawlessly, if the final SELECT selects from a table or a table variable. But if it selects from a custom table type, it throws this exception:

TDS Protocol error: Invalid packet type 0x0, SQLSTate=08S01

The workaround is to create a (redundant) table variable in the procedure with the same columns as the table type and INSERT the rows via:

INSERT INTO @l_TableFromLocalTableVariable
SELECT * FROM @l_TableFromTableType

With this workaround the error doesn't occur. This is strange as the original source of the final select should be irrelevant - but it seems, it is not. Is this a known problem in the jTDS driver?

Of course, the workaround is ugly, as it just copies the same rows from a table type container to a table variable container.

Discussion

  • m.hilpert
    m.hilpert
    2013-01-18

    The @l_TableFromTableType is a container for a table variable:

    CREATE TYPE test_MyType AS TABLE (...)

    IN the called stored procedure it is used as:

    DECLARE @l_TableFromTableType AS test_MyType

    filled with data and finllay selected with

    SELECT Col1, Col2, Col3 FROM @l_TableFromTableType

    The workaround is:

    DECLARE @l_TableFromLocalTableVariable TABLE (...)

    INSERT INTO @l_TableFromLocalTableVariable
    SELECT Col1, Col2, Col3 FROM @l_TableFromTableType

    SELECT * FROM @l_TableFromLocalTableVariable

     
    Last edit: m.hilpert 2013-01-18
  • momo
    momo
    2013-01-18

    Thank you for reporting this issue and providing a workaround! Looks like there is something wrong with our protocol implementation... but this needs further investigation.

    Cheers,
    momo

     
  • m.hilpert
    m.hilpert
    2013-03-06

    Any chance to get this fixed soon? The workaround is just to show that it's a driver issue. Of course, we can't change all database code to implement this workaround at any such places.

     
  • momo
    momo
    2013-03-07

    If you are able to provide a simple unit test showing the problem I could have a look at this within the next few days.

    Cheers,
    momo