Tag Info

Hot answers tagged

10

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


4

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


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

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


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


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)


1

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


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

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

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

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

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

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

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

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

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.


1

I believe that BCP does a two phase process. The first is done with a format only where the query isn't actually executed but it is parsed so that BCP can figure out the columns that will be returned and in the second phase it actually runs the query. You are failing on the first phase as the temp table doesn't exist so it can't be used for the format only ...



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