Only Text pointers are allowed in work...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Only Text pointers are allowed in work...

Author

Topic

debianne
Starting Member

5 Posts

Posted - 2005-06-13 : 18:48:29

Hi. I have a relatively simple select query involving a view. WE are using sql2000, but I don't think the view is indexed. The view does have a field described as ntext. The ytd_detail_view is the view and account_master is a normal data table.

My query:

select tablea.*, ydv.* from

account_master tablea,
ytd_detail_view ydv

where posting_date between '07/01/04' and '06/30/05' and
(tablea.account = ydv.account) and (tablea.fund like '1%')

order by fund, gl_code, department, object, posting_date

My intention with the above query is to find all the accounts in account_master whose fund, department and object codes match a selection criteria. Then I want to get all the entries in ytd_Detail_view for those accounts that fall within a date range. I had originally tried joins, then resorted to the simplest form, because I kept getting:

Server: Msg 8626, Level 16, State 1, Line 1
Only text pointers are allowed in work tables, never text, ntext, or image columns. The query processor produced a query plan that required a text, ntext, or image column in a work table.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-13 : 19:15:37

Are you ordering by a ntext column? Cant do that.

Youll have to convert to varchar (if < 8000) or cast a substring of it as varchar (if > 8000).

ex:

order by cast(your_text_col as varchar(8000))

debianne
Starting Member

5 Posts

Posted - 2005-06-13 : 19:46:53

First, I am not sorting by the text field. Secondly, I looked at the script in analyzer. The field that I think is giving me trouble is actually defined as varchar(255) in the underlying tables.
The view is gleaned from several similar tables and all have some commonalities. The only field I can see in the view that might be considered as a ntext is the description, but as I said previously, it's defined as a varchar(255), not a blob or ntext. I'll list the view's description, then one of the tables that make up the view:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create view dbo.ytd_detail_view as
select 'W' as ledger_type, pj.account, PJ.PROJECT, pj.posting_date,
pj.amount,
'VN:' + cast(pj.vendor_id as varchar(10)) as reference_1,
'CK:' + cast(wm.check_number as varchar(8)) as reference_2,
CAST(VM.VENDOR_NAME AS VARCHAR(30)) as reference_3,
pj.warrant_id as reference_key, pj.description
from payments_journal pj left outer join
warrant_master wm on pj.warrant_id = wm.warrant_id
LEFT OUTER JOIN VENDOR_MASTER VM ON PJ.VENDOR_ID = VM.VENDOR_ID
union all
select 'E' as ledger_type, ej.account, EJ.PROJECT, ej.posting_date,
ej.amount, 'VN:' + cast(ej.vendor_id as varchar(10)) as
reference_1,
'PO:' + ej.po_number as reference_2,CAST(vm.VENDOR_NAME AS VARCHAR(30))
AS REFERENCE_3,
0 as reference_key, pl.description
from encumbrance_journal ej
left outer join po_line pl on ej.po_number = pl.po_number
and ej.po_line_number = pl.line_number
LEFT OUTER JOIN VENDOR_MASTER VM ON EJ.VENDOR_ID = VM.VENDOR_iD
union all
select 'P' as ledger_type, pj.account, PJ.PROJECT,
pj.posting_date,pj.amount, 'VN:' + cast(pj.vendor_id as varchar(10)) as
reference_1,
'INV:' + dm.invoice_number as reference_2, CAST(VM.VENDOR_NAME AS
VARCHAR(30)) AS REFERENCE_3 ,
pj.demand_id as reference_key, dm.description
from purchase_journal pj left outer join
demand_master dm on pj.demand_id = dm.demand_id
LEFT OUTER JOIN VENDOR_MASTER VM ON PJ.VENDOR_ID = VM.VENDOR_ID

union all
select 'B' as ledger_type, account, PROJECT,
posting_date,amount,'BUDGET' as reference_1, ' ' as reference_2,
' ' as reference_3, 0 as reference_key, description from budget_journal
union all
select 'J' as ledger_type, account, PROJECT, posting_date, amount,'JV:'
+ cast(sheet_number as varchar(10)) as reference_1,
'LINE: ' + cast(line_number as varchar(8)) as reference_2,
' ' as reference_3,0 as reference_key, description from general_journal
union all
select 'R' as ledger_type,account,PROJECT, posting_date, amount,'CASH:'
+ cast(sheet_number as varchar(10)) as reference_1,
'PR:' + payment_reference as reference_2, 'RR:' + receivable_reference
as reference_3,
0 as reference_key, description from receipts_journal

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TABLE [RECEIPTS_JOURNAL] (
[JOURNAL_ID] [int] IDENTITY (1, 1) NOT NULL ,
[FISCAL_YEAR] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACCOUNT] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROJECT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AMOUNT] [money] NULL ,
[POSTING_DATE] [datetime] NULL ,
[TRANSACTION_ID] [int] NULL ,
[SHEET_NUMBER] [int] NULL ,
[LINE_NUMBER] [smallint] NULL ,
[DESCRIPTION] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYMENT_REFERENCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RECEIVABLE_REFERENCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [RECEIPTS_JOURNAL_KEY] PRIMARY KEY CLUSTERED
(
[JOURNAL_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-13 : 19:51:39

quote:
Originally posted by debianne

The only field I can see in the view that might be considered as a ntext is the description

What makes you think it is "considered ntext" if it is of datatype varchar(255) ?
Are there any text/ntext columns returned in the view or the joined table? If so, explicitly list out the columns (you should do this anyways) and cast the text col as varchar (like above).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources