Click here to monitor SSC
  • Av rating:
  • Total votes: 188
  • Total comments: 132
Robyn Page and Phil Factor

SQL Server Excel Workbench

06 February 2007

/*The need to produce Excel reports from SQL Server is very common. Here, Robyn Page and Phil Factor present practical techniques for creating and manipulating Excel spreadsheets from SQL Server, using linked servers and T-SQL. The pièce de résistance is a stored procedure that uses OLE Automation to allow you full control over the formatting of your Excel report, and the ability to include sums, ranges, pivot tables and so on. */

 

/*

The C.R.U.D. of Excel

=====================

 

 

 

Phil and I have teamed up on this workbench, which demonstrates how to create, read, update and delete information in Excel using T-SQL, from SQL Server. As always, the workbench is structured so that it can be pasted into Query Analyser and SSMS, and the individual examples executed - you can download the .sql from the "Code Download" link above, load it up and start experimenting!

 

Contents

========

 

Creating Excel spreadsheets via ADODB

Manipulating Excel data via a linked server

Synchronising the Spreadsheet with SQL Server Tables

Manipulating Excel data using OPENDATASOURCE and OPENROWSET functions

Creating Excel spreadsheets using sp_MakeWebTask

OLE Automation

 

 


 

We start by showing you how to create an Excel Spreadsheet from SQL Server in TSQL(Transact SQL), create a worksheet, attach to it as a linked server, write to it, read from it, update it as if it was an ordinary SQL Server Database table, and then synchronise the data in the worksheet with SQL Server. We also illustrate the use of OPENQUERY, OPENDATASOURCE and OPENROWSET.

 

To create the Excel spreadsheet, we show how to attach to an ADODB source from SQL Server and execute SQL against that source. We then show you an alternative 'quick cheat' way (using sp_makewebtask) to create and populate an excel spreadsheet from Transact SQL.

 

If you need more control over the Excel Spreadsheet that you are creating, we then show you how to do it via OLE automation. This will enable you to do anything you can do via keystrokes, and allow you to generate full Excel reports with pivot tables and Graphs.

 

Using this technique, you should be able to populate the data, or place data in particular calls or ranges. You can even do 'macro substitutions'

 

A word of caution before you start. If you have your security wide open, it is not just you who would be able to write out data as a spreadsheet. An intruder would be able to do it with that list of passwords or credit-card numbers. In a production system, this sort of operation needs to be properly ring-fenced. We tend to create a job queue and have a special user, with the appropriate permissions, on the Task Scheduler, to do anything that involves OLE automation or xp_CMDShell. Security precautions can get quite complex, but they are outside the scope of the article.

 

Some of what we illustrate can be done using DTS or SSIS. Unfortunately, these are outside the scope of this article. In fact, transferring data between Excel and SQL Server can be done in a surprising variety of ways and it would be fun one day to

try to list them all

  

First we need some simple test data

*/

CREATE TABLE ##CambridgePubs

        (Pubname VARCHAR(40),

        Address VARCHAR(80),

        Postcode VARCHAR(8))

 

INSERT INTO ##CambridgePubs (PubName, Address, Postcode)

    SELECT 'Bees In The Wall','36 North Road,

Whittlesford, Cambridge','CB2 4NZ'

INSERT INTO ##CambridgePubs (PubName, Address, Postcode)

    SELECT 'Blackamoors Head','205 Victoria Road,

Cambridge','CB4 3LF'

INSERT INTO ##CambridgePubs (PubName, Address, Postcode)

    SELECT 'Blue Lion','2 Horningsea Road,

Fen Ditton, Cambridge','CB5 8SZ'

INSERT INTO ##CambridgePubs (PubName, Address, Postcode)

    SELECT 'Cambridge Blue','85-87 Gwydir Street,

Cambridge','CB1 2LG'

INSERT INTO ##CambridgePubs (PubName, Address, Postcode)

    SELECT 'Champion Of The Thames','68 King Street,

Cambridge','CB1 1LN'

INSERT INTO ##CambridgePubs (PubName, Address, Postcode)

    SELECT 'Cross Keys','77 Ermine Street,

Caxton, Cambridge','CB3 8PQ'

INSERT INTO ##CambridgePubs (PubName, Address, Postcode)

    SELECT 'Crown Inn','11 High Street,

Linton, Cambridge','CB1 6HS'

INSERT INTO ##CambridgePubs (PubName, Address, Postcode)

    SELECT 'Devonshire Arms','1 Devonshire Road,

Cambridge','CB1 2BH'

INSERT INTO ##CambridgePubs (PubName, Address, Postcode)

    SELECT 'Duke Of Argyle','90 Argyle Street,

Cambridge','CB1 3LS'

INSERT INTO ##CambridgePubs (PubName, Address, Postcode)

    SELECT 'Duke Of Wellington','49 Alms Hill,

Bourn, Cambridge','CB3 7SH'

INSERT INTO ##CambridgePubs (PubName, Address, Postcode)

    SELECT 'Eagle Public House','Benet Street,

Cambridge','CB2 3QN'

/*And so on. (The full import file is in the ZIP, as is the Excel

file!)

Create the table and then execute the contents of CambridgePubs.SQL

 

Creating Excel spreadsheets via ADODB

-----------------------------------------

 

First, we need to create the spreadsheet with the correct headings (PubName, Address, PostCode)

 

There are two possible ways one might do this. The most obvious way is using the CREATE statement to create the worksheet and define the columns, but there seems to be no way of doing this by linking the excel FILE, unless the Excel file already exists. We need a utility stored procedure to get at ADODB in order to create databases and execute DDL and SQL against it. */

 

CREATE PROCEDURE spExecute_ADODB_SQL

@DDL VARCHAR(2000),

@DataSource VARCHAR(100),

@Worksheet VARCHAR(100)=NULL,

@ConnectionString VARCHAR(255)

    = 'Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=%DataSource;

Extended Properties=Excel 8.0'

AS

DECLARE

    @objExcel INT,

    @hr INT,

    @command VARCHAR(255),

    @strErrorMessage VARCHAR(255),

    @objErrorObject INT,

    @objConnection INT,

    @bucket INT

 

SELECT @ConnectionString

    =REPLACE (@ConnectionString, '%DataSource', @DataSource)

IF @Worksheet IS NOT NULL

    SELECT @DDL=REPLACE(@DDL,'%worksheet',@Worksheet)

 

SELECT @strErrorMessage='Making ADODB connection ',

            @objErrorObject=NULL

EXEC @hr=sp_OACreate 'ADODB.Connection', @objconnection OUT

IF @hr=0

    SELECT @strErrorMessage='Assigning ConnectionString property "'

            + @ConnectionString + '"',

            @objErrorObject=@objconnection

IF @hr=0 EXEC @hr=sp_OASetProperty @objconnection,

            'ConnectionString', @ConnectionString

IF @hr=0 SELECT @strErrorMessage

        ='Opening Connection to XLS, for file Create or Append'

IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Open'

IF @hr=0 SELECT @strErrorMessage

        ='Executing DDL "'+@DDL+'"'

IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Execute',

        @Bucket out , @DDL

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 @strErrorMessage='Error whilst '

        +COALESCE(@strErrorMessage,'doing something')+', '

        +COALESCE(@Description,'')

    RAISERROR (@strErrorMessage,16,1)

    END

EXEC @hr=sp_OADestroy @objconnection

GO

--------------------------------------

/* Now we have it, it is easy */

 

spExecute_ADODB_SQL @DDL='Create table CambridgePubs

(Pubname Text, Address Text, Postcode Text)',

@DataSource ='C:\CambridgePubs.xls'

--the excel file will have been created on the Database server of the

-- database you currently have a connection to

 

--We could now insert data into the spreadsheet, if we wanted

spExecute_ADODB_SQL @DDL='insert into CambridgePubs

(Pubname,Address,Postcode)

values (''The Bird in Hand'',

''23, Marshall Road, Cambridge CB4 2DQ'',

''CB4 2DQ'')',

@DataSource ='C:\CambridgePubs.xls'

 

--you could drop it again!

spExecute_ADODB_SQL @DDL='drop table CambridgePubs',

@DataSource ='c:\CambridgePubs.xls'

 

/* Manipulating Excel data via a linked server

----------------------------------------------

 

We can now link to the created excel file as follows */

 

EXEC sp_addlinkedserver 'CambridgePubDatabase',

@srvproduct = '',

@provider = 'Microsoft.Jet.OLEDB.4.0',

@datasrc = 'C:\CambridgePubs.xls',

@provstr = 'Excel 8.0;'

GO

 

EXEC sp_addlinkedsrvlogin 'CambridgePubDatabase', 'false'

GO

 

--to drop the link, we do this!

--EXEC sp_dropserver 'CambridgePubDatabase', 'droplogins'

 

-- Get the spreadsheet data via OpenQuery

SELECT * FROM OPENQUERY

    (CambridgePubDatabase, 'select * from [CambridgePubs]')

GO

--or more simply, do this

SELECT * FROM CambridgePubDatabase...CambridgePubs

 

--so now we can insert our data into the Excel Spreadsheet

INSERT INTO CambridgePubDatabase...CambridgePubs

    (Pubname, Address, postcode)

    SELECT Pubname, Address, postcode FROM ##CambridgePubs

 

/*Synchronizing the Spreadsheet with SQL Server tables

------------------------------------------------------

 

As we are directly manipulating the Excel data in the worksheet as if it was a table we can do joins.

 

What about synchronising the table after editing the excel spreadsheet?

 

To try this out, you'll need to delete, alter and insert a few rows from the excel spreadsheet, remembering to close it after you've done it

*/

 

 

--Firstly, we'll delete any rows from ##CambridgePubs

-- that do not exist in the Excel spreadsheet

 

DELETE FROM ##CambridgePubs

FROM ##CambridgePubs c

LEFT OUTER JOIN CambridgePubDatabase...CambridgePubs ex

ON c.address LIKE ex.address

    AND c.pubname LIKE ex.pubname

    AND c.postcode LIKE ex.postcode

WHERE ex.pubname IS NULL

 

-- then we insert into #CambridgePubs any rows in the spreadsheet

-- that don't exist in #CambridgePubs

 

INSERT INTO ##CambridgePubs (Pubname,Address,Postcode)

SELECT ex.Pubname,ex.Address,ex.Postcode

FROM CambridgePubDatabase...CambridgePubs ex

LEFT OUTER JOIN ##CambridgePubs c

ON c.address LIKE ex.address

    AND c.pubname LIKE ex.pubname

    AND c.postcode LIKE ex.postcode

WHERE c.pubname IS NULL

 

--all done (reverse syncronisation would be similar)

 

/*Manipulating Excel data using OPENDATASOURCE and OPENROWSET

-------------------------------------------------------------

 

If you don't want to do the linking, you can also read the data like

this*/

 

SELECT *

FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',

 'Data Source="C:\CambridgePubs.xls";

  Extended properties=Excel 8.0')...CambridgePubs

--and write to it

 

UPDATE OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0',

'Data Source="C:\CambridgePubs.xls";

extended Properties=Excel 8.0')...CambridgePubs

SET Address='St. Kilda Road, Cambridge'

WHERE Pubname = 'Jenny Wren'

 

INSERT INTO OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0',

'Data Source="C:\CambridgePubs.xls";

extended Properties=Excel 8.0')...CambridgePubs

(Pubname,Address,Postcode )

SELECT 'The St George','65 Cavendish Road','CB2 4RT'

 

--You can read and write toExcel Sheet using OpenRowSet,

--if the mood takes you

 

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=C:\CambridgePubs.xls', 'Select * from CambridgePubs')

 

UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\CambridgePubs.xls',

'Select * from CambridgePubs')

    SET Address='34 Glemsford Road' WHERE Address = '65 Cavendish Road'

 

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\CambridgePubs.xls',

'Select * from CambridgePubs')

(Pubname, Address, Postcode)

SELECT 'The Bull', 'Antioch Road','CB2 5TY'

 

/*Creating Excel Spreadsheets using sp_makewebtask

--------------------------------------------------

 

Instead of creating the Excel spreadsheet with OLEDB One can use the

sp_makewebtask

 

Users must have SELECT permissions to run a specified query and CREATE PROCEDURE permissions in the database in which the query will run. The SQL Server account must have permissions to write the generated HTML document to the specified location. Only members of the sysadmin server role can impersonate other users.

*/

 

sp_makewebtask @outputfile = 'c:\CambridgePubsHTML2.xls',

  @query = 'Select * from ##CambridgePubs',

  @colheaders =1,

    @FixedFont=0,@lastupdated=0,@resultstitle='Cambridge Pubs',

  @dbname ='MyDatabaseName'

 

/* This is fine for distributing information from databases but no good

if you subsequently want to open it via ODBC.*/

 

/*OLE Automation

----------------

 

So far, so good. However, we really want rather more than this. When we create an excel file for a business report, we want the data and we also want nice formatting, defined ranges, sums, calculated fields and pretty graphs. If we do financial reporting, we want a pivot table and so on in order to allow a degree of data mining by the recipient. A different approach is required.

 

We can, of course, use Excel to extract the data from the database. However, in this example, we'll create a spreadsheet, write the data into it, fit the columns nicely and define a range around the data

 

*/ 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=

     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

 

--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=

    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=

            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=@CurrentRow-1

        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(@CurrentRow-1)) +', '

                + 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(@CurrentRow-1)) +', '

        + LTRIM(STR(@currentcolumn-1))+')' ,

    @objErrorObject=@objExcel,

    @command='Cells(1, 1).Resize('+STR(@currentRow-1) +', '

                                    + STR(@CurrentColumn-1)+').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 @objWorkBookks

EXEC sp_OADestroy @objWorkBook

EXEC sp_OADestroy @objRange

RETURN @hr

GO

 

--Now we can create our pubs spreadsheet, and can do it from any of

--our servers

--

spDMOExportToExcel @SourceServer='MyServer',

@SourceUID= 'MyUserID',

@SourcePWD = 'MyPassword',

@QueryText = 'use MyDatabase

select Pubname, Address, Postcode from ##CambridgePubs',

@filename = 'C:\MyPubDatabase.xls',

@WorksheetName='MyFavouritePubs',

@RangeName ='MyRangeName'

 

--or if you are using integrated security!

spDMOExportToExcel @SourceServer='MyServer',

@QueryText = 'use MyDatabase

select Pubname, Address, Postcode from ##CambridgePubs',

@filename = 'C:\MyPubDatabase.xls',

@WorksheetName='MyFavouritePubs',

@RangeName ='MyRangeName'

 

 

/* Although this is a very handy stored procedure, you'll probably need to modify and add to it for particular purposes.

 

We use the DMO method because we like to dump build data into Excel spreadsheets e.g. users, logins, Job Histories. However, an ADODB version is very simple to do and can be made much faster for reads and writes.

 

We have just inserted values, but you can insert formulae and formatting numberformat) and create or change borders. You can, in fact, manipulate the spreadsheet in any way you like. When we do this, we record macros in Excel and then convert these macros to TSQL! Using the above example, it should be simple */

Robyn Page and Phil Factor

Author profile:


Robyn Page has worked as a consultant with Enformatica and USP Networks with a special interest in the provision of broadcast services over IP intranets. She was also a well known actress, being most famous for her role as Katie Williams, barmaid and man-eater in the Television Series Family Affairs. She is currently having a career break to raise a young family.

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 20 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

Search for other articles by Robyn Page and Phil Factor

Rate this article:   Avg rating: from a total of 188 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Robyn Page
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 07, 2007 at 4:57 PM
Message: A perfect example for beauty with the brains...


Subject: SQL Server Excel Workbench
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 08, 2007 at 12:32 AM
Message: Hi

It is nice

Chears

Lalith

Subject: SQL Server Excel Workbench
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 08, 2007 at 1:50 AM
Message: Geez, hectic stuff.

I’m still learning T-SQL, so most of the things used in here are a bit over my head.

My colleagues seem to think it's great, so well done.
I must agree on the previous comment posted by Anonymous on Robyn Page, extremely Intelligent and Beautiful - The perfect combination a man looks for in a woman...

Subject: SQL Server Excel Workbench
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 08, 2007 at 3:06 AM
Message: Congratulations!

I am very pleased to have had the opportunity in reading this article. It is a very valuable collection of code integrating T-Sql with excel manipulating. I am also surprised as on how it got to me precisely at a time when I have to transfer data from SQL Server into Excel. Besides all of this I must refer the sharing of such valuable code on behalf of Robin Page and Phil factor. Thanks a lot and keep up the intelligent work.

Subject: Where does Excel Need to Be Installed?
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 08, 2007 at 7:32 AM
Message: Nice work.

I'm no Excel expert, but where does Excel need to be installed for this?

We don't have it installed on the same box as the SQL Server (and we probably can't get approved to get it), although I have Office 2000 with Excel installed on my client machine.

Subject: SQL Workbench
Posted by: cje (view profile)
Posted on: Thursday, February 08, 2007 at 8:25 AM
Message: Excellent.

Subject: OpenDataSource "Non-Linked"
Posted by: colinsobers@bellsouth.net (view profile)
Posted on: Thursday, February 08, 2007 at 9:04 AM
Message: Man this is sooooo timely! Thanks a bunch Robyn. Keep up the good work. I'm going to be using these examples to bring in a whole bunch of spreadsheets in to our SQL Server and to write out to Excel also. Once again, very timely!

Peace and blessings
Colin

Subject: OpenDataSource "Non-Linked"
Posted by: colinsobers@bellsouth.net (view profile)
Posted on: Thursday, February 08, 2007 at 9:04 AM
Message: Man this is sooooo timely! Thanks a bunch Robyn. Keep up the good work. I'm going to be using these examples to bring in a whole bunch of spreadsheets in to our SQL Server and to write out to Excel also. Once again, very timely!

Peace and blessings
Colin

Subject: Robyn
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 08, 2007 at 9:41 AM
Message: Best looking tech person I have ever seen
would love to have her as a consultant

Subject: Re: Where does Excel Need to Be Installed?
Posted by: Robyn Page (view profile)
Posted on: Thursday, February 08, 2007 at 12:56 PM
Message: Until you do the full automation routine, you do not need to have Excel installed, as the Jet OLEDB driver is able to create, read to, or write from, excel files. When you do the automation, it will need to be on the server that has the database server on it, and the User with which SQL Server is operating must have the rights to access Excel.

For anyone who wants to extend what our Excel Automation routine does, there is a good worked example using C# at http://support.microsoft.com/kb/302084
which provides all the help you'd need to get started.

Subject: I agree with all the comments above
Posted by: Anonymous (not signed in)
Posted on: Friday, February 09, 2007 at 6:34 AM
Message: Also, Robyn, will you marry me? ;)

Subject: Re: I agree with all the comments above
Posted by: Phil Factor (view profile)
Posted on: Friday, February 09, 2007 at 11:19 AM
Message: Bah! Nobody ever says that to me after reading my articles. :-)


Subject: well done
Posted by: Anonymous (not signed in)
Posted on: Sunday, February 11, 2007 at 11:01 PM
Message: nice article, never would have guessed this is possible using TSQL
what I really like to know is, what keeps you Robyn busier, certainly the above article is not at the beginner level, and being able to come up with that means a serious amount of time & eduction invested in the past, is it the acting or the programming career that you're after ?

Hakan from Canada

Subject: Reading Excel Sheet - without sheet name?
Posted by: Anonymous (not signed in)
Posted on: Monday, February 12, 2007 at 11:02 AM
Message: Hi -
Your information is great, but I have one dilemma: I sometimes want to read the FIRST sheet of a Workbook, but users often name that sheet differently (e.g. Sheet1, MySheet, Orders, etc.) and I don't know in advance what they're going to call it. Using the OPENROWSET method,
[SELECT * FROM OPENROWSET('Microsoft.Jet... ]
how can I read the first sheet of the workbook, regardless of its name? And if not by this method, what is a simple way to do it (if one exists)?
Thank you!

Subject: Fantastic
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 14, 2007 at 1:05 AM
Message: Good work done by Robyn and excellent.
She looks beautiful.

Subject: Re: Reading Excel Sheet - without sheet name?
Posted by: Robyn Page (view profile)
Posted on: Wednesday, February 14, 2007 at 3:35 AM
Message: It is possible to specify the first sheet through automation, but I cannot find a way of doing it through a link or ADODB. The 'table' has to be named.
The best bet might be an automation procedure that accessed the first worksheet and then, if necessary, renamed it to a standard name of your choosing? Then it is all easy.
Has anyone else got an idea?

Subject: SQL Server Excel Workbench
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 14, 2007 at 10:54 AM
Message: Brilliant. I had done this kind of work previously using DTS but this approach makes it more portable and more reusable. I may even convert my old routines to use this method. If either of you (or both) are considering a DBA job in Canada let me know. Seriously, this caliber of work is worth a job offer. (In your case Phil I'd need your real name ... eventually)

Subject: SQL Server Excel Workbench
Posted by: fhanlon (view profile)
Posted on: Wednesday, February 14, 2007 at 10:56 AM
Message: My last post came out as Anonymous which I am not (always)

Subject: Re: SQL Server Excel Workbench-Job offer
Posted by: Phil Factor (view profile)
Posted on: Thursday, February 15, 2007 at 2:29 AM
Message: We're both pleased you liked the article. We don't decry the use of DTS for doing Excel integration work-we use DTS a fair amount, but sometimes one really needs to do more than DTS was designed for.

We're both very flattered by the job offer!

Subject: Re: SQL Server Excel Workbench
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 15, 2007 at 3:10 AM
Message: Ma'm Robyn,
you made mention about 'an ADODB
version is very simple to do and can be made much faster for reads and writes'. Do you mean an excel workbench adodb version? If so, please write a sql server excel workbench using an ADODB. Thank you.

Subject: An ADODB version
Posted by: Robyn Page (view profile)
Posted on: Saturday, February 17, 2007 at 11:53 AM
Message: Yes. Phil and I adapted Phil's existing DMO version simply because he was using it to save configuration stuff from a whole lot of Servers. If you use ADODB, you can use the 'Range(MyRange).CopyFromRecordset rs' command in order to fill the cells, which is much faster than using the iterative approach we used.

Subject: SQL Server Excel Workbench
Posted by: Anonymous (not signed in)
Posted on: Tuesday, February 20, 2007 at 10:58 PM
Message: Hi,

Thanks for your artical on this subject

I want to know more about scripting for SQL Server 2000.

Subject: SQL Server Excel Workbench
Posted by: Anonymous (not signed in)
Posted on: Friday, February 23, 2007 at 4:35 PM
Message: Thanks, some great ideas.

But spExecute_ADODB_SQL fails if you loop more than 64 times.

It needs a needs an extra step:
exec @hr = sp_OAMethod @objConnection, 'Close'

Subject: SQL Server Excel Workbench
Posted by: Anonymous (not signed in)
Posted on: Friday, February 23, 2007 at 5:05 PM
Message: Thanks, some great ideas.

But spExecute_ADODB_SQL fails if you loop more than 64 times.

It needs a needs an extra step:
exec @hr = sp_OAMethod @objConnection, 'Close'

Subject: Re: spExecute_ADODB_SQL fails if you loop more than 64 times.
Posted by: Phil Factor (view profile)
Posted on: Saturday, February 24, 2007 at 3:27 AM
Message: Oops! Quite right. Mea Culpa! Thanks very much for the bugfix. I must confess that neither Robyn or I looped it 64 times!

Subject: SQL Server Excel Workbench
Posted by: Anonymous (not signed in)
Posted on: Monday, March 19, 2007 at 1:22 AM
Message: Thanks, Robyn & Phil, for an article that was both informative and useful, especially to people like me who were not trained as DBAs but drifted into using SQL as part of their job.

I would have posted this earlier but I was too busy adapting bits of your C.R.U.D for my own work.

Subject: Really cool script
Posted by: Anonymous (not signed in)
Posted on: Monday, March 26, 2007 at 8:48 AM
Message: Thank you for this magnificent script.

I used the script for my reports and discovered, that if you change some small thing in the script you can actually reuse 1 file for many worksheets.

This is the code that I Used to be able to append or create a new workbook using your code startin with a few lines of your code first.


--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

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

Hope it helps someone.

Regards, Denis

Subject: Re: Really cool script.
Posted by: Phil Factor (view profile)
Posted on: Tuesday, March 27, 2007 at 8:14 AM
Message: Yeah. That is great, Denis. Thanks for the contribution. Much appreciated.

Subject: Great scripts
Posted by: Anonymous (not signed in)
Posted on: Thursday, March 29, 2007 at 11:13 AM
Message: Hi Robyn and Phil,
Some great scripts, nice to see several different solutions presented to a particular challenge.

One question about the ADO script, you replace in @DDL the stub %worksheet with the worksheet name passed in the @worksheet parameter, but please can you give an example of how it is used?

Cheers

David

Subject: ge: Great scripts
Posted by: Phil Factor (view profile)
Posted on: Friday, March 30, 2007 at 8:16 AM
Message: There are three examples of the use of the stored procedure just after the source of the stored procedure. The DDL is the SQL Data Definition Language command that you want to send to the data source. The DDL commands are defined by ODBC. These ODMC sources must be able to respond to a certain number of these commands, depending whether the ODBC driver is Minimun, Core or Extended.

I hope that helps!

see http://www.jsoftware.com/help/user/sql_statements.htm

Subject: Dumb question
Posted by: Anonymous (not signed in)
Posted on: Tuesday, April 03, 2007 at 11:53 AM
Message: Hi,
Really cool stuff. One thing I seem to be missing though; how do you fill the spreadsheet with data from sql server as opposed to explicitly indicating the values you want to insert into the spreadsheet?

My apologies for the goofy question. It's probably in there somewhere and I missed it.

Many thanks!

Subject: next step - array functions
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 12, 2007 at 6:49 AM
Message: Another Excellent Article! Thanks for the great detail Robyn and Phil!

Using similiar steps as in your article, I am creating an Excel worksheet with two sheets from SQL; the second sheet is a summary of the data in the first sheet.
In my testing I create the summary sheet with SQL as well and that works of course.
For formating (ease of viewing) however I would like to use Excel functions in the second sheet.
Examples:
{=SUMPRODUCT((Sheet1!J2:J40)*(Sheet1!$I$2:$I$40="PartA"))}
or
{=SUM(VALUE(Sheet1!J2:J40))}
or
{=COUNT(VALUE(Sheet1!J2:J40))}

The "Rub" is that to enter an array function into an Excel Cell you must type it without the curly brackets and hit CTRL + SHIFT + ENTER.

I cannot recreate this in SQL to insert the Array Function into the Cell...

Any ideas?

I thought about recording a Macro and having that run when the Worksheet was opened; how do I insert a Macro into a new sheet from SQL?
At the end of my SQL Script I also email the Excel as an attachment to the User - they need to be able to open the Worksheet with few if not zero steps... (nothing new there...)

Thanks!
Todd

Subject: 64-bit server issue for ADODB
Posted by: Anonymous (not signed in)
Posted on: Friday, April 13, 2007 at 10:09 AM
Message: Great article! Finally I've found other who are trying to solve the same problems that I am.

One issue I've encountered though. The Microsoft.Jet.OLEDB.4.0 driver does not exist for 64-bit Windows Servers AND Microsoft apparently has no plans to create one. Very little info from Microsoft on this topic (http://connect.microsoft.com/SQLServer/feedback/Vote.aspx?FeedbackID=125117).

Subject: re: Next Step Array functions
Posted by: Phil Factor (view profile)
Posted on: Saturday, April 14, 2007 at 8:14 AM
Message: You can insert formulas into Excel just as easily as you do values from TSQL. I'm sorry if we didn't quite make that clear. you just assign your formula to the .FORMULA attribute of the cell or cell range. You'll see in the example in the article where we assign to the .VALUE of a cell to insert the resultset values into excel. You just do the same, but assign to the .FORMULA

Good luck and let us know how you get on!

Subject: re 64-bit Server Issue for ADODB
Posted by: Phil Factor (view profile)
Posted on: Saturday, April 14, 2007 at 8:24 AM
Message: This is an 'interesting' problem. The way I get around this is to use a linked SQL Server Express 'Grunt' server to do this sort of job, on a 32-bit machine. Works fine: no hassle. But it aint the perfect solution.

I've heard that one can get ODBC to work by running C:\WINDOWS\SysWOW64\odbcad32.exe but I've never tried it.

Subject: Script generating an error...
Posted by: Anonymous (not signed in)
Posted on: Thursday, June 21, 2007 at 10:56 AM
Message: Hi Robyn,
Hi Phil,
Thanks for the great work you've been doing. I think this would be a very useful script for me to have. However having copied it from the site (in steps of course)I find that I'm getting the error... "Msg 50000, Level 16, State 1, Procedure spDMOExportToExcel, Line 249
Error whilst Creating the Excel Application, on MyServer, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]"
So I know it has to do with the excel application (which exists) but not what to do about it. Any light for the darkness of this newbie?
Thanks

Subject: Re: Script generating an error...
Posted by: Phil Factor (view profile)
Posted on: Thursday, June 21, 2007 at 1:25 PM
Message: Copy the script from the box at the top of the article. it is more reliable. Click on where CODE DOWNLOAD is written. The error means that there is a parameter missing in the call to the sp_oaMethod is being called to create the OLE object. As far as I'm aware, the code itself is OK, so I suspect something got lost when you pasted it in. Let us know if that doesn't solve the problem

We're glad you like the article. I use the code a lot myself.

Subject: Re: Re: Script generating an error...
Posted by: Anonymous (not signed in)
Posted on: Friday, June 22, 2007 at 11:56 AM
Message: Hi Phil (and Robyn), thanks for the quick response. I did as suggested but I still get that same error message. I thought it might be related to the surface area configuration so I ensured that OLE automation was enabled as well as xp_cmdshell. However that hasn't made a difference. Any other ideas? I've already got three assignments that could use this approach once I get it working. Thanks again...

Subject: error 0x80004005
Posted by: Moses (not signed in)
Posted on: Friday, June 22, 2007 at 3:01 PM
Message: Hi Robyn and Phil,
as everybody, i love your solution. It was going to help me a lot (36 servers on which some results are to be published).
I tested on my ntb - all went fine.
But ... On the first server I went to test, I can't get after EXEC @hr=sp_OAMethod @objconnection, 'Open' - I keep getting that weird error code, with no additional info ...Did you ever get this one ?
Thanks a lot !

Subject: Re: Re: Script generating an error...
Posted by: Anonymous (not signed in)
Posted on: Monday, June 25, 2007 at 2:35 AM
Message: Hi Phil (and Robyn), thanks for the quick response. I did as suggested but I still get that same error message. I thought it might be related to the surface area configuration so I ensured that OLE automation was enabled as well as xp_cmdshell. However that hasn't made a difference. Any other ideas? I've already got three assignments that could use this approach once I get it working. Thanks again...

Subject: Deleting the spreadsheet rows
Posted by: Anonymous (not signed in)
Posted on: Monday, June 25, 2007 at 7:40 PM
Message: Great work on this. Extremely helpful.

I am able to populate the spreadsheet with data selected from my sql db using an "insert into", and I can update specified rows.

But I cannot determine how to delete all rows. Maybe I missed something in your detail. I tried the following script

delete OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="\\FS1\DriveF\Misc-Admin\JobInfo\New.xls"
;User ID=Admin;Password=;Extended properties=Excel 8.0')
...Newtab$

and get the following error

Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' could not delete from table 'Newtab$'. There was a recoverable, provider-specific error, such as an RPC failure.
[OLE/DB provider returned message: Deleting data in a linked table is not supported by this ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

If I remove the ...Newtab$, I get the following error

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.

Could you plz assist. Thanks



Subject: Deleting the spreadsheet rows
Posted by: Anonymous (not signed in)
Posted on: Monday, June 25, 2007 at 7:42 PM
Message: Great work on this. Extremely helpful.

I am able to populate the spreadsheet with data selected from my sql db using an "insert into", and I can update specified rows.

But I cannot determine how to delete all rows. Maybe I missed something in your detail. I tried the following script

delete OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="\\FS1\DriveF\Misc-Admin\JobInfo\New.xls"
;User ID=Admin;Password=;Extended properties=Excel 8.0')
...Newtab$

and get the following error

Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' could not delete from table 'Newtab$'. There was a recoverable, provider-specific error, such as an RPC failure.
[OLE/DB provider returned message: Deleting data in a linked table is not supported by this ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

If I remove the ...Newtab$, I get the following error

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.

Could you plz assist. Thanks



Subject: re:Deleting the spreadsheet rows
Posted by: Phil Factor (view profile)
Posted on: Wednesday, June 27, 2007 at 1:23 PM
Message: Yes. Isn't it strange. It looks from the error message that the OLEDB provider doesn't even support deletion of rows. I often have to shrug and go back to doing a full automation of Excel when something simple doesn't work!
Try the link approach, but I suspect that will not work either

Subject: 255 chars
Posted by: Steve Davis (not signed in)
Posted on: Thursday, August 02, 2007 at 9:22 AM
Message: Good morning and thanks for the above. The issue that i am having with this and hope that you have found a workaround is for fields with more than 255 chars. Seems that utilizing Microsoft.Jet.OLEDB.4.0 , Excel 8.0 will throw an error "Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated." I have tried the tweak at http://support.microsoft.com/kb/189897/EN-US/ to no avail. any help would be greatlt appreciated!
steve@nyctechsupport.com

Subject: Excel never ends
Posted by: Anonymous (not signed in)
Posted on: Monday, August 13, 2007 at 10:30 AM
Message: Parfait! Exactly the code I was looking for.
I just have a trouble when calling the procedure from a loop using a cursor with more than 41 calls. Excel applications never end and my server isn't able to create new ones.
Any ideas?

Subject: Excel never ends 2
Posted by: Franck (not signed in)
Posted on: Monday, August 13, 2007 at 11:03 AM
Message: Try this code, after having updated Postcode column :
update ##CambridgePubs set Postcode=replace(Postcode,' ','_')


DECLARE @four varchar(7)
DECLARE @file varchar(100)
DECLARE @query varchar(8000)
DECLARE @collist varchar(500)
DECLARE @quote varchar(1)

set @quote=''''

DECLARE FOURNISSEUR CURSOR FOR
select distinct Postcode from ##CambridgePubs

OPEN FOURNISSEUR
FETCH NEXT
FROM FOURNISSEUR
INTO @four

WHILE @@FETCH_STATUS = 0 BEGIN
SET @query = 'select * from ##CambridgePubs where Postcode =''' + @four + ''''
SET @file = 'C:\temp\Manquants\' +@four + '.xls'
EXEC spDMOExportToExcel2
@SourceServer=NULL,
@QueryText = @query,
@filename = @file,
@WorksheetName='A',
@RangeName ='A'
FETCH NEXT
FROM FOURNISSEUR
INTO @four
END
CLOSE FOURNISSEUR
DEALLOCATE FOURNISSEUR


Subject: Looking for example code Accces VB module and sp_OACreate
Posted by: Anonymous (not signed in)
Posted on: Thursday, August 23, 2007 at 7:03 PM
Message: I want to see if I can create an instance of access application and a module with code to compact an access .mdb file from sql server so I can run this from a secure sever using msdb to schedule the task. Anyone know where I can find an example of sp_OACreate working with MS Access?

Subject: Reading the table name from Excel
Posted by: TimothAWiseman@gmail.com (not signed in)
Posted on: Monday, October 01, 2007 at 11:14 AM
Message: Based on the above article, I started using this script to read the table name for the first sheet of an excel file. I am still searching for a more efficient way and perhaps one that does not use OLE if anyone knows one.

declare @sheetname varchar(1000)
declare @objExcel int
declare @objworkbook int
declare @objworksheet int
declare @error int


EXEC @error=sp_OACreate 'Excel.Application', @objExcel OUT --This opens an instance of excel, it must be closed

if error = 0
exec @error = sp_OAMethod @objExcel, 'Workbooks.open', @objWorkbook OUT, @filename = @fullfile

if error = 0
exec @error =sp_OAMethod @objWorkbook, 'Worksheets(1)', @objWorkSheet OUT

if error = 0
exec @error = sp_OAGetProperty @objworksheet, 'name', @sheetname OUT

if error = 0
exec @error = sp_OAMethod @objWorkBook, 'Close'

exec sp_OAdestroy @objworkbook

exec sp_OAdestroy @objWorkSheet

EXEC @error = sp_OAMethod @objExcel, 'quit'

exec sp_OAdestroy @objExcel

Subject: great article
Posted by: lava kafle (not signed in)
Posted on: Wednesday, October 03, 2007 at 9:18 PM
Message: thanks for such a great innovative solution

Subject: Defining Range And/Or Header Row when Selecting from Sheet
Posted by: Jason (view profile)
Posted on: Monday, October 08, 2007 at 9:43 AM
Message: If the spreadsheet had the column headings in the third row, is there anyway you can specify that when doing the select from the spreadsheet.

I often get spreadsheets where someone had decided to use the first couple of rows for comments, and actually start the data on row 3 or 4.

Subject: Formula in sql table
Posted by: Anonymous (not signed in)
Posted on: Monday, October 22, 2007 at 8:47 AM
Message: Hi

I want the formulas in SQL table to be rolled on to excel sheet and fetch the values again to a new table in sql.
When I rolled the formula table to excel the formula was prefixed by '(quote) and shows as a formula only instead of getting the value for the linked sheets.

Can u help me on this.

Subject: Formula in sql table
Posted by: Anonymous (not signed in)
Posted on: Monday, October 22, 2007 at 8:50 AM
Message: Sample data that was roled on to excel sheet. This is the 1st Row.

Column1 =+'Main menu'!$D$2
Column2 =+Date!$N$4
Column3 =+'Daily-Sum'!D8
Column4 =+'Daily-tab3'!F25
Column5 0

Subject: Using ADODB method and dealing with NULLs in Excel worksheets
Posted by: Lisa (not signed in)
Posted on: Thursday, November 01, 2007 at 10:18 AM
Message: Hi,

I'm using the ADODB.Connection object method. Everything is working fine except all the NULL values "sink to the bottom" of the Excel worksheet. Is there anyway way to sort the Excel worksheet once all the data is already in there?

If I try to run a sp_OACreate 'Excel.Application' object, do I need Excel to be installed on my SQL Server? I'm assuming yes, because I get an invalid class when I try to do it.

Many thanks!
Lisa

Subject: How do we use a variable in place of Datasource in OPENDATASOURCE
Posted by: tatini (not signed in)
Posted on: Friday, November 02, 2007 at 12:19 PM
Message: Nice article which gives what i have been looking for, being a novice in t-sql.
I have used this script myself and i was wondering whether we can use a variable in place of Datasource part of OPENDATASOURCE

I appreciate the good work. :)

Subject: Variable in place of datasource
Posted by: tatini (not signed in)
Posted on: Friday, November 02, 2007 at 5:17 PM
Message: I achieved the solution for my above post. I used dynamic sql to build the OPENDATASOURCE.
Now, is there any way to read a specific sheet from the excel sheet, say first sheet or second sheet?

Subject: Error: Cannot access 'CambridgePubs.xls'
Posted by: Anonymous (not signed in)
Posted on: Tuesday, November 13, 2007 at 12:14 PM
Message: Hi,

Great article. This may be the solution I have been searching for but when I tried your example, I receive the above error upon trying to open the Excel file after it has been created.

Any suggestions?

Subject: Error: Cannot access 'CambridgePubs.xls'
Posted by: Anonymous (not signed in)
Posted on: Tuesday, November 13, 2007 at 12:32 PM
Message: Hi,

Great article. This may be the solution I have been searching for but when I tried your example, I receive the above error upon trying to open the Excel file after it has been created.

Any suggestions?

Subject: Dynamically creating columnlist
Posted by: Lady (not signed in)
Posted on: Thursday, November 29, 2007 at 10:50 AM
Message: Hey girl
great article, it has helped very much keep it up, i wanted to ask if there is a way to dynamically create the column heading list for the excel table by using a function to get the column list & datatype of any given table and use the return value as a parameter into this part

spExecute_ADODB_SQL @DDL='Create table myexceltable
(return value of function)',
@DataSource ='C:\CambridgePubs.xls'

any ideas will be appreciated
thanks

Subject: re: Dynamically creating columnlist
Posted by: Phil Factor (view profile)
Posted on: Wednesday, December 05, 2007 at 8:45 AM
Message: spDMOExportToExcel already does this to create the column headings in the excel spreadsheet.

Subject: Problems with spExecute_ADODB_SQL
Posted by: uriol17 (view profile)
Posted on: Wednesday, December 12, 2007 at 5:52 AM
Message: Hi,

I have a problem using spExecute_ADODB_SQL.
I don´t have problems creating the excel file and insert into with values.
But when I try to insert into with a select of a sql table like this

spExecute_ADODB_SQL @DDL='insert into Excel(code,Description)
SELECT Code,Description FROM Table',
@DataSource ='c:\excel.xls'

the programs returns me an error because the database is open or the object is only read.

I don´t understand whats happen because with value I can write in the excel file

Sorry for my bad english and thanks for all.

Subject: 2 worksheets
Posted by: Anonymous (not signed in)
Posted on: Wednesday, December 19, 2007 at 11:26 PM
Message: Hi,
I am new to T-SQL.
How can i have two sheets with different data in the same workbook created at 'C:\MyPubDatabase.xls'. I need to have different sets of records in that?

Subject: re: 2 worksheets
Posted by: Robyn Page (view profile)
Posted on: Thursday, December 20, 2007 at 10:27 AM
Message: You'll notice that spDMOExportToExcel creates a new workbook and puts a worksheet in it.
The whole purpose of these workbenches is to kick-start you into developing your own code. In this case, Phil and I just use the code to show you how to create a workbook and then a worksheet. This should be enough to get you started with your own routine. It should be pretty easy to modify the code to put a new worksheet in an existing workbook, but you won't catch us doing it for you!
Let us know how you get on.

Subject: Re: Problems with spExecute_ADODB_SQL
Posted by: Robyn Page (view profile)
Posted on: Thursday, December 20, 2007 at 10:34 AM
Message: This generally happens when you open your excel file in Excel to have a look at the data, and forget to close it. The routine will not open a spreadsheet if it is locked for use by another application.
Another error one sometimes gets with this routine is when you use TSQL tather than ODBC/OLEDB SQL. Remember that you are communicating with an ODBC database, not SQL Server.

Subject: Error reading from excel
Posted by: Dunc (not signed in)
Posted on: Wednesday, January 09, 2008 at 12:42 PM
Message: I cannot read from the spreadsheet - whether I link the spreadsheet as a linked server or not I get the same error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Subject: Alphabet question
Posted by: Terrie (view profile)
Posted on: Wednesday, January 23, 2008 at 3:54 PM
Message: Hi :) First off - amazing article!!! Thanks so much for publishing it.

I am running into a problem with the section that sets the range to bold the headers. If there are 26 columns in my sheet, I get an error. Here's a simplifed mock-up I did so I could look at the affects:

Declare @Alphabet varchar(26), @currentColumn int

SET @Alphabet='ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SET @currentColumn = 1

WHILE (@currentColumn <= 30)
begin
print'Range("A1:'+substring(@alphabet,@currentColumn / 26,1)+substring(@alphabet,@currentColumn % 26,1)+'1'+'").font.bold'

SET @currentColumn = @currentColumn + 1
end

-------------This returns:---------------
Range("A1:A1").font.bold
Range("A1:B1").font.bold
Range("A1:C1").font.bold
Range("A1:D1").font.bold
Range("A1:E1").font.bold
Range("A1:F1").font.bold
Range("A1:G1").font.bold
Range("A1:H1").font.bold
Range("A1:I1").font.bold
Range("A1:J1").font.bold
Range("A1:K1").font.bold
Range("A1:L1").font.bold
Range("A1:M1").font.bold
Range("A1:N1").font.bold
Range("A1:O1").font.bold
Range("A1:P1").font.bold
Range("A1:Q1").font.bold
Range("A1:R1").font.bold
Range("A1:S1").font.bold
Range("A1:T1").font.bold
Range("A1:U1").font.bold
Range("A1:V1").font.bold
Range("A1:W1").font.bold
Range("A1:X1").font.bold
Range("A1:Y1").font.bold
Range("A1:A1").font.bold
Range("A1:AA1").font.bold
Range("A1:AB1").font.bold
Range("A1:AC1").font.bold
Range("A1:AD1").font.bold
---------------------------------------
Please note that there is no:
Range("A1:Z1").font.bold
which makes sense because 26/26 is of course simply 1 with no remainder.

Any thoughts on how I can bypass this? I love the elegance of this and would love to use it if I can get the Z problem solved.

Thanks!!!!

Subject: Sending the file as an attachment from within the proc.
Posted by: Anonymous (not signed in)
Posted on: Monday, February 11, 2008 at 3:46 PM
Message: Hello,
As the many previous posts declared... This is an awesome article. Thanks for posting it. I have many automated routines on multiple servers that currently use ActiveX scripts to write to the .xls files. I will be migrating to this as time permits. I have one question for you though. I was unable to email the file from the same sp that builds the .xls. The file is not unlocked until the sp completes. It was easy enough to combat this by creating a 2nd proc to use as an additional step in the job, but I'd sure like to know how to release the file and mail it from the same proc. Any input would be much appreciated. Thanks again! Bob.

...this shows the basic mail sql I'm using.
if
@cnt > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_Name = N'default',
@recipients= @distList,
@body= @bodyText,
@subject = @SubjectText,
@file_attachments = 'C:\Mail.Files\ServiceCalls.xls'
END

Subject: Alphabet question
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 13, 2008 at 5:43 AM
Message: Hi Terrie,

I encountered the same problem. If your column count is 26 then 26 % 26 = 0 which nets an empty string from the alphabet.

I got around this by creating a small function to build the cell reference. In it it checks for column numbers that are evenly divided by 26 and sets the value to 'Z'.


Subject: Error with sp_OACreate
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 13, 2008 at 5:48 AM
Message: At one point during testing I received an error that caused my test procedure to fail.

Now when I run it sp_OACreate returns the error "Server execution failed" when trying to create the excel.application object. Could it be because the object already exists? If so, how do I delete the object? I figure that I could call sp_OADestroy, but I don't know the object's ID.

Any ideas?

Thanks!

Subject: Error with sp_OACreate
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 13, 2008 at 7:22 AM
Message: At one point during testing I received an error that caused my test procedure to fail.

Now when I run it sp_OACreate returns the error "Server execution failed" when trying to create the excel.application object. Could it be because the object already exists? If so, how do I delete the object? I figure that I could call sp_OADestroy, but I don't know the object's ID.

Any ideas?

Thanks!

Subject: Error with sp_OACreate
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 13, 2008 at 7:22 AM
Message: At one point during testing I received an error that caused my test procedure to fail.

Now when I run it sp_OACreate returns the error "Server execution failed" when trying to create the excel.application object. Could it be because the object already exists? If so, how do I delete the object? I figure that I could call sp_OADestroy, but I don't know the object's ID.

Any ideas?

Thanks!

Subject: Excel Import using SSIS
Posted by: Faith (not signed in)
Posted on: Wednesday, February 20, 2008 at 10:53 AM
Message: I have 25 columns in my excel which is the second row in the excel. I have merged column comprising of 3-4 columns so like wise 7 columns as the 1st row. How can I suggest SSIS that 2ns row in the header or column names?
Also is there any way using SSIS that I can refer to specific cell in the excel?

Subject: Drop table (tab)
Posted by: Philippe (not signed in)
Posted on: Monday, February 25, 2008 at 3:34 PM
Message: Hello,
Too much of a good stuff here but you know we are all hungry for more.
This one
--you could drop it again!
spExecute_ADODB_SQL @DDL='drop table CambridgePubs',
@DataSource ='c:\CambridgePubs.xls'
Does not seem to work, I get error.
I want to remove the excel tab from the workbook then I can re-create it. Doing it this way let me keep the rest of the workbook intact and populate the tab I re-create from cell A2. I can create the tab, I cannot just delete it. Is that possible?

Subject: Drop table (tab)
Posted by: Philippe (not signed in)
Posted on: Monday, February 25, 2008 at 4:01 PM
Message: Hello,
Too much of a good stuff here but you know we are all hungry for more.
This one
--you could drop it again!
spExecute_ADODB_SQL @DDL='drop table CambridgePubs',
@DataSource ='c:\CambridgePubs.xls'
Does not seem to work, I get error.
I want to remove the excel tab from the workbook then I can re-create it. Doing it this way let me keep the rest of the workbook intact and populate the tab I re-create from cell A2. I can create the tab, I cannot just delete it. Is that possible?

Subject: Drop table (tab)
Posted by: Philippe (not signed in)
Posted on: Tuesday, February 26, 2008 at 12:17 PM
Message: Hello,
Too much of a good stuff here but you know we are all hungry for more.
This one
--you could drop it again!
spExecute_ADODB_SQL @DDL='drop table CambridgePubs',
@DataSource ='c:\CambridgePubs.xls'
Does not seem to work, I get error.
I want to remove the excel tab from the workbook then I can re-create it. Doing it this way let me keep the rest of the workbook intact and populate the tab I re-create from cell A2. I can create the tab, I cannot just delete it. Is that possible?

Subject: Thnx
Posted by: Anonymous (not signed in)
Posted on: Tuesday, February 26, 2008 at 8:48 PM
Message: I'm new with SQL and this really helps me generating reports...ty...

Subject: Great
Posted by: Anonymous (not signed in)
Posted on: Wednesday, March 05, 2008 at 7:08 AM
Message: Thanks for all this... great... thanz once again

Subject: Great
Posted by: Anonymous (not signed in)
Posted on: Wednesday, March 05, 2008 at 7:08 AM
Message: Thanks for all this... great... thanz once again

Subject: excellent article
Posted by: Anonymous (not signed in)
Posted on: Monday, March 24, 2008 at 9:31 PM
Message: one of the best that I hav read recently, excellent job

Subject: SQL Server Excel Workbench
Posted by: Andylysm (not signed in)
Posted on: Tuesday, March 25, 2008 at 3:13 AM
Message: why error this :
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'CambridgePubs'.

Subject: Thanks a lot
Posted by: Vladislav (not signed in)
Posted on: Monday, April 14, 2008 at 4:40 AM
Message: Its just that I've been looking for. Thank you very much!

Subject: Passwords
Posted by: Neil (view profile)
Posted on: Wednesday, April 16, 2008 at 4:27 AM
Message: Hi,

If I try and add a password to the .xls file then the OPENROWSET/OPENDATASOURCE commands return an error
[OLE/DB provider returned message: Unrecognized database format 'C:\CambridgePubs.xls'.]

Any Ideas?

EXEC sp_addlinkedserver 'CambridgePubDatabase',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\CambridgePubs.xls',
NULL,
';PWD=password',
'Excel 8.0'

Subject: Re: I agree with all the comments above
Posted by: Anonymous (not signed in)
Posted on: Tuesday, April 22, 2008 at 1:54 PM
Message: Phil if you posted a pic like the beautiful Robyn maybe some of our lady collegues would give you the kudos. Just a suggestion....lol Thanks Robyn and Phil for all your articles.

Subject: Naming a table after the date.
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 24, 2008 at 7:58 AM
Message: I am trying to create the table name using the current date, but I keep getting Syntax errors, is it possible to use a varible as the table name?

Subject: Naming a table after the date.
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 24, 2008 at 7:59 AM
Message: I am trying to create the table name using the current date, but I keep getting Syntax errors, is it possible to use a varible as the table name?

Subject: Naming a table after the date.
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 24, 2008 at 8:05 AM
Message: I am trying to create the table name using the current date, but I keep getting Syntax errors, is it possible to use a varible as the table name?

Subject: Naming a table after the date.
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 24, 2008 at 9:13 AM
Message: I am trying to create the table name using the current date, but I keep getting Syntax errors, is it possible to use a varible as the table name?

Subject: Naming a table after the date.
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 24, 2008 at 9:31 AM
Message: I am trying to create the table name using the current date, but I keep getting Syntax errors, is it possible to use a varible as the table name?

Subject: Thanks
Posted by: MVV (not signed in)
Posted on: Tuesday, April 29, 2008 at 2:39 AM
Message: Nice work.I stumbled on this page googling and now it's bookmarked.

Thank you both a lot for sharing this knowledge.

Subject: re: Naming a table after the date.
Posted by: Anonymous (not signed in)
Posted on: Tuesday, April 29, 2008 at 11:07 AM
Message: I am sure you cannot do a Table Create and use the date to create the table name.

Subject: re: Naming a table after the date.
Posted by: Anonymous (not signed in)
Posted on: Tuesday, April 29, 2008 at 1:19 PM
Message: Using "spExecute_ADODB_SQL" and "exec sp_executesql", this will allow you to create a worksheet based on date. You may be able to modify it to allow for another variable name.

declare
@cmd nvarchar(2000),
@StoreProcedureName varchar(30),
@ParameterName varchar(30),
@tablename varchar(30),
@therows varchar(60),
@thesource varchar(30),
@filename varchar(30)

set @StoreProcedureName = 'spExecute_ADODB_SQL @DDL= '
set @ParameterName = '''Create Table '
set @tablename = CONVERT(VARCHAR(8),GetDate(),112)
set @therows = ' (Pubname Text, Address Text, Postcode Text)'''
set @thesource = '@DataSource'
set @filename = '''C:\CambridgePubs.xls'''
set @cmd = @StoreProcedureName + @ParameterName + @tablename + @therows + ', ' + @thesource + '=' + @filename

exec sp_executesql @cmd

Subject: re: Naming a table after the date.
Posted by: Anonymous (not signed in)
Posted on: Tuesday, April 29, 2008 at 1:28 PM
Message: Using "spExecute_ADODB_SQL" and "exec sp_executesql", this will allow you to create a worksheet based on date. You may be able to modify it to allow for another variable name.

declare
@cmd nvarchar(2000),
@StoreProcedureName varchar(30),
@ParameterName varchar(30),
@tablename varchar(30),
@therows varchar(60),
@thesource varchar(30),
@filename varchar(30)

set @StoreProcedureName = 'spExecute_ADODB_SQL @DDL= '
set @ParameterName = '''Create Table '
set @tablename = CONVERT(VARCHAR(8),GetDate(),112)
set @therows = ' (Pubname Text, Address Text, Postcode Text)'''
set @thesource = '@DataSource'
set @filename = '''C:\CambridgePubs.xls'''
set @cmd = @StoreProcedureName + @ParameterName + @tablename + @therows + ', ' + @thesource + '=' + @filename

exec sp_executesql @cmd

Subject: Count no. of worksheets in Excel workbook
Posted by: Srinivas Alwala (not signed in)
Posted on: Friday, May 09, 2008 at 1:38 AM
Message: Hi,

I would like to count no. of excel sheets in excel workbook without opening the file.

Any help will be appreciated.

Regards,

Srinivas Alwala

Subject: 64 Bit server issue
Posted by: Chris Frank (not signed in)
Posted on: Wednesday, June 04, 2008 at 4:56 PM
Message: In reference to an earlier post ....

you answered....

This is an 'interesting' problem. The way I get around this is to use a linked SQL Server Express 'Grunt' server to do this sort of job, on a 32-bit machine. Works fine: no hassle. But it aint the perfect solution.


Could you put a little detail around how you used the 'grunt' server to do this. I have tried setting something up on a linked server and then called it from the 64 bit server but I am still having a issue.

Any help is aprreciated.

Thanks Chris

Subject: Re: worksheet names in ADO...
Posted by: Anonymous (not signed in)
Posted on: Wednesday, June 11, 2008 at 5:07 PM
Message: The default worksheet names in an Excel workbook are named [Sheet1$],[Sheet2$],[Sheet3$], etc. as far as ADO is concerned.

If you renamed the sheets, then just put square brackets around the worksheet name.

One thing about using ADO to pump data into an existing, preformatted Excel spreadsheet is that any formatting done to the first row of the spreadsheet will be pushed down into the cells as well (just like DTS will do) ...

To determine the number of "tables" in a workbook, you might be able to use the OpenSchema method (see any reference on ADODB.Connection object)...

I haven't tried this per se with Excel, but there are docs out there for doing just this for Access & SQL Server...

Subject: Excel automation & ADO...
Posted by: Anonymous (not signed in)
Posted on: Wednesday, June 11, 2008 at 5:18 PM
Message: Also, Excel's CopyFromRecordset method (method of Range object), when passed an ADO recordset, far and away crushes any other method for getting data into Excel from a recordset. Make sure you're not pushing more than 65K records (Excel 2000-2003), and you're golden.

Next fastest is walking through the ADO recordset from the database, and using ADO methods on the Excel recordset (AddNew, field-by-field update).
This is pretty much what DTS does behind the scenes when pumping data to an Excel file.

Way slowest is using Excel automation to do a record-by-record, field-by-field insert. Not even worth doing in a worst-case scenario if you have more than 10 or 20 records. I hyperbolize, but not much...

Subject: Re: worksheet names in ADO...
Posted by: Anonymous (not signed in)
Posted on: Wednesday, June 11, 2008 at 5:31 PM
Message: The default worksheet names in an Excel workbook are named [Sheet1$],[Sheet2$],[Sheet3$], etc. as far as ADO is concerned.

If you renamed the sheets, then just put square brackets around the worksheet name.

One thing about using ADO to pump data into an existing, preformatted Excel spreadsheet is that any formatting done to the first row of the spreadsheet will be pushed down into the cells as well (just like DTS will do) ...

To determine the number of "tables" in a workbook, you might be able to use the OpenSchema method (see any reference on ADODB.Connection object)...

I haven't tried this per se with Excel, but there are docs out there for doing just this for Access & SQL Server...

Subject: @QueryText
Posted by: simple-Ton (view profile)
Posted on: Tuesday, June 17, 2008 at 9:10 PM
Message: Awesome stuff.

One propblem I've faced - and YES! I AM a newbie.

If the querySQL is too long - I get the feeling it gets truncated and the SP falls over.

I have tried altering the SP @queryText parameter to increase the allowable string length - but ot no avail.

Do I need to make this change elsewhere.

Subject: Pretty useful stuff
Posted by: Arvind (view profile)
Posted on: Friday, June 20, 2008 at 6:55 PM
Message: This is useful code for excel manipulation from sql server. As for Phil Factor..it has really got me interested in finding out who this was ! **looking up old news to see who this person was** AND Robyn Page..dont remember watching the show ..but now i just might ! so what were you first..an actress or a developer? :)

Subject: Fraud
Posted by: Anonymous (not signed in)
Posted on: Tuesday, July 15, 2008 at 12:40 PM
Message: Robyn Page is no developer. LOL! This is a haox to induce interest in the author and posts. Why not separate profiles?

Subject: gives error
Posted by: Sreekumar (not signed in)
Posted on: Wednesday, July 30, 2008 at 3:10 PM
Message: Msg 50000, Level 16, State 1, Procedure spDMOExportToExcel, Line 260
Error whilst Creating the Excel Application, on DTRTW0871IIDV02, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

Subject: Thank you
Posted by: Anonymous (not signed in)
Posted on: Friday, August 01, 2008 at 6:21 AM
Message: This site, it is a wonderful help. God Bless U1

Subject: Gives Error
Posted by: Sreekumar (not signed in)
Posted on: Tuesday, August 05, 2008 at 9:48 AM
Message: Page/Phil

Please let me know why the above mentioned error is happening, i am calling the procedure in a loop and when some error occurs next time when i re-run i get all kind of errors, i think the excel obects or work boob is properly not getting closed when error out

Subject: Error
Posted by: sonukapoor (view profile)
Posted on: Friday, September 26, 2008 at 9:00 AM
Message: I copied the sql from the download file and it throws the following error for the sp: spDMOExportToExcel

Msg 50000, Level 16, State 1, Procedure spDMOExportToExcel, Line 240
Error whilst Creating the Excel Application, on SQL4, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

Can you help out Robyn?

Subject: Questions on more formatting options
Posted by: DucatiFan (view profile)
Posted on: Wednesday, October 15, 2008 at 8:12 PM
Message: Hello. Again, thanks for posting this article. It was extremely useful!

I have a question about creating some additional formatting within the worksheet. I saw your code on bolding text, autofitting, etc. I was able to use that code as a model to set some columns to width 20 and to add some shading. To find the syntax to use I looked at VBA code.

However, there are two things I cannot figure out for the life of me. I cannot figure out how to set a column's horizontal alignment to center and to set a Freeze Pane. I tried using .HorizontalAlignment and .FreezePanes in a million different ways with no success. Any ideas?

Is there an online reference we can look at that shows all the T-SQL formatting syntax that is available?

Thanks again!

Subject: Very useful article on 32 bit servers
Posted by: timothyawiseman@gmail.com (view profile)
Posted on: Wednesday, November 05, 2008 at 10:43 AM
Message: This is very useful information on 32 bit servers, but as other commenters have already mentioned it tends not to work on a 64 bit server. Phil suggested using a 32-bit "grunt" machine, which is certainly a viable work around, but is there a genuine fix?

Is there any plan to either port Microsoft.Jet.OLEDB.4.0 to a 64-bit version or create a full replacement?

Subject: A bit of help please.
Posted by: Nicon (view profile)
Posted on: Saturday, December 27, 2008 at 1:54 PM
Message: Hi guys

Thanks for the information. I have used this script as part of an automated export routine and it has worked fantastically on my local instance.

I am however encountering problems when i try and run the same code on from my local machine on a remote instance. It seems that there may be a security issue when creating the worksheet on the remote machine.

The error that is being returned is:

Msg 50000, Level 16, State 1, Procedure usp_spDMOExportToExcel, Line 250
Error whilst Creating the Excel Application, on C6SERVER13, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval OUT [, additional IN, OUT, or BOTH params]]
Msg 22051, Level 16, State 1, Line 0

I have added the service user into a server role that has full control over the server in question.

There is some more information here http://www.sqlservercentral.com/Forums/Topic626016-145-1.aspx?Update=1

Any help would be great.

Regards

Steve

Subject: A bit of help please.
Posted by: Nicon (view profile)
Posted on: Saturday, December 27, 2008 at 4:19 PM
Message: Hi guys

Thanks for the information. I have used this script as part of an automated export routine and it has worked fantastically on my local instance.

I am however encountering problems when i try and run the same code on from my local machine on a remote instance. It seems that there may be a security issue when creating the worksheet on the remote machine.

The error that is being returned is:

Msg 50000, Level 16, State 1, Procedure usp_spDMOExportToExcel, Line 250
Error whilst Creating the Excel Application, on C6SERVER13, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval OUT [, additional IN, OUT, or BOTH params]]
Msg 22051, Level 16, State 1, Line 0

I have added the service user into a server role that has full control over the server in question.

There is some more information here http://www.sqlservercentral.com/Forums/Topic626016-145-1.aspx?Update=1

Any help would be great.

Regards

Steve

Subject: A bit of help please.
Posted by: Nicon (view profile)
Posted on: Saturday, December 27, 2008 at 5:03 PM
Message: Hi guys

Thanks for the information. I have used this script as part of an automated export routine and it has worked fantastically on my local instance.

I am however encountering problems when i try and run the same code on from my local machine on a remote instance. It seems that there may be a security issue when creating the worksheet on the remote machine.

The error that is being returned is:

Msg 50000, Level 16, State 1, Procedure usp_spDMOExportToExcel, Line 250
Error whilst Creating the Excel Application, on C6SERVER13, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval OUT [, additional IN, OUT, or BOTH params]]
Msg 22051, Level 16, State 1, Line 0

I have added the service user into a server role that has full control over the server in question.

There is some more information here http://www.sqlservercentral.com/Forums/Topic626016-145-1.aspx?Update=1

Any help would be great.

Regards

Steve

Subject: A bit of help please.
Posted by: Nicon (view profile)
Posted on: Saturday, December 27, 2008 at 6:12 PM
Message: Hi guys

Thanks for the information. I have used this script as part of an automated export routine and it has worked fantastically on my local instance.

I am however encountering problems when i try and run the same code on from my local machine on a remote instance. It seems that there may be a security issue when creating the worksheet on the remote machine.

The error that is being returned is:

Msg 50000, Level 16, State 1, Procedure usp_spDMOExportToExcel, Line 250
Error whilst Creating the Excel Application, on C6SERVER13, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval OUT [, additional IN, OUT, or BOTH params]]
Msg 22051, Level 16, State 1, Line 0

I have added the service user into a server role that has full control over the server in question.

There is some more information here http://www.sqlservercentral.com/Forums/Topic626016-145-1.aspx?Update=1

Any help would be great.

Regards

Steve

Subject: A bit of help please.
Posted by: Nicon (view profile)
Posted on: Sunday, December 28, 2008 at 5:33 AM
Message: Hi guys

Thanks for the information. I have used this script as part of an automated export routine and it has worked fantastically on my local instance.

I am however encountering problems when i try and run the same code on from my local machine on a remote instance. It seems that there may be a security issue when creating the worksheet on the remote machine.

The error that is being returned is:

Msg 50000, Level 16, State 1, Procedure usp_spDMOExportToExcel, Line 250
Error whilst Creating the Excel Application, on C6SERVER13, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval OUT [, additional IN, OUT, or BOTH params]]
Msg 22051, Level 16, State 1, Line 0

I have added the service user into a server role that has full control over the server in question.

There is some more information here http://www.sqlservercentral.com/Forums/Topic626016-145-1.aspx?Update=1

Any help would be great.

Regards

Steve

Subject: save error
Posted by: zjasz (view profile)
Posted on: Wednesday, July 01, 2009 at 2:03 AM
Message: Hi all!

First I'd like to say: It's a great article!

I tried to use the OLE automation script to create and manipulate an excel spreadsheet but, I got in an error. Everything worked fine until I tried to save the workbook, I got this error: "Unable to get the SaveAs property of the Workbook class". I'm using MS-SQL 2005 (SP1), Office 2003 on Vista (SP1).

I would greatly appreciate any help!

Regards,
Zoltan.

Subject: save error
Posted by: zjasz (view profile)
Posted on: Wednesday, July 01, 2009 at 2:50 AM
Message: Hi all!

First I'd like to say: It's a great article!

I tried to use the OLE automation script to create and manipulate an excel spreadsheet but, I got in an error. Everything worked fine until I tried to save the workbook, I got this error: "Unable to get the SaveAs property of the Workbook class". I'm using MS-SQL 2005 (SP1), Office 2003 on Vista (SP1).

I would greatly appreciate any help!

Regards,
Zoltan.

Subject: save error
Posted by: zjasz (view profile)
Posted on: Wednesday, July 01, 2009 at 3:17 AM
Message: Hi all!

First I'd like to say: It's a great article!

I tried to use the OLE automation script to create and manipulate an excel spreadsheet but, I got in an error. Everything worked fine until I tried to save the workbook, I got this error: "Unable to get the SaveAs property of the Workbook class". I'm using MS-SQL 2005 (SP1), Office 2003 on Vista (SP1).

I would greatly appreciate any help!

Regards,
Zoltan.

Subject: save error
Posted by: zjasz (view profile)
Posted on: Wednesday, July 01, 2009 at 3:18 AM
Message: Hi all!

First I'd like to say: It's a great article!

I tried to use the OLE automation script to create and manipulate an excel spreadsheet but, I got in an error. Everything worked fine until I tried to save the workbook, I got this error: "Unable to get the SaveAs property of the Workbook class". I'm using MS-SQL 2005 (SP1), Office 2003 on Vista (SP1).

I would greatly appreciate any help!

Regards,
Zoltan.

Subject: Errror
Posted by: Bhaskar Biswas (view profile)
Posted on: Monday, July 20, 2009 at 6:37 AM
Message: I am using the above script but when i change the Datasource to something like this:-\\192.168.1\Leads\CambridgePubs.xls.It thows error:- Error whilst Opening Connection to XLS, for file Create or Append, The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.

Any solution??

Subject: multiple result sets
Posted by: buijules (view profile)
Posted on: Thursday, January 21, 2010 at 10:56 AM
Message: My script returns multiple results but the excel only grabs the 1st one, does anyone know how to fix this?
Thanks

Subject: Turn on Autofilters?
Posted by: roadrunner71 (view profile)
Posted on: Tuesday, September 14, 2010 at 9:35 AM
Message: Great article.

I wanted a little more formatting done to the standard (ie. heading cell color) so I added a few more lines of code just after the font bold stuff.

--format the headings font color
--set the font.colorindex, then the number at the end of the execute command
--defines the color on the output. based on excel 56 colors - 2 white - 1 black
IF @hr=0
SELECT @strErrorMessage='formatting the column headings font color',
@objErrorObject=@objWorkSheet,
@command='Range("A1:'
+SUBSTRING(@alphabet,@currentColumn/26,1)
+SUBSTRING(@alphabet,@currentColumn % 26,1)
+'1'+'").font.colorindex'
IF @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 2
--format the headings cell color
IF @hr=0
SELECT @strErrorMessage='formatting the column headings cell color',
@objErrorObject=@objWorkSheet,
@command='Range("A1:'
+SUBSTRING(@alphabet,@currentColumn/26,1)
+SUBSTRING(@alphabet,@currentColumn % 26,1)
+'1'+'").interior.colorindex'
IF @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 16

Does anyone know how to turn on autofilters for the headings? Also, it would be nice to turn on grid lines.

Thanks

Subject: Excel 97-2003 output for machines with Excel 2007
Posted by: roadrunner71 (view profile)
Posted on: Tuesday, September 14, 2010 at 10:04 AM
Message: I also had an issue where I have a machine with Excel 2007 installed so the output from the above program was in Excel 2007 format. I have some users that are still using Excel 2003 so by adding the following code, I was able to save the file in 97-2003 format.

old code:
@command = 'SaveAs("' + @filename + '")'
new code:
@command = 'SaveAs("' + @filename + '",FileFormat:=56)'


Here are a list of formats:
51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)

52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)

50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)

56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)

One more thing, if you do not add the following quit command, after creating your file, Excel will be left open on the machine. You can see it in the task manager.

EXEC sp_OAMethod @objExcel, 'Quit'

Subject: Excel 97-2003 output for machines with Excel 2007
Posted by: roadrunner71 (view profile)
Posted on: Tuesday, September 14, 2010 at 1:42 PM
Message: I also had an issue where I have a machine with Excel 2007 installed so the output from the above program was in Excel 2007 format. I have some users that are still using Excel 2003 so by adding the following code, I was able to save the file in 97-2003 format.

old code:
@command = 'SaveAs("' + @filename + '")'
new code:
@command = 'SaveAs("' + @filename + '",FileFormat:=56)'


Here are a list of formats:
51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)

52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)

50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)

56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)

One more thing, if you do not add the following quit command, after creating your file, Excel will be left open on the machine. You can see it in the task manager.

EXEC sp_OAMethod @objExcel, 'Quit'

Subject: Changing Font Color using SQL Server
Posted by: markLondon (view profile)
Posted on: Thursday, October 21, 2010 at 3:39 PM
Message: Can anyone show me how to change the font color using SQL Server? The below is the programming style I'm using.


-- SET cell format to Currency two digits
EXECUTE @rs = master.dbo.sp_OASETProperty @xlCell, 'NumberFormat', '$#,##0.00'

Subject: Formatting
Posted by: Vishnuvardhan (view profile)
Posted on: Tuesday, April 26, 2011 at 2:19 PM
Message: This article helps me so much.
But i need one more help on this.
Suppose my Column A has integers that comes from Stored procedures.
I want to apply formatting (like 0000,0000.00,-0000.00,etc). How to achieve this? Can you help me on this?
Thanks in advance.

Subject: Keeping Numbers as Numbers
Posted by: twl55 (view profile)
Posted on: Thursday, September 01, 2011 at 6:48 PM
Message: I use OPENROWSET to write an SQL query output to Excel. The problem I'm having is that all my numeric fields end up in Excel as text strings. Does anyone know how to make them be numbers in Excel?

Thanks

Subject: ADO on server
Posted by: MartinMaule (view profile)
Posted on: Friday, October 07, 2011 at 3:08 AM
Message: What I need install on SQL 2008 64bit for create excel 2007 worksheets?Is it not possible to install full Office 2007 to server.
Our admins tried install this
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734
but no happened and when I try run
spExecute_ADODB_SQL @DDL='Create table CambridgePubs
(Pubname Text, Address Text, Postcode Text)',
@DataSource ='C:\CambridgePubs.xls'
I get this error
Msg 50000, Level 16, State 1, Procedure spExecute_ADODB_SQL, Line 105
Error whilst Opening Connection to XLS, for file Create or Append, Provider cannot be found. It may not be properly installed.

Subject: ADO on server part II
Posted by: MartinMaule (view profile)
Posted on: Friday, October 07, 2011 at 3:12 AM
Message: maybe is problem in spExecute_ADODB_SQL. Can I use this or have I edit with new?I think Excel 2007 is Excel 12.0?
@ConnectionString VARCHAR(255)

= 'Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=%DataSource;

Extended Properties=Excel 8.0'

Subject: ADO on server part II
Posted by: MartinMaule (view profile)
Posted on: Friday, October 07, 2011 at 4:31 AM
Message: maybe is problem in spExecute_ADODB_SQL. Can I use this or have I edit with new?I think Excel 2007 is Excel 12.0?
@ConnectionString VARCHAR(255)

= 'Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=%DataSource;

Extended Properties=Excel 8.0'

Subject: develop excel report from sqlserver
Posted by: bhatiaashu (view profile)
Posted on: Tuesday, October 25, 2011 at 4:27 AM
Message: Hello,

I am new to T-SQL.I have to develop excel report from sqlserver So I want to know how should I start and what needs to be downloaded for this purpose

Subject: Getting error
Posted by: swapnajeet (view profile)
Posted on: Friday, November 18, 2011 at 1:55 AM
Message: I wanted to create excel sheet from Stored procedure with some formatting.I have copied the code after downloading it but i am getting the following error when running it.
"sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BO"

I was using the following command to run it

spDMOExportToExcel @SourceServer='My DB Server Name',
@QueryText = 'use MyDatabase select Pubname, Address, Postcode from ##CambridgePubs',
@filename = 'C:\MyPubDatabase.xls',
@WorksheetName='MyFavouritePubs',
@RangeName ='ABCDE'

Am i missing something?What does the Parameter @Range Expect.

Subject: Getting error
Posted by: swapnajeet (view profile)
Posted on: Friday, November 18, 2011 at 2:05 AM
Message: I wanted to create excel sheet from Stored procedure with some formatting.I have copied the code after downloading it but i am getting the following error when running it.
"sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BO"

I was using the following command to run it

spDMOExportToExcel @SourceServer='My DB Server Name',
@QueryText = 'use MyDatabase select Pubname, Address, Postcode from ##CambridgePubs',
@filename = 'C:\MyPubDatabase.xls',
@WorksheetName='MyFavouritePubs',
@RangeName ='ABCDE'

Am i missing something?What does the Parameter @Range Expect.

Subject: Getting error
Posted by: swapnajeet (view profile)
Posted on: Friday, November 18, 2011 at 2:12 AM
Message: I wanted to create excel sheet from Stored procedure with some formatting.I have copied the code after downloading it but i am getting the following error when running it.
"sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BO"

I was using the following command to run it

spDMOExportToExcel @SourceServer='My DB Server Name',
@QueryText = 'use MyDatabase select Pubname, Address, Postcode from ##CambridgePubs',
@filename = 'C:\MyPubDatabase.xls',
@WorksheetName='MyFavouritePubs',
@RangeName ='ABCDE'

Am i missing something?What does the Parameter @Range Expect.

Subject: Getting error
Posted by: swapnajeet (view profile)
Posted on: Friday, November 18, 2011 at 3:11 AM
Message: I wanted to create excel sheet from Stored procedure with some formatting.I have copied the code after downloading it but i am getting the following error when running it.
"sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BO"

I was using the following command to run it

spDMOExportToExcel @SourceServer='My DB Server Name',
@QueryText = 'use MyDatabase select Pubname, Address, Postcode from ##CambridgePubs',
@filename = 'C:\MyPubDatabase.xls',
@WorksheetName='MyFavouritePubs',
@RangeName ='ABCDE'

Am i missing something?What does the Parameter @Range Expect.

Subject: Getting error
Posted by: swapnajeet (view profile)
Posted on: Friday, November 18, 2011 at 3:11 AM
Message: I wanted to create excel sheet from Stored procedure with some formatting.I have copied the code after downloading it but i am getting the following error when running it.
"sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BO"

I was using the following command to run it

spDMOExportToExcel @SourceServer='My DB Server Name',
@QueryText = 'use MyDatabase select Pubname, Address, Postcode from ##CambridgePubs',
@filename = 'C:\MyPubDatabase.xls',
@WorksheetName='MyFavouritePubs',
@RangeName ='ABCDE'

Am i missing something?What does the Parameter @Range Expect.

Subject: Missing Close
Posted by: CodeMonkey (view profile)
Posted on: Friday, January 17, 2014 at 7:28 AM
Message: You forgot to close the connection. It leaves a connection open every time you run a command.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.