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 at the bottom of the article, load it up and start experimenting!<\/p>\n
OPENDATASOURCE<\/code> and OPENROWSET<\/code> functions <\/a><\/li>\n- Creating Excel spreadsheets using
sp_MakeWebTask<\/code> <\/a><\/li>\n- OLE Automation <\/a><\/li>\n<\/ul>\n
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<\/code>, OPENDATASOURCE<\/code> and OPENROWSET<\/code>.<\/p>\nTo 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<\/code>) to create and populate an Excel spreadsheet from Transact SQL.<\/p>\nIf 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.<\/p>\n
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’.<\/p>\n
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<\/code>. Security precautions can get quite complex, but they are outside the scope of the article.<\/p>\nSome 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.<\/p>\n
First we need some simple test data:<\/p>\n
CREATE TABLE ##CambridgePubs \r\n (Pubname VARCHAR(40), \r\n Address VARCHAR(80), \r\n Postcode VARCHAR(8)) \r\n \r\n\r\n INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \r\n SELECT 'Bees In The Wall','36 North Road, \r\n Whittlesford, Cambridge','CB2 4NZ' \r\n INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \r\n SELECT 'Blackamoors Head','205 Victoria Road, \r\n Cambridge','CB4 3LF' \r\n INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \r\n SELECT 'Blue Lion','2 Horningsea Road, \r\n Fen Ditton, Cambridge','CB5 8SZ' \r\n INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \r\n SELECT 'Cambridge Blue','85-87 Gwydir Street, \r\n Cambridge','CB1 2LG' \r\n INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \r\n SELECT 'Champion Of The Thames','68 King Street, \r\n Cambridge','CB1 1LN' \r\n INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \r\n SELECT 'Cross Keys','77 Ermine Street, \r\n Caxton, Cambridge','CB3 8PQ' \r\n INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \r\n SELECT 'Crown Inn','11 High Street, \r\n Linton, Cambridge','CB1 6HS' \r\n INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \r\n SELECT 'Devonshire Arms','1 Devonshire Road, \r\n Cambridge','CB1 2BH' \r\n INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \r\n SELECT 'Duke Of Argyle','90 Argyle Street, \r\n Cambridge','CB1 3LS' \r\n INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \r\n SELECT 'Duke Of Wellington','49 Alms Hill, \r\n Bourn, Cambridge','CB3 7SH' \r\n INSERT INTO ##CambridgePubs (PubName, Address, Postcode) \r\n SELECT 'Eagle Public House','Benet Street, \r\n Cambridge','CB2 3QN' \r\n <\/pre>\nAnd so on. (The full import file is in the ZIP, as is the Excel file!).<\/p>\n
Create the table and then execute the contents of CambridgePubs.SQL<\/code>.<\/p>\nCreating Excel spreadsheets via ADODB <\/b><\/h2>\n
First, we need to create the spreadsheet with the correct headings (PubName, Address, PostCode<\/code>).<\/p>\nThere are two possible ways one might do this. The most obvious way is using the CREATE<\/code> 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.<\/p>\n CREATE PROCEDURE spExecute_ADODB_SQL \r\n @DDL VARCHAR(2000), \r\n @DataSource VARCHAR(100), \r\n @Worksheet VARCHAR(100)=NULL, \r\n @ConnectionString VARCHAR(255) \r\n = 'Provider=Microsoft.Jet.OLEDB.4.0; \r\n Data Source=%DataSource; \r\n Extended Properties=Excel 8.0' \r\n AS \r\n DECLARE \r\n @objExcel INT, \r\n @hr INT, \r\n @command VARCHAR(255), \r\n @strErrorMessage VARCHAR(255), \r\n @objErrorObject INT, \r\n @objConnection INT, \r\n @bucket INT \r\n \r\n\r\n SELECT @ConnectionString \r\n =REPLACE (@ConnectionString, '%DataSource', @DataSource) \r\n IF @Worksheet IS NOT NULL \r\n SELECT @DDL=REPLACE(@DDL,'%worksheet',@Worksheet) \r\n \r\n\r\n SELECT @strErrorMessage='Making ADODB connection ', \r\n @objErrorObject=NULL \r\n EXEC @hr=sp_OACreate 'ADODB.Connection', @objconnection OUT \r\n IF @hr=0 \r\n SELECT @strErrorMessage='Assigning ConnectionString property \"' \r\n + @ConnectionString + '\"', \r\n @objErrorObject=@objconnection \r\n IF @hr=0 EXEC @hr=sp_OASetProperty @objconnection, \r\n 'ConnectionString', @ConnectionString \r\n IF @hr=0 SELECT @strErrorMessage \r\n ='Opening Connection to XLS, for file Create or Append' \r\n IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Open' \r\n IF @hr=0 SELECT @strErrorMessage \r\n ='Executing DDL \"'+@DDL+'\"' \r\n IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Execute', \r\n @Bucket out , @DDL \r\n IF @hr<>0 \r\n BEGIN \r\n DECLARE \r\n @Source VARCHAR(255), \r\n @Description VARCHAR(255), \r\n @Helpfile VARCHAR(255), \r\n @HelpID INT \r\n \r\n EXECUTE sp_OAGetErrorInfo @objErrorObject, @source output, \r\n @Description output,@Helpfile output,@HelpID output \r\n SELECT @strErrorMessage='Error whilst ' \r\n +COALESCE(@strErrorMessage,'doing something')+', ' \r\n +COALESCE(@Description,'') \r\n RAISERROR (@strErrorMessage,16,1) \r\n END \r\n EXEC @hr=sp_OADestroy @objconnection \r\n GO \r\n <\/pre>\nNow we have it, it is easy.<\/p>\n
spExecute_ADODB_SQL @DDL='Create table CambridgePubs \r\n (Pubname Text, Address Text, Postcode Text)', \r\n @DataSource ='C:\\CambridgePubs.xls' \r\n <\/pre>\nThe Excel file will have been created on the Database server of the database you currently have a connection to.<\/p>\n
We could now insert data into the spreadsheet, if we wanted:<\/p>\n
spExecute_ADODB_SQL @DDL='insert into CambridgePubs \r\n (Pubname,Address,Postcode) \r\n values (''The Bird in Hand'', \r\n ''23, Marshall Road, Cambridge CB4 2DQ'', \r\n ''CB4 2DQ'')', \r\n @DataSource ='C:\\CambridgePubs.xls' \r\n \r\n\r\n --you could drop it again! \r\n spExecute_ADODB_SQL @DDL='drop table CambridgePubs', \r\n @DataSource ='c:\\CambridgePubs.xls' \r\n <\/pre>\nManipulating Excel data via a linked server <\/b><\/h2>\n
We can now link to the created Excel file as follows.<\/p>\n
EXEC sp_addlinkedserver 'CambridgePubDatabase', \r\n @srvproduct = '', \r\n @provider = 'Microsoft.Jet.OLEDB.4.0', \r\n @datasrc = 'C:\\CambridgePubs.xls', \r\n @provstr = 'Excel 8.0;' \r\n GO \r\n \r\n\r\n EXEC sp_addlinkedsrvlogin 'CambridgePubDatabase', 'false' \r\n GO \r\n <\/pre>\nTo drop the link, we do this!<\/p>\n
--EXEC sp_dropserver 'CambridgePubDatabase', 'droplogins' \r\n \r\n\r\n -- Get the spreadsheet data via OpenQuery \r\n SELECT * FROM OPENQUERY \r\n (CambridgePubDatabase, 'select * from [CambridgePubs]') \r\n GO \r\n --or more simply, do this \r\n SELECT * FROM CambridgePubDatabase...CambridgePubs \r\n <\/pre>\nSo now we can insert our data into the Excel spreadsheet:<\/p>\n
INSERT INTO CambridgePubDatabase…CambridgePubs<\/p>\n
(Pubname, Address, postcode)<\/p>\n
SELECT Pubname, Address, postcode FROM ##CambridgePubs<\/p>\n
<\/pre>\nSynchronizing the Spreadsheet with SQL Server tables <\/b><\/h2>\n
As we are directly manipulating the Excel data in the worksheet as if it was a table we can do JOINs<\/code>.<\/p>\nWhat about synchronising the table after editing the Excel spreadsheet?<\/p>\n
To try this out, you’ll need to DELETE<\/code>, ALTER<\/code> and INSERT<\/code> a few rows from the Excel spreadsheet, remembering to close it after you’ve done it.<\/p>\nFirstly, we’ll delete any rows from ##CambridgePubs <\/code>that do not exist in the Excel spreadsheet.<\/p>\n DELETE FROM ##CambridgePubs \r\n FROM ##CambridgePubs c \r\n LEFT OUTER JOIN CambridgePubDatabase...CambridgePubs ex \r\n ON c.address LIKE ex.address \r\n AND c.pubname LIKE ex.pubname \r\n AND c.postcode LIKE ex.postcode \r\n WHERE ex.pubname IS NULL \r\n <\/pre>\nThen we insert into ##CambridgePubs<\/code> any rows in the spreadsheet that don’t exist in ##CambridgePubs<\/code>.<\/p>\n INSERT INTO ##CambridgePubs (Pubname,Address,Postcode) \r\n SELECT ex.Pubname,ex.Address,ex.Postcode \r\n FROM CambridgePubDatabase...CambridgePubs ex \r\n LEFT OUTER JOIN ##CambridgePubs c \r\n ON c.address LIKE ex.address \r\n AND c.pubname LIKE ex.pubname \r\n AND c.postcode LIKE ex.postcode \r\n WHERE c.pubname IS NULL \r\n<\/pre>\nAll done (reverse syncronisation would be similar).<\/p>\n
Manipulating Excel data using OPENDATASOURCE and OPENROWSET <\/b><\/h2>\n
If you don’t want to do the linking, you can also read the data like this:<\/p>\n
SELECT * \r\n FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0', \r\n 'Data Source=\"C:\\CambridgePubs.xls\"; \r\n Extended properties=Excel 8.0')...CambridgePubs \r\n --and write to it \r\n \r\n\r\n UPDATE OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0', \r\n 'Data Source=\"C:\\CambridgePubs.xls\"; \r\n extended Properties=Excel 8.0')...CambridgePubs \r\n SET Address='St. Kilda Road, Cambridge' \r\n WHERE Pubname = 'Jenny Wren' \r\n \r\n\r\n INSERT INTO OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0', \r\n 'Data Source=\"C:\\CambridgePubs.xls\"; \r\n extended Properties=Excel 8.0')...CambridgePubs \r\n (Pubname,Address,Postcode ) \r\n SELECT 'The St George','65 Cavendish Road','CB2 4RT' \r\n <\/pre>\nYou can read and write to the Excel sheet using OpenRowSet, if the mood takes you.<\/p>\n
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', \r\n 'Excel 8.0;DATABASE=C:\\CambridgePubs.xls', 'Select * from CambridgePubs') \r\n \r\n\r\n UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0', \r\n 'Excel 8.0;DATABASE=c:\\CambridgePubs.xls', \r\n 'Select * from CambridgePubs') \r\n SET Address='34 Glemsford Road' WHERE Address = '65 Cavendish Road' \r\n \r\n\r\n INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', \r\n 'Excel 8.0;DATABASE=c:\\CambridgePubs.xls', \r\n 'Select * from CambridgePubs') \r\n (Pubname, Address, Postcode) \r\n SELECT 'The Bull', 'Antioch Road','CB2 5TY' \r\n <\/pre>\nCreating Excel Spreadsheets using sp_makewebtask <\/b><\/h2>\n
Instead of creating the Excel spreadsheet with OLEDB One can use the sp_makewebtask<\/code>.<\/p>\nUsers must have SELECT<\/code> permissions to run a specified query and CREATE PROCEDURE<\/code> 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.<\/p>\n sp_makewebtask @outputfile = 'c:\\CambridgePubsHTML2.xls', \r\n @query = 'Select * from ##CambridgePubs', \r\n @colheaders =1, \r\n @FixedFont=0,@lastupdated=0,@resultstitle='Cambridge Pubs', \r\n @dbname ='MyDatabaseName' \r\n <\/pre>\nThis is fine for distributing information from databases but no good if you subsequently want to open it via ODBC.<\/p>\n
OLE Automation <\/b><\/h2>\n
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.<\/p>\n
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.<\/p>\n
*\/ ALTER PROCEDURE [dbo].[spDMOExportToExcel] ( \r\n @SourceServer VARCHAR(30), \r\n @SourceUID VARCHAR(30)=NULL, \r\n @SourcePWD VARCHAR(30)=NULL, \r\n @QueryText VARCHAR(200), \r\n @filename VARCHAR(100), \r\n @WorksheetName VARCHAR(100)='Worksheet', \r\n @RangeName VARCHAR(80)='MyRangeName' \r\n ) \r\n AS \r\n DECLARE @objServer INT, \r\n @objQueryResults INT, \r\n @objCurrentResultSet INT, \r\n @objExcel INT, \r\n @objWorkBooks INT, \r\n @objWorkBook INT, \r\n @objWorkSheet INT, \r\n @objRange INT, \r\n @hr INT, \r\n @Columns INT, \r\n @Rows INT, \r\n @Output INT, \r\n @currentColumn INT, \r\n @currentRow INT, \r\n @ResultSetRow INT, \r\n @off_Column INT, \r\n @off_Row INT, \r\n @command VARCHAR(255), \r\n @ColumnName VARCHAR(255), \r\n @value VARCHAR(255), \r\n @strErrorMessage VARCHAR(255), \r\n @objErrorObject INT, \r\n @Alphabet VARCHAR(27) \r\n \r\n\r\n SELECT @Alphabet='ABCDEFGHIJKLMNOPQRSTUVWXYZ' \r\n \r\n\r\n IF @QueryText IS NULL \r\n BEGIN \r\n RAISERROR ('A query string is required for spDMOExportToExcel',16,1) \r\n RETURN 1 \r\n END \r\n \r\n\r\n -- Sets the server to the local server \r\n IF @SourceServer IS NULL SELECT @SourceServer = @@servername \r\n \r\n\r\n SET NOCOUNT ON \r\n \r\n\r\n SELECT @strErrorMessage = 'instantiating the DMO', \r\n @objErrorObject=@objServer \r\n EXEC @hr= sp_OACreate 'SQLDMO.SQLServer', @objServer OUT \r\n \r\n\r\n IF @SourcePWD IS NULL OR @SourceUID IS NULL \r\n BEGIN \r\n --use a trusted connection \r\n IF @hr=0 SELECT @strErrorMessage= \r\n 'Setting login to windows authentication on ' \r\n +@SourceServer, @objErrorObject=@objServer \r\n IF @hr=0 EXEC @hr=sp_OASetProperty @objServer, 'LoginSecure', 1 \r\n IF @hr=0 SELECT @strErrorMessage= \r\n 'logging in to the requested server using windows authentication on ' \r\n +@SourceServer \r\n IF @SourceUID IS NULL AND @hr=0 EXEC @hr=sp_OAMethod @objServer, \r\n 'Connect', NULL, @SourceServer \r\n IF @SourceUID IS NOT NULL AND @hr=0 \r\n EXEC @hr=sp_OAMethod \r\n @objServer, 'Connect', NULL, @SourceServer ,@SourceUID \r\n END \r\n ELSE \r\n BEGIN \r\n IF @hr=0 \r\n SELECT @strErrorMessage = 'Connecting to '''+@SourceServer+ \r\n ''' with user ID '''+@SourceUID+'''', \r\n @objErrorObject=@objServer \r\n IF @hr=0 \r\n EXEC @hr=sp_OAMethod @objServer, 'Connect', NULL, \r\n @SourceServer, @SourceUID, @SourcePWD \r\n END \r\n \r\n\r\n --now we execute the query \r\n IF @hr=0 SELECT @strErrorMessage='executing the query \"' \r\n +@querytext+'\", on '+@SourceServer, \r\n @objErrorObject=@objServer, \r\n @command = 'ExecuteWithResults(\"' + @QueryText + '\")' \r\n IF @hr=0 \r\n EXEC @hr=sp_OAMethod @objServer, @command, @objQueryResults OUT \r\n \r\n\r\n IF @hr=0 \r\n SELECT @strErrorMessage='getting the first result set for \"' \r\n +@querytext+'\", on '+@SourceServer, \r\n @objErrorObject=@objQueryResults \r\n IF @hr=0 EXEC @hr=sp_OAMethod \r\n @objQueryResults, 'CurrentResultSet', @objCurrentResultSet OUT \r\n IF @hr=0 \r\n SELECT @strErrorMessage='getting the rows and columns \"' \r\n +@querytext+'\", on '+@SourceServer \r\n IF @hr=0 \r\n EXEC @hr=sp_OAMethod @objQueryResults, 'Columns', @Columns OUT \r\n IF @hr=0 \r\n EXEC @hr=sp_OAMethod @objQueryResults, 'Rows', @Rows OUT \r\n \r\n\r\n --so now we have the queryresults. We start up Excel \r\n IF @hr=0 \r\n SELECT @strErrorMessage='Creating the Excel Application, on ' \r\n +@SourceServer, @objErrorObject=@objExcel \r\n IF @hr=0 \r\n EXEC @hr=sp_OACreate 'Excel.Application', @objExcel OUT \r\n IF @hr=0 SELECT @strErrorMessage='Getting the WorkBooks object ' \r\n IF @hr=0 \r\n EXEC @hr=sp_OAGetProperty @objExcel, 'WorkBooks', \r\n @objWorkBooks OUT \r\n --create a workbook \r\n IF @hr=0 \r\n SELECT @strErrorMessage='Adding a workbook ', \r\n @objErrorObject=@objWorkBooks \r\n IF @hr=0 \r\n EXEC @hr=sp_OAGetProperty @objWorkBooks, 'Add', @objWorkBook OUT \r\n \r\n\r\n --and a worksheet \r\n IF @hr=0 \r\n SELECT @strErrorMessage='Adding a worksheet ', \r\n @objErrorObject=@objWorkBook \r\n IF @hr=0 \r\n EXEC @hr=sp_OAGetProperty @objWorkBook, 'worksheets.Add', \r\n @objWorkSheet OUT \r\n \r\n\r\n IF @hr=0 \r\n SELECT @strErrorMessage='Naming a worksheet as \"' \r\n +@WorksheetName+'\"', @objErrorObject=@objWorkBook \r\n IF @hr=0 \r\n EXEC @hr=sp_OASetProperty @objWorkSheet, 'name', @WorksheetName \r\n \r\n\r\n SELECT @currentRow = 1 \r\n \r\n\r\n --so let's write out the column headings \r\n SELECT @currentColumn = 1 \r\n WHILE (@currentColumn <= @Columns AND @hr=0) \r\n BEGIN \r\n IF @hr=0 \r\n SELECT @strErrorMessage='getting column heading ' \r\n +LTRIM(STR(@currentcolumn)) , \r\n @objErrorObject=@objQueryResults, \r\n @Command='ColumnName(' \r\n +CONVERT(VARCHAR(3),@currentColumn)+')' \r\n IF @hr=0 EXEC @hr=sp_OAGetProperty @objQueryResults, \r\n @command, @ColumnName OUT \r\n IF @hr=0 \r\n SELECT @strErrorMessage='assigning the column heading '+ \r\n + LTRIM(STR(@currentColumn)) \r\n + ' from the query string', \r\n @objErrorObject=@objExcel, \r\n @command='Cells('+LTRIM(STR(@currentRow)) +', ' \r\n + LTRIM(STR(@CurrentColumn))+').value' \r\n IF @hr=0 \r\n EXEC @hr=sp_OASetProperty @objExcel, @command, @ColumnName \r\n SELECT @currentColumn = @currentColumn + 1 \r\n END \r\n \r\n\r\n --format the headings in Bold nicely \r\n IF @hr=0 \r\n SELECT @strErrorMessage='formatting the column headings in bold ', \r\n @objErrorObject=@objWorkSheet, \r\n @command='Range(\"A1:'\r\n +SUBSTRING(@alphabet,@currentColumn\/26,1) \r\n +SUBSTRING(@alphabet,@currentColumn % 26,1)\r\n +'1'+'\").font.bold' \r\n IF @hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 1 \r\n --now we write out the data \r\n \r\n\r\n SELECT @currentRow = 2 \r\n WHILE (@currentRow <= @Rows+1 AND @hr=0) \r\n BEGIN \r\n SELECT @currentColumn = 1 \r\n WHILE (@currentColumn <= @Columns AND @hr=0) \r\n BEGIN \r\n IF @hr=0 \r\n SELECT \r\n @strErrorMessage= \r\n 'getting the value from the query string' \r\n + LTRIM(STR(@currentRow)) +',' \r\n + LTRIM(STR(@currentRow))+')', \r\n @objErrorObject=@objQueryResults, \r\n @ResultSetRow=@CurrentRow-1 \r\n IF @hr=0 \r\n EXEC @hr=sp_OAMethod @objQueryResults, 'GetColumnString', \r\n @value OUT, @ResultSetRow, @currentColumn \r\n IF @hr=0 \r\n SELECT @strErrorMessage= \r\n 'assigning the value from the query string' \r\n + LTRIM(STR(@CurrentRow-1)) +', ' \r\n + LTRIM(STR(@currentcolumn))+')' , \r\n @objErrorObject=@objExcel, \r\n @command='Cells('+STR(@currentRow) +', ' \r\n + STR(@CurrentColumn)+').value' \r\n IF @hr=0 \r\n EXEC @hr=sp_OASetProperty @objExcel, @command, @value \r\n SELECT @currentColumn = @currentColumn + 1 \r\n END \r\n SELECT @currentRow = @currentRow + 1 \r\n END \r\n --define the name range \r\n --Cells(1, 1).Resize(10, 5).Name = \"TheData\" \r\n IF @hr=0 SELECT @strErrorMessage='assigning a name to a range ' \r\n + LTRIM(STR(@CurrentRow-1)) +', ' \r\n + LTRIM(STR(@currentcolumn-1))+')' , \r\n @objErrorObject=@objExcel, \r\n @command='Cells(1, 1).Resize('+STR(@currentRow-1) +', ' \r\n + STR(@CurrentColumn-1)+').Name' \r\n IF @hr=0 EXEC @hr=sp_OASetProperty @objExcel, @command, @RangeName \r\n \r\n\r\n --Now autofilt the columns we've written to \r\n IF @hr=0 SELECT @strErrorMessage='Auto-fit the columns ', \r\n @objErrorObject=@objWorkSheet, \r\n @command='Columns(\"A:'\r\n +SUBSTRING(@alphabet,(@Columns \/ 26),1) \r\n +SUBSTRING(@alphabet,(@Columns % 26),1)+ \r\n '\").autofit' \r\n \r\n\r\n IF @hr=0 --insert into @bucket(bucket) \r\n EXEC @hr=sp_OAMethod @objWorkSheet, @command, @output out \r\n \r\n\r\n \r\n\r\n IF @hr=0 SELECT @command ='del \"' + @filename + '\"' \r\n IF @hr=0 EXECUTE master..xp_cmdshell @Command, no_output \r\n IF @hr=0 \r\n SELECT @strErrorMessage='Saving the workbook as \"'+@filename+'\"', \r\n @objErrorObject=@objRange, \r\n @command = 'SaveAs(\"' + @filename + '\")' \r\n IF @hr=0 EXEC @hr=sp_OAMethod @objWorkBook, @command \r\n IF @hr=0 SELECT @strErrorMessage='closing Excel ', \r\n @objErrorObject=@objExcel \r\n EXEC @hr=sp_OAMethod @objWorkBook, 'Close' \r\n EXEC sp_OAMethod @objExcel, 'Close' \r\n \r\n\r\n IF @hr<>0 \r\n BEGIN \r\n DECLARE \r\n @Source VARCHAR(255), \r\n @Description VARCHAR(255), \r\n @Helpfile VARCHAR(255), \r\n @HelpID INT \r\n \r\n EXECUTE sp_OAGetErrorInfo @objErrorObject, \r\n @source output,@Description output,\r\n @Helpfile output,@HelpID output \r\n SELECT @hr, @source, @Description,@Helpfile,@HelpID output \r\n SELECT @strErrorMessage='Error whilst ' \r\n +COALESCE(@strErrorMessage,'doing something') \r\n +', '+COALESCE(@Description,'') \r\n RAISERROR (@strErrorMessage,16,1) \r\n END \r\n EXEC sp_OADestroy @objServer \r\n EXEC sp_OADestroy @objQueryResults \r\n EXEC sp_OADestroy @objCurrentResultSet \r\n EXEC sp_OADestroy @objExcel \r\n EXEC sp_OADestroy @objWorkBookks \r\n EXEC sp_OADestroy @objWorkBook \r\n EXEC sp_OADestroy @objRange \r\n RETURN @hr<\/pre>\nGO<\/p>\n
<\/pre>\nNow we can create our pubs spreadsheet, and can do it from any of our servers.<\/p>\n
spDMOExportToExcel @SourceServer='MyServer', \r\n @SourceUID= 'MyUserID', \r\n @SourcePWD = 'MyPassword', \r\n @QueryText = 'use MyDatabase \r\n select Pubname, Address, Postcode from ##CambridgePubs', \r\n @filename = 'C:\\MyPubDatabase.xls', \r\n @WorksheetName='MyFavouritePubs', \r\n @RangeName ='MyRangeName' \r\n <\/pre>\nOr if you are using integrated security!<\/p>\n
spDMOExportToExcel @SourceServer='MyServer', \r\n @QueryText = 'use MyDatabase \r\n select Pubname, Address, Postcode from ##CambridgePubs', \r\n @filename = 'C:\\MyPubDatabase.xls', \r\n @WorksheetName='MyFavouritePubs', \r\n @RangeName ='MyRangeName' \r\n <\/pre>\nAlthough this is a very handy stored procedure, you’ll probably need to modify and add to it for particular purposes.<\/p>\n
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.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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\u00e8ce de r\u00e9sistance 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.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4644,4178,4643,4647,4646,4645,4179,4150,4151,4183,4252,4460],"coauthors":[6813,6814],"class_list":["post-221","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-adodb","tag-bi","tag-excel","tag-ole-automation","tag-opendatasource","tag-openrowset","tag-source-control","tag-sql","tag-sql-server","tag-t-sql","tag-t-sql-programming","tag-workbench"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=221"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/221\/revisions"}],"predecessor-version":[{"id":77226,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/221\/revisions\/77226"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=221"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}