Vbscript SQL and fOREFRONT TMG Help !!

  • Hello, I’m having problems when debugging the database as it grows and grows TMG and the idea is to use a script to delete old records. I’m ussing remote SQL logging. I make this two script but seems not to work I have an ODBC connection. Could you please help me?

    ——————————————————————————————-

    Const adOpenStatic = 3

    Const adLockOptimistic = 3

    Const adUseClient = 3

    Set objConnection = CreateObject(“ADODB.Connection”)

    objConnection.ConnectionTimeout=1200

    objConnection.Open “DSN=BaseTMGF;UID=xxx;PWD=xxxxxxxxx”

    objConnection.EXECUTE “delete FROM dptmgf.dbo.FirewallLog WHERE logtime <= getdate()-60 "

    objConnection.Close

    ——————————————————————————————-

    Const adOpenStatic = 3

    Const adLockOptimistic = 3

    Const adUseClient = 3

    Set objConnection = CreateObject("ADODB.Connection")

    Set objRecordset = CreateObject("ADODB.Recordset")

    objConnection.ConnectionTimeout=1200

    objConnection.Open "DSN=BaseTMGW;UID=xxx;PWD=xxxxxxxx"

    objConnection.EXECUTE "delete FROM dptmgw.dbo.WebProxyLog WHERE logtime <= getdate()-60 "

    objConnection.Close

  • That looks like VBScript. You posted in a PowerShell Forum. You may have better luck looking online for a VBScript Forum, I do not know of one here on SSC.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I could be done in powershell? i dosent matter if you know how to do it in powershell is ok aldo.

  • Sure, it could be done using PowerShell. Here is a link to get you started:

    Executing SQL the Right Way in PowerShell[/url]

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • It's OK, I'm a VB forum... 😀

    Which one doesn't work, or is it both?

    And what error if any do you get?

    And how are you executing this? (what facility are you using to host and execute the VBscript?)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Both Script dont work, mi setup is :i have two odbc conections in a windows 2008R2 64 bits which is where i have forefront TMG installed i send the TMG logs to a remote SQL 2005 server , i create like i say two odbc connections just to run the vbscripts “DSN=BaseTMGW and “DSN=BaseTMGF one is for te Firewall database and the otherone is for the Web database, for that reason i have two scripts , the TMG i sending the logs ok , but the databases are geting biger and bigger and y need that two scripts to schedule a task that can delete logs older than: “delete FROM WebProxyLog WHERE logtime <= getdate()-60" and the error im getting is:

    (4,34) Microsoft Vbscript compilation error Invalid caracter

    Thanks in advance for you help

    -------------------------------------------------------------------------------------------

    Const adOpenStatic = 3

    Const adLockOptimistic = 3

    Const adUseClient = 3

    Set objConnection = CreateObject(“ADODB.Connection”)

    objConnection.ConnectionTimeout=1200

    objConnection.Open “DSN=BaseTMGW;UID=PTMGUSR;PWD=K/00RdWjFK0Dg8MFF4MEX7”

    objConnection.EXECUTE “delete FROM WebProxyLog WHERE logtime <= getdate()-60"

    objConnection.Close

    --------------------------------------------------------------------------------------------

    Const adOpenStatic = 3

    Const adLockOptimistic = 3

    Const adUseClient = 3

    Set objConnection = CreateObject(“ADODB.Connection”)

    objConnection.ConnectionTimeout=1200

    objConnection.Open “DSN=BaseTMGF;UID=PTMGUSR;PWD=K/00RdWjFK0Dg8MFF4MEX7”

    objConnection.EXECUTE “delete FROM Firewall WHERE logtime <= getdate()-60"

    objConnection.Close

  • Ah well, "(4,34) Microsoft Vbscript compilation error Invalid c(h)aracter" is a completely different problem, it has nothing to do with SQL Server or ODBC.

    In the code that you posted:

    -------------------------------------------------------------------------------------------

    Const adOpenStatic = 3

    Const adLockOptimistic = 3

    Const adUseClient = 3

    Set objConnection = CreateObject(“ADODB.Connection”)

    objConnection.ConnectionTimeout=1200

    objConnection.Open “DSN=BaseTMGW;UID=PTMGUSR;PWD=K/00RdWjFK0Dg8MFF4MEX7”

    objConnection.EXECUTE “delete FROM WebProxyLog WHERE logtime <= getdate()-60"

    objConnection.Close

    --------------------------------------------------------------------------------------------

    Const adOpenStatic = 3

    Const adLockOptimistic = 3

    Const adUseClient = 3

    Set objConnection = CreateObject(“ADODB.Connection”)

    objConnection.ConnectionTimeout=1200

    objConnection.Open “DSN=BaseTMGF;UID=PTMGUSR;PWD=K/00RdWjFK0Dg8MFF4MEX7”

    objConnection.EXECUTE “delete FROM Firewall WHERE logtime <= getdate()-60"

    objConnection.Close

    Are the dashes ("----...") actually in there? If so, then try putting an apostrophe (" ' ") in front of them:

    '-------------------------------------------------------------------------------------------'

    Const adOpenStatic = 3

    Const adLockOptimistic = 3

    Const adUseClient = 3

    Set objConnection = CreateObject(“ADODB.Connection”)

    objConnection.ConnectionTimeout=1200

    objConnection.Open “DSN=BaseTMGW;UID=PTMGUSR;PWD=K/00RdWjFK0Dg8MFF4MEX7”

    objConnection.EXECUTE “delete FROM WebProxyLog WHERE logtime <= getdate()-60"

    objConnection.Close

    '--------------------------------------------------------------------------------------------'

    Const adOpenStatic = 3

    Const adLockOptimistic = 3

    Const adUseClient = 3

    Set objConnection = CreateObject(“ADODB.Connection”)

    objConnection.ConnectionTimeout=1200

    objConnection.Open “DSN=BaseTMGF;UID=PTMGUSR;PWD=K/00RdWjFK0Dg8MFF4MEX7”

    objConnection.EXECUTE “delete FROM Firewall WHERE logtime <= getdate()-60"

    objConnection.Close

    Also, I notice that you've got some stylized quotes in there, like (“...”) instead of ("..."). These stylized quotes are invalid in almost every programming language. You should carefully remove each one by hand and replace it with an actual Quotation Mark character.

    These stylized quotes usually come from trying to use a word processor (like MS Word, or Wordpad) to edit code, instead of a code editor or Notepad, so make sure that you don't repeat this problem (just use Notepad if you don't have a code editor).

    Alternatively, you can cut and paste my corrected version, below:

    '-------------------------------------------------------------------------------------------'

    Const adOpenStatic = 3

    Const adLockOptimistic = 3

    Const adUseClient = 3

    Set objConnection = CreateObject("ADODB.Connection")

    objConnection.ConnectionTimeout=1200

    objConnection.Open "DSN=BaseTMGW;UID=PTMGUSR;PWD=K/00RdWjFK0Dg8MFF4MEX7"

    objConnection.EXECUTE "delete FROM WebProxyLog WHERE logtime <= getdate()-60"

    objConnection.Close

    '--------------------------------------------------------------------------------------------'

    Const adOpenStatic = 3

    Const adLockOptimistic = 3

    Const adUseClient = 3

    Set objConnection = CreateObject("ADODB.Connection")

    objConnection.ConnectionTimeout=1200

    objConnection.Open "DSN=BaseTMGF;UID=PTMGUSR;PWD=K/00RdWjFK0Dg8MFF4MEX7"

    objConnection.EXECUTE "delete FROM Firewall WHERE logtime <= getdate()-60"

    objConnection.Close

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I'm OK with VBScript and I know what your script is trying to do. What's the error you're receiving, please?

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • Seriously...get this person some PowerShell 😎

    Add-Type -AssemblyName “Microsoft.SqlServer.Smo”

    ##################################################################

    # initialize variables

    $instanceName = "myserver\myinstancename"

    $databaseName = "mydatabase"

    $login = "testlogin" # leave blank to use Windows Authentication

    $password = "password"

    $sql = "delete FROM WebProxyLog WHERE logtime <= getdate()-60"

    $statementTimeout = 600 # in seconds; set to 0 to allow statement to execute indefinitely

    ##################################################################

    # do work (nothing below here needs to change)

    $instance = New-Object('Microsoft.SqlServer.Management.Smo.Server') $instanceName

    if($login)

    {

    $instance.ConnectionContext.LoginSecure = False

    $instance.ConnectionContext.Login = $login

    $instance.ConnectionContext.Password = $password

    }

    $instance.ConnectionContext.StatementTimeout = $statementTimeout

    $database = $instance.Databases.Item($databaseName)

    $database.ExecuteNonQuery($sql)

    EDIT: updated to support statement timeout

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Whit the vbscripts im getting this error :

  • Looks like the error about Firewall being an invalid object is because the script is connecting to SQL Server as a user with the wrong default database.

    You need to either:

    1) Modify the connection string to set the default database to your target database (Initial Catalog=dbname)

    or

    2) Modify the properties of the application login in SSMS to have the correct database as default.

    Also make sure you explicitly specify the schema in your inline SQL, so dbo.Firewall (or whatever your schema is), especially important when using multiple schemas.

    Post back if it works.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply