0

I have a user defined data type in SQL Server 10 (2008):

CREATE TYPE [dbo].[sweyi047_corrid] FROM [binary](24) NULL

When this type is selected from a table I am unable to retrieve the data from .NET because I get an SqlException at ExecuteReader claiming that the type sweyi047_corrid is unknown for parameter no. '1'. Trivial example:

var stmt = Connection.CreateCommand();
stmt.CommandText = @"CREATE TABLE #test ( col sweyi047_corrid );
  INSERT INTO #test VALUES(0x1020);
  SELECT col from #test";
var rdr = stmt.ExecuteReader(); // crash!
rdr.Read();
var ret = rdr.GetValue(0);

If I select only a scalar, everything works as expected for the same type:

stmt.CommandText = @"DECLARE @var sweyi047_corrid;
  SET @var = 0x1020;
  SELECT @var;";

In this case I get an object of type byte[24] as expected.

How to read the tables containing UDT columns correctly?

Marcel
  • 1,688
  • 1
  • 14
  • 25
  • In your first SQL, does it work if you `SELECT Convert(binary(24), col from #test` ? – tgolisch Oct 15 '18 at 20:16
  • @tgolisch Thanks for pointing me into the right direction. Your test case fails too and the error message has nothing to do with retrieval of data. The message text is just somewhat misleading. – Marcel Oct 16 '18 at 20:44

1 Answers1

0

The question turns out to be a duplicate of User-defined Data Type and #temp table. It has nothing to do with .NET at all. The SQL code itself is invalid.

The problem is not that temporary tables cannot refer to UDTs of the current database. And there is no reasonable work around without hard coding the UDTs underlying data type into the script, which turns UDT ad absurdum.

The only practical work around is not to use temporary tables and use table variables instead. They, however, do not support the full feature set of temporary tables. In case you need one of this features like indices you are lost.

Marcel
  • 1,688
  • 1
  • 14
  • 25