Coding Help

Question

Coding Help

moosamca

Right there with Babe

Points: 773
More actions
September 24, 2007 at 3:17 am

#70123

My developers are complaining that if we execute the below SQL outside of pgm it works. But in my devlopement Sql server 2005 server it is not working, any suggestions? They are running through cobol coding.
          Actually they are updating one view in main database. That view is calling an another table in second database (WMS). Please check and help me out to resolve this issue.
Failed

8152 String or binary data would be truncated.
Based on code it looks like it should be ok?! Anyone see anything strange?
       00500-UPDATE-WMS-LOAD-TO-I.
           EXEC SQL
                UPDATE V_WMSDB_WMS_OUTPT_OUTBD_LOAD
                   SET UOPS_STATUS_CD = 'I',
                       UOPS_MODIFIED_TS = CURRENT_TIMESTAMP
                 WHERE UOPS_STATUS_CD = 'N'
           END-EXEC.
In WMS Datasbase the table itself:
[UOPS_STATUS_CD] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UOPS_MODIFIED_TS] [datetime] NOT NULL,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bob Fazio SSChampion Points: 10794 More actions · Answer 1

I agree that there doesn't appear to be any issues with the SQL. If this works in T-SQL, then it is probably a code issue. Are you sure this is the code that is failing?

DROP TABLE #TestTable

CREATE

TABLE #TestTable

(

UOPS_STATUS_CD

CHAR(1),

uops_modified_ts

datetime

)

INSERT

INTO #TestTable

VALUES

('N',CURRENT_TIMESTAMP)

UPDATE

#TestTable

SET

UOPS_STATUS_CD = 'I',

UOPS_MODIFIED_TS

= CURRENT_TIMESTAMP

WHERE

UOPS_STATUS_CD = 'N'

SELECT

* FROM #TestTable

aureolin Hall of Fame Points: 3520 More actions · Answer 2

Have you looked into security issues? Running it "outside of your program" it may work because you're running it "as yourself" - while inside the program you may actually be executing it as a different login.

Steve G.

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 3

I would take a look at UOPS_MODIFIED_TS = CURRENT_TIMESTAMP, what is the value of current_timestamp? String or binary would be truncated usually indicates that the value you're trying to jam into a column just doesn't fit into the target column.

Joe

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 4

I think code is correct and its working fine with SQL server 2005,

Cheers!

Sandy

--

Janet Keith-489067 Ten Centuries Points: 1052 More actions · Answer 5

I see this error primarily when a column definition or variable is incorrectly defined or is too small to hold the value being inserted.

Check all the tables and recompile the view. also, make sure you don't have multiple tables or views with different owners and that you are fully qualifying the objects. Possibly there is a user.mytable and a dbo.mytable with different definitions (one being correct and one incorrect). In this case, the developer may be inserting into an object created by him and not the object that is being called by the application.