May 16, 2012 at 12:31 pm
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
May 16, 2012 at 1:46 pm
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
May 16, 2012 at 1:51 pm
I could be done in powershell? i dosent matter if you know how to do it in powershell is ok aldo.
May 16, 2012 at 2:29 pm
May 16, 2012 at 2:36 pm
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]
May 17, 2012 at 7:10 am
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
May 17, 2012 at 8:51 am
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]
May 17, 2012 at 9:24 am
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: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.
May 17, 2012 at 9:47 am
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
May 18, 2012 at 9:00 am
Whit the vbscripts im getting this error :
May 18, 2012 at 9:11 am
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: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