BCP SQL Server 6.5

i need to get some data out from a SQL Server 6.5

i tried using the BCP

bcp iac.dbo.sf_profil out c:\test.bcp /c /Slocal /Usa /Pabc123

bcp iac.dbo.sf_profil out c:\test.bcp /c /Svots138svr09 /Usa /Pabc123

bcp iac.dbo.sf_profil out c:\test.bcp /c /S(local) /Usa /Pabc123

but still was unable to run

the error message
DB-library error
unable to connect : SQL Server is unavailable does not existAre all three command lines failing? The first one seems to be referencing a server named "local" instead of the (local) server. Also, when in doubt, explicitly use the name of the local server.

Not to be completely anal-retentive but are the user and password valid and capable of accessing the database and objects?|||i need to get some data out from a SQL Server 6.5

i tried using the BCP

bcp iac.dbo.sf_profil out c:\test.bcp /c /Slocal /Usa /Pabc123

bcp iac.dbo.sf_profil out c:\test.bcp /c /Svots138svr09 /Usa /Pabc123

bcp iac.dbo.sf_profil out c:\test.bcp /c /S(local) /Usa /Pabc123

but still was unable to run

the error message
DB-library error
unable to connect : SQL Server is unavailable does not exist
In sql server 2000 ,
With default format,you could run
exec master..xp_cmdshell 'bcp test.dbo.jojo out c:\test.txt -c -T -Sservername -Usa -Psecrectcode'

Joydeep

BCP problem

Exec Master..xp_CmdShell 'bcp "exec mydb..SP_test ''200603''" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'

when i run this code in java , the file is created , but data not pump in , anyone know why is it so ?

it can run in query analyzer and no problemsorry, is my mistake , it doesn't give any error :( , just that i put in wrong parameter so no data|||sorry, is my mistake , it doesn't give any error :( , just that i put in wrong parameter so no data out

bcp problem

Can any one why I am facing the following error..
bcp pubs..authors out 'c:\Testing.xls' -c -S{local} -Usa -PDbPass
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
Thanks
bcp pubs..authors out 'c:\Testing.xls' -c -Usa -PDbPass
Without the -S, it will try the default instance on the local host. It is
generally best to give a server name and instance to ensure you don't
connect to the wrong server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rogers" <naissani@.hotmail.com> wrote in message
news:u4Xd4U%234FHA.2616@.TK2MSFTNGP12.phx.gbl...
> Can any one why I am facing the following error..
> bcp pubs..authors out 'c:\Testing.xls' -c -S{local} -Usa -PDbPass
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> Thanks
>
|||I used the following query but it gave me an error..
bcp pubs..authors out 'c:\Testing.xls' -c -Usa -PDbPass
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
Thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:etsm8z%234FHA.2040@.TK2MSFTNGP14.phx.gbl...
> bcp pubs..authors out 'c:\Testing.xls' -c -Usa -PDbPass
> Without the -S, it will try the default instance on the local host. It is
> generally best to give a server name and instance to ensure you don't
> connect to the wrong server.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:u4Xd4U%234FHA.2616@.TK2MSFTNGP12.phx.gbl...
>

bcp copying,but no file

DECLARE @.query NVARCHAR(2000)
SELECT @.query = 'bcp "SELECT * from dbname..tablename" queryout c:\test.txt -c -Slocalhost -Usa -Ppassword'
EXEC master.dbo.xp_cmdshell @.query

I have tried doing this with a test table and this is the output
NULL
Starting copy...
NULL
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 16
NULL

and I did have 3 row,but file is still not created, I have put the server name,login,pasword inside "" and without it,no error,no file.

Could somebody please help meWhere are you looking for the file?

bcp command

I am using the bcp command to export a bulk text file into the database,

bcp elearning.dbo.BulkData in mobile.txt -c -t, -SZOHL-02 -Usa -P1234567890 -E

I have 6 fields in the table to which i am exporting data.
One field is numeric and i have to set the identity to yes,
It gives me an error string data trucncated. When i remove the identity field, i am able to export data.So, how do i tackle this prob?
I used the -E attribute to keep the identity .But still i get the error. The text file has comma seperated fields.
I am using sql server 2000

You have to import your data to a temp table or an intermediate table before moving the data to the table with IDENTITY because a text file will appear to have NULL primary key and IDENTITY which is a primary key cannot be NULL. And no it is not a bug but just the nature of a text file compared to tabular data in a database. Hope this helps.

Bcp

Hi Folks,

I am tring to import data using bcp command.

bcp LNTtest..Connections in Connections.txt -c -r \n -SQaserver -Usa -P

However I am getting the following error messages for three tables in my db:

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

0 rows copied.

Please note that the design for the source and target tables is the same. Here is the table design for one of the tables:

CONNECTIONID varchar 8
DESCRIPTION varchar 50
RASENTRYNAME varchar 50
NETUSEDRIVE char 10
CONNECTIONTYPE int 4
STATUS char
RAS_USERID varchar 16
RAS_PASSWORD varchar 16

Any help would be appriciated.
Thanks,
-Meghna.Can you attach the file you are trying to import or a sample of the records ?|||Originally posted by rnealejr
Can you attach the file you are trying to import or a sample of the records ?

Here is the sample record for table connections:

Session4 Wan Connection 4 N/A 3 A
Session3 Wan Connection 3 N/A 3 A
Session2 Wan Connection 2 N/A 3 A
Session1 Wan Connection 1 N/A 3 B
Session5 Wan Connection 5 N/A 3 A
Session7 Wan Connection 7 N/A 3 A
Session6 Wan Connection 6 N/A 3 A
Session8 Wan Connection 8 N/A 3 A|||Which field is N/A being applied to ?|||What are the potential values for the first field "SessionX" ? Also, just take the first line from the file and use that line to test.|||Originally posted by rnealejr
Which field is N/A being applied to ?

N/A applies to rasEntryName. I also tried with only one row , I get the following:

Network packet size (bytes): 4096
Clock Time (ms.): total 15

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file

When tried with two rows, I get the following:

Network packet size (bytes): 4096
Clock Time (ms.): total 1

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file|||What is your column seperator in your data file? Since you did not specify one, "-t", it needs to be a tab "\t".

Based on your example data, could you take one row and anotate the associated attribute? If you need to skip an attribute, you either need to provide a place holder in your data file OR use a format file, IMHO the second option works better.

The "Unexpected EOF encountered in BCP data-file" error is probably du to the last row NOT having a "\r" at the end, open the file and do a "CTRL+END", are you at the end of a line or an empty line?

Post back your thoughts and one of use will help.