Solved

Sybase ASE BCP In With Table with No Identity Column

Posted on 2013-10-22
1
1,216 Views
Last Modified: 2013-10-23
Here's a fun one. I wanted to test some edge case stuff like tables that are not properly defined. When I have a bcp in for a table that has no IDENTITY column, I get an error. Here's the whole flow of things:

My table is defined like this:

CREATE TABLE [dbo].[XTABLE] (
            [BOGUSID] INT NOT NULL ,
            [DAT] DATETIME NOT NULL
)

No pk/identity.

I export its data (few rows ) in bcp with this:

bcp mytester.dbo.XTABLE out XTABLE.bcp -U sa -P mypass -T -n

now, I want to bring it back in using this:

bcp mytester.dbo.XTABLE in MyTable.bcp  -E -n -S Sybase_157 -U sa -P SyAdmin

The result says:

Server Message: Sybase157 - Msg 7756, Level 16, State 1:

Cannot use 'SET IDENTITY_INSERT' for table 'mytester.dbo.XTABLE' because the table does not have the identity property.

I have looked over the bcp page, but can't come up with some way to tell it it's ok to insert without identity column...

Any suggestions experts?
0
Comment
Question by:Jay Redd
[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
1 Comment
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 total points
ID: 39593387
This is correct and expected behaviour.

You used the "-E" switch, which means "the values in this BCP file are to be explicitly loaded into the identity column in this table".

You don't have an identity column in your table, hence your error message. "-E" implicitly issues the "set identity_insert on" command at the start of the bcp connection.

Try it without "-E". It should work, although note if your ASE versions are not the same you may run into issues, and if they are not running on the same platform (hardware and operating system) you may also run into (other) issues because "-n" means "native" or platform-dependent mode.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
The goal of this blog is to: > note what has impeded us from reaching effective life on-call > provide 3 steps to mastering life on-call > highlight what will be achieved with effective life on-call
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 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