Monday, September 7, 2009

Coding for Export Sqlserver Data into Excel

Before go in for export,just configure ur Database Driver With the Given Code Below.

EXEC sp_configure 'show advanced options', 1;

GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

While Run a below code U must create a xls file in a give path,and specify the header in that file.

Below Code is used to Export Sql Data into Excel Format


INSERT INTO
  OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=C:\testing.xls',
  'select fname, lname, email, dateadd, lastchgd, firsttimeuser, active from sql4.ctsi.dbo.users where custno=0913 and active=Y
 FROM [Sheet1$]')

  select fname, lname, email, dateadd, lastchgd, firsttimeuser, active from sql4.ctsi.dbo.users where custno='0913' and active='Y'

No comments: