error 18456 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

error 18456

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-03-05 : 07:45:49

Rupesh writes "Hi,

I am using the follwing command to copy data from SQL SERVER(Pubs Database) to Text file but I am getting the error shown below.

I am connected as user name ca\rupepatel(Window Nt Account) and has SYSADMIN right and also a member of LocaL Admin Account. The server is using Mixmode aurthentification. This Account has access to Pub Database.

My sqlAgent running under the sql account sa

Script that I am using for copy data from SQL SERVER to Text File

-- This Script created to Query IN/OUT the Data from/To SQL SERVER
--In this Store Procedure specity the value of @tablename = Name of Table from/To which you want to query IN/OUT the Data
--In this Store Procedure specity the value of @Server_name = Name of Server (Name of Seible Server)
--In this Store Procedure specity the value of IN/OUT
--In this Store Procedure specity the value @username = name of user
--In this Store Procedure specity the value @password = password of the user

declare @filename varchar(150)
declare @errorfilename varchar(150)
declare @bcpcommand varchar(2000)

set @filename = replace ('c:\sieble_' + convert (char(8), Getdate(),1) + '_authors' + '.txt','/','-')

set @errorfilename = replace ('c:\Siebel_' + convert (char(8), Getdate(),1) + '_authors' + '.err','/','-')

set @bcpcommand = 'bcp pubs..authors out'

set @bcpcommand = @bcpcommand + ' ' + @filename + ' ' + '-U ca\rupepatel -P password -c -S SERVERNAME' + ' '+ '-e ' + ' ' + @errorfilename

PRINT @bcpcommand

Exec master..xp_cmdshell @bcpcommand

ERROR that I Getting:

SQLState = 28000, NativeError = 18456
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ca\rupepatel'."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-03-05 : 14:45:21

You are trying to use bcp with windows authentication (trusted connection). When you do it this way, you can not use the following switches: -U and -P. So instead of those two switches, use -T. This tells bcp to look at who is currently logged into the windows machine. Here is what your command should now look like:

set @bcpcommand = @bcpcommand + ' ' + @filename + ' ' + '-T -c -S SERVERNAME' + ' '+ '-e ' + ' ' + @errorfilename

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources