Monday, September 14, 2009

Query for creating store procedure for running dynamic table with single Procedure

create procedure select_table_dynamic @table_name nvarchar(1000) as
Declare @table nVarchar(1000)
set @table = 'select * from ' + @table_name
exec(@table)

Run Store Procedure

Exec  select_table_dynamic 'table_name'

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'