Error in executeQuery() on callableStatement

[Surya Indukuri]

Hi,
Please somebody help me. I am unable to figureout what the problem is.
I am trying to run a Stored-Procedure and get the results in the resultSet.
I am getting the following error
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
at TracerFTP.FileWriteHelper.writeToFile(FileWriteHelper.java:34)
at TracerFTP.FileWriteHelper.main(FileWriteHelper.java:14)

Here is my code


SQLConnection = getSQLServConnection();
String sql = "{call pr_TRM_WeeklyTimeToMetrix}";
CallableStatement stproc_stmt = SQLConnection.prepareCall(sql);
stproc_stmt.executeQuery();
rs = stproc_stmt.getResultSet();


private Connection getSQLServConnection(){
Connection connection = null;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
String url = "jdbc dbc:TestDB";
connection = DriverManager.getConnection(url,"userid", "password");
} catch(Exception e){
e.printStackTrace();
)
return connection;
}


Here is my storedProcedure. I am able to see the results fine when I run the stored procedure seperately.


CREATE PROCEDURE dbo.pr_TRM_WeeklyTimeToMetrix
AS

/* Setup tblReporting */

CREATE TABLE #tblReporting (
Customer_Number VARCHAR(9),
Team_ID VARCHAR(10),
Employee_ID VARCHAR(12),
First_Name VARCHAR(12),
Last_Name VARCHAR(12)
)


INSERT INTO #tblReporting
SELECT DISTINCT tbl_Account_Team.Customer_Number, Team_Resource.Team_ID,
Resource.Employee_ID, Resource.First_Name, Resource.Last_Name
FROM tbl_Account_Team
INNER JOIN (Team_Resource
INNER JOIN Resource
ON Team_Resource.Employee_ID = Resource.Employee_ID)
ON tbl_Account_Team.Team_ID = Team_Resource.Team_ID
WHERE tbl_Account_Team.Customer_Number LIKE 320



/* Run query to return data */
SELECT Event_Instance.Application_ID,
Timesheet_History.Task_ID,
Timesheet_History.Employee_ID,
Timesheet_History.Week_Of,
Timesheet_History.Sunday,
Timesheet_History.Monday,
Timesheet_History.Tuesday,
Timesheet_History.Wednesday,
Timesheet_History.Thursday,
Timesheet_History.Friday,
Timesheet_History.Saturday,
Timesheet_History.Status_ID,
Timesheet_History.Labor_Code
FROM ((((((Timesheet_History LEFT JOIN Event_Instance ON Timesheet_History.Event_ID = Event_Instance.Event_ID)
LEFT JOIN tbl_Corporate_Job ON Timesheet_History.Corporate_Job_ID = tbl_Corporate_Job.Corporate_Job_ID)
LEFT JOIN Team ON Timesheet_History.Team_ID = Team.Team_ID)
LEFT JOIN Resource ON Timesheet_History.Employee_ID = Resource.Employee_ID)
LEFT JOIN Task_Detail ON (Timesheet_History.Event_ID = Task_Detail.Event_ID)
AND (Timesheet_History.Task_ID = Task_Detail.Task_ID)
LEFT JOIN Account ON tbl_Corporate_Job.Owning_Account = Account.Customer_Num)
LEFT JOIN Application ON Event_Instance.Application_ID = Application.Application_ID)
WHERE (--(Timesheet_History.Employee_ID like @UserID) AND
(Timesheet_History.Week_Of=CAST(DATEADD(wk, DATEDIFF(wk,0,getdate()), -8) AS smalldatetime)));
GO

Thank You,

[Scott Selikoff]

Without going into your code much "String or binary data would be truncated" usually implies that you are passing a value to a column that is larger (in number of characters) then the column can hold. I would compare the table column sizes with the data you are trying to insert. 9-12 is pretty small for text data and IDs are often stored as integers (although not required by any means)

[Surya Indukuri]

Scott,

I am just doing a select. I am not doing any inserts or updates.

[Paul Clapham]

Originally posted by Surya Indukuri:
I am just doing a select. I am not doing any inserts or updates.

But the stored procedure you posted has an INSERT INTO statement in it.

[Scott Selikoff]

Took the words right out of my mouth.

[Surya Indukuri]

I am not using that Insert

[Paul Clapham]

You say you are using that stored procedure, of which you posted the code. It contains a CREATE TABLE, an INSERT, and a SELECT. When you use the stored procedure you are using all three of those statements, one after another. So you can't say things like "I am not using that Insert". You are using that Insert. And that is what the error message is talking about.

It looks to me as if the SELECT statement doesn't use the results of the INSERT statement. But that doesn't prevent the INSERT from being run before its results are ignored. If you don't need it then why don't you just take it out of the stored procedure? Or just use the SELECT as a query and not use a stored procedure at all?

[Surya Indukuri]

Thank you, I am in the process of changing it to a query. Let me see if that works.

Thanks you for your immediate response again.

[Surya Indukuri]

Thank You Paul, It worked.

[Scott Selikoff]

Yes, thank you Paul for defending what I had said.