CODEWITHSUNDEEP
sql-serverviewntext
Balaji Pooruli
Balaji Pooruli

Reputation: 250

MS SQL Server cannot call methods on ntext

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

Upvotes: 1

Views: 2445

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

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

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35583

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

Upvotes: 1

Related Questions