November 8, 2004 at 1:24 pm
Hi all,
I am currently having a problem with creating a proper Bulk Insert BCP format file.
Here is all of the info I have done so far:
Database Name: MBLOGS_DEV ---- Table Name: AuditJournal
Fields: (Name, DataType, Length)
1. AuditID - Numeric - 9 (Auto-Number)
2. AccntNo - Numeric - 9
3. EditDate - DateTime - 8
4. EditTime - VarChar - 50
5. User - VC - 50
6. AccntName - VC - 50
7. Field - VC - 50
8. OldValue - VC -50
9. NewValue - VC - 50
10. ClientName - VC - 50
Here is the BCP Format .FMT Textfile I have so far:
8.0
9
1 SQLNUMERIC 0 8 "," 1 AccntNo
2 SQLDATETIME 0 9 "," 2 EditDate
3 SQLCHAR 0 50 "," 3 EditTime
4 SQLCHAR 0 50 "," 4 User
5 SQLCHAR 0 50 "," 5 AccntName
6 SQLCHAR 0 50 "," 6 Field
7 SQLCHAR 0 50 "," 7 OldValue
8 SQLCHAR 0 50 "," 8 NewValue
9 SQLCHAR 0 50 "\r\n" 9 ClientName
Note that AuditID is not in the file. It is an auto-number and is not in the CSV file I am trying to copy into the SQL table.
Here is the code for my ASP page:
<% Option Explicit %>
<%
'Declare connection string
Dim oConn, strConn
'Create connection object and open connection
Set oConn = Server.CreateObject("ADODB.Connection")
strConn = "PROVIDER=sqloledb;DRIVER=SQL Server;SERVER=MYSERVER;DATABASE=MYDATABASE;UID=MYNAME;Password=MYPWORD;"
oConn.Open strConn
'Build bulk insert string
Dim sPath, sFileName, sTableName
sPath = "\\MYSERVER\Inetpub\wwwroot\Intranet\medbill\test\"
sFileName = "MYFILE.CSV"
sTableName = "MYTABLE_DEV"
Dim strSQL
strSQL = "BULK INSERT "
strSQL = strSQL & sTableName
strSQL = strSQL & " FROM '" & sPath & sFileName & "'"
strSQL = strSQL & " WITH ("
strSQL = strSQL & "FIRSTROW = 2, "
strSQL = strSQL & "FORMATFILE = '" & sPath & "Audit_BCP.fmt'"
strSQL = strSQL & ")"
oConn.Execute strSQL
oConn.Close
Set oConn = Nothing
Response.Write "Bulk insert successful!<br><br>"
Response.Write strSQL
%>
When I try to run this, I get the following error message:
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Cannot perform bulk insert. Invalid collation name for source column 1 in format file '\\MYSERVER\Inetpub\wwwroot\Intranet\medbill\test\Audit_BCP.fmt'.
/medbill/test/Audit.asp, line 30
Any help would be cool
Sanctos
November 8, 2004 at 2:52 pm
Try creating a new bcp format file using
bcp MBLOGS_DEV..AuditJournal out c:\fmtAuditJournal.txt -Sservername -Uuid -Ppwd
The new file should have default collation.
Hope it helps!
November 8, 2004 at 6:20 pm
A couple of things:
If AuditID is the first column in your table, your format file is saying that AccntNo is the first column in your table. The sixth column in each format file row should be the server column order, or what number column (SELECT ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AuditJournal') you want to put that file field into. So you have to change that on all rows of your format file.
The last column on each row is the column collation. If you want to use the default, just put two sets of double quotes to denote an empty string:
1 SQLNUMERIC 0 8 "," 2 AccntNo ""
I always bcp/bulk insert into a staging table that has all the columns of type varchar. Then I CAST values when I distribute the data throughout the schema. It is just easier that way.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply