ADQO

 

 

I created this homepage for my personal use.
In every day live I am a senior MI architect.
Furthermore I instruct and consult at database maintenance.
For that purpase this page is used as a reference guide.
I welcome anybody who would like to use this page.

 

 

Index

Microsoft SQL Server

ActiveX

Oracle

 


Microsoft SQL Server

tbl_001_track_statistics_db

-- TABLE
CREATE TABLE tbl_001_track_statistics_db
            (t001_date                  DATETIME                                                NOT NULL ,
             t001_server                VARCHAR(255)      COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL ,
             t001_database              VARCHAR(255)      COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL ,
             t001_size_file_data_MB     FLOAT                                                       NULL ,
             t001_size_file_log_MB      FLOAT                                                       NULL ,
             t001_data_KB               FLOAT                                                       NULL ,
             t001_index_KB              FLOAT                                                       NULL ,
             t001_unused_KB             FLOAT                                                       NULL )
GO

-- PRIMARY KEY
ALTER TABLE  tbl_001_track_statistics_db WITH NOCHECK ADD
CONSTRAINT   PKC_0001 PRIMARY KEY CLUSTERED
            (t001_date                                                                                   ,
             t001_server                                                                                 ,
             t001_database                                                                               )
GO

-- DEFAULT
ALTER TABLE  tbl_001_track_statistics_db ADD
CONSTRAINT   DF_t001_size_file_data_MB  DEFAULT(0)        FOR t001_size_file_data_MB                     ,
CONSTRAINT   DF_t001_size_file_log_MB   DEFAULT(0)        FOR t001_size_file_log_MB                      ,
CONSTRAINT   DF_t001_reserved_KB        DEFAULT(0)        FOR t001_reserved_KB                           ,
CONSTRAINT   DF_t001_data_KB            DEFAULT(0)        FOR t001_data_KB                               ,
CONSTRAINT   DF_t001_index_KB           DEFAULT(0)        FOR t001_index_KB                              ,
CONSTRAINT   DF_t001_unused_KB          DEFAULT(0)        FOR t001_unused_KB
GO

                                               

tbl_002_track_statistics_tbl

-- TABLE
CREATE TABLE tbl_002_track_statistics_tbl
            (t002_date                  datetime                                                NOT NULL ,
             t002_server                varchar(255)      COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL ,
             t002_database              varchar(255)      COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL ,
             t002_filegroup             varchar(255)      COLLATE SQL_Latin1_General_CP1_CI_AS      NULL ,
             t002_table_name            varchar(255)      COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL ,
             t002_rows                  float                                                       NULL ,
             t002_reserved_KB           float                                                       NULL ,
             t002_data_KB               float                                                       NULL ,
             t002_index_KB              float                                                       NULL ,
             t002_unused_KB             float                                                       NULL )
GO

-- PRIMARY KEY
ALTER TABLE  tbl_002_track_statistics_tbl WITH NOCHECK ADD
CONSTRAINT   PKC_0002 PRIMARY KEY  CLUSTERED
            (t002_date                                                                                   ,
             t002_server                                                                                 ,
             t002_database                                                                               ,
             t002_table_name                                                                             )
GO

-- DEFAULT
ALTER TABLE  tbl_002_track_statistics_tbl ADD
CONSTRAINT   DF_t002_rows               DEFAULT(0)        FOR t002_rows                                  ,
CONSTRAINT   DF_t002_reserved_KB        DEFAULT(0)        FOR t002_reserved_KB                           ,
CONSTRAINT   DF_t002_data_KB            DEFAULT(0)        FOR t002_data_KB                               ,
CONSTRAINT   DF_t002_index_KB           DEFAULT(0)        FOR t002_index_KB                              ,
CONSTRAINT   DF_t002_unused_KB          DEFAULT(0)        FOR t002_unused_KB
GO

                                               

tbl_003_track_statistics_file

CREATE TABLE tbl_003_track_statistics_file
            (t003_date                  DATETIME                                                NOT NULL ,
             t003_location              VARCHAR(50)     COLLATE SQL_Latin1_General_CP1_CI_AS    NOT NULL ,
             t003_space_free_bytes      DECIMAL(19, 0)                                          NOT NULL ,
             t003_space_used_bytes      DECIMAL(19, 0)                                          NOT NULL ,
             t003_dirs                  DECIMAL(19, 0)                                          NOT NULL ,
             t003_files                 DECIMAL(19, 0)                                          NOT NULL )

                                               

spr_adqo_track_statistics_db

CREATE PROCEDURE spr_adqo_track_statistics_db
                 @str_server VARCHAR(255) = 'ADQO'
AS
 
/*
** INITIATION & PREPARATION
*/
SET NOCOUNT ON
SET DATEFIRST 1
 
DECLARE @str_adqo_database         VARCHAR(255),
        @str_adqo_server           VARCHAR(255),
        @str_database              VARCHAR(255),
        @str_text                  VARCHAR(8000)
 
SELECT  @str_adqo_database = 'ADQO',
        @str_adqo_server = 'ADQO',
        @str_server = UPPER(@str_server)
 
IF @str_server <> @str_adqo_server
BEGIN
  EXECUTE('IF EXISTS (SELECT 1
                      FROM ' + @str_adqo_server + '.master.dbo.sysservers
                      WHERE  srvname = ''' + @str_server + ''') ' +
          '  EXECUTE sp_dropserver ''' + @str_server + ''', ''droplogins''')
  EXECUTE('EXECUTE sp_addlinkedserver ''' + @str_server + ''', ''SQL Server''')
  EXECUTE('EXECUTE sp_addlinkedsrvlogin ''' + @str_server + ''', ''true''')
END
EXECUTE('DECLARE cursor_spr_adqo_track_statistics_db_001 CURSOR GLOBAL STATIC FOR ' +
        'SELECT  sys_d.name ' +
        'FROM    ' + @str_server + '.master.dbo.sysdatabases sys_d ' +
        'WHERE   sys_d.name NOT IN (''tempdb'') ' +
        'ORDER   BY sys_d.name ')
OPEN cursor_spr_adqo_track_statistics_db_001
FETCH NEXT FROM cursor_spr_adqo_track_statistics_db_001 INTO @str_database
WHILE (@@fetch_status = 0)
BEGIN
  SELECT  @str_text = 'osql.exe -E -S ' + @str_server + ' -d ' + @str_database + ' -t 3600000 ' +
                               '-Q "EXIT(DBCC UPDATEUSAGE (''' + @str_database + ''') WITH COUNT_ROWS)" '
  PRINT   @str_text
  EXECUTE master.dbo.xp_cmdshell @str_text
 
/*
** DATABASE STATISTICS
*/
  EXECUTE('DELETE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_001_track_statistics_db ' +
          'WHERE      CONVERT(VARCHAR(50), t001_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND        t001_server = ''' + @str_server + ''' ' +
          'AND        t001_database = ''' + @str_database + ''' ')
  -- t001_size_file_data_MB
  EXECUTE('INSERT INTO ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_001_track_statistics_db ' +
          '      (t001_date, ' +
          '       t001_server, ' +
          '       t001_database, ' +
          '       t001_size_file_data_MB) ' +
          'SELECT GETDATE(), ' +
          ' ''' + @str_server + ''', ' +
          ' ''' + @str_database + ''', ' +
          '       SUM(sys_f.size) ' +
          'FROM   ' + @str_server + '.[' + @str_database + '].dbo.sysfiles sys_f ' +
          'WHERE  sys_f.groupid <> 0')
  -- t001_size_file_log_MB
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_001_track_statistics_db ' +
          'SET    t001_size_file_log_MB = (SELECT SUM(ISNULL(sys_f.size, 0)) ' +
          '                                 FROM   ' + @str_server + '.[' + @str_database + '].dbo.sysfiles sys_f ' +
          '                                 WHERE  sys_f.groupid = 0) ' +
          'WHERE  CONVERT(VARCHAR(50), t001_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND    t001_server = ''' + @str_server + ''' ' +
          'AND    t001_database = ''' + @str_database + ''' ')
  -- t001_reserved_KB
  --   = t001_data_KB + t001_index_KB + t001_unused_KB
  --   reserved: sum(reserved) where indid in (0, 1, 255)
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_001_track_statistics_db ' +
          'SET    t001_reserved_KB = (SELECT   SUM(sys_i.reserved) ' +
          '                            FROM ' + @str_server + '.[' + @str_database + '].dbo.sysindexes sys_i ' +
          '                            WHERE    sys_i.indid IN (0, 1, 255) ) ' +
          'WHERE  CONVERT(VARCHAR(50), t001_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND    t001_server = ''' + @str_server + ''' ' +
          'AND    t001_database = ''' + @str_database + ''' ')
  -- t001_data_KB
  --   data: sum(dpages) where indid < 2
  --           + sum(used) where indid = 255 (text)
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_001_track_statistics_db ' +
          'SET    t001_data_KB = (SELECT   SUM(sys_i.dpages) ' +
          '                        FROM ' + @str_server + '.[' + @str_database + '].dbo.sysindexes sys_i ' +
          '                        WHERE    sys_i.indid < 2 ) ' +
          'WHERE  CONVERT(VARCHAR(50), t001_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND    t001_server = ''' + @str_server + ''' ' +
          'AND    t001_database = ''' + @str_database + ''' ')
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_001_track_statistics_db ' +
          'SET    t001_data_KB = t001_data_KB + (SELECT   ISNULL(SUM(sys_i.used), 0) ' +
          '                                        FROM ' + @str_server + '.[' + @str_database + '].dbo.sysindexes sys_i ' +
          '                                        WHERE    sys_i.indid = 255) ' +
          'WHERE  CONVERT(VARCHAR(50), t001_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND    t001_server = ''' + @str_server + ''' ' +
          'AND    t001_database = ''' + @str_database + ''' ')
 
  -- t001_index_KB
  --   index: sum(used) where indid in (0, 1, 255) - data
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_001_track_statistics_db ' +
          'SET    t001_index_KB = - t001_data_KB + (SELECT   SUM(sys_i.used) ' +
          '                                           FROM ' + @str_server + '.[' + @str_database + '].dbo.sysindexes sys_i ' +
          '                                           WHERE    sys_i.indid IN (0, 1, 255) ) ' +
          'WHERE  CONVERT(VARCHAR(50), t001_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND    t001_server = ''' + @str_server + ''' ' +
          'AND    t001_database = ''' + @str_database + ''' ')
  -- t001_unused_KB
  --   unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_001_track_statistics_db ' +
          'SET    t001_unused_KB = t001_reserved_KB - (SELECT   SUM(sys_i.used) ' +
          '                                              FROM ' + @str_server + '.[' + @str_database + '].dbo.sysindexes sys_i ' +
          '                                              WHERE    sys_i.indid IN (0, 1, 255)) ' +
          'WHERE  CONVERT(VARCHAR(50), t001_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND    t001_server = ''' + @str_server + ''' ' +
          'AND    t001_database = ''' + @str_database + ''' ')
  -- Space used calculated in the following way
  --   @dbsize = Pages used
  --   @bytesperpage = spt_v.low (where spt_v = master.dbo.spt_values) is
  --   the # of bytes per page when spt_v.type = 'E' and
  --   spt_v.number = 1.
  --   Size = @dbsize * spt_v.low / (1048576 (OR 1 MB))
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_001_track_statistics_db ' +
          'SET        t001_size_file_data_MB = t001_size_file_data_MB * spt_v.low / 1048576, ' +
          '           t001_size_file_log_MB = t001_size_file_log_MB * spt_v.low / 1048576, ' +
          '           t001_reserved_KB = t001_reserved_KB * spt_v.low / 1024, ' +
          '           t001_data_KB = t001_data_KB * spt_v.low / 1024, ' +
          '           t001_index_KB = t001_index_KB * spt_v.low / 1024, ' +
          '           t001_unused_KB = t001_unused_KB * spt_v.low / 1024 ' +
          'FROM   ' + @str_server + '.master.dbo.spt_values spt_v ' +
          'WHERE      spt_v.number = 1 ' +
          'AND        spt_v.type = ''E'' ' +
          'AND        CONVERT(VARCHAR(50), t001_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND        t001_server = ''' + @str_server + ''' ' +
          'AND        t001_database = ''' + @str_database + ''' ')
 
/*
** TABLE STATISTICS
*/
  EXECUTE('DELETE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_002_track_statistics_tbl ' +
          'WHERE  CONVERT(VARCHAR(50), t002_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND    t002_server = ''' + @str_server + ''' ' +
          'AND    t002_database = ''' + @str_database + ''' ')
  EXECUTE('INSERT INTO ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_002_track_statistics_tbl ' +
          '      (t002_date, ' +
          '       t002_server, ' +
          '       t002_database, ' +
          '       t002_filegroup, ' +
          '       t002_table_name, ' +
          '       t002_rows) ' +
          'SELECT GETDATE(), ' +
          ' ''' + @str_server + ''', ' +
          ' ''' + @str_database + ''', ' +
          '       sys_g.groupname, ' +
          '       sys_o.name, ' +
          '       sys_i.rows ' +
          'FROM ' + @str_server + '.[' + @str_database + '].dbo.sysobjects sys_o, ' +
          '     ' + @str_server + '.[' + @str_database + '].dbo.sysindexes sys_i, ' +
          '     ' + @str_server + '.[' + @str_database + '].dbo.sysfilegroups sys_g ' +
          'WHERE  sys_o.xtype = ''U'' ' +
          'AND    sys_o.id = sys_i.id ' +
          'AND    sys_i.indid < 2 ' +
          'AND    sys_i.groupid = sys_g.groupid ' +
          'ORDER BY 1, 2, 3 ')
  -- t002_reserved_KB
  --   = t002_data_KB + t002_index_KB + t002_unused_KB
  --   Now calculate the summary data.
  --   reserved: sum(reserved) where indid in (0, 1, 255)
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_002_track_statistics_tbl ' +
          'SET        t002_reserved_KB = (SELECT   SUM(sys_i.reserved) ' +
          '                               FROM ' + @str_server + '.[' + @str_database + '].dbo.sysindexes sys_i ' +
          '                               WHERE    sys_i.indid IN (0, 1, 255) ' +
          '                               AND      sys_i.id = (SELECT   sys_o.id ' +
          '                                                    FROM ' + @str_server + '.[' + @str_database + '].dbo.sysobjects sys_o ' +
          '                                                    WHERE    sys_o.name COLLATE SQL_Latin1_General_CP1_CI_AS
                                                                         = t002_table_name COLLATE SQL_Latin1_General_CP1_CI_AS)) ' +
          'WHERE      CONVERT(VARCHAR(50), t002_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND        t002_server = ''' + @str_server + ''' ' +
          'AND        t002_database = ''' + @str_database + ''' ')
  -- t002_data_KB
  --   data: sum(dpages) where indid < 2
  --           + sum(used) where indid = 255 (text)
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_002_track_statistics_tbl ' +
          'SET        t002_data_KB = (SELECT   SUM(sys_i.dpages) ' +
          '                            FROM ' + @str_server + '.[' + @str_database + '].dbo.sysindexes sys_i ' +
          '                            WHERE    sys_i.indid < 2 ' +
          '                            AND      sys_i.id = (SELECT   sys_o.id ' +
          '                                                 FROM ' + @str_server + '.[' + @str_database + '].dbo.sysobjects sys_o ' +
          '                                                 WHERE    sys_o.name COLLATE SQL_Latin1_General_CP1_CI_AS
                                                                     = t002_table_name COLLATE SQL_Latin1_General_CP1_CI_AS)) ' +
          'WHERE      CONVERT(VARCHAR(50), t002_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND        t002_server = ''' + @str_server + ''' ' +
          'AND        t002_database = ''' + @str_database + '''')
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_002_track_statistics_tbl ' +
          'SET        t002_data_KB = t002_data_KB + (SELECT   ISNULL(SUM(sys_i.used), 0) ' +
          '                                            FROM ' + @str_server + '.[' + @str_database + '].dbo.sysindexes sys_i ' +
          '                                            WHERE    sys_i.indid = 255 ' +
          '                                            AND      sys_i.id = (SELECT sys_o.id ' +
          '                                                                 FROM ' + @str_server + '.[' + @str_database + '].dbo.sysobjects sys_o ' +
          '                                                                 WHERE  sys_o.name COLLATE SQL_Latin1_General_CP1_CI_AS
                                                                                   = t002_table_name COLLATE SQL_Latin1_General_CP1_CI_AS)) ' +
          'WHERE      CONVERT(VARCHAR(50), t002_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND        t002_server = ''' + @str_server + ''' ' +
          'AND        t002_database = ''' + @str_database + '''')
  -- t002_index_KB
  --   index: sum(used) where indid in (0, 1, 255) - data
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_002_track_statistics_tbl ' +
          'SET        t002_index_KB = - t002_data_KB + (SELECT   SUM(sys_i.used) ' +
          '                                               FROM ' + @str_server + '.[' + @str_database + '].dbo.sysindexes sys_i ' +
          '                                               WHERE    sys_i.indid IN (0, 1, 255) ' +
          '                                               AND      sys_i.id = (SELECT sys_o.id ' +
          '                                                                    FROM ' + @str_server + '.[' + @str_database + '].dbo.sysobjects sys_o ' +
          '                                                                    WHERE  sys_o.name COLLATE SQL_Latin1_General_CP1_CI_AS
                                                                                 = t002_table_name COLLATE SQL_Latin1_General_CP1_CI_AS)) ' +
          'WHERE      CONVERT(VARCHAR(50), t002_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND        t002_server = ''' + @str_server + ''' ' +
          'AND        t002_database = ''' + @str_database + '''')
  -- t002_unused_KB
  --   unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_002_track_statistics_tbl ' +
          'SET        t002_unused_KB = t002_reserved_KB - (SELECT   SUM(sys_i.used) ' +
          '                                                  FROM ' + @str_server + '.[' + @str_database + '].dbo.sysindexes sys_i ' +
          '                                                  WHERE    sys_i.indid IN (0, 1, 255) ' +
          '                                                  AND      sys_i.id = (SELECT   sys_o.id ' +
          '                                                                       FROM ' + @str_server + '.[' + @str_database + '].dbo.sysobjects sys_o ' +
          '                                                                       WHERE    sys_o.name COLLATE SQL_Latin1_General_CP1_CI_AS
                                                                                           = t002_table_name COLLATE SQL_Latin1_General_CP1_CI_AS)) ' +
          'WHERE      CONVERT(VARCHAR(50), t002_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND        t002_server = ''' + @str_server + ''' ' +
          'AND        t002_database = ''' + @str_database + '''')
  -- Space used calculated in the following way
  --   @dbsize = Pages used
  --   @bytesperpage = spt_v.low (where spt_v = master.dbo.spt_values) is
  --   the # of bytes per page when spt_v.type = 'E' and
  --   spt_v.number = 1.
  --   Size = @dbsize * spt_v.low / (1048576 (OR 1 MB))
  EXECUTE('UPDATE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_002_track_statistics_tbl ' +
          'SET        t002_reserved_KB = t002_reserved_KB * spt_v.low / 1024, ' +
          '           t002_data_KB = t002_data_KB * spt_v.low / 1024, ' +
          '           t002_index_KB = t002_index_KB * spt_v.low / 1024, ' +
          '           t002_unused_KB = t002_unused_KB * spt_v.low / 1024 ' +
          'FROM   ' + @str_server + '.master.dbo.spt_values spt_v ' +
          'WHERE      spt_v.number = 1 ' +
          'AND        spt_v.type = ''E'' ' +
          'AND        CONVERT(VARCHAR(50), t002_date, 112) = CONVERT(VARCHAR(50), GETDATE(), 112) ' +
          'AND        t002_server = ''' + @str_server + ''' ' +
          'AND        t002_database = ''' + @str_database + ''' ')
  FETCH NEXT FROM cursor_spr_adqo_track_statistics_db_001 INTO @str_database
END
CLOSE cursor_spr_adqo_track_statistics_db_001
DEALLOCATE cursor_spr_adqo_track_statistics_db_001
IF @str_server <> @str_adqo_server
BEGIN
  EXECUTE('IF EXISTS (SELECT 1 FROM ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.sysservers WHERE srvname = ''' + @str_server + ''') ' +
          '  EXECUTE sp_dropserver ''' + @str_server + ''', ''droplogins''')
END
 
/*
** COMPRESS TABLE STATISTICS
*/
-- tbl_002_track_statistics_tbl
EXECUTE('DELETE ' + @str_adqo_server + '.' + @str_adqo_database + '.dbo.tbl_002_track_statistics_tbl ' +
        'WHERE  NOT EXISTS (SELECT * ' +
        '                   FROM  (SELECT   MAX(t002_date) AS fic01_date, ' +
        '                                   t002_server AS fic01_server, ' +
        '                                   t002_database AS fic01_database, ' +
        '                                   t002_table_name AS fic01_table_name ' +
        '                          FROM     tbl_002_track_statistics_tbl ' +
        '                          GROUP BY YEAR(t002_date), ' +
        '                                   DATEPART(WEEK, t002_date), ' +
        '                                   t002_server, ' +
        '                                   t002_database, ' +
        '                                   t002_table_name) AS fic01 ' +
        '                   WHERE t002_date = fic01_date ' +
        '                   AND   t002_server = fic01_server ' +
        '                   AND   t002_database = fic01_database ' +
        '                   AND   t002_table_name = fic01_table_name) ' +
        'AND    t002_date < DATEADD(MONTH, -1, GETDATE())')

                                               

Link to Oracle

-- Create Linked Server
IF EXISTS (SELECT *
           FROM master.dbo.sysservers
           WHERE srvname = 'ADQO')
BEGIN
  EXECUTE  master.dbo.sp_dropserver 'ADQO', 'droplogins'
END
GO
EXEC master.dbo.sp_addlinkedserver
     @server = 'ADQO',
     @srvproduct = 'Oracle',
     @provider = 'MSDAORA',
     @datasrc = 'Name_Oracle_Server'
GO
 
-- Create Login For Linked Server
EXEC master.dbo.sp_addlinkedsrvlogin
    'ADQO',
    'FALSE',
     NULL,
    'user',
    'password'
GO

                                               

Retrieve Oracle Data

-- General Select
SELECT *
FROM   OPENQUERY(ADQO ,
                'SELECT *
                 FROM   all_objects
                 WHERE  owner = ''ADQO''
                 AND    object_type = ''TABLE''
                 ORDER BY object_name')
 
-- Execute BodyProcedure_01
SELECT *
FROM OPENQUERY(TST03,
             '{Call ADQO.ADQO_Test_Package.ADQO_Test_BodyProcedure_01
              (''2004-03-15 00:00:00'',
              {resultset 4000,
               column_out_01,
               column_out_02,
               column_out_03,
               column_out_04})}')
 
-- Execute BodyProcedure_02
SELECT *
FROM OPENQUERY(TST03,
             '{Call ADQO.ADQO_Test_Package.ADQO_Test_BodyProcedure_02
              (''Hello hi'',
              {resultset 1,
               parameter_out_01})}')
 
-- Execute BodyProcedure_03
SELECT *
FROM OPENQUERY(TST03,
             '{Call ADQO.ADQO_Test_Package.ADQO_Test_BodyProcedure_03
              ({resultset 1, parameter_out_01})}')

                                               


ActiveX

ActiveX Mail

Dim iConf, iMsg, iBP
Dim Flds
Dim strCc, strFrom, strSMTP, strServer, strSubject, strText, strTo
 
Const cdoSendUsingPort = 2
 
strSmtp = "ADQO"
strTo = "ADQO@ADQO.nl"
strCc = "ADQO2@ADQO.nl; ADQO3@ADQO.nl"
strServer = "ADQO"
strFrom = "SQLServer_" & strServer
strSubject = "ADQO"
strText = "ADQO"
set iMsg = CreateObject("CDO.Message")
set iConf = CreateObject("CDO.Configuration")
 
Set Flds = iConf.Fields
 
With Flds
  .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSMTP
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
  .Update
End With
 
Set iBP = iMsg.AddAttachment("C:\ADQO.doc")
 
With iMsg
  Set .Configuration = iConf
      .To =  strTo
      .Cc = strCc
      .From = strFrom
      .Subject = strSubject
      .TextBody= strText
      .Send
End With
 
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing
Set iBP = Nothing

                                               

ActiveX Backup DTS to File

Dim obj_cn, obj_fso, obj_pckg, obj_rs
Dim str_backup_dir, str_database, str_date, str_password, str_pckg, str_server, str_sql, str_user
 
str_backup_dir = "E:\ADQO\"
str_server = "ADQO"
str_database = "msdb"
str_user = "sa"
str_password = "ADQO"
 
Set obj_cn = CreateObject("ADODB.Connection")
Set obj_fso = CreateObject("Scripting.FileSystemObject")
Set obj_rs = CreateObject("ADODB.Recordset")
 
obj_cn.Provider = "sqloledb"
obj_cn.Properties("Data Source") = str_server
obj_cn.Properties("Initial Catalog") = str_database
obj_cn.Properties("User ID") = str_user
obj_cn.Properties("Password") = str_password
obj_cn.Open
 
str_sql = "SELECT '_' + CONVERT(VARCHAR(10), DATEPART(YEAR, GETDATE())) + 'wk' + CASE WHEN LEN(DATEPART(WEEK, GETDATE())) = 1 THEN '0' ELSE '' END + CONVERT(VARCHAR(10), DATEPART(WEEK, GETDATE()))"
obj_rs.Open str_sql, obj_cn
obj_rs.Movefirst
str_dt = obj_rs.Fields(0)
obj_rs.Close
 
str_sql = "SELECT COUNT(DISTINCT name) FROM msdb.dbo.sysdtspackages"
obj_rs.Open str_sql, obj_cn
obj_rs.Movefirst
counter = obj_rs.Fields(0)
obj_rs.Close
 
str_sql = "SELECT DISTINCT name FROM msdb.dbo.sysdtspackages ORDER BY 1"
obj_rs.Open str_sql, obj_cn
obj_rs.Movefirst
 
Do While counter > 0
  str_pckg = obj_rs.Fields(0)
 
  Set obj_pckg = CreateObject("DTS.Package2")
  obj_pckg.LoadFromSQLServer str_server, str_user, str_password, , , , , str_pckg
 
  If obj_fso.FileExists(str_backup_dir + str_pckg + str_dt + ".dts") Then
    obj_fso.DeleteFile str_backup_dir + str_pckg + str_dt + ".dts"
  End If
 
  obj_pckg.SaveToStorageFile str_backup_dir + str_pckg + str_dt + ".dts"
  Set obj_pckg = Nothing
 
  obj_rs.MoveNext
  counter = counter - 1
Loop
 
Set obj_cn = Nothing
Set obj_fso = Nothing
Set obj_rs = Nothing

                                               

ActiveX Backup DTS to File Integrated

Dim obj_cn, obj_fso, obj_pckg, obj_rs
Dim str_backup_dir, str_database, str_date, str_password, str_pckg, str_server, str_sql, str_user
 
str_backup_dir = "E:\ADQO\"
str_server = "ADQO"
str_database = "msdb"
 
Set obj_cn = CreateObject("ADODB.Connection")
Set obj_fso = CreateObject("Scripting.FileSystemObject")
Set obj_rs = CreateObject("ADODB.Recordset")
 
obj_cn.Provider = "sqloledb"
obj_cn.Properties("Data Source") = str_server
obj_cn.Properties("Initial Catalog") = str_database
obj_cn.Properties("Integrated Security") = "SSPI"
obj_cn.Open
 
str_sql = "SELECT '_' + CONVERT(VARCHAR(10), DATEPART(YEAR, GETDATE())) + 'wk' + CASE WHEN LEN(DATEPART(WEEK, GETDATE())) = 1 THEN '0' ELSE '' END + CONVERT(VARCHAR(10), DATEPART(WEEK, GETDATE()))"
obj_rs.Open str_sql, obj_cn
obj_rs.Movefirst
str_dt = obj_rs.Fields(0)
obj_rs.Close
 
str_sql = "SELECT COUNT(DISTINCT name) FROM msdb.dbo.sysdtspackages"
obj_rs.Open str_sql, obj_cn
obj_rs.Movefirst
counter = obj_rs.Fields(0)
obj_rs.Close
 
str_sql = "SELECT DISTINCT name FROM msdb.dbo.sysdtspackages ORDER BY 1"
obj_rs.Open str_sql, obj_cn
obj_rs.Movefirst
 
Do While counter > 0
  str_pckg = obj_rs.Fields(0)
 
  Set obj_pckg = CreateObject("DTS.Package2")
  obj_pckg.LoadFromSQLServer str_server, , , 256, , , , str_pckg
 
  If obj_fso.FileExists(str_backup_dir + str_pckg + str_dt + ".dts") Then
    obj_fso.DeleteFile str_backup_dir + str_pckg + str_dt + ".dts"
  End If
 
  obj_pckg.SaveToStorageFile str_backup_dir + str_pckg + str_dt + ".dts"
  Set obj_pckg = Nothing
 
  obj_rs.MoveNext
  counter = counter - 1
Loop
 
Set obj_cn = Nothing
Set obj_fso = Nothing
Set obj_rs = Nothing

                                               


Oracle

ADQO_Test_Package

CREATE OR REPLACE PACKAGE ADQO_Test_Package
IS
 
-- Declare
TYPE column_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE column_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE column_varchar2_30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
 
-- BodyProcedure_01
PROCEDURE ADQO_Test_BodyProcedure_01 (parameter_in_01 IN VARCHAR2,
                                      column_out_01 OUT column_varchar2_30,
                                      column_out_02 OUT column_number,
                                      column_out_03 OUT column_varchar2_30,
                                      column_out_04 OUT column_date);
 
-- BodyProcedure_02
PROCEDURE ADQO_Test_BodyProcedure_02 (parameter_in_01 IN VARCHAR2,
                                      parameter_out_01 OUT column_varchar2_30);
 
-- BodyProcedure_03
PROCEDURE ADQO_Test_BodyProcedure_03 (parameter_out_01 OUT column_date);
 
END ADQO_Test_Package;

                                               

ADQO_Test_BodyProcedure

CREATE OR REPLACE PACKAGE BODY ADQO_Test_Package
IS
 
-- BodyProcedure_01
PROCEDURE ADQO_Test_BodyProcedure_01
         (parameter_in_01 IN VARCHAR2,
          column_out_01 OUT column_varchar2_30,
          column_out_02 OUT column_number,
          column_out_03 OUT column_varchar2_30,
          column_out_04 OUT column_date)
IS
count_records BINARY_INTEGER DEFAULT 1;
CURSOR cursor_all_objects IS
       SELECT owner,
              object_id,
              object_name,
              last_ddl_time
       FROM   all_objects
       WHERE  last_ddl_time >=  TO_DATE(parameter_in_01, 'YYYY-MM-DD HH24:MI:SS');
BEGIN
  FOR all_objects IN cursor_all_objects
  LOOP
    column_out_01(count_records) := all_objects.owner;
    column_out_02(count_records) := all_objects.object_id;
    column_out_03(count_records) := all_objects.object_name;
    column_out_04(count_records) := all_objects.last_ddl_time;
    count_records := count_records + 1;
  END LOOP;
END ADQO_Test_BodyProcedure_01;
 
-- BodyProcedure_02
PROCEDURE ADQO_Test_BodyProcedure_02
         (parameter_in_01 IN VARCHAR2,
          parameter_out_01 OUT column_varchar2_30)
IS
BEGIN
  parameter_out_01(1) := parameter_in_01;
END ADQO_Test_BodyProcedure_02;
 
-- BodyProcedure_03
PROCEDURE ADQO_Test_BodyProcedure_03
         (parameter_out_01 OUT column_date)
IS
CURSOR cursor_all_objects IS
       SELECT MAX(last_ddl_time) AS last_ddl_time
       FROM   all_objects;
BEGIN
  FOR all_objects IN cursor_all_objects
  LOOP
    parameter_out_01(1) := all_objects.last_ddl_time;
  END LOOP;
END ADQO_Test_BodyProcedure_03;
 
END ADQO_Test_Package;
 

                                               


copyright © 2004 ADQO
most recent update: 2004-05-09