Creating Excel file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Creating Excel file

Author

Topic

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2010-12-30 : 06:41:22

Hi all,

How can I create an excel file with .xls extension on a specific path (e.g. C:/New Folder/Test.xls) through T-SQL at run time.

Or to create an excel file at run time along with the result set of a query e.g. select col1,col2 from tab1.

P.S. There is an option of OPENROWSET through which the data can be exported into an excel file. But I need to generate the file at run time as well.

Thanks

latch
Yak Posting Veteran

62 Posts

Posted - 2010-12-30 : 09:03:34

Hi,

It will be used to create excel destination using OpenRowSet:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:/New Folder/Test.xls;',
'SELECT col1,col2 FROM [Sheet1$]')
SELECT [col1],[col2] from tab1
GO

For more information check the site
http://www.mssqltips.com/tip.asp?tip=1202.

Thanks,
Latch

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2010-12-31 : 03:15:51

Hi Latch thank you for the response:

Actually i was trying to create an excel file at run time (upon execution of a stored procedure) and did it via xtended stored procedure.

However I am not facing another issue related to the OpenRowSet function, with following code

set @db_file='Excel 8.0;Database='+ltrim(rtrim(@ReportFilePath))+';'
go
Insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''' + @db_file + ''',
''SElect * FROM [ITSMetaInfo$]''
)
SELECT * FROM TestDB.dbo.MasterSheetMeta

This throws the following error message:

Msg 8152, Level 16, State 4, Line 2
String or binary data would be truncated.
The statement has been terminated.

Which seems that the columns width in excel page is some how smaller to the data length in the Table's column. In order to check the Length of the columns created by the OpenRowSet i have run the following code

Select * into testdb.dbo.temptab from openrowSet .....

And examined the columns in the TempTab, where all columns were of the following datatype and length: nvarchar(510)

Please advise how to overcome this issue ?

Thanks!
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources