SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP Command, Error - Login failed for user


BCP Command, Error - Login failed for user

Author
Message
Learner093
Learner093
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 49
I am using this BCP command to output the data from a table into the file, and getting this error message:

SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'domain\username'.

Here's the command I have:

bcp "SELECT * FROM database.dbo.TableName" queryout \\sharedlocation\Test\File.DAT -S"SERVERNAME" -U"domain\username" -P"password" -o"\\sharedlocation\Test\Log.txt"

I do not want to have -T (trusted connection) rather want to have SQL authentication. Am I missing anything in the command? Please help!!
David Webb-CDS
David Webb-CDS
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7292 Visits: 8587
How is the ID set up in SQL Server? Is it set up to be a windows authenticated id? If so, putting the network name and the password on the command line isn't going to work. You'll either have to use a trusted connection or set up a separate ID with SQL Server Authentication.



And then again, I might be wrong ...
David Webb
Learner093
Learner093
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 49
Thanks, that worked!
axpetursson
axpetursson
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 40
It looks like you are passing a domain\windows_acct to what is supposed to be a sql_login
nevarda
nevarda
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 39
Hi,
I use this BCP command in a SP and get the same user login error.
I use widows Auth when connecting but have tried SQL server auth as well with the same error.

SP Part is as follows:
...
begin
set @cmd = 'bcp.exe ' +
@dbName + '..' + @tbName + ' in ' +
@filePath + ' -c -U ' + @usr +
' -P ' + @pwd + ' -t ' + @sep
print @cmd --+ '...'
exec xp_cmdShell @cmd
end
and my call to this would be...
Exec uSp_Import_Table 'SampleDB',
'INV_StageTable',
'c:\Test\Aut_2014-04-22.csv',
',',
'MyUserName',
'MyPassword'
and still get the same error.
any ideas?
Lowell
Lowell
SSC Guru
SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)

Group: General Forum Members
Points: 139207 Visits: 41521
you cannot pass a windows username and password. it's just not allowed.

you can pass a SQL username and password, or use the -T for trusted connection, which uses the windows account the SQL instance starts with in services... which may or may not have access to the path for the file in question.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Lowell
Lowell
SSC Guru
SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)SSC Guru (139K reputation)

Group: General Forum Members
Points: 139207 Visits: 41521
you'd probably be much better off using BULK INSERT over bcp + xp_cmdshell anyway:

--in via bulk insert
BULK INSERT INV_StageTable FROM 'c:\Test\Aut_2014-04-22.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 1
)



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
nevarda
nevarda
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 39
Hi,

Thanks Lowell, I'll give that approach a go.
I think i did try it but it was forever complaining about a type mismatch or invalid character.
i think its because the file is originally in Excel Format and saved as a .CSV before dropped into a server directory where a SP finds it
and in that process the DateTime gets saved as 123456.654321 value but i'll have another look.

Thanks a mil.
if i dont come right ill get a shout

Nevarda
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search