?
Solved

Identical Tables "Insert Error: Column name or number of supplied values does not match table definition"

Posted on 2009-04-07
7
Medium Priority
?
598 Views
Last Modified: 2013-11-30
I wrote a stored procedure to dynamically build the insert statement as I am using it as part of an SSIS package.  It only fails on certain tables, not all of the tables. I am testing right now and the frustrating part is that this worked yesterday.  I copied the table from dev to my test environment and I am comparing test with dev.  Obivously, they match since I copied the table from dev to test.  I have been debugging through the code and the dev table, the test table and the staging table all match.  They all have the same columns in the same order with the same datatypes.  I am getting this error when I execute the stored procedure from the package.  When I run the stored procedure in the Management Studio, I don't get the error.  Any ideas???
0
Comment
Question by:seasonmmclane
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 16

Expert Comment

by:Auric1983
ID: 24089144
Are you trying to insert into an identity field?
0
 

Author Comment

by:seasonmmclane
ID: 24089809
No.  A big part of the mystery to me is that if I run the script in SSMS, it works.  Another thing...The package will execute successfully for most tables.  it is just a few tables that are giving me problems.  I don't have any databases or tablenames hardcoded.
0
 

Author Comment

by:seasonmmclane
ID: 24091665
I figured out one problem.  When I try to run this in DEV against QA (comparison), I am trying to insert into an identity field.  Any ideas???
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 51

Accepted Solution

by:
Mark Wills earned 1000 total points
ID: 24104368
Well Auric1983 picked the identity business...

Depending on how you wrote the dynamic part, what you can do is check or use sys.columns which has the is_idenity attribute and exclude that from the insert. Found that system / catalogue view to be one of the handiest.

The other thing to do is to wrap it up in a try/catch block - but whilst it might catch the error, it doesn't fix the problem.

Updating with identities is always a challenge, like, do you need to preserve the value (as in a new master record with details already point to it). In which case you might need to allow insert into the odentity, but if there are multiple streams of data into that table then it is likely to cause problems.

Can you show us the procedure ? Might be able to make some recommendations.





0
 
LVL 16

Assisted Solution

by:Auric1983
Auric1983 earned 1000 total points
ID: 24106726
You can specify that you want to insert into an identity column.  

SET IDENTITY_INSERT tablename ON
Insert records
SET IDENTITY_INSERT tablename OFF
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24108852
Please do not do that without careful consideration of number sequences and potentially different data sources. If all coming from the other table, then fine, but if there is anything that can add to that table being the target of the insert then you are going to have problems. That identity insert setting is really only for initial data takeup.
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24129354
If creating a dev environment from Test / Production is what you are after I'd do a full backup / restore, the only problem there is you lose any specific security applied only to the dev db.

But Mark is definately right that using the identity insert you really need to understand what data is being inserted...
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

765 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