MS SQL Server cannot call methods on ntext

Question

I am trying to create a view in MS SQL server from a table. The table name is Account_Plan and I am trying to create a view as Account_Plan_vw. While executing the DDL to create the view, I am getting the error as shown below.

Msg 258, Level 15, State 1, Procedure Account_Plan_vw, Line 56
Cannot call methods on ntext

Msg 207, Level 16, State 1, Procedure Account_Plan_vw, Line 22
Invalid column name 'How_the_CU_will_achieve_these_objective2__c'.

The error message shows the column 'How_the_CU_will_achieve_these_objective2__c' as invalid. However, this is a valid column in the Account_Plan table of ntext type.

Can someone help? I just removed the extra columns from the Create view statement.

CREATE VIEW [dbo].[Account_Plan_vw]
AS
SELECT   
 Results_1.Account__c
,Results_1.How_the_CU_will_achieve_these_objectives__c
,Results_1.How_the_CU_will_achieve_these_objective2__c
FROM 
(
  SELECT ROW_NUMBER() OVER (PARTITION BY apc1.Account__c ORDER BY apc1.Year__c DESC, apc1.CreatedDate DESC) AS RN_1
    ,apc1.Account__c
,apc1.How_the_CU_will_achieve_these_objectives__c
,apc1.How_the_CU_will_achieve_these_objective2__c
  FROM Account_Plan apc1
  INNER JOIN RecordType rtp1
  ON apc1.RecordTypeId=rtp1.[Id]
  AND rtp1.DeveloperName = 'Account_Plan' 
  INNER JOIN Account acc1
  ON acc1.[Id] = apc1.Account__c
  WHERE apc1.Year__c <= YEAR(GETDATE())
  ) AS Results_1
  WHERE RN_1 = 1

I am not able to add the code here. I am getting an error message saying comment is long. — Balaji Pooruli, Nov 07 '17 at 22:46
[Edit](https://stackoverflow.com/posts/47168524/edit) your question and add your code there. — Ilyes, Nov 07 '17 at 22:47
What happens if you create your view using the code posted (i.e. without the extra columns)? — Alex, Nov 07 '17 at 23:17
Did not try that. Will try that tomorrow morning and see what happens. — Balaji Pooruli, Nov 07 '17 at 23:24
`ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) — marc_s, Nov 08 '17 at 06:03

score 1 · Answer 1 · answered Nov 08 '17 at 01:20

1

NTEXT is deprecated, convert it to NVARCHAR(MAX) instead

see: ntext, text, and image (Transact-SQL)

You should consider altering the table not just casting in the view, but:

CREATE VIEW [dbo].[Account_Plan_vw]
AS
SELECT
      results_1.Account__c
    , results_1.How_the_CU_will_achieve_these_objectives__c
    , results_1.How_the_CU_will_achieve_these_objective2__c
FROM (
      SELECT
            ROW_NUMBER() OVER (PARTITION BY apc1.Account__c ORDER BY apc1.Year__c DESC, apc1.CreatedDate DESC) AS rn_1
          , apc1.Account__c
          , apc1.How_the_CU_will_achieve_these_objectives__c
          , cast(apc1.How_the_CU_will_achieve_these_objective2__c as nvarchar(max)) as How_the_CU_will_achieve_these_objective2__c
      FROM Account_Plan apc1
      INNER JOIN RecordType rtp1 ON apc1.RecordTypeId = rtp1.[Id]
            AND rtp1.DeveloperName = 'Account_Plan'
      INNER JOIN Account acc1 ON acc1.[Id] = apc1.Account__c
      WHERE apc1.Year__c <= YEAR(GETDATE())
) AS results_1
WHERE RN_1 = 1

answered Nov 08 '17 at 01:20

Paul Maxwell

33,002
3
32
51

I fail to see how this answers the initial question. If you search the internet for this question, you will find examples of a similar question about `VARCHAR`. The problem being seems to be an obscure syntax error. – Alex Nov 08 '17 at 06:33
@Alex but you will also find that the solutions to such questions are to cast/convert to supported data types (n)varchar instead of (n)text - do refer to the MS warning about the deprecated types. **Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.** – Paul Maxwell Nov 08 '17 at 06:41
`NTEXT` is still a supported data type, hence should not be a problem on its own. I would also like to see examples you are referencing where casting `NTEXT` to `VARCHAR( MAX )` solved the problem. I have not found any, except some dealing with `NTEXT` to `XML` conversion. – Alex Nov 08 '17 at 07:14
@Alex regret that I am not doing further research on this. I don't understand why you consider them to be supported data types. Take it up with Microsoft Support perhaps? The reference I provided is from Microsoft. – Paul Maxwell Nov 08 '17 at 07:18
They are currently supported because deprecated means supported for now but its use is discouraged. My initial comment was about your answer having nothing to do with the OP's error. – Alex Nov 08 '17 at 08:28
@Alex thank you for the semantic advice. If you can locate any solution to that error message that does not involve **casting to the recommend equivalent data type** please do provide that information, or perhaps add your own answer. – Paul Maxwell Nov 08 '17 at 08:36
1

Thanks everyone for the responses. Unfortunately I do not have the liberty of altering the table Account_Plan as it is getting replicated from Salesforce CRM using the SF_Replicate command using DBAMP connector. The Long Text Area fields in Salesforce object Account_Plan__c is automatically mapped to NTEXT fields. I will try the cast solution while creating the view and see whether it helps or not. FYI, the long text area in Salesforce can handle upto 32,768 characters, but I guess ntext can handle only upto 8000 characters. So, there might be some truncation issue. – Balaji Pooruli Nov 08 '17 at 14:50
1

Thanks for the responses. I figured the issue and it was somewhat cryptic to find. The salesforce object had a field as last_peer_review_date__c for which no permissions were given to anybody. As a result, DBAMP user was not able to see the field and hence missed to create this field in SQL server when I used the SF_Replicate command. The create view SQL was created by me couple of weeks ago and it did work at that time. Now, when I used the same SQL, it failed because the SQL had the last_peer_review_date field, but the Account_Plan table does not. – Balaji Pooruli Nov 08 '17 at 15:43
1

To fix the issue, I had to do the following. 1) Change permissions on the field last_peer_review_date in Salesforce 2) Drop the table Account_Plan in SQL server 3) Replicate the Account_Plan table using SF_Replicate and 4) Execute the create view statement again. It worked fine now. – Balaji Pooruli Nov 08 '17 at 15:44
1

You should add that as an answer and accept it. Then there is a clear record of the csuse. Thanks. – Paul Maxwell Nov 08 '17 at 18:10

score 1 · Answer 2 · answered Nov 15 '17 at 04:13

the issue and it was somewhat cryptic to find. The salesforce object had a field as last_peer_review_date__c for which no permissions were given to anybody. As a result, DBAMP user was not able to see the field and hence missed to create this field in SQL server when I used the SF_Replicate command. The create view SQL was created by me couple of weeks ago and it did work at that time. Now, when I used the same SQL, it failed because the SQL had the last_peer_review_date field, but the Account_Plan table does not.

Balaji Pooruli

MS SQL Server cannot call methods on ntext

2 Answers2