I've been banging my head against SQL Server 2005 trying to get a lot of data out. I've been given a database with nearly 300 tables in it and I need to turn this into a MySQL database. My first call was to use bcp but unfortunately it doesn't produce valid CSV - strings aren't encapsulated so you can't deal with any row that has a str...
I have a format file where I want one of the columns to be "group". I'm auto-generating the format file and a client wants to upload a file with "group" as one of the columns. I could restrict it so they can't use SQL keywords, but then I need a function to determine if a column name is a SQL keyword, so I'd like to support the user be...
Hi, I have created a proc that grabs all the user tables in a local DB on my machine. I want to be able to create a flat file of all my tables using BCP and SQL. Its a dummy database in SQL 2000 connecting through windows authentication. I have set my enviroment path variable in WinXP SP2. I have created new users to access the db, swi...
(ANSWER) How to export some Geography data from a Microsoft Sql Server 2008. You'll need to use the command line argument BCP to dump the data in it's original (native) format to a binary file. Then on the other server you can bulk insert this binary data back into a table of the same strucutre. here's some code. Export Command Line:...
Hi My reuirement: Input File: 1,abc,xyx 2,def,mno 3,ghi,suv DB Table Structure: Col1 char col2 char col3 char col4 char col5 char Data in Table after BCP: col1 col2 col3 col4 col5 1 abc xyz ab xy 2 def mno de mn 3 ghi suv gh su Basically the col4 and col5 are calculated values from col2 and col3 v...
Hi I have a table which has a column which is of type xml. I have to extract data from this table and load the data into another environment. i am using bcp to extract and laod the target table but there are some special characters that is causing some issues when i bcp them into the target table. are there any workarounds thanks Ben ...
I am looking for either a NAnt Task for SQL Server bcp, or the file format for bcp native output. I supposed I could build a NAntContrib Task for bcp but I don't have time at the moment (Do we ever?). Has anybody strolled this path before? Advice? Thanks - Jon ...
Here is my sample data: 1;a;b;c;; 2;d;e;f;; 3;g;h;i;; 4;j;k;l;; 5;m;n;o;; 6;p;q;r;; Here is my sample format file (BCP 9): 9.0 7 1 SQLCHAR 0 0 "" 0 x Latin1_General_CI_AS 2 SQLCHAR 0 0 ";" 2 i Latin1_General_CI_AS 3 SQLCHAR 0 0 ";" 3 s Latin1_General_CI_AS 4 SQLCHAR 0 0 ";" 4 t Latin1_General_CI_AS 5 SQLCHAR 0 0 ";" 5 u Latin1_Genera...
First Observation, limitation with this BCP format file Data File: (Continuous up to three lines) 1;a;b;c;d;e;f;; 2;d;e;f;g;h;i;; 3;g;h;i;j;k;l;; 4;j;k;l;m;n;o;; 5;m;n;o;p;q;r;; 6;p;q;r;s;t;u;;What bcp format file that I'm gonna used to insert 59 columns? Format File: 9.0 9 1 SQLCHAR 0 0 "" 0 x "" 2 SQLCHAR 0 0 ";" 2 col1 Latin...
How do you use a binarywriter to write the correct MS SQL native format for the Money data type? I'd like to take a value in .net, read from a file as string representation of a decimal amount (actually an exported "Money" data type from SQL, but that is unimportant). How can I use a binary writer to write the value so that you can u...
I want to use the bcp command in Linux as the following: bcp "EasyCash.dbo.user" out "[EasyCash].[dbo].[user].dat" -q -c -t "<EOFD>" -r "<EORD>" -Usa -P123456 -S192.168.1.5 The message in Linux shows like that: -base bcp: command not found Can anyone solve my problem? ...
Is there a direct route that is pretty straight forward? (i.e. can SQL Server read XML) Or, is it best to parse the XML and just transfer it in the usual way via ADO.Net either as individual rows or perhaps a batch update? I realize there may be solutions that involve large complex stored procs--while I'm not entirely opposed to this,...
I would like to use SSIS to create database table flat file exports then import them in to variously distributed SQL Express installs. As you may guess, SSIS is not available in the Express version. When I do the bulk insert, it errors with: Msg 4866, Level 16, State 8, Line 1 The bulk load failed. The column is too long in the data f...
I'm working on a a tool to generate TSV files for import into a database using bcp.exe and I'd like to validate my output. I can do this by comparing the file I generate to the files produced by exporting using bcp from an existing database. My problem is that the ordering can sometimes be different between files. I'd like a tool that wi...
I'm writing an application that is trying to bulk export data from a SQL Server database (with a given connection string) to a local file (in whatever format). Normally the sql server utility BCP.exe would be ideal, however, the bcp utility may not be available on the machine my app is running on. Also, bcp is not considered a redistri...
I want to export some SQL Server 2005 data to CSV format (comma-separated with quotes). I can think of a lot of complicated ways to do it, but I want to do it the right way. I've looked at bcp, but I can't figure out how to put the quotes around the fields (except concatenating them to the field values, which is ugly). I guess I could do...
If I have a large amount of data in memory, what is the best way to copy it into a SQL CE table? The current technology stack is C#, ADO.net, and SQL CE. My initial idea was to do one INSERT statement for each row of data, but this is time-consuming. Is there an easier way? ...
I have a csv file and i need to import it to a table in sql 2005 or 2008. The column names and count in the csv are different from the table column names and count. The csv is splitted by a ';' . Example CSV FILEcontents: FirstName;LastName;Country;Age Roger;Mouthout;Belgium;55 SQL Person Table Columns: FName,LName,Country ...
I have a console app that uses two batch files to decompress a zip file, take the xls which was decompressed convert it to CSV and BCP the data into a SQL table. I'd like to schedule this console app to run once a day. I set up windows task scheduler to run said app. I try to "test run" the task and don't get an error. I have the app wri...
I am running a bulk copy of two columns of a table from one server to another. The table on the source side has about 8 columns, but I only need 2. The table on the destination side has 2 columns (the two that I need, both are of type int) Both databases are SQL Server 2005. Here's my two bcp commands: c:\> bcp "select c1, c2 from s...