SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Text data question
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 10/30/2001 :  13:43:44  Show Profile  Visit robvolk's Homepage  Reply with Quote
Anyone have any luck passing the value of a text column into a stored procedure?

Yes, it HAS to be a text variable I can pass to a stored procedure. I won't be able to SELECT it or READTEXT it within a sproc.

Yes, I DO have to keep it in a text column in a table (unless you can tell me how to manipulate a text VARIABLE).

Yes, I DO need to do this entirely within T-SQL (no ADO solutions please).

If I'm missing something obvious I expect to be slapped hard by the SQL Team bunch. Can't figure this out and it's pissing me off.

Any help is unbelieveably appreciated, you really cannot believe how much! Thanks.

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 10/31/2001 :  15:21:44  Show Profile  Visit robvolk's Homepage  Reply with Quote
No luck yet? Mike Femenella had a solution, but it didn't quite work , God knows he made a more than valiant effort.

Go to Top of Page

PiecesOfEight
Posting Yak Master

USA
200 Posts

Posted - 10/31/2001 :  15:34:58  Show Profile  Reply with Quote
Can we see the partial solution? I know he put in 6 hours, so there must be some interesting stuff in there.

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/31/2001 :  15:39:12  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
I knew he had put in 6 hours too. How did I know that? Wasn't there a post here from him? Rob did you magically delete his post, or is there a glitch in the system?

-------------------------------------------------------------------
Make mine a Vienti quad-shot half-skinny no foam with vanilla...
Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 10/31/2001 :  15:42:08  Show Profile  Visit mfemenel's Homepage  Reply with Quote
Well, first. Where's my post here from last night? Ok. I'll explain what I was going for, so maybe it spurs some ideas for someone else. First, I created a test table with a text field and put in a small sql command just to test it out(select * from customers) Second, break apart the text field into smaller pieces so that they fit into a varchar(8000). Third, dynamically build a temporary stored procedure to hold the information and execute the seperate pieces, i.e. @variable1, @variable2 etc. My solution dies in the fact that in the end, I'm still limited to the number of characters I can finally execute. Take a look, maybe it'll give someone a place to start. This one hurts.

--Holds values of the text field broken into 8000 char increments
Create table ##temp(
sequence int identity(1,1),
value varchar(8000)
)
Set nocount on
Declare @execstring varchar(8000)
Declare @maxlength int
select @maxlength=datalength(testtext) from testtable
declare @offset int
Declare @length int
set @length=1
set @offset=1
--Parse through the text field and break it up into chunks and insert into #temp
While @length<=@maxlength + 3
begin
insert into ##temp(value) select substring(testtext,@offset,3) from testtable
set @offset=@offset + 3
set @length=@length + 3
End
select @maxlength=count(*) from ##temp
set @offset=1

Declare @mystring nvarchar(200)
declare @string2 nvarchar(500)
declare @variablestring nvarchar(600)
declare @selects as nvarchar(4000)
declare @finalstring as nvarchar(4000)
declare @workstring nvarchar(4000)
set @string2=''
set @selects=''
set @finalstring=''
set @workstring=' set @finalstring='
set @variablestring='create procedure ##execme as Declare @finalstring nvarchar(4000),'

--Now, build a temp stored procedure to hold all your dynamic values
--It's broken into 4 parts
--1. Variable string, which will hold the top of your stored procedure declaration
--2. Selects, which query #TEMP to get their values
--3. Workstring which is the final statement to execute all of your smaller pieces
--4. Final string pulls the above 3 strings together and creates the procedure.
while @offset<>@maxlength
begin

--If it's the final variable, we don't want commas and seperating punctuation
if @offset=@maxlength-1
begin
--Build the create procedure string
set @variablestring=@variablestring + ' @myvar' + cast(@offset as varchar(30)) + ' varchar(8000)'
--Build select statements
set @selects=@selects + 'select @myvar' + cast(@offset as varchar(30)) + '=value from ##temp where sequence=' + cast(@offset as varchar(30))
--Build Workstring
Set @workstring=@workstring + '@myvar' + cast(@offset as varchar(30))
End
--Otherwise, run this batch to include commas and seperating punctuation
if @offset<>@maxlength-1
begin
--Build the create procedure string
set @variablestring=@variablestring + ' @myvar' + cast(@offset as varchar(30)) + ' varchar(8000),'
--Build select statements
set @selects=@selects + ' select @myvar' + cast(@offset as varchar(30)) + '= value from ##temp where sequence=' + cast(@offset as varchar(30))
--Build Workstring
set @workstring=@workstring + '@myvar' + cast(@offset as varchar(30)) + ' + '
End
--Increment our counter
set @offset=@offset + 1
end

--Tack on a few finishing statements and pull it together
set @workstring=@workstring + ' exec sp_executesql @finalstring'
set @finalstring=@variablestring + @selects + @workstring

--Display the finished string to the results window
print @finalstring

--Build the temporary stored procedure
exec sp_executesql @finalstring

--Execute the stored procedure
exec ##execme
--Clean up
drop table ##temp
drop procedure ##execme
Set Nocount off



Mike
"A program is a device used to convert data into error messages."
Go to Top of Page

PiecesOfEight
Posting Yak Master

USA
200 Posts

Posted - 10/31/2001 :  16:05:05  Show Profile  Reply with Quote
That is a thing of beauty -- I'm sorry it doesn't work. I knew about the six hours b/c Mike mentioned that in a post on another thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10225&FORUM_ID=4&CAT_ID=3&Topic_Title=Unique Feature of SQL SERVER 2000&Forum_Title=General SQL Server

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/31/2001 :  16:07:34  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Yeah, but I remember seeing a post last night that told Rob to email him if he was still around... Or did I just imagine all that?

-------------------------------------------------------------------
Make mine a Vienti quad-shot half-skinny no foam with vanilla...
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 10/31/2001 :  16:08:44  Show Profile  Visit robvolk's Homepage  Reply with Quote
You know, being a moderator is not good if your ability to distinguish REALLY TINY ICONS is low or nonexistant. Yes, I did delete Mike's reply; however, he posted his code, so I'm sorta off the hook. (I meant to edit it)

As a little more elaboration, I'm looking to EXEC a huge SQL command, longer than 8000 characters, and found that SP's can accept text parameters, and you can EXEC a text parameter. The trick is actually GETTING a text column into a text variable. So far I have only been able to pass a literal text value, not one from a variable or column.

Go to Top of Page

PiecesOfEight
Posting Yak Master

USA
200 Posts

Posted - 10/31/2001 :  16:09:08  Show Profile  Reply with Quote
No you're not imagining things -- I forgot about that one.

Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 10/31/2001 :  16:28:35  Show Profile  Visit mfemenel's Homepage  Reply with Quote
Volk you bastard! You killed Kenny!

Mike
"A program is a device used to convert data into error messages."
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/31/2001 :  16:45:21  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
If I reference BOL, are you going to shoot me?

Maybe I don't understand the limit to Mike's solution, but can't you change it to something like EXEC (@Part1 + @Part2 + @Part3...) or EXEC (@variablestring + @selects + @workstring)? BOL says that you can get around the 8000 limit this way:

quote:
Using EXECUTE with a Character String
Use the string concatenation operator (+) to create large strings for dynamic execution. Each string expression can be a mixture of Unicode and non-Unicode data types.

Although each [N] ‘tsql_string’ or @string_variable must be less than 8,000 bytes, the concatenation is performed logically in the SQL Server parser and never materializes in memory. For example, this statement never produces the expected 16,000 concatenated character string:

EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')




-------------------------------------------------------------------
Make mine a Vienti quad-shot half-skinny no foam with vanilla...
Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 10/31/2001 :  17:00:31  Show Profile  Visit mfemenel's Homepage  Reply with Quote
Honestly, I was at the point and was getting syntax errors and threw it all into the 1 variable. If you can get it to work, do it! Here's the setup I was using in Northwind.

Create table testtable(
texttext text
)
insert testtable values('select * from customers')

That's it. Right now I have the strings set to break up at 3 characters each. I know it's close! If anyone wants to put the time in, feel free to wrap it up. I'd love to see this thing end!

Mike
"A program is a device used to convert data into error messages."
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 10/31/2001 :  17:00:37  Show Profile  Visit robvolk's Homepage  Reply with Quote
Yes. You are now tasked with a way of:

-Extracting 8000 byte chunks from a text column
-Dynamically assigning each of these chunks to a variable, actually, as many variables as needed
-Dynamically creating a (@var1 + @var2 + @var3...etc.) string to EXEC it

It kinda has to be variables too, because if you substitute the literal values of the 8000 byte chunks, you'll be stuck with embedded apostrophes, and no real way to fix them, since the string functions tend to die with >8000 bytes.

So there needs to be a loop that dynamically declares as many variables as needed, then slices up the text column and assigns them to the variable.

Did Mike say that this sucks? Well, he said it hurts, but in reality it does both!

I know, I know, I'm thinking of some other way to do it.

BTW, does anyone else think it was MONUMENTALLY STOOOOOOOPID of M$ to allow passing text parameters into a procedure yet provide ABSOLUTELY NO WAY to assign a text value to that parameter? READTEXT won't do the job, BTW, so that's not an option.

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/31/2001 :  17:34:21  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
OK, I'll take up the challenge. Back soon (I hope!)

-------------------------------------------------------------------
Make mine a Vienti quad-shot half-skinny no foam with vanilla...
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/31/2001 :  19:10:48  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
I think I see land! Oh wait, no, that's just some smoke on the water .

Rob, I'm still working on my idea, but have a question for clarification... Do you have a known max size, or does the solution have to handle up to the theoretical maximum 2 Billion chars in a text field?

P.S. No disrespect meant to either you or Mike earlier. Was hoping I had spied a simple oversight, but I'm beginning to understand the complications more. Still working on it though!

-------------------------------------------------------------------
Make mine a Vienti quad-shot half-skinny no foam with vanilla...
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/31/2001 :  20:50:53  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
quote:
As a little more elaboration, I'm looking to EXEC a huge SQL command, longer than 8000 characters,...


Rob, are you really only looking to execute a SQL command? Taking a really different track, could you do something like this?


-- These are command prompt commands...
-- BCP the Text field out to a plain text file

bcp Northwind..TestText out sqlcmd.txt -T -c

-- Call osql to run the command
osql -E -i sqlcmd.txt -o Results.txt -d Northwind -n

-- the -o parameter puts the output results in a text file.


I did this with a command that was 8132 bytes long. Whaddya think?

-------------------------------------------------------------------
Make mine a Vienti quad-shot half-skinny no foam with vanilla...
Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 11/01/2001 :  08:13:45  Show Profile  Visit mfemenel's Homepage  Reply with Quote
Hey, that's an interesting idea. I didn't even think of that one. Don't worry about the post earlier. When Rob first told me, I thought, sure, give me an hour. Little problematic wrinkles unfold as you get further into it. It seems so simple at first.

Mike
"A program is a device used to convert data into error messages."
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 11/01/2001 :  09:16:01  Show Profile  Visit robvolk's Homepage  Reply with Quote
bcp out the SQL statement and then running it with iSQL/oSQL is a great solution, I had the same idea. But....can you find a way to get the results from oSQL back into the original call?

EXEC sp_BigHonkingSQLStatementRunner not only needs to do the oSQL call, but give me back the results too. Jeez, it basically needs to bcp in the results as a table from the oSQL output, then SELECT it.

It's not imperative, but that's ultimately where I'd like to be with it. I don't imagine I need to actually execute 2 billion characters worth of SQL, but 100,000 bytes could very well be possible.

Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 11/01/2001 :  09:46:25  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
If 100,000 is the longest command you'd ever need, then you know you would never need more than 13 varchar(8000) variables. Just initialize them all to empty strings in the beginning of your proc. Then execute up to 13 READTEXT statements to populate each one (you'll need some IFs to make sure you don't attempt reading past the end of text field), and then exec (@var1+@var2...+@var13)


Edited by - izaltsman on 11/01/2001 09:48:40
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 11/01/2001 :  09:56:02  Show Profile  Visit robvolk's Homepage  Reply with Quote
How to you assign the output from READTEXT into a variable? I've tried SET and SELECT @var=READTEXT blah blah blah and it throws an error.

Go to Top of Page

izaltsman
A custom title

USA
1139 Posts

Posted - 11/01/2001 :  10:39:46  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
May not be the most graceful way, but it seems to work:


USE pubs

CREATE PROCEDURE #readmytext
@ptrval varbinary(16)
, @offset int
, @blk_size int
AS
READTEXT pub_info.pr_info @ptrval @offset @blk_size

go


create table #tmp(lineid int not null identity, formertext varchar (255))


GO
DECLARE @ptrval varbinary(16)
DECLARE @myvar varchar (255)

BEGIN TRAN

SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr WITH (HOLDLOCK) INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'

INSERT INTO #temp
EXEC #readmytext @ptrval, 1, 25

commit tran

select @myvar = formertext from #temp

print '@myvar now equals to -->' +@myvar



I am guessing that executing READTEXT from dynamic SQL (instead of readmytext stored procedure) would also work.

Edited by - izaltsman on 11/01/2001 10:56:30
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000