By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,630 Members | 1,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,630 IT Pros & Developers. It's quick & easy.

executing sql code from text field or from multiple varchar(8000) rows in a table

P: n/a
Does anyone know of a way to execute sql code from a dynamically built
text field?

Before beginning, let me state that I know this db architecture is
built solely for frustration and I hope to make it better soon.
Unfortunately, there is never a non-crucial time in which we can do an
upgrade, so we are stuck for now.

Point 1:
There are multiple tables: students, courses, cross-reference
student/courses, teachers, cross-reference teacher/courses, and
others.

Point 2:
Yearly archiving is done by appending a 2 digit year suffix to the
table and rebuilding the table structure for the current year. That
is, each of the tables named above has an archive table for 1999,
2000, 2001, etc. This leads to many tables and a big mess when
unioning them.

Point 3:
A history report is run by building a big query that unions each union
of tables (students, courses, etc) by year. This query has grown too
big for a varchar(8000) field. Actually, it's too big for 2 of them.

Point 4:
I don't want to write code to maintain any more varchar(8000) fields
for this query. It should be much more easily handled with a
temporary table holding each bit of yearly archive data. I have built
this and it works fine. I have also figured out how to pull the rows
from that table, concatenate them, and insert the resulting lump into
a text field in another table.

Point 5:
I haven't figured out how to grab the 5 or so records from that table
and execute them on their own. I figured I could grab them, put them
into a text field that's big enough to hold the whole query and then
select and execute that text field. I've had no luck with that and I
haven't had any luck finding any references that might help me with
this problem. I keep thinking of nesting execute() calls, but that
doesn't work.
I'm open to questions, potential solutions, hints about different way
to approach the problem, anything.

Many thanks in advance,
Rick Caborn
Jul 20 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Since you didn't specify - I am assuming that the SQL is fairly static from
year to year, just when you change years you add tables.

Use the dynamic logic you have to create a procedure or view to call, which
also gives you performance enhancements.

I am a big fan of captured print and selects statement that get redirected
into a file, and then run that file via OSQL. Sometime you need to use
GAWK, SED, or PERL to clean the text up before executing. I've done this
to create multiple duplicate triggers.

And whatever process breaks the old view/procedure by adding a new table,
then has to create the new procs, since they are the one that extended the
need.

"Rick Caborn" <ri********@yahoo.ca> wrote in message
news:db**************************@posting.google.c om...
Does anyone know of a way to execute sql code from a dynamically built
text field?

Before beginning, let me state that I know this db architecture is
built solely for frustration and I hope to make it better soon.
Unfortunately, there is never a non-crucial time in which we can do an
upgrade, so we are stuck for now.

Point 1:
There are multiple tables: students, courses, cross-reference
student/courses, teachers, cross-reference teacher/courses, and
others.

Point 2:
Yearly archiving is done by appending a 2 digit year suffix to the
table and rebuilding the table structure for the current year. That
is, each of the tables named above has an archive table for 1999,
2000, 2001, etc. This leads to many tables and a big mess when
unioning them.

Point 3:
A history report is run by building a big query that unions each union
of tables (students, courses, etc) by year. This query has grown too
big for a varchar(8000) field. Actually, it's too big for 2 of them.

Point 4:
I don't want to write code to maintain any more varchar(8000) fields
for this query. It should be much more easily handled with a
temporary table holding each bit of yearly archive data. I have built
this and it works fine. I have also figured out how to pull the rows
from that table, concatenate them, and insert the resulting lump into
a text field in another table.

Point 5:
I haven't figured out how to grab the 5 or so records from that table
and execute them on their own. I figured I could grab them, put them
into a text field that's big enough to hold the whole query and then
select and execute that text field. I've had no luck with that and I
haven't had any luck finding any references that might help me with
this problem. I keep thinking of nesting execute() calls, but that
doesn't work.
I'm open to questions, potential solutions, hints about different way
to approach the problem, anything.

Many thanks in advance,
Rick Caborn

Jul 20 '05 #2

P: n/a
Hi David,

Many thanks for your hints. I started to look into xp_cmdshell and
osql, but there are permission problems and of course I can't contact
the person that deals with that and time constraints, etc. I'm hoping
that my approach may still have a hidden escape hatch that will help
me solve this.

If I create a procedure or view, it still needs to pull that big query
out and execute it at some point. That's what I am having trouble
doing.

This may help focus my rambling question a bit:

I have the dynamically built query stored in a field of type "text".
How can I execute that query? It's too big to store in a varchar
variable, so I can't SELECT and EXECUTE() it.

EXECUTE('SELECT t_Query FROM tblQueries WHERE i_QID = 1') returns the
big query itself, but I'm thinking I want something like:
EXECUTE('EXECUTE(''SELECT t_Query FROM tblQueries WHERE i_QID = 1''))

Of course, that doesn't work as I would like it to. It simply returns
the big query again. What I'm shooting for is to be able to execute
the string returned from the nested EXECUTE(). Any other ideas?

Regards,
Rick Caborn
"David Rawheiser" <ra*******@hotmail.com> wrote in message news:<Tm*********************@bgtnsc04-news.ops.worldnet.att.net>...
Since you didn't specify - I am assuming that the SQL is fairly static from
year to year, just when you change years you add tables.

Use the dynamic logic you have to create a procedure or view to call, which
also gives you performance enhancements.

I am a big fan of captured print and selects statement that get redirected
into a file, and then run that file via OSQL. Sometime you need to use
GAWK, SED, or PERL to clean the text up before executing. I've done this
to create multiple duplicate triggers.

And whatever process breaks the old view/procedure by adding a new table,
then has to create the new procs, since they are the one that extended the
need.

"Rick Caborn" <ri********@yahoo.ca> wrote in message
news:db**************************@posting.google.c om...
Does anyone know of a way to execute sql code from a dynamically built
text field?

Before beginning, let me state that I know this db architecture is
built solely for frustration and I hope to make it better soon.
Unfortunately, there is never a non-crucial time in which we can do an
upgrade, so we are stuck for now.

Point 1:
There are multiple tables: students, courses, cross-reference
student/courses, teachers, cross-reference teacher/courses, and
others.

Point 2:
Yearly archiving is done by appending a 2 digit year suffix to the
table and rebuilding the table structure for the current year. That
is, each of the tables named above has an archive table for 1999,
2000, 2001, etc. This leads to many tables and a big mess when
unioning them.

Point 3:
A history report is run by building a big query that unions each union
of tables (students, courses, etc) by year. This query has grown too
big for a varchar(8000) field. Actually, it's too big for 2 of them.

Point 4:
I don't want to write code to maintain any more varchar(8000) fields
for this query. It should be much more easily handled with a
temporary table holding each bit of yearly archive data. I have built
this and it works fine. I have also figured out how to pull the rows
from that table, concatenate them, and insert the resulting lump into
a text field in another table.

Point 5:
I haven't figured out how to grab the 5 or so records from that table
and execute them on their own. I figured I could grab them, put them
into a text field that's big enough to hold the whole query and then
select and execute that text field. I've had no luck with that and I
haven't had any luck finding any references that might help me with
this problem. I keep thinking of nesting execute() calls, but that
doesn't work.
I'm open to questions, potential solutions, hints about different way
to approach the problem, anything.

Many thanks in advance,
Rick Caborn

Jul 20 '05 #3

P: n/a
Text columns are a bit of a PITA in Transact-SQL. You can accomplish the
task using SUBSTRING like the untested example below.

DECLARE
@QueryPart1 varchar(8000),
@QueryPart2 varchar(8000),
@QueryPart3 varchar(8000),
@QueryPart4 varchar(8000),
@QueryLength int

SELECT
@QueryLength=DATALENGTH(t_Query),
@QueryPart1 = SUBSTRING(t_Query, 1, 8000)
tblQueries WHERE i_QID = 1

IF @QueryLength > 8000
SELECT
@QueryPart2 = SUBSTRING(t_Query, 8001, 8000)
FROM tblQueries WHERE i_QID = 1
ELSE
SET @QueryPart2 = ''

IF @QueryLength > 16001
SELECT
@QueryPart3 = SUBSTRING(t_Query, 16001, 8000)
FROM tblQueries WHERE i_QID = 1
ELSE
SET @QueryPart3 = ''

IF @QueryLength > 24001
SELECT
@QueryPart4 = SUBSTRING(t_Query, 24001, 8000)
FROM tblQueries WHERE i_QID = 1
ELSE
SET @QueryPart4 = ''

EXEC(@QueryPart1 + @QueryPart2 + @QueryPart3 + @QueryPart4)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rick Caborn" <ri********@yahoo.ca> wrote in message
news:db**************************@posting.google.c om...
Hi David,

Many thanks for your hints. I started to look into xp_cmdshell and
osql, but there are permission problems and of course I can't contact
the person that deals with that and time constraints, etc. I'm hoping
that my approach may still have a hidden escape hatch that will help
me solve this.

If I create a procedure or view, it still needs to pull that big query
out and execute it at some point. That's what I am having trouble
doing.

This may help focus my rambling question a bit:

I have the dynamically built query stored in a field of type "text".
How can I execute that query? It's too big to store in a varchar
variable, so I can't SELECT and EXECUTE() it.

EXECUTE('SELECT t_Query FROM tblQueries WHERE i_QID = 1') returns the
big query itself, but I'm thinking I want something like:
EXECUTE('EXECUTE(''SELECT t_Query FROM tblQueries WHERE i_QID = 1''))

Of course, that doesn't work as I would like it to. It simply returns
the big query again. What I'm shooting for is to be able to execute
the string returned from the nested EXECUTE(). Any other ideas?

Regards,
Rick Caborn
"David Rawheiser" <ra*******@hotmail.com> wrote in message
news:<Tm*********************@bgtnsc04-news.ops.worldnet.att.net>...
Since you didn't specify - I am assuming that the SQL is fairly static
from
year to year, just when you change years you add tables.

Use the dynamic logic you have to create a procedure or view to call,
which
also gives you performance enhancements.

I am a big fan of captured print and selects statement that get
redirected
into a file, and then run that file via OSQL. Sometime you need to use
GAWK, SED, or PERL to clean the text up before executing. I've done
this
to create multiple duplicate triggers.

And whatever process breaks the old view/procedure by adding a new table,
then has to create the new procs, since they are the one that extended
the
need.

"Rick Caborn" <ri********@yahoo.ca> wrote in message
news:db**************************@posting.google.c om...
> Does anyone know of a way to execute sql code from a dynamically built
> text field?
>
> Before beginning, let me state that I know this db architecture is
> built solely for frustration and I hope to make it better soon.
> Unfortunately, there is never a non-crucial time in which we can do an
> upgrade, so we are stuck for now.
>
> Point 1:
> There are multiple tables: students, courses, cross-reference
> student/courses, teachers, cross-reference teacher/courses, and
> others.
>
> Point 2:
> Yearly archiving is done by appending a 2 digit year suffix to the
> table and rebuilding the table structure for the current year. That
> is, each of the tables named above has an archive table for 1999,
> 2000, 2001, etc. This leads to many tables and a big mess when
> unioning them.
>
> Point 3:
> A history report is run by building a big query that unions each union
> of tables (students, courses, etc) by year. This query has grown too
> big for a varchar(8000) field. Actually, it's too big for 2 of them.
>
> Point 4:
> I don't want to write code to maintain any more varchar(8000) fields
> for this query. It should be much more easily handled with a
> temporary table holding each bit of yearly archive data. I have built
> this and it works fine. I have also figured out how to pull the rows
> from that table, concatenate them, and insert the resulting lump into
> a text field in another table.
>
> Point 5:
> I haven't figured out how to grab the 5 or so records from that table
> and execute them on their own. I figured I could grab them, put them
> into a text field that's big enough to hold the whole query and then
> select and execute that text field. I've had no luck with that and I
> haven't had any luck finding any references that might help me with
> this problem. I keep thinking of nesting execute() calls, but that
> doesn't work.
>
>
> I'm open to questions, potential solutions, hints about different way
> to approach the problem, anything.
>
> Many thanks in advance,
> Rick Caborn

Jul 20 '05 #4

P: n/a

Hi Dan,

Thanks for your detailed response and sample code. I neglected to
mention that I have already tried this approach and for some reason,
DATALENGTH() is reporting a length of 8000 for the t_Query field (TEXT
type). When I run an asp page that selects that field and prints it
out, I get all that I expect (10866 characters).

I have tried setting TEXTSIZE and that doesn't seem to affect the result
in Query Analyzer.

When I try reading past the 8000 point using SUBSTRING(), I get the
following result:

Server: Msg 212, Level 16, State 1, Line 32
Expression result length exceeds the maximum. 8000 max, 9000 found.

When I try to use READTEXT() to get the entire query, I get the
following result:

Server: Msg 7124, Level 16, State 1, Line 55
The offset and length specified in the READTEXT statement is greater
than the actual data length of 8000.
My asp page returning the full size of the text field shows me that the
actual data length is over 8000. Why would SQL Server think that it's
only 8000? I have tried this with "text in row" both enabled and
disabled.

Any other ideas, pointers, references, etc?
Regards,
Rick Caborn
Dan Guzman wrote:

Text columns are a bit of a PITA in Transact-SQL. You can accomplish the
task using SUBSTRING like the untested example below.

DECLARE
@QueryPart1 varchar(8000),
@QueryPart2 varchar(8000),
@QueryPart3 varchar(8000),
@QueryPart4 varchar(8000),
@QueryLength int

SELECT
@QueryLength=DATALENGTH(t_Query),
@QueryPart1 = SUBSTRING(t_Query, 1, 8000)
tblQueries WHERE i_QID = 1

IF @QueryLength > 8000
SELECT
@QueryPart2 = SUBSTRING(t_Query, 8001, 8000)
FROM tblQueries WHERE i_QID = 1
ELSE
SET @QueryPart2 = ''

IF @QueryLength > 16001
SELECT
@QueryPart3 = SUBSTRING(t_Query, 16001, 8000)
FROM tblQueries WHERE i_QID = 1
ELSE
SET @QueryPart3 = ''

IF @QueryLength > 24001
SELECT
@QueryPart4 = SUBSTRING(t_Query, 24001, 8000)
FROM tblQueries WHERE i_QID = 1
ELSE
SET @QueryPart4 = ''

EXEC(@QueryPart1 + @QueryPart2 + @QueryPart3 + @QueryPart4)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rick Caborn" <ri********@yahoo.ca> wrote in message
news:db**************************@posting.google.c om...
Hi David,

Many thanks for your hints. I started to look into xp_cmdshell and
osql, but there are permission problems and of course I can't contact
the person that deals with that and time constraints, etc. I'm hoping
that my approach may still have a hidden escape hatch that will help
me solve this.

If I create a procedure or view, it still needs to pull that big query
out and execute it at some point. That's what I am having trouble
doing.

This may help focus my rambling question a bit:

I have the dynamically built query stored in a field of type "text".
How can I execute that query? It's too big to store in a varchar
variable, so I can't SELECT and EXECUTE() it.

EXECUTE('SELECT t_Query FROM tblQueries WHERE i_QID = 1') returns the
big query itself, but I'm thinking I want something like:
EXECUTE('EXECUTE(''SELECT t_Query FROM tblQueries WHERE i_QID = 1''))

Of course, that doesn't work as I would like it to. It simply returns
the big query again. What I'm shooting for is to be able to execute
the string returned from the nested EXECUTE(). Any other ideas?

Regards,
Rick Caborn
"David Rawheiser" <ra*******@hotmail.com> wrote in message
news:<Tm*********************@bgtnsc04-news.ops.worldnet.att.net>...
Since you didn't specify - I am assuming that the SQL is fairly static
from
year to year, just when you change years you add tables.

Use the dynamic logic you have to create a procedure or view to call,
which
also gives you performance enhancements.

I am a big fan of captured print and selects statement that get
redirected
into a file, and then run that file via OSQL. Sometime you need to use
GAWK, SED, or PERL to clean the text up before executing. I've done
this
to create multiple duplicate triggers.

And whatever process breaks the old view/procedure by adding a new table,
then has to create the new procs, since they are the one that extended
the
need.

"Rick Caborn" <ri********@yahoo.ca> wrote in message
news:db**************************@posting.google.c om...
> Does anyone know of a way to execute sql code from a dynamically built
> text field?
>
> Before beginning, let me state that I know this db architecture is
> built solely for frustration and I hope to make it better soon.
> Unfortunately, there is never a non-crucial time in which we can do an
> upgrade, so we are stuck for now.
>
> Point 1:
> There are multiple tables: students, courses, cross-reference
> student/courses, teachers, cross-reference teacher/courses, and
> others.
>
> Point 2:
> Yearly archiving is done by appending a 2 digit year suffix to the
> table and rebuilding the table structure for the current year. That
> is, each of the tables named above has an archive table for 1999,
> 2000, 2001, etc. This leads to many tables and a big mess when
> unioning them.
>
> Point 3:
> A history report is run by building a big query that unions each union
> of tables (students, courses, etc) by year. This query has grown too
> big for a varchar(8000) field. Actually, it's too big for 2 of them.
>
> Point 4:
> I don't want to write code to maintain any more varchar(8000) fields
> for this query. It should be much more easily handled with a
> temporary table holding each bit of yearly archive data. I have built
> this and it works fine. I have also figured out how to pull the rows
> from that table, concatenate them, and insert the resulting lump into
> a text field in another table.
>
> Point 5:
> I haven't figured out how to grab the 5 or so records from that table
> and execute them on their own. I figured I could grab them, put them
> into a text field that's big enough to hold the whole query and then
> select and execute that text field. I've had no luck with that and I
> haven't had any luck finding any references that might help me with
> this problem. I keep thinking of nesting execute() calls, but that
> doesn't work.
>
>
> I'm open to questions, potential solutions, hints about different way
> to approach the problem, anything.
>
> Many thanks in advance,
> Rick Caborn

Jul 20 '05 #5

P: n/a
> I neglected to
mention that I have already tried this approach and for some reason,
DATALENGTH() is reporting a length of 8000 for the t_Query field
Something is not right then. DATALENGTH should report the actual length of
the text data in bytes. The TEXTSIZE setting is not relevant. The
following script returns the expected length of 24000 on my test server. Do
you get the same results on your machine with this script? Does this SELECT
return a length of 8000 against your actual table?

USE tempdb
CREATE TABLE tblQueries
(
i_QID int NOT NULL
CONSTRAINT PK_tblQueries PRIMARY KEY,
t_Query text NOT NULL,
)

DECLARE @TextPtr varbinary(16)
DECLARE @TestData varchar(8000)
SET @TestData = REPLICATE('x', 8000)
INSERT INTO tblQueries VALUES(1, @TestData)
SELECT @TextPtr = TEXTPTR(t_Query) FROM tblQueries
UPDATETEXT tblQueries.t_Query @TextPtr 8000 0 @TestData
UPDATETEXT tblQueries.t_Query @TextPtr 16000 0 @TestData

SELECT DATALENGTH(t_Query)
FROM tblQueries
WHERE i_QID = 1

DROP TABLE tblQueries
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rick Caborn" <ri********@yahoo.ca> wrote in message
news:41***************@yahoo.ca...
Hi Dan,

Thanks for your detailed response and sample code. I neglected to
mention that I have already tried this approach and for some reason,
DATALENGTH() is reporting a length of 8000 for the t_Query field (TEXT
type). When I run an asp page that selects that field and prints it
out, I get all that I expect (10866 characters).

I have tried setting TEXTSIZE and that doesn't seem to affect the result
in Query Analyzer.

When I try reading past the 8000 point using SUBSTRING(), I get the
following result:

Server: Msg 212, Level 16, State 1, Line 32
Expression result length exceeds the maximum. 8000 max, 9000 found.

When I try to use READTEXT() to get the entire query, I get the
following result:

Server: Msg 7124, Level 16, State 1, Line 55
The offset and length specified in the READTEXT statement is greater
than the actual data length of 8000.
My asp page returning the full size of the text field shows me that the
actual data length is over 8000. Why would SQL Server think that it's
only 8000? I have tried this with "text in row" both enabled and
disabled.

Any other ideas, pointers, references, etc?
Regards,
Rick Caborn
Dan Guzman wrote:

Text columns are a bit of a PITA in Transact-SQL. You can accomplish the
task using SUBSTRING like the untested example below.

DECLARE
@QueryPart1 varchar(8000),
@QueryPart2 varchar(8000),
@QueryPart3 varchar(8000),
@QueryPart4 varchar(8000),
@QueryLength int

SELECT
@QueryLength=DATALENGTH(t_Query),
@QueryPart1 = SUBSTRING(t_Query, 1, 8000)
tblQueries WHERE i_QID = 1

IF @QueryLength > 8000
SELECT
@QueryPart2 = SUBSTRING(t_Query, 8001, 8000)
FROM tblQueries WHERE i_QID = 1
ELSE
SET @QueryPart2 = ''

IF @QueryLength > 16001
SELECT
@QueryPart3 = SUBSTRING(t_Query, 16001, 8000)
FROM tblQueries WHERE i_QID = 1
ELSE
SET @QueryPart3 = ''

IF @QueryLength > 24001
SELECT
@QueryPart4 = SUBSTRING(t_Query, 24001, 8000)
FROM tblQueries WHERE i_QID = 1
ELSE
SET @QueryPart4 = ''

EXEC(@QueryPart1 + @QueryPart2 + @QueryPart3 + @QueryPart4)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rick Caborn" <ri********@yahoo.ca> wrote in message
news:db**************************@posting.google.c om...
> Hi David,
>
> Many thanks for your hints. I started to look into xp_cmdshell and
> osql, but there are permission problems and of course I can't contact
> the person that deals with that and time constraints, etc. I'm hoping
> that my approach may still have a hidden escape hatch that will help
> me solve this.
>
> If I create a procedure or view, it still needs to pull that big query
> out and execute it at some point. That's what I am having trouble
> doing.
>
> This may help focus my rambling question a bit:
>
> I have the dynamically built query stored in a field of type "text".
> How can I execute that query? It's too big to store in a varchar
> variable, so I can't SELECT and EXECUTE() it.
>
> EXECUTE('SELECT t_Query FROM tblQueries WHERE i_QID = 1') returns the
> big query itself, but I'm thinking I want something like:
> EXECUTE('EXECUTE(''SELECT t_Query FROM tblQueries WHERE i_QID = 1''))
>
> Of course, that doesn't work as I would like it to. It simply returns
> the big query again. What I'm shooting for is to be able to execute
> the string returned from the nested EXECUTE(). Any other ideas?
>
> Regards,
> Rick Caborn
>
>
> "David Rawheiser" <ra*******@hotmail.com> wrote in message
> news:<Tm*********************@bgtnsc04-news.ops.worldnet.att.net>...
>> Since you didn't specify - I am assuming that the SQL is fairly static
>> from
>> year to year, just when you change years you add tables.
>>
>> Use the dynamic logic you have to create a procedure or view to call,
>> which
>> also gives you performance enhancements.
>>
>> I am a big fan of captured print and selects statement that get
>> redirected
>> into a file, and then run that file via OSQL. Sometime you need to
>> use
>> GAWK, SED, or PERL to clean the text up before executing. I've done
>> this
>> to create multiple duplicate triggers.
>>
>> And whatever process breaks the old view/procedure by adding a new
>> table,
>> then has to create the new procs, since they are the one that extended
>> the
>> need.
>>
>> "Rick Caborn" <ri********@yahoo.ca> wrote in message
>> news:db**************************@posting.google.c om...
>> > Does anyone know of a way to execute sql code from a dynamically
>> > built
>> > text field?
>> >
>> > Before beginning, let me state that I know this db architecture is
>> > built solely for frustration and I hope to make it better soon.
>> > Unfortunately, there is never a non-crucial time in which we can do
>> > an
>> > upgrade, so we are stuck for now.
>> >
>> > Point 1:
>> > There are multiple tables: students, courses, cross-reference
>> > student/courses, teachers, cross-reference teacher/courses, and
>> > others.
>> >
>> > Point 2:
>> > Yearly archiving is done by appending a 2 digit year suffix to the
>> > table and rebuilding the table structure for the current year. That
>> > is, each of the tables named above has an archive table for 1999,
>> > 2000, 2001, etc. This leads to many tables and a big mess when
>> > unioning them.
>> >
>> > Point 3:
>> > A history report is run by building a big query that unions each
>> > union
>> > of tables (students, courses, etc) by year. This query has grown
>> > too
>> > big for a varchar(8000) field. Actually, it's too big for 2 of
>> > them.
>> >
>> > Point 4:
>> > I don't want to write code to maintain any more varchar(8000) fields
>> > for this query. It should be much more easily handled with a
>> > temporary table holding each bit of yearly archive data. I have
>> > built
>> > this and it works fine. I have also figured out how to pull the
>> > rows
>> > from that table, concatenate them, and insert the resulting lump
>> > into
>> > a text field in another table.
>> >
>> > Point 5:
>> > I haven't figured out how to grab the 5 or so records from that
>> > table
>> > and execute them on their own. I figured I could grab them, put
>> > them
>> > into a text field that's big enough to hold the whole query and then
>> > select and execute that text field. I've had no luck with that and
>> > I
>> > haven't had any luck finding any references that might help me with
>> > this problem. I keep thinking of nesting execute() calls, but that
>> > doesn't work.
>> >
>> >
>> > I'm open to questions, potential solutions, hints about different
>> > way
>> > to approach the problem, anything.
>> >
>> > Many thanks in advance,
>> > Rick Caborn

Jul 20 '05 #6

P: n/a

Magnificent! It works like a charm.

My original approach was to build the big query from smaller pieces
stored in a different table. I inserted the pieces of the big query in
one go via an EXECUTE() call wrapped around the 6 or 7 select queries
into the subquery table. Inserting the big query that way seems to be
the problem, although I don't know why. Even though the entire query is
there to be read in its entirety from an asp page, trying SUBSTRING or
READTEXT past the 8000 byte position in a stored procedure or via Query
Analyzer consistently failed.

Now, instead of putting the pieces of the big query into a "query chunk"
table and selecting from that to insert the whole big query, I'm
inserting the first part directly into tblQueries and using UPDATETEXT
to append to it. It works exactly as expected.

Thanks for your input - you helped me see that I could remove the
unnecessary middle step and make the query work. I hope that I can be
as helpful to someone someday - many thanks!

Best regards,
Rick Caborn
Dan Guzman wrote:
I neglected to
mention that I have already tried this approach and for some reason,
DATALENGTH() is reporting a length of 8000 for the t_Query field


Something is not right then. DATALENGTH should report the actual length of
the text data in bytes. The TEXTSIZE setting is not relevant. The
following script returns the expected length of 24000 on my test server. Do
you get the same results on your machine with this script? Does this SELECT
return a length of 8000 against your actual table?

USE tempdb
CREATE TABLE tblQueries
(
i_QID int NOT NULL
CONSTRAINT PK_tblQueries PRIMARY KEY,
t_Query text NOT NULL,
)

DECLARE @TextPtr varbinary(16)
DECLARE @TestData varchar(8000)
SET @TestData = REPLICATE('x', 8000)
INSERT INTO tblQueries VALUES(1, @TestData)
SELECT @TextPtr = TEXTPTR(t_Query) FROM tblQueries
UPDATETEXT tblQueries.t_Query @TextPtr 8000 0 @TestData
UPDATETEXT tblQueries.t_Query @TextPtr 16000 0 @TestData

SELECT DATALENGTH(t_Query)
FROM tblQueries
WHERE i_QID = 1

DROP TABLE tblQueries
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rick Caborn" <ri********@yahoo.ca> wrote in message
news:41***************@yahoo.ca...

Hi Dan,

Thanks for your detailed response and sample code. I neglected to
mention that I have already tried this approach and for some reason,
DATALENGTH() is reporting a length of 8000 for the t_Query field (TEXT
type). When I run an asp page that selects that field and prints it
out, I get all that I expect (10866 characters).

I have tried setting TEXTSIZE and that doesn't seem to affect the result
in Query Analyzer.

When I try reading past the 8000 point using SUBSTRING(), I get the
following result:

Server: Msg 212, Level 16, State 1, Line 32
Expression result length exceeds the maximum. 8000 max, 9000 found.

When I try to use READTEXT() to get the entire query, I get the
following result:

Server: Msg 7124, Level 16, State 1, Line 55
The offset and length specified in the READTEXT statement is greater
than the actual data length of 8000.
My asp page returning the full size of the text field shows me that the
actual data length is over 8000. Why would SQL Server think that it's
only 8000? I have tried this with "text in row" both enabled and
disabled.

Any other ideas, pointers, references, etc?
Regards,
Rick Caborn
Dan Guzman wrote:

Text columns are a bit of a PITA in Transact-SQL. You can accomplish the
task using SUBSTRING like the untested example below.

DECLARE
@QueryPart1 varchar(8000),
@QueryPart2 varchar(8000),
@QueryPart3 varchar(8000),
@QueryPart4 varchar(8000),
@QueryLength int

SELECT
@QueryLength=DATALENGTH(t_Query),
@QueryPart1 = SUBSTRING(t_Query, 1, 8000)
tblQueries WHERE i_QID = 1

IF @QueryLength > 8000
SELECT
@QueryPart2 = SUBSTRING(t_Query, 8001, 8000)
FROM tblQueries WHERE i_QID = 1
ELSE
SET @QueryPart2 = ''

IF @QueryLength > 16001
SELECT
@QueryPart3 = SUBSTRING(t_Query, 16001, 8000)
FROM tblQueries WHERE i_QID = 1
ELSE
SET @QueryPart3 = ''

IF @QueryLength > 24001
SELECT
@QueryPart4 = SUBSTRING(t_Query, 24001, 8000)
FROM tblQueries WHERE i_QID = 1
ELSE
SET @QueryPart4 = ''

EXEC(@QueryPart1 + @QueryPart2 + @QueryPart3 + @QueryPart4)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rick Caborn" <ri********@yahoo.ca> wrote in message
news:db**************************@posting.google.c om...
> Hi David,
>
> Many thanks for your hints. I started to look into xp_cmdshell and
> osql, but there are permission problems and of course I can't contact
> the person that deals with that and time constraints, etc. I'm hoping
> that my approach may still have a hidden escape hatch that will help
> me solve this.
>
> If I create a procedure or view, it still needs to pull that big query
> out and execute it at some point. That's what I am having trouble
> doing.
>
> This may help focus my rambling question a bit:
>
> I have the dynamically built query stored in a field of type "text".
> How can I execute that query? It's too big to store in a varchar
> variable, so I can't SELECT and EXECUTE() it.
>
> EXECUTE('SELECT t_Query FROM tblQueries WHERE i_QID = 1') returns the
> big query itself, but I'm thinking I want something like:
> EXECUTE('EXECUTE(''SELECT t_Query FROM tblQueries WHERE i_QID = 1''))
>
> Of course, that doesn't work as I would like it to. It simply returns
> the big query again. What I'm shooting for is to be able to execute
> the string returned from the nested EXECUTE(). Any other ideas?
>
> Regards,
> Rick Caborn
>
>
> "David Rawheiser" <ra*******@hotmail.com> wrote in message
> news:<Tm*********************@bgtnsc04-news.ops.worldnet.att.net>...
>> Since you didn't specify - I am assuming that the SQL is fairly static
>> from
>> year to year, just when you change years you add tables.
>>
>> Use the dynamic logic you have to create a procedure or view to call,
>> which
>> also gives you performance enhancements.
>>
>> I am a big fan of captured print and selects statement that get
>> redirected
>> into a file, and then run that file via OSQL. Sometime you need to
>> use
>> GAWK, SED, or PERL to clean the text up before executing. I've done
>> this
>> to create multiple duplicate triggers.
>>
>> And whatever process breaks the old view/procedure by adding a new
>> table,
>> then has to create the new procs, since they are the one that extended
>> the
>> need.
>>
>> "Rick Caborn" <ri********@yahoo.ca> wrote in message
>> news:db**************************@posting.google.c om...
>> > Does anyone know of a way to execute sql code from a dynamically
>> > built
>> > text field?
>> >
>> > Before beginning, let me state that I know this db architecture is
>> > built solely for frustration and I hope to make it better soon.
>> > Unfortunately, there is never a non-crucial time in which we can do
>> > an
>> > upgrade, so we are stuck for now.
>> >
>> > Point 1:
>> > There are multiple tables: students, courses, cross-reference
>> > student/courses, teachers, cross-reference teacher/courses, and
>> > others.
>> >
>> > Point 2:
>> > Yearly archiving is done by appending a 2 digit year suffix to the
>> > table and rebuilding the table structure for the current year. That
>> > is, each of the tables named above has an archive table for 1999,
>> > 2000, 2001, etc. This leads to many tables and a big mess when
>> > unioning them.
>> >
>> > Point 3:
>> > A history report is run by building a big query that unions each
>> > union
>> > of tables (students, courses, etc) by year. This query has grown
>> > too
>> > big for a varchar(8000) field. Actually, it's too big for 2 of
>> > them.
>> >
>> > Point 4:
>> > I don't want to write code to maintain any more varchar(8000) fields
>> > for this query. It should be much more easily handled with a
>> > temporary table holding each bit of yearly archive data. I have
>> > built
>> > this and it works fine. I have also figured out how to pull the
>> > rows
>> > from that table, concatenate them, and insert the resulting lump
>> > into
>> > a text field in another table.
>> >
>> > Point 5:
>> > I haven't figured out how to grab the 5 or so records from that
>> > table
>> > and execute them on their own. I figured I could grab them, put
>> > them
>> > into a text field that's big enough to hold the whole query and then
>> > select and execute that text field. I've had no luck with that and
>> > I
>> > haven't had any luck finding any references that might help me with
>> > this problem. I keep thinking of nesting execute() calls, but that
>> > doesn't work.
>> >
>> >
>> > I'm open to questions, potential solutions, hints about different
>> > way
>> > to approach the problem, anything.
>> >
>> > Many thanks in advance,
>> > Rick Caborn

Jul 20 '05 #7

P: n/a
I'm glad I was able to help.

--
Dan Guzman
SQL Server MVP

"Rick Caborn" <ri********@yahoo.ca> wrote in message
news:41***************@yahoo.ca...

Magnificent! It works like a charm.

My original approach was to build the big query from smaller pieces
stored in a different table. I inserted the pieces of the big query in
one go via an EXECUTE() call wrapped around the 6 or 7 select queries
into the subquery table. Inserting the big query that way seems to be
the problem, although I don't know why. Even though the entire query is
there to be read in its entirety from an asp page, trying SUBSTRING or
READTEXT past the 8000 byte position in a stored procedure or via Query
Analyzer consistently failed.

Now, instead of putting the pieces of the big query into a "query chunk"
table and selecting from that to insert the whole big query, I'm
inserting the first part directly into tblQueries and using UPDATETEXT
to append to it. It works exactly as expected.

Thanks for your input - you helped me see that I could remove the
unnecessary middle step and make the query work. I hope that I can be
as helpful to someone someday - many thanks!

Best regards,
Rick Caborn
Dan Guzman wrote:
> I neglected to
> mention that I have already tried this approach and for some reason,
> DATALENGTH() is reporting a length of 8000 for the t_Query field


Something is not right then. DATALENGTH should report the actual length
of
the text data in bytes. The TEXTSIZE setting is not relevant. The
following script returns the expected length of 24000 on my test server.
Do
you get the same results on your machine with this script? Does this
SELECT
return a length of 8000 against your actual table?

USE tempdb
CREATE TABLE tblQueries
(
i_QID int NOT NULL
CONSTRAINT PK_tblQueries PRIMARY KEY,
t_Query text NOT NULL,
)

DECLARE @TextPtr varbinary(16)
DECLARE @TestData varchar(8000)
SET @TestData = REPLICATE('x', 8000)
INSERT INTO tblQueries VALUES(1, @TestData)
SELECT @TextPtr = TEXTPTR(t_Query) FROM tblQueries
UPDATETEXT tblQueries.t_Query @TextPtr 8000 0 @TestData
UPDATETEXT tblQueries.t_Query @TextPtr 16000 0 @TestData

SELECT DATALENGTH(t_Query)
FROM tblQueries
WHERE i_QID = 1

DROP TABLE tblQueries
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rick Caborn" <ri********@yahoo.ca> wrote in message
news:41***************@yahoo.ca...
>
> Hi Dan,
>
> Thanks for your detailed response and sample code. I neglected to
> mention that I have already tried this approach and for some reason,
> DATALENGTH() is reporting a length of 8000 for the t_Query field (TEXT
> type). When I run an asp page that selects that field and prints it
> out, I get all that I expect (10866 characters).
>
> I have tried setting TEXTSIZE and that doesn't seem to affect the
> result
> in Query Analyzer.
>
> When I try reading past the 8000 point using SUBSTRING(), I get the
> following result:
>
> Server: Msg 212, Level 16, State 1, Line 32
> Expression result length exceeds the maximum. 8000 max, 9000 found.
>
>
>
> When I try to use READTEXT() to get the entire query, I get the
> following result:
>
> Server: Msg 7124, Level 16, State 1, Line 55
> The offset and length specified in the READTEXT statement is greater
> than the actual data length of 8000.
>
>
> My asp page returning the full size of the text field shows me that the
> actual data length is over 8000. Why would SQL Server think that it's
> only 8000? I have tried this with "text in row" both enabled and
> disabled.
>
> Any other ideas, pointers, references, etc?
>
>
> Regards,
> Rick Caborn
>
>
> Dan Guzman wrote:
>>
>> Text columns are a bit of a PITA in Transact-SQL. You can accomplish
>> the
>> task using SUBSTRING like the untested example below.
>>
>> DECLARE
>> @QueryPart1 varchar(8000),
>> @QueryPart2 varchar(8000),
>> @QueryPart3 varchar(8000),
>> @QueryPart4 varchar(8000),
>> @QueryLength int
>>
>> SELECT
>> @QueryLength=DATALENGTH(t_Query),
>> @QueryPart1 = SUBSTRING(t_Query, 1, 8000)
>> tblQueries WHERE i_QID = 1
>>
>> IF @QueryLength > 8000
>> SELECT
>> @QueryPart2 = SUBSTRING(t_Query, 8001, 8000)
>> FROM tblQueries WHERE i_QID = 1
>> ELSE
>> SET @QueryPart2 = ''
>>
>> IF @QueryLength > 16001
>> SELECT
>> @QueryPart3 = SUBSTRING(t_Query, 16001, 8000)
>> FROM tblQueries WHERE i_QID = 1
>> ELSE
>> SET @QueryPart3 = ''
>>
>> IF @QueryLength > 24001
>> SELECT
>> @QueryPart4 = SUBSTRING(t_Query, 24001, 8000)
>> FROM tblQueries WHERE i_QID = 1
>> ELSE
>> SET @QueryPart4 = ''
>>
>> EXEC(@QueryPart1 + @QueryPart2 + @QueryPart3 + @QueryPart4)
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Rick Caborn" <ri********@yahoo.ca> wrote in message
>> news:db**************************@posting.google.c om...
>> > Hi David,
>> >
>> > Many thanks for your hints. I started to look into xp_cmdshell and
>> > osql, but there are permission problems and of course I can't
>> > contact
>> > the person that deals with that and time constraints, etc. I'm
>> > hoping
>> > that my approach may still have a hidden escape hatch that will help
>> > me solve this.
>> >
>> > If I create a procedure or view, it still needs to pull that big
>> > query
>> > out and execute it at some point. That's what I am having trouble
>> > doing.
>> >
>> > This may help focus my rambling question a bit:
>> >
>> > I have the dynamically built query stored in a field of type "text".
>> > How can I execute that query? It's too big to store in a varchar
>> > variable, so I can't SELECT and EXECUTE() it.
>> >
>> > EXECUTE('SELECT t_Query FROM tblQueries WHERE i_QID = 1') returns
>> > the
>> > big query itself, but I'm thinking I want something like:
>> > EXECUTE('EXECUTE(''SELECT t_Query FROM tblQueries WHERE i_QID =
>> > 1''))
>> >
>> > Of course, that doesn't work as I would like it to. It simply
>> > returns
>> > the big query again. What I'm shooting for is to be able to execute
>> > the string returned from the nested EXECUTE(). Any other ideas?
>> >
>> > Regards,
>> > Rick Caborn
>> >
>> >
>> > "David Rawheiser" <ra*******@hotmail.com> wrote in message
>> > news:<Tm*********************@bgtnsc04-news.ops.worldnet.att.net>...
>> >> Since you didn't specify - I am assuming that the SQL is fairly
>> >> static
>> >> from
>> >> year to year, just when you change years you add tables.
>> >>
>> >> Use the dynamic logic you have to create a procedure or view to
>> >> call,
>> >> which
>> >> also gives you performance enhancements.
>> >>
>> >> I am a big fan of captured print and selects statement that get
>> >> redirected
>> >> into a file, and then run that file via OSQL. Sometime you need to
>> >> use
>> >> GAWK, SED, or PERL to clean the text up before executing. I've
>> >> done
>> >> this
>> >> to create multiple duplicate triggers.
>> >>
>> >> And whatever process breaks the old view/procedure by adding a new
>> >> table,
>> >> then has to create the new procs, since they are the one that
>> >> extended
>> >> the
>> >> need.
>> >>
>> >> "Rick Caborn" <ri********@yahoo.ca> wrote in message
>> >> news:db**************************@posting.google.c om...
>> >> > Does anyone know of a way to execute sql code from a dynamically
>> >> > built
>> >> > text field?
>> >> >
>> >> > Before beginning, let me state that I know this db architecture
>> >> > is
>> >> > built solely for frustration and I hope to make it better soon.
>> >> > Unfortunately, there is never a non-crucial time in which we can
>> >> > do
>> >> > an
>> >> > upgrade, so we are stuck for now.
>> >> >
>> >> > Point 1:
>> >> > There are multiple tables: students, courses, cross-reference
>> >> > student/courses, teachers, cross-reference teacher/courses, and
>> >> > others.
>> >> >
>> >> > Point 2:
>> >> > Yearly archiving is done by appending a 2 digit year suffix to
>> >> > the
>> >> > table and rebuilding the table structure for the current year.
>> >> > That
>> >> > is, each of the tables named above has an archive table for 1999,
>> >> > 2000, 2001, etc. This leads to many tables and a big mess when
>> >> > unioning them.
>> >> >
>> >> > Point 3:
>> >> > A history report is run by building a big query that unions each
>> >> > union
>> >> > of tables (students, courses, etc) by year. This query has grown
>> >> > too
>> >> > big for a varchar(8000) field. Actually, it's too big for 2 of
>> >> > them.
>> >> >
>> >> > Point 4:
>> >> > I don't want to write code to maintain any more varchar(8000)
>> >> > fields
>> >> > for this query. It should be much more easily handled with a
>> >> > temporary table holding each bit of yearly archive data. I have
>> >> > built
>> >> > this and it works fine. I have also figured out how to pull the
>> >> > rows
>> >> > from that table, concatenate them, and insert the resulting lump
>> >> > into
>> >> > a text field in another table.
>> >> >
>> >> > Point 5:
>> >> > I haven't figured out how to grab the 5 or so records from that
>> >> > table
>> >> > and execute them on their own. I figured I could grab them, put
>> >> > them
>> >> > into a text field that's big enough to hold the whole query and
>> >> > then
>> >> > select and execute that text field. I've had no luck with that
>> >> > and
>> >> > I
>> >> > haven't had any luck finding any references that might help me
>> >> > with
>> >> > this problem. I keep thinking of nesting execute() calls, but
>> >> > that
>> >> > doesn't work.
>> >> >
>> >> >
>> >> > I'm open to questions, potential solutions, hints about different
>> >> > way
>> >> > to approach the problem, anything.
>> >> >
>> >> > Many thanks in advance,
>> >> > Rick Caborn

Jul 20 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.