Script missing BCP commands!

Dec 20, 2010 at 4:26 PM

Hi All,

I have a problem with the output script.

When SQL Azure Migration Wizard produces the script, he forgets to add the lines about the bcp command utility.

So, I can later execute the utility SQLAzureMWBatch and he creates the tables, the views, etc.

But he doesn’t transfer the date because he doesn’t find the command about BCP.

Is there anybody with the same problem?

Thanks a lot,

Davide

Coordinator
Dec 20, 2010 at 8:36 PM


Hi Davide,

 

When you run the wizard, you should come to a results summary page.  On the Results Summary tab, you should see an output something like this:

 

Using BCP to get data from table [SalesLT].[Customer]

*

Starting copy...

847 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 62     Average : (13661.29 rows per sec.)

 

BCP output file: "c:\SQLAzureMW\BCPData\SalesLT.Customer.dat"

 

for each table that has data.  On the SQL Script tab, you should see something like this:

 

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

BEGIN

CREATE TABLE [SalesLT].[Customer](

            [CustomerID] [int] IDENTITY(1,1) NOT NULL,

            [NameStyle] [dbo].[NameStyle] NOT NULL,

            [Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

            [FirstName] [dbo].[Name] NOT NULL,

            [MiddleName] [dbo].[Name] NULL,

            [LastName] [dbo].[Name] NOT NULL,

            [Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

            [SalesPerson] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

            [Phone] [dbo].[Phone] NULL,

            [PasswordHash] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

            [PasswordSalt] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

            [rowguid] [uniqueidentifier] NOT NULL,

            [ModifiedDate] [datetime] NOT NULL,

 CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED

(

            [CustomerID] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF),

 CONSTRAINT [AK_Customer_rowguid] UNIQUE NONCLUSTERED

(

            [rowguid] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)

)

END

 

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[SalesLT].[Customer]') AND name = N'IX_Customer_EmailAddress')

CREATE NONCLUSTERED INDEX [IX_Customer_EmailAddress] ON [SalesLT].[Customer]

(

            [EmailAddress] ASC

)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)

GO

-- BCPArgs:847:[SalesLT].[Customer] in "c:\SQLAzureMW\BCPData\SalesLT.Customer.dat" -E -n -b 10000

GO

 

 

The BCPArgs that you see in green above is the command that SQLAzureMWBatch (and SQLAzureMW) looks for to clue it that a BCP execution is required.  Note, that when you step through the wizard, on the “Choose Objects” dialog, there is an “Advanced” button.  Click on that and make sure that you have “Table Schema with Data” selected for “Script Table / Data”.  If you have all that selected (and your table has data), then you should have some kind of output in the Result Summary tab telling you (good are bad) what BCP results were.

 

The next question for you is are you using an old SQLAzureMW.exe.config file?  I changed how the BCP command is formatted with SQLAzureMW v3.4.1 so you need to make sure that if you are using an old config file that it has the updated commands in it.  If you are getting an error generating the script, send me the exact error message and I will see it I can’t see what is going on.

 

Another question for you is if you run SQLAzureMW all the way through to deploying to SQL Azure, is you data getting uploaded?  In other words, are you experiencing this issue only with SQLAzureMWBatch?

 

Thanks,

George

Dec 21, 2010 at 9:56 AM

 

Hi George,

Thank you for your answer.

I’m using the v3.4.1 Release.

I haven’t problem with bcp utility. For instance, I can upload my date and I haven’t error.

My problem is that while the SQLAzureMW utility creates the script, it doesn’t write the bcp line command.

I have set the option Table Schema with Data and I find the same in the xml config file.

So, I don’t have a line alike

GO

-- BCPArgs:847:[SalesLT].[Customer] in "c:\SQLAzureMW\BCPData\SalesLT.Customer.dat" -E -n -b 10000

GO

I have an idea. I’ve not modified the xml and I’m using SQLAzureMW with bcp in French version.

Maybe is there cause of the problem?

Thanks,
Davide

Coordinator
Dec 21, 2010 at 10:11 PM


Hi Davide,

 

Yes, that could be your problem.  You will notice in SQLAzureMW.exe.config, I have two sections (one US and one for Germany):

 

  <configSections>

    <section name="en-US" type="System.Configuration.NameValueSectionHandler" />

    <section name="de-DE" type="System.Configuration.NameValueSectionHandler" />

  </configSections>

 

  <en-US>

    <add key="BCPRowsCopied" value="[0-9]+\srows copied" />               <!-- Regex Search -->

    <add key="BCPError" value="Error =" />                                <!-- Regex Search -->

    <add key="BCPSQLState" value="SQLState =" />                          <!-- Regex Search -->

    <add key="BCPTotalSent" value="Total sent: [0-9]+\W+SQLState =" />    <!-- Regex Search -->

    <add key="BCPNumber" value="[0-9]+" />                                <!-- Regex Search -->

    <add key="BCPSummary" value="Clock[\w\W]+sec.\)" />                   <!-- Regex Search -->

    <add key="BCPOutputSummary" value="Copied {0} of {1} ({2}%)" />       <!-- Summary output -->

    <add key="BCPCodePage" value="437" />

  </en-US>

  <de-DE>

    <add key="BCPRowsCopied" value="[0-9]+\sZeilen kopiert" />                    <!-- Regex Search -->

    <add key="BCPError" value="Error =" />                                        <!-- Regex Search -->

    <add key="BCPSQLState" value="SQLState =" />                                  <!-- Regex Search -->

    <add key="BCPTotalSent" value="Insgesamt gesendet: [0-9]+\W+SQLState =" />    <!-- Regex Search -->

    <add key="BCPNumber" value="[0-9]+" />                                        <!-- Regex Search -->

    <add key="BCPSummary" value="Zeit[\w\W]+sec.\)" />                            <!-- Regex Search -->

    <add key="BCPOutputSummary" value="Copied {0} of {1} ({2}%)" />               <!-- Summary output -->

    <add key="BCPCodePage" value="437" />

  </de-DE>

 

If your BCP output is in French, then you will need to add an fr-FR section (I think that is the code).  Anyway, if you add a section for French you will probably be good to go.  Since it looks like you are an experienced coder, you can open up ScriptDatabase.cs (in the SQLAzureMWUtils library) and set a break point at line 916. This is the ScriptTableData function you can see the output of the BCP and what I am trying to match against.  At the top of this function, you will see (line 806) “Thread.CurrentThread.CurrentCulture.Name  You can verify that the code is fr-FR.  If this works, then send me the French translation and I will add it to the config file for other people to use.

 

Thanks,

George

Jan 7, 2011 at 3:35 PM

 

Hi George,

I come back from holiday and I work to project again. I give you with pleasure the French matching.I tried to match the bcp output and to use the utility in French system. But, I have the problem again.

So, I proceeded as follow:

  1. I add a line as like in the <configSection> element :

<section name="fr-FR" type="System.Configuration.NameValueSectionHandler" />

 

  1. I add the whole <fr-FR> element:

<fr-FR>

   <add key="BCPRowsCopied" value="[0-9]+\slignes copiées" />             <!-- Regex Search -->

   <add key="BCPError" value="Error =" />                               <!-- Regex Search -->

   <add key="BCPSQLState" value="SQLState =" />                         <!-- Regex Search -->

   <add key="BCPTotalSent" value="Total sent: [0-9]+\W+SQLState =" />   <!-- Regex Search -->

   <add key="BCPNumber" value="[0-9]+" />                               <!-- Regex Search -->

   <add key="BCPSummary" value="Clock[\w\W]+sec.\)" />                   <!-- Regex Search -->

   <add key="BCPOutputSummary" value="Copied {0} of {1} ({2}%)" />       <!-- Summary output -->

   <add key="BCPCodePage" value="437" />

</fr-FR>

 

Now, I need to translate the other lines.

Notes:

I’ve opened the project with my visual studio.

I haven’ the line 916. The file have 915 lines.

At line 806, I have another command as follow: SqlSmoObject[] objs = new SqlSmoObject[1];.

If I search the ScriptTableData on the file, I found public void ScriptTableData(SqlSmoObject obj) at line 571. It’s right?

If I search Thread.CurrentThread.CurrentCulture.Name, I find nothing on the whole project.

Best regards,
Davide