RE: Strange issue while executing sql server sp

Tech-Archive recommends: Fix windows errors by optimizing your registry

OK,
This is what I did. I commented all fields and released each of the field
one by one. I found that the error mesage is milseading. The customername
field is the one where truncation is occurring as my temp table field size is
less then the corresponding field in the veiw. So making the change helped me
to resolve the issue. I am little surprised that express edition did not
catch it. It is free and water dwon version. So I guess all features are not
there. Thanks again to both of you.

"Jack" wrote:

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
    ... I would still recommend you compare all data types between your temp table ... 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)
  • Strange issue while executing sql server sp
    ... I have a simple stored procedure in sql server 2005 express version. ... ,finishdate datetime ...
    (microsoft.public.sqlserver.programming)
  • Re: SQLserver and the WHERE x IN y
    ... to a stored procedure - that would only work if you introduced subtyping into ... the temp table from the calling procedure (the temp table is still in scope, ... INSERT INTO #MyTempTable EXECUTE YourPopulateProc 123 ... INSERT INTO @MyTableVar VALUES ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored Procedures in SE 7.3
    ... My end goal to write a stored procedure to be used by Lotus Enterprise ... Integrator (LEI). ... into temp t1; ... from bmdata, t1 ...
    (comp.databases.informix)
  • Re: How to navigate recursive stored procedures results
    ... connection is inherited by the next user, ... beforehand so any residual #Temp tables would be dropped. ... the identity is not "flow"ing to the SQL Server - ... union a SQL query and the results of a stored procedure. ...
    (microsoft.public.dotnet.framework.adonet)