I am running a stored proc that only has one return select statement
(the others are for inserting into a temp table. But I get the
following error...DTS Designer Error No rowset was returned from the
SQL statement.

Here is my proc

DECLARE @tmpOrg TABLE
(
code varchar(15),
display varchar(50),
office varchar(50),
officecd varchar(3)
)

INSERT @tmpOrg
(
code,
display,
office,
officecd
)
SELECT '',
'',
OfficeName,
OfficeCd
FROM Office

INSERT @tmpOrg
(
code,
display,
office,
officecd
)
SELECT 'ATTY',
'Attorney',
OfficeName,
OfficeCd
FROM Office


INSERT @tmpOrg
(
code,
display,
office,
officecd
)
SELECT 'NONATTY',
'Non-Attorney',
OfficeName,
OfficeCd
FROM Office

DECLARE @tmpOrganization TABLE
(
OfficeDisplayCd varchar(15),
OfficeDisplay varchar(50),
OfficeCode varchar(50),
Sonnenschein varchar(50),
Value int
)

INSERT @tmpOrganization
(
OfficeDisplayCd,
OfficeDisplay,
OfficeCode,
Sonnenschein,
Value
)
SELECT 'SNR','Sonnenschein','','Sonnenschein',1

INSERT @tmpOrganization
(
OfficeDisplayCd,
OfficeDisplay,
OfficeCode,
Sonnenschein,
Value
)
SELECT 'SNRREGION','SNR Region','SNR','Sonnenschein',0

INSERT @tmpOrganization
(
OfficeDisplayCd,
OfficeDisplay,
OfficeCode,
Sonnenschein,
Value
)
SELECT 'SNRFUNC','SNR Functional','SNR','Sonnenschein',0

INSERT @tmpOrganization
(
OfficeDisplayCd,
OfficeDisplay,
OfficeCode,
Sonnenschein,
Value
)
SELECT officecd + code AS OfficeDisplayCd,
RTRIM(office + ' ' + display) AS OfficeDisplay,
CASE code
WHEN '' THEN 'SNRREGION'
ELSE officecd
END AS OfficeCode,
'Sonnenschein' AS Sonnenschein,
0 AS Value
FROM @tmpOrg
ORDER BY OfficeDisplayCd

INSERT @tmpOrganization
(
OfficeDisplayCd,
OfficeDisplay,
OfficeCode,
Sonnenschein,
Value
)
SELECT LEFT(DepartmentID, 2),
Department,
'SNRFUNC',
'Sonnenschein' AS Sonnenschein,
0 AS Value
FROM Department
ORDER BY DepartmentID

SELECT OfficeDisplayCd,
OfficeDisplay,
OfficeCode,
Sonnenschein,
Value
FROM @tmpOrganization

Run your script in Query Analyzer and see whether any rows are returned.

Charles Kangai, MCT, MCDBA

Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm





"Erik" wrote: