RE: Strange issue while executing sql server sp

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

In my experience, the reported line number can be misleading. The error
references a string or binary data, so it probably isn't the ctns column. I
would look at all string or binary data columns in the temp table, and
compare their size with the corresponding size of the columns in your
dbo.qmkProjection1_mod2_exp2_shiftdetail table.

On a side note, if what you've supplied is the complete stored procedure
definition, I see no need for the temp table. Just execute the query that
populates the temp table to return the result set.

Vern Rabe

"Jack" wrote:

Hi,
I have a simple stored procedure in sql server 2005 express version. This sp
runs fine. However when I am uploading this stored procedure in full version
sql server 2005 and running it throws me an error. I am failing to understand
why the same procedure gives different result (error in later). I would
appreciate any help for resolution of this issue. Thanks

PROCEDURE CODE:

ALTER procedure [dbo].[shiftdetail_report_sources]
as

CREATE TABLE #temp_table_report
(

progress varchar(50)
,machine varchar(3)
,priority decimal(3,0)
,material varchar(20)
,ordernumber varchar(10)
,status varchar(1)
,customername varchar(20)
,finished_part varchar(15)
,quantity decimal(12,4)
,EstRunHrs decimal(12,4)
,duedate datetime
,CtnsToDo decimal(12,4)
,CtnsDone decimal(12,4)
,pcsperctn decimal(12,4)
,cartondesc varchar(30)
,finishdate datetime
,hrspriorday decimal(12,4)
,CtnsPriorDay decimal(12,4)
,hrsperum decimal(12,4)
,setuphours decimal(12,4)
,team decimal(3,0)
,hrspercarton decimal(12,4)
,shift decimal(1,0)
,[date] datetime
,setup varchar(1)
,ctns decimal(10,0)
)

insert into #temp_table_report

SELECT
progress
,machine
,priority
,material
,ordernumber
,status
,[name]
,finished_part
,qty
,EstRunHrs
,duedate
,CtnsToDo
,CtnsDone
,pcsperctn
,cartondesc
,finishdate
,CASE WHEN setup = 'Y' THEN ctnspriorday * pcsperctn / 1000 * hrsperum +
setuphrs ELSE ctnspriorday * pcsperctn / 1000 * hrsperum END AS hrspriorday
,CtnsPriorDay
,hrsperum
,setuphrs
,team
,hrspercarton
,shift
,[date]
,setup
,ctns ---------------This is line 36



FROM dbo.qmkProjection1_mod2_exp2_shiftdetail

select

progress
,machine
,priority
,material
,ordernumber
,status
,customername
,finished_part
,quantity
,EstRunHrs
,duedate
,CtnsToDo
,CtnsDone
,pcsperctn
,cartondesc
,finishdate
,hrspriorday
,CtnsPriorDay
,hrsperum
,setuphours
,team
,hrspercarton
,shift
,[date]
,setup
,ctns
from #temp_table_report

The following is the error code in the full version:
Msg 8152, Level 16, State 2, Procedure shiftdetail_report_sources, Line 36
String or binary data would be truncated.
The statement has been terminated.


Line 36 is ,ctns decimal(10,0).
.

Relevant Pages

  • RE: Strange issue while executing sql server sp
    ... "Vern Rabe" wrote: ... references a string or binary data, so it probably isn't the ctns column. ... I have a simple stored procedure in sql server 2005 express version. ...
    (microsoft.public.sqlserver.programming)
  • RE: SQL stored procedure executing twice
    ... from one parent record to another. ... Dim stDocName As String ... My pass-thru query syntax going to stored procedure ...
    (microsoft.public.access.modulesdaovba)
  • Re: Starting from the beginning
    ... Then, it usses an English-language string in the source code, which is ... possible reasons into the useless and incomprehensible "Unable to open CIP file", ... // more processing will be done here like getting the binary data ... /CIP3PreviewImageHeight 726 def ...
    (microsoft.public.vc.mfc)
  • RE: MS Query "Connections"
    ... handle that with my connection string. ... for using the stored procedure route is that it is faster. ... If you truly want to pass in the query string, ...
    (microsoft.public.excel.programming)
  • Re: Searching for byte sequence
    ... Several lines of "text" header info followed by 10,000bytes of binary data ... like your text string) then the Find approach will work. ... If n gives me an offset in a string (that ... CString cSelect; ...
    (microsoft.public.vc.mfc)