Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Invalid Cursor State...no cursor in SP

Status
Not open for further replies.
KrystlDuk

KrystlDuk

Programmer
May 19, 2006
6
US
I'm tearing my hair out here and at my wits end! Any help would be much appreciated.

I am using CR 9 for my report
and SQL Server 2000 for the Stored Procedure. This stored procedure is lengthy, but not overly so, so if you want me to post it I will. But there is NO CURSOR in it, just 6 input parameters. Three of which are nullable.

The report was running fine last week, but when I revisited it and made some modifications it went kah-bloo-y on me. So I decided to go back to my original one that WAS working on Friday and now it's doing the same damn thing! Even though I have gone back to the SP that was also working fine on Friday. [ponder]

I was also getting an error that just said "Not Supported" when trying to amend the subreports that were part of this monster of a report.

Anyone ever see this weird behavior that might point me in a direction to my problem?

I'm at a complete loss, and will kiss the ground you walk on if you can lead me out of this mess. *ha*

Thanks in advance,
KrystlDuk
[ponytails2]
 
Sort by date Sort by votes
First, suppress al subreports and try it, then start unsuppressing untiol youi find the offending subreport (if it is one of the subs).

Also make sure that you do a Database->Verify Database after EACH change to an SP. If you palced a new one in, and it was bad, then palced the old one in, and did NOT verify the database agian, then it will still be broken.

-k
 
Upvote 0 Downvote
Well, I guess I should have written that I've tried the Verify Database route, I tried removing the subreports, one by one. I've even gone so far as to sart all over again. Just to see where the problem may ahve been. And even with a fresh start, it still gives errors...and like I said there are no cursors in my SP.
Oh, and then to top it off. Even when I start from scratch, I'm sometimes getting the CR32.exe error, that shuts me out of the system.

I am including my stored proc this time, maybe that is where the problem is. Although, it works just fine, error-free, quick, correct amount of records every time without fail. So I don't know how it's causing issues in Crystal...but I'm always learning, so maybe I've done something wrong after all. Here's the SP...<Warning...it's a long one>

Code: 
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE tp2.rsp_CoursesCompIncompRpt
@Start_Date 	smalldatetime, 
@End_Date 	smalldatetime,
@Course_Code	varchar(25),
@Super_User 	varchar(45),
@Business_Unit	varchar(80) = NULL,
@Region		varchar(255)= NULL

AS


--Create variables for "placeholders" for those employees that are not attached to a Course
DECLARE
@Course_Name	varchar(255),
@Course		varchar(255)


	--Create Place holders for input parameters
	IF @Region IS NULL
	BEGIN
		SET @Region = NULL
	END
	
	IF @Business_Unit IS NULL
	BEGIN
		SET @Business_Unit = NULL
	END



--Strip off time from date
SET @Start_Date = CONVERT(CHAR(10),@Start_Date,101)
SET @End_Date 	= CONVERT(CHAR(10),@End_Date,101)

IF @Super_User = 'All - Summary'
	BEGIN
		SET @Region = NULL
		SET @Business_Unit = NULL
	END

--CREATE ALL TEMP TABLES NEEDED

CREATE TABLE #EMPLOYEES_INFO
(
EmployeeName 		varchar(100),	--Last,First(#)EmployeeId		varchar(60),	--EmployeeId
JobTitle		varchar(80),	--Job Title abbr.
EmpStatus		varchar(25),	--Full or Part Time
Location		varchar(80),	--Location Code
CostCenter		varchar(25),	--Cost Center Code
Station_CostCenter	varchar(50),	--Loc-CostCenter
Region			varchar(255)	--Region Name
)

CREATE TABLE #COURSE_INFO
(
CourseId		char(20),	--Course ID
CourseName		varchar(255),	--Course Name
CourseCode		varchar(25),	--Course Code
Course			varchar(255)	--CourseCode/Name
)

CREATE TABLE #EMPLOYEE_COURSE_INFO
(
EmployeeId 		varchar(60),	--EmployeeId
CostCenter		varchar(25),	--Cost Center Code
Location		varchar(80),	--Location Code
CourseId		char(20),	--Course ID
CourseName		varchar(255),	--Course Name
CourseCode		varchar(25),	--Course Code
Course			varchar(255),	--CourseCode/Name
CompletionDate		smalldatetime	--Date completed
)

CREATE TABLE #FINAL_RESULTS(
EmployeeName 		varchar(100),	--Last,First(#)
EmployeeId		varchar(60),	--EmployeeId
JobTitle		varchar(80),	--Job Title abbr.
EmpStatus		varchar(25),	--Full or Part Time
Location		varchar(80),	--Location Code
CostCenter		varchar(25),	--Cost Center Code
Station_CostCenter	varchar(50),	--Loc-CostCenter
Region			varchar(255),	--Region Name
CourseName		varchar(255),	--Course Name
CourseCode		varchar(25),	--Course Code
Course			varchar(255),	--CourseCode/Name
CompletionDate		smalldatetime,	--Date completed
CompletedFlag		char(1),	--Y or N
Super_User 		varchar(45)  	--Detail/Summary


--Insert Data into temp table to hold the course info
	INSERT INTO #COURSE_INFO
	SELECT
	Course.[id]	               CourseID,
	substring(Course.title,1,80)   CourseName, 
	Course.course_no 	       CourseCode, 
	Course.course_no+' / '+
	 substring(Course.title,1,80)  Course
	FROM
	tp2.tpv_pub_courses    Course
	WHERE 	 Course.Course_no = @Course_Code
   UNION ALL
        SELECT
	id 		CourseId, 	--ProductId
	name		CourseName,	--ProductName,
	Part_no		CourseCode,	--ProductCode, 
	Product.Part_no+' / '+
 substring(Product.name,1,80)	Course	--Product
	FROM
	tp2.TPV_PUB_PRODUCT_CATALOG	Product
	WHERE 	Product.Part_no = @Course_Code


SET @Course_Name = (SELECT #COURSE_INFO.CourseName FROM #COURSE_INFO)
SET @Course	=  (SELECT #COURSE_INFO.Course FROM #COURSE_INFO)
/* ^^Sets the default course name and course (concatenated) for the query, 
	so that any employee who did NOT take the course is "assigned" a course name.
	This ensures that NO employees at the given Region, Business Unit, etc. provided 
	by the input parameters are left out.*/



   IF @Super_User = 'Regional - Summary'
	BEGIN
		/* Insert all employee information for Region in question */
	      	INSERT INTO #EMPLOYEES_INFO
		SELECT DISTINCT
	(Employee.lname+', '
	 +Employee.fname)+' ('
	 +RTRIM(Employee.employee_no)+')'  EmployeeName,
	RTRIM(Employee.employee_no)	   EmployeeId,
	ISNULL(JobType.[name],'')	   JobTitle,
	ISNULL(Employee.[Status],'')	   EmpStatus,
	ISNULL(Location.loc_name,'')	   Location,
	ISNULL(BusinessUnit.cost_center,'')CostCenter,
ISNULL(BusinessUnit.name2,'')	   Station_CostCenter,
	ISNULL(BusinessUnit.custom1,(
	RTRIM(LEFT(BusinessUnit.name2,
         (charindex('-',BusinessUnit.name2)-2)))))  Region
	FROM 	tp2.tpt_employees 	Employee
	INNER JOIN
		tp2.tpt_job_type 	JobType
	ON	Employee.jobtype_id = JobType.[id]
	INNER JOIN 
		tp2.tpt_company 	BusinessUnit
	ON	Employee.company_id = BusinessUnit.[id]
	INNER JOIN 
		tp2.tpt_locations 	Location
	ON	Employee.location_id = Location.[id]
	
	WHERE 	Employee.Status <> 'Terminated'
	AND 	JobType.job_type =  0
	AND	(RTRIM(BusinessUnit.custom1) = @Region)


	/*Insert employee id's of those who have completed the course in 
		question at the specified Region*/
		INSERT INTO #EMPLOYEE_COURSE_INFO
		SELECT DISTINCT
		RTRIM(Employee.employee_no)	EmployeeID,
		BusinessUnit.cost_center	CostCenter,
	        Location.loc_name		Location,
		Course.CourseId			CourseId,
		Course.CourseName	       CourseName, 
		Course.CourseCode              CourseCode, 
		Course.Course			Course,
		CourseCompleted.Acquired_on  CompletionDate
				
		FROM 	tp2.tpt_employees 	Employee
		INNER JOIN 
		    tp2.tpt_company      BusinessUnit
		ON  Employee.company_id = BusinessUnit.[id]
		INNER JOIN 
		    tp2.tpt_locations 	Location
		ON  Employee.location_id = Location.[id]
		INNER JOIN
	    tp2.tpv_pub_ed_prod_completed CourseCompleted
	ON  Employee.[id] = CourseCompleted.student_id
	        INNER JOIN
		#COURSE_INFO 			Course
		ON	
              CourseCompleted.product_id = Course.CourseId
		WHERE	(CONVERT(CHAR(10),CourseCompleted.Acquired_on,101)>= @Start_date
		AND 	 CONVERT(CHAR(10),CourseCompleted.Acquired_on,101) <= @End_Date)
		AND 	 Employee.Status <> 'Terminated'
		AND	 Course.CourseCode = @Course_Code
		AND	(RTRIM(BusinessUnit.custom1) = @Region)
	END
   ELSE
	/* Insert all employee information for Business Unit in question */
	      	INSERT INTO #EMPLOYEES_INFO
		SELECT DISTINCT
			(Employee.lname+', '
			 +Employee.fname)+' ('
			 +RTRIM(Employee.employee_no)+')'  EmployeeName,
			RTRIM(Employee.employee_no)	   EmployeeId,
			ISNULL(JobType.[name],'')	   JobTitle,
			ISNULL(Employee.[Status],'')	   EmpStatus,
			ISNULL(Location.loc_name,'')	   Location,
			ISNULL(BusinessUnit.cost_center,'')CostCenter,
			ISNULL(BusinessUnit.name2,'')	   Station_CostCenter,
			ISNULL(BusinessUnit.custom1,(
				RTRIM(LEFT(BusinessUnit.name2,(charindex('-',BusinessUnit.name2)-2))))
						     )   	Region
		
		FROM 	tp2.tpt_employees 	Employee
		INNER JOIN
			tp2.tpt_job_type 	JobType
		ON	Employee.jobtype_id = JobType.[id]
		INNER JOIN 
			tp2.tpt_company 	BusinessUnit
		ON	Employee.company_id = BusinessUnit.[id]
		INNER JOIN 
			tp2.tpt_locations 	Location
		ON	Employee.location_id = Location.[id]
		WHERE 	Employee.Status <> 'Terminated'
		AND 	JobType.job_type =  0
		AND	((@Business_Unit IS NULL) 
		OR 	(Location.loc_name = @Business_Unit))

	/*Insert employee id's of those who have completed the course in 
		question at the specified Business Unit*/
		INSERT INTO #EMPLOYEE_COURSE_INFO
		SELECT DISTINCT
			RTRIM(Employee.employee_no)	EmployeeID,
			BusinessUnit.cost_center	CostCenter,
			Location.loc_name		Location,
			Course.CourseId			CourseId,
			Course.CourseName		CourseName, 
			Course.CourseCode		CourseCode, 
			Course.Course			Course,
			CourseCompleted.Acquired_on	CompletionDate
		
		FROM 	tp2.tpt_employees 	Employee
		INNER JOIN 
			tp2.tpt_company 	BusinessUnit
		ON	Employee.company_id = BusinessUnit.[id]
		INNER JOIN 
			tp2.tpt_locations 	Location
		ON	Employee.location_id = Location.[id]
		INNER JOIN
			tp2.tpv_pub_ed_prod_completed 	CourseCompleted
		ON	Employee.[id] = CourseCompleted.student_id
		INNER JOIN
			#COURSE_INFO 			Course
		ON	CourseCompleted.product_id = Course.CourseId
		WHERE	(CONVERT(CHAR(10),CourseCompleted.Acquired_on,101) >= @Start_date 
		AND 	CONVERT(CHAR(10),CourseCompleted.Acquired_on,101) <= @End_Date)
		AND 	Employee.Status <> 'Terminated'
		AND	Course.CourseCode = @Course_Code
		AND	((@Business_Unit IS NULL) 
		OR 	(Location.loc_name = @Business_Unit))


--Insert Final dataset into temp table
	INSERT INTO #FINAL_RESULTS
		SELECT 
		EmployeeInfo.EmployeeName,
		EmployeeInfo.EmployeeID,
		EmployeeInfo.JobTitle,
		EmployeeInfo.EmpStatus,
		EmployeeInfo.Location,
		EmployeeInfo.CostCenter,
		EmployeeInfo.Station_CostCenter,
		ISNULL(EmployeeInfo.Region,'Multiple Use'),
		ISNULL(CourseInfo.CourseName,(SUBSTRING(@Course,(charindex ('/',@Course) + 2), LEN(@Course)))),
		ISNULL(CourseInfo.CourseCode,(RTRIM(LEFT(@Course,(charindex('/',@Course)-2))))),
		ISNULL(CourseInfo.Course,@Course),
		EmpCourseInfo.CompletionDate,
		CASE WHEN EmpCourseInfo.CompletionDate IS NULL THEN 'N' ELSE 'Y' END,
		@Super_User
		FROM 	#EMPLOYEES_INFO EmployeeInfo
		LEFT OUTER JOIN
			#EMPLOYEE_COURSE_INFO EmpCourseInfo
		ON	EmployeeInfo.EmployeeID = EmpCourseInfo.EmployeeID
		LEFT OUTER JOIN
			#COURSE_INFO	CourseInfo
		ON	CourseInfo.CourseId = EmpCourseInfo.CourseId

		
	
--Return data set to Crystal Reports
SELECT * FROM #FINAL_RESULTS

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Insulations504
  • Locked
  • Question
Crystal Reports duplicating invoices
Replies
1
Views
608
dgillz
dgillz
Ivan Kok
  • Locked
  • Question
Crystal Reports Runtime causing AS/400 DB2 Database ID to be disabled intermittently
Replies
0
Views
582
Ivan Kok
Ivan Kok
sal21
  • Locked
  • Question
create report without database in vb 6.0 and recordset
Replies
0
Views
612
sal21
sal21
Ray M
  • Locked
  • Question
Time In Care 1
Replies
6
Views
618
Ray M
Ray M
BTPJayW
  • Locked
  • Question
Verify Database Not Working Against Stored Procedure
Replies
1
Views
649
hilfy
hilfy

Part and Inventory Search

Sponsor

Back
Top