#INCLUDE DEFINES.H

if type('application') = 'O'
        application.visible = .F.
endif
STORE PARAMETERS() TO lnParams
STORE .F. TO llError, lRelease
ON ERROR llError = .T.
IF TYPE('oSQLSrvr') <> 'O'
        osqlsrvr = createobject('sqldmo.sqlserver')
        IF TYPE('oSQLSrvr') <> 'O'
                WAIT WINDOW "Unable to create SQL Server OLE object." TIME 2.0
                RELEASE osqlSrvr
                ON ERROR
                if type('application') = 'O'
                        application.visible = .t. and .f.
                endif
                RETURN
        ENDIF
        WAIT WINDOW NOWAIT "Connecting to SQL Server..."
        ret =osqlsrvr.connect(_SQL_SERVER, _SQL_LOGIN, _SQL_PASSWORD)
        IF llError
                WAIT WINDOW MESS() TIME 2.0
                RELEASE osqlSrvr
                ON ERROR
                if type('application') = 'O'
                        application.visible = .t. and .f.
                endif
                RETURN
        ENDIF
        lRelease = .T.
ENDIF
oDb = osqlsrvr.databases('pubs')
IF TYPE('oDb') <> 'O' OR llError
        WAIT WINDOW "Unable to create SQL Server Database object." TIME 2.0
        RELEASE osqlSrvr, oDb
        ON ERROR
                if type('application') = 'O'
                        application.visible = .t. and .f.
                endif
        RETURN
ENDIF
cScrptName = 'c:\temp\Scripts.SQL'
h = FCREATE(cScrptName)
cAppName = 'OSITraffic'
eH = FCREATE(_LOG_PATH + "\" + cAppName + ' Sprocs Error.log')
ah = FCREATE(_LOG_PATH + "\" + cAppName + ' Sprocs Audit.log')
= FPUTS(h, "/******************** Script Started: " + TTOC(DATETIME()) + "********************/")
= FPUTS(h, "SET NOCOUNT ON")
oQryResults = oDb.ExecuteWithResults("select p.name from sysobjects p " + ;
                                "where objectproperty(id, 'IsProcedure') = 1 " + ;
                                "and exists(select * from syscomments c where c.id = p.id " + ;
                                "and encrypted = 1)")
NOTE USE (IIF(TYPE('cDbpath') <> 'C', _SQL_PATH, cDbPath) + "\" + cAppName + "_SPROCS")
lcCount = LTRIM(STR(oQryResults.Rows))
SET MESSAGE TO 'Generating...'
FOR lnRecCount = 1 To oQryResults.Rows
        cSpName = "dbo." + ALLTRIM(oQryResults.GetColumnString(lnRecCount,1))
        WAIT WINDOW NOWAIT "Generating for " + cSpName + "..." + ;
                                ALLT(STR(lnRecCount)) + "/" + lcCount
        = FPUTS(h, 'PRINT "Creating ' + ALLTRIM(Name) + '..."')
        = FPUTS(h, 'GO')
        = FPUTS(h, '')
        = FPUTS(h, oDb.StoredProcedures(cSpName).Script(_FULLSP_SCRIPT))
        IF llError
                llError = .F.
                = FPUTS(eH, cSpName + ":" + MESS())
                = FFLUSH(eH)
        ELSE
                = FPUTS(ah, cSpName)
                = FFLUSH(ah)
        ENDIF
        = FFLUSH(h)
ENDFOR
=FCLOSE(aH)
= FPUTS(h, "/******************** Script Ended: " + TTOC(DATETIME()) + "********************/")
= FCLOSE(h)
USE
IF FSEEK(eh, 0, 2) > 0
        = FCLOSE(eh)
        cLogFile = _LOG_PATH + "\" + cAppName + ' Tables Error.log'
        MODI FILE &cLogFile NOWAIT
ELSE
        = FCLOSE(eh)
ENDIF
SET MESSAGE TO
MODI FILE &cScrptName NOWAIT
if type('application') = 'O'
                application.visible = .t. and .f.
endif
IF lRelease
        WAIT WINDOW NOWAIT "Releasing Resources..."
        oSqlSrvr.Disconnect
        osqlsrvr.Close
        RELEASE ALL
        WAIT CLEAR
        ON ERROR
        RETURN
ENDIF

This page was last updated on May 01, 2006 04:28 PM.