Stored Procedure called from DTS Package fails

[jpotucek]

I am executing a DTS package that I did not create. it is failing on a step titled 'transpose to staging table' and it is failing when calling this stored procedure: :
exec usp_transpose_ilisa
This is the error:
Step 'DTSStep_DTSExecuteSQLTask_5' failed
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:The statement has been terminated. (Microsoft OLE DB Provider for SQL Server (80040e57): String or binary data would be truncated.)
Step Error code: 80040E57
Step Error Help File:
Step Error Help Context ID: 0
I am able to execute the code from the stored procedure in query analyzer with NO errors ..but it fails when called by the DTS package.....Can anyone help!! (the code from the stored procedure is below)

CREATE PROCEDURE usp_transpose_ilisa AS
DECLARE @firstrow VARCHAR(256)
SELECT @firstrow = a FROM temptry WHERE recid = 1
DECLARE cur_transpose CURSOR FOR
SELECT recid, a, b
FROM temptry
WHERE recid > 1
ORDER BY recid
DECLARE @temprecid INT
DECLARE @tempa VARCHAR(256)
DECLARE @tempb VARCHAR(256)
DECLARE @tempc VARCHAR(256)
DECLARE @tempd VARCHAR(256)
DECLARE @cur_group VARCHAR(256)
DECLARE @nextrecid INT
INSERT INTO trytext (recid, biz_type, distance, [name], addy, phone)
VALUES (1,'X',999.9,@firstrow,'X','X')
OPEN cur_transpose
FETCH NEXT FROM cur_transpose
INTO @temprecid, @tempa, @tempb
WHILE @@FETCH_STATUS = 0
BEGIN
IF @tempa IN ('Archery Equipment & Supplies',
'Archery Ranges',
'Auto Dealers',
'bicycle',
'Bicycle Dealers',
'Bicycle Repair & Maintenance',
'Bicycles',
'Boat & Yacht Charters/Rent/Lease',
'Boat Dealers',
'Boat Service & Repair',
'Campgrounds & RV Parks',
'Camping Equipment & Supplies',
'Canoes & Kayaks',
'Computer & Equipment Dealers',
'Diving Equipment & Supplies',
'Diving Instruction',
'electronic',
'Electronics',
'Exercise & Fitness Classes',
'Exercise & Fitness Equipment',
'Fishing Bait & Tackle',
'Fishing Guides Charters & Parties',
'Guns & Ammunition',
'health clubs & gyms',
'Horse Dealers',
'Horse Stables',
'Horse Trainers',
'Horseback Riding',
'Photographic Equipment & Supplies',
'riding academies & schools',
'RV & Camper Dealers',
'RV & Camper Rent & Lease',
'Skates, Skateboards & Rollerblades',
'Skating Equipment & Supplies',
'Ski Clothing, Equipment & Supplies',
'Ski Equipment Clothing & Supplies',
'Ski Equipment Rental',
'Ski Instruction',
'Ski Resorts',
'sporting goods',
'Taxidermists',
'Travel Agents')
SET @cur_group = @tempa
ELSE
IF @tempa LIKE '%_._'
BEGIN
SELECT @nextrecid = MIN(recid)
FROM temptry
WHERE recid > @temprecid
SELECT @tempc = a
FROM temptry
WHERE recid = @nextrecid
SELECT @tempd = a
FROM temptry
WHERE recid = (SELECT MIN(recid) FROM temptry WHERE recid > @nextrecid)
IF @tempc LIKE '(%'
BEGIN
SET @tempd = @tempc
SET @tempc = 'no address listed'
END
IF @tempd NOT LIKE '(%'
SET @tempd = 'no phone listed'
INSERT INTO trytext (recid, biz_type, distance, [name], addy, phone)
VALUES (@temprecid, @cur_group, @tempa, @tempb, @tempc, @tempd)
END
FETCH NEXT FROM cur_transpose
INTO @temprecid, @tempa, @tempb
END
CLOSE cur_transpose
DEALLOCATE cur_transpose
GO

 

[yksvaan]

Is there tables/views and procedures only in dbo user schema ?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[EdwinGene]

Unless I missed it, I didn't see any parameters or return values in the stored procedure. And, since the stored procedure runs without errors outside of the DTS package, my first thought would be to look at the DTS package, not the stored procedure.

Is the DTS package connected to the same server and database as the Query Analyzer window?

Is the call to the stored procedure the only thing that is taking place in the Execute SQL Task?

 

[gradley]

For what it's worth, have you tried using SET NOCOUNT ON at the beginning of the procedure and SET NOCOUNT OFF at the end.

It looks like the SP is performing multiple select and insert statements, each of witch return records selected/inserted accordingly. I know Query Analyzer is much more forgiving about this then DTS. I believe these multiple return values may be causing this problem.

 

[jpotucek]

Gradley

Thanks for the tip!!!!!!!!!!!!!!!!!!!!!!