Hope someone can give me advice on a problem I'm having with my
current development.
I need to build up very long pieces of SQL, then execute them. The SQL
itself is dependent on the structure of the data in 2 other DBs. I was
using varchar(8000) fields to accumulate the dynamic SQL, as I didn't
realize at the time how big it could grow. Each piece of SQL is just
is single SELECT, but a huge one. I reckon that the size could grow to
arounf 100k characters.
To try to get around the varchar limitation, I'm now accumulating the
SQL in a Text column in a DB table (since I can't use temporary
variables of data type Text), and that's all going fine. What I'm not
sure about is how to execute the SQL once I've finished accumulating
it.
So, I'll have something like this:
'
SELECT myField
FROM myTable
WHERE
this1=that1 AND
this2=that2 AND
:
:
thisN=thatN
'
- held as a value within a Text column. How do I run that query?
Ideally, I'd like to do all of this without breaking into programming
C#, or anything like that. I can do all of the difficult construction
bit in SQL already (to build the query strings), so I just want to do
something like call a stored procedure (I'm not averse to a bit of
complexity in the SP). How would I go about that - or is their an
easier way?
Any advice gratefully received. Last time I asked here, everyone was
very helpful, and it got me over the previous problem, so I'm very
optimistic!
Ronsugnaboris@.gmail.com (Ron) wrote in message news:<93d83728.0504050957.324dbf4a@.posting.goog
le.com>...
> Hope someone can give me advice on a problem I'm having with my
> current development.
> I need to build up very long pieces of SQL, then execute them. The SQL
> itself is dependent on the structure of the data in 2 other DBs. I was
> using varchar(8000) fields to accumulate the dynamic SQL, as I didn't
> realize at the time how big it could grow. Each piece of SQL is just
> is single SELECT, but a huge one. I reckon that the size could grow to
> arounf 100k characters.
> To try to get around the varchar limitation, I'm now accumulating the
> SQL in a Text column in a DB table (since I can't use temporary
> variables of data type Text), and that's all going fine. What I'm not
> sure about is how to execute the SQL once I've finished accumulating
> it.
> So, I'll have something like this:
> '
> SELECT myField
> FROM myTable
> WHERE
> this1=that1 AND
> this2=that2 AND
> :
> :
> thisN=thatN
> '
> - held as a value within a Text column. How do I run that query?
> Ideally, I'd like to do all of this without breaking into programming
> C#, or anything like that. I can do all of the difficult construction
> bit in SQL already (to build the query strings), so I just want to do
> something like call a stored procedure (I'm not averse to a bit of
> complexity in the SP). How would I go about that - or is their an
> easier way?
> Any advice gratefully received. Last time I asked here, everyone was
> very helpful, and it got me over the previous problem, so I'm very
> optimistic!
> Ron
I still haven't found a way of doing this. Can anyone help?|||Hi
You may want to look at the undocumented sp_execresultset.
John
"Ron" wrote:
> sugnaboris@.gmail.com (Ron) wrote in message news:<93d83728.0504050957.324d
bf4a@.posting.google.com>...
> I still haven't found a way of doing this. Can anyone help?
>|||sugnaboris@.gmail.com (Ron) wrote in
news:93d83728.0504050957.324dbf4a@.posting.google.com:
> Hope someone can give me advice on a problem I'm having with my
> current development.
> I need to build up very long pieces of SQL, then execute them. The SQL
> itself is dependent on the structure of the data in 2 other DBs. I was
> using varchar(8000) fields to accumulate the dynamic SQL, as I didn't
> realize at the time how big it could grow. Each piece of SQL is just
> is single SELECT, but a huge one. I reckon that the size could grow to
> arounf 100k characters.
> To try to get around the varchar limitation, I'm now accumulating the
> SQL in a Text column in a DB table (since I can't use temporary
> variables of data type Text), and that's all going fine. What I'm not
> sure about is how to execute the SQL once I've finished accumulating
> it.
> So, I'll have something like this:
> '
> SELECT myField
> FROM myTable
> WHERE
> this1=that1 AND
> this2=that2 AND
> :
> :
> thisN=thatN
> '
> - held as a value within a Text column. How do I run that query?
> Ideally, I'd like to do all of this without breaking into programming
> C#, or anything like that. I can do all of the difficult construction
> bit in SQL already (to build the query strings), so I just want to do
> something like call a stored procedure (I'm not averse to a bit of
> complexity in the SP). How would I go about that - or is their an
> easier way?
> Any advice gratefully received. Last time I asked here, everyone was
> very helpful, and it got me over the previous problem, so I'm very
> optimistic!
> Ron
Try the sp_executesql system stored procedure.
Rumble
"Write something worth reading, or do something worth writing."
-- Benjamin Franklin|||Ron,
I have never been able to reach the limit on the EXEC statement, and I've
thrown things like 500k at it. You can concatenate quite a lot of nvarchars
together to do what you need to do:
declare @.buffer1 nvarchar(4000)
...
declare @.buffer100 nvarchar(4000)
exec (@.buffer1+@.buffer2 + ... + @.buffer100)
Of course, splitting a TEXT into NVARCHAR is a whole diferent topic ... post
a new question with how to do that if you have trouble ...
-- Alex Papadimoulis
SQL
"Ron" wrote:
> Hope someone can give me advice on a problem I'm having with my
> current development.
> I need to build up very long pieces of SQL, then execute them. The SQL
> itself is dependent on the structure of the data in 2 other DBs. I was
> using varchar(8000) fields to accumulate the dynamic SQL, as I didn't
> realize at the time how big it could grow. Each piece of SQL is just
> is single SELECT, but a huge one. I reckon that the size could grow to
> arounf 100k characters.
> To try to get around the varchar limitation, I'm now accumulating the
> SQL in a Text column in a DB table (since I can't use temporary
> variables of data type Text), and that's all going fine. What I'm not
> sure about is how to execute the SQL once I've finished accumulating
> it.
> So, I'll have something like this:
> '
> SELECT myField
> FROM myTable
> WHERE
> this1=that1 AND
> this2=that2 AND
> :
> :
> thisN=thatN
> '
> - held as a value within a Text column. How do I run that query?
> Ideally, I'd like to do all of this without breaking into programming
> C#, or anything like that. I can do all of the difficult construction
> bit in SQL already (to build the query strings), so I just want to do
> something like call a stored procedure (I'm not averse to a bit of
> complexity in the SP). How would I go about that - or is their an
> easier way?
> Any advice gratefully received. Last time I asked here, everyone was
> very helpful, and it got me over the previous problem, so I'm very
> optimistic!
> Ron
>|||John Bell <JohnBell@.discussions.microsoft.com> wrote in message news:<06BB67E8-16E1-4FFA-A6
3A-070307732CD0@.microsoft.com>...
> Hi
> You may want to look at the undocumented sp_execresultset.
> John
That sounds good, John, Thanks.
I've read a few articles about that stored procedure since you posted,
and I see that there's an xp_ version to this, too.
Just to confirm: I will have a column that holds Text data type
strings, which can be several tens of thousands of characters long.
Each individual value will be a SQL statement, and I want to be able
to execute the SQL statements.
Does that sound feasible with the SP you suggest?
I suppose that the way to use this would be to write a wrapper SP that
does the selection, then calls sp_execresultset, so that I don't see
the varchar(8000) limit from Query Analyzer?|||Thanks, Alex. I think that I can work out how to split the Text value -
but I'll certainly get back onto this newsgroup if it defeats me.
At the moment, I have a pair of nested cursors in the script that
generates the SQL (which is held in the TEXT column). I'm interrogating
some existing databases by looking over some of their objects, and
drilling down to analyze them, so the outer cursor handles tables, and
the inner one handles columns. I suppose that I could chunk the
generated SQL up into different VARCHAR variables as I'm generating;
but I have two misgivings about that:
1) It would contaminate the logic of the generation of the SQL with the
details of how the SQL is to be run, and it feels wrong to mix up those
separate concerns; and
2) I'm not sure if the Query Analyzer limit would apply to running the
EXEC with the concatentation of VARCHARs.
So I think that I'll continue to generate the entire SQL query as a
TEXT value, then read it out and execute it within a stored procedure.
Does that sound OK? It should make the overall process much cleaner,
logically, at the negligible cost of writing a very simple SP. (He
said, before he tried it...)
Thanks again!
Ron|||Thanks very much for your suggestion.
The parameter this SP takes is a unicode string that's too short for
what I need to do, I think. However, I did learn a bit more about
running dynamic SQL while following up on this, so that's been useful!
Ron
