January 26, 2005 at 10:06 am
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 on this command:
exec usp_transpose_ilisa
I'm not sure what this step is doing.. can anyone help?
January 26, 2005 at 10:17 am
That is a stored procedure in your package. You can run this in Query Analyzer to see what the error is.
January 26, 2005 at 10:34 am
This is the stored procedure: I understand that I can run this code from query analyzer and perhaps see where it's failing.. but I'm not sure which DB to run it against.. master???
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
January 26, 2005 at 1:28 pm
This step is calling a stored_procedure (see code above) I AM able to run the code from the stored procedure from query analyzer but when it tries to call the stored procedure from the package, I get this error::
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
Can anyone help?
January 26, 2005 at 2:00 pm
Open the Execute SQL Task in the DTS designer. Make a note of the 2nd item in the General tab, labelled "Existing Connection". Close the dialog and locate the connection object in the designer with the name you noted. Open the connection object. The details of the connection object will tell you which server and which database the stored procedure is expected to be executed in.
January 26, 2005 at 2:09 pm
Yes, I see that. The Package is set to be executed against one of the user databases on the local instance. I CAN run the code from the stored procedure against this DB from queary analyzer but the sp fails when called from the dts package????????
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply