Link to home
Start Free TrialLog in
Avatar of dpwhite
dpwhite

asked on

How can the contents of a Text datatype field be changed from 'NULL' to blank?

I have a 3rd party application using SQL 2005. One table (PHYSICIANS) contains a column ( NOTES) with a TEXT datatype that was implicitly set to 'NULL' at the point of initial table INSERT, i.e., the INSERTed column was empty. The presence of 'NULL' in this column is causing a pop-up window to appear in the application that must be acknowledged by the users.  I need to replace the 'NULL' with a blank field.  I have tried using UPDATETEXT and WRITETEXT, but these require a pointer. Since the text column  is not initialized by SQL Server when 'NULL' values are implicitly INSERTED, no pointer can be obtained for such nulls.  How can I get rid of these 'NULL's and and replace them with a blank (empty) field?
USE FAC;
GO
ALTER DATABASE FAC SET RECOVERY SIMPLE
GO
DECLARE @PTRVAL BINARY(16)
SELECT @PTRVAL =TEXTPTR(NOTES)
FROM PHYSICIANS$ NOTES
WHERE NOTES IS NULL
WRITETEXT PHYSICIANS$.NOTES @PTRVAL '        '

Open in new window

Avatar of dbaSQL
dbaSQL
Flag of United States of America image

I'm not sure i follow you regarding the pointer, but if 'NULL' was written, then

update physicians
set notes = ''
where notes = 'NULL'

Avatar of dpwhite
dpwhite

ASKER

dbaSQL - thanks for your reply. I attempted your solution yesterday, but I get this error:

Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.

I replaced "where notes = 'NULL'  "  with  " where notes IS NULL "  and it executes successfully, but with no impact on replacing the 'NULL' in the column, i.e., " (0 row(s) affected)"
Your second solution does work. I tested it on a small table.

update table2
SET notes = ''
WHERE notes is null

How many records are returned with this query?

SELECT *
FROM table2
WHERE notes is null
ASKER CERTIFIED SOLUTION
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
per BOL:

CONTAINS
Is a predicate used to search columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches.

A. Use CONTAINS with <simple_term>
This example finds all products with a price of $15.00 that contain the word "bottles."

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
   AND CONTAINS(QuantityPerUnit, 'bottles')
GO

B. Use CONTAINS and phrase in <simple_term>
This example returns all products that contain either the phrase "sasquatch ale" or "steeleye stout."

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
GO

C. Use CONTAINS with <prefix_term>
This example returns all product names with at least one word starting with the prefix choc in the ProductName column.

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "choc*" ')
GO

Avatar of dpwhite

ASKER

ptjcb - over 3300 rows are returned with 'NULL' using your select statement.  It's not working here.
I'm not sure how much data you've got in there, but it may be to your advantage to get a count on the diff notes present.

select distinct(notes),count(*)
from physicians
group by notes

then we can see exactly what the value is, and how many we've got to work with
Avatar of dpwhite

ASKER

dbaSQL - I tried this systax and got the following error. Did I get your syntax correct?
USE Fac
UPDATE Physicians
SET NOTES = ''
WHERE CONTAINS(NOTES, '"NULL" ')

Msg 7601, Level 16, State 2, Line 2
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'physicians$' because it is not full-text indexed.
Avatar of dpwhite

ASKER

dbaSQL - I ran your script and got the following error.
select distinct(notes),count(*)
from physicians
group by notes

Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try this - converts the column type to varchar(max)

SELECT CAST(notes as varchar(max)), count(*)
FROM physicians
GROUP BY CAST(notes as varchar(max))

Open in new window

Avatar of dpwhite

ASKER

ptjcb - I don't see any constraints.  Attached is the table build script.
USE [Fac]
GO
/****** Object:  Table [dbo].[Physicians]    Script Date: 03/05/2008 08:49:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Physicians](
	[PhNPI] [varchar](15) NOT NULL,
	[PhLName] [varchar](16) NULL,
	[PhFName] [varchar](12) NULL,
	[DEANo] [varchar](10) NULL,
	[DEASuffix] [varchar](4) NULL,
	[LicenseNo] [varchar](15) NULL,
	[UserID1] [varchar](15) NULL,
	[UserID2] [varchar](15) NULL,
	[PhType] [varchar](6) NULL,
	[PhStreet1] [varchar](30) NULL,
	[PhStreet2] [varchar](30) NULL,
	[PhCity] [varchar](20) NULL,
	[PhState] [varchar](2) NULL,
	[PhZip] [varchar](10) NULL,
	[PhPhone1] [varchar](14) NULL,
	[Extension1] [varchar](10) NULL,
	[PhPhone2] [varchar](14) NULL,
	[Extension2] [varchar](10) NULL,
	[Email] [varchar](50) NULL,
	[Notes] [text] NULL,
	[UserID1Qual] [varchar](2) NULL,
	[UserID2Qual] [varchar](2) NULL,
	[PhFax] [varchar](14) NULL,
	[SLXDOCTORACCOUNTID] [varchar](12) NULL,
 CONSTRAINT [PK_Physicians] PRIMARY KEY CLUSTERED 
(
	[PhNPI] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Open in new window

ok.  CONTAINS is specific to a column that is registered for full-text.  yours is not.  hence, the msg 7601

try this:

select isnull(notes,'')
Avatar of dpwhite

ASKER

ptjcb - ran your CAST statement with the following result:

row     (no column name)                             (no column name)
1         No CII's                                               1
2         NULL                                                  3365
3         Has two offices, one in Phoenix...    1

Rows 1 & 3 are valid data entries.
Avatar of dpwhite

ASKER

dbaSQL - select isnull(notes,'') statement with the following result:
USE Fac
select isnull(notes,'')
from physicians

row    (no column name)
1         NULL
2         NULL
...
15      Has two offices, one in Phoenix...
...

Continues to display individual rows with NULL through the end of the table.
Try this...
DECLARE @ptrval binary(16)
UPDATE physicians SET notes = '' WHERE notes IS NULL
SELECT @ptrval = TEXTPTR(notes) FROM physicians 
UPDATETEXT physicians.notes @ptrval 0 0 ''
GO

Open in new window

Avatar of dpwhite

ASKER

ptjcb - your code executes successfully, but results in (0 row(s) affected) with no change to the table
USE Fac
DECLARE @ptrval binary(16)
UPDATE physicians$ SET notes = '' WHERE notes IS NULL
SELECT @ptrval = TEXTPTR(notes) FROM physicians$
UPDATETEXT physicians$.notes @ptrval 0 0 ''
GO

(0 row(s) affected)
Why is the table named physicians but you are using physicians$?
Avatar of dpwhite

ASKER

ptjcb - the production table is names physicians, I am testing this against an identical backup table called physicians$  Sorry for the confusion.
Cool. I hoped that you were testing this first.

You ran the CAST script against physicians$? (Just to be sure).


BEGIN TRY
DECLARE @ptrval binary(16)
UPDATE physicians SET notes = '' WHERE notes IS NULL
SELECT @ptrval = TEXTPTR(notes) FROM physicians --WHERE t2id = 4
UPDATETEXT physicians.notes @ptrval 0 0 ''
END TRY
BEGIN CATCH
SELECT 
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH

Open in new window

Avatar of dpwhite

ASKER

ptjcb - Yep, everything today has actually been tested against the physicians$ backup table, not in the physicians production table.  Your new code again executes successfully, but results in (0 row(s) affected) with no change to the table.

USE Fac
BEGIN TRY
DECLARE @ptrval binary(16)
UPDATE physicians$ SET notes = '' WHERE notes IS NULL
SELECT @ptrval = TEXTPTR(notes) FROM physicians$ --WHERE t2id = 4
UPDATETEXT physicians$.notes @ptrval 0 0 ''
END TRY
BEGIN CATCH
SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH

(0 row(s) affected)
This is odd.

I assume that you have the necessary rights to the test table and the test column to update it.

I have tested  the code against a small (10 record) table on my local computer and the notes column was set to empty string without issue. It fails on your database but without sending an error message.

Could you send the table structure of the physicians$ table? To compare it to production.

Just to test, could you create a small table with a text column that defaults to NULL and then add 10 records to it.
Then run the last code.

I am trying to figure out if the issue is with your database configuration or the table. The following code lists the constraints on the database.
Use FAC
go
SELECT OBJECT_NAME(object_id) as NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO

Open in new window

By the way, I assume that you are running each of these queries in SQL Server Management Studio.
Avatar of dpwhite

ASKER

ptjcb - yes, all queries are being run in SQL Server Management Studio.  I will build a small table and test it in a minute and let you know the results. Here are the results from your constraints query. The code snipet below is the Physicians$ table build info.

1      PK_Facilities      dbo      Facilities      PRIMARY_KEY_CONSTRAINT
2      FK_NursingStations_Facilities      dbo      NursingStations      FOREIGN_KEY_CONSTRAINT
3      FK_CareLvlCds_Facilities      dbo      CareLvlCds      FOREIGN_KEY_CONSTRAINT
4      FK_FacFormulary_Facilities      dbo      FacFormulary      FOREIGN_KEY_CONSTRAINT
5      FK_LitCatCds_Facilities      dbo      LitCatCds      FOREIGN_KEY_CONSTRAINT
6      PK_LitIdAdminTimes      dbo      LitIdAdminTimes      PRIMARY_KEY_CONSTRAINT
7      PK_PlanBenefitManagers      dbo      PlanBenefitManagers      PRIMARY_KEY_CONSTRAINT
8      PK_GlobalLitOrdAdminTimes      dbo      GlobalLitOrdAdminTimes      PRIMARY_KEY_CONSTRAINT
9      DF_Facilities_BlockDirectionsOnRxLabel      dbo      Facilities      DEFAULT_CONSTRAINT
10      DF_Facilities_EnablePerDiem      dbo      Facilities      DEFAULT_CONSTRAINT
11      PK_CareLvlCds      dbo      CareLvlCds      PRIMARY_KEY_CONSTRAINT
12      DF_FacilityTaxStatus_Taxable      dbo      FacilityTaxStatus      DEFAULT_CONSTRAINT
13      PK_FacilityTaxStatus      dbo      FacilityTaxStatus      PRIMARY_KEY_CONSTRAINT
14      PK_InsPharmECS      dbo      InsPlanPharmECS      PRIMARY_KEY_CONSTRAINT
15      DF__Facilitie__MOTEn__151B244E      dbo      Facilities      DEFAULT_CONSTRAINT
16      PK_InsPlans      dbo      InsPlans      PRIMARY_KEY_CONSTRAINT
17      PK_LitCatCds      dbo      LitCatCds      PRIMARY_KEY_CONSTRAINT
18      PK_CareLvlLocations      dbo      CareLvlLocations      PRIMARY_KEY_CONSTRAINT
19      PK_NursingStations      dbo      NursingStations      PRIMARY_KEY_CONSTRAINT
20      DF_Facilities_PerDiemFormularyType      dbo      Facilities      DEFAULT_CONSTRAINT
21      PK_Physicians      dbo      Physicians      PRIMARY_KEY_CONSTRAINT
22      DF_Facilities_AllOTCsAreHouseStock      dbo      Facilities      DEFAULT_CONSTRAINT
23      PK_AdminScheds      dbo      AdminScheds      PRIMARY_KEY_CONSTRAINT
24      PK_FacAdminTimes      dbo      FacAdminTimes      PRIMARY_KEY_CONSTRAINT
25      PK_FacCustomerNos      dbo      FacCustomerNos      PRIMARY_KEY_CONSTRAINT
26      PK_FacFormulary      dbo      FacFormulary      PRIMARY_KEY_CONSTRAINT
27      PK_FacGroupDefs      dbo      FacGroupDefs      PRIMARY_KEY_CONSTRAINT
28      PK_FacilityCustomFields      dbo      FacilityCustomFields      PRIMARY_KEY_CONSTRAINT
29      PK_FacGroups      dbo      FacGroups      PRIMARY_KEY_CONSTRAINT
30      PK_FacTypeDefs      dbo      FacTypeDefs      PRIMARY_KEY_CONSTRAINT
31      PK_PhysicianCustomFields      dbo      PhysicianCustomFields      PRIMARY_KEY_CONSTRAINT
32      DF_LitCatCds_OnNursingFlow      dbo      LitCatCds      DEFAULT_CONSTRAINT
33      PK_FacPackageTypes      dbo      FacPackageTypes      PRIMARY_KEY_CONSTRAINT
34      PK_NursingStationPackageTypes      dbo      NursingStationPackageTypes      PRIMARY_KEY_CONSTRAINT
35      DF_Facilities_AlwaysAllowRefills      dbo      Facilities      DEFAULT_CONSTRAINT
36      PK_PerDiemRates      dbo      PerDiemRates      PRIMARY_KEY_CONSTRAINT
37      PK_Deliveries      dbo      Deliveries      PRIMARY_KEY_CONSTRAINT
38      PK_FacDeliverySched      dbo      FacDeliverySched      PRIMARY_KEY_CONSTRAINT
39      PK_NsDeliverySched      dbo      NsDeliverySched      PRIMARY_KEY_CONSTRAINT
40      DF_PhTheraChange_TheraType      dbo      PhTheraChange      DEFAULT_CONSTRAINT
41      DF_PhTheraChange_ReplaceNDC      dbo      PhTheraChange      DEFAULT_CONSTRAINT
42      PK_PhTheraChange      dbo      PhTheraChange      PRIMARY_KEY_CONSTRAINT
43      PK_GlobalLitOrds      dbo      GlobalLitOrds      PRIMARY_KEY_CONSTRAINT
44      DF_DefaultMARGroups_DefType      dbo      DefaultMarGroups      DEFAULT_CONSTRAINT
45      PK_InsPlanCustomerNos      dbo      InsPlanCustomerNos      PRIMARY_KEY_CONSTRAINT
46      DF_DefaultMARGroups_ReplaceNDC      dbo      DefaultMarGroups      DEFAULT_CONSTRAINT
47      PK_InsPlanECSCds      dbo      InsPlanECSCds      PRIMARY_KEY_CONSTRAINT
48      PK_DefaultMARGroups      dbo      DefaultMarGroups      PRIMARY_KEY_CONSTRAINT
49      DF_InsPlanFormulary_UseForRx      dbo      InsPlanFormulary      DEFAULT_CONSTRAINT
50      DF_FacHouseStock_HSType      dbo      FacHouseStock      DEFAULT_CONSTRAINT
51      PK_InsPlanFormulary      dbo      InsPlanFormulary      PRIMARY_KEY_CONSTRAINT
52      DF_FacHouseStock_ReplaceNDC      dbo      FacHouseStock      DEFAULT_CONSTRAINT
53      DF_InsPlans_ExclFormulary      dbo      InsPlans      DEFAULT_CONSTRAINT
54      PK_FacHouseStock      dbo      FacHouseStock      PRIMARY_KEY_CONSTRAINT
55      DF_InsPlans_EcsMode      dbo      InsPlans      DEFAULT_CONSTRAINT
56      DF_InsPlans_ForceMetDecQty      dbo      InsPlans      DEFAULT_CONSTRAINT
57      DF_InsPlans_NoOTC      dbo      InsPlans      DEFAULT_CONSTRAINT
58      PK__sysdiagrams__40F9A68C      dbo      sysdiagrams      PRIMARY_KEY_CONSTRAINT
59      DF_InsPlans_PrintUandC      dbo      InsPlans      DEFAULT_CONSTRAINT
60      UK_principal_name      dbo      sysdiagrams      UNIQUE_CONSTRAINT
61      PK_InvoiceGrps      dbo      InvoiceGrps      PRIMARY_KEY_CONSTRAINT
62      DF_LitCatCds_OnMedSht      dbo      LitCatCds      DEFAULT_CONSTRAINT
63      DF_LitCatCds_OnPhysOrd      dbo      LitCatCds      DEFAULT_CONSTRAINT
64      DF_LitCatCds_onTreatmentRec      dbo      LitCatCds      DEFAULT_CONSTRAINT
65      DF_LitCatCds_OnSigMedsSht      dbo      LitCatCds      DEFAULT_CONSTRAINT
66      PK_LitIDs      dbo      LitIDs      PRIMARY_KEY_CONSTRAINT
67      DF_MARGroups_PageBreakBefore      dbo      MARGroups      DEFAULT_CONSTRAINT
68      PK_MARGroups      dbo      MARGroups      PRIMARY_KEY_CONSTRAINT
69      PK_MARMessages      dbo      MARMessages      PRIMARY_KEY_CONSTRAINT
70      PK_PatientStatusNames      dbo      PatientStatusNames      PRIMARY_KEY_CONSTRAINT
71      PK_NursingStationAdminTimes      dbo      NursingStationAdminTimes      PRIMARY_KEY_CONSTRAINT
72      PK_PhysicianTypes      dbo      PhysicianTypes      PRIMARY_KEY_CONSTRAINT
73      PK_FacilityLinkAlerts      dbo      FacilityLinkAlerts      PRIMARY_KEY_CONSTRAINT
74      FK_InsPlanECSCds_InsPlans      dbo      InsPlanECSCds      FOREIGN_KEY_CONSTRAINT
75      FK_InsPlanFormulary_InsPlans      dbo      InsPlanFormulary      FOREIGN_KEY_CONSTRAINT
76      PK_DefInvoiceGrps      dbo      DefInvoiceGrps      PRIMARY_KEY_CONSTRAINT
77      FK_InsPlanPharmECS_InsPlans      dbo      InsPlanPharmECS      FOREIGN_KEY_CONSTRAINT
78      PK_FacilityRooms      dbo      FacilityRooms      PRIMARY_KEY_CONSTRAINT
79      FK_LitIDs_LitCatCds      dbo      LitIDs      FOREIGN_KEY_CONSTRAINT
80      PK_FacilityBeds      dbo      FacilityBeds      PRIMARY_KEY_CONSTRAINT
81      DF_Facilities_AllowCredits      dbo      Facilities      DEFAULT_CONSTRAINT
82      DF__PerDiemFo__GPIDr__57DD0BE4      dbo      PerDiemFormulary      DEFAULT_CONSTRAINT
83      DF__PerDiemFo__GPIDr__58D1301D      dbo      PerDiemFormulary      DEFAULT_CONSTRAINT
84      DF__PerDiemFo__GPIDr__59C55456      dbo      PerDiemFormulary      DEFAULT_CONSTRAINT
85      DF__PerDiemFo__GPIDr__5AB9788F      dbo      PerDiemFormulary      DEFAULT_CONSTRAINT
86      DF__PerDiemFo__Dosag__5BAD9CC8      dbo      PerDiemFormulary      DEFAULT_CONSTRAINT
87      DF__PerDiemFo__Stren__5CA1C101      dbo      PerDiemFormulary      DEFAULT_CONSTRAINT
88      DF__PerDiemFo__Produ__5D95E53A      dbo      PerDiemFormulary      DEFAULT_CONSTRAINT
89      PK_PerDiemFormulary      dbo      PerDiemFormulary      PRIMARY_KEY_CONSTRAINT
90      PK_StandingLitOrds      dbo      StandingLitOrds      PRIMARY_KEY_CONSTRAINT
91      PK_StandingLitOrdAdminTimes      dbo      StandingLitOrdAdminTimes      PRIMARY_KEY_CONSTRAINT
92      PK_FacBlockedWLblCds      dbo      FacBlockedWLblCds      PRIMARY_KEY_CONSTRAINT
93      PK_FacContacts      dbo      FacContacts      PRIMARY_KEY_CONSTRAINT
94      DF_Facilities_FacName      dbo      Facilities      DEFAULT_CONSTRAINT
95      DF_Facilities_FacStreet1      dbo      Facilities      DEFAULT_CONSTRAINT
96      DF_Facilities_FacStreet2      dbo      Facilities      DEFAULT_CONSTRAINT
97      DF_Facilities_FacCity      dbo      Facilities      DEFAULT_CONSTRAINT
98      DF_Facilities_FacState      dbo      Facilities      DEFAULT_CONSTRAINT
99      DF_Facilities_FacZip      dbo      Facilities      DEFAULT_CONSTRAINT
100      DF_Facilities_FacAdminPhone      dbo      Facilities      DEFAULT_CONSTRAINT
101      DF_Facilities_ExclFormulary      dbo      Facilities      DEFAULT_CONSTRAINT
102      DF_Facilities_PdeType1      dbo      Facilities      DEFAULT_CONSTRAINT
103      DF_Facilities_PdeType2      dbo      Facilities      DEFAULT_CONSTRAINT
104      DF_Facilities_PdeType3      dbo      Facilities      DEFAULT_CONSTRAINT
105      DF_Facilities_PdeType4      dbo      Facilities      DEFAULT_CONSTRAINT
106      DF_Facilities_PdeType5      dbo      Facilities      DEFAULT_CONSTRAINT
107      DF_Facilities_PdeType6      dbo      Facilities      DEFAULT_CONSTRAINT
108      DF_Facilities_PdeType7      dbo      Facilities      DEFAULT_CONSTRAINT
109      DF_Facilities_PdeType8      dbo      Facilities      DEFAULT_CONSTRAINT
110      DF_Facilities_RetFacility      dbo      Facilities      DEFAULT_CONSTRAINT
111      DF_Facilities_DDIFood      dbo      Facilities      DEFAULT_CONSTRAINT
112      DF_Facilities_DDIAlcohol      dbo      Facilities      DEFAULT_CONSTRAINT
113      DF_Facilities_UnitDose      dbo      Facilities      DEFAULT_CONSTRAINT
114      DF_Facilities_TreatmentsOnMar      dbo      Facilities      DEFAULT_CONSTRAINT
USE [Fac]
GO
/****** Object:  Table [dbo].[Physicians$]    Script Date: 03/05/2008 14:22:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Physicians$](
	[PhNPI] [varchar](15) NOT NULL,
	[PhLName] [varchar](16) NULL,
	[PhFName] [varchar](12) NULL,
	[DEANo] [varchar](10) NULL,
	[DEASuffix] [varchar](4) NULL,
	[LicenseNo] [varchar](15) NULL,
	[UserID1] [varchar](15) NULL,
	[UserID2] [varchar](15) NULL,
	[PhType] [varchar](6) NULL,
	[PhStreet1] [varchar](30) NULL,
	[PhStreet2] [varchar](30) NULL,
	[PhCity] [varchar](20) NULL,
	[PhState] [varchar](2) NULL,
	[PhZip] [varchar](10) NULL,
	[PhPhone1] [varchar](14) NULL,
	[Extension1] [varchar](10) NULL,
	[PhPhone2] [varchar](14) NULL,
	[Extension2] [varchar](10) NULL,
	[Email] [varchar](50) NULL,
	[Notes] [text] NULL,
	[UserID1Qual] [varchar](2) NULL,
	[UserID2Qual] [varchar](2) NULL,
	[PhFax] [varchar](14) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Open in new window

Is the database with physicians$ set to SQL 2000 compatibility instead of 2005? Comparing the table scripts the physcians$ is missing

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

These are SQL 2005 properties that you can change. Are both databases 2005?

It also does not have a primary key.

The list of constraints looks OK. The only one for physicians is for the primary key.

OK, I'm officially tired. Yes, both tables are 2005.
Avatar of dpwhite

ASKER

ptjcb - I created a simple two column databse names TEST the attached code snippet. I loaded the table and re-ran your script. The result was the same, i.e., the code ran successfully, but no records were affected and no changes were made to the table.
USE Fac
BEGIN TRY
DECLARE @ptrval binary(16)
UPDATE TEST SET notes = '' WHERE notes IS NULL
SELECT @ptrval = TEXTPTR(notes) FROM TEST --WHERE t2id = 4
UPDATETEXT TEST.notes @ptrval 0 0 ''
END TRY
BEGIN CATCH
SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH

(0 row(s) affected)
USE [Fac]
GO
/****** Object:  Table [dbo].[Test]    Script Date: 03/05/2008 15:03:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Test](
	[PhLName] [varchar](16) NULL,
	[Notes] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Open in new window

Sorry, it has to be a setting on your server or database or permissions or something.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup Zone:
Split the points between ptjcb and dbaSQL.

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

matrix_aash Experts Exchange Cleanup Volunteer