Errors after upgrading to SQL 2008

Last Post 18 Jan 2010 05:01 PM by sarkisp. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
sarkisp
New Member
New Member

--
18 Jan 2010 10:51 AM
hello,

I am upgrading my clients from MSDE 2000 to SQL Express 2008.
Everything seems to be working fine except one thing.

Some procedures generate random errors while trying to execute.
These error never popped up in 2000 and I think error message in 2008 is kind of misleading.

here is the error:

"Schema changed after the target table was created".
It start giving me headache with one procedure which was just a select statement with 4-5 joins.
I had no other choice as to temporarily put it in dynamic statement and pass it from program.

Now it start giving error on other procedure which is more complex and uses few temporary tables to manage data. Nothing ever get changed in code and no schema has changed. I just ran out of all options, cannot understand what is actual problem. My problem is that I already converted about 30 clients and stopped once realized they start getting error. Any help appreciated.

thank you, Paul.
sarkisp
New Member
New Member

--
18 Jan 2010 05:01 PM
hi Russel,

thanks for reply: I am including entire procedure: Hope this will give you some clue.

Create Procedure dbo.spGetCode

@PatientID int,
@IDNumber varchar(9),
@DateStart datetime


AS

SET NOCOUNT ON

BEGIN

Select Distinct I03.KDCx, R.IDNumber
Into #Inter_PatientHistory
From DataBank.dbo.DataInterIDNumberKDC I03
Join LogFile R
On I03.IDNumberx = R.IDNumber
Where R.PatientID = @PatientID
And isnull(R.Status, '') <> 'D'
And R.LogDate >= @DateStart

Select Distinct D01.KDC, D01.Class, PK.IDNumber
Into #PatientClass From DataBank.dbo.DataInterKDCClass D01
Join #Inter_PatientHistory PK
On D01.KDC = PK.KDCx

Select Distinct D01.KDC, D01.Class, @IDNumber as 'IDNumber'
Into #IDNumberKey
From DataBank.dbo.DataInterKDCClass D01
Join DataBank.dbo.DataInterIDNumberKDC I03
on I03.KDCx = D01.KDC
Where I03.IDNumberx = @IDNumber

Select Distinct P.Class as 'Class1', I.Class as 'Class2',
'00000' as 'Ex1', '00000' as 'Ex2', P.IDNumber as 'IDNumber1', I.IDNumber as 'IDNumber2',
P.IDNumber as 'IDNumber1x', I.IDNumber as 'IDNumber2x', 0 as 'Switched'
Into #IntClass
from #PatientClass P
join #IDNumberKey I
on P.Class <> I.Class

-- switch columns. make first columns to have always lower number
Update #Intclass
Set Ex1 = Class1, Ex2 = Class2,
IDNumber1x = IDNumber1, IDNumber2x = IDNumber2
Where Class1 > Class2

update #Intclass
Set Class1 = Ex2, Class2 = Ex1,
IDNumber1 = IDNumber1x, IDNumber2 = IDNumber2x, Switched = 1
Where Class1 > Class2

Select M01.*, D1.BrandName as 'Brand1', D2.Brandname as 'Brand2', I.IDNumber1, I.IDNumber2, 0 as 'SortOrder'
Into #Inter_Final
From DataBank.dbo.DataInterMonograph M01
Join #IntClass I
on M01.ClassCode1 = I.Class1
and M01.ClassCode2 = I.Class2
Left Join ProductRoot D1
on D1.IDNumber = I.IDNumber1
Left Join ProductRoot D2
on D2.IDNumber = I.IDNumber2
Where I.Switched = 0

Insert Into #Inter_Final
Select M01.*, D2.BrandName as 'Brand1', D1.Brandname as 'Brand2', I.IDNumber2 as 'IDNumber1',
I.IDNumber1 as 'IDNumber2', 0
From DataBank.dbo.DataInterMonograph M01
Join #IntClass I
on M01.ClassCode1 = I.Class1
and M01.ClassCode2 = I.Class2
Left Join ProductRoot D1
on D1.IDNumber = I.IDNumber1
Left Join ProductRoot D2
on D2.IDNumber = I.IDNumber2
Where I.Switched = 1

Update #Inter_Final Set SortOrder = 1 Where Severity = 'Major'
Update #Inter_Final Set SortOrder = 2 Where Severity = 'Moderate'
Update #Inter_Final Set SortOrder = 3 Where Severity = 'Minor'

Select * From #Inter_Final Order By SortOrder

Drop Table #Inter_PatientHistory
Drop Table #PatientClass
Drop Table #IntClass
Drop Table #IDNumberKey

Drop Table #Inter_Final

END
sarkisp
New Member
New Member

--
18 Jan 2010 06:02 PM
hi Russell, I wanted add little bit more about error: Exact error is saying:
Schema changed after the target table was created. Rerun the Select Into query.

looks like it does not like Select Into statement. worst part of it, I cannot recreate it on my side, it happens randomly with different clients.


Acceptable Use Policy
---