Export to Excel from SQL Server

I have revisited this topic with the question to export multiple tables to different WorSheet in one Excel file. The OLE Automation script from  Robyn Page and Phil Factor
posted here:
http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/#first
With a reader addition to the final script, we can use the stored procedure to do this:
I reposted the script here with the modification. For full explaination, you should visit the above link.
 
 

spDMOExportToExcel @SourceServer=‘.’,

@QueryText

=mydb select * from MyaTable’,

@filename = ‘C:\Book2.xls’,

@WorksheetName

=‘MySheet2’,

–@WorksheetName=’MySheet1′,

@RangeName

=‘bbb’

—————————————————————————

—The stored procedure:

ALTER PROCEDURE [dbo].[spDMOExportToExcel] (

@SourceServer VARCHAR(30),

@SourceUID

VARCHAR(30)=NULL,

@SourcePWD

VARCHAR(30)=NULL,

@QueryText VARCHAR(200),

@filename

VARCHAR(100),

@WorksheetName

VARCHAR(100)=‘Worksheet’,

@RangeName

VARCHAR(80)=‘MyRangeName’

)

AS

DECLARE

@objServer INT,

@objQueryResults

INT,

@objCurrentResultSet

INT,

@objExcel

INT,

@objWorkBooks INT,

@objWorkBook

INT,

@objWorkSheet

INT,

@objRange

INT,

@hr

INT,

@Columns

INT,

@Rows INT,

@Output

INT,

@currentColumn

INT,

@currentRow

INT,

@ResultSetRow INT,

@off_Column

INT,

@off_Row

INT,

@command

VARCHAR(255),

@ColumnName

VARCHAR(255),

@value

VARCHAR(255),

@strErrorMessage VARCHAR(255),

@objErrorObject

INT,

@Alphabet VARCHAR(27)

SELECT

@Alphabet=‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’

IF @QueryText IS NULL

BEGIN

RAISERROR (‘A query string is required for spDMOExportToExcel’,16,1)

RETURN 1

END

— Sets the server to the local server

IF @SourceServer IS NULL SELECT @SourceServer = @@servername

SET

NOCOUNT ON

SELECT

@strErrorMessage = ‘instantiating the DMO’,

@objErrorObject

=@objServer

EXEC

@hr= sp_OACreate ‘SQLDMO.SQLServer’, @objServer OUT

IF

@SourcePWD IS NULL OR @SourceUID IS NULL

BEGIN

–use a trusted connection

IF @hr=0 SELECT @strErrorMessage=

‘Setting login to windows authentication on ‘

+@SourceServer, @objErrorObject=@objServer

IF @hr=0 EXEC @hr=sp_OASetProperty @objServer, ‘LoginSecure’, 1

IF @hr=0 SELECT @strErrorMessage=

‘logging in to the requested server using windows authentication on ‘

+@SourceServer

IF @SourceUID IS NULL AND @hr=0 EXEC @hr=sp_OAMethod @objServer,

‘Connect’, NULL, @SourceServer

IF @SourceUID IS NOT NULL AND @hr=0

EXEC @hr=sp_OAMethod

@objServer

, ‘Connect’, NULL, @SourceServer ,@SourceUID

END

ELSE

BEGIN

IF @hr=0

SELECT @strErrorMessage = ‘Connecting to ”’+@SourceServer+

”’ with user ID ”’+@SourceUID+””,

@objErrorObject

=@objServer

IF @hr=0

EXEC @hr=sp_OAMethod @objServer, ‘Connect’, NULL,

@SourceServer

, @SourceUID, @SourcePWD

END

–now we execute the query

IF

@hr=0 SELECT @strErrorMessage=‘executing the query "’

+@querytext+‘", on ‘+@SourceServer,

@objErrorObject

=@objServer,

@command

= ‘ExecuteWithResults("’ + @QueryText + ‘")’

IF

@hr=0

EXEC @hr=sp_OAMethod @objServer, @command, @objQueryResults OUT

IF

@hr=0

SELECT @strErrorMessage=‘getting the first result set for "’

+@querytext+‘", on ‘+@SourceServer,

@objErrorObject

=@objQueryResults

IF

@hr=0 EXEC @hr=sp_OAMethod

@objQueryResults

, ‘CurrentResultSet’, @objCurrentResultSet OUT

IF

@hr=0

SELECT @strErrorMessage=‘getting the rows and columns "’

+@querytext+‘", on ‘+@SourceServer

IF @hr=0

EXEC @hr=sp_OAMethod @objQueryResults, ‘Columns’, @Columns OUT

IF

@hr=0

EXEC @hr=sp_OAMethod @objQueryResults, ‘Rows’, @Rows OUT

–so now we have the queryresults. We start up Excel

IF

@hr=0

SELECT @strErrorMessage=‘Creating the Excel Application, on ‘

+@SourceServer, @objErrorObject=@objExcel

IF

@hr=0

EXEC @hr=sp_OACreate ‘Excel.Application’, @objExcel OUT

IF

@hr=0 SELECT @strErrorMessage=‘Getting the WorkBooks object ‘

IF

@hr=0

EXEC @hr=sp_OAGetProperty @objExcel, ‘WorkBooks’,

@objWorkBooks

OUT

—-create a workbook

–IF @hr=0

— SELECT @strErrorMessage=’Adding a workbook ‘,

— @objErrorObject=@objWorkBooks

–IF @hr=0

— EXEC @hr=sp_OAGetProperty @objWorkBooks, ‘Add’, @objWorkBook OUT

/*modified*/

IF

@hr=0

EXEC @hr = sp_OASetProperty @objExcel, ‘DisplayAlerts’, ‘FALSE’

–create a workbook or append a workbook

DECLARE

@status int

EXEC

master..xp_fileexist @filename, @Status OUTPUT

IF

@status = 1

BEGIN

SELECT

@strErrorMessage=‘Opening a workbook ‘, @objErrorObject=@objWorkBooks

IF

@hr=0

SELECT @strErrorMessage=‘Opening the workbook as "’+@filename+‘"’, @objErrorObject=@objRange, @command = ‘Open("’ + @filename + ‘")’

IF

@hr=0

EXEC

@hr=sp_OAGetProperty @objWorkBooks, @command, @objWorkBook OUT

END

ELSE

BEGIN

IF

@hr=0

SELECT

@strErrorMessage=‘Adding a workbook ‘, @objErrorObject=@objWorkBooks

IF

@hr=0

EXEC

@hr=sp_OAGetProperty @objWorkBooks, ‘Add’, @objWorkBook OUT

END

/*modified*/

 

 

–and a worksheet

IF @hr=0

SELECT @strErrorMessage=‘Adding a worksheet ‘,

@objErrorObject

=@objWorkBook

IF @hr=0

EXEC @hr=sp_OAGetProperty @objWorkBook, ‘worksheets.Add’,

@objWorkSheet OUT

IF @hr=0

SELECT @strErrorMessage=‘Naming a worksheet as "’

+@WorksheetName+‘"’, @objErrorObject=@objWorkBook

IF

@hr=0

EXEC @hr=sp_OASetProperty @objWorkSheet, ‘name’, @WorksheetName

SELECT

@currentRow = 1

 

–so let’s write out the column headings

SELECT

@currentColumn = 1

WHILE

(@currentColumn <= @Columns AND @hr=0)

BEGIN

IF @hr=0

SELECT @strErrorMessage=‘getting column heading ‘

+LTRIM(STR(@currentcolumn)) ,

@objErrorObject

=@objQueryResults,

@Command

=‘ColumnName(‘

+CONVERT(VARCHAR(3),@currentColumn)+‘)’

IF @hr=0 EXEC @hr=sp_OAGetProperty @objQueryResults,

@command, @ColumnName OUT

IF @hr=0

SELECT @strErrorMessage=‘assigning the column heading ‘+

+ LTRIM(STR(@currentColumn))

+ ‘ from the query string’,

@objErrorObject=@objExcel,

@command

=‘Cells(‘+LTRIM(STR(@currentRow)) +‘, ‘

+ LTRIM(STR(@CurrentColumn))+‘).value’

IF @hr=0

EXEC @hr=sp_OASetProperty @objExcel, @command, @ColumnName

SELECT @currentColumn = @currentColumn + 1

END

–format the headings in Bold nicely

IF

@hr=0

SELECT @strErrorMessage=‘formatting the column headings in bold ‘,

@objErrorObject

=@objWorkSheet,

@command

=‘Range("A1:’

+SUBSTRING(@alphabet,@currentColumn/26,1)

+SUBSTRING(@alphabet,@currentColumn % 26,1)

+‘1’+‘").font.bold’

IF

@hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 1

–now we write out the data

SELECT

@currentRow = 2

WHILE

(@currentRow <= @Rows+1 AND @hr=0)

BEGIN

SELECT @currentColumn = 1

WHILE (@currentColumn <= @Columns AND @hr=0)

BEGIN

IF @hr=0

SELECT

@strErrorMessage

=

‘getting the value from the query string’

+ LTRIM(STR(@currentRow)) +‘,’

+ LTRIM(STR(@currentRow))+‘)’,

@objErrorObject

=@objQueryResults,

@ResultSetRow

=@CurrentRow1

IF @hr=0

EXEC @hr=sp_OAMethod @objQueryResults, ‘GetColumnString’,

@value

OUT, @ResultSetRow, @currentColumn

IF @hr=0

SELECT @strErrorMessage=

‘assigning the value from the query string’

+ LTRIM(STR(@CurrentRow1)) +‘, ‘

+ LTRIM(STR(@currentcolumn))+‘)’ ,

@objErrorObject

=@objExcel,

@command

=‘Cells(‘+STR(@currentRow) +‘, ‘

+ STR(@CurrentColumn)+‘).value’

IF @hr=0

EXEC @hr=sp_OASetProperty @objExcel, @command, @value

SELECT @currentColumn = @currentColumn + 1

END

SELECT @currentRow = @currentRow + 1

END

–define the name range

–Cells(1, 1).Resize(10, 5).Name = "TheData"

IF

@hr=0 SELECT @strErrorMessage=‘assigning a name to a range ‘

+ LTRIM(STR(@CurrentRow1)) +‘, ‘

+ LTRIM(STR(@currentcolumn1))+‘)’ ,

@objErrorObject

=@objExcel,

@command=‘Cells(1, 1).Resize(‘+STR(@currentRow1) +‘, ‘

+ STR(@CurrentColumn1)+‘).Name’

IF

@hr=0 EXEC @hr=sp_OASetProperty @objExcel, @command, @RangeName

–Now autofilt the columns we’ve written to

IF

@hr=0 SELECT @strErrorMessage=‘Auto-fit the columns ‘,

@objErrorObject

=@objWorkSheet,

@command=‘Columns("A:’

+SUBSTRING(@alphabet,(@Columns / 26),1)

+SUBSTRING(@alphabet,(@Columns % 26),1)+

‘").autofit’

IF

@hr=0 –insert into @bucket(bucket)

EXEC @hr=sp_OAMethod @objWorkSheet, @command, @output out

 

IF

@hr=0 SELECT @command =‘del "’ + @filename + ‘"’

IF @hr=0 EXECUTE master..xp_cmdshell @Command, no_output

IF

@hr=0

SELECT @strErrorMessage=‘Saving the workbook as "’+@filename+‘"’,

@objErrorObject=@objRange,

@command

= ‘SaveAs("’ + @filename + ‘")’

IF

@hr=0 EXEC @hr=sp_OAMethod @objWorkBook, @command

IF

@hr=0 SELECT @strErrorMessage=‘closing Excel ‘,

@objErrorObject=@objExcel

EXEC

@hr=sp_OAMethod @objWorkBook, ‘Close’

EXEC sp_OAMethod @objExcel, ‘Close’

IF

@hr<>0

BEGIN

DECLARE

@Source VARCHAR(255),

@Description

VARCHAR(255),

@Helpfile

VARCHAR(255),

@HelpID INT

EXECUTE sp_OAGetErrorInfo @objErrorObject,

@source

output,@Description output,

@Helpfile

output,@HelpID output

SELECT @hr, @source, @Description,@Helpfile,@HelpID output

SELECT @strErrorMessage=‘Error whilst ‘

+COALESCE(@strErrorMessage,‘doing something’)

+‘, ‘+COALESCE(@Description,)

RAISERROR (@strErrorMessage,16,1)

END

EXEC

sp_OADestroy @objServer

EXEC

sp_OADestroy @objQueryResults

EXEC

sp_OADestroy @objCurrentResultSet

EXEC

sp_OADestroy @objExcel

EXEC

sp_OADestroy @objWorkBooks

EXEC sp_OADestroy @objWorkBook

EXEC

sp_OADestroy @objRange

RETURN

@hr

GO

Advertisements

Link to:Creating a Number (Sequentially incrementing values) table in T-SQL

Anith’s summary:
Creating a Number (Sequentially incrementing values) table in T-SQL

http://www.projectdmx.com/tsql/tblnumbers.aspx


End day of last month

 

SELECT DateAdd(second, 1, DateAdd(month, DateDiff(month, 0, GETDATE()), 0) ) AS LastMonthEnd

 

Datediff and dateadd are two very important date function. It can address many date related questions.


Split one column into multiple row for comma delimited data

Data in column Items in this format:
id1 id2     Items
  1   1       aa,bbb,ccc,ddd
  1   2       xx,yy,zzz
 
Output:
1 1 aa
1 1 bbb
1 1 ccc
1 1 ddd
1 2 xx
1 2 yy
1 2 zzz
 

SELECT a.id1, a.id2, a.Items, b.Value FROM dbo.Test AS a

CROSS

APPLY fn_Split(a.Items, ‘,’) AS b

We can create an SSIS package to do this with the Wizard:

Execute SQL Task>>Dataflow Task (From OLE DB Source > Destination : tableName)

 

Another way to so this through a number table (see Note 1):

SELECT

id1,id2,items,

SUBSTRING

(items, n, CHARINDEX(‘,’, items + ‘,’, n) n) AS item, n + 1 LEN(REPLACE(LEFT(items, n), ‘,’, )) AS item_idx

FROM

Test AS P

CROSS

JOIN ( SELECT number FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY newid()) as number FROM sys.objects ) t

WHERE number <= 100 ) AS Numbers(n)

WHERE

SUBSTRING(‘,’ + items, n, 1) = ‘,’ AND n < LEN(items) + 1

ORDER

BY id1, id2, item_idx

 

One more solution  with CTE from Itzik Ben-Gan: I have developed a sample with his solutions.

http://jingyang.spaces.live.com/blog/cns!CC21A118B1B5250!252.entry

 

 
Reference:
Cross Apply:
http://www.mydatabasesupport.com/forums/sqlserver-programming/383420-problem-inline-table-valued-udf-some-sort-join-onparameters-wanted.html
Split Function:
http://www.odetocode.com/articles/365.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
Note 1: A solution provided by Plamen Ratchev from this link:
http://www.eggheadcafe.com/software/aspnet/31741612/split-column-into-multipl.aspx
and link to create a number table:
http://www.projectdmx.com/tsql/tblnumbers.aspx
 
 

SSIS Derived column check nulls for concatenation

We can concatenate firstname and Lastname with checking null like this  to avoid null result: SELECT ISNULL(FName,”) + " " + ISNULL(LName,”)   AS FullName
But this will not work in SSIS’s derived column. Instead, use the following syntax to concatenat a FullName derived column:

(ISNULL(FirstName) ? "" : FirstName) + " " + (ISNULL(LastName) ? "" : LastName)

There is a link at technet.microsoft.com for a step by step guild for this: http://technet.microsoft.com/en-us/library/ms365300.aspx


New MSDN SQL Server “FAQ” from community


How to transform string date from a text file to SQL Server 2005?

SSIS way:

You can transform your string date through a Derived Column component to a new derived column with the Expression like: (Assume your date is in YYYYMMDD format)

(DT_DBTIMESTAMP)(SUBSTRING(mydate,5,2) + "/" + SUBSTRING(mydate,7,2) + "/" + SUBSTRING(mydate,1,4))

So, the whole process is:

(Under Data Flow Tab)

1. Drag a Flat File Source ;

2. Drag a Derived column and use DT_DBTIMESTAMP transformation with SUBSTRING manipulations;

3.map the derived column with a new table in SQL Server Destination.