CODEWITHSUNDEEP
sql-serversql-server-2012
TrialByError
TrialByError

Reputation: 53

Conversion failed with uniqueidentifier

Pretty basic here as I'm trying to insert into table but coming up with error below. Help Please

SET ANSI_WARNINGS  OFF
INSERT INTO [P6_GrassleyCommitteeInc_20150223].[dbo].[std_Individual]
           ([ind_GUID]
           ,[ind_Prefix]
           ,[ind_FirstName]
           ,[ind_MiddleName]
           ,[ind_LastName]
           ,[ind_Suffix]
           ,[ind_ProfSuffix]
           ,[ind_Title]
           ,[ind_MailName]
           ,[ind_Salutation1]
           ,[ind_Salutation2]
           ,[ind_pty_GUID]
           ,[ind_eth_GUID]
           ,[ind_rac_GUID]
           --,[ind_clo_GUID]
           ,[ind_occ_GUID]
           ,[ind_og1_GUID]
           ,[ind_og2_GUID]
           ,[ind_og3_GUID]
           ,[ind_Birthdate]
           ,[ind_Register]
           ,[ind_Gender]
           ,[ind_Marital]
           ,[ind_VoterId]
           ,[ind_SourceID]
           ,[ind_HashedID]
           ,[ind_Deleted]
           ,[ind_CreatedOn]
           ,[ind_CreatedBy]
           --,[ind_UpdatedOn]
           --,[ind_UpdatedBy]
           --,[ind_DeletedOn]
           --,[ind_DeletedBy]
           ,[ind_int_GUID]
           ,[ind_API_Id]
           ,[ind_Password]
           --,[ind_tracking_GUID]
           ,[ind_Inactive]
           --,[ind_GRPassword]
           )
     select 
          isnull(indguid,''), --,<ind_GUID, uniqueidentifier,>
         '',--  ,<ind_Prefix, nvarchar(10),> --(i thought this field was [indiv. title] but it was not in this database
         isnull(first_name,''),  --,<ind_FirstName, nvarchar(30),>
         '',  --,<ind_MiddleName, nvarchar(30),>
         isnull(last_name,''),  --,<ind_LastName, nvarchar(30),>
         '',  --,<ind_Suffix, nvarchar(10),>
       '',    --,<ind_ProfSuffix, nvarchar(10),>
       isnull(title,''),    --,<ind_Title, nvarchar(100),>
       isnull(mail_name,''),    --,<ind_MailName, nvarchar(50),>
        isnull(salutation,''),   --,<ind_Salutation1, nvarchar(50),>
        '',   --,<ind_Salutation2, nvarchar(50),>
       ptyguid,    --,<ind_pty_GUID, uniqueidentifier,>
       isnull(ethguid,''),    --,<ind_eth_GUID, uniqueidentifier,>
       racguid,    --,<ind_rac_GUID, uniqueidentifier,>
           --,<ind_clo_GUID, uniqueidentifier,>
       occguid,  --  ,<ind_occ_GUID, uniqueidentifier,>
       og1guid,    --,<ind_og1_GUID, uniqueidentifier,>
       og2guid,    --,<ind_og2_GUID, uniqueidentifier,>
       og3guid,    --,<ind_og3_GUID, uniqueidentifier,>
       isnull(birthdate,''), --   <ind_Birthdate, datetime,>
         isnull(register,''), -- <ind_Register, datetime,>
         ISNULL(sex,''),  --,<ind_Gender, nchar(1),>
         'Z',  --,<ind_Marital, nchar(1),>
     ISNULL(VRNUMBER,''),   --,<ind_VoterId, nvarchar(20),>
        LTRIM(IDNUMBER),   --,<ind_SourceID, nvarchar(50),>
        '',   --,<ind_HashedID, nvarchar(50),>
       0,   --,<ind_Deleted, bit,>
        getdate(),  --,<ind_CreatedOn, datetime,>
        '00000000-0000-0000-0000-000000000000',   --,<ind_CreatedBy, uniqueidentifier,>
           --,<ind_UpdatedOn, datetime,>
           --,<ind_UpdatedBy, uniqueidentifier,>
           --,<ind_DeletedOn, datetime,>
           --,<ind_DeletedBy, uniqueidentifier,>
         intguid,  --,<ind_int_GUID, uniqueidentifier,>
         '',  --,<ind_API_Id, nvarchar(10),>
         '',  --,<ind_Password, nvarchar(40),>
          -- ,<ind_tracking_GUID, uniqueidentifier,>
         case when recstatus = '3' then 1 else 0 end  --,<ind_Inactive, bit,>
          -- ,<ind_GRPassword, nvarchar(40),>)
From [P6_GrassleyCommitteeInc_20150223].[dbo].[i_Master] where enttype = 'ind'
GO
SET ANSI_WARNINGS  ON

Receiving Error:

Msg 8169, Level 16, State 2, Line 2 Conversion failed when converting from a character string to uniqueidentifier.

Upvotes: 1

Views: 1129

Answers (1)

rswords
rswords

Reputation: 31

Looking at the documentation (https://msdn.microsoft.com/en-us/library/ms187942(v=sql.110).aspx), I don't think '' is a valid uniqueidentifier, so your ISNULLs are probably causing this problem.

You will need to either remove the ISNULLs and change your schema to allow null values in those columns; or if you can't allow null values or can't change the schema, then you can put in a default value with all 0's (like you did for ind_CreatedBy) or else generate one with NEWID(), which is documented here https://msdn.microsoft.com/en-us/library/ms190348(v=sql.110).aspx.

Thus, your ind_guid line might look like one of these:

    ISNULL(ind_guid, '00000000-0000-0000-0000-000000000000')

or

    ISNULL(ind_guid, NEWID())

I don't know what your intention is, but my guess would be that using NEWID() is probably more in line with what you want.

Upvotes: 2

Related Questions