Execute sql scripts saved in a text column of a table

22 Jun 2006 11:33 PM

Peter

How can I execute sql scripts saved in a text column of a table?

Thanks,

Peter

23 Jun 2006 1:09 AM

Derekman

Use dynamic sql statement

EXAMPLE (using Northwind database)

DECLARE @vSQL VARCHAR(1000), @numrows INT
SELECT @numrows = 25
SELECT @vSQL = 'SELECT TOP ' + CONVERT(VARCHAR, @numrows) + ' * FROM
Products ORDER BY ProductName'
EXECUTE(@vSQL)

This evaluates to:
SELECT TOP 25 * FROM Products ORDER BY ProductName
Good luck and hope it helps!

Show quote

"Peter" wrote:

> How can I execute sql scripts saved in a text column of a table?
>
>
> Thanks,
>
> Peter

23 Jun 2006 1:54 AM

Omnibuzz

Hi,
Go for a cursor that will loop through all the rows and get the text field
in a variable and use dynamic SQL (EXEC or sp_Executesql )
But again, is it a text field or varchar field?
If its text and can span over 8000 characters.
Then you will have to split it to 8000 char length strings, say str1, str2
then you can use
exec(str1 +str2)

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/

Show quote

"Peter" wrote:

> How can I execute sql scripts saved in a text column of a table?
>
>
> Thanks,
>
> Peter

23 Jun 2006 5:38 AM

Arnie Rowland

If one has a query exceeding 8000 characters, using dynamic SQL is the least
of the worries.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message
news:85C96AC5-6DD2-44B8-B0EE-79737C41C94B@microsoft.com...
> Hi,
> Go for a cursor that will loop through all the rows and get the text field
> in a variable and use dynamic SQL (EXEC or sp_Executesql )
> But again, is it a text field or varchar field?
> If its text and can span over 8000 characters.
> Then you will have to split it to 8000 char length strings, say str1, str2
> then you can use
> exec(str1 +str2)
> --
> -Omnibuzz (The SQL GC)
>
> http://omnibuzz-sql.blogspot.com/
>
>
>
> "Peter" wrote:
>
>> How can I execute sql scripts saved in a text column of a table?
>>
>>
>> Thanks,
>>
>> Peter