Executing SQL held in a Text column

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

Executing Dynamic SQL with update

I have a temp table #Temp(id int, statement varchar, result int)
The statement column contains a prebuilt sql select statement e.g
id statement
Result
1 select count(*) from books where authorname like 'A%'.
2 select count(*) from books where authorname like 'J%'.
I want run an update statement on the table so that i can set the result
column to the result of the select statement in the statement column.
e.g. update #temp
set result = [statement result]
I want to aviod cursors. Can this be done?
Please help.This is in general, a poor approach. You can use certain undocumented
procedures ( in SQL 2000 ) to get this done, but it is complex, error prone
and rarely worth it.
Instead of having SQL statements as data values & updating #temp tables,
consider using a view. Alternatively, depending on tables & wild card
patterns involved, in some cases you might be able to resolve the problem
with a single query with CASE.
If you want a workable solution, pl. refer to www.aspfaq.com/5006 and post
the required information along with a brief explanation of your
requirements.
In case you are wondering how to get the scalar result of a SELECT statement
into a variable dynamically, refer to the procedure sp_ExecuteSQL in SQL
Server Books Online.
Anith

Executing a queries contained in a column of a table IN SQL Server.

Hi,
I have a Table that contains SQL queries in one of its columns.I need to execute those query and finally want to retrive the result in another table i cannot use Cursors,Its working extremely slow, near about 1 min only for 2000 rows.Please tell me how can i minimize my time.Or any solution (without cursor) for such problem.Please help its very urgent.
e.g Say TableContainQuery(PKID,QueryField)
Now above table have 10000 of records,
I need to retrieve data from by executing queries contain in above table. I am using SQL Server.

Regards,
Dheeraj

Using SQL Server, queries that are slow are generally slow because there are not proper indexes on the tables involved.
Have you ensured that there are useful indexes on the tables involved?|||Yes,
There is a primary key in the table I suppose indexing will automatically done.
Regards,
Dheeraj Verma|||No, indexing, other than indexes you created, willnot automatically be done. If all your queries use only columns involved in the primary keys, then you are fine. However, if your query references other columns in joins or where clauses, you must add indexes yourself.

ExecuteSQL task has changed

Since the last IDW.

The column "ParameterName" has been added to the ParameterMapping tab of the ExecuteSQL task.

I enter a statement of

SELECT * FROM TABLE WHERE COLUMN = ?

I map ? to a input variable. The default name of the parameter supplied is "NewParameterName"

My task now fails with

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "SELECT * FROM TheBigOne WHERE HostName = ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
What parameter? The one the task supplies?

Allan

Allan
I was able to get passed this by putting ? in the paramter name column.

Norman P.|||I cannot get it to work even using that and it would kinda make no sense either because what if I had

SELECT X FROM TABLE WHERE DATE BETWEEN ? AND ?

What would be the Parameter Names?

Allan|||Ok so i just got it to work

say I have a statement of

SELECT AddressID
FROM Person.Address
WHERE City = ?

The parmeter name should be @.City

I could not find this anywhere in the docs.

Allan|||Allan,

Are you using a managed provider?

ash|||Allan
I tried it your way which works but also used ? in both paramter names and it also worked provided the paramters where in the correct order.

Norman P.

Execute SQL Task (catching results)

Hi,

I'm trying to use an execute SQL task with a simple select to get results and scan them.

I have to create a variable for each column to get results? or may i create something like a resultset variable?

Does this task return only one row and i have to loop manually? (maybe on al script task..) or can i get all returned data on a result set to be the input for next step?

Thanks!

Hi ya,

It depends on the result set property of the Execute sql task. The property can be set to xml, single row, full result set or none.
So whatever variable you're creating would be entirely dependent on the result set and how you want to achieve it.

Cheers

Rizwan

|||

I have set the property to "full result set", and create a variable which its type is "Object", but how can i get and scan the results in the next step?|||

You would need to point the resultset to a variable. Take a look at this article on Sql IS.

http://www.sqlis.com/58.aspx

This has how return a full row set in a variable.

Hope that helps

Cheers

RIzwan

|||

Thanks RizwanKhan,

I have read the article and its very interesting, but there is'nt nothing regarding how to manage the output (rowset).

Do you have any article about it? Can i get this rowset to be the input of another component or script?

Thanks!!

|||

You can use a For Each Loop to iterate the resultset in the control flow, or you can use a script component in the data source to "shred" the recordset.

A couple of examples (there are many, many more, if you search for them).

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/writing-a-resultset-to-a-flat-file.aspx

Execute sql scripts saved in a text column of a table

How can I execute sql scripts saved in a text column of a table?
Thanks,
PeterUse dynamic sql statement
EXAMPLE (using Northwind database)
DECLARE @.vSQL VARCHAR(1000), @.numrows INT
SELECT @.numrows = 25
SELECT @.vSQL = 'SELECT TOP ' + CONVERT(VARCHAR, @.numrows) + ' * FROM
Products ORDER BY ProductName'
EXECUTE(@.vSQL)
This evaluates to:
SELECT TOP 25 * FROM Products ORDER BY ProductName
Good luck and hope it helps!
"Peter" wrote:

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

> How can I execute sql scripts saved in a text column of a table?
>
> Thanks,
> Peter|||If one has a query exceeding 8000 characters, using dynamic SQL is the least
of the worries.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:85C96AC5-6DD2-44B8-B0EE-79737C41C94B@.microsoft.com...
> Hi,
> Go for a cursor that will loop through all the rows and get the text field
> in a variable and use dynamic SQL (EXEC or sp_Executesql )
> But again, is it a text field or varchar field?
> If its text and can span over 8000 characters.
> Then you will have to split it to 8000 char length strings, say str1, str2
> then you can use
> exec(str1 +str2)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Peter" wrote:
>