stored procedure error

[inutelinside]

TrackingID=57AA9699433C2F47 6056.SD_ICBAM V1.0.90.CDatabaseSvc.ExecuteQueryEx.0> error '80040e7b'

Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source. vsSQLStatement=sp_erwinReport_KTNET

/researchtrackingtool/erwinReport.asp, line 243
What is causing the error above? When I check syntax it seems ok but I still get the error above.


CREATE PROCEDURE dbo.sp_erwinReport_KTNET
@start_period datetime,
@end_period datetime,
@team varchar(50)


AS



DECLARE @soldby_name varchar(500)
DECLARE @request_id varchar(10)
DECLARE @Temp_soldby_combined_tbl TABLE(
request_id varchar(10) Null,
sold_by varchar(500) Null)


IF @team = 'All Teams'
BEGIN

DECLARE soldby_cursor CURSOR FOR
SELECT a.request_id FROM kcnetRequest_KCNet_TBL a INNER JOIN
customer_KCNet_TBL b ON a.customer_ID=b.customer_ID INNER JOIN
person_KRIS_TBL c ON c.personnel_ID=a.originalRep_ID INNER JOIN
charge_number_type_KCNet_TBL e ON e.charge_number_type_ID=a.charge_number_type_ID INNER JOIN
team_KRIS_TBL d ON d.team_id=a.team_id
WHERE (a.request_date >= @start_period) and (a.request_date<= @end_period)
ORDER BY a.request_id

OPEN soldby_cursor

FETCH NEXT FROM soldby_cursor
INTO @request_id

WHILE @@FETCH_STATUS = 0
BEGIN
SET @soldby_name = ''

SELECT @soldby_name = COALESCE(@soldby_name + ', ', '') +
CAST((ltrim(rtrim(First_Name)) + ' ' + ltrim(rtrim(middle_initial)) + ' ' + ltrim(rtrim(Last_Name))) AS varchar(500))
FROM Research_sold_by_TBL r
JOIN person_kris_tbl p on
ltrim(rtrim(p.personnel_ID)) = ltrim(rtrim(r.personnel_id))
WHERE request_ID = @request_id and
r.Personnel_ID <> ''

INSERT @Temp_soldby_combined_tbl
VALUES (@request_id, @soldby_name)

FETCH NEXT FROM soldby_cursor
INTO @request_id
END


CLOSE soldby_cursor
DEALLOCATE soldby_cursor

SELECT a.request_id,ltrim(rtrim(b.customer_fname)) + ' ' + ltrim(rtrim(b.customer_initial)) + ' ' + ltrim(rtrim(b.customer_lname)) as Customer,a.title_topic,a.Request_Date,a.end_period,
ltrim(rtrim(c.first_name)) + ' ' + ltrim(rtrim(c.middle_initial)) + ' ' + ltrim(rtrim(c.last_name)) as Researcher,d.team_name,e.charge_number_type_description,a.WBS1, t.sold_by
FROM kcnetRequest_KCNet_TBL a INNER JOIN
customer_KCNet_TBL b ON a.customer_ID=b.customer_ID INNER JOIN
person_KRIS_TBL c ON c.personnel_ID=a.originalRep_ID INNER JOIN
charge_number_type_KCNet_TBL e ON e.charge_number_type_ID=a.charge_number_type_ID INNER JOIN
team_KRIS_TBL d ON d.team_id=a.team_id LEFT JOIN @Temp_soldby_combined_tbl t ON a.request_id = t.request_Id
WHERE (a.request_date >= @start_period) and (a.request_date<= @end_period)
ORDER BY a.request_id


END
ELSE
BEGIN
SELECT a.request_id,ltrim(rtrim(b.customer_fname)) + ' ' + ltrim(rtrim(b.customer_initial)) + ' ' + ltrim(rtrim(b.customer_lname)) as Customer,a.title_topic,a.Request_Date,a.end_period,
ltrim(rtrim(c.first_name)) + ' ' + ltrim(rtrim(c.middle_initial)) + ' ' + ltrim(rtrim(c.last_name)) as Researcher,d.team_name,e.charge_number_type_description,a.WBS1

FROM kcnetRequest_KCNet_TBL a INNER JOIN
customer_KCNet_TBL b ON a.customer_ID=b.customer_ID INNER JOIN
person_KRIS_TBL c ON c.personnel_ID=a.originalRep_ID INNER JOIN
charge_number_type_KCNet_TBL e ON e.charge_number_type_ID=a.charge_number_type_ID INNER JOIN
team_KRIS_TBL d ON d.team_id=a.team_id

WHERE (a.request_date >= @start_period) and (a.request_date<= @end_period) and (d.team_name = @team)

ORDER BY request_id
END
GO

 

[SQLBill]

Have you run the CREATE PROCEDURE in Query Analyzer?
Have you tested the procedure in Query Analyzer?

If you did both and they work in QA, then the problem is with whatever language you are using to connect and run the script. You should post in a forum for that language.

-SQLBill

Posting advice: FAQ481-4875

 

[inutelinside]

Hello,

Temporarily disabling the count fixed the problem.
CREATE PROCEDURE dbo.sp_requestsReport_KCNET
@start_period datetime,
@end_period datetime,
@team varchar(50)


AS



DECLARE @soldby_name varchar(3500)
DECLARE @request_id varchar(10)
DECLARE @Temp_soldby_combined_tbl TABLE(
request_id varchar(10) Null,
sold_by varchar(3500) Null)


IF @team = 'All Teams' -- All Teams is selected by the user
BEGIN

SET NOCOUNT ON

..........

 

[SQLDenis]

SET NOCOUNT ON should be on the top of every stored procedure (right after the AS)
Also don't use sp_ to start you procedure name this is used by MS stored procedures read here what could happen (

http://www.sqlmag.com/Article/ArticleID/23011/sql_server_23011.html)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/