?
Solved

Error Number (515) Severity (16) State (3)

Posted on 2006-11-10
11
Medium Priority
?
3,393 Views
Last Modified: 2010-08-05
Error Number (515) Severity (16) State (3)

"Attempt to insert NULL value into column 'ActualByPort', table '#tmp'; column does not allow nulls. Update fails.Command has been aborted."


We re getting this error on both our development and production boxes, but on production the job continues and finishes.  On development it does not.

Can someone explain this behaivor? Why does it finish in production?
0
Comment
Question by:leonstryker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17915757
{wild guess}  
Figure out if you're supposed to allow nulls there
Check the #tmp table declaration in your dev box, and make sure it reflects what you want.
Then see what's populating it.
0
 
LVL 29

Author Comment

by:leonstryker
ID: 17915796
The temp table is created by a Select Into method and the data between machines is replicated.
0
 
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 1000 total points
ID: 17920581
The data may still not be identical - check for defaults that might be overriding NULLs in one system but not in the other? Defaults could be attached to the column directly, or to a user-defined datatype. There may also be triggers, which replication might not be firing.
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 29

Author Comment

by:leonstryker
ID: 17958378
Thanks Joe, but I found no such differences. It is a bit strange. I fixed the code using the IsNull, so teh issue is no longer there, but its still nagging at me.
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 17961446
Hmm!

Unless there are, say, differences in ASE version (which I assume you've checked), yeah, I'm scratching my head now too!
0
 
LVL 3

Expert Comment

by:knel1234
ID: 17973117
Hi,

Do you have different traceon flags set
1) in the run files
2) set after the servers have each started

knel
0
 
LVL 29

Author Comment

by:leonstryker
ID: 18019314
knel,

Sorry, but I do not have the ability to check this any longer. Could you expand on your answer?  Have you seen such a thing happening?

Leon
0
 
LVL 3

Accepted Solution

by:
knel1234 earned 1000 total points
ID: 18019608
leon,

I was going down a path similar to Joe.  When an issue occurs, we always look at the version(s) of the server(s).  However, you can see different behavior via the use of different trace flags on the same Sybase versions.  Do you have a copy of the run file from the various environments?  Are you the only dba that works on these systems?  If not, did your partner(s) adjust any traceflag(s)?  Sybase often recommends using various trace flags to adjust/ "fix" certain behavior at least until a patch(next EBF/version) comes out.  I know you mentioned that you dont have the ability to check this any longer.  In the future, you may want to check the OS version, Sybase version, and trace flags.  Of course, these are many things that need to be checked including the particulars of the system (Stored Procs, triggers, etc).

As an example I recently implemented the following, feel free to review Sybase et all for details.

If I wanted to set trace flags for enhanced remote optimization, I have the following options.

I can set it for myself  Dbcc traceon(11216)
I can set it for everyone Dbcc traceon(11216, -1)
I can set it in the run file.  In this case, I believe both -T11216 and  -T11217 would have worked.

11216 - disable enhanced remote optimization (spid)
11217 - disables enhanced remote optimization (global)

You can ignore the specifics of this trace flag.  The point being that trace flags can be set in the run file or on the fly.  In addition, these trace flags will cause different behavior on identical system (ie same solaris and sybase versions).  Therefore, you should verify the trace flag(s) on your systems.  Just one more thing to think about when things on your system go "wrong".

knel
0
 
LVL 3

Expert Comment

by:dave_gr
ID: 18072144
Easier solution - avoid temp tables and always define your temp tables up front:

create proc do_something
as

create table #blah
(
something_name     varchar(100) not null,
something_date       datetime not null,
something_notes      varchar(255) null
)

insert #blah
(something_name, something_date)
select etc...


This will give you more control and more predictable results.  It will also make debugging easier as you will have described the table definition explicitly rather than inferring it from a select/into.

David
0
 
LVL 29

Author Comment

by:leonstryker
ID: 18072160
Yes, except select into is much faster.

Leon
0
 
LVL 29

Author Comment

by:leonstryker
ID: 18168235
Thanks for everyone's help. Like I said its out of my hands now, but  I would suspect the answer is check everything.

Leon
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
I don't pretend to be an expert at this, but I have found a few things that are useful. I hope that sharing them here will help others, so they will not have to face some rather hard choices. Since I felt this to be a topic of enough importance and…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question