Error during preparedstmt.setNull(1) in timestamp datatype column

------------------------

I would like to get information related to timestamp data type in SQL Server (WANT TO SET NULL IN TIMESTAMP COLUMN )I have Following case

try {
try {
stmt.execute("drop table timestampTable");
}
catch (SQLException ex1) {
}
stmt.execute(
"Create table timestampTable(c1 int Primary Key, c2 Timestamp)");

PreparedStatement pst = connection.prepareStatement(
"insert into timestampTable values(?,?)");
pst.setInt(1, 2);
pst.setNull(2, Types.TIMESTAMP);
pst.execute();
}
catch (SQLException ex) {
ex.printStackTrace();
}

TRACE IS GIVEN BELOW
====================

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Disallowed implicit conversion from data type datetime to data type timestamp, table 'ClientDB.dbo.timestampTable', column 'c2'. Use the CONVERT function to run this query.
at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeInter nal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec ute(Unknown Source)
at JDBC.TestSQLServer.testTIMETAMPDataTypes(TestSQLSe rver.java:75)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.daffodilwoods.tools.testworker.TestRunner.runM ethod(TestRunner.java:159)
at com.daffodilwoods.tools.testworker.TestRunner.runI nSequence(TestRunner.java:83)
at com.daffodilwoods.tools.testworker.TestRunner.runT estCase(TestRunner.java:4

PLEASE REPLY ME AS SOON AS POSSIBLE

THANKS

SUBE SINGHit appears that types.timestamp is actually a datetime data type. timestamp data type in sql has nothing to do with date and/or time.

Error converting datatypes

Hi,

This script gets run by a job every 3 mins, and it's falling over with an "Error converting varchar value... to column of datatype int", and I think it's on this line:

select @.sbj1='New ICNA Forum Post (ThreadID='+@.existingID+')'

...where I'm trying to build up a string by dropping an ID number (datatype int) into it.

So I tried:

select @.sbj1='New ICNA Forum Post (ThreadID='+CAST(@.existingID AS varchar(100))+')'

and:

select @.sbj1='New ICNA Forum Post (ThreadID='+CONVERT(varchar(100), @.existingID)+')'

Both of these result in the job running successfully, but no emails get sent and the job history shows the error "Incorrect syntax near 'Forum'." On the good side, it's supposed to be looping through the email-sending bit 4 times (there are currently 4 users) and sure enough, it repeats that error message 4 times.

The full script follows below. I'd be hugely grateful if anyone could point out what I'm doing wrong, and how to do it right.

Cheers.

Declare @.hMessage varchar(255),@.msg_id varchar(255)
Declare @.MessageText varchar(8000),@.message varchar(8000)
Declare @.MessageSubject varchar(8000),@.subject varchar(8000)
Declare @.Origin varchar (8000), @.originator_address varchar(8000)

EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT

WHILE @.hMessage IS NOT NULL
BEGIN

exec master.dbo.xp_readmail
@.msg_id=@.hMessage,
@.message=@.MessageText OUT,
@.subject=@.MessageSubject OUT,
@.originator_address=@.Origin OUT

IF ((SELECT COUNT(*) FROM forum_users WHERE email = @.Origin) = 1) -- IF email from forum-recognised address
BEGIN
IF (CHARINDEX('(ThreadID=', @.MessageSubject)>0) -- IF email has a thread ID
BEGIN
DECLARE @.existingID int, @.em1 varchar(100), @.bdy1 varchar(8000), @.sbj1 varchar(500)
SELECT @.existingID=CAST(SUBSTRING(@.MessageSubject, (CHARINDEX('=', @.MessageSubject)+1), (CHARINDEX(')', @.MessageSubject)-(CHARINDEX('=', @.MessageSubject)+1))) AS int)
INSERT INTO forum_posts (body, thread_id) VALUES (@.MessageText, @.existingID)

-- Do mailing

declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor1
fetch next from em_cursor1
into @.em1

while @.@.FETCH_STATUS=0
begin
select @.bdy1='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messag eText
select @.sbj1='New ICNA Forum Post (ThreadID='+@.existingID+')'
exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1
fetch next from em_cursor1
into @.em1
end
close em_cursor1
deallocate em_cursor1

END
ELSE -- IF email has no thread ID
BEGIN
DECLARE @.newID int, @.em2 varchar(100), @.bdy2 varchar(8000), @.sbj2 varchar(500) -- Create a new thread record and use the resulting ID to add a thread_post record
INSERT INTO forum_threads (subject) VALUES (@.MessageSubject)
SELECT @.newID=@.@.IDENTITY
INSERT INTO forum_posts (body, thread_id) VALUES (@.MessageText, @.newID)

-- Do mailing

declare em_cursor2 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor2
fetch next from em_cursor2
into @.em2

while @.@.FETCH_STATUS=0
begin
select @.bdy2='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messag eText
select @.sbj2='New ICNA Forum Post (ThreadID='+@.newID+')'
exec master.dbo.xp_sendmail @.em2,@.bdy2,@.sbj2
fetch next from em_cursor2
into @.em2
end
close em_cursor2
deallocate em_cursor2

END
END

SET @.hMessage = NULL

EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT
ENDHave you tried substituing the xp_sendmail with SELECT just to see if you have formatted very thing correctly.|||Ah. Did I mention that my grasp of SQL and its debugging techniques was a little sparse?

Thanks very much for your help, but could you possibly explain how do do that?

Cheers.|||OK;

In your script you have a line like:
exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1
RewriteSELECT 'master.dbo.xp_sendmail', @.em1,@.bdy1,@.sbj1
Do this for all xp_sendmail. This may shine some light.|||Aha! Well, at least I've learnt how to get some debugging output. Unfortunately I'm none the wiser as to why sendmail isn't working.

It loops 4 times, once for each email address in my forum_users table. I'm sending the "trigger" email from a hotmail address (needs to be external to our network) and thus for each email it tries to send the subject and body look like:

@.sbj1:
New ICNA Forum Post (ThreadID=24)

@.bdy1:
New ICNA Forum Post: Friday morning test 1 __________________________________________________ _______________ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com

Which are pretty well exactly what I was expecting - so why on earth is it falling over? :(|||At last, got it working! It didn't like this line:

exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1

when I replaced it with:

exec master.dbo.xp_sendmail
@.recipients=@.em1,
@.message=@.bdy1,
@.subject=@.sbj1

it worked fine. I don't know why, and I don't care :) It works...

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