Hot answers tagged

11

This is not a direct answer to your question, but there are some articles that you would benefit from reading them (in case you didn't find them first :-) ). They are about loading lots of data using bcp/bulk copy. I've read them all and I didn't find anything detailed regarding KILOBYTES_PER_BATCH, they're all using ROWS_PER_BATCH, but I'm sure you will ...


6

You have to use one of the SQL Server distributable packages to stay within licensing terms. These are not full installs. The one you want is "Microsoft® SQL Server® 2008 R2 Command Line Utilities" Note: copying bcp.exe by itself will not work


6

Powershell is your friend here. When working with cmd commands in Powershell you can use the $LASTEXITCODE variable to read the result of the command you executed. The code below passes a BCP command into the Invoke-Expression cmdlet and captures it's output. $OutputPath = "C:\temp\Numbers-20151230.dat" try { $Command = "bcp dbo.Numbers out $...


5

A varbinary value is of the form 0x01020304 for SQL Server to read. So your CSV would be col1,col2,varbinary foo,1,0x01020304 bar,2,0x988776655443 Edit May 2013, After testing, I can't get this to work with BULK INSERT with or without a format file. I will post something more when I get it working. I can't delete this answer


5

One option you might consider is separating the two. Create one file with your export and all fields. The other file just has your header. The last step would be to combine the two with something like this: REM create the header file ECHO 100|0|SSO|UPDATE|EN|N|N| >"MyExport.txt.header" REM append the bcp export to the header file TYPE "MyExport.txt">&...


4

You would need to add the folder containing bcp.exe to the environment variables search path.


4

Presumably you are expecting the .csv file to be opened using Excel or some equivalent. I'd recommend wrapping the outputted text columns in double-quotes, such as: declare @sql VARCHAR(1000); Select @sql = 'bcp.exe "SELECT ''"userid"'',''"name"'',''"data"'', ''"data1"'',''"data2"'' UNION ALL SELECT CHAR(34) + CAST([userid] As VARCHAR(MAX)) + CHAR(34) , ...


3

Is it safe to assume columns of NVARCHAR, NTEXT, NCHAR, BIT, INT, DECIMAL, FLOAT, and DATETIME all MUST be UNICODE... Only the XML and N-prefixed types (NCHAR, NVARCHAR, and NTEXT [which has been deprecated since SQL Server 2005 was released so please do not use it]) are Unicode. Those other types you mentioned are not strings and are not stored as strings,...


3

The first problem that I see is that you are concatenating Unicode and non-Unicode values together. A better way of dealing with this would be to keep the stored procedures in some sort of source control then deploy the stored procedures from source control after the database is restored. Or better yet restore the database you are testing to a different ...


3

I have seen this before. Ensure your datetimes are formatted like this: yyyy-mm-ddThh:mm:ss Example: 2011-01-18T17:30:59 would represent January 18th 2011 at 5:30:59PM It has been a while since I ran into an issue similar to this, but I remember this being a step I took to resolve my problem.


3

If you make sure that your filesystems are on striped storage. That way you can prevent the io bottleneck. Just specifying different location without knowing what kind of storage they are on, does not make much sense. This workload typically has large scans and large writes so striping will certainly help you. Ronald.


3

It is technically possible, yes. But you may be able to leverage performance gains by having multiple files on multiple disks as opposed to just one file. Why not try using one file and see if it works, and what the performance is like? Here is a link for info on BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx


3

I had the same - somehow a windows or SQL Server update changed something and a previously successful build started failing. In my case it was access permissions to the folder containing the dat files that was failing. I could run the bcp from the command line and it worked but it failed from SQL Server. The SQL Service needs access to the folder, basically. ...


3

You can create a CMD script to create a temp header row file, run BCP, and then append the BCP output to the temp header file. This would be called via xp_cmdshell, just like the existing call to BCP in your current setup. Here is the CMD script, which I named AddHeaderToExportFile.cmd. It takes two parameters: The filename. The header row. If it changes, ...


2

You probably look for the file on the client computer. xp_cmdshell executes on the server, so look for C:\test.txt on the SQL Server machine.


2

For sysadmins, xp_cmdshell runs the command as the SQL database engine service account. Make sure that account has been granted write permissions on the target location.


2

As I wrote in the comment to your previous post: the bcp syntax look ok. I double checked with BOL and the only limitation for bcp is that if you use a procedure all tables have to exist prior to bcp being executed No temporary tables or bcp will fail! Update 1: Here is an example on how to exclude columns in the format file (*.fmt) for the bcp ...


2

You have CR\LF in shell invocation, a no-no. Make it a single line: DECLARE @SQL VARCHAR(8000); SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" '+ 'queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_20121120.txt" '+ '-f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + ''; EXEC ...


2

You can read the bvp.exe path from registry and then simply use full path RootKey : HKEY_LOCAL_MACHINE PathKey : SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\ClientSetup Key : path (String)


2

You're lacking the -E flag for identity columns, which allows you to preserve the values instead of re-seeding them on the import. bcp DocControl.dbo.Documents in Documents.dat -n -E -S SERVER\SQLEXPRESS -T From MSDN, [-E] Specifies that identity value or values in the imported data file are to be used for the identity column. If -E is not given, ...


2

The solution seem to be to first execute BCP manually, set prefix to 0 in the user dialog. And then use the generated .fmt-file as in this command: EXEC master..xp_cmdshell 'BCP "SELECT my_column FROM my_table WHERE my_id = 11 " queryout "C:\bcpdir\bcpout.xml" -T -f "C:\bcpdir\bcp.fmt'


2

The problem with using UNION to include the column names is that column names are strings but the fields in the unioned query are not necessarily strings (as you discovered with that error posted in the Question). And you cannot convert names into other types such as INT, DECIMAL, MONEY, DATETIME, etc. I did not have to provide any cast/convert when I ...


1

If your first test showed a problem, assume that there is a problem! Problems don't just disappear on production, even if you have the best possible theory for their existence, you still need to test. BCP copy should be using row-level locks by default. Which should escalate to page-level locks due to the size of your data. If despite these locks, you are ...


1

The solution I settled on was to specify the database name using the -d flag. bcp "SELECT char(34) + REPLACE([Code], '""""', '""""""""') + char(34), char(34) + REPLACE([Description], '""""', '""""""""') + char(34) FROM [Table Name]" queryout C:\Users\jwallace\out.csv -c -t "," -d "Database Name With Spaces" -S "10.0.0.1,1848" -U ...


1

I managed to make this work by combining all columns into one single string from SQL and return it to BCP tool.


1

Finally found a solution to this. Turns out that it won't throw an error if there is a standard string in a field that should have a GUID. It could be because the said string was contained in colons.


1

Well we tend not to care what the originators table structure is, but only if it meets our requirements (which we send to them). If you are trying to figure out how to design a way to store the data permanently because you don't currently have a structure, then this is the method I use. Import the file into a staging table (not the final permanent table, I ...


1

You can do this using the BCP format parameter. This will create a format file of the table for you. Based on your requirement, you can create XML/native/character formatted files. eg: bcp db_name.schema_name.table_name format nul -T -n -f format-file.fmt


1

Or just use the full path to the file: C:\>"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp" rest_of_options_here


1

At first I would try to change everything to be on the same line: Instead: SELECT @SQL = 'bcp "EXEC ispsSelectEmptyAsNull ''B1A'';" queryout "F:\aaData\IPACostData\R15TData\2BSHAEOS_B1A_20121120.txt" -f "F:\aaData\IPACostData\R15TData\tmpFormatCard_B1A.fmt" -T -S' + @@SERVERNAME + ''; Change it to: SELECT @SQL = 'bcp "...



Only top voted, non community-wiki answers of a minimum length are eligible