| Author |
Topic |
|
fredk
Starting Member
8 Posts |
 Posted - 2008-09-13 : 23:39:26
|
| I am trying to import text file data with the bcp utility. I have used the -c -t -p on another machine with SQLServer2000 and it works fine, but here in SQLServer2005 it is asking me for a user name. I supplied the -U but there is no prompt for a user name. I put the user name after the -U but still the same message:User name not provided, either use -U to provide the username or -T for a trust connection.[-m -f -e ... (all the flags listedThe cursor is back at the directory prompt |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-13 : 23:49:52
|
| Here are two examples:Windows authentication (uses the windows account of the current security context), -T is the key here:bcp SomeDatabase.dbo.SomeTable out C:\SomeFile.txt -T -Sserver1\instance1 -c -t, -r\r\nSQL authentication, -U and -P are the key here:bcp SomeDatabase.dbo.SomeTable out C:\SomeFile.txt -Usa -Ppassword -Sserver1\instance1 -c -t, -r\r\nSo you either pass -T or -U/-P.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
fredk
Starting Member
8 Posts |
Posted - 2008-09-14 : 14:11:37
|
| I use windows authentication with the -c -t -p. It works in SQLS2000. In SQL2005, I use the same thing,bcp "fred.dbo.casemain" in "c:\ftp\casemain.txt" -c -t -pThe funny thing is if I dont put any flags, the same 'user name not provided comes. The user name is Administrator but it does not seem to work .. there is no prompt for a user name.Also tried to run the bcp utility from within Management Studio but it gives 'incorrect syntax error .' |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-14 : 21:08:00
|
| You are using lower case t, which is the column delimiter flag. You need to use upper case T, which is the Windows authentication switch. See my post again.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
fredk
Starting Member
8 Posts |
Posted - 2008-09-15 : 12:06:50
|
| Atually I did try the -T flag (with and without the -t), but it keeps coming back with the same message (User name not provided, use the -U or -T). I am using a file that is SDF, no field delimeters. Tried the Bulk insert command in Management studio thinking this would get me past the user name problem, but I get the message,Msg 4866, Level 16, State 1, Line 1The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.Msg 7399, Level 16, State 1, Line 1The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7330, Level 16, State 2, Line 1Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The Bulk insert command I used was,BULK INSERT fredk.dbo.casemain FROM 'c:\fredk\casemain.txt' WITH (datafiletype='char', fieldterminator='',rowterminator='/n') |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
fredk
Starting Member
8 Posts |
Posted - 2008-09-15 : 17:34:59
|
| Sorry, I am trying both, whichever I can get to work. I need to put data into SQL otherwise nothing else will work.I have used the bcp, with the -T, and it keeps saying 'User name not provided'. I tried the bulk insert with my text file being SDF but it says the column is too long.the bcp line works fine in sql2000 but we need to upgrade to 2005. Could it be the permissions? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-15 : 17:52:19
|
| Check if the current security context (the Windows users logged into the computer that is running bcp.exe) has permissions on the SQL Server.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
fredk
Starting Member
8 Posts |
Posted - 2008-09-15 : 21:31:34
|
| What are the fieldterminator and rowterminator for sdf files? The only thing I can see wrong here is a problem with the terminators since it says, 'The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.'I have been using fieldterminator='\t' and rowterminator='\n' |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
fredk
Starting Member
8 Posts |
Posted - 2008-09-16 : 14:05:12
|
| sdf is standard data format e.g.00 NEWARK TIRES NJ 50021 PAID00 LA BELTS CA 12000 NOT PAID00 CHARLOTTE RIDERS NC 14500 PAIDI use a database (e.g. foxpro) and the command.use paydetailscopy to myfile sdf |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-09-16 : 14:06:40
|
| Use a hex editor to determine what characters are in between the fields, at the end of each row, and also the end of file character.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
bcp utility problem