Insert...execute problem with text data type

Question

Insert...execute problem with text data type

pcwc66

Say Hey Kid

Points: 690
More actions
September 3, 2003 at 12:21 am

#81096

I have run the following coding in SQL SERVER 7:
create table #temp (a text, b text)
insert into #temp values('', '') -- 1 row affected
insert into #temp select '' as a, '' as b -- 1 row affected
execute("insert into #temp " + "select '' as a, '' as b") -- 1 row affected
insert into #temp execute("select '' as a, '' as b") -- 0 row affected
insert into #temp execute("select ' ' as a, '' as b") -- 0 row affected
insert into #temp execute("select ' ' as a, ' ' as b") -- 1 row affected
insert into #temp execute("select '' as a, ' ' as b") -- Error (Note: Be careful about this. I have experienced that the query may keep running and the query cannot be cancelled. I need to stop the sql server service.)
However, running any of the insert statements in SQL Server 2000 will have 1 row affected. So far, I cannot explain this problem but I believe this is related to both text data type and using insert...execute. I have a feeling that it is a bug in SQL Server 7 regardless of what service pack is applied.
Also, what is the difference between the following 2 statements since they behave differently in SQL Server 7?:
execute("insert into #temp " + "select '' as a, '' as b") -- 1 row affected
insert into #temp execute("select '' as a, '' as b") -- 0 row affected

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

AnzioBake SSCarpal Tunnel Points: 4135 More actions · Answer 1

I suspect you have a issue with quoted identifiers in the last two statements. You may want to set quoted_identifiers off so double quotes(") are not seen as delimiting a DB object

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 2

Have a look at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/reskit/sql2000/part3/c1161.asp

Read in SQL Server Books Online about TEXT, READTEXT and WRITETEXT.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 3

AnzioBake is correct.

BOL 2000 states, When SET QUOTED_IDENTIFIER is ON, all strings delimited by double quotation marks are interpreted as object identifiers



create table Temp (a text, b text) 

SET QUOTED_IDENTIFIER off

create table #temp (a text, b text) 

insert into #temp values('', '') -- 1 row affected 

insert into #temp select '' as a, '' as b -- 1 row affected 

execute("insert into #temp " + "select '' as a, '' as b") -- 1 row affected 

insert into #temp execute("select '' as a, '' as b") -- 0 row affected 

insert into #temp execute("select ' ' as a, '' as b") -- 0 row affected 

insert into #temp execute("select ' ' as a, ' ' as b") -- 1 row affected 

insert into #temp execute("select '' as a, ' ' as b") -- Error

drop table #temp

Also have a look at

sp_tableoption N'Temp', 'text in row', 'Off'