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 adding a column to a table

am trying to add a temporary column to a table(for the duration of the stored procedure)

code

ALTER TABLE [EpiSurv].[ECC] ADD tCaseMigrationID INT NULL

SET IDENTITY_INSERT [EpiSurv].[ECC] ON

INSERT INTO [EpiSurv].[ECC]

( [Name]

, [IsEnabled]

, [CreatedByUserID]

, [CreatedDateTime]

, [tCaseMigrationID]

)

SELECT DISTINCT placeofwk as Workplace

, 0 as [IsEnabled]

, 0 as [CreatedByUserID]

, getdate()

, CaseMigrationID

FROM tCaseMigration

WHERE (WSP1 = 3)

AND (placeofwk is not null and placeofwk <> '')

order by workplace

get back

Msg 207, Level 16, State 1, Procedure spMigration_Step4_Populate_ECCtable, Line 18

Invalid column name 'tCaseMigrationID'.

So your alter table works, and then your insert works, and then the proc fails during the query?

SELECT DISTINCT placeofwk as Workplace
, 0 as [IsEnabled]
, 0 as [CreatedByUserID]
, getdate()
, CaseMigrationID <<<<<-- should this be your newly created column name "tCaseMigration"?
FROM tCaseMigration <<<<<-- is this a correct table name. This is the column you created, is
there a table with the same name or should this be [EpiSurv].[ECC]?
WHERE (WSP1 = 3)
AND (placeofwk is not null and placeofwk <> '')
order by workplace

|||

dan

thx for the reply

i think the t-sql is "correct" - i am trying to add a temporary column on the ECC table to store the CaseMigrationID from the tCaseMigration table - the new temporary column name is tCaseMigrationID on the EpiSurv.ECC table

|||

Maybe you have just to add a semicolon (;) to your line:

ALTER TABLE [EpiSurv].[ECC] ADD tCaseMigrationID INT NULL ;

When i run the alter Table Statement and the Insert statement in the same batch, then i get a similar error message. By adding the semicolon, i have solved it.

But i haven't tried it using a stored procedure.

|||

Yes ..

You will get a compile time error.

When you create the SP the Table ECC doesn;t have the column tCaseMigrationID and the compilation will be fail and you SQL Server won't allow to create a SP..

The possible alternate solution is,

ALTER TABLE [EpiSurv].[ECC] ADD tCaseMigrationID INT NULL

SET IDENTITY_INSERT [EpiSurv].[ECC] ON

Exec ('INSERT INTO [EpiSurv].[ECC]

( [Name]

, [IsEnabled]

, [CreatedByUserID]

, [CreatedDateTime]

, [tCaseMigrationID]

)

SELECT DISTINCT placeofwk as Workplace

, 0 as [IsEnabled]

, 0 as [CreatedByUserID]

, getdate()

, CaseMigrationID

FROM tCaseMigration

WHERE (WSP1 = 3)

AND (placeofwk is not null and placeofwk <> '''')

order by workplace')