Re: Creating stored procedures using a DTS package

#1

Hello Gloria,

If the syntax is correct for the procs to be created and the exact same code
works when created outside of DTS then i see absolutely no reason why the
different versions should yield different results.

Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

Quote:

Hi all,

I created a DTS package that creates several (12) stored procedures.
The package runs successfully.
I can see all of the procedures in the database.
When I try to run them in SQL Query Analyzer, I get no results.
The heading to the output columns are there, but no results.
When I create another stored procedure by cutting and pasting the
exact
script and give it the same name with a '1' added, the procedure
returns the
correct results.
I am using SQL Server 2000.
Below is an example of the stored procedure from the DTS package:
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'ProcSelectLotData' AND type = 'P')
DROP PROCEDURE ProcSelectLotData
GO
CREATE PROCEDURE ProcSelectLotData
(@LotName nvarchar(100) = NULL,
@DateFrom datetime = NULL,
@DateTo datetime = NULL)
AS
IF @LotName = NULL
SET @LotName = '%'
ELSE
SET @LotName = '%' + @LotName + '%'
IF @DateFrom = NULL
BEGIN
SELECT tblLot.stLotID, tblWafer.dtStartTime AS WaferStart,
tblWafer.idWafer, tblRecipe.idMeasurementType
FROM tblLot INNER JOIN
tblWafer ON tblLot.idLot =
tblWafer.idLot
INNER JOIN
tblRecipe ON tblLot.idRecipe =
tblRecipe.idRecipe
WHERE (tblLot.stLotID LIKE @LotName) AND
(tblRecipe.idMeasurementType
= 1) OR
(tblLot.stLotID LIKE @LotName) AND
(tblRecipe.idMeasurementType = 7)
ORDER BY tblLot.stLotID
END
ELSE
BEGIN
SELECT tblLot.stLotID, tblWafer.dtStartTime AS WaferStart,
tblWafer.idWafer, tblRecipe.idMeasurementType
FROM tblLot INNER JOIN
tblWafer ON tblLot.idLot =
tblWafer.idLot
INNER JOIN
tblRecipe ON tblLot.idRecipe =
tblRecipe.idRecipe
WHERE (tblLot.stLotID LIKE @LotName) AND (tblLot.dtCreateDate >=
@DateFrom) AND
(tblLot.dtCreateDate <= @DateTo) AND
(tblRecipe.idMeasurementType = 1) OR
(tblLot.stLotID LIKE @LotName) AND (tblLot.dtCreateDate >=
@DateFrom) AND
(tblLot.dtCreateDate <= @DateTo) AND
(tblRecipe.idMeasurementType = 7)
ORDER BY tblLot.dtCreateDate
END
GO
The stored procedure works fine if I create it by right-clicking on
'Stored Procedure' and select 'New Stored Procedure'.

Thanks for the help,
Gloria

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Creating stored procedures using a DTS package

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Creating stored procedures using a DTS package - 12-11-2006 , 02:49 PM