February 8, 2010 at 9:47 am
I've got the following code in our 2008 upgrade test system which contains temporary tables and views. The views all contain tables from our remote server (2005).
IF Object_Id('tempdb..#MaxSCE') IS NOT NULL
BEGIN
DROP TABLE #MaxSCE
END
CREATE TABLE #MaxSCE (
sce_scjc NVARCHAR(15) PRIMARY KEY,
MaxSeq NCHAR(2)
)
--Get the max SCE record for 08/09
INSERT INTO #MaxSCE
SELECT StudentCourseJoinCode, MAX(SequenceNumber) AS MaxSeq
FROM sits.vwStudentCourseEnrolment sce JOIN sits.vwRegistrationCourse rc
ON sce.CourseCode = rc.CourseCode
WHERE sce.AcademicYear = '08/09'
AND sce.Occurence <> 'H2' --Exclued H2 as some on H2 will be calculated as Expected but since H2 finish in December they won't achieve by the summer
AND sce.EnrolmentStatusCode NOT IN ('T','P','PW','NE','NS')
AND rc.ExternalReturnFormat = 'M'
GROUP BY StudentCourseJoinCode
IF Object_Id('tempdb..#CreditAtYearStart') IS NOT NULL
BEGIN
DROP TABLE #CreditAtYearStart
END
--Using temp tables here makes the overall query run faster (~1 sec) rather then joining the 2 queries directly (~3 mins)
CREATE TABLE #CreditAtYearStart(
sce_scjc NVARCHAR(15) PRIMARY KEY,
MaxSeq NCHAR(2),
PreviousCredit NUMERIC(7,2)
)
INSERT INTO #CreditAtYearStart
--Total up credits from past completed modules and advanced standing
SELECT sce_scjc, MaxSeq, SUM(Credit) AS TotalCredit
FROM(
SELECT sce_scjc, MaxSeq, ModuleCode , MAX(ISNULL(AchievedCredit,0)) AS Credit
FROM #MaxSCE LEFT OUTER JOIN sits.vwStudentModuleResults
ON sce_scjc = StudentProgrammeRouteCode COLLATE Latin1_General_CI_AS
AND ((AssType <> 'SMC' AND '20' + LEFT(AcademicYearCode,2) < 2008) OR AssType = 'SMC')
GROUP BY sce_scjc, MaxSeq, ModuleCode
) AS MaxModuleCredits
GROUP BY sce_scjc, MaxSeq
IF Object_Id('tempdb..#CreditToBeGained') IS NOT NULL
BEGIN
DROP TABLE #CreditToBeGained
END
CREATE TABLE #CreditToBeGained(
sce_scjc NVARCHAR(15) PRIMARY KEY,
MaxSeq NCHAR(2),
CreditToTake NUMERIC(7,2)
)
INSERT INTO #CreditToBeGained
--Total up the amount of credits the student is studying in the current year
SELECT sce_scjc, MaxSeq, SUM(ModuleCredits) AS ModuleCredits
FROM
(
SELECT sce_scjc, MaxSeq, ModuleCode , MAX(ISNULL(TakenCredit,0)) AS ModuleCredits
FROM #MaxSCE LEFT OUTER JOIN sits.vwStudentModuleResults
ON sce_scjc = StudentProgrammeRouteCode COLLATE Latin1_General_CI_AS
AND AssType <> 'SMC'
AND AcademicYearCode = '08/09'
GROUP BY sce_scjc, MaxSeq, ModuleCode
) AS MaxModuleResults
GROUP BY sce_scjc, MaxSeq
IF Object_Id('tempdb..#AchievedCredit') IS NOT NULL
BEGIN
DROP TABLE #AchievedCredit
END
CREATE TABLE #AchievedCredit(
sce_scjc NVARCHAR(15) PRIMARY KEY,
MaxSeq NCHAR(2),
AchievedCredit NUMERIC(7,2)
)
INSERT INTO #AchievedCredit
SELECT sce_scjc, MaxSeq, SUM(AchievedCredit)
FROM
(
SELECT sce_scjc, MaxSeq, ModuleCode , MAX(ISNULL(AchievedCredit,0)) AS AchievedCredit
FROM #MaxSCE LEFT OUTER JOIN sits.vwStudentModuleResults
ON sce_scjc = StudentProgrammeRouteCode COLLATE Latin1_General_CI_AS
AND AssType <> 'SMC'
AND AcademicYearCode = '08/09'
GROUP BY sce_scjc, MaxSeq, ModuleCode
) AS MaxModuleResults
GROUP BY sce_scjc, MaxSeq
--Dataset to return
SELECT cs.sce_scjc, cs.PreviousCredit, cg.CreditToTake, ac.AchievedCredit, rc.Credits, sce.HistoricFacultyCode,
sce.HistoricFaculty, sce.HistoricDepartmentCode
FROM #CreditAtYearStart cs JOIN #CreditToBeGained cg
ON cs.sce_scjc = cg.sce_scjc
JOIN #AchievedCredit ac
ON cg.sce_scjc = ac.sce_scjc
JOIN sits.vwStudentCourseEnrolment sce
ON ac.sce_scjc = StudentCourseJoinCode COLLATE Latin1_General_CI_AS
AND ac.MaxSeq = SequenceNumber COLLATE Latin1_General_CI_AS
JOIN sits.vwRegistrationCourse rc
ON rc.CourseCode = sce.CourseCode --Returns without this join and the join on the line below
and rc.RouteCode = sce.EnrolmentRouteCode
AND sce.Occurence = rc.CourseBlockOccurrence
AND sce.AcademicYear = rc.CourseBlockOccurrenceAcademicYear
AND sce.EnrolmentLevel = rc.CourseBlock
AND rc.AwardCode <> 'ROA'
LEFT OUTER JOIN sits.SAREAward awd
ON rc.AwardCode = awd.AwardCode COLLATE Latin1_General_CI_AS
LEFT OUTER JOIN sits.vwStudentAward sa
ON sce.StudentCourseJoinCode = sa.SPRCode
AND sce.AcademicYear = sa.AcademicYear
AND sa.AgreedAwardCode <> 'ROA'
AND sa.StatusCode IN ('A','CONF')
DROP TABLE #MaxSCE
DROP TABLE #CreditAtYearStart
DROP TABLE #CreditToBeGained
DROP TABLE #AchievedCredit
When this code is run I get the following error
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Col3432'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1007.sce_efid" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1007.sce_occl" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1007.sce_blok" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1007.sce_crsc" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1007.sce_ayrc" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1007.sce_seq2" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Tbl1007.sce_scjc" could not be bound.
After realising that this looked like it was something to do a query that was being run on the remote server, I decided to run the profiler to see if I could find anything odd - and indeed I did. My 2008 server was telling the remote server to run the following
SELECT "Tbl1007"."sce_scjc","Tbl1007"."sce_seq2","Tbl1007"."sce_ayrc","Tbl1007"."sce_crsc","Tbl1007"."sce_blok","Tbl1007"."sce_occl","Tbl1007"."sce_stac","Tbl1007"."sce_efid","Col4130"
FROM "sipr"."dbo"."srs_crs" "Tbl1076"
As you can see, the above code will never work as its asking to get the columns from "Tbl1007" when the table in the FROM is "Tbl1076". This isn't just a case of an incorrect alias the table in the FROM clause is completely wrong.
I've noticed that if I take out 2 lines in the ON clause after "JOIN sits.vwRegistrationCourse" that the code runs (but obviously the data is incorrect).
I've tried droping and recreating the views but that hasn't helped. This code runs perfectly in 2005.
Can anyone explain why the remote server is being asked to run garbage SQL?
Thanks,
Ben
February 8, 2010 at 12:08 pm
Create your tables using real table names, not tempdb names. I would create them in the current DB or another staging DB specic to this purpose.
If you must use tempdb, you will have to have permission to create tables (be a member of dbowner role)
The probability of survival is inversely proportional to the angle of arrival.
February 8, 2010 at 5:12 pm
1. Add in the specific columns in your insert statements - even though you don't need to.
2. Us AS for all returned columns - even if you don't need to
The above can simply make it clearer to read the code and probably won't help but may end shedding some light on the issue.
Check the views outside of the procedure - make sure you can open, select, etc. I am sure you have already done so but somewhere something is throwing a monkey wrench into this.
Delete the tempdb tables by name - use dbo.#xxx to create them - just in case for some bizarre reason you are picking up another temp table based on owner name..
Break it into small pieces - if the view works by itself and not in the procedure then something outside of my WAGs
I can't explain the issue but hope this gives you some food for thought,
February 9, 2010 at 2:16 am
I have the sysadmin server role so there shouldb't be any permission issues with the temp tables. I don't really want to create proper tables for this as that seems like a bit of a waste given that I don't need them for anything else.
The views all work fine and putting "dbo." infront of all the temp table names and specificying the columns in the inserts makes no difference.
February 9, 2010 at 7:06 am
I seemed to have got this working again now. I decided to change the collations used in the query so that the collation SQL_Latin1_General_CP1_CI_AS was used. This is the collation of the remote server - previously I was using the collation of the local server.
I'm happy that this now works, but I'd love to hear any suggestions as to why it has worked.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply