Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
?
620 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

610 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