March 31, 2008 at 1:22 am
Hi
I've been trying to solve the following issue after moving database:
Created new database on local server than used restore to restore it from a backup from the production server.
Both on SQL Server2000.
The database and stored procs make extensive use of temp tables and user defined data types.
After the move I started getting the following exception from one of the procs:
Conversion failed when converting the nvarchar value 'EXCL' to data type int.
I have solved the above issue by restoring master and msdb using backups from the source server.
Now I have to move the above database to SQL Server 2005
Same as above restored from backup, plus recreated the users etc.
Most procs work on the 2005 however I'm getting the same exception from the same proc.
Conversion failed when converting the nvarchar value 'EXCL' to data type int.
Can anyone suggest a way of sorting the above issue on the 2005 server?
Regards
Raf
March 31, 2008 at 1:27 am
Is this stored procedure accessing the master or the msdb databases?
It seems strange that restoring the master and msdb database on sql 2000 fixed your problem, and it would suggest that the procedure is accessing them.
In either case, I suggest you figure out where in the procedure the problem is happening and you troubleshoot the problem there.
March 31, 2008 at 1:36 am
Hi Karl
Thanks for your response. I think the problem is related to conversion issue when comparing values in columns in two tables one being a tmp table. Tried debugging the proc but it's a very complex one and the exception actually happens in another stored proc that the first one calls.
But the thing is the procs work on both the production 2000 server and now the local 2000 server. I'm hoping there is some setting I can play with. Or sys table I have to add....
Raf
March 31, 2008 at 1:49 am
Check the sort order/collation for your restored database and check it for your master database. It's possible you've installed SQL Server 2005 with a different collation to what you were using on SQL Server 2000. When you did the restore, the database retained its collation, which is now different to the collation on the system databases.
select databasepropertyex('master','Collation')
March 31, 2008 at 2:58 am
Hi Karl
It's not a collation issue. Have ensured all databases use same collation.
Thanks for the suggestion though.
Raf
March 31, 2008 at 3:19 am
Hi Raf,
That's a shame because it was my best guess as to what was going wrong. Just to be sure, I wouldn't use sp_helpsort to confirm the collation. Use sp_helpdb to check the collation for all databases and see the status column. You may well have been check this anyway but I thought I'd mention it, just in case.
Not sure what else to suggest apart from tracking where exactly the problem is occurring. Now that you're on SQL Server 2005 you can modify the procedures to utilise TRY...CATCH statements for error handlingl, which might make it easier to find the problem.
March 31, 2008 at 4:12 am
Thanks Karl
will give it a go.
Regards
Raf
March 31, 2008 at 6:46 pm
Sounds like you have an implict conversion that SQL 2000 did not mind, but 2005 is throwing a fit. SQL 2005 is a lot more strict that 2000 when it comes to syntax.
You need to go through that stored procedure and validate the code.
March 31, 2008 at 6:57 pm
Hi Adam
Thanks for the replay. Do you think there could be some permissions or other settings that I need to apply to the master database for example, that would sort this out?
The procs we are talking about are 100s of lines long. The use dynamic sql and call other procs.
Debugging is a nightmare. The exception returns the line number but it's incorrect.
Any furthere ideas?
Also when you say fix the code what precisely do you have in mind?
Raf
March 31, 2008 at 7:31 pm
Unfortunately, I think the stored procedure will have to be modified. I would open the stored procedure and do a search for 'EXCL'. There may be 100s of lines, but 'EXCL' should appear infrequently.
Also, you may want to try running the upgrade advisor to validate all your 2000 code will work in 2005.
March 31, 2008 at 8:26 pm
I've run the upgrade advisor and it didn't mention anything about this proc or the user defined types.
As for the string constant it's a part of the query string passed to it. It's a used defined type that maps to nvarchar(4000).
I can't really figure how it's getting compared to an int value.
there are some statements like if @param = 2param2. Where the other param is usually a char or varchar. Nothing to do with ints it seems...
I tell you I'm in pain...
Raf
March 31, 2008 at 8:27 pm
I've run the upgrade advisor and it didn't mention anything about this proc or the user defined types.
As for the string constant it's a part of the query string passed to it. It's a used defined type that maps to nvarchar(4000).
I can't really figure how it's getting compared to an int value.
there are some statements like if @param = 2param2. Where the other param is usually a char or varchar. Nothing to do with ints it seems...
I tell you I'm in pain...
Raf
April 1, 2008 at 6:07 am
I worked out what causes the exception here it is- the statement in the stored proc:
------------------------------------------
insert #SIZES_WORK
(
ProfileItemTypeCode,
MinMaxCode,
PreferenceConversionID,
ProfileItemTypeGroupID
)
select pit.ProfileItemTypeCode,
nm_2,
convert(int,vl),
pit.ProfileItemTypeGroupID
from #PARAMS p,
PROFILE_ITEM_TYPE pit
where pit.ProfileItemTypeMethodCode = 'CONV' -- Size Conversion
and p.nm_1 = pit.ProfileItemTypeCode
------------------------------------------------
more precisely it's the call to:
convert(int,vl)
The actual exception being:
"Conversion failed when converting the nvarchar value 'EXCL' to data type int."
PLEASE NOTE this very proc threw some exception on sql Server 2000 before I restored the master db.
So something in the master db makes convert(int, 'EXCL') work?
-----------------------------------------------
where the temp tables definitions are:
create table #PARAMS
(
nm n_desc_tp not null,
nm_1 n_name_tp null,
nm_2 n_name_tp null,
ProfileSkillTypeID fident_tp null,
ProfileSkillValueID fident_tp null,
vl nvarchar(3550) null
)
create table #SIZES_WORK
(
ProfileItemTypeCode code_tp not null,
MinMaxCode code_tp not null,
PreferenceConversionID fident_tp not null,
ProfileItemTypeGroupID fident_tp null,
PreferenceConversionSeq seq_tp null
)
-----------------------------------------
as you can see vl is actually declared as nvarchar(3550)
The other types:
n_desc_tp map to nvarchar(255)
n_name_tp to nvarchar(250)
fident_tp to int
code_tp char(4)
seq_tp to numeric.
Anyhow
#PARAMS.vl can hold varius things such as first name, last name, city name, four letter gender code, or some numerical values etc...
In this particullar case the value in vl makes no difference to the outcome of the query. I can get it to work fine, by
substituting and numerical value for vl. So as long as convert(int,vl) doesn't crash it's OK.
So for now I've fixed the problem by thesting the vl in
isnumeric(@vlValue)
before actually shoving it into convert(int, vl).
Thanks for all your help I really appreciate it and if anyone else has anything to add on this issue I'll be interested to read it.
Regards
April 1, 2008 at 6:57 am
If restoring the Master resolved your issue, then you have setting that is different between instances. The Master database stores all system information for an instance. The meta data includes logins, end points, linked servers, and configurations settings.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply