August 30, 2005 at 9:05 am
Hi, I have a few questions regarding the BCP (bulk copy) command for SQL Server 2000.
server name: SW520SQLDEV01
database name: merch
ownername: this should be the owner of the table and not the owner of the database, right?
Whenever I try to run the BCP command from DOS command prompt, it gives me native error 17 - that I don't have a correct username or that it can't find the server.
Any help would be greaty appreciated. It seems like a simple enough task!
Many thanks in advance
August 30, 2005 at 9:53 am
Could you show us the command you're using to invoke the bulk copy? If you have rights to create tables in a database, you should be able to copy data into those tables. Do you know who owns the tables you create? (Do you create them as dbo.tablename?). Do you provide a user name and password when you log in using Query Analyzer? The format file location should not matter as log as the location is specified correctly after the -f and the person invoking the BCP has rights to read it.
August 30, 2005 at 10:12 am
Thanks for the reply. I'm actually using a very simple test file and table to try this out first. So I created a very small table, with 3 fields. The following is the BCP statement I am trying to use:
bcp "merch.roc\zyahya.tester" in "c:\temp\testdata" -f"c:\temp\testformat.fmt" -Uzyahya -Ppass
With regards to who owns the tables, well after I created the table using the T-SQL CREATE TABLE command, the table is listed under user table as
roc\zyahya.tester
For query analyzer, it says I am logging in using my "Windows Authentication". That seems to mean it is the same UserID ans Password that I use for my Windows account.
Finally as I said, the SQL server database resides on its own server machine on a network. The name of the SQL server is the following: SW520SQLDEV01
I am guessing I have to specify the location and/or name of the server to the BCP command somehow.
Finally, I have been trying this in dos comand prompt and in Query Analyzer. Query Analyzer seems to interpret "in" as an SQL statement.
Dos has been giving me native error 17 - SQL Server does not exist or access is denied.
And native error 2 - Connection open
Thanks again
Regards
August 30, 2005 at 10:22 am
If you're logging in with your Windows credentials, you need to leave off the -U and -P and add -T. Give this a try:
bcp "merch.roc\zyahya.tester" in "c:\temp\testdata" -f"c:\temp\testformat.fmt" -T -SSW520SQLDEV01
August 30, 2005 at 11:44 am
Ok it seems like I am actually getting somewhere. It is now giveing me the error, "Unexpected EOF encountered in BCP data-file."
My format file looks like the following:
8.0
3
1 SQLCHAR 0 4 "\0" 1 zaidone SQL_Latin1_General_Cp437_Bin
2 SQLCHAR 0 1 "\0" 2 zaidtwo SQL_Latin1_General_Cp437_Bin
3 SQLCHAR 0 10 "\0" 3 zaidthree SQL_Latin1_General_Cp437_Bin
And my notepad data file looks like the following:
zzzzxvisavisavi
aaaaavisamaster
llllpamericanex
oooosdiscoverjj
I think I'm actually moving in the right direction thanks to you!
Zaid
August 30, 2005 at 12:42 pm
Give this a try:
Remove the first two \0
Change the third one to \n
I'm assuming that the text file doesn't actually contain string delimiters between the fields. This should pick up the first 4 characters and put them into zaidone, the next character and put it into zaidtwo, and the rest of the row and put it into zaidthree
August 30, 2005 at 12:48 pm
Great, that worked!!!
You've been a great help Mr. Webb.
Now, it's time to try with the real master file!
Many thanks again for all your help and time!
August 30, 2005 at 12:55 pm
No problem. If the master file is large, you might want to use the -b switch to force the server to commit after a know quantity of rows. (something like -b1000 will force the server to commit every 1000 rows). Depending on the recovery model you have set on the database, a BCP operation may generate widely varying amounts of log activity.
August 30, 2005 at 1:02 pm
Hi again David,
I am not a "pro" with all the lingo of database administration. Can you please explain to me what you mean by commit?
Thanks!!!
August 30, 2005 at 3:28 pm
Is there a reason it would give me the unexpected EOF error when I try to copy a file of roughly 500,000 records into the table?
When I only take a small sample of the file (50 records) it imports just fine, but when I attempt to import the entire file it tells me it found an unexpected EOF.
The file is coming from another organization and is supposed to be fixed length. Also, would there be a way to test if the file is fixed in length and format all the way through? The file is just so massive!
Thanks
August 30, 2005 at 3:40 pm
'Unexpected EOF' is a pretty generic message. It normally means that there was a formatting problem somewhere along the line and the BCP program got lost and couldn't figure out what to do next.
Add the -e option (-e"error.txt") to print any errors out to a readable text file. You might also want to add the -m option ( like -m10, the number of errors bcp can tolerate before it stops).
If you have access to Book Online you might want to look up 'BCP Utility'. There are lots of other options and interactions you might want to consider.
You can also use the -F option, which tells BCP which input row to start on (-F1 starts on the first row, -F2 the second) and the -L option to tell BCP which row to end on. If your incorrectly formatted data row is in the middle of the file somewhere, you might have to use all these tricks to find it and correct or delete it.
August 31, 2005 at 11:43 am
The following self-documenting VBScript can analyze your data file for wrong-length records. Another way to determine that your file contains WLR's (it won't prove that it DOESN'T, however) is to divide the byte-size of the file (after accounting for the possible presence of the final byte being an "EOF" byte) by the correct record length (+ the length of the record terminator(s)). If you get any remainder, then at least 1 record is a WLR.
Here's the script:
'*****************************************************************************************
' ShowFileInfo.vbs
'
' Examines a text file to determine if all records are fixed length or not. User supplies
' the expected record length then reads/compares every row in file. Records which are not
' correct length are written to and ".info" file. When specifying the record length, do
' not include the line terminator length... juss the data length.
'
' This code assumes input file records are terminated with CRLF (carriage return and line
' feed). Similar logic can be constructed of the line terminator is different.
'
' run this program like this:
'
' cscript ShowFileInfo.vbs /file= /reclen=
'
' substituting your filename for <InputFileName and your expected record length for
'
' An ".info" file will be created (just another text file) by appending ".info" to the
' name of the input file in the same directory as the input file.
'
' Lynn Ransdell 8/31/2005
'
'*****************************************************************************************
Option Explicit
Const FORREADING = 1
Dim oFSO, oFile, oInfo
Dim iExpectedRecLen, iActualRecLen
Dim sInputFile
Dim sInfoFile
Dim iRecNo, iErrNo
Dim sParam, sLine
If Wscript.Arguments.Count < 2 Then
Wscript.Echo "Missing command-line parameters..."
Wscript.Echo "Usage: cscript ShowFileInfo.vbs /file= /reclen="
Wscript.Quit(16)
End If
sInputFile = ""
iExpectedRecLen = 0
sParam = WScript.Arguments.Item(0)
If UCase(Left(sParam,6)) = "/FILE=" Then
sInputFile = RemoveQuotes(Mid(sParam,7))
End If
If UCase(Left(sParam,8)) = "/RECLEN=" Then
iExpectedRecLen = Cint(Mid(sParam,9))
End If
sParam = WScript.Arguments.Item(1)
If UCase(Left(sParam,6)) = "/FILE=" Then
sInputFile = RemoveQuotes(Mid(sParam,7))
End If
If UCase(Left(sParam,8)) = "/RECLEN=" Then
iExpectedRecLen = Cint(Mid(sParam,9))
End If
If sInputFile = "" Then
Wscript.Echo "Input file not specified."
Wscript.Quit(16)
End If
If iExpectedRecLen <= 1 Then
Wscript.Echo "Missing or invalid expected record length."
Wscript.Quit(16)
End If
iRecNo = 0
Set oFSO = CreateObject("Scripting.FileSystemObject")
'-- determine if input file can be found...
If Not oFSO.FileExists(sInputFile) Then
Wscript.Echo "Input file not found."
Set oFso = Nothing
Wscript.Quit(16)
End If
'-- Let's get to work...
Set oFile = oFso.OpenTextFile(sInputFile, FORREADING)
Set oInfo = oFSO.CreateTextFile(sInputFile & ".info")
Do Until oFile.AtEndOfStream
sLine = oFile.ReadLine
iRecNo = iRecNo + 1
iActualRecLen = Len(sLine)
If iActualRecLen iExpectedRecLen Then
iErrNo = iErrNo + 1
oInfo.WriteLine "Rec No " & iRecNo & " Rec Len = " & iActualRecLen & " Rec Text = " & sLine
End If
Loop
Wscript.Echo "Finished: Records processed = " & iRecNo & " with " & iErrNo & " wrong length records."
If iErrNo > 0 Then
Wscript.Echo " ... examine the contents of " & sInputFile & ".info for more details."
End If
oFile.Close
oInfo.Close
Set oFSO = Nothing
Function RemoveQuotes(sFileName)
Dim Quotes, Which
Quotes = "'" & Chr(34)
'-- strip off any enclosing quote characters..
If Left(sFileName,1) = Instr(sFileName, Quotes) > 0 Then
Which = Left(sFileName,1)
sFileName = Mid(sFileName,2)
If Right(sFileName,1) = Which Then
sFileName = Mid(sFileName,1,Len(SrcPath) - 1)
Else
Wscript.Echo "Quoted filename not properly formeds"
Wscript.Quit(16)
End If
End If
RemoveQuotes = sFileName
End Function
August 31, 2005 at 11:50 am
I just noticed that this forum software strips out "bracketed" content... not sure how to avoid that. My syntax examples aren't quite clear without it so here's an "alternate" description using square brackets in lieu of angle brackets (the brackets, of course, aren't actually part if the actual syntax):
cscript ShowFileInfo /file=[InputFileName] /recno=[nnn]
I hope that helps make the description more clear... if not, just ask.
BTW, does anyone know how "angle bracketed" text can be properly specified or should I just use a different notation for future posts?
Thanks, Lynn
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply