Hi All,

Thanks for your reply.

I'm creating a BCP stored procedure that I will eventually put into a .NET app but I'm having trouble with the BCP command. I'm testing the BCP at the command line and in QA.

This command works at the command line and in QA as long as I'm using a csv file:

bcp NoethDEV..Rule1173Import in C:\SignalHill_WestPlant_1173.csv -c -F2 -t , -r \n -SPIANO -U"sa" -Pavanti!2472

But there are commas in the data and I need to delimit using tab delimited. This command works at the command line but not in QA. Does anyone have any idea why?

bcp NoethDEV..Rule1173Import in C:\SignalHill_WestPlant_1173.ctxt -c -F2 -t \t -r \n -SPIANO -U"sa" -Pavanti!2472


DECLARE @bcpCommand varchar(2000)

SET @bcpCommand = 'bcp NoethDEV..Rule1173Import in C:\SignalHill_WestPlant_1173.ctxt -c -F2 -t \t -r \n -SPIANO -U"sa" -Pavanti!2472'

EXEC master..xp_cmdshell @bcpCommand
output
----------------------------------------------------------------------
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
NULL

(3 row(s) affected)

Your response is much appreciated.

Thanks,
Richard

First of all, since you just published the server instance name and the SA login and password to the whole bloody world, I'm thinking that you need to change the password on your server RIGHT AWAY!!! Then, when posting questions of this nature, you need to be a bit more careful.

The problem with the file not found thing is that when YOU run it from a command line... you are logged in as YOU with all the correct read permissions to the file. When you do it from QA using xp_CmdShell, the server has to find the file as ITS SERVICE is logged in.

If you are using the default to start the SQL service, you will need to change it to login as a (very secure well kept secret login) domain user (under properties for the DB in EM). You will also need to change the path to a UNC (machinename\\path\file) instead of "C:\yada-yada".

Of course, you could avoid all of that by putting the file(s) to be imported on one of the server drives... SQL can certainly see it's own drives.

--Jeff Moden

Hey Jeff. Thanks man. Much appreciated. I moved the file to the server and it worked like a charm. Yeah, OK, that's just made up login info anyway.

I'm going to be using a file uploader component in a .NET app so it will be just as easy to move the files to the server.

Just two minor problems with creating the tab-delimited files from Excel is that if there is a comma in any of the text cells it puts double quotes around the text in the tab-delimited .txt text file. Its easy enough to clean afterwards. The other thing is that there are umpteen null rows in the tab-delimited text file.

Just wondered if you know anything about those issues.

Thanks a million.

Richard

For the first problem about the comma and the quotes it causes... if you use DTS instead of BCP, that would allow you to define the "Text Identifier" as quotes. It's a bit slower than BCP but not real bad or anything.

Usually, though, just to be on the safe side, I load the data with either BULK INSERT (is actually faster than BCP and does NOT require "SA" privs to run) or BCP like you are but I load it into a "Staging" table... Here, I can fix things like the quotes and do some extreme data validation long before I let the data anywhere near my production data. It also allows me to NOT copy any Null Rows that might occur into production data... Might want to give this some thought...



--Jeff Moden

Hi Jeff,

Thanks for the reply.

I got to thinking about the permissions thing, cmd versus QA and it makes sense what you say about the permissions on command line but in QA, as long as I was referencing a .csv file and not a txt file it seemed to be working.

Well, I did something really dumb. It wasn't a permissions thing at all. Ding, dong. At one point I had copied the .csv file to the server and not the txt file. So BCP just couldn't find the file but you could never tell from the MS error, "Could not open file...", Hey, I'm glad this happened. I know so much now about BCP and how I can make it work with UNC like you suggested.

However, we ended up using BULK INSERT anyway to a temporary table where we can run some cleaning scripts to make sure everything is in order before it goes to "production". The .NET upload doesn't care about permissions on the file, I think that it just loads the file to the server before it even gets read.

Thanks a million. Much appreciated. See ya around.

Richard

My pleasure, Richard... Sounds like you guys are doing the right thing with the staging table. Thank you for the feedback...


--Jeff Moden