BCP Problem

Hi,
I am trying to bulk copy a text file into a table and I get this error
message:
SQLState = 28000, NativeError = 18452
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'Test Login'. Reason: Not associated with a trusted SQL Server connection.
I am pretty sure that my username and password are correct because I just
created them 5 minutes before.
Any clues on how to get around this problem?
JDHi
It would help if you showed the command you where executing, but it seems
that you have specified the -T option possibly instead of -t (the arguements
are case sensitive!). See Books online for more information on the BCP
utility.
John
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:Zi7Xe.253734$E95.165564@.fed1read01...
> Hi,
> I am trying to bulk copy a text file into a table and I get this error
> message:
> SQLState = 28000, NativeError = 18452
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
> user
> 'Test Login'. Reason: Not associated with a trusted SQL Server connection.
> I am pretty sure that my username and password are correct because I just
> created them 5 minutes before.
> Any clues on how to get around this problem?
>
> JD
>
>|||Joe Delphi (delphi561@.nospam.cox.net) writes:
> I am trying to bulk copy a text file into a table and I get this error
> message:
> SQLState = 28000, NativeError = 18452
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
> user'Test Login'. Reason: Not associated with a trusted SQL Server
> connection.
> I am pretty sure that my username and password are correct because I just
> created them 5 minutes before.
It appears that the SQL Server is not configured for SQL authentication,
but only accepts Windows authentication. To examine this right-click
the sercer in Enterprise Manager and select Properties. Then go the
Security tab.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Sorry, here is the command I am using. I am not using the -t or -T option.
bcp "[local].Compensation.tblOCImport" in "PAYOC.txt" -c -o"C:\2005\Raw
Extracts\OutputLog.txt" -U"Test Login" -P"TestPassword"
I changed the Server properties to use both Windows authentication and also
SQL Server authentication and now I get this error message:
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
requested in login '[local]'. Login fails.
Like I said in my original post, I just created my login and password a few
minutes ago so I know they are correct. But I don't understand why I am
getting the error message.
JD
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%238cosSCvFHA.664@.tk2msftngp13.phx.gbl...
> Hi
> It would help if you showed the command you where executing, but it seems
> that you have specified the -T option possibly instead of -t (the
arguements
> are case sensitive!). See Books online for more information on the BCP
> utility.
> John
> "Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
> news:Zi7Xe.253734$E95.165564@.fed1read01...
connection.
just
>|||The qualified table name is specified the format: <database
name>.<schema>.<object>. The server name can be specified with the '-S' BCP
parameter.
It is unclear what your server and database names are. Assuming a database
named 'Compensation' in the 'dbo' schema on the local server:
bcp "Compensation.dbo.tblOCImport" in "PAYOC.txt" -c -o"C:\2005\Raw
Extracts\OutputLog.txt" -U"Test Login" -P"TestPassword" -S"(local)"
The -S parameter can be omitted for the local server. For a remote server:
bcp "Compensation.dbo.tblOCImport" in "PAYOC.txt" -c -o"C:\2005\Raw
Extracts\OutputLog.txt" -U"Test Login" -P"TestPassword" -S"MyServer"
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:ISfXe.253752$E95.85768@.fed1read01...
> Sorry, here is the command I am using. I am not using the -t or -T
> option.
> bcp "[local].Compensation.tblOCImport" in "PAYOC.txt" -c -o"C:\2005\Raw
> Extracts\OutputLog.txt" -U"Test Login" -P"TestPassword"
> I changed the Server properties to use both Windows authentication and
> also
> SQL Server authentication and now I get this error message:
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open
> database
> requested in login '[local]'. Login fails.
> Like I said in my original post, I just created my login and password a
> few
> minutes ago so I know they are correct. But I don't understand why I am
> getting the error message.
> JD
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%238cosSCvFHA.664@.tk2msftngp13.phx.gbl...
> arguements
> connection.
> just
>|||Joe Delphi (delphi561@.nospam.cox.net) writes:
> Sorry, here is the command I am using. I am not using the -t or -T
> option.
> bcp "[local].Compensation.tblOCImport" in "PAYOC.txt" -c -o"C:\2005\Raw
> Extracts\OutputLog.txt" -U"Test Login" -P"TestPassword"
> I changed the Server properties to use both Windows authentication and
> also SQL Server authentication and now I get this error message:
> SQLState = 37000, NativeError = 4060
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open
> database requested in login '[local]'. Login fails.
> Like I said in my original post, I just created my login and password a
> few minutes ago so I know they are correct. But I don't understand why
> I am getting the error message.
Did you also add the Test login as a user in the database? You get this
message if the user does not have access to the database. You also get it,
if the database does not exist at all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

bcp error: Unable to open BCP host data-file

I get this error message:

Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

if I try to run

EXEC master..xp_cmdshell
'bcp TestDB.dbo.Employees out \\Ioana\Export\Employees.txt -c -t"," '

from Query Analyzer (or a store procedure).

If run the same command in cmd or run window (of course without EXEC and xp_cmdshell) I do not get any error and the content of the table is exported in the file I want...

If the exported file is on a local folder on the server everything is working fine even in Query Analyzer...

Thanking in AdvanceMy guess is that the user that runs SQL server (default is LocalSystem) has no permissions on the server called Ioana. If SQL Server is running under LocalSystem, you will have to create a domian (or domain local) account for SQL Server to run under. Then grant that user permission on this share on Ioana. You will also have to add the user to the local administrators group on the SQL Server, too. Hope this helps.|||Originally posted by MCrowley
My guess is that the user that runs SQL server (default is LocalSystem) has no permissions on the server called Ioana. If SQL Server is running under LocalSystem, you will have to create a domian (or domain local) account for SQL Server to run under. Then grant that user permission on this share on Ioana. You will also have to add the user to the local administrators group on the SQL Server, too. Hope this helps.

SQL Server is install on SQLSERVER machine and Ioana is my machine.
I want to run this script on SQLSERVER machine (in Query Analyzer) and to export the file in Export directory on my machine. The Export directory on my machine is shared with all the rights.

If run the bcp from cmd window on SQLSERVER machine is working fine. From Query Analyzer on the same machine (SQLSERVER) is not working even if I am logged in as system administrator with windows authentication. The administrator of SQLSERVER have full permision on my shared directory.

Thanks again

bcp error: Truncation cannot occur for BCP output file

Hi,

I'm trying to export sql table as fixed length text file with format file but I got the following error message:

---

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]
Warning: Server data (61 bytes) exceeds host-file field length (60 bytes) for field (4).
Use prefix length, termination string, or a larger host-file field size.
Truncation cannot occur for BCP output file

---

All fields in the SQL input table have char data type and I use the format file like this:

---

7.0
29
1 SQLCHAR 0 10 "" 1 SEQ
2 SQLCHAR 0 1 "" 2 NPARSED
3 SQLCHAR 0 115 "" 3 COMPANY
4 SQLCHAR 0 60 "" 4 ADDR1
....
27 SQLCHAR 0 1 "" 27 LACS
28 SQLCHAR 0 2 "" 28 DPV
29 SQLCHAR 0 2 "\r\n" 29 ZIP4CODE

---

I've been researched about this error but I couldn't find the clear answer.

The strange thing is that all the records are char() fields, not varchar()

And I checked the max length record for the 4th column(ADDR1) and it was 60, not 61.

However I'm still getting the error.

The output file was exported but some of the records have short length.

Is this some kind of bcp bug?

I used SQL Server 2000 Standard w/ SP4

And the following is the command that I used:

---

declare @.cmd varchar(2000)

SET @.cmd = 'bcp "Input_table" out "D:\AddressUpdate\Tmp\xFixADDR.dat" -fD:\AddressUpdate\Tmp\xFixADDR.fmt -Usa -Psapass -SMyMachine''
print(@.cmd)
EXEC master..xp_cmdshell @.cmd

---

Please let me know if anyone solve the similar problem.

Thanks,

- Hyung -

Hyung,

Are the client and server code pages different. Translation of data from one code page to another results in larger size for the destination data than the original size. This is specially common in east-asian languages like Chinese, Japanese etc.

Also, what server/client versions are you using? The error seems to come from bcp executable which comes with SQL Server 2000, if you have SQL Server 2005 make sure you use the SQL Server 2005 version of bcp executable. You can check that by using bcp /v command, where version should be 09.XX.XXXX

Thanks

Waseem

|||

Thank you for your answer.

I changed the server collation by rebuilding the master database upon your advice and it solved the problem.

The SQL server version that I'm using is a kind of mixed.

I'd installed SQL Server 2000 personal edition (default) on my machine and I also installed SQL Server 2005 Developer edition on the same machine as Named instance.

It made me prevent using SQL 2000 version of Enterprise Manager with error something like "MMC was created by a later version"

And the program was created from SQL project in the Visual Studio 2005 standard edition and one IS package file is used inside the program.

Also one of the "Execute SQL Task" in the pacakge uses bcp command on the SQL Server 2000 machines.

Thank you again,

- Hyung -

bcp error: Truncation cannot occur for BCP output file

Hi,

I'm trying to export sql table as fixed length text file with format file but I got the following error message:

---

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]
Warning: Server data (61 bytes) exceeds host-file field length (60 bytes) for field (4).
Use prefix length, termination string, or a larger host-file field size.
Truncation cannot occur for BCP output file

---

All fields in the SQL input table have char data type and I use the format file like this:

---

7.0
29
1 SQLCHAR 0 10 "" 1 SEQ
2 SQLCHAR 0 1 "" 2 NPARSED
3 SQLCHAR 0 115 "" 3 COMPANY
4 SQLCHAR 0 60 "" 4 ADDR1
....
27 SQLCHAR 0 1 "" 27 LACS
28 SQLCHAR 0 2 "" 28 DPV
29 SQLCHAR 0 2 "\r\n" 29 ZIP4CODE

---

I've been researched about this error but I couldn't find the clear answer.

The strange thing is that all the records are char() fields, not varchar()

And I checked the max length record for the 4th column(ADDR1) and it was 60, not 61.

However I'm still getting the error.

The output file was exported but some of the records have short length.

Is this some kind of bcp bug?

I used SQL Server 2000 Standard w/ SP4

And the following is the command that I used:

---

declare @.cmd varchar(2000)

SET @.cmd = 'bcp "Input_table" out "D:\AddressUpdate\Tmp\xFixADDR.dat" -fD:\AddressUpdate\Tmp\xFixADDR.fmt -Usa -Psapass -SMyMachine''
print(@.cmd)
EXEC master..xp_cmdshell @.cmd

---

Please let me know if anyone solve the similar problem.

Thanks,

- Hyung -

Hyung,

Are the client and server code pages different. Translation of data from one code page to another results in larger size for the destination data than the original size. This is specially common in east-asian languages like Chinese, Japanese etc.

Also, what server/client versions are you using? The error seems to come from bcp executable which comes with SQL Server 2000, if you have SQL Server 2005 make sure you use the SQL Server 2005 version of bcp executable. You can check that by using bcp /v command, where version should be 09.XX.XXXX

Thanks

Waseem

|||

Thank you for your answer.

I changed the server collation by rebuilding the master database upon your advice and it solved the problem.

The SQL server version that I'm using is a kind of mixed.

I'd installed SQL Server 2000 personal edition (default) on my machine and I also installed SQL Server 2005 Developer edition on the same machine as Named instance.

It made me prevent using SQL 2000 version of Enterprise Manager with error something like "MMC was created by a later version"

And the program was created from SQL project in the Visual Studio 2005 standard edition and one IS package file is used inside the program.

Also one of the "Execute SQL Task" in the pacakge uses bcp command on the SQL Server 2000 machines.

Thank you again,

- Hyung -

BCP Error

Hi I need to create a trigger on a table which bcps the table out to a flat fine. I am getting the error "Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file"
Although if I run the bcp command separately I do not get this error and the command runs successfully. I am using the Exec Master..xp_cmdshell command.

Please advice.

Thanks,
PankajError = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file"

this error occurs when you give the output path as a directory which does not exist. i.e. you give the path as C:\enigma\enigma.txt even though the directory enigma does not exist on your PC. Create the directory and reissue the command. The BCP command only creates a data file ... not the directory