Executing SQL of datatype text

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)

Executing SQL of datatype text

Author

Topic

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-05-29 : 20:19:54

This question is kind of similar to:
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=11000

except that I want to execute sql that is of datatype text:

i.e.

create table #temptable (
sql text not null
)
go
insert into #temptable (sql)
values ('<insert some sql over 8000 characters>')
go

declare @Sql varchar(8000)
select @Sql = Sql from #temptable
exec(@Sql)

Anyone know a trick on how to execute this sql without truncation??? (This would be easy if we could create a local variable of datatype text.)

I was thinking about breaking the text into multiple variables of datatype varchar(8000) and using exec(@SQL1 + @SQL2 + @SQLN), but would like to know if there is a more direct approach.

Edited by - jbkayne on 05/29/2002 20:37:24

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-29 : 21:15:50

Take a look here:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=10245

We didn't really solve the problem, but Ilya has some code that makes it easier to do. Truthfully, the best way to do it is to use multiple varchar variables.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-05-30 : 08:25:08

You could also write the text out to file and use osql. I don't know if this is any more friendly or not. I would use the multiple variables.

ToddV
Posting Yak Master

218 Posts

Posted - 2002-05-30 : 08:25:23

You could also write the text out to file and use osql. I don't know if this is any more friendly or not. I would use the multiple variables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources