Solved

Sybase ASE BCP In With Table with No Identity Column

Posted on 2013-10-22
1
1,233 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article is in regards to the Cisco QSFP-4SFP10G-CU1M cables, which are designed to uplink/downlink 40GB ports to 10GB SFP ports. I recently experienced this and found very little configuration documentation on how these are supposed to be confi…
The recent Petya-like ransomware attack served a big blow to hundreds of banks, corporations and government offices The Acronis blog takes a closer look at this damaging worm to see what’s behind it – and offers up tips on how you can safeguard your…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 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