bcp u/l into Azure fails on PK

Nov 3, 2010 at 8:56 PM

Running SQLAzureMW, on just a cut-down sql file to test gave me this error.

Process started at ...
 -- Success: CREATE TABLE [dbo].[foo]

11/3/2010 4:30:00 PM --> Uploading data to "dbo.foo
*
11/3/2010 4:30:01 PM --> Error:

Starting copy...
SQLState = 23000, NativeError = 2627
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Violation of PRIMARY KEY constraint 'PK_foo'. Cannot insert duplicate key in object 'dbo.industry_segment'.
SQLState = 01000, NativeError = 3621
Warning = [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been terminated.
BCP copy in failed

Table CREATE statements below as generated by SQLAzureMW:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[foo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[foo](
    [foo_id] [int] IDENTITY(1,1) NOT NULL,
    [description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_foo] PRIMARY KEY NONCLUSTERED
(
    [industry_segment_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
)
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[foo]') AND name = N'IX_foo')
CREATE CLUSTERED INDEX [IX_foo] ON [dbo].[foo]
(
    [foo_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO
-- BCPArgs:101:.dbo.foo" in "c:\SQLAzureMW\BCPData\dbo.foo.dat" -E -q -n -b 10000
GO

Thanks in advance

Coordinator
Nov 4, 2010 at 12:06 PM

Hi,

I get an error from the script above (industry_segment_id) does not exists.  I will be happy to take this offline with you to resolve.  When you run SQLAzureMW can you save the generated script and send me the .sql and dbo.foo.dat file (you can script for just a single table)?  You can contact me through codeplex and then we can exchange emails directly.

Regards,
George

Nov 4, 2010 at 1:33 PM

I was s/r the actual table details and missed one.

My situation is I'm 5+ weeks (!) into migrating a SQL Server 2000 database to SQL Azure via SQL Server 2008 R2.  What was supposed to only take a day or two has taken weeks with no results.

Our database has 270+ tables, 100+ procedures, and a handful of views/triggers.  It works just fine with our ColdFusion MX 6.1 app (which I'm simultaneously and equally frustrated by) migrating to ColdFusion 9.

We lost our Beta box and have no machine backup for either our production app box or db box.  

So we thought moving our db box to SQL Azure would free up that box for a direct hardware replacement for the app box, but give us some benefits of having our db service in the cloud.

Still with me?!

Here's the corrected code from above:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[foo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[foo](
    [foo_id] [int] IDENTITY(1,1) NOT NULL,
    [description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_foo] PRIMARY KEY NONCLUSTERED
(
    [foo_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
)
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[foo]') AND name = N'IX_foo')
CREATE CLUSTERED INDEX [IX_foo] ON [dbo].[foo]
(
    [foo_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO
-- BCPArgs:101:.dbo.foo" in "c:\SQLAzureMW\BCPData\dbo.foo.dat" -E -q -n -b 10000
GO

And here's the data that's supposed to be in this table:

77	HOLD
141	Medical
142	Transportation
143	Industrial
144	Consumer
145	Entertainment / Media / Hospitality
146	Telco & Communications
147	Finance
148	Technology
149	Public Sector
159	Business Services
160	Retail Banking
161	Capital Markets
162	All
163	
164	Utilities
165	Diversified Financials
166	Insurance
167	Internet Software & Services
168	Hotels
169	Food & Drug Retailing
170	Metals & Mining
171	Multiline Retail
172	Media
173	Textiles Apparel & Luxury Goods
174	Household Durables
175	Auto Components
176	Oil & Gas
177	Food Products
178	Automobiles
179	Beverages
180	Transportation Infrastructure
181	Specialty Retail
182	Hotels Restaurants & Leisure
183	Household Products
184	Internet & Catalog Retail
185	Distributors
186	Machiner
187	Personal Products
188	Building Products
189	Office Electronics
190	Computers & Peripherals
191	Food Beverage & Tobacco
192	Energy Equipment & Services
193	Food Retailer
194	Health Care
195	Consumer Staples
196	Information Technology
197	Financials
198	Consumer Discretionary
199	Industrials
200	Energy
201	Telecommunication Services
202	Materials
203	Consumer/Discretionary
204	Telecommunications Service
205	Telecommunications Services
206	Information Technology�
207	Financials�
208	Consumer/Discretionary�
209	High Tech
210	Automotive
211	Engineering
212	Construction
213	Aerospace
214	Retail
215	Financial
216	Pharmaceutical
217	Services
218	Media/Retail
219	Chemical
220	Energy/Utility
221	Agriculture
222	Healthcare
223	Telecom
224	Metal
225	Forest
226	Apparel
227	Discrete
228	Textiles
229	Mining
230	Building Materials
231	Mill Products
232	Wood Products
233	Banking
234	Storage/Dist
235	Hospitality
236	Higher Education
237	Distribution
238	BioTechnology
239	Process
240	InformationTechnology
241	Capital Goods
242	Consumer Durables & Apparel
243	Household & Personal Products
244	Technology Hardware & Equipment
245	Retailing
246	Automobiles & Components
247	Software & Services
248	Health Care Equipment & Services
249	Pharmaceuticals & Biotechnology
I'll see about contacting you via Codeplex.