Re: getting data in triggers

From: oj (nospam_ojngo_at_home.com)
Date: 03/05/04 

Date: Fri, 5 Mar 2004 10:57:46 -0800

Scott,

Here are some reads:
ConnectionTimeout:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdproconstringx.asp?frame=true

CommandTimeout:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdproactiveconx.asp?frame=true

Btw, what you're doing is quite *dangerous*. This is because your
transaction will hold everything in lock until your trigger is done.

Looking at your vbscript, you are making a new sqlconnection and attempting
to modify ADSI user's properties. All these external calls/services could
put your current transaction in an undersired state (appear as hang). I
suggest you create a sql job and schedule it to run these external calls at
your desired time. 

-- 
-oj
http://www.rac4sql.net
"Scott Elgram" <SElgram@verifpoint.com> wrote in message
news:e89E55tAEHA.2720@TK2MSFTNGP11.phx.gbl...
> 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: getting data in triggers
    ... You do not have error-handling in your vbscript. ... > 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. ...
    (microsoft.public.sqlserver.programming)
  • Re: getting data in triggers
    ... After seeing my script you have no doubt realized I am a novice (at ... VBScript I ran into a little tidbit that I tried with my trigger. ... >> I can execute the script using xp_CmdShell in the SQL Query Analyzer ...
    (microsoft.public.sqlserver.programming)
  • Re: MS Query capabilities/regex
    ... Not so bad, but at home I'm a Mac user, and VBScript seems to be dead-ended... ... MS Query uses Access SQL which is similar to T-SQL - you can use expressions with wildcards % and ?, but not regex AFAIK. ...
    (microsoft.public.excel.newusers)
  • Re: Trigger oddity
    ... So I figured a query within the trigger is running slowly. ... I change NOTHING and then execute the query to rebuild the trigger. ... SQL code but the create query is run in VB.NET app. ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger oddity
    ... Once the trigger is rebuilt using SSMS, the update query which was slow before is now fast and it runs fast within SSMS OR from the vb.net app. ... I take great care in building indexes and makeing sure the trigger sql is as fast and simple as possible. ...
    (microsoft.public.sqlserver.programming)
Loading