Error converting nvarchar to a column of..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error converting nvarchar to a column of..

Author

Topic

cokebaby666
Starting Member

7 Posts

Posted - 2005-03-10 : 19:20:54

Hi,
I am using a UDF which is giving me the following error when I run it in an access adp file:
"Error converting the nvarchar 'MyValue' to a column of data type int"

However when I run the function through SQLServer Query analyser I have no trouble at all. I've checked the source tables and the field in question is an nvarchar in both tables (I am joining on this value)

Any ideas?

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-10 : 19:31:05

More likely than not the udf is not formatted correctly and is trying to stick the data in question into the wrong column, or is not selecting properly from the source.
Post the code, let's have a look..
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

cokebaby666
Starting Member

7 Posts

Posted - 2005-03-10 : 19:37:35

two things that may be of interest: Access asks for the int arguments first, not in the order I've defined them. I guess I shoudl try swapping them round, and will. Secondly as I said before it works fine in SQLServer query analyser as follows:

print dbo.Fn_FindStandardTime('MBS-L',24,2,'proof')

here you go:
ALTER function dbo.Fn_FindStandardTime
(@DocID nvarchar(50),@units int,@OneTwo int,@OperationType nvarchar(50))
returns int
as
begin
declare @extrapages int
declare @cutoff int
declare @Result int
select @cutoff = (select P.FirstSectionCutoff from dbo.Price P inner join dbo.document D on P.documentID = D.documentid where D.DocumentID = @DocID)
if (@units > @cutoff)
set @extrapages = (@units - @cutoff)
else
set @extrapages = 0

select @result= case
WHEN (@OperationType='master input' and (@OneTwo =1)) then [StandardTimeInput1st]
WHEN (@OperationType='input' and (@OneTwo =1)) then D.StandardTimeInput1st + (@extrapages * D.StandardTimePageInput)
WHEN (@OperationType='input' and (@OneTwo =2)) then D.StandardTimeInput2nd + (@extrapages * D.StandardTimePageInput)
WHEN (@OperationType='proof') then D.StandardTimeProof + (@extrapages * D.StandardTimePageProof)
WHEN (@OperationType='braille') then D.StandardTimeBraille + (@extrapages * D.StandardTimePageBraille)
WHEN (@OperationType='audio record' or @OperationType='audio check') then D.StandardTimeAudio + (@extrapages * D.StandardTimePageAudio)
WHEN (@OperationType='fulfill') then D.StandardTimeFulfill + (@extrapages * D.StandardTimePageFulfill)
ELSE 0
end
from dbo.document D inner join dbo.price P on P.documentID = D.documentid
where D.documentID = @DocID

return @result
end

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-10 : 19:49:33

quote:
Access asks for the int arguments first, not in the order I've defined them. I guess I shoudl try swapping them round

Thats the problem....
The udf is fine. Where does the value 'MyValue' come in? Thats where the arguments are flipped around more likely than not.
ALSO!! If you are out of order coming from Access, and the column types that are flipped are different, that will cause this issue. Make sure you double check your column types after you rearrange things..
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

cokebaby666
Starting Member

7 Posts

Posted - 2005-03-10 : 19:58:14

Thanks Andy, works like a charm now!
The 'myvalue' was just a generic way of showing it was returning the value i'd passed in (should have said for DocID)
thanks again!

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-03-10 : 20:46:24

Yup,
I knew the 'MyValue' thing was a placecard. Just used it for continuity's sake. Glad to here you're all fixed up.
OH! Welcome aboard!

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources