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 an external process() in SQLCLR Project

I can create an external text file from within the SQLCLR project, but I cannot run an external executable. Just in case you are asking, I need to do this to push data into a legacy application using a different DB format. I have found it best to simply use my old language (Clipper) for data validation, etc. - and especially since I require multiple indices to be open. So, if you could just take my word on this.

The following code:

Process newProcess = new Process();

string path = @."C:\TEST.BAT";

newProcess.StartInfo.FileName = path;

newProcess.Start();

Executes without error, but does not actually run the external.

Now, I can have a DOS (Clipper) application poll a directory for text files, but I am trying to get away from all these "mini" data transformation applications. If an exception is caused in the DOS app, a remote user on the other side of the country has no idea it is broke and his data (coming from a SQL Mobile Device) never gets to our legacy database structure.

So, am I out of luck?

Is your assembly deployed at the UNSAFE permission level? What happens if you try to run the same code in a console app run under the same user account (SQL Server service account or impersonated account, as appropriate)?|||

Hi Nicole - thx for responding.

I am able to execute a console app - the issue is that SQL Server will not allow touching any network drive or network resource period (from a SQLCLR Project). So my cmd app can update a DB on the C drive, but not one of my network drives.

As well, I can establish an OLEDB connection (Visual Foxpro) to a local directory, but not a network directory. I'm surprised that CLR will not even allow an external app to touch a network resource. Wild, eh? Although I understand why there is such security, there must be a way for me to execute an external console app that can update a DB on a network resource.

Oh - and yes, I am running the assembly at the UNSAFE permission level.

|||Might the problem be the user context rather than anything having to do with SQLCLR? Unless you deliberately impersonate another user (e.g.: via SqlContext.WindowsIdentity.Impersonate), your SQLCLR code will run under the user context of the SQL Server service account, which is highly unlikely to have any permissions on any network resources.|||

I think you're correct - it has to do with attempting to authenticate a local user (IUSR_Computername) on a network resource. I will look into impersonation (something my wife says I'm horrible at...)

You know, Nicole, you are the first person to assist me in these forums. I had actually thought of using a female handle - seems they get a pretty quick response... :)

I'll post the results of my efforts... and thx again.

|||

After impersonation, I can do something simple like use a streamwriter to create a text file on a network share. But it still throws an exception when attempting to execute an executable on the same network share. The impersonation rights are those of administrator (only for testing!). Oh, in case you're wondering - I simply change the Process StartInfo from @."C:\Mobile.exe" to @."F:\Mobile.exe", and you will have to take my word for it that the Mobile exists in both places. It runs without exception on the C drive.

What's really wild, is that the actual Mobile.exe code (clipper) will update a dbf on the C drive, but not on the F drive. What does the CLR do? Freeze all resources when in process? geez!

I'm also using:

[PermissionSetAttribute(SecurityAction.Demand, Name = "FullTrust")]

So... I really thought this would allow my CLR code to access a network share. I can't understand why the answer to this wouldn't be just a little more simple, even for an old programmer like me.

Executing A File In Sql Query Analyzer

I Had A Text File In Which There Are Sqlstatements.i Want To Execute The Entire File In Query Analyzer.what Command I Must Use For That?

Quote:

Originally Posted by megastar5

I Had A Text File In Which There Are Sqlstatements.i Want To Execute The Entire File In Query Analyzer.what Command I Must Use For That?

Open the file in Query Analyzer, then hit F5.

execute sql statements in text file

I am new to batch files. I have a text file that has several INSERT/UPDATE
sql statements. I would like to create a batch file to execute the sql
statements in the text file on my server. I am not having any success with
this. I thought I could create a batch file using osql that executes the sql
statements in text file but it's not working and I have no idea what I'm
doing wrong. Maybe there is a better way to do this? Any suggestions are
welcomed.
I am using the following:
osql -S server -d database -E -I file pathname
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1> osql -S server -d database -E -I file pathname
Change -I to -i
Linchi
"fullertee via SQLMonster.com" wrote:
> I am new to batch files. I have a text file that has several INSERT/UPDATE
> sql statements. I would like to create a batch file to execute the sql
> statements in the text file on my server. I am not having any success with
> this. I thought I could create a batch file using osql that executes the sql
> statements in text file but it's not working and I have no idea what I'm
> doing wrong. Maybe there is a better way to do this? Any suggestions are
> welcomed.
> I am using the following:
> osql -S server -d database -E -I file pathname
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1
>|||Thanks Linchi
Sorry that was just a typo. It not working with the i.
osql -S server -d database -E -i file pathname
fullertee
Linchi Shea wrote:
>> osql -S server -d database -E -I file pathname
>Change -I to -i
>Linchi
>> I am new to batch files. I have a text file that has several INSERT/UPDATE
>> sql statements. I would like to create a batch file to execute the sql
>[quoted text clipped - 5 lines]
>> I am using the following:
>> osql -S server -d database -E -I file pathname
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Error message? Can you post the exact command you execute?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fullertee via SQLMonster.com" <u21934@.uwe> wrote in message news:6066962294ae1@.uwe...
> Thanks Linchi
> Sorry that was just a typo. It not working with the i.
> osql -S server -d database -E -i file pathname
> fullertee
> Linchi Shea wrote:
>> osql -S server -d database -E -I file pathname
>>Change -I to -i
>>Linchi
>> I am new to batch files. I have a text file that has several INSERT/UPDATE
>> sql statements. I would like to create a batch file to execute the sql
>>[quoted text clipped - 5 lines]
>> I am using the following:
>> osql -S server -d database -E -I file pathname
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Thank you so much. Your question made me look at my bat file closer. I was
referencing the wrong database. Thanks! It's working now.
or Karaszi wrote:
>Error message? Can you post the exact command you execute?
>> Thanks Linchi
>> Sorry that was just a typo. It not working with the i.
>[quoted text clipped - 12 lines]
>> I am using the following:
>> osql -S server -d database -E -I file pathname
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1

execute sql statements in text file

I am new to batch files. I have a text file that has several INSERT/UPDATE
sql statements. I would like to create a batch file to execute the sql
statements in the text file on my server. I am not having any success with
this. I thought I could create a batch file using osql that executes the sql
statements in text file but it's not working and I have no idea what I'm
doing wrong. Maybe there is a better way to do this? Any suggestions are
welcomed.
I am using the following:
osql -S server -d database -E -I file pathname
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1> osql -S server -d database -E -I file pathname
Change -I to -i
Linchi
"fullertee via droptable.com" wrote:

> I am new to batch files. I have a text file that has several INSERT/UPDAT
E
> sql statements. I would like to create a batch file to execute the sql
> statements in the text file on my server. I am not having any success wit
h
> this. I thought I could create a batch file using osql that executes the s
ql
> statements in text file but it's not working and I have no idea what I'm
> doing wrong. Maybe there is a better way to do this? Any suggestions are
> welcomed.
> I am using the following:
> osql -S server -d database -E -I file pathname
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1
>|||Thanks Linchi
Sorry that was just a typo. It not working with the i.
osql -S server -d database -E -i file pathname
fullertee
Linchi Shea wrote:[vbcol=seagreen]
>Change -I to -i
>Linchi
>
>[quoted text clipped - 5 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||Error message? Can you post the exact command you execute?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fullertee via droptable.com" <u21934@.uwe> wrote in message news:6066962294ae1@.uwe...[vbcol
=seagreen]
> Thanks Linchi
> Sorry that was just a typo. It not working with the i.
> osql -S server -d database -E -i file pathname
> fullertee
> Linchi Shea wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1[/vbcol]|||Thank you so much. Your question made me look at my bat file closer. I was
referencing the wrong database. Thanks! It's working now.
or Karaszi wrote:[vbcol=seagreen]
>Error message? Can you post the exact command you execute?
>
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1

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:
>

Execute process task

I want to pass in a text file to execute process task.Is it possible?

You can pass the location of the file to a process. For example, in the execute process task, you can set the following properties:

Executable: notepad.exe

Arguments: abc.txt

WorkingDirectory: C:\temp

When you execute the task, notepad will attempt to open abc.txt from C:\temp folder.

Execute permission cannot be acquired?

I have an assembly which uses file I/O to stream in some text based reports
into a container in a report. I have set the FileIOPermissionAttribute and
it works in the design environment but when I deploy the dll to the report
server it only works on that box. If I try to run it through the preview
window I get the following error: "Execute permission cannot be acquired"
Anybody help?Did you get this resolved? Assuming that the security policies are updated
correctly and you assert the FileIOPermission in the custom assembly, it
could be a file system permission issue. Basically, the executing user does
not have permissions to access the file. Check the NTFS permissions on the
file.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:3FA6A470-1E7D-421D-AF3E-226ADAC397D9@.microsoft.com...
> I have an assembly which uses file I/O to stream in some text based
reports
> into a container in a report. I have set the FileIOPermissionAttribute
and
> it works in the design environment but when I deploy the dll to the report
> server it only works on that box. If I try to run it through the preview
> window I get the following error: "Execute permission cannot be acquired"
> Anybody help?
>

Execute Non Query: Command Text Property has not been initialized

I get this when I delete a row then save data in a data grid view control.
Hints to fix ?Hello
This is not the best group for this problem; you will get better
answers in a .Net group, for example
microsoft.public.dotnet.framework.adonet or
microsoft.public.dotnet.datatools.
It's not my domain, but I suppose that you should check the
DeleteCommand property of the corresponding TableAdapter in the
appropriate DataSet.
Razvan