Error (8626) while inserting record into table with text field and which is the base for i

I have a problem with inserting records into table when an indexed view
is based on it.
Table has text field (without it there is no problem, but I need it).
Here is a sample code:

USE test
GO

CREATE TABLE dbo.aTable (
[id] INT NOT NULL
, [text] TEXT NOT NULL
)
GO

CREATE VIEW dbo.aView
WITH SCHEMABINDING AS
SELECT [id]
, CAST([text] AS VARCHAR(8000)) [text]
FROM dbo.aTable
GO

CREATE TRIGGER dbo.aTrigger ON dbo.aView INSTEAD OF INSERT
AS
BEGIN
INSERT INTO aTable
SELECT [id], [text]
FROM inserted
END
GO

Do the insert into aTable (also through aView).

INSERT INTO dbo.aTable VALUES (1, 'a')
INSERT INTO dbo.aView VALUES (2, 'b')

Still do not have any problem. But when I need index on view

CREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])
GO

I get following error while inserting record into aTable:

-- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4
-- 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.

Does anyone know what causes the error?ing42 (Inga.Korczowska@.gmail.com) writes:
> Do the insert into aTable (also through aView).
> INSERT INTO dbo.aTable VALUES (1, 'a')
> INSERT INTO dbo.aView VALUES (2, 'b')
> Still do not have any problem. But when I need index on view
> CREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])
> GO
> I get following error while inserting record into aTable:
> -- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4
> -- 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.
> Does anyone know what causes the error?

Did you notice the warning when you created the index:

Warning: The optimizer cannot use the index because the select list of
the view contains a non-aggregate expression.

So the index is not of much use. I guess you have hit a restriction
in SQL Server, which does not report as such in a nice way. When I
run your code in SQL 2005, I get:

Msg 1942, Level 16, State 1, Line 1
Cannot create index on view 'tempdb.dbo.aView'. It contains text, ntext,
image or xml columns.

Which is a more resolute message.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Error

Hello,

Following is the code written to call a crystal report having records for branch 3 only:

Dim appl As New CRAXDRT.Application
Dim rpt As New CRAXDRT.Report
Dim dat As CRAXDRT.Database

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set rpt = appl.OpenReport(app.path & "\password.rpt", 1)

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "dsn=invdet"
rs.Open "select * from table1 where branch=3", cn, adOpenStatic, adLockPessimistic

Set dat = rpt.Database
dat.SetDataSource rs, 3, 1
CR.ReportSource = rpt
CR.ViewReport

Error:

The Report shows all the records from "table1". It resembles the same report when I run from Crystal Reports.

i.e. it is not reporting for the above recordset set.My changes are in Bold.

'Get rid of these
'Dim appl As New CRAXDRT.Application
'Dim rpt As New CRAXDRT.Report
'Dim dat As CRAXDRT.Database

Dim Report as CrystalReport1 'This is the dsr file that you create

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set rpt = appl.OpenReport(app.path & "\password.rpt", 1)

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "dsn=invdet"
rs.Open "select * from table1 where branch=3", cn, adOpenStatic, adLockPessimistic

Report.SetDataSource rs, 3, 1
'Make sure CR is the name of the CrystalViewer on your form
CR.ReportSource = Report
CR.ViewReport|||I do not know how to create the dsr file|||What version of Crystal Reports are you using?|||I am using version 8.5|||I'm using version 8.5 Developer Edition. When I installed it, it placed all the necessary dlls on my comuter for me.

To create a dsr file, All I have to do is click 'Project', 'Add Crystal Reports 8.5'. Then follow the directions to create a dsr file.

Erratic Performance on 5,000,000 records

Hi
I have a mssql 2000 database with 5 mil records in a table and the
performance seems to change from one day to the next from 5 sec result set
returns to 30sec. If i change the index sort order or sometimes move the
position of one of the lines in the "where" statement then it improves to 5
sec only to change the next day or two to 30 sec again.
Other variables like loading on the server, db etc are constant - it;s a
test system still so no users hitting DB.
Simple things like changing the position of the "null" condition below
improved things yesterday but today it's slow (3 sec to 30sec).
eg:
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
worked well yesterday after being the other way around but now does not.
(This is for a param that could be passed thru as null or with a value)
Thanks
MikeHi,
According to information you provide i can only say that
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
is not SARGABLE. It will perform Index Scan instead of Index Seek. If you're
index will grow or get fragmented you will have more costs.
Did you run this code in Stored Procedure?
I recommend you to use Dynamic SQL or if this is a SP then use wrapper sp
according to parameter passed to sp .
Many of the programmers using this kind of code but this type of usage is
easy to write for programmers but bad for performance.
Hope this helps.
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to
5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>|||Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to
5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>|||Again this will perform index scan ..
(Assuming you have an index on DEPTCODE column)
"Saket" wrote:
[vbcol=seagreen]
> Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
> "Mike C" wrote:
>|||If column DeptCode does not contain NULLs, then you can use the
following statement. It assumes DeptCode is of datatype varchar. If is
of a different data type, then post back.
WHERE CL.DeptCode LIKE COALESCE(@.DeptCode,'%')
Hope this helps,
Gert-Jan
Mike C wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to
5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike

Erratic Performance on 5,000,000 records

Hi
I have a mssql 2000 database with 5 mil records in a table and the
performance seems to change from one day to the next from 5 sec result set
returns to 30sec. If i change the index sort order or sometimes move the
position of one of the lines in the "where" statement then it improves to 5
sec only to change the next day or two to 30 sec again.
Other variables like loading on the server, db etc are constant - it;s a
test system still so no users hitting DB.
Simple things like changing the position of the "null" condition below
improved things yesterday but today it's slow (3 sec to 30sec).
eg:
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
worked well yesterday after being the other way around but now does not.
(This is for a param that could be passed thru as null or with a value)
Thanks
Mike
Hi,
According to information you provide i can only say that
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
is not SARGABLE. It will perform Index Scan instead of Index Seek. If you're
index will grow or get fragmented you will have more costs.
Did you run this code in Stored Procedure?
I recommend you to use Dynamic SQL or if this is a SP then use wrapper sp
according to parameter passed to sp .
Many of the programmers using this kind of code but this type of usage is
easy to write for programmers but bad for performance.
Hope this helps.
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>
|||Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
"Mike C" wrote:

> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>
|||Again this will perform index scan ..
(Assuming you have an index on DEPTCODE column)
"Saket" wrote:
[vbcol=seagreen]
> Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
> "Mike C" wrote:
|||If column DeptCode does not contain NULLs, then you can use the
following statement. It assumes DeptCode is of datatype varchar. If is
of a different data type, then post back.
WHERE CL.DeptCode LIKE COALESCE(@.DeptCode,'%')
Hope this helps,
Gert-Jan
Mike C wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike

Erratic Performance on 5,000,000 records

Hi
I have a mssql 2000 database with 5 mil records in a table and the
performance seems to change from one day to the next from 5 sec result set
returns to 30sec. If i change the index sort order or sometimes move the
position of one of the lines in the "where" statement then it improves to 5
sec only to change the next day or two to 30 sec again.
Other variables like loading on the server, db etc are constant - it;s a
test system still so no users hitting DB.
Simple things like changing the position of the "null" condition below
improved things yesterday but today it's slow (3 sec to 30sec).
eg:
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
worked well yesterday after being the other way around but now does not.
(This is for a param that could be passed thru as null or with a value)
Thanks
MikeHi,
According to information you provide i can only say that
WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
is not SARGABLE. It will perform Index Scan instead of Index Seek. If you're
index will grow or get fragmented you will have more costs.
Did you run this code in Stored Procedure?
I recommend you to use Dynamic SQL or if this is a SP then use wrapper sp
according to parameter passed to sp .
Many of the programmers using this kind of code but this type of usage is
easy to write for programmers but bad for performance.
Hope this helps.
"Mike C" wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>|||Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
"Mike C" wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike
>
>|||Again this will perform index scan ..
(Assuming you have an index on DEPTCODE column)
"Saket" wrote:
> Try using isnull(CL.DEPTCODE,'') = isnull(@.DEPTCODE,'')
> "Mike C" wrote:
> > Hi
> >
> > I have a mssql 2000 database with 5 mil records in a table and the
> > performance seems to change from one day to the next from 5 sec result set
> > returns to 30sec. If i change the index sort order or sometimes move the
> > position of one of the lines in the "where" statement then it improves to 5
> > sec only to change the next day or two to 30 sec again.
> >
> > Other variables like loading on the server, db etc are constant - it;s a
> > test system still so no users hitting DB.
> >
> > Simple things like changing the position of the "null" condition below
> > improved things yesterday but today it's slow (3 sec to 30sec).
> >
> > eg:
> >
> > WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> >
> > worked well yesterday after being the other way around but now does not.
> > (This is for a param that could be passed thru as null or with a value)
> >
> > Thanks
> >
> > Mike
> >
> >
> >|||If column DeptCode does not contain NULLs, then you can use the
following statement. It assumes DeptCode is of datatype varchar. If is
of a different data type, then post back.
WHERE CL.DeptCode LIKE COALESCE(@.DeptCode,'%')
Hope this helps,
Gert-Jan
Mike C wrote:
> Hi
> I have a mssql 2000 database with 5 mil records in a table and the
> performance seems to change from one day to the next from 5 sec result set
> returns to 30sec. If i change the index sort order or sometimes move the
> position of one of the lines in the "where" statement then it improves to 5
> sec only to change the next day or two to 30 sec again.
> Other variables like loading on the server, db etc are constant - it;s a
> test system still so no users hitting DB.
> Simple things like changing the position of the "null" condition below
> improved things yesterday but today it's slow (3 sec to 30sec).
> eg:
> WHERE (@.DEPTCODE IS NULL OR CL.DEPTCODE = @.DEPTCODE)
> worked well yesterday after being the other way around but now does not.
> (This is for a param that could be passed thru as null or with a value)
> Thanks
> Mike