Executing long texts (ntext) - SQL Server Forums

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 long texts (ntext)

Author

Topic

tomino79
Starting Member

13 Posts

Posted - 2004-10-21 : 13:16:31

Hi,

I am new here, and I have not so much experiences with writing queries. I have a table where in the rows are the definitions of stored procedures. I have to execute them, but the procedures are long, so they are stored as ntext. But I can't find out how to execute ntext...

The table has a structure: SCHEMA_ROUTINES
( ROUTINE_NAME nvarchar(128) -- name of the sp
ROUTINE_DEFINITION ntext )

I have a clean database (no stored procedures) and this table. I need to create these procedures from this table.
Please If you can help me, post a reply.
Many many thanks

Tomas.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 09:18:37

EDIT 1
sorry wrong topic...

EDIT 2:

if i understand correctly you have sprocs scripted in rows??
so why not just use
select col from table
and simply execute the script like that?
if it spans over few columns just do
exec (@col1 + @col2 + @coln)
where @col1, @col2, @coln are your column values....

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-22 : 10:00:39

IIUC the column is a TEXT, not a VARCHAR ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 10:30:29

IIUC??? what's that?

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-22 : 23:59:22

Sorry, If I Understand Correctly - I probably made it up ... ;-)

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-23 : 06:56:48

but that doesn't matter, does it?
he can just do copy paste and that's it, no?

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-23 : 09:03:48

I think tomino79 would like to do something like


DECLARE CURSOR CUR_FOO AS SELECT MyTextColumn FROM MyTable

LOOP
   FETCH @MyText FROM CUR_FOO

   EXEC (@MyText)

which I assume is a problem with a TEXT column

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-23 : 09:30:03

most likely....

Go with the flow & have fun! Else fight the flow

tomino79
Starting Member

13 Posts

Posted - 2004-10-24 : 11:04:46

Yes exactly, I solved this problem using ADO.
ADOConnection.Open
ADOQuery : select spdef from sproutines
for each DataSet I execute the ADOCommand with the sp definition.
But I was dissapointed that I can't solve this problem through TSQL.

Thanks Kristen.

quote:
Originally posted by Kristen

I think tomino79 would like to do something like

DECLARE CURSOR CUR_FOO AS SELECT MyTextColumn FROM MyTable

LOOP
FETCH @MyText FROM CUR_FOO

EXEC (@MyText)

which I assume is a problem with a TEXT column

Kristen

Kristen
Test

22859 Posts

Posted - 2004-10-24 : 11:07:29

Well, you could solve it using T-SQL, but you'd have to do all that nasty GetChunk stuff to read the TEXT data into a number of varchar(8000) [or is that nvarchar(4000)?) variables ... and then EXEC the composite.

FWIW We do stuff like this by just "outputting" all the text to be executed, and then cuting & pasting that to QA

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-05 : 05:00:17

There is a proc that is handy for executing large text:
sp_execresultset

However, you will syill have to chunk the data into rows of nvarchar(4000) size.
( you don't have to do the concatenation stuff though, since the proc does that for you )

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources