migration error. SQL to Azure

Apr 3, 2013 at 7:45 PM
Hi there,
Im keep getting the following errors:
03/04/2013 22:10:10 --> Error #: 1781 -- Column already has a DEFAULT bound to it.
Could not create constraint. See previous errors.
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__UFForms__Archive__45544755]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[UFForms] ADD  DEFAULT ((0)) FOR [Archived]
END

03/04/2013 22:10:11 --> Error #: 1781 -- Column already has a DEFAULT bound to it.
Could not create constraint. See previous errors.
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__UFForms__StoreRe__46486B8E]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[UFForms] ADD  DEFAULT ((1)) FOR [StoreRecordsLocally]
END

03/04/2013 22:10:12 --> Error #: 1781 -- Column already has a DEFAULT bound to it.
Could not create constraint. See previous errors.
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__UFForms__Disable__473C8FC7]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[UFForms] ADD  DEFAULT ((0)) FOR [DisableDefaultStylesheet]
END
Any idea?
Many thanks
mkariti
Coordinator
Apr 4, 2013 at 1:18 AM
Hi There,

First, while you got these errors, you should probably have everything successfully migrated. Each statement is handled on it's own and won't effect the next statement unless that next statement depend on it. Now, that said, you by chance didn't save off your SQL script that SQLAzureMW generated during the export phase? What I would look at is the CREATE TABLE statement for UFForms and see if there are default values set for the columns. If there are no default statements in the CREATE TABLE command, then the next thing that I would look for is duplicate ALTER TABLE commands. Basically, I use SMO to create all of the TSQL scripts, but what I do is capture all of the ALTER TABLE commands and put them back in after all of the tables have been created. It might be that I somehow put the same ALTER TABLE command in twice. Anyway, if you don't have an issue with me looking at your TSQL, I would be happy to take a look at it and see if I can figured out what happened. If you want, just contact me offline and we can figure out what is going on.

Regards,
George
Apr 4, 2013 at 9:59 AM
Edited Apr 4, 2013 at 10:44 AM
Hi George,
Many thanks for your quick and comprehensive answer!
I tried generating it again but this time I set the collection as it should be and it seems fine.
Can the previous error be as a result of mismatch collection?
Also, I can't seem to get the process to go all the way.
it's just says :
"Processing 333 out of 335".
Save or Cancel.

the last 2 lines in the results are as follows:
04/04/2013 12:50:21 --> Copied 2046 of 2046 (100%)
Clock Time (ms.) Total     : 123740 Average : (16.53 rows per sec.)
and on the app side everything seems fine. is that means that its ok?

Many thanks
mkariti
Coordinator
Apr 4, 2013 at 12:34 PM
The last two lines are the output of a BCP upload, but not the end of the program. The very last line you should see is:
 Processing finished at 4/4/2013 7:25:54 AM -- UTC -> 4/4/2013 12:25:54 PM
Telling you that the process is finished. During the data upload process, I kick off several tabs for the parallel upload of data. Are all of the tabs gone except for the Results tab? Question for you. Would you mind if I see the SQL script that is generated during the export process? I would like to see if I can't figure out what is going on. I tried creating a database with different collation and I had no problem moving one of my test databases. What collation did you use? I can try that one as well.

Thanks for your time,
George
Apr 4, 2013 at 3:12 PM
Hi George,
Tried to contact you via PM but not sure if you got it.
cheers
mkariti