September 11, 2018 at 6:51 am
Insert data in Microsoft Word, I have a Script that takes data from a SQL Server Backup, and these results I need to save in a Word file, could they be declared Variables or something like that? I need you to help me, here is the code I use to obtain the data:
DROP DATABASE dbSuarez_ABO516
GO
declare @backupFile varchar(max) = '\\server28\Dvirtual1\Cesto\VerificaciondeBases\dbempresa 201805111743.bak';
declare @dbName varchar(256) = 'dbSuarez_ABO516';
declare @dbMDFLocation varchar (256) = 'C:\Dvirtual1\Bases de datos\dbSuarez_ABO516.mdf' ;
declare @dbLDFLocation varchar (256) = 'C:\Dvirtual1\Bases de datos\dbSuarez_ABO516.ldf';
SELECT CONVERT(VARCHAR(12), GETDATE(), 105) + ' ' + CONVERT(VARCHAR(5), GETDATE(), 108) as 'FechaVerificacion'
declare @Backups table
(
BackupName varchar(256),
BackupDescription varchar(256),
BackupType varchar(256),
ExpirationDate varchar(256),
Compressed varchar(256),
Position varchar(256),
DeviceType varchar(256),
UserName varchar(256),
ServerName varchar(256),
DatabaseName varchar(256),
DatabaseVersion varchar(256),
DatabaseCreationDate varchar(256),
BackupSize varchar(256),
FirstLSN varchar(256),
LastLSN varchar(256),
CheckpointLSN varchar(256),
DatabaseBackupLSN varchar(256),
BackupStartDate varchar(256),
BackupFinishDate smalldatetime,
SortOrder varchar(256),
CodePage varchar(256),
UnicodeLocaleId varchar(256),
UnicodeComparisonStyle varchar(256),
CompatibilityLevel varchar(256),
SoftwareVendorId varchar(256),
SoftwareVersionMajor varchar(256),
SoftwareVersionMinor varchar(256),
SoftwareVersionBuild varchar(256),
MachineName varchar(256),
Flags varchar(256),
BindingID varchar(256),
RecoveryForkID varchar(256),
Collation varchar(256),
FamilyGUID varchar(256),
HasBulkLoggedData varchar(256),
IsSnapshot varchar(256),
IsReadOnly varchar(256),
IsSingleUser varchar(256),
HasBackupChecksums varchar(256),
IsDamaged varchar(256),
BeginsLogChain varchar(256),
HasIncompleteMetaData varchar(256),
IsForceOffline varchar(256),
IsCopyOnly varchar(256),
FirstRecoveryForkID varchar(256),
ForkPointLSN varchar(256),
RecoveryModel varchar(256),
DifferentialBaseLSN varchar(256),
DifferentialBaseGUID varchar(256),
BackupTypeDescription varchar(256),
BackupSetGUID varchar(256),
CompressedBackupSize varchar(256),
Containment varchar(256),
KeyAlgorithm varchar(256),
EncryptorThumbprint varchar(256),
EncryptorType varchar(256),
Seq int NOT NULL identity(1,1)
);
insert into @Backups exec('restore headeronly from disk = '''+ @backupFile +'''');
select ServerName as 'ServidorOrigen', DatabaseName as NombreBase /*, BackupName as 'Nombre del Backup'*/ , CONVERT(VARCHAR(10), BackupFinishDate, 105) + ' ' + CONVERT(VARCHAR(5), BackupFinishDate, 108) as 'Fecha del Backup' from @Backups;
declare @GrupodeArchivos table
(
LogicalName varchar(256),
PhysicalName varchar(256),
Type varchar(256),
FileGroupName varchar(256),
Size float,
MaxSize varchar(256),
FileId varchar(256),
CreateLSN varchar(256),
DropLSN varchar(256),
UniqueId varchar(256),
ReadOnlyLSN varchar(256),
ReadWriteLSN varchar(256),
BackupSizeInBytes varchar(256),
SourceBlockSize varchar(256),
FileGroupId varchar(256),
LogGroupGUID varchar(256),
DifferentialBaseLSN varchar(256),
DifferentialBaseGUID varchar(256),
IsReadOnly varchar(256),
IsPresent varchar(256),
TDEThumbprint varchar(256),
SnapshotUrl varchar(256),
Seq int NOT NULL identity(1,1)
);
insert into @GrupodeArchivos exec('RESTORE FILELISTONLY from disk = '''+ @backupFile +'''');
select LogicalName as 'Nombre Logico' ,/* PhysicalName as 'Nombre Fisico',*/ Type as Tipo ,convert(decimal(10, 2), Size /1048576) as 'Tamaño MB' from @GrupodeArchivos;
declare @dbNombreMDFaCrear varchar (256) = (select LogicalName from @GrupodeArchivos where type='d');
declare @dbNombreLDFaCrear varchar (256) = (select LogicalName from @GrupodeArchivos where type='l');
RESTORE DATABASE @dbName
FROM DISK = @backupFile
WITH MOVE @dbNombreMDFaCrear TO @dbMDFLocation , --Carga la variable @dbNombreMDFaCrear con el LogicalName del Backup
MOVE @dbNombreLDFaCrear TO @dbLDFLocation
GO
DBCC CHECKDB ('dbSuarez_ABO516') WITH NO_INFOMSGS
USE dbSuarez_ABO516
DECLARE @spaceUsed TABLE (
database_name varchar(max),
database_size varchar(max),
"unallocated space" varchar(max),
reserved varchar(max),
data varchar(max),
index_size varchar(max),
unused varchar(max))
INSERT INTO @spaceUsed
EXEC sp_spaceused @updateusage = N'TRUE' , @oneresultset = 1
SELECT database_size as 'TamañoBaseDatos' FROM @spaceUsed
use dbSuarez_ABO516
Select sum (case When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
end) * 8192 / 1024 / 1024.00 as 'TamañoRealDatos'
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
USE dbSuarez_ABO516
select top 15 AUDI_AUDITOR as N_Auditoria, AUDI_USUARIO as Usuario, AUDI_PROGRAMA as Programa, AUDI_HORA as Hora, AUDI_ORIGEN_TRANS as OrigenTrans, AUDI_PC as Pc
from SEGU_AUDI order by AUDI_HORA desc
September 11, 2018 at 11:19 am
About the only way to automate this beyond just copying and pasting the data into Word, would be to use an SSIS package, where you can run that SQL code into recordsets in a Script Task, that then instantiates MS Word, opens an empty document, formats a couple of tables, and iterates through each recordset to populate the data in those tables, followed by saving the document somewhere on the same machine as the SQL instance. It would require either C# or VB skills and knowledge of the MS Word Object Model. It would also require MS Word be installed on the same machine. Not sure if it's worth that much effort.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 11, 2018 at 11:26 am
sgmunson - Tuesday, September 11, 2018 11:19 AMAbout the only way to automate this beyond just copying and pasting the data into Word, would be to use an SSIS package, where you can run that SQL code into recordsets in a Script Task, that then instantiates MS Word, opens an empty document, formats a couple of tables, and iterates through each recordset to populate the data in those tables, followed by saving the document somewhere on the same machine as the SQL instance. It would require either C# or VB skills and knowledge of the MS Word Object Model. It would also require MS Word be installed on the same machine. Not sure if it's worth that much effort.
if I am currently copying by hand gluing 🙁 ... and I was trying to apply several solutions but all, all ... are very complex, when returning several group of results, it complicates me a bit.
I had tried to save all the data in a single table and from there import it into Word, but it is complex starting from my script to do that.
September 11, 2018 at 11:31 am
One thing you could try (but this is a little advanced and would require a little reading) is to use an XML Transform to generate a word document. Note this link. Also note that the instructions don't detail how to automate the process but it's highly doable. For this solution to work you would need CLR integration enabled along with the mdq.xmltransform CLR.
An alternative (which I thought of while reviewing the aforementioned link) is to create an SSRS report with your SQL data. Then you can create a subscription to that report and specify MSWord as the format for your report. This is simple if you are familiar with SSRS and subscriptions.
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply