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


4

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


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 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

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 ...


3

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 ...


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 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. ...


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

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

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 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 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

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

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

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.


1

You can use the BCP unicode native format to import/export. Use bcp -n for the export and the WITH (DATAFILETYPE='widenative') clause for import. Click link to read more.


1

No you can't take the output of mysqldump and use it with bcp. Mysqldump creates a SQL Script which can then be run. If the schema is the same you can probably just run the outputted script using sqlcmd.


1

I just got your code to work on my end. Try this: Option A Assign Full Control NTFS permissions to 'C:\BCPFiles\bcpFile.dat to the user you will use in SQL Server Management Studio to run the query Re-run your query in SQL Server Management Studio Option B Or, just to get past the following error try: Error = [Microsoft][ODBC Driver 11 for SQL ...


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 ...


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

I found the reason that did not work, and really was not password correct because I used the password to these characters who divided password to pieces, and of that reason but it was not correct. I used to password ",", "$" and other characters.


1

Copy bcp.rll from C:\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033. Put bcp.exe & bcp.rll in the same folder. That should allow BCP to run.


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

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

This is a long shot but seen as you are running SQL 2014 Enterprise I would have a test with using a Clustered Columnstore Index on this table and see if this improves performance for you. Especially considering that you are using the table for selects and bulk inserts only - no updates and no deletes. You will have the added advantage of taking a bit of ...



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