Please give me sample coding for "exporting table to excel file".

Thanks !
You could go down the OA_Create route - I believe this was originally cribbed
from www.swynk.org.
CREATE PROCEDURE ExportToExcel (
@.server sysname = null,
@.uname sysname = null,
@.pwd sysname = null,
@.QueryText varchar(200) = null,
@.filename varchar(200) = 'C:\NorthwindCategories.xls'
)
AS
DECLARE @.SQLServer int,
@.QueryResults int,
@.CurrentResultSet int,
@.object int,
@.WorkBooks int,
@.WorkBook int,
@.Range int,
@.hr int,
@.Columns int,
@.Rows int,
@.indColumn int,
@.indRow int,
@.off_Column int,
@.off_Row int,
@.code_str varchar(100),
@.result_str varchar(255)
IF @.QueryText IS NULL
BEGIN
PRINT 'Set the query string'
RETURN
END
-- Sets the server to the local server
IF @.server IS NULL SELECT @.server = @.@.servername
-- Sets the username to the current user name
IF @.uname IS NULL SELECT @.uname = SYSTEM_USER
SET NOCOUNT ON
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.SQLServer OUT
IF @.hr <> 0
BEGIN
PRINT 'error create SQLDMO.SQLServer'
RETURN
END
-- Connect to the SQL Server
IF @.pwd IS NULL
BEGIN
EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname
IF @.hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
ELSE
BEGIN
EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname, @.pwd
IF @.hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
PRINT @.QueryText
SELECT @.result_str = 'ExecuteWithResults("' + @.QueryText + '")'
EXEC @.hr = sp_OAMethod @.SQLServer, @.result_str, @.QueryResults OUT
IF @.hr <> 0
BEGIN
PRINT 'error with method ExecuteWithResults'
RETURN
END
EXEC @.hr = sp_OAMethod @.QueryResults, 'CurrentResultSet', @.CurrentResultSet
OUT
IF @.hr <> 0
BEGIN
PRINT 'error get CurrentResultSet'
RETURN
END
EXEC @.hr = sp_OAMethod @.QueryResults, 'Columns', @.Columns OUT
IF @.hr <> 0
BEGIN
PRINT 'error get Columns'
RETURN
END
EXEC @.hr = sp_OAMethod @.QueryResults, 'Rows', @.Rows OUT
IF @.hr <> 0
BEGIN
PRINT 'error get Rows'
RETURN
END
EXEC @.hr = sp_OACreate 'Excel.Application', @.object OUT
IF @.hr <> 0
BEGIN
PRINT 'error create Excel.Application'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.object, 'WorkBooks', @.WorkBooks OUT
IF @.hr <> 0
BEGIN
PRINT 'error create WorkBooks'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.WorkBooks, 'Add', @.WorkBook OUT
IF @.hr <> 0
BEGIN
PRINT 'error with method Add'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.object, 'Range("A1")', @.Range OUT
IF @.hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
SELECT @.indRow = 1
SELECT @.off_Row = 0
SELECT @.off_Column = 1
WHILE (@.indRow <= @.Rows)
BEGIN
SELECT @.indColumn = 1
WHILE (@.indColumn <= @.Columns)
BEGIN
EXEC @.hr = sp_OAMethod @.QueryResults, 'GetColumnString', @.result_str OUT,
@.indRow, @.indColumn
IF @.hr <> 0
BEGIN
PRINT 'error get GetColumnString'
RETURN
END
EXEC @.hr = sp_OASetProperty @.Range, 'Value', @.result_str
IF @.hr <> 0
BEGIN
PRINT 'error set Value'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.Range, 'Offset', @.Range OUT, @.off_Row,
@.off_Column
IF @.hr <> 0
BEGIN
PRINT 'error get Offset'
RETURN
END
SELECT @.indColumn = @.indColumn + 1
END
SELECT @.indRow = @.indRow + 1
SELECT @.code_str = 'Range("A' + LTRIM(str(@.indRow)) + '")'
EXEC @.hr = sp_OAGetProperty @.object, @.code_str, @.Range OUT
IF @.hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
END
SELECT @.result_str = 'exec master..xp_cmdshell ''del ' + @.filename + ''',
no_output'
EXEC(@.result_str)
SELECT @.result_str = 'SaveAs("' + @.filename + '")'
EXEC @.hr = sp_OAMethod @.WorkBook, @.result_str
IF @.hr <> 0
BEGIN
PRINT 'error with method SaveAs'
RETURN
END
EXEC @.hr = sp_OAMethod @.WorkBook, 'Close'
IF @.hr <> 0
BEGIN
PRINT 'error with method Close'
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT 'error destroy Excel.Application'
RETURN
END
EXEC @.hr = sp_OADestroy @.SQLServer
IF @.hr <> 0
BEGIN
PRINT 'error destroy SQLDMO.SQLServer'
RETURN
END
GO
"SOHO" wrote:

>
> --
> Thanks !
>
>
|||Not an actual xls file, but excell will treat it like one and it will be
associated with excell on most users machines
Read KB#890775 first.
****************************
declare @.cmd varchar(255)
select @.cmd =
'bcp "pubs.dbo.authors" out "c:\authors.csv" -c -t"," -r\n -U"sa" -P""'
exec master.dbo.xp_cmdshell @.cmd
****************************
kcwms
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:%23ZWTPpkdFHA.1040@.TK2MSFTNGP10.phx.gbl...
>
> --
> Thanks !
>
>
|||Thanks for your reply.
Thanks !
"BarryC" <BarryC@.discussions.microsoft.com> glsD:4A433E6C-3276-466E-8B52-5EC3DD615DA2@.microsoft.com...[vbcol=seagreen]
> You could go down the OA_Create route - I believe this was originally
> cribbed
> from www.swynk.org.
> CREATE PROCEDURE ExportToExcel (
> @.server sysname = null,
> @.uname sysname = null,
> @.pwd sysname = null,
> @.QueryText varchar(200) = null,
> @.filename varchar(200) = 'C:\NorthwindCategories.xls'
> )
> AS
> DECLARE @.SQLServer int,
> @.QueryResults int,
> @.CurrentResultSet int,
> @.object int,
> @.WorkBooks int,
> @.WorkBook int,
> @.Range int,
> @.hr int,
> @.Columns int,
> @.Rows int,
> @.indColumn int,
> @.indRow int,
> @.off_Column int,
> @.off_Row int,
> @.code_str varchar(100),
> @.result_str varchar(255)
> IF @.QueryText IS NULL
> BEGIN
> PRINT 'Set the query string'
> RETURN
> END
> -- Sets the server to the local server
> IF @.server IS NULL SELECT @.server = @.@.servername
> -- Sets the username to the current user name
> IF @.uname IS NULL SELECT @.uname = SYSTEM_USER
> SET NOCOUNT ON
> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.SQLServer OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create SQLDMO.SQLServer'
> RETURN
> END
> -- Connect to the SQL Server
> IF @.pwd IS NULL
> BEGIN
> EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname
> IF @.hr <> 0
> BEGIN
> PRINT 'error Connect'
> RETURN
> END
> END
> ELSE
> BEGIN
> EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname,
> @.pwd
> IF @.hr <> 0
> BEGIN
> PRINT 'error Connect'
> RETURN
> END
> END
> PRINT @.QueryText
> SELECT @.result_str = 'ExecuteWithResults("' + @.QueryText + '")'
> EXEC @.hr = sp_OAMethod @.SQLServer, @.result_str, @.QueryResults OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method ExecuteWithResults'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.QueryResults, 'CurrentResultSet',
> @.CurrentResultSet
> OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error get CurrentResultSet'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.QueryResults, 'Columns', @.Columns OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error get Columns'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.QueryResults, 'Rows', @.Rows OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error get Rows'
> RETURN
> END
> EXEC @.hr = sp_OACreate 'Excel.Application', @.object OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create Excel.Application'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.object, 'WorkBooks', @.WorkBooks OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create WorkBooks'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.WorkBooks, 'Add', @.WorkBook OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method Add'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.object, 'Range("A1")', @.Range OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create Range'
> RETURN
> END
> SELECT @.indRow = 1
> SELECT @.off_Row = 0
> SELECT @.off_Column = 1
> WHILE (@.indRow <= @.Rows)
> BEGIN
> SELECT @.indColumn = 1
> WHILE (@.indColumn <= @.Columns)
> BEGIN
> EXEC @.hr = sp_OAMethod @.QueryResults, 'GetColumnString', @.result_str OUT,
> @.indRow, @.indColumn
> IF @.hr <> 0
> BEGIN
> PRINT 'error get GetColumnString'
> RETURN
> END
> EXEC @.hr = sp_OASetProperty @.Range, 'Value', @.result_str
> IF @.hr <> 0
> BEGIN
> PRINT 'error set Value'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.Range, 'Offset', @.Range OUT, @.off_Row,
> @.off_Column
> IF @.hr <> 0
> BEGIN
> PRINT 'error get Offset'
> RETURN
> END
> SELECT @.indColumn = @.indColumn + 1
> END
> SELECT @.indRow = @.indRow + 1
> SELECT @.code_str = 'Range("A' + LTRIM(str(@.indRow)) + '")'
> EXEC @.hr = sp_OAGetProperty @.object, @.code_str, @.Range OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create Range'
> RETURN
> END
> END
> SELECT @.result_str = 'exec master..xp_cmdshell ''del ' + @.filename + ''',
> no_output'
> EXEC(@.result_str)
> SELECT @.result_str = 'SaveAs("' + @.filename + '")'
> EXEC @.hr = sp_OAMethod @.WorkBook, @.result_str
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method SaveAs'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.WorkBook, 'Close'
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method Close'
> RETURN
> END
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> PRINT 'error destroy Excel.Application'
> RETURN
> END
> EXEC @.hr = sp_OADestroy @.SQLServer
> IF @.hr <> 0
> BEGIN
> PRINT 'error destroy SQLDMO.SQLServer'
> RETURN
> END
> GO
>
> "SOHO" wrote:
|||Thanks for your reply.
Thanks !
"kcwms" <noneOfYoBusiness@.me.net> glsD:eBCSdQndFHA.3184@.TK2MSFTNGP15.phx.g bl...
> Not an actual xls file, but excell will treat it like one and it will be
> associated with excell on most users machines
> Read KB#890775 first.
> ****************************
> declare @.cmd varchar(255)
> select @.cmd =
> 'bcp "pubs.dbo.authors" out "c:\authors.csv" -c -t"," -r\n -U"sa" -P""'
> exec master.dbo.xp_cmdshell @.cmd
> ****************************
> kcwms
>
> "SOHO" <hkwin2000@.hotmail.com> wrote in message
> news:%23ZWTPpkdFHA.1040@.TK2MSFTNGP10.phx.gbl...
>