SQLTeam.com | Weblogs | Forums

Invalid character value for cast specification SQL 2016

#1

have a webpage that calls a stored procedure, it worked fine in sql 2005 but we upgraded to MS SQL 2016 last week and now I get the "Invalid character value for cast specification ..". nothing has changed in the data table.

any ideas on why and how to resolve..

Note, I am not an expert..

Thanks in advance

#2

To update this a bit, I took the query out of the stored procedure and stuck it directly in the web page code and it runs without a problem.

so this does not work

but this does work

<CFPROCPARAM TYPE="IN" CFSQLTYPE=CF_SQL_INTEGER VALUE="#Data[nC]#"

the difference being I took out the

DBVARNAME=@iLoc>

#3

this is kinda confusing. can you provide some more details and maybe DDL and what you've tried?

#4

Sorry not sure what happened so will try the post again.
This does NOT work.
cfstored proc procedure="sp_ReturnLeases" DATASOURCE="Wells" RETURNCODE="NO"
CFPROCRESULT NAME = OOPS4
CFPROCPARAM TYPE="IN" CFSQLTYPE=CF_SQL_INTEGER VALUE="#Data[nC]#" DBVARNAME=@iLoc

but this does

cfstored proc procedure="sp_ReturnLeases" DATASOURCE="Wells" RETURNCODE="NO"
CFPROCRESULT NAME = OOPS4
CFPROCPARAM TYPE="IN" CFSQLTYPE=CF_SQL_INTEGER VALUE="#Data[nC]#"

I take out hte dbvarname=@iloc which used to work in sql 2005 but not in 2016. But I dont know why it no longer works.