BCP within a Trigger Freezes

Hey all,

Has anybody run into an issue where a call to BCP within a trigger freezes Query Analyzer? When I try to stop the execution of the update statement, the program then crashes.

I have no problem running BCP from within Query Analyzer itself.

Here is the code I am using
----
CREATE TRIGGER tr_ack ON [dbo].[Track_tb]
FOR UPDATE
AS
if update(AckStatus)
insert into ins_hold
select e.dc + e.po, e.emp,
case AckStatus
when 2 then 'Acknowledged'
when 6 then 'Overdue'
end
from eack as e, document_tb as d, inserted
where (inserted.DocumentKey = d.DocumentKey) and
(d.DocumentName = e.DocNum)

exec master..xp_cmdshell 'bcp ins_hold out c:\temp\testfile.txt -c -T -STestServer'

drop table ins_hold
------

I have also tried this with global temp tables rather than a permanent one, doing a select query and using queryout from BCP and receive the same type of crash.

I can do a select * from ins_hold within the trigger and the results display correctly. I can do bcp out from within Query Analyzer using the data from the ins_hold table within the trigger and it works correctly.

The update statement I'm using within Query Analyzer to test is:
----
update Track_tb
Set AckStatus = 2
where DocumentKey = (
select DocumentKey
from Document_tb
where DocumentName = '0018830')
---

Thanks for any help you can provide, I'm greatly appreciative.

-Gregwhen issuing the command:

exec master..xp_cmdshell 'bcp ins_hold out c:\temp\testfile.txt -c -T -STestServer'

you need to use a three part naming convention for the table <DB NAME>.<OWNER NAME>.<TABLE NAME>.

What version of SQL Server are you using?|||Using SQL 2000 for this (sorry I didn't include that originally)

I did try doing what you suggested before and ran into the same problem.

Thanks for the reply.|||Okay, go back to using a three part name, BCP will not work otherwise.

xp_cmdshell runs under the SQL Server Agent account. HAve you created that account on your SQL server?

I just tried issueing a BCP command via xp_cmdshell (Cut & past of your code) and everything worked.

have you tried xp_cmdshell'dir c:\temp\*.*'? I tried it on my server and get a nasty access denied message.

This might be a bugger to track down so hang in there!|||Hey Paul,

Went back to the 3 part naming convention. The Server Agent account is set up on the system. I also tried doing the xp_cmdshell 'dir c:\temp' from within the trigger and it returned the results without any hitches.

When you say you tried the BCP command of my code, do you mean that you tried that within a trigger or just from Query Analyzer? It seems to work fine for me within Query Analyzer, just gets hitched up when it tries to run from within a trigger.

I'm going to run it again today and put all the traces on to see if I can come up with something in there. I tried this before, but didn't see anything indicative of why it may be freezing up.

For the time being, I've been using a simple VBScript program on a 2 hour schedule to accomplish the same task, but I'd much rather have it running within the trigger so the results are instantaneous rather than having a potential 2 hour wait.

Thanks again for the help,|||Okay, did some more testing.

I also tried to do an out from an ISQL command.

It worked fine from Query Analyzer, but when I tried to have it fire from within the trigger, it again hung and eventually froze.

That command was:
----
declare @.str
set @.str = 'isql -Q"select * from ins_hold" -E -oc:\temp\testfile.txt'
exec master..xp_cmdshell @.str
----

So I was wondering if perhaps it isn't configured for write access through sa, so I then did a:
----
exec master..xp_cmdshell 'dir c:\temp > c:\temp\testfile.txt'
----
within the trigger and it executed correctly and didn't freeze.

Doing more research for the time being to see if I can find anything on the 'net related to this (haven't had any luck so far).

Thanks,

Greg|||No, I did not try the BCP via a trigger. I do think you hit apon the problem though.

What happens if you add th "-o" parameter to your BCP command? My WAG is that if any results are returned form xp_cmdshell the trigger will hang. The "-o" will redirect output from BCP to a file eliminating any data returned from xp_cmdshell. Worth a shot!|||Okay, tried using the -o option to see if I can get something returned from BCP, but nothing gets generated (in either the output or log file). When I run from within Query Analyzer, it posts the return text, so it's again limited to being an issue with the Trigger itself.

So back to the drawing board (or Internet if you prefer).

Regardless of whether I get this worked out or not, thanks Paul--you've been a tremendous help thus far.

-Greg|||Okay, I couldn't find anything that said you couldn't do this so I tried setting up a table and trigger to simulate the problem...

Code:
------------------------------------------
exec master..xp_cmdshell 'dir f:\MSSQL2k'
go
create table TestTable(f1 int)
go
create trigger Tigger on dbo.TestTable
for INSERT, UPDATE, DELETE
AS
exec master..xp_cmdshell 'bcp dba.dbo.ProcedureUsage out f:\MSSQL2k\temp.txt -c -T -S hgw2db17 -o f:\MSSQL2k\temp.out', no_output
go
insert into TestTable values(1)
go
exec master..xp_cmdshell 'dir f:\MSSQL2k'
go
------------------------------------------

Results:
------------------------------------------
output
----------------------------
Volume in drive F is New Volume
Volume Serial Number is 183C-AD6B
NULL
Directory of f:\MSSQL2k
NULL
08/21/2002 11:54a <DIR> .
08/21/2002 11:54a <DIR> ..
02/22/2002 03:23p <DIR> MSSQL
08/21/2002 12:02p 108 temp.out
08/21/2002 12:02p 4,319 temp.txt
2 File(s) 4,427 bytes
3 Dir(s) 12,744,605,696 bytes free
NULL

output
----------------------------
Volume in drive F is New Volume
Volume Serial Number is 183C-AD6B
NULL
Directory of f:\MSSQL2k
NULL
08/21/2002 11:54a <DIR> .
08/21/2002 11:54a <DIR> ..
02/22/2002 03:23p <DIR> MSSQL
08/21/2002 12:02p 108 temp.out
08/21/2002 12:02p 4,319 temp.txt
2 File(s) 4,427 bytes
3 Dir(s) 12,744,605,696 bytes free
NULL
------------------------------------------
Any chance you could try the above code on your server? 'F:\MSSQL2K' is the "root" for my SQL Server install. Also, I remembered that you can add the "no_output" parameter to xp_cmdshell, maybe that would help.|||Okay, ran through the code which you had there (couple of changes to reflect my setup obviously). Worked perfectly--within the trigger and everything.

So that being said, I did the same thing with the table I had created in the previous trigger and it worked fine as well.

So now I'm wondering where in my old code I'm getting hitched up at since it is not the BCP actually freezing up (though I was able to do selects from within the trigger previously and display the data I was attempting to write out via BCP).

I also tried having two triggers:
One on track_Tb off of updates which inserts the values into ins_hold
One on ins_hold off of inserts which bcp's itself out to a data file.

Sames results with those.

Both processes seem to work correctly independent of one another correctly, but freeze up whenever you attempt to use them together.

Thanks again,

Greg|||Glad to hear you got everything to work. Sometimes the bloody forest blocks the view of the trees! My advice, press on to your final solution!|||I don't know if this TRIGGER is used in production and is being fired by an event within a client application. If it is you may want to rethink executing BCP from within a TRIGGER. The event that fired the TRIGGER will hold locks on the records until the TRIGGER has completed. Which means that locks are held until the BCP has finished. This could and most likely will degrade performance and cause potential deadlocks.|||Originally posted by achorozy
I don't know if this TRIGGER is used in production and is being fired by an event within a client application. If it is you may want to rethink executing BCP from within a TRIGGER. The event that fired the TRIGGER will hold locks on the records until the TRIGGER has completed. Which means that locks are held until the BCP has finished. This could and most likely will degrade performance and cause potential deadlocks.

It is in fact going to be used in a production environment, with the table being updated from a client application. However, the table being updated is Track_tb, and I am inserting data into ins_hold and running BCP off of the ins_hold table. Will this table be locked as well?

Also, when the insert is performed on the ins_hold table from within the trigger, will the trigger hold this lock until the end of the execution of it? If so, that would explain why the system seems to hang up when I try to do a BCP with that data.

Thanks for the advice and help,

Greg|||What about using DTS and a scheduled job to create the text file every 5 minutes or so? You could even go a bit further and use the trigger to record the last time a change was made to the table and the job could check to see if the DTS was even needed!

OR

Set up a DTS package to export the data to a test file. Set up a job to launch the DTS package. Create a trigger on the ins_hold table to launch the job two or so minutes in the future. This will allow you to get in and out of your trigger quickly, keep your text file current, and prevent excessive data io when frequent updates are performed. I would probably add a table to record the last time the job ran so I could guarantee a text file write every ten minutes or so during peek times.|||To answer your question - yes. When an SQL statement (INSERT/UPDATE/DELETE) causes a trigger to fire and that trigger performs a database operation that in turn fires another trigger, etc., etc.. This become one big, long transaction and all locks are held.

You can have the trigger insert data into ins_hold table, but you'll need to create a scheduled job that will peform the BCP on a time interval. You can use DTS as Paul has stated or you can modify the current code that does the BCP into a stored procedure and schedule it with SQL Server Agent. Either way is fine.|||Hey guys,

Thanks for all the help. I hadn't even thought of the locks on the table when I was trying to do this. I already have a VBScript program working which essentially does what I was trying to do, so I will continue to utilize this and tack it on to NT Services.

Thanks again,

Greg|||Wow, Success!

Thanks to your guys help and pointing me in the direction of locking, I got the sucker working perfectly now.

If I just put WITH (NOLOCK) in the query from BCP, it will do a dirty read (which is fine because this table exists only for this trigger) and BCP executes without any problem.

So what it looks like now

--
exec master..xp_cmdshell 'bcp "select * from ins_hold WITH (NOLOCK)" queryout c:\temp\testfile.txt -c -T -STestServer'
--

Works like a charm...

Can't thank you guys enough.

-Gregsql

batching an update, use TRANSACTION?

I have a storedproc that does several UPDATEs to a table, some of which fire
and some of which don't based on various IFs through the proc. There's a
maximum f three, it's not THAT complex.
However there is a trigger on the table that records all UPDATEs into a
separate auditing log. I would like there to be only one trigger fire
regardless if one, two or three of the UPDATEs were called.
Does TRANSACTION do this? I do _not_ want to turn off the triggers in the
proc.
MauryIf I understand you correctly, TRANSACTION will not do it. Can you put in
some logic in your trigger to check whether a previous UPDATE has already
fired the trigger?
Linchi
"Maury Markowitz" wrote:
> I have a storedproc that does several UPDATEs to a table, some of which fire
> and some of which don't based on various IFs through the proc. There's a
> maximum f three, it's not THAT complex.
> However there is a trigger on the table that records all UPDATEs into a
> separate auditing log. I would like there to be only one trigger fire
> regardless if one, two or three of the UPDATEs were called.
> Does TRANSACTION do this? I do _not_ want to turn off the triggers in the
> proc.
> Maury|||"Linchi Shea" wrote:
> If I understand you correctly, TRANSACTION will not do it. Can you put in
> some logic in your trigger to check whether a previous UPDATE has already
> fired the trigger?
I guess, but only with peril.
Maury|||"Linchi Shea" wrote:
> If I understand you correctly, TRANSACTION will not do it. Can you put in
> some logic in your trigger to check whether a previous UPDATE has already
> fired the trigger?
Can I perhaps wrap the individual fields of the update in some sort of
conditional, and thereby combine them into one larger statement? Everything
is already loaded into local vars.
Maury

Batch update of a SQL table

Can anyone help a beginner with some T-SQL which runs as a scheduled stored procedure to update a table with is then accessed via an ASP web application.

I have a table called Loans which contains a calculated column which will indicate in days if a loan item is late and also each row has a charges column to reflect a charge for late returns.

In a seperate table I have a charge per day for late returns. I read this into a variable @.LateCharges

I'd like to consutruct some T-SQL to scan through the Loans table and for every row where Status is not 'Returned' I woule like it to update the charges column based on the DaysLatecolumn*@.Latecharges

Any help much appreciated.

Regards

Clive

UPDATE Loans
SET CHARGES = CHARGES + @.LateCharges
WHERE Status <> 'Returned'

|||

Hi

That would almost do it I think - however it would need to read the value of DaysLate in each case too - can I just use that name in my set statement and it woudl automatically be the one applicable to the current row?

ie:

UPDATE Loans
SET CHARGES = DaysLate * @.LateCharges
WHERE Status <> 'Returned'

|||

It sounds like you need to do a join. rather than read just one value into a variable. Please post the table definitions.

batch update in stored procedure

The following code is a part of my stored procedure MySP. I would like to update Users table with input variable @.userIDs which has the following format:

101, 102, 103

I got the error message:

Syntax error converting the varchar value '101, 102, 103' to a column of data type int.

Obviously, UserID has datatype int. How can I write this SP?

CREATE PROCEDURE dbo.MySP
@.userIDs varchar(100)
AS
SET NOCOUNT ON

BEGIN TRANSACTION

UPDATE Users SET IsActive = 1 WHERE UserID IN (@.userIDs)

IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

SET NOCOUNT OFFOriginally posted by gyuan
Obviously, UserID has datatype int.

Obviousley it doesn't...

Check this out:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string

Use the UDF that's posted in there|||You could use dynamic query - sp_executesql or execute it.|||Originally posted by Brett Kaiser
Use the UDF that's posted in there

Brett,

I read the posters from the link you posted but I'm not very clear how to use UDF on my stored procedure since UDF is new to me. Can you give me some hint? UserID is the primary key in the table Users and its datatype is int. Thanks.|||Cut and paste the following code in to QA and run it...

USE Northwind
GO

CREATE FUNCTION CSVTable(@.Str varchar(7000))
RETURNS @.t table (numberval int, stringval varchar(100), DateVal datetime)
AS
BEGIN
DECLARE @.i int, @.c varchar(100);
SELECT @.Str = @.Str + ',', @.i = 1, @.c = '';
WHILE @.i <= LEN(@.Str)
BEGIN
IF substring(@.Str,@.i,1) = ','
BEGIN
INSERT INTO @.t(numberval, stringval, DateVal)
VALUES ( CASE WHEN ISNUMERIC(@.c)=1 THEN @.c ELSE Null END
, RTRIM(LTRIM(@.c))
, CASE WHEN ISDATE(@.c)=1 THEN @.c ELSE Null END)
SET @.c = ''
END
ELSE

SET @.c = @.c + substring(@.Str,@.i,1)
SET @.i = @.i +1
END
RETURN
END
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
GO

INSERT INTO myTable99(Col2)
SELECT 100 UNION ALL SELECT 101 UNION ALL SELECT 102 UNION SELECT 103 UNION ALL
SELECT 104 UNION ALL SELECT 105 UNION ALL SELECT 106 UNION SELECT 107 UNION ALL
SELECT 108 UNION ALL SELECT 109 UNION ALL SELECT 110
GO

DECLARE @.userIDs varchar(100)
SELECT @.userIDs = '101,102,103'

SELECT *
FROM myTable99
WHERE Col2 IN (SELECT StringVal FROM dbo.CSVTable(@.userIDs))
GO

DROP FUNCTION CSVTable
DROP TABLE myTable99
GO

Thank you Dr. Cross Join

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=6859|||I run it and get the result I need. So now do I need to add the function to the stored procedure?|||Just make you're WHERE Clause look like mine...

Just need to make sure the udf is in the same enviroment where you release the procedure...

Get it?|||Yes, it works. That is a great function. Thank you, Brett.

By the way, can you tell me what the difference is between UDF and dynamic SQL? Performance?|||Yeah, go to the link and give thanks to Jeff..

Use dynamic sql as a last resort...and never in application code...

I use it for admin support...but it's not released...

Batch update and trigger

Hi All,
I have a trigger on a table tracking changes to certain fields.
However, when I do a batch update on that field, it looks like the
trigger only gets fired once. However, I do want to track changes on
every record that the update statement touches. So, is there anyway to
make it work or is there any work around? Do I have to create a cursor
and update one record a time?
Thanks a lot,
blueyep, triggers in SQL Server are fired once per statement, not once per
row. when you describe how do you need to track changes, we might be
able to help|||what I want to do is very simple. I want to track the change to a field
in one table. if the value changes, I will insert a row in the change
log table. Basically if old field value <> new field value, insert a
row in the change log.
However, in another script, I sometimes update the field for many rows
if they meet the criteria. Therefore, I have something like UPDATE
table1 SET field1= 'Y' WHERE field2>field3. When this statement is
executed, the trigger only fired once and therefore, only one row gets
inserted into the changelog table.
I just wonder if there is anyway I can make the trigger fired for each
row without abandoning the batch update and use cursor to do update for
each individual row
Thanks a lot.|||if you don't modify the PK, that's easy:
create table seq(s_id int identity, i int)
insert into seq(i) values(1)
insert into seq(i) values(2)
insert into seq(i) values(3)
insert into seq(i) values(4)
go
create table seq_audit(s_id int, old_i int, new_i int)
go
create trigger seq_upd
on seq
for update
as
insert into seq_audit
select inserted.s_id, deleted.i, inserted.i from inserted, deleted
where inserted.s_id = deleted.s_id
go
select * from seq
s_id i
-- --
1 1
2 2
3 3
4 4
go
update seq set i=i+1 where s_id > 1
go
select * from seq_audit
s_id old_i new_i
-- -- --
2 2 3
3 3 4
4 4 5
go
drop table seq
drop table seq_audit

BATCH Update ?

Hi all,

we need to update many single cells with individual MDX Update Statements.
We're doing this with ADOMD now (C# Project) in a loop.
In order to save roundtrips and put things in one transaction
we considered using the <Batch> Element of XMLA.
I could'nt find a example how to use this with MDX Commands.
this doesnt work:
<Batch>
<Command>
<Statement>
UPDATE ..
</Statement>
</Command>
<Command>
<Statement>
UPDATE..
</Statement>
</Command>
</Batch>

The Batch element at line 7, column 22 (namespace urn:schemas-microsoft-com:xml-analysis) cannot appear under Envelope/Body/Execute/Command.

Do you have any hints ?
BTW: what happend to www.xmla.org, its down?
Wher can I find a complete schema file for XMLA?
a lot of questions...
Thanks a lot,
mik

You can update multiple cell values in a single Update statement seperated by commas.

For example:

UPDATE CUBE [Cube1] SET

(USA, Sales) = 100 USE_EQUAL_ALLOCATION,

(Canada, Sales) = 50 USE_EQUAL_ALLOCATION

You can also find some information on XML/A at

http://msdn2.microsoft.com/de-de/library/ms186604.aspx

Batch SQL

Questions

I need to batch a set of update commands. Can that be done and if so what are the possible ramfications? Can one mix / match Deletes/Inserts and Updates into a SQL command via the semi-colon in a batch set? (Is this the appropriate forum?)

WHY

I am unable to use the

dynamic sql building found in the dataset due to a multiple table

contstraints. I have built classes that will extract the differences

from two datasets and report on the updates/deletes and inserts

required. Since I have multiple SQL statements, it would be nice to combine them into one statement.

Platform

.Net 2C#IIS6SQL Server 2000 is the eventual target database for the generated SQL

advTHANKSance

Regarding Transact-SQL it is possible to combine multiple statement into one batch. Separate the statements with semi-colon.

If possible I would look into using stored procedures instead. It will help keeping more logic on the backend and allowing re-usal if you want to build a client for another platform.

Batch Job

Hi,

I want to schedule a daily job using sql server to update the info. in a sql server table. This is very new to me. Could you please forward me some helpful resources.

Thanks,

First, what version of SQL Server? Second, did you install Books Online? If so, look up scheduled jobs.

Jeff

|||It is SQL Server 2000. Can you guys forward me to any website that has the details.|||You can create a job which runs a T-SQL script to do the update, and schedule the job to run.?Use?SQL?Book?Online?if?you?have. Here is a link for this:
Scheduling Jobs

Basic Table update/append question

I'm new to SQL server. I want to add or append a unique set of rows to a destination table from a source table, they are essentially the same table by definition. The source table is updated every hour via DTS, all rows deleted and new set added. Both tables have the same primary key. Approximately 40 unique rows are created each hour and I would think the best approach would be to append the new rows to the destination table. I think an Append query will run into a primary key conflict.

In Access, I did this within VB by checking the max value of the primary key and then running the append for any values greater than that.

In SQL, I'm not sure if this should be done as a stored procedure or if there is an easier approach altogether.In SQL Server (or in Access...) you are better off using a LEFT OUTER JOIN against your destination table, and filtering where the destination table key is null:

select [YourFields]...
from SourceTable
left outer join DestinationTable on SourceTable.PKey = DestinationTable.PKey
where Destination.PKey is null

You could also use NOT EXISTS, but I prefer this method.|||Thanks, that worked really well!

Basic Replication trouble.

I'm trying to run a simple update script on this database which is
created from a replication agent. Several individual databases are
replicated into one consolidated. I've not worked with replication
before and I was hoping someone could tell me what this error means.

UPDATE
CUSTOMER
SET
[TIMESTAMP]='20060920090453'
[IVBTYPE]='M '
WHERE
[ROWID]='9f83bc89-76f8-4140-a0cc-58fa34962638'

yields:

Server: Msg 208, Level 16, State 1, Procedure
upd_0119B5A9AA624A55AF1B73F2E32A7A0C, Line 14
Invalid object name 'dbo.sysmergearticles'.

Do I have to do something to the database before trying to update it?P.S. I notice I'm missing a comma in there after the first set, missed
it while I was copying it over and didn't notice until I posted.