MS-SQL Server Database Size Check from Within a Clarion Application

 

Note: This no longer works in SQL-Server 2008 and later.The OLE feature has been dropped/changed :-(  There "should" be another way to accomplish the same results, but as yet I have not yet worked on this. 

 

When running with MS-SQLExpress you may need to periodically check the size of the database. The reason is that the maximum size allowed in the SQLExpress version is 4Gigs.

After this size is reached, BAD things happen = No more INSERTS.

I have built a small CW6 project that allows you to use OLE ( SQL Object Linking and Embedding) of a SQL- DMO (SQL Distributed Management Object)  to gather the sizes of all the databases on a particular server. If you want to check just one database, then you could get details for all, then select the specific one from the Queue.

 
Here is the project file:

============== begin JGdbsizer.prj ===============

-- John Griffiths MS-SQL SQLDMO Database Sizer
#noedit
#system win32
#model clarion dll
#set RELEASE = on
#pragma debug(vid=>off)
#compile "JGdbsizer.clw"
#link "JGdbsizer.exe"

============== end JGdbsizer.prj ===============


and below is the JGdbsizer.CLW file
 

==============  begin JGdbsizer.CLW ===============

  program
!! ++++++++++++++++++++++++++++++++++++++++++++++++++++++
!  SQLDMO Example to fid sizes of existing database files
!  by John Griffiths
!  Size is shown in Megabytes and includes size of data and LOG files combined
!  Enjoy.
!  e-mail:    questions@sqlkey.com   OR   globaljohn@gmail.com
! 
!! ++++++++++++++++++++++++++++++++++++++++++++++++++++++
  map
  end
!! display Q of Database sizes by John Griffiths, Kerrville Tx and Perth Australia.

HowMany         LONG
ii  long

TrustedLogin     byte(1)

ServerName     cstring(41)
LoginName      cstring(21)
LoginPwd       cstring(21)


dbq    queue,pre(dbq)
ix           long
dbname       string(40)
SizeMB       long
       end


window WINDOW(' SQLDMO Sizer'),AT(,,315,180),SYSTEM,GRAY,DOUBLE
       OLE,AT(17,3,40,14),USE(?Ole)
       END
       LIST,AT(64,2,223,110),USE(?Listdbs),VSCROLL,FORMAT('20L(1)|~IX~@n_4@102L(2)|~database Name~@S40@20L(3)|~SIZE in MB~@n11@'), |
           FROM(dbq)
       CHECK('Use Trusted Login'),AT(78,131),USE(TrustedLogin),VALUE('1','0')
       PROMPT('By John Griffiths'),AT(3,167),USE(?Prompt1)
       BUTTON('Do it'),AT(232,130,48,16),USE(?DoitButton)
       ENTRY(@s20),AT(79,146,115,10),USE(LoginName),HIDE
       ENTRY(@s40),AT(65,118,115,10),USE(ServerName)
       BUTTON('Close'),AT(231,155,48,16),USE(?CloseButton),STD(STD:Close)
       ENTRY(@s20),AT(79,160,115,10),USE(LoginPwd),HIDE
     END


  code

  ServerName = '.\SQLExpress'
  LoginName  = 'sa'
  LoginPwd   = 'putYoursHere'


  open(window)
  display()
  accept
    case event()
    of event:openwindow

    of event:User + 111

      ?ole{prop:reportexception}=true
      ?ole{prop:create}='SQLDMO.SQLSERVER'

      if TrustedLogin
        ?ole{'LoginSecure'} = TRUE
        ?ole{'Connect(' & ServerName & ')'}                         !      = '.\sqlexpress'
      else
        ?ole{'connect(' & ServerName &',' & LoginName & ',' & LoginPwd &')' }

      end
      HowMany =  ?ole{'Databases.count'}
      loop  ii= 1 to HowMany
            dbq.ix = ii
            dbq.dbName = ?ole{'databases(' & ii & ').name' }
            dbq.SizeMB = ?ole{'databases(' & ii & ').size' }
            add(dbq)
      end
      sort(dbq, dbq.dbname)
      display()
    END   ! CASE Event

    CASE FIELD()
    OF ?TrustedLogin
       CASE EVENT()
       of event:Accepted
         if TrustedLogin
           hide(?LoginName)
           hide(?LoginPwd)

         else

           unhide(?LoginName)
           unhide(?LoginPwd)

         end
         display()
       end
    OF ?DoitButton
      CASE event()
      OF EVENT:Accepted
        post((event:User + 111) )
      END
    END   ! Case FIELD
 end  ! ACCEPT
 close(window)
 RETURN


==============  end  JGdbsizer.CLW ===============
download a .zip of the two files  here
 
John Griffiths.

 

      email graphic

 

 

SQLKey.com  (c) 2008