Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Vbscript SQL and fOREFRONT TMG Help !! Expand / Collapse
Author
Message
Posted Wednesday, May 16, 2012 12:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 18, 2012 11:34 AM
Points: 4, Visits: 10
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
Post #1301283
Posted Wednesday, May 16, 2012 1:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1301330
Posted Wednesday, May 16, 2012 1:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 18, 2012 11:34 AM
Points: 4, Visits: 10
I could be done in powershell? i dosent matter if you know how to do it in powershell is ok aldo.
Post #1301333
Posted Wednesday, May 16, 2012 2:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
Sure, it could be done using PowerShell. Here is a link to get you started:

Executing SQL the Right Way in PowerShell


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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1301358
Posted Wednesday, May 16, 2012 2:36 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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?)


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1301360
Posted Thursday, May 17, 2012 7:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 18, 2012 11:34 AM
Points: 4, Visits: 10
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
Post #1301733
Posted Thursday, May 17, 2012 8:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1301830
Posted Thursday, May 17, 2012 9:24 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1301869
Posted Thursday, May 17, 2012 9:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1301884
Posted Friday, May 18, 2012 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 18, 2012 11:34 AM
Points: 4, Visits: 10
Whit the vbscripts im getting this error :



Post #1302598
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse