Trigger Problems Part II

Trigger Problems Part II

Post by Patrick P » Fri, 16 Oct 1998 04:00:00



Hello again,

Thanks for the help on my last problem.But now I've got another problem:

I've created the trigger with:
"create trigger onins
on Testtable
for INSERT
as exec master..cmdshell "bcp ISDB..Testtable out c:\temp\test.asc -c -t
; -r ;\n -S -T"

and insered data into the table.When I did that,he began to work and
didn't stop.When I pressed the stop button,isql/w crashed.

Hope,you can help me again.
Thanks

 
 
 

Trigger Problems Part II

Post by Tony Rogerso » Fri, 16 Oct 1998 04:00:00


Patrick,

Because you've executed the bcp inside a transaction i.e. insert into
Testtable and the bcp is selecting from Testtatble, the bcp you've started
get's blocked.

So, basically, you've locked yourself up and because it's xp_cmdshell and
xp_cmdshell isn't usually killable you need to stop and restart SQL Server.

I wouldn't recomend bcping out like this, especially from the table your
inserting into, however, in your example if you still wish to proceed,
create a view and in the from clause add the locking hint (NOLOCK) and
change the bcp to use the view.

--
Hope the above helps.

Tony Rogerson  MCP, SQL Server MVP
Torver Computer Consultants Ltd.

 
 
 

1. How to call a *.exe with a trigger (Part II)

Hi Bob, thanks for your help;

I want to run a VB GUI program that only show to the user a message
containing what is doing the trigger (in my case the trigger changes a date
in a sale order only in certain conditions).

With a trigger, as I have understood, I can not send a Message (like MsgBox
in VB) then my only solution is to create a simple VB msg.exe program that
executes this function.

The program is only a Small Form with a Message and a button OK

So if I call the VB GUI program using the delay.exe, is it possible to
obtain what I need?

i.e.

Sub Main()

Dim strTime As String
strTime = Time()
Do Until Time() > CDate(strTime) + "00:00:10"
DoEvents
Loop

Call (VB Program) myprogram.exe

'MsgBox "done"

End Sub

Thank You

Giovanni

2. Table Design question

3. How to call a *.exe with a trigger in SQL 7.0 Part II

4. pgsql/src/backend/utils/mb/conversion_procs

5. GetChunk problems - part II

6. being miserly with disks

7. Inserting with a TDBGrid Problem - Part II

8. JDBC Servlet problem under FastTrack (part II)

9. Complex import/lookup problem (part II)

10. Need Help on SQL Server 6.5 - Part II

11. EXCEPTION_ACCESS_VIOLATION Part II

12. Advanced MDX Question: Filter on Member Property Part II