Tips And Tricks Programmer

Store Procedure for Audit Trial in SQL

2010-01-24T22:47:00.001-08:00

Here the code for create procedure to deploy audit trial in sql :

/****** Object: StoredProcedure [dbo].[GenerateAudittrail] Script Date: 01/25/2010 13:30:29 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GenerateAudittrail]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GenerateAudittrail]
GO

CREATE PROCEDURE [dbo].[GenerateAudittrail]
@TableName varchar(128),
@Owner varchar(128) = 'dbo',
@AuditNameExtention varchar(128) = '_shadow',
@DropAuditTable bit = 0
AS
BEGIN

-- Check if table exists
IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'ERROR: Table does not exist'
RETURN
END

-- Check @AuditNameExtention
IF @AuditNameExtention is null
BEGIN
PRINT 'ERROR: @AuditNameExtention cannot be null'
RETURN
END

-- Drop audit table if it exists and drop should be forced
IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1)
BEGIN
PRINT 'Dropping audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
EXEC ('drop table ' + @TableName + @AuditNameExtention)
END

-- Declare cursor to loop over columns
DECLARE TableColumns CURSOR Read_Only
FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
FROM sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname'
WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']')
and OBJECTPROPERTY(a.id, N'IsUserTable') = 1
ORDER BY b.colId

OPEN TableColumns

-- Declare temp variable to fetch records into
DECLARE @ColumnName varchar(128)
DECLARE @ColumnType varchar(128)
DECLARE @ColumnLength smallint
DECLARE @ColumnNullable int
DECLARE @ColumnCollation sysname
DECLARE @ColumnPrecision tinyint
DECLARE @ColumnScale tinyint

-- Declare variable to build statements
DECLARE @CreateStatement varchar(8000)
DECLARE @ListOfFields varchar(2000)
SET @ListOfFields = ''

-- Check if audit table exists
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + @AuditNameExtention + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
-- AuditTable exists, update needed
PRINT 'Table already exists. Only triggers will be updated.'

FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + @ColumnName + ','
END

FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale

END
END
ELSE
BEGIN
-- AuditTable does not exist, create new

-- Start of create table
SET @CreateStatement = 'CREATE TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + '] ('
SET @CreateStatement = @CreateStatement + '[AuditId] [bigint] IDENTITY (1, 1) NOT NULL,'

FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale

WHILE @@FETCH_STATUS = 0
BEGIN
IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
BEGIN
SET @ListOfFields = @ListOfFields + @ColumnName + ','

SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '

IF @ColumnType in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
BEGIN
IF (@ColumnLength = -1)
Set @CreateStatement = @CreateStatement + '(max) '
ELSE
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') '
END

IF @ColumnType in ('decimal', 'numeric')
SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') '

IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '

IF @ColumnNullable = 0
SET @CreateStatement = @CreateStatement + 'NOT '

SET @CreateStatement = @CreateStatement + 'NULL, '
END

FETCH Next FROM TableColumns
INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
END

-- Add audit trail columns
SET @CreateStatement = @CreateStatement + '[AuditAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,'
SET @CreateStatement = @CreateStatement + '[AuditDate] [datetime] NOT NULL ,'
SET @CreateStatement = @CreateStatement + '[AuditUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,'
SET @CreateStatement = @CreateStatement + '[AuditApp] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)'

-- Create audit table
PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtention + ']'
EXEC (@CreateStatement)

-- Set primary key and default values
SET @CreateStatement = 'ALTER TABLE [' + @Owner + '].[' + @TableName + @AuditNameExtention + '] ADD '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditDate] DEFAULT (getdate()) FOR [AuditDate],'
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditUser] DEFAULT (suser_sname()) FOR [AuditUser],CONSTRAINT [PK_' + @TableName + @AuditNameExtention + '] PRIMARY KEY CLUSTERED '
SET @CreateStatement = @CreateStatement + '([AuditId]) ON [PRIMARY], '
SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtention + '_AuditApp] DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp]'

EXEC (@CreateStatement)

END

CLOSE TableColumns
DEALLOCATE TableColumns

/* Drop Triggers, if they exist */
PRINT 'Dropping triggers'
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]')

IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]')

IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
EXEC ('drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]')

/* Create triggers */
PRINT 'Creating triggers'
EXEC ('CREATE TRIGGER tr_' + @TableName + '_Insert ON ' + @Owner + '.' + @TableName + ' FOR INSERT AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted')

EXEC ('CREATE TRIGGER tr_' + @TableName + '_Update ON ' + @Owner + '.' + @TableName + ' FOR UPDATE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''U'' FROM Inserted')

EXEC ('CREATE TRIGGER tr_' + @TableName + '_Delete ON ' + @Owner + '.' + @TableName + ' FOR DELETE AS INSERT INTO ' + @TableName + @AuditNameExtention + '(' + @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted')

END

After you create the procedure, try this :

create table testing1
(
id int identity(1,1) not null,
name varchar(50),
primary key (id)
)
go

exec [GenerateAudittrail] testing1, 'dbo', '_history', 1
go

insert into testing1(name) values('Wihemdra')
insert into testing1(name) values('Halim')
update testing1 set name = 'Wihemdra1' where id = 1
delete from testing1 where id = 2

select * From testing1
select * from testing1_history

Then you can find what exactly the procedure use for.

Happy Code ^_^

Capture Error Message on Procedure

2010-01-17T19:44:00.000-08:00

When we want to make a procedure from sql, sometimes we need to capture the error message on procedure. It will allow us to make an history into the physical table on databases. Here the simulation :

---------------------------------
if exists(select 1 from sys.objects where name = 't_test') drop table t_test

create table t_test
(
id int not null,
primary key(id)
)
go
---------------------------------
if exists(select 1 from sys.objects where name = 't_msg') drop table t_msg

create table t_msg
(
id int identity(1,1) not null,
msg varchar(max) not null,
date datetime,
primary key(id)
)
go
---------------------------------
if exists(select 1 from sys.objects where name = 'sp_addTest') drop procedure sp_addTest
---------------------------------
create procedure sp_addTest
@id int
as
begin

begin try
begin tran
insert into t_test values(@id)
commit tran
end try
begin catch
declare @err_msg varchar(max), @err_proc varchar(max)
set @err_msg = ERROR_MESSAGE()
set @err_proc = ERROR_PROCEDURE()
rollback tran

insert t_msg(msg,date) select 'sp_addTest Error : '+ltrim(rtrim(@err_msg))+ 'Error in '+@err_proc,getdate()
end catch

end
go
---------------------------------
exec sp_addTest 2
go

exec sp_addTest 3
go

exec sp_addTest 4
go

exec sp_addTest 2
go

select * From t_test

select * From t_msg

the message is "sp_addTest Error : Violation of PRIMARY KEY constraint 'PK__t_test__1CA7377D'. Cannot insert duplicate key in object 'dbo.t_test'.Error in sp_addTest"

Happy Code ^_^"

Exporting data to a XML file in SQL Server 2005

2010-01-10T22:37:00.000-08:00

Overview

In this article, I'll demonstrate how we can combine the new CLR integration and FOR XML enhancements to easily generate xml files from our relational data. For an introduction to .NET Stored procedures have a look at Introducing CLR Stored Procedures in SQL Server 2005. This article will reference the Orders table in the Northwind database. You can download the Northwind and pubs databases here.

.NET Code

The first thing we need to do is write our .Net code for our Stored Procedure and compile it. In this case, we are going to create a procedure that accepts a filename and some content (using a nvarchar(max) parameter) and simply writes that content to the specified file.

[Visual Basic]

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO

Public Class SQLCLRIO
  <> _
  Public Shared Sub WriteToFile(ByVal content As String, _
                                ByVal filename As String)

      Try
         File.WriteAllText(filename, content)

      Catch ex As Exception
          SqlContext.Pipe.Send("Error writing to file " & ex.Message)
      End Try

  End Sub

End Class

[C#]

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;

public class SQLCLRIO{

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void WriteToFile(String content,String filename)
  {
      try
      {
         File.WriteAllText(filename, content);
      }
      catch(Exception ex)
      {
         SqlContext.Pipe.Send("Error writing to file " + ex.Message);
      }
  }
}

In order for us to use this code in SQL Server 2005 we must first compile it into an assembly. We can then load it into SQL Server and create our TSQL Stored Procedure wrapper for our .Net code. The command below demonstrates how to compile the code into an assembly. For the purposes of this article we will assume that the code has been saved in a source file called c:\WriteToFile.vb or c:\WriteToFile.cs. To compile this open a command prompt and execute the relavent lines to change to the .NET 2.0 directory and compile the code.

[Visual Basic]
cd "%SystemRoot%\Microsoft.NET\Framework\v2.0.50727"
vbc /target:library c:\WriteToFile.vb

[C#]
cd "%SystemRoot%\Microsoft.NET\Framework\v2.0.50727"
csc /target:library c:\WriteToFile.cs

TSQL Code

Now that we have created our assembly (c:\WriteToFile.dll) we can load it into SQL Server and create our Stored Procedure. We do this by using the new CREATE ASSEMBLY command as below. We need to specify a permission_set of EXTERNAL_ACCESS because the stored procedure needs to access the file system. We need to ensure that execution of user defined CLR code is enabled using sp_configure and we also need to set the database Trustworthy property ON to allow the loading of the EXTERNAL_ACCESS assembly. NOTE : The external access will be under the security context of the SQL Service account

exec sp_configure 'clr enabled',1
reconfigure
go
alter database Northwind set trustworthy on
go
use Northwind
go
create assembly WriteToFile from 'c:\WriteToFile.dll'
with permission_set = external_access

Now that we have loaded our assembly, we can create our Stored Procedure. To do this we simply use the standard TSQL CREATE PROCEDURE syntax but instead of defining a TSQL procedure body, we specify an EXTERNAL NAME that points to our .NET Code. We specify the assembly, class and method name in the form .. as shown below.

create procedure dbo.writetofile
(
@content nvarchar(max),
@filename nvarchar(255)
)
as external name WriteToFile.SQLCLRIO.WriteToFile

Now that the Stored Procedure is created, we can use it to write our content (in this case xml) to a file. The sample query will take the entire contents of the Northwind.dbo.Orders table and create a single, well formed xml document from it which we will then write to a xml file using the WriteToFile stored procedure. The query takes advantage of the ability to declare local variables for LOB types such as varchar(max) and nvarchar(max) - these datatypes supercede the text and ntext datatypes - as well as the new root directive of the FOR XML clause that allows you to specify a single top level root element for you xml document, in the code below the top level element will be . This code will create a new file called AllOrders.xml containing all the order header information from the Northwind database. You can view the contents of the generated file by simply opening it in your web browser.

declare @xml nvarchar(max)

set @xml = (
select * from Orders
for xml auto,elements,root('AllOrders'))

exec writetofile @xml,'c:\AllOrders.xml'

Source : http://www.sqldbatips.com/showarticle.asp?ID=23

Generate System Column ID based on Format Required in SQL

2009-12-13T20:18:00.000-08:00

If we want to make a generate id column in table which format is 'TR-091214-001' where,
- tr is define transaction
- 091213 is define yymmdd
- 001 is define increment number of transaction each day

we can make a simple logic and validation for that.

here the steps :

-- step 1 create table
create table testgenid
(
tr_id varchar(30) not null,
primary key(tr_id)
)

-- step 2 generate id
declare @gen_id varchar(30)
set @gen_id = (
select 'TR-'+ right(convert(varchar,getdate(),112),6) + '-' + right('00' + cast((cast(right(isnull(max(tr_id),'000'),3) as int) + 1) as varchar),3)
from testgenid
where tr_id like '%' + right(convert(varchar,getdate(),112),6) + '%')

print @gen_id

insert into testgenid values(@gen_id)

-- step 3 see the result
select * From testgenid

the result is : TR-091214-001

happy coding ^^

Data Dictionary Using Query SQL

2009-11-25T04:03:00.000-08:00

When we want to summarize all the tables on database from SQL we need the query to make it simple and fast... here the query :

select distinct b.name, a.name, c.name, a.max_length,c.precision, c.scale
from sys.columns a left join sys.objects b
on a.object_id = b.object_id
left join sys.types c
on c.system_type_id = a.system_type_id
and c.user_type_id = a.user_type_id

Having A Nice Try… ^^

Good Luck

Row Number Function in SQL 2005

2009-11-16T20:23:00.000-08:00

A new function in sql server 2005. It's function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Syntax :

ROW_NUMBER ( ) OVER ( [] )

Example :

create table Testing ( Kd varchar(10) not null, Name varchar(10) )

insert into Testing values('K01','Siji')
insert into Testing values('K02','Loro')
insert into Testing values('K03','Telu')
insert into Testing values('K04','Opat')

select ROW_NUMBER() OVER(ORDER BY Kd) AS 'No.' , Kd , Name from Testing

Output :

Easy Right!!!

Happy Code \^_^/

Find Text In SQL Server

2009-07-03T00:41:00.001-07:00

Sometimes we want to check where the certain text could appear on the store procedure or trigger we made... Here is the query provide to maximize the searching :

declare @text varchar(max)
set @text = 'Wihemdra'

select name from sys.objects
where object_id in
(
select id from syscomments
where text like '%'+@text+'%'
)

Regards,
Wihemdra

Example Duwamish Architecture with ASP.NET C#

2009-07-02T02:36:00.000-07:00

Above is the screen capture for duwamish layer template.

The Duwamish 7.0 architecture is divided into four logical layers:

Web Layer
The Web layer provides access for clients to the application. This layer is implemented as the Web project in the Duwamish.sln solution file. The Web layer consists of ASP.NET Web Forms and code-behind files. The Web Forms simply provide the user experience in HTML, while the code-behind files implement event handling for the various controls.

Business Facade Layer
The Business Facade layer provides interfaces to the Web layer to handle accounts, category browsing, and book purchasing. This layer is implemented as the BusinessFacade project in the Duwamish.sln solution file. The Business Facade layer serves as an isolation layer, segregating the user interface from the implementation of the various business functions. Apart from low-level system and support functions, all calls to database servers are made through this assembly.

Business Rules Layer
The Business Rules layer, which is implemented as the BusinessRules project in the Duwamish.sln solution file, contains the implementation of the various business rules and logic. Business rules do tasks such as the validation of customer accounts and book orders.

Data Access Layer
The Data Access layer provides data services to the Business Rules layer. This layer is implemented as the DataAccess project in the Duwamish.sln solution file.

Download Template

Reference :

MSDN

Design Dynamic Structure Table

2009-06-04T21:09:00.000-07:00

Design table for meet the requirement is not so simple. Sometimes rule and process of business change rapidly. We must predict the future before we start to create the table. The table structure we made could impact anywhere which related it. As you could see the logic for application , reports, calculation, batch, etc depend on the design of the table. Sometimes we think the table is unpowerfull or too much field unrequired...

One example to handle this requirement for current and future development :

The requirement is : every product have own rate accumulated per year, and not every year is at the same rate but we assumed that the maximum rate until 100 years or less. So the simulation is like this :

--bad design table
--take too much columns
create table mst_rate_product
(
code_product nvarchar(20) not null,
rate_year01 decimal(10,4),
rate_year02 decimal(10,4),
rate_year03 decimal(10,4),
rate_year04 decimal(10,4),
rate_year05 decimal(10,4),
rate_year06 decimal(10,4),
rate_year07 decimal(10,4),
rate_year08 decimal(10,4),
rate_year09 decimal(10,4),
rate_year10 decimal(10,4),
--...
rate_year100 decimal(10,4)
primary key (code_product)
)

--recommended design table structured
--powerfull on data requirements and saving data
create table mst_rate_product_recommended
(
code_product nvarchar(20) not null,
year int,
rate decimal(10,4),
primary key (code_product)
)

We don't have to affraid to make a complicated report by logic because sql server 2005 have pivot table to make a report based on column(if needed) not by rows.

Regards,
Wihemdra

Using Begin Tran, Rollback Tran and Commit Tran on SQL Server

2009-05-28T18:12:00.000-07:00

Sometimes, we're using too much windows document in sql server.
To prevent the mass Execute query on one window we didn't want, better we use query "begin tran" in every windows document.
So, if we're executing the windows document by accident, we could rollback all the transaction we made ^^".

-----------------------------Assumed this is the windows document used
Begin Tran

select * from nametable

insert into nametable values('string value')
insert into nametable values('string value')
insert into nametable values('string value')
insert into nametable values('string value')
insert into nametable values('string value')
insert into nametable values('string value')

update nametable
set namecolumn = 'asdf'

/*
When we Execute execute for the second time, but we want to cancel it. Just use rollback tran. But otherwise use commit tran
*/

Regards,
Wihemdra

varchar(max) in sql server 2005

2009-05-27T05:23:00.000-07:00

Varchar(max) is one of the new type data of sql server 2005.

Usually we use it when we didn't know the maximum length of string or we couldn't expect how long the string is.

As you know the length of varchar(max) is -1. We'll see when we're using ALT+F1 at table name. Or we could using this query to figure it out :

---------------------------------------------------------------------------
drop table test1

create table test1
(
strMax varchar(max)
)

select distinct b.name, a.name, c.name, a.max_length,c.precision, c.scale
from
sys.columns a left join sys.objects b
on a.object_id = b.object_id
left join sys.types c
on c.system_type_id = a.system_type_id
and c.user_type_id = a.user_type_id
where b.name = 'test1'
----------------------------------------------------------------------------

Here the results :

name name name max_length precision scale
test1 strMax varchar -1 0 0

Regards,

Wihemdra

String or binary data would be truncated. Error Message @ SQL

2009-05-26T01:47:00.000-07:00

Error Message:
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.

Severity level:
16.

Description:
This error message appears when you try to insert a string with more characters than the

column can maximal accommodate.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Errors of the Severity Level 16 are generated by the user and are corrigible by the user.

The statement cannot be executed this way. You must either shorten the string to be isnerted

to widen the column.

Versions:
All versions of SQL Server.

Example(s):
USE tempdb;
IF OBJECT_ID('tempdb.#t') > 0
DROP TABLE #t
GO
CREATE TABLE #t
(
c1 CHAR
);
INSERT INTO #t SELECT 'abc'
GO

Remarks:
In the above example we try to insert a string 'abc' with a length of 3 into the column c1 of the table #t. Because c1 is of the data type CHAR(1), the error is raised.

String Connection To Upload Data From Excel To SQL Using ASP.NET VB

2009-05-24T23:54:00.000-07:00

Sometimes we would like to upload mass data from excel to SQL 2005.
Here the connection string :

Dim FileLocation As String = "D:\Wihemdra\Book1.xls"

' Create connection string variable. Modify the "Data Source" parameter as' appropriate for your environment.

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=" & FileLocation & ";" & _"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

' Create the connection object by using the preceding connection string.
Dim objConn As New OleDbConnection(sConnectionString)

' Open connection with the database.
objConn.Open()

' The code to follow uses a SQL SELECT command to display the data from the worksheet.
' Create new OleDbCommand to return data from worksheet.
Dim objCmdSelect As New OleDbCommand("SELECT * FROM [Sheet1$]", objConn)

' Create new OleDbDataAdapter that is used to build a DataSet' based on the preceding SQL SELECT statement.
Dim objAdapter1 As New OleDbDataAdapter()

' Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect

' Create new DataSet to hold information from the worksheet.
Dim objDataset1 As New Data.DataSet()

' Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData")

' Clean up objects.
objConn.Close()

Make excel report from query option of SQL Server 2005

2009-05-23T23:06:00.000-07:00

Sometimes we want to check data from our database or make temporary report using excel format.
Here the step to resolve your problem.
It is faster way to copy data from sql to excel.
I'm using Query options facility.

Please Follow this instruction :

1. Make a short sample query. Example :

USE [AdventureWorks]
Go

select * from HumanResources.Department

2. Choose the file option like this :

Query -> Query Options
Text -> Output format : Tab delimited
OK

3. Choose "Results to Text" (in toolbars)

4. Press F5

5. Drag / "Copy and Paste" the results from sql to Excel.

See what happen. It's cool right... ^^"

Best Regards,

Wihemdra