Problem with BCP in trigger, how to do it?

Hello all,

I′m pretty new to T-SQL, so please bear with me.

I′m trying to output the temp "inserted" table avalible in the trigger to a text file.

When this trigger executes, the server seems to enter a never ending query.

If i comment the last three lines (declare... select... exec...) the trigger works fine,

so it seems to be a problem with the BCP part of the trigger.

What is wrong here? If you have better suggestions on how to

accoplish the same thing, please feel free to share!

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [Skapa_Transfil]

ON [dbo].[Products]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

insert into dbo.TempProducts (ProdNo, CountryOfOrigin)

select prodno, CountryOfOrigin

from inserted

declare @.sql varchar(8000)

select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'

exec master..xp_cmdshell @.sql

END

I've managed to replicate this behaviour. The cause seems to be that the TRIGGER is taking out an exclusive lock on the rows that are being inserted into TempProducts and therefore the BCP statement is unable to obtain a shared and so cannot read the data. I can't see a way around this unfortunately.

Would you be able to handle this logic in a stored procedure,similar to the following:

Code Snippet

create procedure insertandexport
@.int1 int, @.int2 int
as
insert into tempproducts
values (@.int1, @.int2)

if @.@.rowcount > 0
begin
declare @.sql varchar(8000)

select @.sql = 'bcp tempdb..TempProducts out c:\fil.txt -c -t, -U"User" -P"password" -S"YouServer"'

exec master..xp_cmdshell @.sql
end

HTH!|||

The trigger runs inside a transaction, so the "insert into" statement is also inside that transaction and could be blocking the table or index. The execution of bcp is out of that transaction and has to wait till the blocking has gone if it is running in "read committed" isolation level.

AMB

|||

Since it would be acceptable with some delay of the updates from tempProducts to fil.txt, would it be a good idea to do something like this?

The trigger keeps updating the tempProducts table whenever the trigger fires, like so:

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [Skapa_Transfil]

ON [dbo].[Products]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

insert into dbo.TempProducts (ProdNo, CountryOfOrigin)

select prodno, CountryOfOrigin

from inserted

END

Then I schedule the following sql script to run using "sqlcmd -i exportfromtempproducts" once a minute or so.

Code Snippet

begin transaction

declare @.sql varchar(8000)

select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'

exec master..xp_cmdshell @.sql

go

use avk

go

delete

from tempProducts

go

commit transaction

I tried this and to me it seems to work. Since I run the bcp and delete in one transaction, the trigger would never be able to insert data into tempProducts between the bcp and the delete?

Thoughts someone?

|||

I think the key to this is setting your TRANSATION ISOLATION LEVEL to SNAPSHOT. This will guarentee that the you will only be working with the rows as they were at the start of the transaction. Otherwise, no exclusive locks will be put on the tempProducts table and so you could get rows inserted after the bcp statement which would then be deleted.

Simulate this behaviour by executing the stages in 2 separate query windows, step by step (ie being tran, run the bcp statement only, update more rows in products in the other window, come back and then run the delete statement etc).

Check Books Online for a more thorough explanation of isolation levels.

As an aside, will you be overwriting fil.txt every minute? Does that matter?

Let us know how you get on!

|||

I made some changes to the query in order to get different file names for each execution,

naming the file with date and time.

How would you go about to execute the query in "separate stages" as you mention above?

I′m used to working with break-points from VB, but I can′t seem to find any similar feature for T-SQL

EDIT: For clarification, I also did the

ALTER DATABASE AVK

SET ALLOW_SNAPSHOT_ISOLATION

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION

DECLARE @.date char(8)

DECLARE @.time char(8)

DECLARE @.sql VARCHAR(8000)

SELECT @.date = CONVERT(char(8), getdate(),112)

SELECT @.time = CONVERT(char(8), getdate(),108)

SELECT @.time = REPLACE(@.time,':','')

SELECT @.time

DECLARE @.dt char(14)

SELECT @.dt = @.date + '_' + @.time

SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'

EXEC master..xp_cmdshell @.sql

GO

USE AVK

GO

DELETE

FROM tempProducts

GO

COMMIT TRANSACTION

|||

For testing purposes, i did the following:

Paste the whole block into a query window in SSMS and then just highlght and execute the individual sections:

ie hightlight this and execute

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION

DECLARE @.date char(8)

DECLARE @.time char(8)

DECLARE @.sql VARCHAR(8000)

SELECT @.date = CONVERT(char(8), getdate(),112)

SELECT @.time = CONVERT(char(8), getdate(),108)

SELECT @.time = REPLACE(@.time,':','')

SELECT @.time

DECLARE @.dt char(14)

SELECT @.dt = @.date + '_' + @.time

SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'

EXEC master..xp_cmdshell @.sql

GO

Then go into your another query window (which is a separate transaction) and execute an update statement on products

Then return to the main window, highlight and execute the final section

Code Snippet

USE AVK

GO

DELETE

FROM tempProducts

GO

COMMIT TRANSACTION

This will give you the behaviour as if an update to your products tabla occured while your second transaction was running and will allow you to view how the different isolation levels affect your results.

As for debugging a la VB, i think you are able to use this facility for stored procedures in Visual Studio but not SSMS.

HTH!

|||

WOHO!

Works like a charm!

Tried updating 3 records, then running the BCP-part of the query. 3 rows out as expected.

The updated 3 more records, did a select * on tempProducts, which now contained 6 records.

Finally ran the delete part of the query, checked tempProducts again. And yep, my last three updates where still there!

Thanks a bunch for all the help!

Problem with BCP in trigger, how to do it?

Hello all,

I′m pretty new to T-SQL, so please bear with me.

I′m trying to output the temp "inserted" table avalible in the trigger to a text file.

When this trigger executes, the server seems to enter a never ending query.

If i comment the last three lines (declare... select... exec...) the trigger works fine,

so it seems to be a problem with the BCP part of the trigger.

What is wrong here? If you have better suggestions on how to

accoplish the same thing, please feel free to share!

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [Skapa_Transfil]

ON [dbo].[Products]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

insert into dbo.TempProducts (ProdNo, CountryOfOrigin)

select prodno, CountryOfOrigin

from inserted

declare @.sql varchar(8000)

select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'

exec master..xp_cmdshell @.sql

END

I've managed to replicate this behaviour. The cause seems to be that the TRIGGER is taking out an exclusive lock on the rows that are being inserted into TempProducts and therefore the BCP statement is unable to obtain a shared and so cannot read the data. I can't see a way around this unfortunately.

Would you be able to handle this logic in a stored procedure,similar to the following:

Code Snippet

create procedure insertandexport
@.int1 int, @.int2 int
as
insert into tempproducts
values (@.int1, @.int2)

if @.@.rowcount > 0
begin
declare @.sql varchar(8000)

select @.sql = 'bcp tempdb..TempProducts out c:\fil.txt -c -t, -U"User" -P"password" -S"YouServer"'

exec master..xp_cmdshell @.sql
end

HTH!|||

The trigger runs inside a transaction, so the "insert into" statement is also inside that transaction and could be blocking the table or index. The execution of bcp is out of that transaction and has to wait till the blocking has gone if it is running in "read committed" isolation level.

AMB

|||

Since it would be acceptable with some delay of the updates from tempProducts to fil.txt, would it be a good idea to do something like this?

The trigger keeps updating the tempProducts table whenever the trigger fires, like so:

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [Skapa_Transfil]

ON [dbo].[Products]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

insert into dbo.TempProducts (ProdNo, CountryOfOrigin)

select prodno, CountryOfOrigin

from inserted

END

Then I schedule the following sql script to run using "sqlcmd -i exportfromtempproducts" once a minute or so.

Code Snippet

begin transaction

declare @.sql varchar(8000)

select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'

exec master..xp_cmdshell @.sql

go

use avk

go

delete

from tempProducts

go

commit transaction

I tried this and to me it seems to work. Since I run the bcp and delete in one transaction, the trigger would never be able to insert data into tempProducts between the bcp and the delete?

Thoughts someone?

|||

I think the key to this is setting your TRANSATION ISOLATION LEVEL to SNAPSHOT. This will guarentee that the you will only be working with the rows as they were at the start of the transaction. Otherwise, no exclusive locks will be put on the tempProducts table and so you could get rows inserted after the bcp statement which would then be deleted.

Simulate this behaviour by executing the stages in 2 separate query windows, step by step (ie being tran, run the bcp statement only, update more rows in products in the other window, come back and then run the delete statement etc).

Check Books Online for a more thorough explanation of isolation levels.

As an aside, will you be overwriting fil.txt every minute? Does that matter?

Let us know how you get on!

|||

I made some changes to the query in order to get different file names for each execution,

naming the file with date and time.

How would you go about to execute the query in "separate stages" as you mention above?

I′m used to working with break-points from VB, but I can′t seem to find any similar feature for T-SQL

EDIT: For clarification, I also did the

ALTER DATABASE AVK

SET ALLOW_SNAPSHOT_ISOLATION

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION

DECLARE @.date char(8)

DECLARE @.time char(8)

DECLARE @.sql VARCHAR(8000)

SELECT @.date = CONVERT(char(8), getdate(),112)

SELECT @.time = CONVERT(char(8), getdate(),108)

SELECT @.time = REPLACE(@.time,':','')

SELECT @.time

DECLARE @.dt char(14)

SELECT @.dt = @.date + '_' + @.time

SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'

EXEC master..xp_cmdshell @.sql

GO

USE AVK

GO

DELETE

FROM tempProducts

GO

COMMIT TRANSACTION

|||

For testing purposes, i did the following:

Paste the whole block into a query window in SSMS and then just highlght and execute the individual sections:

ie hightlight this and execute

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION

DECLARE @.date char(8)

DECLARE @.time char(8)

DECLARE @.sql VARCHAR(8000)

SELECT @.date = CONVERT(char(8), getdate(),112)

SELECT @.time = CONVERT(char(8), getdate(),108)

SELECT @.time = REPLACE(@.time,':','')

SELECT @.time

DECLARE @.dt char(14)

SELECT @.dt = @.date + '_' + @.time

SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'

EXEC master..xp_cmdshell @.sql

GO

Then go into your another query window (which is a separate transaction) and execute an update statement on products

Then return to the main window, highlight and execute the final section

Code Snippet

USE AVK

GO

DELETE

FROM tempProducts

GO

COMMIT TRANSACTION

This will give you the behaviour as if an update to your products tabla occured while your second transaction was running and will allow you to view how the different isolation levels affect your results.

As for debugging a la VB, i think you are able to use this facility for stored procedures in Visual Studio but not SSMS.

HTH!

|||

WOHO!

Works like a charm!

Tried updating 3 records, then running the BCP-part of the query. 3 rows out as expected.

The updated 3 more records, did a select * on tempProducts, which now contained 6 records.

Finally ran the delete part of the query, checked tempProducts again. And yep, my last three updates where still there!

Thanks a bunch for all the help!

Problem with a temp table

For some reason the compiler is telling me that I must declarethe
variable @.costcenter_tmp on lines 74 and 98...but if i put a select
statement in ther (for testing) before the loop I get data back from
the temp table..

why is this happening to this temp table and no others?..heres my
code..its a little lengthy

--Fiscal Year
declare @.year smallint
set @.year = 2004

--Month number the Fiscal year starts and ends
declare @.month smallint
set @.month = 7

--Place holder for number of costcenters
declare @.cccounter smallint

--loop counter for cost centers
declare @.ccount smallint
set @.ccount = 1

--Place holder for number of payor types
declare @.ptcounter smallint

--loop counter for payor types
declare @.pcount smallint
set @.pcount = 1

--Temp table to store the blank values for all cost centers/payor
types for the fiscal year
declare @.Recorded_Revenue_tmp table
(
Revenue money default 0,
[Date] varchar(15),
monthn smallint,
[Cost Center] varchar(50),
[Payor Type] varchar(50)
)

--Temp table to store the values of the coster centers
declare @.costcenter_tmp table
(
ccid int IDENTITY (1,1),
ccname varchar(50)
)

--Inserts cost centers and code into the @.costcenter_tmp temp table
insert into @.costcenter_tmp (ccname) select costcenter.fullname + ' '
+ costcenter.code from costcenter, agency_cost_center
where costcenter.oid = agency_cost_center.cost_center_moniker

--Sets the @.cccounter variable to the number of cost centers
select @.cccounter = count(*) from @.costcenter_tmp

--Temp table to store the values of the payor types
declare @.payor_type_tmp table
(
ptid int identity (1,1),
ptname varchar(50)
)

--Inserts payor types into the @.payor_type_tmp temp table
Insert into @.payor_type_tmp(ptname)select fullname from payor_type
where payor_type.oid = payor.payor_type_moniker

--Sets the @.ptcounter variable to the number of payor types
select @.ptcounter = count(*) from @.payor_type_tmp

--Loop that gets the first part of the fiscal year
While (@.month <13)
begin
--Loop that gets the value of the cost center to insert
While (@.ccount <= @.cccounter)
begin
--Loop that inserts values for the first part of the fiscal year into
the @.Recorded_Revenue_tmp temp table
while (@.pcount <= @.ptcounter)
begin
Insert into @.Recorded_Revenue_tmp(Revenue, [Date], monthn, [Cost
Center],[Payor Type])
select 0, datename(month, @.month)+ ' ' + @.year -1, @.month, [Cost
Center], [Payor Type]
from @.costcenter_tmp,@.payor_type_tmp where @.costcenter_tmp.ccid =
@.ccount and
@.payor_type_tmp.ptid = @.pcount
set @.pcount = @.pcount + 1
end
set @.pcount = 1
set @.ccount = @.ccount + 1
end
set @.ccount = 1
set @.month = @.month + 1
end

set @.month = 1

--Loop that inserts values for the second part of the fiscal year into
the @.Recorded_Revenue_tmp temp table
While (@.month <7)
begin
--Loop that gets the value of the cost center to insert
While (@.ccount <= @.cccounter)
begin
--Loop that inserts values for the first part of the fiscal year into
the @.Recorded_Revenue_tmp temp table
while (@.pcount <= @.ptcounter)
begin
Insert into @.Recorded_Revenue_tmp([Date], monthn, [Cost Center],[Payor
Type])
select 0,datename(month, @.month)+ ' ' + @.year, @.month, [Cost Center],
[Payor Type]
from @.costcenter_tmp, @.payor_type_tmp where @.costcenter_tmp.ccid =
@.ccount and
@.payor_type_tmp.ptid = @.pcount
set @.pcount = @.pcount + 1
end
set @.pcount = 1
set @.ccount = @.ccount + 1
end
set @.ccount = 1
set @.month = @.month + 1
end

--Pulls in all the data for the report
(select Revenue,[Date],[Cost Center],[Payor Type] from
@.Recorded_Revenue_tmp)

union

(select (revenue) as Revenue, (b.monthname + ' ' + Cast(b.yearn as
varchar(4))) as 'Date',
c.fullname + ' ' + c.code as 'Cost Center',d.fullname as 'Payor
Type'

from chr_recorded_revenue a, chr_recorded_revenue_dates b,
costcenter c, payor_type d

where a.date = b.day and a.[Cost Center]= c.oid and a.[Payor Type]
= d.oid)

order by d.fullname,b.monthn, c.oid

thanks..Jim[posted and mailed, please reply in news]

Jim (jim.ferris@.motorola.com) writes:
> For some reason the compiler is telling me that I must declarethe
> variable @.costcenter_tmp on lines 74 and 98...but if i put a select
> statement in ther (for testing) before the loop I get data back from
> the temp table..
> why is this happening to this temp table and no others?..heres my
> code..its a little lengthy
>...

The problem is here:

> from @.costcenter_tmp,@.payor_type_tmp where @.costcenter_tmp.ccid =
===============

You cannot use a table variable as a column prefix. Use an alias instead:

from @.costcenter_tmp, ct @.payor_type_tmp pt where ct.ccid =

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

problem when using temp table to hold data that return from another procedure

Hello,

We have a Query-Hierarchy procedure take two personID and return the persons that in between of them if they are in the same hierarchy. The procedure works fine. But this procedure was called in many other procedures. We use a temp table to hold the data that return from Query_Hierarchy:

create Table #TEMPRESOURCEHI(ResourceID int, Firstname varchar(50), Secondname varchar(50))

insert into #TEMPRESOURCEHI exec ResourceDB.dbo.Query_Hierarchy @.ResID, @.s_LSOResID

The problem is, we have some procedures using such code to get data, and most of them work. 2 years ago, only one procedure is not working, though Query_Hierarchy return correct resutls which have more than one rows, the temp table only contain one row. And later on, some other procedures get the same problem as well, I know some of them are working before, now is broken as well, the temp table always only get one row inserted no even Query_Hierarchy return more than one rows.

For example, after
insert into #TEMPRESOURCEHI exec ResourceDB.dbo.Query_Hierarchy @.ResID, @.s_LSOResID

If I select * from #TEMPRESOURCEHI so I can get the result in query analyzer, it contains one row.
I also try directly call Query_Hierarchy to let the result show in query anaylyzer as well, it return correctly with more than one row.

We have no idea why such problem occurs and why occurs randomly, we can't see any problem from our code and hope someone can have a look and give some suggestions...

Thanks in advanced

The Query_Hierarchy code is as below:
========================================
CREATE PROCEDURE dbo.Query_Hierarchy
@.FirstID int, --ResourceID of the 'leaf'
@.LastID int --ResourceID of the ancestor
AS
DECLARE @.S TABLE (ResourceID int, FirstName VARCHAR (50), LastName VARCHAR (50))
DECLARE @.R TABLE (ResourceID int)

INSERT INTO @.S (ResourceID, FirstName, LastName)
(SELECT Resource.[ID], Person.FirstName, Person.LastName
FROM Resource
INNER JOIN Person ON PersonID = Person.[ID]
WHERE Resource.[ID] = @.FirstID)
WHILE (@.@.ROWCOUNT > 0)
BEGIN
INSERT INTO @.S (ResourceID, FirstName, LastName)
(SELECT ParentID, Person.FirstName, Person.LastName
FROM @.S
INNER JOIN ResourceAssociation ON ResourceID = ChildID
INNER JOIN Resource ON ResourceAssociation.ParentID = Resource.[ID]
LEFT OUTER JOIN Person ON Resource.PersonID = Person.[ID]
WHERE ParentID NOT IN (SELECT ResourceID FROM @.S) AND ParentID <> @.LastID)
END

INSERT INTO @.S (ResourceID, FirstName, LastName)
(SELECT Resource.[ID], Person.FirstName, Person.LastName
FROM Resource
LEFT OUTER JOIN Person ON PersonID = Person.[ID]
WHERE Resource.[ID] = @.LastID)

--Now walk the tree from the top down and filter out any nodes that don't belong to the root given by @.LastID. Easy.
INSERT INTO @.R (ResourceID)
VALUES (@.LastID)
WHILE (@.@.ROWCOUNT > 0)
BEGIN
INSERT INTO @.R (ResourceID)
(SELECT ChildID FROM @.R INNER JOIN ResourceAssociation ON ResourceID = ParentID
WHERE ChildID NOT IN (SELECT ResourceID FROM @.R))
END

SELECT ResourceID, FirstName, LastName FROM @.S
WHERE ResourceID IN (SELECT ResourceID FROM @.R)
AND FirstName is not null
AND LastName is not null

GO
========================================

Try setting the NOCOUNT ON at the beginning of the procedure that loads the temp table. I ran into a similar situation a couple of years ago loading recordsets and only getting one result. When we set nocount on the problem resolved.

Code Snippet

SET NOCOUNT ON

create Table #TEMPRESOURCEHI(ResourceID int, Firstname varchar(50), Secondname varchar(50))

insert into #TEMPRESOURCEHI exec ResourceDB.dbo.Query_Hierarchy @.ResID, @.s_LSOResID

Try this out and see if it resolves your issue. If not try setting the NOCOUNT ON in the ResourceDB.dbo.Query_Hierarchy stored procedure.

problem when using temp table to hold data that return from another procedure

Hello,

We have a Query-Hierarchy procedure take two personID and return the persons that in between of them if they are in the same hierarchy. The procedure works fine. But this procedure was called in many other procedures. We use a temp table to hold the data that return from Query_Hierarchy:

create Table #TEMPRESOURCEHI(ResourceID int, Firstname varchar(50), Secondname varchar(50))

insert into #TEMPRESOURCEHI exec ResourceDB.dbo.Query_Hierarchy @.ResID, @.s_LSOResID

The problem is, we have some procedures using such code to get data, and most of them work. 2 years ago, only one procedure is not working, though Query_Hierarchy return correct resutls which have more than one rows, the temp table only contain one row. And later on, some other procedures get the same problem as well, I know some of them are working before, now is broken as well, the temp table always only get one row inserted no even Query_Hierarchy return more than one rows.

For example, after
insert into #TEMPRESOURCEHI exec ResourceDB.dbo.Query_Hierarchy @.ResID, @.s_LSOResID

If I select * from #TEMPRESOURCEHI so I can get the result in query analyzer, it contains one row.
I also try directly call Query_Hierarchy to let the result show in query anaylyzer as well, it return correctly with more than one row.

We have no idea why such problem occurs and why occurs randomly, we can't see any problem from our code and hope someone can have a look and give some suggestions...

Thanks in advanced

The Query_Hierarchy code is as below:
========================================
CREATE PROCEDURE dbo.Query_Hierarchy
@.FirstID int, --ResourceID of the 'leaf'
@.LastID int --ResourceID of the ancestor
AS
DECLARE @.S TABLE (ResourceID int, FirstName VARCHAR (50), LastName VARCHAR (50))
DECLARE @.R TABLE (ResourceID int)

INSERT INTO @.S (ResourceID, FirstName, LastName)
(SELECT Resource.[ID], Person.FirstName, Person.LastName
FROM Resource
INNER JOIN Person ON PersonID = Person.[ID]
WHERE Resource.[ID] = @.FirstID)
WHILE (@.@.ROWCOUNT > 0)
BEGIN
INSERT INTO @.S (ResourceID, FirstName, LastName)
(SELECT ParentID, Person.FirstName, Person.LastName
FROM @.S
INNER JOIN ResourceAssociation ON ResourceID = ChildID
INNER JOIN Resource ON ResourceAssociation.ParentID = Resource.[ID]
LEFT OUTER JOIN Person ON Resource.PersonID = Person.[ID]
WHERE ParentID NOT IN (SELECT ResourceID FROM @.S) AND ParentID <> @.LastID)
END

INSERT INTO @.S (ResourceID, FirstName, LastName)
(SELECT Resource.[ID], Person.FirstName, Person.LastName
FROM Resource
LEFT OUTER JOIN Person ON PersonID = Person.[ID]
WHERE Resource.[ID] = @.LastID)

--Now walk the tree from the top down and filter out any nodes that don't belong to the root given by @.LastID. Easy.
INSERT INTO @.R (ResourceID)
VALUES (@.LastID)
WHILE (@.@.ROWCOUNT > 0)
BEGIN
INSERT INTO @.R (ResourceID)
(SELECT ChildID FROM @.R INNER JOIN ResourceAssociation ON ResourceID = ParentID
WHERE ChildID NOT IN (SELECT ResourceID FROM @.R))
END

SELECT ResourceID, FirstName, LastName FROM @.S
WHERE ResourceID IN (SELECT ResourceID FROM @.R)
AND FirstName is not null
AND LastName is not null

GO
========================================

Try setting the NOCOUNT ON at the beginning of the procedure that loads the temp table. I ran into a similar situation a couple of years ago loading recordsets and only getting one result. When we set nocount on the problem resolved.

Code Snippet

SET NOCOUNT ON

create Table #TEMPRESOURCEHI(ResourceID int, Firstname varchar(50), Secondname varchar(50))

insert into #TEMPRESOURCEHI exec ResourceDB.dbo.Query_Hierarchy @.ResID, @.s_LSOResID

Try this out and see if it resolves your issue. If not try setting the NOCOUNT ON in the ResourceDB.dbo.Query_Hierarchy stored procedure.