Ssms: Automatically Save Multiple Result Sets From Same Sql Script Into Separate Tabs In Excel?
I have a SQL query I'm running in SSMS that generates about 8 different resulting datasets (all with different columns). Is there a way to have each of the result sets get out outp
Solution 1:
If you are able to allow Ole Automation Procedures you can use this solution as below. You could also create a stored procedure that does not use the @OutputFileName parameter and use bcp to save the binary content to a file (using a format file that exports binary content without adding data to the beginning of the output).
I wrote this out of frustration for not being able to easily generate Excel Spreadsheets with text or number formatting that I required. Procedure AddFileToArchive generates a zip archive containing the contents of an input file and is used internally by GetExcelSpreadsheetData.
GetExcelSpreadsheetData generates a worksheet for one or more temporary tables and outputs a compliant open xml workbook. Worksheets have column headings in bold font. If you output the file with a .zip extension you will see the internal file contents similar to any other .xlsx file, however the data is internally stored inline instead of in shared values. Worksheets can be autofiltered by adding the "autofilter" option.
Date formatting is currently hard coded to dd/mm/yyyy however you can change that by altering these lines:
<numFmtformatCode="dd/mm/yyyy\ hh:mm"numFmtId="165"/><numFmtformatCode="hh:mm"numFmtId="166"/><numFmtformatCode="dd/mm/yyyy"numFmtId="167"/>Sorry about the lack of comments.
Credit to Does SQL Server CheckSum calculate a CRC? If not how can I get MS SQL to calculate a CRC on an arbitrary varchar column? for the GetCRC32 code used if executed on SQL Server 2012 or 2014.
Example usage at the bottom.
Hopefully you will find this useful. This procedure does not work on SQL Server 2008R2 or earlier.
/****** Object: UserDefinedFunction [dbo].[GetCRC32] Script Date: 23/10/2020 4:52:31 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
createfunction [dbo].[GetCRC32](@Textvarchar(max)) returnsbinary(4) asbegindeclare@crcbigint=0xFFFFFFFF
,@Lookupvarbinary(2048) =0x0000000077073096EE0E612C990951BA076DC419706AF48FE963A5359E6495A30EDB883279DCB8A4E0D5E91E97D2D98809B64C2B7EB17CBDE7B82D0790BF1D911DB710646AB020F2F3B9714884BE41DE1ADAD47D6DDDE4EBF4D4B55183D385C7136C9856646BA8C0FD62F97A8A65C9EC14015C4F63066CD9FA0F3D638D080DF53B6E20C84C69105ED56041E4A26771723C03E4D14B04D447D20D85FDA50AB56B35B5A8FA42B2986CDBBBC9D6ACBCF94032D86CE345DF5C75DCD60DCFABD13D5926D930AC51DE003AC8D75180BFD0611621B4F4B556B3C423CFBA9599B8BDA50F2802B89E5F058808C60CD9B2B10BE9242F6F7C8758684C11C1611DABB6662D3D76DC419001DB710698D220BCEFD5102A71B1858906B6B51F9FBFE4A5E8B8D4337807C9A20F00F9349609A88EE10E98187F6A0DBB086D3D2D91646C97E6635C016B6B51F41C6C6162856530D8F262004E6C0695ED1B01A57B8208F4C1F50FC45765B0D9C612B7E9508BBEB8EAFCB9887C62DD1DDF15DA2D498CD37CF3FBD44C654DB261583AB551CEA3BC0074D4BB30E24ADFA5413DD895D7A4D1C46DD3D6F4FB4369E96A346ED9FCAD678846DA60B8D044042D7333031DE5AA0A4C5FDD0D7CC95005713C270241AABE0B1010C90C20865768B525206F85B3B966D409CE61E49F5EDEF90E29D9C998B0D09822C7D7A8B459B33D172EB40D81B7BD5C3BC0BA6CADEDB883209ABFB3B603B6E20C74B1D29AEAD547399DD277AF04DB261573DC1683E3630B1294643B840D6D6A3E7A6A5AA8E40ECF0B9309FF9D0A00AE277D079EB1F00F93448708A3D21E01F2686906C2FEF762575D806567CB196C36716E6B06E7FED41B7689D32BE010DA7A5A67DD4ACCF9B9DF6F8EBEEFF917B7BE4360B08ED5D6D6A3E8A1D1937E38D8C2C44FDFF252D1BB67F1A6BC57673FB506DD48B2364BD80D2BDAAF0A1B4C36034AF641047A60DF60EFC3A867DF55316E8EEF4669BE79CB61B38CBC66831A256FD2A05268E236CC0C7795BB0B4703220216B95505262FC5BA3BBEB2BD0B282BB45A925CB36A04C2D7FFA7B5D0CF312CD99E8B5BDEAE1D9B64C2B0EC63F226756AA39C026D930A9C0906A9EB0E363F720767850500571395BF4A82E2B87A147BB12BAE0CB61B3892D28E9BE5D5BE0D7CDCEFB70BDBDF2186D3D2D4F1D4E24268DDB3F81FDA836E81BE16CDF6B9265B6FB077E118B7477788085AE6FF0F6A7066063BCA11010B5C8F659EFFF862AE69616BFFD3166CCF45A00AE278D70DD2EE4E0483543903B3C2A7672661D06016F74969474D3E6E77DBAED16A4AD9D65ADC40DF0B6637D83BF0A9BCAE53DEBB9EC547B2CF7F30B5FFE9BDBDF21CCABAC28A53B3933024B4A3A6BAD03605CDD7069354DE572923D967BFB3667A2EC4614AB85D681B022A6F2B94B40BBE37C30C8EA15A05DF1B2D02EF8D
,@LenTextint= len(@Text);
with x as (
select''as Id
unionallselect''as Id from x
)
,y as (select top (cast(ceiling(sqrt(sqrt(@LenText))) asint)) ''as id from x)
,v as (
select top (@LenText) row_number() over (orderby (selectnull)) as ID
from y crossjoin y as y2 crossjoin y as y3 crossjoin y as y4
)
SELECT@crc= (@crc/256) ^Substring(@Lookup, ((@crc&0xFF) ^ Ascii(Substring(@Text, V.ID, 1))) *4+1, 4)
FROM V
SET@crc=~@crc;
returncast(reverse(cast(@crcasvarbinary(4))) asbinary(4));
end
GO
/****** Object: UserDefinedFunction [dbo].[StringSplit2] Script Date: 23/10/2020 4:52:32 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
createfunction [dbo].[StringSplit2] (@StringToSplitvarchar(max), @Seperatorchar(1)) returnstableasreturn (
with Split as (
select1as ValueOrder
,cast(1asint) as ValueStartPos
,cast(charindex(@Seperator, @StringToSplit+@Seperator) asint) as ValueEndPos
unionallselect
ValueOrder +1as ValueOrder
,ValueEndPos +1as ValueStartPos
,cast(charindex(@Seperator, @StringToSplit+@Seperator, ValueEndPos +1) asint) as ValueEndPos
from Split
where ValueEndPos <>0
)
select
ValueOrder
,substring(@StringToSplit, ValueStartPos, casewhen ValueEndPos =0then0else ValueEndPos-ValueStartPos end) asvaluefrom Split
where ValueEndPos <>0
)
GO
/****** Object: StoredProcedure [dbo].[AddFileToArchive] Script Date: 23/10/2020 4:52:32 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
createprocedure [dbo].[AddFileToArchive](@FileNamevarchar(max), @FileContentsvarbinary(max), @ArchiveDatavarbinary(max) output, @ArchiveInfobinary(10) output)
asbeginset nocount on;
declare@LocalFileHeaderSignaturebinary(4) =0x504b0304
,@CentralDirectoryFileHeaderSignaturebinary(4) =0x504b0102
,@EndOfCentralDirectorySignaturebinary(4) =0x504b0506
,@VersionNeededToExtractbinary(2) =0x1400
,@VersionMadeBybinary(2) =0x1400
,@GeneralPurposeBitFlagbinary(2) =0x0000
,@CompressionMethodbinary(2) =0x0000--0x0800
,@CompressionMethodDeflatebinary(2) =0x0800
,@FileLastModificationTimebinary(2) =0xA351-- dummy time
,@FileLastModificationDatebinary(2) =0x6250-- dummy date
,@currentDateTime datetime = getdate()
,@CRC32binary(4)
,@CompressedSizeint
,@UncompressedSizeint
,@FileNameLengthsmallint
,@ExtraFieldLengthsmallint=0
,@FileCommentLengthsmallint=0
,@CommentLengthsmallint=0
,@NumberOfThisDisksmallint=0
,@DiskNumberWhereFileStartssmallint=0
,@InternalFileAttributesbinary(2) =0x0100
,@ExternalFileAttributesbinary(4) =0x20000000
,@LocalFileRecordLengthint
,@LocalFileRecordvarbinary(max)
,@CompressedFileContentsvarbinary(max)
,@LenCompressedFileContentsint
,@FileCRC32binary(4)
,@OffsetOfStartOfCentralDirectoryint
,@CentralDirectoryRecordvarbinary(max)
,@EndOfCentralDirectoryRecordvarbinary(max)
,@sql nvarchar(max)
,@OtherFileCountsmallint
,@FileOffsetint
,@SizeOfCentralDirectoryint;
set@OtherFileCount= IsNull(cast(SubString(@ArchiveInfo, 1, 2) assmallint), 0);
set@FileOffset= IsNull(cast(SubString(@ArchiveInfo, 3, 4) asint), 0);
set@SizeOfCentralDirectory= IsNull(cast(SubString(@ArchiveInfo, 7, 4) asint), 0);
set@FileLastModificationTime=cast(reverse(cast((datepart(ss, @currentDateTime) /2) | (32* datepart(mi, @currentDateTime)) | (2048* datepart(hh, @currentDateTime)) asbinary(2))) asbinary (2));
set@FileLastModificationDate=cast(reverse(cast((datepart(dd, @currentDateTime)) | (32* datepart(mm, @currentDateTime)) | (512* (datepart(yy, @currentDateTime) -1980)) asbinary(2))) asbinary (2));
-- for SQL Server 2016 or higher compress the file contents
if cast(serverproperty('ProductMajorVersion') asint) >12-- SQL Server 2016+beginexec sp_executesql
@stmt= N'set @CompressedFileContents = compress(@FileContents)'
,@params= N'@FileContents varbinary(max), @CompressedFileContents varbinary(max) output'
,@CompressedFileContents=@CompressedFileContents output
,@FileContents=@FileContents;
set@FileCRC32=substring(@CompressedFileContents, len(@CompressedFileContents) -7, 4);
set@CompressionMethod=@CompressionMethodDeflate;
endelsebeginset@CompressedFileContents=0x00000000000000000000+@FileContents+0x0000000000000000;
exec sp_executesql
@stmt= N'set @FileCRC32 = dbo.GetCRC32(@FileContents)'
,@params= N'@FileContents varbinary(max), @FileCRC32 binary(4) output'
,@FileContents=@FileContents
,@FileCRC32=@FileCRC32 output;
end;
set@LenCompressedFileContents= len(@CompressedFileContents);
set@CompressedSize=@LenCompressedFileContents-18;
set@UncompressedSize= len(@FileContents);
set@FileNameLength= len(@FileName);
set@LocalFileRecord=@LocalFileHeaderSignature+@VersionNeededToExtract+@GeneralPurposeBitFlag+@CompressionMethod+@FileLastModificationTime+@FileLastModificationDate+@FileCRC32+cast(reverse(cast(@CompressedSizeasbinary(4))) asbinary(4))
+cast(reverse(cast(@UncompressedSizeasbinary(4))) asbinary(4))
+cast(reverse(cast(@FileNameLengthasbinary(2))) asbinary(2))
+cast(reverse(cast(@ExtraFieldLengthasbinary(2))) asbinary(2))
+cast(@FileNameasvarbinary(max))
+substring(@CompressedFileContents, 11, @LenCompressedFileContents-18);
set@CentralDirectoryRecord=@CentralDirectoryFileHeaderSignature+@VersionMadeBy+@VersionNeededToExtract+@GeneralPurposeBitFlag+@CompressionMethod+@FileLastModificationTime+@FileLastModificationDate+@FileCRC32+cast(reverse(cast(@CompressedSizeasbinary(4))) asbinary(4))
+cast(reverse(cast(@UncompressedSizeasbinary(4))) asbinary(4))
+cast(reverse(cast(@FileNameLengthasbinary(2))) asbinary(2))
+cast(reverse(cast(@ExtraFieldLengthasbinary(2))) asbinary(2))
+cast(reverse(cast(@FileCommentLengthasbinary(2))) asbinary(2))
+cast(reverse(cast(@DiskNumberWhereFileStartsasbinary(2))) asbinary(2))
+@InternalFileAttributes+@ExternalFileAttributes+cast(reverse(cast(@FileOffsetasbinary(4))) asbinary(4))
+cast(@FileNameasvarbinary(max));
set@SizeOfCentralDirectory=@SizeOfCentralDirectory+ len(@CentralDirectoryRecord);
set@LocalFileRecordLength= len(@LocalFileRecord)
set@OffsetOfStartOfCentralDirectory=@FileOffset+@LocalFileRecordLength;
set@EndOfCentralDirectoryRecord=@EndOfCentralDirectorySignature+cast(reverse(cast(@NumberOfThisDiskasbinary(2))) asbinary(2))
+cast(reverse(cast(@NumberOfThisDiskasbinary(2))) asbinary(2)) -- Disk where central directory starts+cast(reverse(cast(@OtherFileCount+cast(1assmallint) asbinary(2))) asbinary(2)) -- Number of central directory records on this disk+cast(reverse(cast(@OtherFileCount+cast(1assmallint) asbinary(2))) asbinary(2)) -- Total number of central directory records+cast(reverse(cast(@SizeOfCentralDirectoryasbinary(4))) asbinary(4))
+cast(reverse(cast(@OffsetOfStartOfCentralDirectoryasbinary(4))) asbinary(4))
+cast(reverse(cast(@CommentLengthasbinary(2))) asbinary(2))
if @ArchiveInfoisnullset@ArchiveData=@LocalFileRecord+@CentralDirectoryRecord+@EndOfCentralDirectoryRecordelseset@ArchiveData=SubString(@ArchiveData, 1, @FileOffset) +@LocalFileRecord+SubString(@ArchiveData, @FileOffset+1, @SizeOfCentralDirectory- len(@CentralDirectoryRecord)) +@CentralDirectoryRecord+@EndOfCentralDirectoryRecord;
set@ArchiveInfo=cast(@OtherFileCount+1asbinary(2))
+cast(@FileOffset+@LocalFileRecordLengthasbinary(4))
+cast(@SizeOfCentralDirectoryasbinary(4));
-- select @ArchiveInfo as ArchiveInfo, @LocalFileRecord as LocalFileRecord,@CentralDirectoryRecord as CentralDirectoryRecord, @EndOfCentralDirectoryRecord as EndOfCentralDirectoryRecordreturn;
end
GO
/****** Object: StoredProcedure [dbo].[GetExcelSpreadsheetData] Script Date: 23/10/2020 4:52:32 PM ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATEprocedure [dbo].[GetExcelSpreadsheetData](@SheetNamevarchar(31) =null, @Worksheetsvarchar(max) =null, @WorkbookDatavarbinary(max) =null output, @OutputFileName nvarchar(500) =null)
asset nocount on;
declare@LocalFileRecordsvarbinary(max)
,@CentralDirectoryRecordsvarbinary(max)
,@EndOfCentralDirectoryRecordvarbinary(max)
,@OtherFileCountsmallint
,@FileOffsetint
,@SizeOfCentralDirectoryint
,@NumberOfWorksheetssmallint
,@CurrentWorksheetsmallint=1
,@sql nvarchar(max) = N''
,@rowcountsql nvarchar(max)
,@rowcountint
,@colcountint
,@datatablevarchar(100)
,@RowDataXML nvarchar(max)
,@SelectOutput bit =0
,@ArchiveInfobinary(10)
,@WorksheetFileNamevarchar(50)
,@ObjectTokenint
,@AutoFilterXML nvarchar(max) = N''
,@_Content_Types__xml__sheets nvarchar(max)
,@_Content_Types__xml varbinary(max)
,@Workbook_xml_temp nvarchar(max)
,@Workbook_xml varbinary(max)
,@_Rels_workbook_xml_rels varbinary(max)
,@styles_xml varbinary(max) =cast(N'<?xml version="1.0" encoding="utf-16"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<numFmts count="3">
<numFmt formatCode="dd/mm/yyyy\ hh:mm" numFmtId="165"/>
<numFmt formatCode="hh:mm" numFmtId="166"/>
<numFmt formatCode="dd/mm/yyyy" numFmtId="167"/>
</numFmts>
<fonts count="7">
<font> <sz val="11"/> <name val="Calibri"/><family val="2" /><scheme val="minor" /></font>
<font> <sz val="11"/><color rgb="FFFF0000" /><name val="Calibri"/><family val="2" /><scheme val="minor" /></font>
<font><b/><sz val="11"/> <name val="Calibri"/><family val="2" /><scheme val="minor" /></font>
<font><i/><sz val="11"/> <name val="Calibri"/><family val="2" /><scheme val="minor" /></font>
<font> <sz val="11"/><color rgb="FF0070C0" /><name val="Calibri"/><family val="2" /><scheme val="minor" /></font>
<font> <sz val="20"/> <name val="Calibri"/><family val="2" /><scheme val="minor" /></font>
<font> <sz val="11"/> <name val="Courier"/><family val="3" /> </font>
</fonts>
<fills count="2">
<fill>
<patternFill patternType="none" />
</fill>
<fill>
<patternFill patternType="gray125" />
</fill>
</fills>
<borders count="1">
<border>
<left />
<right />
<top />
<bottom />
<diagonal />
</border>
</borders>
<cellStyleXfs count="1">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" />
</cellStyleXfs>
<cellXfs count="10">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
<xf numFmtId="0" fontId="2" fillId="0" borderId="0" xfId="0" applyFont="1" />
<xf numFmtId="0" fontId="3" fillId="0" borderId="0" xfId="0" applyFont="1" />
<xf numFmtId="0" fontId="4" fillId="0" borderId="0" xfId="0" applyFont="1" />
<xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1" />
<xf numFmtId="0" fontId="5" fillId="0" borderId="0" xfId="0" applyFont="1" />
<xf numFmtId="0" fontId="6" fillId="0" borderId="0" xfId="0" applyFont="1" />
<xf numFmtId="165" borderId="0" fillId="0" fontId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="166" borderId="0" fillId="0" fontId="0" xfId="0" applyNumberFormat="1"/>
<xf numFmtId="167" borderId="0" fillId="0" fontId="0" xfId="0" applyNumberFormat="1"/>
</cellXfs>
<cellStyles count="1">
<cellStyle name="Standard" xfId="0" builtinId="0" />
</cellStyles>
<dxfs count="0" />
<tableStyles count="0" defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotStyleLight16" />
</styleSheet>'asvarbinary(max))
,@worksheet_xml varbinary(max)
,@_rels_rels varbinary(max) =cast(N'<?xml version="1.0" encoding="UTF-16" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="workbook.xml"/>
</Relationships>'asvarbinary(max));
-- if Worksheets is not specified, set @Worksheets to @SheetName + / + #data
if @Worksheetsisnull-- @SheetName is not null and @Worksheets is nullset@Worksheets= IsNull(@SheetName, 'Sheet1') +'/#data';
-- if a non-null value is passed in @WorkbookData, the output should be set in the optional output parameter @WorkbookData-- if a null or no value is passed in @WorkbookData, the output will be selected at the end of the procedure
if @WorkbookDataisnulland@OutputFileNameisnullset@SelectOutput=1elseset@WorkbookData=null;
select
Worksheet.ValueOrder as WorksheetNumber
,WorksheetTable.ValueOrder as ColumnNumber
,WorksheetTable.value
,WorksheetTableOptions.ValueOrder as WorksheetOptionNumber
,WorksheetTableOptions.value as WorksheetOption
into #WorksheetConfig
from dbo.StringSplit2(@Worksheets, '|') as Worksheet
cross apply dbo.StringSplit2(Worksheet.value, '/') as WorksheetTable
cross apply dbo.StringSplit2(WorksheetTable.value, '~') as WorksheetTableOptions;
select@NumberOfWorksheets=max(WorksheetNumber)
from #WorksheetConfig;
-- xml for @Workbook_xmlwith xmlnamespaces (
'http://schemas.openxmlformats.org/officeDocument/2006/relationships'as r
,default'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
)
select@Workbook_xml_temp = (
select
Worksheet.value as [sheet/@name]
,Worksheet.WorksheetNumber as [sheet/@sheetId]
,'rId'+cast(Worksheet.WorksheetNumber +1asvarchar) as [sheet/@r:id]
from #WorksheetConfig Worksheet
where ColumnNumber =1for xml path('sheets'), root('workbook')
);
set@Workbook_xml =cast(Replace(@Workbook_xml_temp, '</sheets><sheets>', '') asvarbinary(max));
-- xml for @_Content_Types__xmlselect @_Content_Types__xml__sheets = (
select'/sheet'+cast(Worksheet.WorksheetNumber asvarchar) +'.xml'as [@PartName]
,'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml'as [@ContentType]
from #WorksheetConfig Worksheet
where ColumnNumber =1for xml path('Override')
);
-- xml for @_Rels_workbook_xml_relswith xmlnamespaces (
default'http://schemas.openxmlformats.org/package/2006/relationships'
)
,x as (
select'rId1'as [@Id]
,'http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles'as [@Type]
,'styles.xml'as [@Target]
unionallselect'rId'+cast(Worksheet.WorksheetNumber +1asvarchar) as [@Id]
,'http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet'as [@Type]
,'sheet'+cast(Worksheet.WorksheetNumber asvarchar) +'.xml'as [@Target]
from #WorksheetConfig Worksheet
where ColumnNumber =1
)
select @_Rels_workbook_xml_rels =cast((
select*from x
for xml path('Relationship'), root('Relationships')
) asvarbinary(max));
set @_Content_Types__xml =cast(N'<?xml version="1.0" encoding="UTF-16" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Override PartName="/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
<Override PartName="/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
'+ @_Content_Types__xml__sheets +'
</Types>'asvarbinary(max));
exec dbo.AddFileToArchive
@FileName='[Content_Types].xml'
,@FileContents= @_Content_Types__xml
,@ArchiveData=@WorkbookData output
,@ArchiveInfo=@ArchiveInfo output;
exec dbo.AddFileToArchive
@FileName='_rels/.rels'
,@FileContents= @_rels_rels
,@ArchiveData=@WorkbookData output
,@ArchiveInfo=@ArchiveInfo output;
exec dbo.AddFileToArchive
@FileName='_rels/workbook.xml.rels'
,@FileContents= @_Rels_workbook_xml_rels
,@ArchiveData=@WorkbookData output
,@ArchiveInfo=@ArchiveInfo output;
exec dbo.AddFileToArchive
@FileName='workbook.xml'
,@FileContents=@workbook_xml
,@ArchiveData=@WorkbookData output
,@ArchiveInfo=@ArchiveInfo output;
exec dbo.AddFileToArchive
@FileName='styles.xml'
,@FileContents=@styles_xml
,@ArchiveData=@WorkbookData output
,@ArchiveInfo=@ArchiveInfo output;
while @CurrentWorksheet<=@NumberOfWorksheetsbeginset@sql='';
select@datatable=valuefrom #WorksheetConfig
where WorksheetNumber =@CurrentWorksheetand ColumnNumber =2;
select@sql+=','''+ rtrim(casewhen column_id >26thenchar(64+ ((column_id -1) /26)) +char(65+ ((column_id -1) %26)) elsechar(64+ column_id) end) +''' + cast(__r__ as varchar) as [c/@r]'+casewhen sc.system_type_id in (40, 41, 42, 43, 48, 52, 56, 58, 61, 62, 104, 106, 108, 127) then''else',''inlineStr'' as [c/@t]'end-- non text data types+casewhen sc.system_type_id in (42, 43, 58, 61) then',7 as [c/@s]'-- datetime2, datetimeoffset smalldatetime, datetimewhen sc.system_type_id in (41) then',8 as [c/@s]'-- timewhen sc.system_type_id in (40) then',9 as [c/@s]'-- dateelse''end-- smalldatetime, datetime, time, date+','+casewhen sc.system_type_id in (40, 58, 61) then'cast(cast('+ quotename(name) +'as datetime) as float) + case when ''19000301'' > '+ quotename(name) +' then 1 else 2 end'when sc.system_type_id in (42, 43) then'cast(cast(cast('+ quotename(name) +' as date) as datetime) as float) + datepart(hh, '+ quotename(name) +') / 24.00000 + datepart(mi, '+ quotename(name) +') / 1440.00000 + datepart(ss, '+ quotename(name) +') / 86400.00000 + datepart(ms, '+ quotename(name) +') / 86400000.00000 + case when ''19000301'' > '+ quotename(name) +' then 1 else 2 end'when sc.system_type_id in (41) then'cast(cast('+ quotename(name) +' as datetime) as float)'else quotename(name)
end+casewhen sc.system_type_id in (40, 41, 42, 43, 48, 52, 56, 58, 61, 62, 104, 106, 108, 127) then' as [c/v]'else' as [c/is/t]'end+','''''from tempdb.sys.columns sc
where object_id = object_id('tempdb.dbo.'+@datatable)
orderby column_id;
set@sql=right(@sql, len(@sql) -1);
set@sql='select @xml = (select(cast((
select
1 as [@r]
,(
select
rtrim(case when column_id > 26 then char(64 + ((column_id -1) / 26)) + char(65 + ((column_id -1) % 26)) else char(64 + column_id) end) + ''1'' as [c/@r]
,1 as [c/@s]
,''inlineStr'' as [c/@t]
,name as [c/is/t]
from tempdb.sys.columns sc
where object_id = object_id('''+casewhenleft(@datatable, 1) ='#'then'tempdb.dbo.'else''end+ Replace(@datatable, '''', '''''') +''')
order by column_id
for xml path(''''), type
)
from (select 1 as c1) x
for xml path(''row''))
as nvarchar(max))))
+
(select(IsNull(cast((
select
__r__ as [@r]
,(
select
'+@sql+'
for xml path(''''), type
)
from (select row_number() over (order by (select 1)) + 1 as __r__, * from '+ Replace(@datatable, '''', '''''') +') d
for xml path(''row''))
as nvarchar(max)), '''')));';
exec sp_executesql @stmt=@sql, @params= N'@xml nvarchar(max) output', @xml=@RowDataXML output;
if exists (select*from #WorksheetConfig where WorksheetNumber =@CurrentWorksheetand ColumnNumber =3and WorksheetOption ='autofilter')
beginset@rowcountsql= N'select @rowcount = count(*) + 1 from '+@datatable;
exec sp_executesql @stmt=@rowcountsql, @params= N'@rowcount int output', @rowcount=@rowcount output;
select@colcount=count(*)
from tempdb.sys.columns sc
where object_id = object_id('tempdb.dbo.'+@datatable);
select@AutoFilterXML= N'<autoFilter ref="A1:'+ rtrim(casewhen@colcount>26thenchar(64+ ((@colcount-1) /26)) +char(65+ ((@colcount-1) %26)) elsechar(64+@colcount) end) +cast(@rowcountas nvarchar)
+'" xr:uid="{'+cast(newid() as nvarchar(36)) +'}"/>';
end;
set@worksheet_xml =cast(
'<?xml version="1.0" encoding="UTF-16" standalone="yes"?><worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"><sheetData>'+@RowDataXML+'</sheetData>'+@AutoFilterXML+'</worksheet>'asvarbinary(max));
set@WorksheetFileName='sheet'+cast(@CurrentWorksheetasvarchar) +'.xml';
exec dbo.AddFileToArchive
@FileName=@WorksheetFileName
,@FileContents=@worksheet_xml
,@ArchiveData=@WorkbookData output
,@ArchiveInfo=@ArchiveInfo output;
set@CurrentWorksheet=@CurrentWorksheet+1;
set@AutoFilterXML= N'';
end;
if @SelectOutput=1select@WorkbookDataas ExcelSpreadsheetData;
if @OutputFileNameisnotnulland (select value_in_use from sys.configurations where name ='Ole Automation Procedures') =1beginexec sp_oacreate 'ADODB.Stream', @ObjectToken output;
exec sp_oasetproperty @ObjectToken, 'type', 1;
exec sp_oamethod @ObjectToken, 'open';
exec sp_oamethod @ObjectToken, 'write', null, @WorkbookData;
exec sp_oamethod @ObjectToken, 'savetofile', null, @OutputFileName, 2;
exec sp_oamethod @ObjectToken, 'close';
exec sp_oadestroy @ObjectToken;
end;
return;
GO
Example usage:
select*into #systables from sys.tables;
select*into #syscolumns from sys.columns;
select*into #systypes from sys.types;
exec dbo.GetExcelSpreadsheetData
@Worksheets='sys.tables/#systables/autofilter|sys.columns/#syscolumns/autofilter|sys.types/#systypes'
,@OutputFileName='c:\tmp\ExcelData.xlsx';
droptable #systables;
droptable #syscolumns;
droptable #systypes;
Edit: I agree with anyone who comments that this is a ridiculous thing to do in TSQL. It seemed like a bad idea at the time however it does make my life easier.
Solution 2:
I have not tried it but you might be able to send your queries from Excel. I would use R or Python to connect to SQL Server and execute my queries from there and then use R or Python functions to export the data frames to Excel. But I am sure there are easier ways and I would do like that because I know how to use these tools...
Try this Export SQL query data to Excel
Post a Comment for "Ssms: Automatically Save Multiple Result Sets From Same Sql Script Into Separate Tabs In Excel?"