Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP Command, Error - Login failed for user Expand / Collapse
Author
Message
Posted Thursday, June 14, 2012 3:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 1:27 PM
Points: 9, 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!!
Post #1316224
Posted Thursday, June 14, 2012 3:57 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:09 PM
Points: 901, Visits: 7,180
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
Post #1316231
Posted Thursday, June 14, 2012 4:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 1:27 PM
Points: 9, Visits: 49
Thanks, that worked!
Post #1316237
Posted Thursday, June 14, 2012 4:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 23, 2012 12:01 PM
Points: 3, Visits: 40
It looks like you are passing a domain\windows_acct to what is supposed to be a sql_login
Post #1316238
Posted Tuesday, April 29, 2014 5:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:36 AM
Points: 5, Visits: 29
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?

Post #1565866
Posted Tuesday, April 29, 2014 6:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 12,910, Visits: 32,021
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1565871
Posted Tuesday, April 29, 2014 6:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 12,910, Visits: 32,021
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1565874
Posted Wednesday, April 30, 2014 12:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 1:36 AM
Points: 5, Visits: 29
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
Post #1566227
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse