Inserting Data

Sep 27, 2009 at 12:14 PM

Hi,

First of all,thanks for this great tool;but i have a question about tool.When we make inserting data?,because tool only making create tables,db's etc. not working with data.

Thanks.

Coordinator
Sep 28, 2009 at 10:04 PM

Hi,

Good question.  I will see what I can do for you.  What I do (for small tables) is I use SQL Server Management Studio to generate the data for the tables and then I delete everything generated by the Tasks / Generate Script ... except the insert statements.  Then you can run from SSMS directly against SQL Azure or save the inserts off into a file and run them through SQLAzureMW.

Anyway, I have a couple of ideas running around in my head on how to implement this.  I will see about bumping this up on my priority list.

Take care

Coordinator
Sep 30, 2009 at 2:24 PM

Hi,

I just wanted to give you an update that I expect to have the data migration posted by Oct 7th (maybe the 6th if I am lucky).

Regards

Sep 30, 2009 at 2:32 PM

Hi,

Thanks for this quick update :)

Regards

 

 

 

Coordinator
Oct 7, 2009 at 12:02 PM

Hi,

I have got good news and bad news.  The good news is that SQLAzureMW v 1.0 can migrate your data to SQL Azure.  The bad news is that I am relying on some new enhancements to SQL Azure that I was expecting to be pushed out yesterday that didn't make it.  I now understand that the new enhancements would be pushed out until Oct 13th.  Which means that I can't push out the new version of SQLAzureMW until the 13th or 14th.

Sorry for the delay!!!

Oct 7, 2009 at 12:13 PM

Hi,

I'm awaiting for v1.0 :)

Thanks.

Coordinator
Oct 14, 2009 at 2:09 AM

SQLAzureMW Version 1.0 has Shipped!!!

Note this works against the new version of SQL Azure.  It will not work against the old CTP version.

Enjoy!

Oct 14, 2009 at 9:08 AM

ghuey,

Thanks for update.But i've a problem with it.When i script table and data i'm getting an error like:

 

Error = [Microsoft][SQL Native Client][SQL Server]Server name cannot be determined.  It must appear as the first segment of the server's dns name (servername.database.windows.net).  Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername).  In addition, if both formats are used, the server names must match.

 

Thanks.

Oct 14, 2009 at 1:13 PM

ghuey,

Sorry for it.It's my bad :).It's about username

Nov 16, 2009 at 12:57 PM

Hi

I have the same error when trying to bulk upload data.

I have tried setting the username to username@server  either with or without the server name also included in the TCP: connection string.  all with either the same effect or a failure to connect.

Any further help would be appreciated.

Coordinator
Nov 16, 2009 at 5:44 PM

Hi, what is your error message?  Did you open up the SQL Azure Firewall for your address?  I am at PDC, so I will be a little slow responding.

Nov 17, 2009 at 8:20 AM

Hi Ghuey

I am not sure what the problem was in the end.  I had the firewall settings etc all OK.

In the end I tried again with the servername in both of the connection and the username much as I had the first time and it just worked.  I had perhaps been forwards and backwards a few times in the "wizard" and from time to time the scripts button was unavailable - perhaps all that confused it.

I have now used it to migrate several databases and (after manually dealing with the use of multi part identifiers in queries) all with success.

It's a very valuable tool.  In particular the use of BCP to upload the data.  We have a lot of data that is in nVarChar(max) really holding XML blobs and loading that with insert scripts is a pain.

Enjoy PDC, I am looking forward to some of what I know will be announced but also what I don't know.  Should be an interesting week.

Thanks for all your efforts.

GB

 

 

Coordinator
Nov 18, 2009 at 4:15 AM

Hi GB,

I am glad you got things working!  If you need anything else, just let me know.

Take care,

George

Dec 6, 2009 at 12:34 PM

Hi,

first I want to say that this is a great tool.

I have a problem uploading the data. All tables are migrated without problems, but the data isn't migrated. The log always says "Could not find number of records copied. File will not be uploaded.". Could you give me a hint what is going wrong?

Peter

Dec 7, 2009 at 6:40 AM
PeterSchroer wrote:

Hi,

first I want to say that this is a great tool.

I have a problem uploading the data. All tables are migrated without problems, but the data isn't migrated. The log always says "Could not find number of records copied. File will not be uploaded.". Could you give me a hint what is going wrong?

Peter

Hi,

I'm having the same problem, and I think that it is because the bcp.exe I was using was the version 9 and not the version 10.
My version 10 isn't working at the moment, I'm removing everything to reinstall it clean.

Mathieu

Dec 7, 2009 at 8:15 AM
Edited Dec 7, 2009 at 8:38 AM

Hi,

thank you for your very fast answer. How do I find out which version ob bcp.exe is used?
I have SQL Server 2008 R2 Nov CTP installed.

Peter

P.S. My bcp.exe is located in C:\Program Files\Microsoft SQL Server\100\Tools\Binn and its version is 10.50.1352.12. I think this should be right?!?

Dec 7, 2009 at 9:14 AM
Edited Dec 7, 2009 at 1:41 PM

I now have the same version than you, (10.50.1352.12. I) but I still have the same error, like you.

So the problem is coming from somewhere else and I don't know where.

edit: I think I found the reason . I am using a French version of BCP.exe, with French output.

SQL Azure MW is waiting for English output :

Match regMatch = Regex.Match(_bcpOutput.ToString(), "[0-9]+\\srows copied");

Dec 7, 2009 at 2:28 PM
clavelm wrote:I think I found the reason . I am using a French version of BCP.exe, with French output.

SQL Azure MW is waiting for English output :

 

Match regMatch = Regex.Match(_bcpOutput.ToString(), "[0-9]+\\srows copied");

 

I managed to make it work in French :

In the ScriptTableData method in ScriptDatabase :

I change

Match regMatch = Regex.Match(_bcpOutput.ToString(), "[0-9]+\\srows copied");
into
Match regMatch = Regex.Match(_bcpOutput.ToString(), "[0-9]+\\slignes copiées");

for the French output.

I had also to add

p.StartInfo.StandardOutputEncoding = Encoding.GetEncoding("cp437");
when creating the process for BCP.exe so the "é", "à", and other are printed.

Coordinator
Dec 7, 2009 at 8:08 PM

WOW and great job Clavelm!  I really need to move that to a config file.  In regards to BCP, I am currently using BCP version 10.50.1352.12 as well.  Peter, if you are still having problems, do a simple test by using BCP to export your data and upload.  On the upload, copy the output and post it so that I can see it.  If you want, we can take this offline.

Regards, George

Dec 7, 2009 at 8:32 PM

I'm from germany so this will be the same problem. When I try to compile the source code I always get errors so I wasn't able to change the source code. Do you have any idea how long it will take to move these settings to the config file? And how can I get the output of the upload to poste it?

Thanks for the help Peter

Coordinator
Dec 8, 2009 at 3:34 PM

Hi Peter, I made the modification and the Regex search strings are now in the SQLAzureMW.exe.config file.  See SQLAzureMW v2.1

Let me know how it goes or if you have any questions.

Regards,
George

Dec 8, 2009 at 9:48 PM

Thanks thats great. I already got it working with an virtual machine with an englisch sql server, but this is of course way better.

Thanks again Peter

Dec 9, 2009 at 7:23 AM

That's a nice change, thanks for it.

I still got problem with the diacritic in French (é, à, ù, ...)

Maybe putting the config for the Process running bcp.exe :

p.StartInfo.FileName = @"bcp.exe";
p.StartInfo.Arguments = bcpArgsOut.ToString();
p.StartInfo.CreateNoWindow = true;
p.StartInfo.UseShellExecute = false;
p.StartInfo.RedirectStandardOutput = true;
in the config file also, so I could add the line needed :

p.StartInfo.StandardOutputEncoding = Encoding.GetEncoding("cp437");

I don't know if it's feasible.

For the moment, I'm patching you're program on my computer.

Coordinator
Dec 11, 2009 at 1:31 PM

Hi Clavelm,

Can I get you to do me a favor?  Can you change your added line to:

p.StartInfo.StandardOutputEncoding = Encoding.Default;

Test that and see how it works?  Let me know the results and I will make changes one way or the other.

Thanks for your help,
George

Dec 11, 2009 at 2:29 PM
ghuey wrote:

Hi Clavelm,

Can I get you to do me a favor?  Can you change your added line to:

p.StartInfo.StandardOutputEncoding = Encoding.Default;

Test that and see how it works?  Let me know the results and I will make changes one way or the other.

Thanks for your help,
George

Sorry, it's not working :

 

Process Started ...
Using BCP to get data from table dbo.VENTE
*
Could not find number of records copied. File will not be uploaded.

D‚marrage de la copie...
1000 lignes copi‚es en bloc avec succŠs vers le fichier h“te. Total re‡u : 1000
[...]
1000 lignes copi‚es en bloc avec succŠs vers le fichier h“te. Total re‡u : 14000
14115 lignes copi‚es.
Taille du paquet r‚seau (octets)ÿ: 4096
Heure (ms) Total   : 32     Moyenneÿ: (441093.75 lignes par seconde)
[...]

 

Maybe putting the Encoding some way or another in the config file ?

I'm not good with that, I found the line

p.StartInfo.StandardOutputEncoding = Encoding.GetEncoding("cp437");

on the web.

Mathieu

Coordinator
Dec 14, 2009 at 4:27 PM

Hi Mathieu,

Ok, I added encoding to app.config.  Check it out in v3.0.

Let me know if it works!  Thanks,

George

Dec 15, 2009 at 7:14 AM
Edited Dec 15, 2009 at 9:59 AM

Hi George,

Yep, it's working.

I got another error that I already got from time to time when I try to copy a database.

It seems that the Wizard try to do others things in addition of the creation of the database.

It might come from the name of the database I try to create : [PR_992ff7d7-4d02-485a-ae58-3867a0f86445_original2] (without the []). Maybe it's too long.

System.Data.SqlClient.SqlException: The CREATE DATABASE statement must be the only statement in the batch.

There is also that problem that I put in the issue tracker : http://sqlazuremw.codeplex.com/WorkItem/View.aspx?WorkItemId=3288

Regards,

Mathieu

 

Consultez la fin de ce message pour plus de détails sur l'appel du débogage
juste-à-temps (JIT) à la place de cette boîte de dialogue.

************** Texte de l'exception **************
Microsoft.SqlServer.Management.Common.ExecutionFailureException: Une exception s'est produite lors de l'exécution d'une instruction ou d'un lot Transact-SQL.
---> System.Data.SqlClient.SqlException: The CREATE DATABASE statement must be the only statement in the batch.
à Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
à Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
--- Fin de la trace de la pile d'exception interne ---
à Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
à Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand)
à SQLAzureMW.CreateDatabase.btnCreateDatabase_Click(Object sender, EventArgs e)
à System.Windows.Forms.Control.OnClick(EventArgs e)
à System.Windows.Forms.Button.OnClick(EventArgs e)
à System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
à System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
à System.Windows.Forms.Control.WndProc(Message& m)
à System.Windows.Forms.ButtonBase.WndProc(Message& m)
à System.Windows.Forms.Button.WndProc(Message& m)
à System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
à System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
à System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


 

Coordinator
Dec 15, 2009 at 12:46 PM

Hi Mathieu,

The problem was the characters in the database name.  I modified the create database statement to put your database between [ and ] and now SQL knows that it should treat the name as one word.  I published the fix in v3.1.  Check it out and let me know if you run into any more problems.

Thanks!
George

Dec 15, 2009 at 1:45 PM

Hi George;

It's working fine, for the create database and the copy of table with only one row. Thanks a lot !

Just one last bug, easy to solve : you are calling bcp.exe in two classes : ScriptWizard and ScriptDatabase.
In ScriptDatabase you use:

p.StartInfo.StandardOutputEncoding = Encoding.GetEncoding(ConfigurationManager.AppSettings["BCPCodePage"]);

but not in ScriptWizard (line 1435).

Regards,
Mathieu

Coordinator
Dec 15, 2009 at 9:51 PM

Hi Mathieu,

Thanks for helping me out!  I make the change and posted it to v3.1.1.

Regards,
George

Jul 16, 2010 at 4:35 PM

Hi,

My database name had a "." in it, and the export kept failing until I removed the "." Maybe this can be fixed in code?

Thanks,

Ramesh

Jul 16, 2010 at 4:53 PM

Hi,

My tables had Unicode (Tamil) data stored in nvarchar columns, so I had to add the -w switch and remove the -q and -n switches from the values of the BCPArgsIn and BCPArgsOut keys to get the export to work properly. Not sure if removing the -q and -n switches will have any adverse effect. Any suggestions?

Thanks,

Ramesh

Coordinator
Jul 16, 2010 at 9:08 PM

Hi,

I will play with createing a database with a "." in the name and see what I can do to fix this problem.  In regards to changing the BCPArgsIn and BCPArgsOut, I don't see that there are any adverse effects.  You did the right thing here to get BCP to work for you.

Thanks,
George

Coordinator
Jul 17, 2010 at 5:54 PM

Hi Ramesh,

I figured out that BCP does not like having a 4 part name so I had to separate database name from table name and BCP is now happy.  So, check out v3.3.3 and see if it works for you.  If it does not, please let me know.

Thanks,
George

Aug 6, 2010 at 2:08 PM

Hi for all,

I have a problem with SQLAzureMW. I don't make BCP of the my database.

My database is down for a server and i'm running the application in my workstation. I have SqlServer 2008 R2 in booth computers, but I running in my workstation an error occurs "Could not find number of records copied. File will not be uploaded."

The version is 3.3.5

Thanks,Sérgio

Coordinator
Aug 8, 2010 at 9:52 PM

Hi,

Yes, I have seen that when the number of records recorded in the script do not match the number of records in the BCP output file.  For now, I would delete everything in my output directory and then do a clean run.  I will also see about changing the structure of the application to not require this check at all.  This might take me awhile, but I will put it on my todo list.

Regards, George

Aug 10, 2010 at 10:54 AM

Hi

How to  make import data from SqlServer 2008 R2 to SqlAzure?

Regards,

Sérgio

De: ghuey [mailto:notifications@codeplex.com]
Enviada em: domingo, 8 de agosto de 2010 21:34
Para: Sérgio Rezende Jr.
Assunto: Re: Inserting Data [sqlazuremw:70239]

From: ghuey

Hi,

Yes, I have seen that when the number of records recorded in the script do not match the number of records in the BCP output file. For now, I would delete everything in my output directory and then do a clean run. I will also see about changing the structure of the application to not require this check at all. This might take me awhile, but I will put it on my todo list.

Regards, 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
Aug 10, 2010 at 8:44 PM

Hi Sérgio,

There are several ways to take data from SQL Server 2008 R2 and import it into SQL Azure.

1)    BCP. BCP is what SQLAzureMW uses. I use BCP to export the data from SQL Server and I use BCP to upload data to SQL Azure. Check out http://msdn.microsoft.com/en-us/library/ms162802.aspx for more information on BCP.

2)    Export Data. Another way to move data from SQL Server 2008 R2 to SQL Server is to use the Export Data (right mouse click on your database, slide over Tasks and then select Export Data. Export data lets you define your source database and your destination database. Note that when your destination is SQL Azure, that you must choose “.Net Framework Data Provider For SqlServer” in the Destination dropdown list box. Also, for the user ID, you are required to put the @SERVER at the end of the user name just like you do for SQLAzureMW. You will need to enter “User ID (UserName@ jy4kadhxma, Data Source (jy4kadhxma.database.windows.net), Password, and Initial Catalog.

3)    SSIS. You can create an SSIS package to move your data as well. IMO, SQLAzureMW, BCP, and Export Data are the simplest. SSIS might take a little more time to setup, but if you want to do data transformation and other data type jobs, then SSIS works very well.

4)    SQL Scripts. You can always use Generate Scripts from SQL Server 2008 R2 (be sure on the advanced button that you specify SQL Azure as your destination database) and script your data. Note that I would only do this for small tables.

I hope this helps,
George

Coordinator
Aug 17, 2010 at 5:47 PM
Hi Sérgio, In regards to your orginal question, the problem is that I parse the output from BCP and look for the rows copied. If you look in the SQLAzureMW.exe.config file you will see the Regex for this (look at key BCPRowsCopied). The problem usually is that my system is setup in English and if your system is setup for a different language, then the output from BCP will reflect that language. This means that you would have to modify the Regex search string to reflect your language. The best way to do this is to do a BCP from a command line and point to one of your tables and at the end, look at the rows copied and make the BCPRowsCopied match. If you want, copy the last part of your BCP output and send it to me and I will figure out your regex string. Regards, George
Jan 10, 2012 at 3:45 PM

Hello ghuey,

thanks for this tool, I wanted to point out that for the Italian language instead of pointing, "<add key="BCPRowsCopied" value="Righe copiate: [0-9]+."/>" you must enter: "<add key="BCPRowsCopied" value="[0-9]+\srighe copiate"/>"

Regards, Max

Coordinator
Jan 11, 2012 at 12:34 PM

Hi Max!

This is great to know and I will make the change.

Thank you very much for the feedback.

George