Does anyone know how to work around the 128 character limit imposed on the ‘name’ constructor parameter (column name) of the class ‘Microsoft.SqlServer.Server.SqlMetaData’? Or know of an alternative method of returning data to the SQLPipeline, that doesn’t have a similar restriction.
Background: A number of years ago we created a .Net (C#) CLR Stored Procedure, to replace one that was implemented in vb6 and used the ‘TrueOLEDBProviderLib’ (TOLAP). The driving force behind the change was the switch to 64bit SQL Server, which meant the vb6 code could no longer run in process. (vb6 doesn’t do 64bit)
Issue: The core function of our CLR Stored Procedure is, based on a list of ‘data point identifiers’, retrieve and process data from a number of sources (DCOM components), then output a table of data to the SQLPipeline. For the table of data that is returned, we set the column name to the ‘data point identifiers’. Note: That the ‘data point identifiers’ are created based on a hierarchy, so are quite long, with a maximum length of around 256 characters.
The problem we have recently discovered, is that when attempting to output the results to the SQLPipeline, if ‘data point identifiers’ longer than 128 characters are used, then the CLR throws an exception on the length of the ‘name’ (column name). (See ‘.Net Framework Code behaviour’ below) But using the same ‘data point identifiers’ on the old vb6 CLR implementation, it works without error. With the returned table contains column names longer than 128 characters.
Supplementary Question: I know it is a different technology, but why was there no 128 character limit imposed within the SQL Server implementation of ‘TrueOLEDBProviderLib’ (TOLAP). The question I need to provide an answer to is, “if TOLAP can return tables of data that contains column names longer than 128 characters, why can’t the .Net (C#) CLR Stored Procedure”.
Workaround: The obvious fix would be to truncate the ‘data point identifiers’ down to 128 characters. However, as this is a change in functionality from the original vb6 CLR implementation, I need to explore all the alternatives first.
.Net Framework Code behaviour: Within the internal constructor of ‘SqlMetaData’, the method ‘AssertNameIsValid’ is called, where the length of the ‘name’ parameter is checked to be less than ‘SmiMetaData.MaxNameLength’ (128 character), if not an exception is thrown. https://referencesource.microsoft.com/#System.Data/fx/src/data/System/Data/Sql/SqlMetaData.cs I understand that the value of this limit is set based on the 128 character limit SQL Server has for ‘Column_Length’. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-server-info-transact-sql?view=sql-server-ver15
** Additional Info Update: **
The old implementation was a vb6 DLL on the file system, called by a Stored Procedure.
Last version that the vb6 implementation ran on was SQL Server 2008 R2 SP2 (32bit).
The .Net CLR implementation was first run on SQL Server 2012 SP2 (64bit), current version is 2014 SP3 (64bit).
The column names ‘data point identifier’ will have all come from the SP parameters, as there is nothing like this hard coded in the vb6 version. All ‘data point identifier’ are user defined on the deployed system.
