SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Vbscript SQL and fOREFRONT TMG Help !!


Vbscript SQL and fOREFRONT TMG Help !!

Author
Message
shayro.mendez
shayro.mendez
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15027 Visits: 14396
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
shayro.mendez
shayro.mendez
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15027 Visits: 14396
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
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14886 Visits: 9518
It's OK, I'm a VB forum... :-D

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."
shayro.mendez
shayro.mendez
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14886 Visits: 9518
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."
derek.colley
derek.colley
SSC Eights!
SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)

Group: General Forum Members
Points: 880 Visits: 603
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 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.

Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15027 Visits: 14396
Seriously...get this person some PowerShell Cool

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
shayro.mendez
shayro.mendez
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 10
Whit the vbscripts im getting this error :


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search