C
CSDunn
Hello,
I have come across a problem with an Access 2000 or 2003 report where the
record source is a SQL Server 2000 database Proc when the Proc creates and
selects from a temporary table. The proc that I built was working fine, and
the report could 'see' the fields from the Proc until I introduced a
temporary table to the Proc to help with some functionality I needed.
In the 2000 ADP report (but not 2003), when I attempted to add the edited
Proc as a record source to the report, I got an error message that is
addressed in the Microsoft KB article ACC2000 as follows:
****************************************
You receive the following error:
Microsoft Access can't find the object 'Microsoft Access can't find the
object 'StoredProcedure1.'.'
*You misspelled the object name. Check for missing underscores(_) or other
punctuation, and make sure you didn't enter leading spaces.
*You tried to open a linked table, but the file containing the table isn't
on the path you specified. Use the Linked Table Manager to update the link
and point to the correct path.
Cause:
SQL Server 2000 was released over a year after Access 2000; consequently,
Access 2000 does not support all of the new features of SQL Server 2000, and
incompatibilities exist.
****************************************
The problem occurs in 2000 and 2003, but the error message does not appear
in 2003. In 2000, after I click the error messsage's OK button, I can see
that the field list for the report is empty. In 2003, when I create a report
in design view in an ADP created in 2003 and attempt to configure the record
source to a Proc with a temp table, no error message occurs, but the field
list for the report is still empty.
The resolution provided by the article was to apply Microsoft Office service
release 1/1a to 2000. That has been done for Office 2000 ( and SR2 has also
been applied), but the problem continues. In Office 2003, I don't get the
error message, but when I try to create a report in a new 2003 ADP using the
same Proc for the record source, the field list is still empty.
Is this a know problem with using SQL Server 2000 Procs containing temp
tables as the record source for reports? Is there a work around or solution
of some kind that I could implement?
The following will take you to a web page that shows the design and print
preview of the report: http://www.valverde.edu/home/policy/ReportLayout.htm
The following is the code of the Proc:
Create Procedure MM_StanResultsGradeSummRpt_sp
@LocGrade Nvarchar(6)
AS
Create Table #CurrentTeacherID
(
RecID smallint Identity(1,1) not null,
LastName varchar (20) not null,
FirstName varchar (20) not null,
TeacherID varchar(6) not null
)
--*****
Insert #CurrentTeacherID
(TeacherID, LastName, FirstName)
Select Distinct TD.TeacherID, TD.LastName, TD.FirstName
From Teacher_Data_Main TD
Inner Join Student_Data_Main SD On TD.TeacherID = SD.TeacherID
Inner Join tblLocation LC On SD.SchoolNum = LC.Location2
Inner Join tblMMrptStanResultsGrade RS On TD.TeacherID = RS.TeacherID
Where
LC.Location2 + SD.Grade = @LocGrade
and
Exists (Select TD.TeacherID From Teacher_Data_Main TD
Where TD.TeacherID = RS.TeacherID
and SD.Grade = RS.Grade)
Order By TD.LastName, TD.FirstName, TD.TeacherID
--******
Select
Location2,
Grade,
Max(Right(T1,Reverse(CharIndex(' ',Reverse(T1),1)))) as T1Percent,
Max(Left(T1,Len(T1)- CharIndex(' ',Reverse(T1),1))) as T1Name,
Max(Right(T2,Reverse(CharIndex(' ',Reverse(T2),1)))) as T2Percent,
Max(Left(T2,Len(T2)- CharIndex(' ',Reverse(T2),1))) as T2Name,
Max(Right(T3,Reverse(CharIndex(' ',Reverse(T3),1)))) as T3Percent,
Max(Left(T3,Len(T3)- CharIndex(' ',Reverse(T3),1)))as T3Name,
Max(Right(T4,Reverse(CharIndex(' ',Reverse(T4),1)))) as T4Percent,
Max(Left(T4,Len(T4)- CharIndex(' ',Reverse(T4),1)))as T4Name,
Max(Right(T5,Reverse(CharIndex(' ',Reverse(T5),1)))) as T5Percent,
Max(Left(T5,Len(T5)- CharIndex(' ',Reverse(T5),1)))as T5Name,
Max(Right(T6,Reverse(CharIndex(' ',Reverse(T6),1)))) as T6Percent,
Max(Left(T6,Len(T6)- CharIndex(' ',Reverse(T6),1)))as T6Name,
Max(Right(T7,Reverse(CharIndex(' ',Reverse(T7),1)))) as T7Percent,
Max(Left(T7,Len(T7)- CharIndex(' ',Reverse(T7),1)))as T7Name,
Max(Right(T8,Reverse(CharIndex(' ',Reverse(T8),1)))) as T8Percent,
Max(Left(T8,Len(T8)- CharIndex(' ',Reverse(T8),1)))as T8Name,
Max(Right(T9,Reverse(CharIndex(' ',Reverse(T9),1)))) as T9Percent,
Max(Left(T9,Len(T9)- CharIndex(' ',Reverse(T9),1)))as T9Name,
Max(Right(T10,Reverse(CharIndex(' ',Reverse(T10),1)))) as T10Percent,
Max(Left(T10,Len(T10)- CharIndex(' ',Reverse(T10),1)))as T10Name,
Max(Right(T11,Reverse(CharIndex(' ',Reverse(T11),1)))) as T11Percent,
Max(Left(T11,Len(T11)- CharIndex(' ',Reverse(T11),1)))as T11Name,
Max(Right(T12,Reverse(CharIndex(' ',Reverse(T12),1)))) as T12Percent,
Max(Left(T12,Len(T12)- CharIndex(' ',Reverse(T12),1)))as T12Name,
Max(Right(T13,Reverse(CharIndex(' ',Reverse(T13),1)))) as T13Percent,
Max(Left(T13,Len(T13)- CharIndex(' ',Reverse(T13),1)))as T13Name,
Max(Right(T14,Reverse(CharIndex(' ',Reverse(T14),1)))) as T14Percent,
Max(Left(T14,Len(T14)- CharIndex(' ',Reverse(T14),1)))as T14Name,
StanID,
Domain,
Strand,
StanNum,
StanDesc
From
(
Select
RS.Location2,
RS.Grade,
RS.Domain,
RS.Strand,
RS.StanNum,
RS.StanDesc,
Case When CT.RecID = 1 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T1,
Case When CT.RecID = 2 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T2,
Case When CT.RecID = 3 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T3,
Case When CT.RecID = 4 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T4,
Case When CT.RecID = 5 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T5,
Case When CT.RecID = 6 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T6,
Case When CT.RecID = 7 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T7,
Case When CT.RecID = 8 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T8,
Case When CT.RecID = 9 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T9,
Case When CT.RecID = 10 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T10,
Case When CT.RecID = 11 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T11,
Case When CT.RecID = 12 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T12,
Case When CT.RecID = 13 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T13,
Case When CT.RecID = 14 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T14,
RS.StanID
From tblMMrptStanResultsGrade RS
Inner Join #CurrentTeacherID CT On RS.TeacherID = CT.TeacherID
Where RS.Location2 + RS.Grade = @LocGrade
)as Inline
Group By Location2, Grade, StanID, Domain, Strand, StanNum, StanDesc
--*********
Drop table #CurrentTeacherID
****************************************************************************
***********
I have another message posted that goes into further specifics about the
what the code is doing. Please let me know if you require this information.
Though the Proc returns data just the way I want it, I just wonder if it
will work with an Access 2000 report.
Thank you for your help!
CSDunn
I have come across a problem with an Access 2000 or 2003 report where the
record source is a SQL Server 2000 database Proc when the Proc creates and
selects from a temporary table. The proc that I built was working fine, and
the report could 'see' the fields from the Proc until I introduced a
temporary table to the Proc to help with some functionality I needed.
In the 2000 ADP report (but not 2003), when I attempted to add the edited
Proc as a record source to the report, I got an error message that is
addressed in the Microsoft KB article ACC2000 as follows:
****************************************
You receive the following error:
Microsoft Access can't find the object 'Microsoft Access can't find the
object 'StoredProcedure1.'.'
*You misspelled the object name. Check for missing underscores(_) or other
punctuation, and make sure you didn't enter leading spaces.
*You tried to open a linked table, but the file containing the table isn't
on the path you specified. Use the Linked Table Manager to update the link
and point to the correct path.
Cause:
SQL Server 2000 was released over a year after Access 2000; consequently,
Access 2000 does not support all of the new features of SQL Server 2000, and
incompatibilities exist.
****************************************
The problem occurs in 2000 and 2003, but the error message does not appear
in 2003. In 2000, after I click the error messsage's OK button, I can see
that the field list for the report is empty. In 2003, when I create a report
in design view in an ADP created in 2003 and attempt to configure the record
source to a Proc with a temp table, no error message occurs, but the field
list for the report is still empty.
The resolution provided by the article was to apply Microsoft Office service
release 1/1a to 2000. That has been done for Office 2000 ( and SR2 has also
been applied), but the problem continues. In Office 2003, I don't get the
error message, but when I try to create a report in a new 2003 ADP using the
same Proc for the record source, the field list is still empty.
Is this a know problem with using SQL Server 2000 Procs containing temp
tables as the record source for reports? Is there a work around or solution
of some kind that I could implement?
The following will take you to a web page that shows the design and print
preview of the report: http://www.valverde.edu/home/policy/ReportLayout.htm
The following is the code of the Proc:
Create Procedure MM_StanResultsGradeSummRpt_sp
@LocGrade Nvarchar(6)
AS
Create Table #CurrentTeacherID
(
RecID smallint Identity(1,1) not null,
LastName varchar (20) not null,
FirstName varchar (20) not null,
TeacherID varchar(6) not null
)
--*****
Insert #CurrentTeacherID
(TeacherID, LastName, FirstName)
Select Distinct TD.TeacherID, TD.LastName, TD.FirstName
From Teacher_Data_Main TD
Inner Join Student_Data_Main SD On TD.TeacherID = SD.TeacherID
Inner Join tblLocation LC On SD.SchoolNum = LC.Location2
Inner Join tblMMrptStanResultsGrade RS On TD.TeacherID = RS.TeacherID
Where
LC.Location2 + SD.Grade = @LocGrade
and
Exists (Select TD.TeacherID From Teacher_Data_Main TD
Where TD.TeacherID = RS.TeacherID
and SD.Grade = RS.Grade)
Order By TD.LastName, TD.FirstName, TD.TeacherID
--******
Select
Location2,
Grade,
Max(Right(T1,Reverse(CharIndex(' ',Reverse(T1),1)))) as T1Percent,
Max(Left(T1,Len(T1)- CharIndex(' ',Reverse(T1),1))) as T1Name,
Max(Right(T2,Reverse(CharIndex(' ',Reverse(T2),1)))) as T2Percent,
Max(Left(T2,Len(T2)- CharIndex(' ',Reverse(T2),1))) as T2Name,
Max(Right(T3,Reverse(CharIndex(' ',Reverse(T3),1)))) as T3Percent,
Max(Left(T3,Len(T3)- CharIndex(' ',Reverse(T3),1)))as T3Name,
Max(Right(T4,Reverse(CharIndex(' ',Reverse(T4),1)))) as T4Percent,
Max(Left(T4,Len(T4)- CharIndex(' ',Reverse(T4),1)))as T4Name,
Max(Right(T5,Reverse(CharIndex(' ',Reverse(T5),1)))) as T5Percent,
Max(Left(T5,Len(T5)- CharIndex(' ',Reverse(T5),1)))as T5Name,
Max(Right(T6,Reverse(CharIndex(' ',Reverse(T6),1)))) as T6Percent,
Max(Left(T6,Len(T6)- CharIndex(' ',Reverse(T6),1)))as T6Name,
Max(Right(T7,Reverse(CharIndex(' ',Reverse(T7),1)))) as T7Percent,
Max(Left(T7,Len(T7)- CharIndex(' ',Reverse(T7),1)))as T7Name,
Max(Right(T8,Reverse(CharIndex(' ',Reverse(T8),1)))) as T8Percent,
Max(Left(T8,Len(T8)- CharIndex(' ',Reverse(T8),1)))as T8Name,
Max(Right(T9,Reverse(CharIndex(' ',Reverse(T9),1)))) as T9Percent,
Max(Left(T9,Len(T9)- CharIndex(' ',Reverse(T9),1)))as T9Name,
Max(Right(T10,Reverse(CharIndex(' ',Reverse(T10),1)))) as T10Percent,
Max(Left(T10,Len(T10)- CharIndex(' ',Reverse(T10),1)))as T10Name,
Max(Right(T11,Reverse(CharIndex(' ',Reverse(T11),1)))) as T11Percent,
Max(Left(T11,Len(T11)- CharIndex(' ',Reverse(T11),1)))as T11Name,
Max(Right(T12,Reverse(CharIndex(' ',Reverse(T12),1)))) as T12Percent,
Max(Left(T12,Len(T12)- CharIndex(' ',Reverse(T12),1)))as T12Name,
Max(Right(T13,Reverse(CharIndex(' ',Reverse(T13),1)))) as T13Percent,
Max(Left(T13,Len(T13)- CharIndex(' ',Reverse(T13),1)))as T13Name,
Max(Right(T14,Reverse(CharIndex(' ',Reverse(T14),1)))) as T14Percent,
Max(Left(T14,Len(T14)- CharIndex(' ',Reverse(T14),1)))as T14Name,
StanID,
Domain,
Strand,
StanNum,
StanDesc
From
(
Select
RS.Location2,
RS.Grade,
RS.Domain,
RS.Strand,
RS.StanNum,
RS.StanDesc,
Case When CT.RecID = 1 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T1,
Case When CT.RecID = 2 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T2,
Case When CT.RecID = 3 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T3,
Case When CT.RecID = 4 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T4,
Case When CT.RecID = 5 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T5,
Case When CT.RecID = 6 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T6,
Case When CT.RecID = 7 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T7,
Case When CT.RecID = 8 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T8,
Case When CT.RecID = 9 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T9,
Case When CT.RecID = 10 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T10,
Case When CT.RecID = 11 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T11,
Case When CT.RecID = 12 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T12,
Case When CT.RecID = 13 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T13,
Case When CT.RecID = 14 Then RS.TFirstName + ' ' + RS.TLastname + ' ' +
Cast(RS.SPercentage as Varchar(4)) End as T14,
RS.StanID
From tblMMrptStanResultsGrade RS
Inner Join #CurrentTeacherID CT On RS.TeacherID = CT.TeacherID
Where RS.Location2 + RS.Grade = @LocGrade
)as Inline
Group By Location2, Grade, StanID, Domain, Strand, StanNum, StanDesc
--*********
Drop table #CurrentTeacherID
****************************************************************************
***********
I have another message posted that goes into further specifics about the
what the code is doing. Please let me know if you require this information.
Though the Proc returns data just the way I want it, I just wonder if it
will work with an Access 2000 report.
Thank you for your help!
CSDunn