Sql-server – COM error on UnionAggregate

geometryspatialsql serversql-server-2012

Has anyone ever come across a COMException while working with geography data in SQL Server?

I didn't notice the error until I ran .Reduce() on the original zip code data. It may have happened before then had I let queries run long enough, but most queries I tried ran for a very long time (up to 5 hours, in once case) before I terminated them.

I'm not entirely sure how to debug something like this, since it's not giving me any useful error code, and I haven't been able to find anything about this sort of error in the documentation.

The error is as follows:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "GeographyUnionAggregate": 
System.Runtime.InteropServices.COMException: Error HRESULT E_FAIL has been returned from a call to a COM component.
System.Runtime.InteropServices.COMException: 
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
   at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
   at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticCombine(CombineMode combineMode, GeoData g1, GeoData g2, Double eccentricity)
   at Microsoft.SqlServer.Types.GeographyUnionAggregate.Terminate()
.

And I'm able to produce it with the following code:

select
    *
from
    tblGeography
    cross apply
    (
        select
            geography::UnionAggregate(ZC_Geography) as NewGeom
        from
            tblGeographyDetail
            join tblZipCode on ZC_Zip_Code like GD_Zip_Code
        where
            GD_GEO_ID=GEO_ID
    ) a
where
    GEO_ID=2

Best Answer

I've found that this issue seems to be a problem with the number of rows I was attempting to aggregate. I get errors when aggregating over about 5,000 rows, but don't get errors (well, the same error, anyway) when aggregating less than that.

My solution ended up being to segment and aggregate the rows using ROW_NUMBER, then aggregate those groups, as in the following:

select
    geography::UnionAggregate(GroupGeography)
from
    (
        select
            geography::UnionAggregate(ZC_Geography) as GroupGeography
        from
            (
                select
                    *,
                    ROW_NUMBER() OVER(PARTITION BY 'dummy' ORDER BY ZCG_Zip_Code) / 1000 as ZipGroup
                from
                    tblZipCode
            ) a
        group by ZipGroup
    ) groups

Related Solutions

Sql-server – How to identify the column(s) responsible for “String or binary data would be truncated.”

~~No, it is not logged anywhere. Go vote and state your business case; this is one on the long list of things that should be fixed in SQL Server.~~

This was requested years ago on Connect (probably first in the SQL Server 2000 or 2005 timeframe), then again on the new feedback system:

binary or string data would be truncated => error message enhancments

And now it has been delivered in the following versions:

In the very first public CTP of SQL Server 2019, it only surfaces under trace flag 460. This sounds kind of secret, but it was published in this Microsoft whitepaper. This will be the default behavior (no trace flag required) going forward, though you will be able to control this via a new database scoped configuration VERBOSE_TRUNCATION_WARNINGS.

Here is an example:

USE tempdb;
GO
CREATE TABLE dbo.x(a char(1));

INSERT dbo.x(a) VALUES('foo');
GO

Result in all supported versions prior to SQL Server 2019:

Msg 8152, Level 16, State 30, Line 5
String or binary data would be truncated.
The statement has been terminated.

Now, on SQL Server 2019 CTPs, with the trace flag enabled:

DBCC TRACEON(460);
GO

INSERT dbo.x(a) VALUES('foo');
GO
DROP TABLE dbo.x;
DBCC TRACEOFF(460);

Result shows the table, the column, and the (truncated, not full) value:

Msg 2628, Level 16, State 1, Line 11
String or binary data would be truncated in table 'tempdb.dbo.x', column 'a'. Truncated value: 'f'.
The statement has been terminated.

Until you can move to a supported version/CU, or move to Azure SQL Database, you can change your "automagic" code to actually pull the max_length from sys.columns, along with the name which you must be getting there anyway, and then applying LEFT(column, max_length) or whatever PG's equivalent is. Or, since that just means you'll silently lose data, go figure out what columns are mismatched and fix the destination columns so they fit all of the data from the source. Given metadata access to both systems, and the fact that you're already writing a query that must automagically match source -> destination columns (otherwise this error would hardly be your biggest problem), you shouldn't have to do any brute-force guessing at all.

Sql-server – SQL server agent job built on SSIS package not working

I would advice you to upgrade the package by following these steps

If after your upgrade you still run in to problems, it could be that your package is using components/drivers that are not available on 64bit. In that case the "easiest" work around would be to have your package use a 32bit runtime.

Open de Job properties and then edit the job step that is calling the SSIS package.

Go to the Execution Options tab and check "use 32bit runtime" enter image description here

It adds the switch /X86 to the Dtexec execution string. (please note that this switch will be ignored if you directly run it from the command line.)

However, be aware that in that case, you are running the package in 32bit while not making use of the 64bit benefits. This shouldn't have to be a problem, just something to keep in mind.

Best Answer

Related Solutions

Sql-server – How to identify the column(s) responsible for “String or binary data would be truncated.”

Sql-server – SQL server agent job built on SSIS package not working

Related Question