I have a couple of tables in a database where the primary key (stored as a UNIQUEIDENTIFIER) of one table is stored as a column in the other table as VARCHAR representation of the UNIQUEIDENTIFIER in the first table.
I added persisted computed column to the second table, so that it would store the VARCHAR value (if it was present) as a UNIQUEIDENTIFIER.
I did this by using the following formula for the persisted computed column:
SELECT CASE
WHEN isnull(ref_id, '') <> ''
THEN cast(ref_id AS UNIQUEIDENTIFIER)
ELSE NULL
END
It turns out that I don't need the persisted computed column, so I deleted it in SSMS.
The problem is that now when I want to join on the two columns, I get this error message:
Conversion failed when converting from a character string to uniqueidentifier.
This is a result of a query like this:
SELECT *
FROM mngi_referral_ r
INNER JOIN mngi_ppa_ext_ ppa ON r.seq_no = ppa.ref_id
Previous to adding the computed column, I could join on these two columns (r.seq_no is a UNIQUEIDENTIFIER and ppa.ref_id is a VARCHAR). SQL Server simply did an implicit conversion and never complained.
I can still do this in other tables in the database without a problem. It seems to only be specific to this specific column ppa.ref_id) since I added and then subsequently deleted the computed persisted column.
Does anyone have any idea why this would be the case? I would greatly appreciate any insight you can offer.
Here is the table creation script for the table in question:
CREATE TABLE [dbo].[mngi_PPA_ext_](
[enterprise_id] [char](5) NOT NULL,
[practice_id] [char](4) NOT NULL,
[person_id] [uniqueidentifier] NOT NULL,
[created_by] [int] NOT NULL,
[create_timestamp] [datetime] NOT NULL,
[create_timestamp_tz] [smallint] NULL,
[modified_by] [int] NOT NULL,
[modify_timestamp] [datetime] NOT NULL,
[modify_timestamp_tz] [smallint] NULL,
[row_timestamp] [timestamp] NOT NULL,
[seq_no] [uniqueidentifier] NOT NULL,
[txt_proc] [varchar](150) NULL,
[txt_date] [varchar](10) NULL,
[txt_ref_status] [varchar](25) NULL,
[txt_ref_source] [varchar](25) NULL,
[txt_task_status] [varchar](25) NULL,
[txt_mngi_loc] [varchar](75) NULL,
[txt_mngi_provider] [varchar](75) NULL,
[txt_new_update] [varchar](6) NULL,
[txt_referral_md] [varchar](125) NULL,
[txt_referral_link] [varchar](125) NULL,
[txt_ref_md_firstname] [varchar](75) NULL,
[txt_ref_md_lastname] [varchar](75) NULL,
[txt_ref_md_provID] [varchar](40) NULL,
[ref_id] [varchar](36) NULL,
[txt_ref_phone] [varchar](10) NULL,
[txt_interpreter] [varchar](100) NULL,
[txt_diagnosis] [varchar](500) NULL,
[txt_mngi_md_firstname] [varchar](75) NULL,
[txt_mngi_md_lastname] [varchar](75) NULL,
[txt_mngi_prov_id] [varchar](40) NULL,
[chk_intrp] [int] NULL,
[rbtn_sedation] [varchar](1) NULL,
[txt_sedation_orderingMD] [varchar](125) NULL,
[txt_ordering_MD_fname] [varchar](75) NULL,
[txt_ordering_MD_lname] [varchar](75) NULL,
[rbtn_lung] [varchar](1) NULL,
[rbtn_allergicToAnes] [varchar](1) NULL,
[txt_height_ft] [int] NULL,
[txt_height_in] [int] NULL,
[txt_weight] [int] NULL,
[txt_BMI] [decimal](16, 2) NULL,
[txt_height] [int] NULL,
[txt_temp_proc] [varchar](55) NULL,
[chk_send_task] [int] NULL,
[pt_sx] [varchar](1) NULL,
[rbtn_sendtask] [varchar](1) NULL,
[mngi_ppa] [varchar](15) NULL,
[chk_hide] [int] NULL,
[contactDetailID] [varchar](36) NULL,
[txt_procNameFull] [varchar](150) NULL,
[txt_proc_assess_needed] [varchar](3) NULL,
[txt_proc_assess_status] [varchar](15) NULL,
[txt_proc_prep] [varchar](25) NULL,
[txt_proc_seq_no] [varchar](36) NULL,
[txt_change_reason] [varchar](50) NULL,
[rbtn_changeReason] [varchar](3) NULL,
[chk_uploadContactDtl] [int] NULL,
[appt_id] [varchar](36) NULL,
[referring_clinic_id] [varchar](36) NULL,
[health_system_id] [varchar](36) NULL,
[ApplicationUserId] [varchar](36) NULL,
[chk_prepUpdatedManually] [int] NULL,
[ReferringComments] [varchar](300) NULL,
[ReferringCommentsSummary] [varchar](50) NULL,
[completedDate] [varchar](10) NULL,
[completedTime] [varchar](80) NULL,
[txt_appt_date] [varchar](10) NULL,
[txt_appt_rendering] [varchar](100) NULL,
CONSTRAINT [pk_mngi_ppa_ext_] PRIMARY KEY NONCLUSTERED
(
[seq_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [NEXTGEN_INDEX_1]
) ON [NEXTGEN_CORE]
GO
ALTER TABLE [dbo].[mngi_PPA_ext_] ADD DEFAULT (getdate()) FOR [create_timestamp]
GO
ALTER TABLE [dbo].[mngi_PPA_ext_] ADD DEFAULT (getdate()) FOR [modify_timestamp]
GO
I've tried recompiling the table with sp_recompile
to no avail.
There are not only NULL
values, but also blank values in the column, along with valid VARCHAR
representations of GUIDs.