March 23, 2018 at 12:28 pm
Hi All,
I'm trying to populate a temp table. Here is the temp table:CREATE TABLE #SESDMVESEnrollData (
ContractNumber nvarchar(50) not null,
PolicyNumber nvarchar(50) not null,
ProductCode nvarchar(50) not null,
AssetClassCode nvarchar(50) not null,
AssetDesc nvarchar(2000) not null,
CoverageTypeName nvarchar(200) not null,
OrigEnrollTermNum int not null,
EnrollmentID int null,
CoverageID int NOT NULL,
Address1 nvarchar(300) not null,
Address2 nvarchar(300) null,
City nvarchar(200) not null,
StateProvince nvarchar(50) not null,
Country nvarchar(50) not null,
Zip nvarchar(50) null,
EnrollmentAddressId int null,
AssetAddressId int null,
AssetValue money null,
InsuredValueMax money null
);
GO
INSERT INTO #SESDMVESEnrollData
SELECT DISTINCT ContractNumber as ContractNumber
,p.PolicyNumber as PolicyNumber
,mp.ProductCode as ProductCode
,mac.AssetClassCode as AssetClassCode
,EnrollmentAssetDescription as AssetDesc
,psc.PolicySchemaCoverageName AS CoverageTypeName
,COALESCE(CASE WHEN DATEDIFF(DAY, ContractEffectiveDate, ContractMaturityDate) / (365/12) < 1 THEN 1 ELSE DATEDIFF(DAY, ContractEffectiveDate, ContractMaturityDate) / (365/12) END, -1) as OrigEnrollTermNum
,EnrollmentRecordID as EnrollmentID
,CoveragePeriodID as CoverageID
...
Initially, the error I received was "uniqueidentifieris incompatible with int", as the query is trying to us the uniqueidentifier field as an int field. So I tried to use convert by trying:
,Convert(int, convert(varbinary(36),ec.EnrollmentRecordID())) as EnrollmentID
I also tried:
Convert(int,convert(varchar(36),ExtVESEnrollmentCoverage.EnrollmentRecordID())) as EnrollmentID,
but I keep getting a "Cannotfind either column "ec" or the user-defined function or aggregate"ec.EnrollmentRecordID", or the name is ambiguous.". Possiblefailure reasons: Problems with the query, "ResultSet" property notset correctly, parameters not set correctly, or connection not establishedcorrectly." error so some variation of it.
This is part of an SSIS package I'm trying to develop. Any advice on how to correct this would be greatly appreciated. Thanks in advance!
March 24, 2018 at 4:22 pm
daniness - Friday, March 23, 2018 12:28 PMHi All,
I'm trying to populate a temp table. Here is the temp table:
CREATE TABLE #SESDMVESEnrollData (
ContractNumber nvarchar(50) not null,
PolicyNumber nvarchar(50) not null,
ProductCode nvarchar(50) not null,
AssetClassCode nvarchar(50) not null,
AssetDesc nvarchar(2000) not null,
CoverageTypeName nvarchar(200) not null,
OrigEnrollTermNum int not null,
EnrollmentID int null,
CoverageID int NOT NULL,
Address1 nvarchar(300) not null,
Address2 nvarchar(300) null,
City nvarchar(200) not null,
StateProvince nvarchar(50) not null,
Country nvarchar(50) not null,
Zip nvarchar(50) null,
EnrollmentAddressId int null,
AssetAddressId int null,
AssetValue money null,
InsuredValueMax money null
);
GOINSERT INTO #SESDMVESEnrollData
SELECT DISTINCT ContractNumber as ContractNumber
,p.PolicyNumber as PolicyNumber
,mp.ProductCode as ProductCode
,mac.AssetClassCode as AssetClassCode
,EnrollmentAssetDescription as AssetDesc
,psc.PolicySchemaCoverageName AS CoverageTypeName
,COALESCE(CASE WHEN DATEDIFF(DAY, ContractEffectiveDate, ContractMaturityDate) / (365/12) < 1 THEN 1 ELSE DATEDIFF(DAY, ContractEffectiveDate, ContractMaturityDate) / (365/12) END, -1) as OrigEnrollTermNum
,EnrollmentRecordID as EnrollmentID
,CoveragePeriodID as CoverageID
...
Initially, the error I received was "uniqueidentifieris incompatible with int", as the query is trying to us the uniqueidentifier field as an int field. So I tried to use convert by trying:
,Convert(int, convert(varbinary(36),ec.EnrollmentRecordID())) as EnrollmentIDI also tried:
Convert(int,convert(varchar(36),ExtVESEnrollmentCoverage.EnrollmentRecordID())) as EnrollmentID,but I keep getting a "Cannotfind either column "ec" or the user-defined function or aggregate"ec.EnrollmentRecordID", or the name is ambiguous.". Possiblefailure reasons: Problems with the query, "ResultSet" property notset correctly, parameters not set correctly, or connection not establishedcorrectly." error so some variation of it.
This is part of an SSIS package I'm trying to develop. Any advice on how to correct this would be greatly appreciated. Thanks in advance!
I have to ask... what is the reason why you're trying to convert a GUID to an INT in the first place? What is there to gain by the conversion?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2018 at 9:08 am
unique identifier is not a numeric that can be fit into an int. even a bigint is probably not a large enough integer type to contain the unique identifier type even if we did successfully convert each hex digit. its sort of like trying this but not exactly, because obviously the error messages are different:
select convert(int,'hi there, I'm not really an integer compatible data type')
the correct way to store a unique identifier in a numeric field of any sort would be to ditch the dashes, then from rightmost to left most, convert each hex digit into a nibble (the corresponding 4 bits in your destination integer) using a valid hex to int converter. However even then you run into another problem, that is, bigint, your best candidate here still lacks the ability to hold enough bits.
Minus the dashes, unique identifier leaves us with 32 characters of information (at least it looks that way to me), each of which holds one of 16 numbers which is storable into a "nibble" (4 bits). So 32 / 2 (each byte holds two nibbles). With bigint being 64 bits of precision (8 bytes), you'd need two bigints to hold all the information needed to save an instance of unique identifier. So without losing information, you cannot directly store a unique identifier value into an integer that would work with SQL Server natively even if you did do some sort of nibble writer.
edit: I forgot my usual disclaimer, I always welcome posts that will point out mistakes in any information I contribute. thanks!
March 26, 2018 at 3:19 pm
patrickmcginnis59 10839 - Monday, March 26, 2018 9:08 AMunique identifier is not a numeric that can be fit into an int. even a bigint is probably not a large enough integer type to contain the unique identifier type even if we did successfully convert each hex digit. its sort of like trying this but not exactly, because obviously the error messages are different:...{snip}
edit: I forgot my usual disclaimer, I always welcome posts that will point out mistakes in any information I contribute. thanks!
You're correct. A UniqueIdentifier can be directly converted to a BINARY(16), which is 16 bytes, which is "only" 8 orders of magnitude larger than an 8 byte BigInt.
I wish the OP would come back and identify why they think they need to do such a conversion.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 27, 2018 at 6:42 am
Jeff Moden - Monday, March 26, 2018 3:19 PMpatrickmcginnis59 10839 - Monday, March 26, 2018 9:08 AMunique identifier is not a numeric that can be fit into an int. even a bigint is probably not a large enough integer type to contain the unique identifier type even if we did successfully convert each hex digit. its sort of like trying this but not exactly, because obviously the error messages are different:...{snip}
edit: I forgot my usual disclaimer, I always welcome posts that will point out mistakes in any information I contribute. thanks!
You're correct. A UniqueIdentifier can be directly converted to a BINARY(16), which is 16 bytes, which is "only" 8 orders of magnitude larger than an 8 byte BigInt.
I wish the OP would come back and identify why they think they need to do such a conversion.
In my experience with SSIS eventually you're willing to try anything to get it to work. Could just be me though!
March 27, 2018 at 9:43 am
daniness - Friday, March 23, 2018 12:28 PMHi All,
I'm trying to populate a temp table. Here is the temp table:
CREATE TABLE #SESDMVESEnrollData (
ContractNumber nvarchar(50) not null,
PolicyNumber nvarchar(50) not null,
ProductCode nvarchar(50) not null,
AssetClassCode nvarchar(50) not null,
AssetDesc nvarchar(2000) not null,
CoverageTypeName nvarchar(200) not null,
OrigEnrollTermNum int not null,
EnrollmentID int null,
CoverageID int NOT NULL,
Address1 nvarchar(300) not null,
Address2 nvarchar(300) null,
City nvarchar(200) not null,
StateProvince nvarchar(50) not null,
Country nvarchar(50) not null,
Zip nvarchar(50) null,
EnrollmentAddressId int null,
AssetAddressId int null,
AssetValue money null,
InsuredValueMax money null
);
GOINSERT INTO #SESDMVESEnrollData
SELECT DISTINCT ContractNumber as ContractNumber
,p.PolicyNumber as PolicyNumber
,mp.ProductCode as ProductCode
,mac.AssetClassCode as AssetClassCode
,EnrollmentAssetDescription as AssetDesc
,psc.PolicySchemaCoverageName AS CoverageTypeName
,COALESCE(CASE WHEN DATEDIFF(DAY, ContractEffectiveDate, ContractMaturityDate) / (365/12) < 1 THEN 1 ELSE DATEDIFF(DAY, ContractEffectiveDate, ContractMaturityDate) / (365/12) END, -1) as OrigEnrollTermNum
,EnrollmentRecordID as EnrollmentID
,CoveragePeriodID as CoverageID
...
Initially, the error I received was "uniqueidentifieris incompatible with int", as the query is trying to us the uniqueidentifier field as an int field. So I tried to use convert by trying:
,Convert(int, convert(varbinary(36),ec.EnrollmentRecordID())) as EnrollmentIDI also tried:
Convert(int,convert(varchar(36),ExtVESEnrollmentCoverage.EnrollmentRecordID())) as EnrollmentID,but I keep getting a "Cannotfind either column "ec" or the user-defined function or aggregate"ec.EnrollmentRecordID", or the name is ambiguous.". Possiblefailure reasons: Problems with the query, "ResultSet" property notset correctly, parameters not set correctly, or connection not establishedcorrectly." error so some variation of it.
This is part of an SSIS package I'm trying to develop. Any advice on how to correct this would be greatly appreciated. Thanks in advance!
There are two problems here:
1.) CONVERT to int, or even bigint, just isn't viable when the source is a GUID. There simply isn't enough room in either of those data types to handle a value coming from a GUID, and that's why neither CAST nor CONVERT is going to work for that purpose. If you can say what your objective is in that regard, we can probably help figure that piece out.
2.) From the error message, it appears you're trying to call a function named EnrollmentRecordID. If that's a scalar function, you may at least need to be sure you specify the schema correctly for that object, but if it's a table-valued function, then you might be looking to SELECT from it. Or is it even a function at all? Provide some details and let's see what we can figure out...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 3, 2018 at 12:08 pm
Thanks, all for the replies. In brief, the reason I was trying to do this is to pull from a source field into a destination field for an SSIS package. I seemed to have found a workaround, but will revisit this if I come across it again. I appreciate your input.
April 3, 2018 at 5:15 pm
daniness - Tuesday, April 3, 2018 12:08 PMIn brief, the reason I was trying to do this is to pull from a source field into a destination field for an SSIS package.
We mostly know that but why the conversion from a GUID to and INT, especially since a GUID won't actually fit into an INT or even a BIGINT?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 3, 2018 at 5:32 pm
Want my guess. In the source table the EnrollmentRecordID is a uniqueidentifier. In the destination table the EnrollmentID is an int. The OP probably wants to map each unique uniqueidentifier (assuming that there may be duplicates based on the data, OP is using a DISTINCT in the query) to a unique integer value in the new table.
Seems like the OP needs a mapping table of some sort.
April 3, 2018 at 6:49 pm
Lynn Pettis - Tuesday, April 3, 2018 5:32 PMWant my guess. In the source table the EnrollmentRecordID is a uniqueidentifier. In the destination table the EnrollmentID is an int. The OP probably wants to map each unique uniqueidentifier (assuming that there may be duplicates based on the data, OP is using a DISTINCT in the query) to a unique integer value in the new table.
Seems like the OP needs a mapping table of some sort.
Could be. My guess would be that someone has heard how bad GUIDs can be (they're actually pretty good for high frequency INSERTs in avoiding page level hot spots, especially if you know how to build the related CI and then maintain it correctly. And traditional index maintenance isn't going to hack it) and are simply trying to avoid GUIDs. If they actually do want to do that, then they will, in fact, need a separate mapping table but don't see that being much use in the Temp Table where there is no mapping.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 10, 2018 at 8:51 am
Lynn Pettis - Tuesday, April 3, 2018 5:32 PMWant my guess. In the source table the EnrollmentRecordID is a uniqueidentifier. In the destination table the EnrollmentID is an int. The OP probably wants to map each unique uniqueidentifier (assuming that there may be duplicates based on the data, OP is using a DISTINCT in the query) to a unique integer value in the new table.
Seems like the OP needs a mapping table of some sort.
That's exactly what the situation was, Lynn. Thanks! Since posting this, I've actually had to make changes, per advice from the business analysts that we won't have to use the uniqueidentifier field, so I'm in the clear for now. Thank you, Lynn and all for your valuable insight! 🙂
April 11, 2018 at 10:14 pm
Will this affect any foreign keys in other tables that map to your enrollment table, when essentially changing the id field datatype from a unique identifier to a int ?
----------------------------------------------------
April 18, 2018 at 8:01 am
MMartin1, this is quite possible. I suppose that'd be something to consider if this issue needs to be revisited in the future...thanks for your input :-).
April 18, 2018 at 11:45 am
You can leverage the BINARY_CHECKSUM() function to hash a GUID into an INT. However, keep in mind that the cardinality of a 4 byte integer is significantly lower than a 16 byte GUID, so there is potential for collisions, meaning that multiple GUIDs can hash into the same integer value.
For example:
DECLARE @GUID UNIQUEIDENTIFIER = NEWID();
PRINT @GUID;
DECLARE @ID INT = BINARY_CHECKSUM( @GUID );
PRINT @ID;
GUID: 3D1DEBBE-5BB7-4624-8C85-C6456D8144E1
INT: 1137015972
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply