Re: getting data in triggers

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Scott Elgram (SElgram_at_verifpoint.com)
Date: 03/05/04 

Date: Fri, 5 Mar 2004 10:19:58 -0800

OJ,
    Thanks for taking a look and for your feed back.
    After seeing my script you have no doubt realized I am a novice (at
best) with VBScript. In fact I have only just started because of this
little project of mine. Anyway, while researching error handling in
VBScript I ran into a little tidbit that I tried with my trigger. Still
with no error handling I ran "exec master..xp_CMDShell 'CScript C:\DB-2.vbs
PDB'" instead of "exec master..xp_CMDShell 'C:\DB-2.vbs PDB'". It turns out
that when using CScript as the scripting host it will return the errors in
the command line which are then sent back to the Query Analyzer and
terminates the script. So now I can see the error for which it seemed to be
hanging on.
C:\DB-2.vbs(17, 1) Microsoft OLE DB Provider for ODBC Drivers:
[Microsoft][ODBC SQL Server Driver]Timeout expired
I'm not sure how I can increase the Timeout or even if it will help but I
know why it seemed to hang.

thanks, 

-- 
-Scott Elgram
"oj" <nospam_ojngo@home.com> wrote in message
news:%233D0VmkAEHA.2804@tk2msftngp13.phx.gbl...
> Scott,
>
> Thanks for the vbscript.
>
> Here are a few observations:
> 1. You do not have error-handling in your vbscript. If an error occurs you
> will not see it via xp_cmdshell hence the process will show up in Taskmgr
> but thing will appear as hang.
> 2. You will need NT admin to add/update NT user info, especially in ADSI.
> 3. Check your sqlserver proxy account. It hasn't the correct access to
> manipulate AD.
> http://support.microsoft.com/?id=264155
> 4. Also, check this kb out.
> http://support.microsoft.com/?id=298725
>
>
> -- 
> -oj
> http://www.rac4sql.net
>
>
> "Scott Elgram" <SElgram@verifpoint.com> wrote in message
> news:uY31xcgAEHA.916@tk2msftngp13.phx.gbl...
> > OJ,
> >     Here is a copy of both the script and the trigger.
> > --------Begin DB-2.VBS-----------
> > Const ADS_SECURE_AUTHENTICATION = 1
> >
> > dim UserExist, strPlan
> >
> > UserExist = "False"
> > strPlan = Wscript.Arguments(0)
> >
> > Set db = CreateObject("ADODB.Connection")
> > Set rs = CreateObject("ADODB.Recordset")
> >
> > db.Open "Driver={SQL Server}; Server=192.168.0.200; Database=Global;
> > UID=User; PWD=1234"
> >
> > strsql = "SELECT Abbrev, PWD, [Name] FROM [Test] where Abbrev='"
&strPlan
> > &"'"
> > rs.Open strsql, db
> >
> > strComputer = "IS02"
> >
> > Set oDSO = GetObject("WinNT:")
> > Set oComputer = oDSO.OpenDSObject("WinNT://" & strComputer,
> "Administrator",
> > "1234", ADS_SECURE_AUTHENTICATION)
> >
> > Set objDomain = GetObject("WinNT://" & strComputer)
> > objDomain.Filter = Array("user")
> >  For Each User In objDomain
> >          If lcase(User.Name) = lcase(rs("Abbrev")) Then
> >               'WScript.Echo User.Name & " already exists."
> >    Set objUser = GetObject("WinNT://" & strComputer & "/" &rs("Abbrev"))
> >    objUser.SetPassword "1234"
> >    objUser.SetInfo
> >    UserExist = "True"
> >    Exit For
> >   End If
> >  Next
> >
> > If UserExist = "False" then
> >  Set objUser = oComputer.Create("user", rs("Abbrev"))
> >  objUser.SetPassword rs("PWD")
> >  objUser.Put "FullName", "" &rs("Name") &""
> >  ObjUser.Put "Description", "Client access account"
> >  objUser.SetInfo
> >
> >  Set objGroup = GetObject("WinNT://" & strComputer & "/Users,group")
> >  Set objUser = GetObject("WinNT://" & strComputer & "/" &rs("Abbrev")
> > &",user")
> >  objGroup.Add(objUser.ADsPath)
> >  objUser.SetInfo
> > End If
> >
> > rs.Close
> > db.Close
> > --------Begin DB-2.VBS-----------
> >
> > --------Begin Trigger---------------
> > CREATE TRIGGER [tr_Test] ON [dbo].[Test]
> > FOR UPDATE, INSERT
> > AS
> > DECLARE  @Plan sysname,@Cmd nvarchar(50)
> > IF (UPDATE(pwd)) or (UPDATE(Abbrev))
> >  BEGIN
> >   SELECT @Plan = Abbrev FROM INSERTED
> >   SELECT @Cmd = 'C:\DB-2.vbs ' +@Plan
> >
> >   exec master..xp_CMDShell @Cmd
> >  END
> > --------End Trigger----------------
> >
> > I can execute the script using xp_CmdShell in the SQL Query Analyzer
with
> > out any problems.  When I run a query to update the table with the
trigger
> > and I view the processes in SQL I can see both the UPDATE query I ran
and
> > the SELECT query that was run by the VBScript.  For a few seconds the
> UPDATE
> > query blocks the SELECT query but then, as far as SQL shows, the block
> goes
> > away.  After that the two processes just sit there never to finish.
> >
> > -- 
> > -Scott Elgram
> >
> > "oj" <nospam_ojngo@home.com> wrote in message
> > news:ekCgskZAEHA.712@tk2msftngp13.phx.gbl...
> > > Scott,
> > >
> > > Without a repro script it's hard to determine what you're
> encountering...
> > >
> > > Btw, can you execute the vbscript with 'xp_cmdshell' (outside of the
> > trigger).
> > > If so, your block is probably due to lack of permission while the
> vbscript
> > is
> > > executed within the trigger. Take a look at the following article. Pay
> > attention
> > > to the Remarks section.
> > >
> > > http://msdn.microsoft.com/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
> > >
> > > -- 
> > > -oj
> > > http://www.rac4sql.net
> > >
> > >
> > > "Scott Elgram" <SElgram@verifpoint.com> wrote in message
> > > news:eJrvGKYAEHA.2036@TK2MSFTNGP12.phx.gbl...
> > > > OJ,
> > > >     Thanks again for all your help......I have everything up and
> running
> > the
> > > > way it should be except for one major detail.  I am still having
major
> > > > blocking problems between the trigger and the VBscript the trigger
> > executes.
> > > > I took a look through all those links you posted on the other thread
> but
> > > > have not been able to find a possible resolution to the block.  In
> fact,
> > > > After examining the situation I noticed that the block only occurs
for
> a
> > > > short time and then disappears.  Even though the block is not
showing
> in
> > SQL
> > > > anymore both the process for the update that executed the trigger
and
> > the
> > > > VBScript executed by the trigger are still shown in Process Info.
> They
> > seem
> > > > to be hung for whatever reason and I can't seem to find out why.  I
> have
> > > > gone over both the trigger and the VBScript several times and both
> seem
> > to
> > > > work fine separately.  At this point it's looking like my only
option
> my
> > > > indeed be to scheduled the trigger although I was really hoping to
do
> it
> > > > without.
> > > >
> > > > -- 
> > > > -Scott Elgram
> > > >
> > > > "oj" <nospam_ojngo@home.com> wrote in message
> > > > news:%239RzH6VAEHA.1468@tk2msftngp13.phx.gbl...
> > > > > You can use Update(column) to check. Then just assign the value to
a
> > > > > variable and pass it to your vbscript.
> > > > >
> > > > >
http://msdn.microsoft.com/library/en-us/tsqlref/ts_create2_7eeq.asp
> > > > >
> > > > > e.g.
> > > > > declare @v sysname,@sql nvarchar(1000)
> > > > > if update(col1)
> > > > > begin
> > > > >  select @v =  col1 from inserted
> > > > >  select @sql = 'myvbscript '+@v
> > > > > exec master..xp_cmdshell @sql
> > > > > end
> > > > >
> > > > > -- 
> > > > > -oj
> > > > > http://www.rac4sql.net
> > > > >
> > > > >
> > > > > "Scott Elgram" <SElgram@verifpoint.com> wrote in message
> > > > > news:uxMRdyVAEHA.752@TK2MSFTNGP09.phx.gbl...
> > > > > >     Is there a way, in SQL 7, to grab data from a specific field
> in
> > the
> > > > > > updated record with the same trigger that was triggers by an
> update
> > to
> > > > > that
> > > > > > record.  When this table is updated it is by hand so I don't
need
> to
> > > > worry
> > > > > > about multiple records being updated at once but I do need to
pass
> > some
> > > > > data
> > > > > > from the record that was updated to a VBScript the trigger is
> > executing.
> > > > > >
> > > > > > -- 
> > > > > > -Scott Elgram
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >
>
>

Relevant Pages

  • Re: LDAP query information
    ... execution of the vbscript? ... The error message indicates the line number in the script, ... Dim strBase, strFilter, strAttributes, strQuery, adoRecordset ... ' Construct LDAP syntax query. ...
    (microsoft.public.windows.server.scripting)
  • LongestRunningQueries.vbs - Using a VB Script to show long-running queries, complete with qu
    ... Try this script to see what queries are taking over a second. ... you need a long-running query. ... Clicking on SQL opens the full SQL batch as a .txt file, ... filename = temp & filename ...
    (comp.databases.ms-sqlserver)
  • Re: getting data in triggers
    ... transaction will hold everything in lock until your trigger is done. ... Looking at your vbscript, you are making a new sqlconnection and attempting ... suggest you create a sql job and schedule it to run these external calls at ... >>> I can execute the script using xp_CmdShell in the SQL Query Analyzer ...
    (microsoft.public.sqlserver.programming)
  • Re: LDAP query information
    ... Yes, the script uses ADO to query AD directly, which is very efficient - no ... I ran the script, it executed succesfully in cmd prompt. ... the cmds which have to be added to the VBScript to redirect the output. ... Dim strBase, strFilter, strAttributes, strQuery, adoRecordset ...
    (microsoft.public.windows.server.scripting)
  • Re: SQL Insert help
    ... Where, exactly, are you placing your query syntax? ... example) where it could by grabbed by the query script? ... Execute SQL script function simply uses the global field as its query. ...
    (comp.databases.filemaker)