3

I have a problem with UPDATE STATISTICS where the column is a user-defined type (UDT) from an assembly.

Step by step:

  1. Assembly:

    CREATE ASSEMBLY [UDT.TParams] FROM 0x4D...(stripped) WITH PERMISSION_SET = SAFE
    
  2. Type:

    CREATE TYPE [dbo].[TParams] EXTERNAL NAME [UDT.TParams].[Sql.UDT.TParams]
    
  3. Table:

    CREATE TABLE [dbo].[Dic:Table:With&Weird:Name](
        [Id] [tinyint] NOT NULL,
        [Group_Id] [tinyint] NOT NULL,
        [Name] [nvarchar](50) NOT NULL,
        [Code] [varchar](25) NOT NULL,
        [PersonTypes_IDs] [varchar](50) NOT NULL,
        [Unique] [bit] NULL,
        [SortIndex] [tinyint] NOT NULL,
        [FieldName]  AS (replace([Code],':','_')),
        [GUI:Edit:Type] [varchar](64) NULL,
        [GUI:Edit:Params] [dbo].[TParams] NULL,
     ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
  4. Statistics (it's auto-created by SQL Server):

    CREATE STATISTICS [weirdStatistics] ON
          [dbo].[Dic:Table:With&Weird:Name]([GUI:Edit:Params])
    
  5. Then we have scheduled weekly updates on all statistics with FULLSCAN:

    UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [weirdStatistics] WITH FULLSCAN
    

Getting this error after above command:

Msg 1038, Level 15, State 4, Line 3
An object or column name is missing or empty.
For SELECT INTO statements, verify each column has a name.
For other statements, look for empty alias names.
Aliases defined as "" or [] are not allowed.
Change the alias to a valid name.

How do I fix this error, or do I just skip the statistics update?

UPDATE: Opening properties windows of [weirdStatistics] in SSMS, will show error: DataReader.GetFieldType(0) returned null. (SqlManagerUI). Any other statistics for table opens normally.

  • Are 100% sure that the UPDATE STATISTICS command in the question above is the exact command being executed in that weekly update stats (SQL Agent Job, I assume)? I just copied your table definition into SQL Server 2016 (only changing the UDT to one that I have), running the command in step 4, then running the command in step 5. no errors. Maybe the error is correct and the actual execution is missing the object name? – Solomon Rutzky Jul 5 '17 at 13:43
  • @srutzky, object in place, and this is not one error I get with [TParam] field type. – Oleg Strutinskii Jul 6 '17 at 6:41
  • What exact version of everything are you using? I just tested again, this time with SQL Server 2014 SP2 and SSMS 2014 (12.0.5203.0) and it works just fine. In SSMS, I can open up the properties and it starts on the "General" tab. If I select the "Details" tab I get the error you reported in the UPDATE section. But if I try a second time to go to "Details" it works, at least to go to the tab, but the tab doesn't have any details, saying "no statistics available". In SSMS, can you run: DECLARE @Test [dbo].[TParams]; SET @Test = NULL; ? Does that error or complete? – Solomon Rutzky Jul 6 '17 at 19:26
  • Exact version for test environments SQL Server 12.0.5546.0, for production 12.0.5511.0. SSMS 14.0.17119.0 – Oleg Strutinskii Jul 7 '17 at 9:05
  • I have been testing on: SQL Server 12.0.5203 and 13.0.4001, and using SSMS 12.0.5203 and 14.0.17119. – Solomon Rutzky Jul 7 '17 at 14:09
4

I do not believe there is any issue here with regards to the User-Defined Type (UDT) as I am able to execute this successfully:

Please note that I am using the Free version of the SQL# library (which I wrote) to test with:

CREATE TABLE [dbo].[Dic:Table:With&Weird:Name](
    [Id] [tinyint] NOT NULL,
    [Group_Id] [tinyint] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Code] [varchar](25) NOT NULL,
    [PersonTypes_IDs] [varchar](50) NOT NULL,
    [Unique] [bit] NULL,
    [SortIndex] [tinyint] NOT NULL,
    [FieldName]  AS (replace([Code],':','_')),
    [GUI:Edit:Type] [varchar](64) NULL,
    [GUI:Edit:Params] [SQL#].[Type_HashTable] NULL
);

CREATE STATISTICS [weirdStatistics] ON [dbo].[Dic:Table:With&Weird:Name]([GUI:Edit:Params]);

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [weirdStatistics] WITH FULLSCAN;

No errors. However, if I remove the stats object name (the error did mention a missing object name) but keep the square brackets, then I am able to reproduce the error:

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [] WITH FULLSCAN;

returns:

Msg 1038, Level 15, State 4, Line 28
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

And no square brackets at all works just fine (since it updates all stats on the table):

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] WITH FULLSCAN;

So it really needs to be empty square brackets. My guess is that your weekly Update Stats job (I am assuming it is a SQL Agent Job since you said "weekly") is somehow leaving out the stats object name.

AND, since the question actually mentions auto-created statistics, that should be tested as well, just to be sure that there isn't an issue with those (there shouldn't be, but it's still best to test and know for sure):

-- get rid of user-created statistics object:
DROP STATISTICS [dbo].[Dic:Table:With&Weird:Name].[weirdStatistics];

-- add one row so that we can query it:
INSERT INTO dbo.[Dic:Table:With&Weird:Name]
VALUES (1, 2, N'ddfg', 'df:uiui', 'dfgdfgd', 0, 44, 'type', N'key1=SomeVal');

-- get SQL Server to auto-create the statistics object on this column:
SELECT *
FROM   dbo.[Dic:Table:With&Weird:Name]
WHERE  [GUI:Edit:Params] IS NOT NULL;

-- get the dynamically generated statistics object name and use it for UPDATE STATS:
DECLARE @StatName sysname;

SELECT @StatName = st.[name]
FROM sys.stats st
INNER JOIN sys.stats_columns sc
        ON sc.[object_id] = st.[object_id]
       AND sc.[stats_id] = st.[stats_id]
INNER JOIN sys.columns col
        ON col.[object_id] = sc.[object_id]
       AND col.[column_id] = sc.[column_id]
WHERE st.[object_id] = OBJECT_ID(N'dbo.Dic:Table:With&Weird:Name')
AND   col.[name] = N'GUI:Edit:Params';

SELECT @StatName AS [StatisticsName];

EXEC (N'UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [' + @StatName
      + N'] WITH FULLSCAN;');

The code above also completes without errors :-).


The following test should be done to separate the UDT from this particular usage of it (i.e. this table). The example below again uses SQL#, but you should replace [SQL#].[Type_HashTable] with [dbo].[TParams] and then replace the string value in the first INSERT statement with the appropriately formatted initialization text. If this does not error then the issue is with that table, possibly with the column name itself, such as having a hidden character in it. If it does error then we need to look at the definition of that UDT.

CREATE TABLE [dbo].[tmpTestStats](
    [Id] [tinyint] NOT NULL,
    [UDT] [SQL#].[Type_HashTable] NULL
);

INSERT INTO [dbo].[tmpTestStats] ([Id], [UDT]) VALUES (1, N'k1=v1&k2=v2');
INSERT INTO [dbo].[tmpTestStats] ([Id], [UDT]) VALUES (2, NULL);

CREATE STATISTICS [tmpTestStats_UDT] ON [dbo].[tmpTestStats]([UDT]);

UPDATE STATISTICS [dbo].[tmpTestStats] [tmpTestStats_UDT] WITH FULLSCAN;
  • UPDATE STATISTICS [Base].[Dic:Persons:Identifiers&Specificators:Types] WITH FULLSCAN - I get same error: Msg 1038, Level 15, State 4, Line 1...An object or column name is missing or empty. For... – Oleg Strutinskii Jul 6 '17 at 7:08
  • Are logged on as sa or at least a Login in the sysadmin server role, or a User that is in the db_owner database role? Does the table have data in it? Are you able to INSERT a row? Can you try to do that in SSMS by issuing BEGIN TRAN; first and then ROLLBACK TRAN; after so that the changes isn't saved? – Solomon Rutzky Jul 6 '17 at 13:00
  • I tried in test environment all you proposed to do. Table contains 35 lines, and only to lines that not NULL in [TParams] column. I have dropped autocreated statistic, then insert about 7 lines, selected *, but server did not create new statistic. – Oleg Strutinskii Jul 7 '17 at 9:03
  • @OlegStrutinskii auto-stats are created when a WHERE condition (or JOIN, I assume) references that column. That is why I had the WHERE [GUI:Edit:Params] IS NOT NULL in my test code. I think the issue is with the table or the column name somehow. I will update my answer with a new test at the bottom. – Solomon Rutzky Jul 7 '17 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.