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
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.


Trigger Problems Part II

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


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?


Sub Main()

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

Call (VB Program) myprogram.exe

'MsgBox "done"

End Sub

Thank You


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


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