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

bcp Execution

Hai..

I have two systems. SQL server 2000 is installed in one system (SERVER ) and the client tools are installed in a seperate system (Client)
My question is from which system should i have to execute the bcp command line utility, from the server or from the client. ?

Thanks in advance...

with regards
Sudari would have thought it would be best to run it on the server myself, but it really depends what you are trying to do.|||Hai..

thnks for your reply...

Actually i want to know whether it is possible to execute bcp from client or not and if yes then how.. ?

bcoz there is no argument in bcp to identify the server name..

thnks in advance

With regards
Sudar|||Did you try -s ?|||Thnks kaiowas it's working correctly.....

Batch Query Execution?

Hi,

I am executing a set of queries. i.e, a batchExecution of queries. I need to know when the batch execution is over so that i can follow up some other process.
Is there any method or event in SQL which can keep track of these things.?
My primary focus is, in knowing the process/BatchQuery completion.
Thanks in AdvanceThere is a SQL Profiler event called SQL:BatchCompleted

See

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_204z.asp

You might also be able to query sysprocesses, for example, and look for changes in "status" or "last_batch".

Be certain you understrand the ins-and-outs of batch processing. See

Batch Processing
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_204z.asp

Also note that in SQL 2005, multiple interleaved batches are allowed on a single connection. This is the so-called "MARS" feature (Multiple Active Result Sets). The new system views needed in SQL 2005 for accurately monitoring MARS activity go beyond what is available in the "sysprocesses" view.

See

Multiple Active Result Sets (MARS) in SQL Server 2005
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MARSinSQL05.asp

Regards,
Clifford Dibble
Program Manager, SQL Server

Batch Execution of Reports

I have a report server set up with about a dozen or so reports. Right now,
the user is accessing each one (via a URL with the appropriate PDF rendering
tags) from a custom web application to generate the reports. What they'd
really prefer to do is click one "button" or something that executes all of
the reports at once, but still creates a separate PDF output for each (and I
don't want to append the report files into one big master report file). I'm
not sure how to go about this since a user can't "batch" click on a set of
URLs to get the reports. Are there any suggestions or resources that I
could refer to on how to best accomplish this?
-marcLook at the BOL. You can batch run your reports via a VB.NET script.
I am not a scripting kinda guy, so I ill have to defer you to look
elsewhere for specifics on scripting.
RS.EXE is the commandline utility for Reporting Services.
"Marc" <marc@.machin.com> wrote in message news:<eHYf8bEzEHA.2788@.TK2MSFTNGP15.phx.gbl>...
> I have a report server set up with about a dozen or so reports. Right now,
> the user is accessing each one (via a URL with the appropriate PDF rendering
> tags) from a custom web application to generate the reports. What they'd
> really prefer to do is click one "button" or something that executes all of
> the reports at once, but still creates a separate PDF output for each (and I
> don't want to append the report files into one big master report file). I'm
> not sure how to go about this since a user can't "batch" click on a set of
> URLs to get the reports. Are there any suggestions or resources that I
> could refer to on how to best accomplish this?
> -marc

Batch execution

Hello All,
I have a table TableX with one column SQL varchar(8000). This column
contains about 50 records (rows) like
insert into tablename1 select datetime,...From Tbl1 Where...
insert into tablename2 select datetime,...From Tbl2 Where...
insert into tablename3 select datetime,...From Tbl3 Where...
...
My question is, is there any way to run these dml statements from tableX all
at one time as a batch rather than looping through TableX and executing them
one by one.
Thanks
RickyHere goes:
set nocount on;
use tempdb;
go
drop table t1, tablex;
go
create table t1(col1 int);
go
create table tablex(sql varchar(8000) not null);
insert into tablex values('insert into t1 values(1);');
insert into tablex values('insert into t1 values(2);');
insert into tablex values('insert into t1 values(3);');
go
exec master..xp_execresultset
N'select sql from tablex;',
N'tempdb'
go
select * from t1;
-- Output:
col1
--
1
2
3
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:667B6820-D2A8-451C-9BDD-38D763298CD2@.microsoft.com...
> Hello All,
> I have a table TableX with one column SQL varchar(8000). This column
> contains about 50 records (rows) like
> insert into tablename1 select datetime,...From Tbl1 Where...
> insert into tablename2 select datetime,...From Tbl2 Where...
> insert into tablename3 select datetime,...From Tbl3 Where...
> ...
> My question is, is there any way to run these dml statements from tableX
> all
> at one time as a batch rather than looping through TableX and executing
> them
> one by one.
> Thanks
> Ricky|||Thank You Itzik Ben-Gan.
"Brilliant as always...!!"
"Itzik Ben-Gan" wrote:

> Here goes:
> set nocount on;
> use tempdb;
> go
> drop table t1, tablex;
> go
> create table t1(col1 int);
> go
> create table tablex(sql varchar(8000) not null);
> insert into tablex values('insert into t1 values(1);');
> insert into tablex values('insert into t1 values(2);');
> insert into tablex values('insert into t1 values(3);');
> go
> exec master..xp_execresultset
> N'select sql from tablex;',
> N'tempdb'
> go
> select * from t1;
> -- Output:
> col1
> --
> 1
> 2
> 3
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rick" <ricky.arora@.metc.state.mn.us> wrote in message
> news:667B6820-D2A8-451C-9BDD-38D763298CD2@.microsoft.com...
>
>|||Anybody know how to get the value of an output parameter from a proc
executed with xp_ExecuteResultSet?
For Example:
set nocount on
use tempdb
go
create table T (t text default '')
go
create proc P (@.T1 text, @.Message varchar(255) Out)
as
begin
select @.T1
set @.Message = 'Your error goes here'
end
go
DECLARE @.SQL nvarchar(4000)
DECLARE @.ErrorMessage nvarchar(4000)
SET @.SQL = 'exec p ''This is a test.'', @.ErrorMessage'
exec master..xp_execresultset @.SQL, N'tempdb'
select @.ErrorMessage
go
drop table T
drop proc P
Message posted via http://www.webservertalk.com|||--It would go something like this.
set nocount on
use tempdb
go
Create Table ##ErrorMessage (F1 [varchar] (8000))
go
create proc P (@.T1 nvarchar(4000), @.Message varchar(255) Out)
as
begin
select
T1 = @.T1,
Message = @.Message
set @.Message = 'Your error goes here'
end
go
DECLARE @.SQL nvarchar(4000)
SET @.SQL = 'EXECUTE(''' + char(13) + char(10) +
'DECLARE @.ErrorMessage nvarchar(4000) ' + char(13) + char(10) +
'exec p ''''This is a test.'''', @.ErrorMessage OUT' + char(13) + char(10)
+
'insert ##ErrorMessage values(@.ErrorMessage)' + char(13) + char(10) +
''')'
--exec sp_executesql @.SQL
SET @.SQL = 'SELECT ''' + Replace(@.SQL, '''', ''') + ''''
exec master..xp_execresultset @.SQL, N'tempdb'
select Message = F1 from ##ErrorMessage
go
drop table ##ErrorMessage
drop proc P
Message posted via http://www.webservertalk.com

Basic SQL Execution Question

I have a table that contains a history of record changes within another table. It may have rows that look like this:

DML_Type Record_ID Record_Value DateTime
Insert 1 A Today @. 11AM
Update 1 B Today @. 11:15AM
Update 1 C Today @. 11:22AM
Delete 1 C Today @. 11:25AM
Insert 2 A Today @. 11AM

Update 2 B Today @. 11:15AM

Update 2 C Today @. 11:22AM

I've inherited a stored procedure that migrates these changes to another database in another schema. The proc is using a cursor to perform each operation in sequence.

If I perform the migration in 3 steps (inserts, updates, deletes) - Can I be sure that the updates will occur in the correct sequence if ordered by DateTime or is there a possibility that SQL Server will parellize the operation and I'd end up with incorrect data? For example, is there a possibility using the T-SQL below that I'd end up with a record_ID 2 and a value of B?

UPDATE
destination table
SET
field1 = field,
...
FROM
SELECT
Record_ID,
Record_Value
FROM
sourceTable st
WHERE
DML_Type = 'Update'
ORDER BY
DateTime
WHERE
dt.Record_ID = st.Record_ID

Dan:

Is Record_ID unique in the [destination table]?

Dave

|||Yes. Record_ID is unique in the destination table. It's amusing that you ask this because the problem I'm dealing with can be completely avoided by using the state of the record in the original table - the audit history isn't important for our purposes.

I am still interested in the answer to the original question for my own edification however.|||

Dan:

Here are a couple of mock-ups of your update:

set nocount on

update destinationTable
set record_value = 'A',
[dateTime] = '12/13/2006 11:00'
--select * from destinationTable

--
-- This works with the particular data but it is really
-- not supported; this is likely to be faster than
-- the second query:
--
/*
UPDATE destinationTable
SET record_value = st.record_value,
[dateTime] = st.[dateTime]
from destinationTable dt
inner join sourceTable st
on dt.record_Id = st.record_id
and st.DML_Type = 'Update'
*/

--
-- This is "safer":
--

update destinationTable
set record_value = st.record_value,
[dateTime] = st.[dateTime]
from destinationTable dt
inner join
( select record_id,
record_value,
[dateTime],
row_number () over
( partition by record_id
order by [dateTime] desc, record_value
) as seq
from sourceTable
where dml_type = 'Update'
) st
on dt.record_id = st.record_id
and st.seq = 1

select * from destinationTable

-- Output:

-- record_id record_value dateTime
-- -- --
-- 1 C 2006-12-13 11:22:00.000
-- 2 C 2006-12-13 11:22:00.000

|||Thanks for the response. Based on your answer, I'm going to assume that the default behavior is undefined and may produce data loss. Now I'll have to read up on partioning :-)