?
Solved

From MS Access 2003 table to "identical" MS SQL Server 2005 table

Posted on 2006-06-29
2
Medium Priority
?
256 Views
Last Modified: 2008-02-01

Hello there!

When I try

SET IDENTITY_INSERT Stocks ON
INSERT INTO Stocks
SELECT Access.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\PathToDatabase\DatabaseName.mdb';'admin';'',Stocks) As Access

I get the following error:

Msg 8101, Level 16, State 1, Line 5
An explicit value for the identity column in table 'Stocks' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Please explain the error and correct what is wrong.

Note: there are 2 identical Primary Key´s at Access and SQL Server, both called StockID.

Thanks,
fskilnik.


0
Comment
Question by:fskilnik
[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
2 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 1200 total points
ID: 17011912
you need to specifically name your columns

SET IDENTITY_INSERT Stocks ON
INSERT INTO Stocks (StockID, columnA, columnB, ...)
SELECT StockID, columnA, columnB, ...
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\PathToDatabase\DatabaseName.mdb';'admin';'',Stocks) As Access
0
 

Author Comment

by:fskilnik
ID: 17011969

Perfect, BriCrowe. Thanks for the quick and explicit reply.

Please have a look at my other (I guess harder) question, if you don´t mind!
Here: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21902340.html

Thanks a lot,
fskilnik

0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

770 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