programming4us
 
 
 
SQL Server

Migrating Databases and Data to SQL Azure (part 9)

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
12/29/2010 3:53:15 PM
3.3. Importing the Data

The next step is to copy the data into the cloud—specifically, to your SQL Azure TechBio database. The syntax for copying into a database is very similar to the syntax for copying data out. You use the in keyword and specify the server name and credentials for your SQL Azure database, as shown in Figure 18.

Figure 18. Uniqueidentifier data type error during bcp import

After you type in the command, press Enter to execute the bcp utility. Only one row is copied over, and then an error is generated, stating that an unexpected end-of-file (EOF) was encountered. This error isn't specific to SQL Azure; the bcp utility has issues with columns of the uniqueidentifier data type. You can find posts and blogs all over the Internet about this problem.

The solution is to execute the following T-SQL against the Users table in your SQL Azure database:

alter table users
drop column rowguid

The cool thing is that you don't need to re-export the data. You can re-execute the bcp import command. Do that, as shown in Figure 19, and all 105 rows are imported. Then, use the same syntax to import the Docs and UserDocs data.

Figure 19. Successful bcp import

Don't forget to put the rowguid column back on the Users table. You can do this by using the same syntax as before:

ALTER TABLE Users
ADD rowguid uniqeidentifier

Next, let's make this a little simpler and put the export and import together, so you aren't running the statements one at a time.

3.4. Putting the Export and Import Together

You can put the export and import processes together into a single file to make them easier to use. To do so, open Notepad, and type in the following, replacing the italicized information with the appropriate information for your environment:

bcp Techbio.dbo.Users out c:\scott\user.dat -S Scott-PC -U sa -P Password -n -q
bcp Techbio.dbo.Docs out c:\scott\docs.dat -S Scott-PC -U sa -P Password -n -q
bcp Techbio.dbo.UserDocs out c:\scott\userdoc.dat -S Scott-PC -U sa -P Password -n -q

bcp Techbio.dbo.Users in c:\scott\user.dat -S servername.database.windows.net
-U SQLScott@ servername -P #ackThis -n -q
bcp Techbio.dbo.docs in c:\scott\docs.dat -S servername.database.windows.net
-U SQLScott@ servername -P #ackThis -n -q
bcp Techbio.dbo.userdocs in c:\scott\userdoc.dat -S servername.database.windows.net
-U SQLScott@ servername -P #ackThis -n -q
Save the file as AzureBCP.cmd, and navigate to its location.
Double-click the file to execute it. A command window appears, showing the results of the bcp commands that export and import the data.

As stated earlier, SQL Server BOL is full of information about how to use the bcp utility. This section is a brief introductory look at how to use this utility to move data from your local SQL Server instance to SQL Azure. The bcp utility is bulk-copy method of moving data. It lacks SSIS's ability to convert data from one data type to another, and SSIS's workflow components. But if all you're interested in is moving data from one table to a similar destination table, bcp is your best friend.
Other -----------------
- Understanding Service Broker Constructs (part 5)
- Understanding Service Broker Constructs (part 4) - Creating the Conversation Initiator
- Understanding Service Broker Constructs (part 3)
- Understanding Service Broker Constructs (part 2) - Creating Queues for Message Storage
- Understanding Service Broker Constructs (part 1) - Defining Messages and Choosing a Message Type
- SQL Server 2008 : SQL Server Service Broker - Designing a Sample System
- SQL Server 2008 : SQL Server Service Broker - Understanding Distributed Messaging
- SQL Server 2008 : Full-Text Search Troubleshooting
- SQL Azure : Security - Access Control
- SQL Server 2008 : Full-Text Searches (part 3) - Stop Lists
- SQL Server 2008 : Full-Text Searches (part 2)
- SQL Server 2008 : Full-Text Searches (part 1) - Search Phrase
- SQL Azure : Securing Your Data (part 3) - Certificates
- SQL Azure : Securing Your Data (part 2) - Hashing
- SQL Azure : Securing Your Data (part 1) - Encryption
- SQL Azure : Security - Overview
- Setting Up a Full-Text Index (part 4) - Using the Full-Text Indexing Wizard to Build Full-Text Indexes and Catalogs
- Setting Up a Full-Text Index (part 3) - Diagnostics
- Setting Up a Full-Text Index (part 2) - Full-Text Indexing of BLOBs and XML
- Setting Up a Full-Text Index (part 1) - Using T-SQL Commands to Build Full-Text Indexes and Catalogs
 
 
 
Top 10
 
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
Video Sports
- The Banner Saga 2 [PS4/XOne/PC] PC Launch Trailer
- Welkin Road [PC] Early Access Trailer
- 7th Dragon III Code: VFD [3DS] Character Creation Trailer
- Human: Fall Flat [PS4/XOne/PC] Coming Soon Trailer
- Battlefleet Gothic: Armada [PC] Eldar Trailer
- Neon Chrome [PS4/XOne/PC] PC Release Date Trailer
- Rocketbirds 2: Evolution [Vita/PS4] Launch Trailer
- Battleborn [PS4/XOne/PC] 12 Min Gameplay Trailer
- 7 Days to Die [PS4/XOne/PC] Console Trailer
- Total War: Warhammer [PC] The Empire vs Chaos Warriors Gameplay Trailer
- Umbrella Corps [PS4/PC] Mercenary Customization Trailer
- Niten [PC] Debut Trailer
- Stellaris [PC] Aiming for the Stars - Dev. Diary Trailer #1
- LawBreakers [PC] Dev Diary #4: Concept Art Evolutions
programming4us programming4us
 
Popular tags
 
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS