RE: Strange issue while executing sql server sp

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks for your advise Vern. I noted your point. I am going to take a look at
this.

"Vern Rabe" wrote:

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
    ... 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: 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)
  • Re: XML binding advice
    ... Performance - By writing the SQL statement as a stored procedure, ... execution plan will take into account any indexes you have on the tables ... each piece you add to the string. ...
    (microsoft.public.dotnet.xml)