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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 11/01/2001 :  14:07:35  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Rob, is this considered solved?

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

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 11/01/2001 :  14:30:46  Show Profile  Visit robvolk's Homepage  Reply with Quote
I haven't tried Ilya's solution yet, but it sure looks good! I'll try it out when work calms down a little bit and I actually have some time.

However, if you've got any other avenues that you think might be fruitful, please let me know how they turn out. It's just as helpful for me to know what doesn't work as what does.

The original intent was just to be able to EXEC a big SQL string, >8000 bytes. But another option is that the SQL Server XML sproc's can also accept an XML document as a text parameter, and again, I think it's unbelieveably stupid that you can't pass the contents of a text column into a sproc that can accept a text parameter, but only as a literal text value.

I'm very interested in being able to do this using pure T-SQL, just to avoid adding an ActiveX layer or step simply to import data. Any insight on this is also welcome (I'm not sure Ilya's solution will work for this XML part, though.)

Go to Top of Page

mfemenel
Professor Frink

USA
1421 Posts

Posted - 11/01/2001 :  14:53:43  Show Profile  Visit mfemenel's Homepage  Reply with Quote
Notice how he can't pass up a chance to rant? Very funny.

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 - 11/01/2001 :  15:11:00  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Well, he's got a good point. What good is it to be able to pass only a literal value?

I don't know much about XML, but it sounds good. If I get some time this afternoon I'll keep hacking away at my earlier ideas.

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

izaltsman
A custom title

USA
1139 Posts

Posted - 11/01/2001 :  18:09:59  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
quote:

Well, he's got a good point. What good is it to be able to pass only a literal value?



I don't believe this feature (stored procedures receiving arguments of datatype text) was ever meant to be used from within T-SQL. I think it was introduced to accomodate other applications, that are trying to stuff BLOBs into the database. But SQL Server itself was never designed to manipulate BLOBs. I am guessing that is the reason why T-SQL doesn't offer much beyond reading and writing/updating BLOB datatypes.


Edited by - izaltsman on 11/01/2001 18:11:02
Go to Top of Page

suinfall
Starting Member

1 Posts

Posted - 06/14/2004 :  12:10:51  Show Profile  Reply with Quote
For whatever reason, my 'pivot' table records are quite big and it exceeded 8000 for @select argument. it truncated and failed to execute. Please help!

Suinfall



================================================== ========
CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.08 seconds. Powered By: Snitz Forums 2000