Hello everyone, I am interested in creating a stored procedure that can
extract a column from a table and put it into a comma delimited list. Can
someone give me some examples of how this can be done. Thanks in advance for
any help.
Message posted via http://www.webservertalk.comLook at this example:
http://milambda.blogspot.com/2005/0...s-as-array.html
And mind the warnings, please.
Maybe you could also tell us what it is that you're actually trying to
achieve?
ML
http://milambda.blogspot.com/|||In general, a good approach is to bring the return the resultset to the
client and massage the data to appropriate display format. As far as
workarounds for doing it at the server, you can check out the following
links:
( For 2005 )
http://groups.google.com/group/micr...br />
9b9b968a
( For 2000 only )
http://groups.google.com/group/micr...br />
6dd9e73e
Anith|||----
---
CREATE PROCEDURE sp_exportcolumn(
@.p_servername VARCHAR(500) = NULL ,
@.p_databasename VARCHAR(500)= NULL,
@.p_tablename VARCHAR(500)= NULL,
@.p_columnname VARCHAR(500)= NULL,
@.p_outputfile VARCHAR(500)= NULL
)
AS
BEGIN
DECLARE @.v_sql AS VARCHAR(4000)
DECLARE @.v_cmd AS VARCHAR(8000)
/*
-- use these to test the script prior to passing parameters in.
SET @.p_columnname = 'mycolumname'
SET @.p_databasename = 'mydbname'
SET @.p_tablename = 'mytable'
SET @.p_outputfile = 'c:\folderpath\testdynamicbcp.txt'
SET @.p_servername = 'server.domain'
*/
SET @.v_sql = 'SELECT TOP 100 ' + @.p_columnname+' FROM
['+@.p_databasename+'].[dbo].['+@.p_tablename+'] '
SET @.v_cmd = 'bcp "'+@.v_sql+'" queryout "'+ @.p_outputfile+'" -S "'+
@.p_servername +'" -c -b 100000 -m 20000 -T '
PRINT @.v_cmd
EXEC master..xp_cmdshell @.v_cmd
END
----
---
Below you can test without passing parameters if you uncomment the
default values...
otherwise pass in the parameters...
----
---
EXEC sp_exportcolumn
OR
EXEC sp_exportcolumn @.p_servername ,@.p_databasename , @.p_tablename ,
@.p_columnname , @.p_outputfile|||I apologize for the broadness of my request. What I am trying to do is
extract email addresses from a column and then paste them into a web page
that will send out the emails with an attachment. The webpage calls for the
email addresses to be separated by a comma.
ML wrote:
>Look at this example:
>http://milambda.blogspot.com/2005/0...s-as-array.html
>And mind the warnings, please.
>Maybe you could also tell us what it is that you're actually trying to
>achieve?
>ML
>--
>http://milambda.blogspot.com/
Message posted via http://www.webservertalk.com|||The previous code outputted the list with CRLF as separator...For your
purpose you may want
To change the @.v_cmd to give "," as the row terminator, since that is
the format you want in at end...
Also, consider limiting the data by adding a where clause and data
validation if data is not already clean.
SET @.v_cmd = 'bcp "'+@.v_sql+'" queryout "'+ @.p_outputfile+'" -S "'+
@.p_servername +'" -c -b 100000 -m 20000 -T -r ","'|||Thanks for the advice. I give this a try.
dizzler wrote:
>The previous code outputted the list with CRLF as separator...For your
>purpose you may want
>To change the @.v_cmd to give "," as the row terminator, since that is
>the format you want in at end...
>Also, consider limiting the data by adding a where clause and data
>validation if data is not already clean.
>SET @.v_cmd = 'bcp "'+@.v_sql+'" queryout "'+ @.p_outputfile+'" -S "'+
>@.p_servername +'" -c -b 100000 -m 20000 -T -r ","'
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200604/1
No comments:
Post a Comment