'Error converting data type varchar to int' : Solution

Here is the solution to this error message if anyone gets one in the future...It took me about two days to figure out.

This error occurs if you save your stored procedure before execute it. Always execute first before you save. If you save before executing, the server will store its own defaults, usually integers.

This error aslo occurs if your datatypes do not match when passing values from code into the variables of a stored procedure.

It also occurs if the datatypes in your sql file do not match those of the original stored procedure.

You can check to see if the file saved in the "Projects" folder matches with the original by doing the following:

Expand Database, Expand Programmability, Expand Stored Procedures, Expand (Your Stored Procedure), Expand Parameters. Read the datatypes that are revealed in the tree.

Then go to File>Open>Projects>(save sql file). Click open.

View the datatypes in the file. If the datatypes in the file do not match the datatypes in the tree, what you must do to correct, as one solution, is delete both the file and stored procedure.

Then create a new stored procedure by right clicking the stored procedure node. Rewrite the store procedure, execute and then save. Everything should be okay.

Hi,

Thanks that you come back to the forums and want to give something of your solution to your actual problem back.

But I don′t know what your actual problem was (as you did not mentioned it clearly). Perhaps we could summarize this a bit more like Problem: xxxx, Solution: xxx. But from the information given I cannot tell what your actual problem was. I can′t even see why you do all the saves and deletes :-) ?

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

Error converting a text field to a datetime datatype, who can figure this anyone??

Hello,
I'm trying to convert data on a text field to datetime datatype. This data
has invalid dates such as 10/30, 2/31235, 32/1/2004, 345/2345 etc.
I have been trying to convert whatever I can but I keep getting errors when
I perform a
SELECT CONVERT(datetime, text_field) FROM Old_data
Obviously my select statemt is abruptly terminated when I hit one of these
records with a invalid date.
I tried looping throught the records using a curson and try to capture and
check for errors:
SET @.user_1_date = convert(datetime, @.USER_1)
IF @.@.ERROR <> 0
BEGIN
PRINT 'ERROR FOUND'
END
ELSE
BEGIN
print 'NO ERROR FOUND'
END
I'm using PRINT for simplicity but the conversion error can not be
controlled through this method. SQL halts when it tries to convert the date.
I would love to be able to trap the error and skip the record and move on to
the new one. The bulk of the data is good, but there are over 175,000
records and I can not do this manually.
Any thoughts or suggestions?
ThanksAsked and answered by lw in the .programming group. Please don't
multi-post.
"Sd" <nowhere@.sadfs.com> wrote in message
news:3ac3890e730ead0e80076b10ff132aaa@.ne
ws.teranews.com...
> Hello,
> I'm trying to convert data on a text field to datetime datatype. This data
> has invalid dates such as 10/30, 2/31235, 32/1/2004, 345/2345 etc.
> I have been trying to convert whatever I can but I keep getting errors
when
> I perform a
> SELECT CONVERT(datetime, text_field) FROM Old_data
> Obviously my select statemt is abruptly terminated when I hit one of these
> records with a invalid date.
> I tried looping throught the records using a curson and try to capture and
> check for errors:
> SET @.user_1_date = convert(datetime, @.USER_1)
> IF @.@.ERROR <> 0
> BEGIN
> PRINT 'ERROR FOUND'
> END
> ELSE
> BEGIN
> print 'NO ERROR FOUND'
> END
> I'm using PRINT for simplicity but the conversion error can not be
> controlled through this method. SQL halts when it tries to convert the
date.
> I would love to be able to trap the error and skip the record and move on
to
> the new one. The bulk of the data is good, but there are over 175,000
> records and I can not do this manually.
> Any thoughts or suggestions?
> Thanks
>
>|||Sd (nowhere@.sadfs.com) writes:
> I have been trying to convert whatever I can but I keep getting errors
> when I perform a
> SELECT CONVERT(datetime, text_field) FROM Old_data
> Obviously my select statemt is abruptly terminated when I hit one of these
> records with a invalid date.
> I tried looping throught the records using a curson and try to capture and
> check for errors:
But since SQL Server thinks conversion error is reason enough for aborting
the batch, that does not fly.
But this should do:
SELECT CASE WHEN is_date(text_field) = 1
THEN convert(datetime, text_field)
END
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Error converting a text field to a datetime datatype, who can figure this anyone??

Hello,
I'm trying to convert data on a text field to datetime datatype. This data
has invalid dates such as 10/30, 2/31235, 32/1/2004, 345/2345 etc.
I have been trying to convert whatever I can but I keep getting errors when
I perform a
SELECT CONVERT(datetime, text_field) FROM Old_data
Obviously my select statemt is abruptly terminated when I hit one of these
records with a invalid date.
I tried looping throught the records using a curson and try to capture and
check for errors:
SET @.user_1_date = convert(datetime, @.USER_1)
IF @.@.ERROR <> 0
BEGIN
PRINT 'ERROR FOUND'
END
ELSE
BEGIN
print 'NO ERROR FOUND'
END
I'm using PRINT for simplicity but the conversion error can not be
controlled through this method. SQL halts when it tries to convert the date.
I would love to be able to trap the error and skip the record and move on to
the new one. The bulk of the data is good, but there are over 175,000
records and I can not do this manually.
Any thoughts or suggestions?
ThanksAsked and answered by lw in the .programming group. Please don't
multi-post.
"Sd" <nowhere@.sadfs.com> wrote in message
news:3ac3890e730ead0e80076b10ff132aaa@.news.teranews.com...
> Hello,
> I'm trying to convert data on a text field to datetime datatype. This data
> has invalid dates such as 10/30, 2/31235, 32/1/2004, 345/2345 etc.
> I have been trying to convert whatever I can but I keep getting errors
when
> I perform a
> SELECT CONVERT(datetime, text_field) FROM Old_data
> Obviously my select statemt is abruptly terminated when I hit one of these
> records with a invalid date.
> I tried looping throught the records using a curson and try to capture and
> check for errors:
> SET @.user_1_date = convert(datetime, @.USER_1)
> IF @.@.ERROR <> 0
> BEGIN
> PRINT 'ERROR FOUND'
> END
> ELSE
> BEGIN
> print 'NO ERROR FOUND'
> END
> I'm using PRINT for simplicity but the conversion error can not be
> controlled through this method. SQL halts when it tries to convert the
date.
> I would love to be able to trap the error and skip the record and move on
to
> the new one. The bulk of the data is good, but there are over 175,000
> records and I can not do this manually.
> Any thoughts or suggestions?
> Thanks
>
>|||Sd (nowhere@.sadfs.com) writes:
> I have been trying to convert whatever I can but I keep getting errors
> when I perform a
> SELECT CONVERT(datetime, text_field) FROM Old_data
> Obviously my select statemt is abruptly terminated when I hit one of these
> records with a invalid date.
> I tried looping throught the records using a curson and try to capture and
> check for errors:
But since SQL Server thinks conversion error is reason enough for aborting
the batch, that does not fly.
But this should do:
SELECT CASE WHEN is_date(text_field) = 1
THEN convert(datetime, text_field)
END
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Error Code 207

I am getting the 207 error code and can't figure out the problem. I have a coldfusion page that is calling a stored procedure. The page allows users to sort the displayed columns and it is passing the column numbers into my stored procedure. I am using a case statement to decide which sort to use.

The error message is: Invalid column name 'SCHOOL'.
I've tried adding single and double quotes here and it worked before I added the case statment when I just type 'order by school'.

Below is some of my code:

SELECT P.NAME,
P.ASORG,
P.MAILSTOP,
P.OFCPHONE,
R.EMPLID,
SCHOOL = S.DESCR,
R.DEGREE_LEVEL,
R.DISCIPLINE,
TEAM = T.TEAM_NAME,
NAT_EVENT = NE.EVENT_NAME,
R.SCHOOL_CODE,
R.TEAM_CODE,
R.EVENT_CODE,
FROM HR_EXTRAS..RECRUITERS R,
HRWH..PS_SCHOOL_TBL S,
HRWH..PERSON P,
HR_Extras..UP_TEAMS T,
HR_Extras..UP_NATIONAL_EVENTS NE,
WHERE R.SCHOOL_CODE = S.SCHOOL_CODE
AND R.EMPLID = P.SNL_ID
AND T.TEAM_CODE = R.TEAM_CODE
AND NE.EVENT_CODE = R.EVENT_CODE
ORDER BY CASE WHEN @.SORT = 1 THEN P.NAME
WHEN @.SORT = 2 THEN P.ASORG
WHEN @.SORT = 3 THEN SCHOOL
WHEN @.SORT = 4 THEN T.TEAM_NAME
WHEN @.SORT = 5 THEN NE.EVENT_NAME
END

Can anyone offer suggestions?WHEN @.SORT = 3 THEN S.DESCR-PatP|||WHEN @.SORT = 3 THEN S.DESCR

That doesn't work because the SQL statement is actually part of a union query. The previous statements pull the exact same data but have

SCHOOL = ' NM-Not Assigned',

and

SCHOOL = ' CA-Not Assigned',

Sorry I didn't include that previously. The two values above are not stored in the PS_SCHOOL_TBL, thus the explicit call.|||deleted comment