March 20, 2002 at 8:13 am
Hi all . . .
Am trying to use bcp to do a bulk insert into a table. The syntax I'm using is as follows:
bcp "[Database_Name].dbo.[Table_Name]" in "filename.txt" -r"~" -m25 -U"sysadminID" -P"sysadminPW"
Note: the tilde "~" is my flat file field delimiter, and the maxerror flag is set because I'm trying to import into a unique constraint table.
However, when I try to run this, I continually get the following error:
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login '[Database_Name]'. Login fails.
I have double, triple, and quadrupled checked the login ID and password (they are correct). I've even tried other login IDs and passwords. I *think* my syntax is correct.
So why do I keep getting this error? Am checking everything I can think of, and am pulling my hair out!
Any ideas? (I'm guessing it's the security permissions, but I've checked them as well, and they *seem* to be okay.)
Thanks in advance for your help.
March 20, 2002 at 8:36 am
First off the login itself is not failing it is the database login (not server) that is failing. I tried this and ended up with the name thing until I did the follwoing
bcp "Database_Name.dbo.[Table_Name]" in "filename.txt" -r"~" -m25 -U"sysadminID" -P"sysadminPW"
So all I did was remove the [ and ] from dbname and works for me. Try it and see what happens.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 20, 2002 at 9:01 am
Tried your suggestion, and am getting the same problem.
One thing I should mention: both my database and table names have spaces in the names (i.e.: they're actually "database name" and "table name," not "database_name" and "table_name"). I tried different variations of your suggestion, including the following:
bcp "Database Name.dbo..." (etc.)
In this case, I get a different error. It gives me:
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.
I'm assuming it's having trouble differentiating between the database name "Database Name" and the owner "dbo" (trying to read it as something like "database.name.dbo" or something like that).
So, do I have to rename my databases (so that there are no spaces)? The assumption I got from MSDN is that it shouldn't matter, but I'm beginning to wonder.
Also, what effect, if any, do the underscore characters "_" have?
Thanks again! (You've already helped me a great deal!)
March 20, 2002 at 9:06 am
Right after my last post, I found the following on MSDN:
"To specify a database name that contains a space or quotation mark, you must use the –q option."
Am going to give this a try -- will let you know how I make out!
March 20, 2002 at 9:13 am
Apparently to bcp it does matter as it will not work for me either on a db with a space in the name. However here is a work around.
Set the users default database to the one you are trying to bcp into. Then do
bcp "[Table_Name]" in "filename.txt" -r"~" -m25 -U"sysadminID" -P"sysadminPW"
This works for me.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 20, 2002 at 9:27 am
I *almost* got it to work!
It turns out the -q option does in fact allow the bcp command to recognize spaces in the database and table names. I tried it out, and it worked (i.e. I got the prompts for prefix-length, etc.).
(BTW, there's a typo in my command; it should be -t"~" rather than -r"~")
Now, it doesn't seem to recognize that it should conclude its run at the end-of-file. When I run it, I now get the following:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file
So do I need to use the -L flag to specify last row? (I assume I shouldn't have to, and I'd rather not, since I'm going to have to deal with files with a large, varying number of rows.)
Will probably try setting the default (i.e. "-L0") and see what happens.
Thanks again!
March 20, 2002 at 10:00 am
Dooh, I knew those, just didn't have my cheat sheet in front of me. As for -L that is optional an really is only needed when you want to stop at a specific row.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 20, 2002 at 10:22 am
It's not working. 🙁
Just for grins, I set -L to 2, and am still getting the same EOF error. Could it be that it's not reading the linefeeds correctly?
The file I'm trying to import was generated from an export from another SQL (2000) database, so I don't see why the import would have issues with it.
My guess is that it's having trouble with the linefeed (newline?) characters.
Thoughts?
March 20, 2002 at 10:29 am
Open the file and copy out a handfull of lines to another file the try the smaller file. It may be the linefeeds but there is so much I don't know. Also try deleteing the space for the linefeed and add by hand and try again.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 3, 2002 at 8:53 am
For the information of anyone looking at this thread: it turns out that I was trying to import using the default datafiletype (that is, native). When I used the '-c' flag (making it char file type), the command ran.
Next: am trying the bulk insert command (both using bcp and BULK INSERT through Query Analyzer), and it's still giving me unique key violations, despite the fact that I have MAXERRORS set to ridiculously high numbers (up to the max of 65535, which is even more lines that what's in my import file).
I suspect one of the contributing problems is that the import file itself may have duplicate lines.
Ideas, anyone?
April 3, 2002 at 9:13 am
I just confirmed the problem. The file itself has duplicate lines; therefore, it violates the unique key when I try to import.
I created two test import files. I created the first one using "select *" and created the second one using "select distinct *". Lo and behold, the first file failed, but the second one ran successfully.
The program that generates the import file does not have the ability to distinguish whether or not duplicate lines exist within the file it is generating. I want this process to be automated (in other words, I do NOT want the extra step of having to check the file for duplicate lines).
Does anyone know how I can do this without checking the file for duplicates? Or do I need to filter the file before I try to import? (I'd like to avoid this if possible, but if not . . . )
Thanks in advance!
June 5, 2009 at 3:04 pm
Thanks! Removing the [ and ] did the trick for me. Any reason why [ and ] work in most environments but fail in a few?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply