For some reason in Enterprise Manager for SQL Server 2000, I cannot
put the following line into a trigger:
select * into #deleted from deleted
When I hit the Apply button I get the following error:
Cannot use text, ntext, or image columns in the 'inserted' or
'deleted' tables

This seems like a weird error, since I am not actually doing anything
to the inserted or deleted tables, I am just trying to make a temp
copy.

I have another workaround but I am just curious why this happens.

Thanks,
Rebecca

Rebecca Lovelace (usagikawai@yahoo.com) writes:
> For some reason in Enterprise Manager for SQL Server 2000, I cannot
> put the following line into a trigger:
> select * into #deleted from deleted
> When I hit the Apply button I get the following error:
> Cannot use text, ntext, or image columns in the 'inserted' or
> 'deleted' tables
>
> This seems like a weird error, since I am not actually doing anything
> to the inserted or deleted tables, I am just trying to make a temp
> copy.
>
> I have another workaround but I am just curious why this happens.

The message is very clear: there is a text, image, or ntext column in
your table, and cannot access that column. And since SELECT * implies
all columns, you access that column.

On another note, I fond recently that "SELECT * INTO #deleted FROM deleted"
in a trigger can be detrimental to performance. In my case, I was
running a one-by-one processing in a long transaction, and one table
had a trigger with a SELECT INTO like this. I had about given up to
get better speed, when I found that taking out the SELECT INTO and
using "inserted" directly gave a tremendous boost,

The reason this was such a winner, was that the locks on the system
tables in tempdb needed for all these temp tables were eating
resources. I also found that SELECT INTO #temp required more locks and
resources than CREATE TABLE #temp did. But there is a better alternative:
table variables, they don't need any tempdb locks at all.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

> The message is very clear: there is a text, image, or ntext column in
> your table, and cannot access that column. And since SELECT * implies
> all columns, you access that column.

It would be clear, if there were any of those types of columns in my
table. But there aren't. It's just varchars and ints.

Does this work for you in SQL Server 2000?

It's more of a matter of curiousity at this point, I know this isn't
the best way to go, I just want to know why I can't do it.

Rebecca

Rebecca Lovelace (usagikawai@yahoo.com) writes:
>> The message is very clear: there is a text, image, or ntext column in
>> your table, and cannot access that column. And since SELECT * implies
>> all columns, you access that column.
>
> It would be clear, if there were any of those types of columns in my
> table. But there aren't. It's just varchars and ints.

That sounds very strange. I'm afraid that I don't have any answer. Is
possible for you to produce a script with a CREATE TABLE statement and
a CREATE TRIGGER that demonstrates the problem? In such case, I could
bring it up with Microsoft.

> Does this work for you in SQL Server 2000?

Yes, I have used SELECT * FROM #deleted FROM deleted in triggers with
success.

Well, success and success I have ran into two problems, but I have not
gotten that weird error message you got.

One problem I have mentioned: performance. The other problem may be
worth mentioning too. Just like you I called the temp table #deleted.
But then I had a trigger that updated another table which did the same
thing. This caused problems because when the second trigger was compiled,
#deleted already existed, but with different columns. So *if* you
this kind of thing, don't call the temp tables #inserted and #deleted,
but use some part of the table name to get a unique name.



--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp