SQL Azure to SQL 2012 scripting failure

Jul 13, 2012 at 10:55 PM

Have downloaded v4.0.2.

Initially replaced my older version BatchBackup and BatchUpload process  with successful Backup, but had numerous script errors in the Upload process (in CREATE TABLE, CONSTRAINT, etc.). Subsequent BCP calls failed due to Objects not existing.

The CREATE failures were not pervasive, rather hit and miss. My previous processes have been running flawlessly since last August (2011).

~ Jillian

Coordinator
Jul 14, 2012 at 1:15 AM

Hi Jillian,

Does v3.8.9 work ok, but v4.0.2 not work? 

George

Jul 15, 2012 at 1:57 PM

The version that I was using was the one prior to 3.8.9 - backing up from Azure, and uploading to SQL 2008 R2 Enterprise. The backup Which I then changed to 3.8.9 was still successful. The upload - changed to 3.8.9 - was not. I had to fall back on the previous version.
 
I now have to migrate this platform to SQL 2012. I have tried to implement v4.0.2 - the backup (again) is successful (thankfully, as there are no SQL 2008 bits on the machine), but the 4.0.2 upload fails for various CREATE TABLE and CREATE CONSTRAINT calls within the 2012 environment - very similar to the failures for the v3.8.9 upload in the 2008 R2 environment.
 
I cannot fall back to the previous (pre-v3.8.9) version as before as 2008 bits will not be installed on the platform.

Example (one of several errors) from BatchUpload Result.txt:
 
 

7/13/2012 12:04:38 PM --> Error #: 102 -- Incorrect syntax near 'GO'.

 

Incorrect syntax near 'GO'.

 

Incorrect syntax near 'GO'.


Incorrect syntax near ')'.


SET ANSI_NULLS ON


SET QUOTED_IDENTIFIER ON


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Objective]') AND type in (N'U'))


BEGIN


CREATE TABLE [dbo].[Objective](


                [ObjectiveID] [int] IDENTITY(0,1) NOT NULL,


                [Name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


                [ObjectiveDescription] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


                [LeapAttributeID] [bigint] NULL,


                [Version] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


                [Origin] [int] NULL,


                [GURLID] [uniqueidentifier] NULL,


                [Comments] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


 CONSTRAINT [PK_Objective] PRIMARY KEY CLUSTERED


(


                [ObjectiveID] ASC


)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)


)


END


GO


IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Objective_LEAPAttribute]') AND parent_object_id = OBJECT_ID(N'[dbo].[Objective]'))


ALTER TABLE [dbo].[Objective]  WITH NOCHECK ADD  CONSTRAINT [FK_Objective_LEAPAttribute] FOREIGN KEY([LeapAttributeID])


REFERENCES [dbo].[LEAPAttribute] ([LeapAttributeID])


GO


IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Objective_LEAPAttribute]') AND parent_object_id = OBJECT_ID(N'[dbo].[Objective]'))


ALTER TABLE [dbo].[Objective] CHECK CONSTRAINT [FK_Objective_LEAPAttribute]


GO


IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Objective_Objective]') AND parent_object_id = OBJECT_ID(N'[dbo].[Objective]'))


ALTER TABLE [dbo].[Objective]  WITH NOCHECK ADD  CONSTRAINT [FK_Objective_Objective] FOREIGN KEY([Origin])


REFERENCES [dbo].[Objective] ([ObjectiveID])


 


 

 



Jill McRae ~ We travel forward, no matter the turns...
Learning Architect, Data & BIArchitecture
Organization: wrightrobbins.com

Twitter: http://twitter.com/ajillian
LinkedIn: http://www.linkedin.com/in/jillianmcrae

Data & BI Architecture

…structures pour décisions intelligentes!

 

 

Coordinator
Jul 16, 2012 at 5:02 PM

Hi,

 

I fixed the error in v3.9 and v4.0.3.  The problem was a counting issue.  When I generate script from SQL Server / SQL Database, the script has a CR at the end of a line (1 character).  When I read script from a text file, it has a CRLF at the end of a line (2 characters).   Anyway, bug should be fixed.  Please check it out and let me know if I missed anything and it does not work.

 

Sorry about the bug,

George 


Jul 16, 2012 at 8:47 PM
The version that I was using was the one prior to 3.8.9 - backing up from Azure, and uploading to SQL 2008 R2 Enterprise. The backup Which I then changed to 3.8.9 was still successful. The upload - changed to 3.8.9 - was not. I had to fall back on the previous version.

I now have to migrate this platform to SQL 2012. I have tried to implement v4.0.2 - the backup (again) is successful (thankfully, as there are no SQL 2008 bits on the machine), but the 4.0.2 upload fails for various CREATE TABLE and CREATE CONSTRAINT calls within the 2012 environment - very similar to the failures for the v3.8.9 upload in the 2008 R2 environment.

I cannot fall back to the previous (pre-v3.8.9) version as before as 2008 bits will not be installed on the platform.

Example (one of several errors) from BatchUpload Result.txt:


7/13/2012 12:04:38 PM --> Error #: 102 -- Incorrect syntax near 'GO'.


Incorrect syntax near 'GO'.


Incorrect syntax near 'GO'.


Incorrect syntax near ')'.


SET ANSI_NULLS ON


SET QUOTED_IDENTIFIER ON


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Objective]') AND type in (N'U'))


BEGIN


CREATE TABLE [dbo].[Objective](


[ObjectiveID] [int] IDENTITY(0,1) NOT NULL,


[Name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


[ObjectiveDescription] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


[LeapAttributeID] [bigint] NULL,


[Version] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


[Origin] [int] NULL,


[GURLID] [uniqueidentifier] NULL,


[Comments] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,


CONSTRAINT [PK_Objective] PRIMARY KEY CLUSTERED


(


[ObjectiveID] ASC


)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)


)


END


GO


IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Objective_LEAPAttribute]') AND parent_object_id = OBJECT_ID(N'[dbo].[Objective]'))


ALTER TABLE [dbo].[Objective] WITH NOCHECK ADD CONSTRAINT [FK_Objective_LEAPAttribute] FOREIGN KEY([LeapAttributeID])


REFERENCES [dbo].[LEAPAttribute] ([LeapAttributeID])


GO


IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Objective_LEAPAttribute]') AND parent_object_id = OBJECT_ID(N'[dbo].[Objective]'))


ALTER TABLE [dbo].[Objective] CHECK CONSTRAINT [FK_Objective_LEAPAttribute]


GO


IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Objective_Objective]') AND parent_object_id = OBJECT_ID(N'[dbo].[Objective]'))


ALTER TABLE [dbo].[Objective] WITH NOCHECK ADD CONSTRAINT [FK_Objective_Objective] FOREIGN KEY([Origin])


REFERENCES [dbo].[Objective] ([ObjectiveID])






Jill McRae ~ We travel forward, no matter the turns...
Learning Architect, Data & BIArchitecture
Organization: wrightrobbins.com

Twitter: http://twitter.com/ajillian
LinkedIn: http://www.linkedin.com/in/jillianmcrae

Data & BI Architecture

…structures pour décisions intelligentes!




From: [email removed]
To: [email removed]
Date: Fri, 13 Jul 2012 18:16:00 -0700
Subject: Re: SQL Azure to SQL 2012 scripting failure [sqlazuremw:366864]

From: ghuey
Hi Jillian,
Does v3.8.9 work ok, but v4.0.2 not work?
George
Read the full discussion online.
To add a post to this discussion, reply to this email (sqlazuremw@discussions.codeplex.com)
To start a new discussion for this project, email sqlazuremw@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com
Coordinator
Jul 16, 2012 at 11:26 PM

I believe that I have the issue fixed.  Can you try v4.0.3 and see if I have the issue resolved?

Thanks,

George