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
7  6160 
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
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
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
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
> 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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: RotterdamStudents |
last post by:
Hello there,
i have a strange problem. I can't get php to insert multiple rows at once in
a MySQL database. I use the
$sql = "INSERT INTO database (a,b,c,d,e) VALUES ('$a', '$b' ,'$c', '$d',...
|
by: Valerie |
last post by:
Hi everyone,
I really appreciate if anyone could help me with this tricky problem
that I'm having. I'm looking for a sample script to combine data in
multiple rows into one row. I'm using...
|
by: fip |
last post by:
Hi,
On DB2 7.1.2 on MVS OS 390, when I tried to do an insert with
multiple row contents in the values clause:
insert into table11 values('aaaa', 'fa'), ('bbbb', 'fb')
I got the error:
...
|
by: Jay |
last post by:
Hi All,
My users are complaining about the page refreshing when they are
selecting multiple rows in a datagrid. Has anyone tried to manage this
using javascript?
I tried smartnavigation but that...
|
by: Bob Loveshade |
last post by:
I am looking for an example that shows how to select and highlight
multiple rows in a DataGrid.
My DataGrid is part of a Web User Control which is contained in an ASPX
page.
I haven't been...
|
by: craftit |
last post by:
hi everyone,
i need to insert multiple rows in a single table using Oledbdataadapter for access database all in one trip in my winform(VB.Net). i've tried the best i can.Can anyone please help me...
|
by: kevinjbowman |
last post by:
I am by no means a SQl Jedi as will be apparent by my question, but I
can usually figure out a select statement on my own. I have one today
though that really has me stumped. I am working in...
|
by: arockiasamy |
last post by:
hi,
How to select multiple rows in a table using java script? and that selected rows must be deleted?
i have done for single selection. how it can be for multiple?
come on help me.. i...
|
by: =?Utf-8?B?S2F5xLFoYW4=?= |
last post by:
In my project,i added datagridview to my form , i transfered my table to
datagridview and added multiple rows and when i called dataadapther.update
,,result is ok. But when i tried it for the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
