| Author |
 Topic  |
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
 Posted - 11/01/2001 : 14:07:35
|
Rob, is this considered solved?
-------------------------------------------------------------------
Make mine a Vienti quad-shot half-skinny no foam with vanilla... |
 |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 11/01/2001 : 14:30:46
|
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.)
|
|
|
|
mfemenel
Professor Frink
USA
1414 Posts |
Posted - 11/01/2001 : 14:53:43
|
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."
|
|
|
|
AjarnMark
SQL Slashing Gunting Master
USA
3246 Posts |
Posted - 11/01/2001 : 15:11:00
|
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... |
|
|
|
izaltsman
A custom title
USA
1139 Posts |
Posted - 11/01/2001 : 18:09:59
|
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 |
|
|
|
suinfall
Starting Member
1 Posts |
Posted - 06/14/2004 : 12:10:51
|
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 |
|
|
|
Topic |
|
|
|
Text data question
