Return data from a temp table

I have the following stored proc
CREATE PROCEDURE [dbo].[GetPartHistory]
@.PartID int
AS
DECLARE @.tmpStepData table(Station_Name varchar(20), Step int,
Step_Description varchar(200), Station_Step_Description varchar(200), Result
varchar(100), Operator_Name varchar(50), Result_Date datetime)
INSERT INTO @.tmpStepData
(Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name, Result_Date)
SELECT
Station.Station_Name,
Traveler_Step.Step,
Traveler_Step.Step_Description,
Station_Step_Data.Station_step_Description,
Step_Result.Result,
Operator.Operator_Name,
Step_Result.Result_Date
FROM
Step_Result
INNER JOIN
Operator ON Step_Result.Operator_# = Operator.Operator_#
INNER JOIN
Station_Step_Data ON Step_Result.Station_Step_Data_# =
Station_Step_Data.Station_Step_Data_#
INNER JOIN
Traveler_Step ON Station_Step_Data.Traveler_Step_# =
Traveler_Step.Traveler_Step_#
INNER JOIN
Station ON Traveler_Step.Station_# = Station.Station_#
WHERE
Step_Result.Product_# = @.PartID
INSERT INTO @.tmpStepData
(Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name, Result_Date)
SELECT
Station.Station_Name,
Traveler_Step.Step,
Traveler_Step.Step_Description,
Station_Step_Data.Station_step_Description,
Weight.Weight,
Operator.Operator_Name,
Weight.Weight_Date
FROM Traveler_Step INNER JOIN
Station_Step_Data ON Traveler_Step.Traveler_Step_# =
Station_Step_Data.Traveler_Step_# INNER JOIN
Station ON Traveler_Step.Station_# = Station.Station_#
INNER JOIN
Weight ON Station_Step_Data.Station_Step_Data_# =
Weight.Station_Step_Data_# INNER JOIN
Operator ON Weight.Operator_# = Operator.Operator_#
WHERE
Weight.Product_# = @.PartID
SELECT Station_Name, Step, Step_Description, Station_Step_Description,
Result, Operator_Name, Result_Date FROM @.tmpStepData ORDER BY STEP
GO
when executed in query analyser it returns the data I am after, but when I
try to access it via ADO I get nothing. What should I do to return data
from the temp table.
John WrightTry adding SET NOCOUNT ON in the beginning of the proc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"john wright" <riley_wright@.hotmail.com> wrote in message
news:uuuVlR37FHA.2600@.tk2msftngp13.phx.gbl...
>I have the following stored proc
> CREATE PROCEDURE [dbo].[GetPartHistory]
> @.PartID int
> AS
> DECLARE @.tmpStepData table(Station_Name varchar(20), Step int, Step_Descri
ption varchar(200),
> Station_Step_Description varchar(200), Result varchar(100), Operator_Name
varchar(50), Result_Date
> datetime)
>
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result,
Operator_Name,
> Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Step_Result.Result,
> Operator.Operator_Name,
> Step_Result.Result_Date
> FROM
> Step_Result
> INNER JOIN
> Operator ON Step_Result.Operator_# = Operator.Operator_#
> INNER JOIN
> Station_Step_Data ON Step_Result.Station_Step_Data_# =
> Station_Step_Data.Station_Step_Data_#
> INNER JOIN
> Traveler_Step ON Station_Step_Data.Traveler_Step_# = Traveler_
Step.Traveler_Step_#
> INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_#
> WHERE
> Step_Result.Product_# = @.PartID
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result
, Operator_Name,
> Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Weight.Weight,
> Operator.Operator_Name,
> Weight.Weight_Date
> FROM Traveler_Step INNER JOIN
> Station_Step_Data ON Traveler_Step.Traveler_Step_# =
> Station_Step_Data.Traveler_Step_# INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_
# INNER JOIN
> Weight ON Station_Step_Data.Station_Step_Data_# = Wei
ght.Station_Step_Data_#
> INNER JOIN
> Operator ON Weight.Operator_# = Operator.Operator_#
> WHERE
> Weight.Product_# = @.PartID
> SELECT Station_Name, Step, Step_Description, Station_Step_Description, Res
ult, Operator_Name,
> Result_Date FROM @.tmpStepData ORDER BY STEP
> GO
>
> when executed in query analyser it returns the data I am after, but when I
try to access it via
> ADO I get nothing. What should I do to return data from the temp table.
> John Wright
>|||Yea that did it. I remembered just after I posted the message.
John
"john wright" <riley_wright@.hotmail.com> wrote in message
news:uuuVlR37FHA.2600@.tk2msftngp13.phx.gbl...
>I have the following stored proc
> CREATE PROCEDURE [dbo].[GetPartHistory]
> @.PartID int
> AS
> DECLARE @.tmpStepData table(Station_Name varchar(20), Step int,
> Step_Description varchar(200), Station_Step_Description varchar(200),
> Result varchar(100), Operator_Name varchar(50), Result_Date datetime)
>
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description, Result,
> Operator_Name, Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Step_Result.Result,
> Operator.Operator_Name,
> Step_Result.Result_Date
> FROM
> Step_Result
> INNER JOIN
> Operator ON Step_Result.Operator_# = Operator.Operator_#
> INNER JOIN
> Station_Step_Data ON Step_Result.Station_Step_Data_# =
> Station_Step_Data.Station_Step_Data_#
> INNER JOIN
> Traveler_Step ON Station_Step_Data.Traveler_Step_# =
> Traveler_Step.Traveler_Step_#
> INNER JOIN
> Station ON Traveler_Step.Station_# = Station.Station_#
> WHERE
> Step_Result.Product_# = @.PartID
> INSERT INTO @.tmpStepData
> (Station_Name, Step, Step_Description, Station_Step_Description,
> Result, Operator_Name, Result_Date)
> SELECT
> Station.Station_Name,
> Traveler_Step.Step,
> Traveler_Step.Step_Description,
> Station_Step_Data.Station_step_Description,
> Weight.Weight,
> Operator.Operator_Name,
> Weight.Weight_Date
> FROM Traveler_Step INNER JOIN
> Station_Step_Data ON Traveler_Step.Traveler_Step_# =
> Station_Step_Data.Traveler_Step_# INNER JOIN
> Station ON Traveler_Step.Station_# =
> Station.Station_# INNER JOIN
> Weight ON Station_Step_Data.Station_Step_Data_# =
> Weight.Station_Step_Data_# INNER JOIN
> Operator ON Weight.Operator_# = Operator.Operator_#
> WHERE
> Weight.Product_# = @.PartID
> SELECT Station_Name, Step, Step_Description, Station_Step_Description,
> Result, Operator_Name, Result_Date FROM @.tmpStepData ORDER BY STEP
> GO
>
> when executed in query analyser it returns the data I am after, but when I
> try to access it via ADO I get nothing. What should I do to return data
> from the temp table.
> John Wright
>