Showing posts with label exporting. Show all posts
Showing posts with label exporting. Show all posts

Friday, March 23, 2012

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...
>
Posted by subtractionpasx at 8:01 AM 0 comments
Labels: , , , , , , , , , , ,

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> ¼¶¼g©ó¶l¥ó·s»D:4A433E6C-3276-466E-8B52-5EC3DD615DA2@.microsoft.com...
> 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 !
>>
>>|||Thanks for your reply.
--
Thanks !
"kcwms" <noneOfYoBusiness@.me.net> ¼¶¼g©ó¶l¥ó·s»D:eBCSdQndFHA.3184@.TK2MSFTNGP15.phx.gbl...
> 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 !
>>
>
Posted by subtractionpasx at 8:01 AM 0 comments
Labels: , , , , , , , , , , ,

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

Please give me sample coding for "exporting table to excel file".You could go down the OA_Create route - I believe this was originally cribbe
d
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-5EC3DD615
DA2@.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.gbl...[vb
col=seagreen]
> 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...
>[/vbcol]
Posted by subtractionpasx at 8:00 AM 0 comments
Labels: , , , , , , , , , , ,
Subscribe to: Posts (Atom)