I'm using bcp to import a flat file into an SQL Server 2005 database. I'm running into a problem with datetime fields. My format file field is specified as: <COLUMN SOURCE="15" NAME="DATEOFSERVICE" xsi:type="SQLDATETIME"/> My data file has dates formatted as: 19820101 However, some (many) are filled with 00000000 The 00000000 date...
Hi, We have a database table that we pre-populate with data as part of our deployment procedure. Since one of the columns is binary (it's a binary serialized object) we use BCP to copy the data into the table. So far this has worked very well, however, today we tried this technique on a Windows Server 2008 machine for the first time...
Hi, I need to have ability in my application to be able to export some data using the SQL Server BCP utitility. The machines where this utility would run would not have SQL Native Client installed on it, so BCP.exe won't be present by default. Can I put a version of BCP in the installation package. What are licensing implications? I ...
Hi, I've a sample table, and it has cols of a few types including datetime. I'm trying to test bulk insert into it using the bcp_* api. I encounter a problem when trying to insert datetime values into the DB (error msgs include 'invalid date time format' when I try to insert as text or 'numeric value out of range' when trying to use ...
Hi! I'm having a problem "Invalid time format" when I try to use bcp functions(bcp_sendrow/bind etc.) to insert into a table having a datetime column. so as per googled examples, I populated a DBDATETIME structure, for example: if I wanted to populate 3rd august 2009, exactly 8am localtime, I'd populate the structure members like so: d...
I have seen a number of hacks to try to get the bcp utility to export column names along with the data. If all I am doing is dumping a table to a text file what is the most straightforward method to have bcp add the column headers? Here's the bcp command I am currently using: bcp myschema.dbo.myTableout myTable.csv /SmyServer01 /c /t, ...
Hi, I need to export all data from a database into a text or csv file from MSSQL Server2000. I have read from msdn and found that a tool 'bcp' could be useful for exporting data into text file. But MSSQL Server 2000 is not supporting 'queryout' keyword for it on EnterpriseManager Editor. Is there any other possible way with which i can ...
I'm trying to use the SQL Server bcp utility to import a text file from a samba share. bcp is choking on the Unix line endings. I'm sure I could add an intermediate step, either on Unix or Windows, to change the line endings to Windows-style. But I would prefer to import the files from Unix without modification. Anybody know if there's ...
We're using named pipes with Sybase bcp so that we can compress output on-the-fly. This is a paraphrase of the error handling idiom, some error checking in the non-bcp parts of the script has been removed to shorten the example. while : do { rm -f $fifo mkfifo $fifo cat $fifo & CatPid=$! b...
This struck me as really weird behaviour and I spent a while checking for bugs in my code before I found this "out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty ...
Hi!, I´m having trouble with bulk insert a file that contains a field in data type text, and it holds enters and the delimiter for the fields are pipes "|" and the row terminator is "|\n" I get an error of truncation for the fields next to the text field. I think that the bulk insert thinks that the enters in the second field are the n...
Hi, i need to dump my sql query result into the text file. i have created the following query, DECLARE @cmd VARCHAR(2048) SET @cmd = 'OSQL -localhost -CRN370 ' + ' -UCRN370 -PCRN370' + ' -Q"SELECT TOP 5 GageId FROM EwQMS370..msgages"' + ' -oc:\authors.txt' EXEC master..xp_cmdshell @cmd, NO_OUTPUT The above query created the tex...
Running this: bcp MyDb.dbo.uvwMyView out "c:\Test.txt" -SMyServer -T -c I get this error: SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Native Client]Unable to resolve column level collations Searching google finds many possible solutions, none of which work for me or have worked for any of the people they were proposed...
I continually get a logon error no matter which logon I use or from what tool I approach this (shell/sqlsms) output ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------...
We are upgrading a 15 year old code-base, there is a requirement to create some native BCP formatted data files. In the new system, we would ideally like to utilize data in a C# DataTable object to create the data file in native BCP format. Can this be done and if so, what would be the best approach? ...
I have built a SQL Server Express database that is going to be housed on an external hd. I need to be able to add/update data on the database that is on my system, as well as other systems and then only backup or transfer data that has been added or edited to the external hard drive. What is the best way to accomplish this? ...
I need to parse a flat file which is containing backspace (^H) character delimiter between fields. I need to parse this file and insert into sql server 2005 tables.I tried to use bcp utility along with the format file but I wasn't able to specify the delimiter as backspace. The default one is tab (\t). There are several other delimiters ...
I need to export some data using SQL Server 2000's BCP utility. Sometimes my data contains characters, such as \t and \n, that I need to use as column and row terminators. How do I get BCP to escape characters it's using as terminators as it outputs the data, so that I can actually import the data in another program? For example, one ...
What is the difference between bulk copy (bcp) and bulk insert? Is there a switch to export all of the data in all of tables in a database for bcp? ...
When would we use a format file for bcp? ...