Need Bulk Insert help

Hi all,
I am not sure I post this in the correct forum or not, if not please reguide me to the correct forum. I have data files created from streamwriter using tab for field terminator and line break from streamwriter. Then I try to use Bulk Insert to load the data file into a table with format file using bcp command. Then I received the following error:

Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (codeNum).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

the bulk insert query is as follows:

BULK insert revrpt_staging_2.dbo.outgoing
from '<data file path>\<filename>.data'
WITH (
FIELDTERMINATOR = '\t',
FIRSTROW = 1,
FORMATFILE='<format file path>\format1.fmt',
ROWTERMINATOR = '\r\n',
KEEPIDENTITY,
KEEPNULLS
);

using format file as shown below:
9.0
11
1 SQLINT 1 4 "\t" 2 codeNum ""
2 SQLINT 1 4 "\t" 3 code2Num ""
3 SQLINT 1 4 "\t" 4 code3Num ""
4 SQLINT 1 4 "\t" 5 code4Num ""
5 SQLNCHAR 2 30 "\t" 6 messageId SQL_Latin1_General_CP1_CI_AS
6 SQLNCHAR 2 24 "\t" 7 phone SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 8 0 "\t" 8 message SQL_Latin1_General_CP1_CI_AS
8 SQLDATETIME 1 8 "\t" 9 recDateTime ""
9 SQLDECIMAL 1 19 "\t" 10 chargeAmt ""
10 SQLNCHAR 2 4 "\t" 11 eStatus SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 60 "\r\n" 12 dStatus SQL_Latin1_General_CP1_CI_AS

below is 1 row of the data:
1000 4 12345 0 8EDBDEBF 10111111111 Free msg. Call us at xxxxxxxx, Mon - Fri, 10am-5pm 04/25/2006 17:04:45 0 0 1
Anyone have any idea what is wrong? and how should I go about fixing it? Please help. Thanks in advance.
Daren

I believe you should set prefix length field to 0, as you don't have a prefix:

1 SQLINT 0 4 "\t" 2 codeNum ""

BTW, Books Online "Using Format Files" article is a good read on this matter.

|||Hi Yaroslave,
Sorry to bother you, but could you redirect me to the exact link that explains the meaning of the format files, like explain 1 represents the column number declared, SQLINT represents the type of the column, 0 represent the prefix, etc. for the following example:

1 SQLINT 0 4 "\t" 2 codeNum ""

And one more thing, I changed the prefix to 0 for all the rest and rerun the command, I got an error on the 4th one, which is code4Num the data for it is 12345. Any idea what is wrong with it?
Daren
|||Hi Yaroslave,
the exact error for it is "Bulk load data conversion error (overflow)".
Daren
|||Hi Yaroslave,
sorry to bother you, but it still cannot insert into the table, the following errors pop up, I have no idea what to do with it. The error is as follows:
Msg 4866, Level 16, State 7, Line 1

The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

please help. Thanks in advance.
Daren
|||

It does seem like the bulk insert is having objections about the separators in the file. Are you sure that you have tabs in there?

fwiw, I've always used another strategy regarding formatfiles. (though only with bcp, but I believe that it'd work the same with bulk insert)

The idea is that no matter the datatypes in the destination table, the source is just a plain ascii file, so specify all columns in the format file as SQLCHAR, all prefixes as zero, and the length as the actual charachter length, not the bytelength for the datatype. (ie a datetime becomes 26 instead of 8 etc)

An easy method to create the formatfile, is to use bcp without specifying the -c and -t parameters. You'll then be prompted for the format on each column, 4 prompts on each.

For the first, if the suggestion is anything else than [char], type in 'char' at the prompt, if it is [char], press enter.
For the second, always type 0 (zero)
For the third, press <enter>, always accept the suggested length
For the fourth, type your delimiter. (ie /t)

Repeat for all columns in the table until the last one, where you enter the rowdelimiter at the last (4th) prompt.

Save the file when prompted.

When this work is done, you have a formatfile that you can use, should there be any typos in it (could happen =;o) it's easy to just open it and edit where necessary.

I've used this method since 6.x days, and it has never failed. (ie all CHAR types, all prefixes as zero, suggested lenght, and the appropriate delimeter)

Hope it helps some.

=;o)
/Kenneth

|||Hi Kenneth,
I tried using the method you said, I use the following command:

bcp <tablename> format test.fmt -T

first prompt: char
second prompt: 0
third prompt: <press enter>
fourth prompt: \t for all except last which is \r\n

but the following error came out:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Format file could not be opened. Invalid n
ame specified or access denied.
what do I do about it? Thanks in advance.
Daren
|||Sorry Kenneth, I fixed my previous post with the following command:

bcp <tablename> format nul -T -f test.fmt

I try to bulk insert the data file into the table using this format file, the following error came up for a nvarchar column:

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (<column name>).

Any idea on this? Thanks again for helping me out.
Daren
|||Sorry Kenneth,
My mistake again, I use unicode file instead of ascii that bring up the error I posted previously. I change to use ascii file then it works! Thanks again Kenneth, you saved me a lot of time wondering around.
Daren
|||

Glad it worked out for you. =:o)

/Kenneth

Need advise on Insert trigger

Hi all,

I am trying to create a insert trigger that gets a value from the new record and use that to get additional data from other tables and update the new record

this is howfar i came:

create trigger trUpdateGEOData
on BK_Machine
after insert
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID, BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode, inserted
where BK_Machine.MachineID = Inserted.MachineID and BK_Machine.Loc_Postalcode = GEO_Postcode.postcode and BK_Machine.LOC_Doornumber <= GEO_Postcode.van and
BK_Machine.LOC_Doornumber <= GEO_Postcode.tem

Trigger runs fine but doesn't do a thing probably it can't find the machineid i think,

can someone help me?

Cheers WimmoSo sorry for posting such a stupid mistake from my hazy view today!!

< should be >

Trying to get awake today, sorry for the disturbance

Cheers Wimmo|||Cleaned up your code:create trigger trUpdateGEOData
on BK_Machine
after insert
as
update BK_Machine
set BK_Machine.LOC_Street = GEO_Postcode.STraatID,
BK_Machine.Loc_City = GEO_Postcode.PlaatsID
from BK_Machine
inner join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode
and BK_Machine.LOC_Doornumber <= GEO_Postcode.van
and BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
inner join inserted on BK_Machine.MachineID = Inserted.MachineID
Now, it strikes me that the inner join on GEO_Postcode could return no records, in which case no BK_Machine records would be updated. It could also potentially return more than one record, in which case you would get unpredictable results for your update.|||Thanx Blindman,

What do you mean with the inner join: is it when there are more records inserted at the same time with the same machineid?

Wimmo

Need advice for Parent Child simultaneusly insert by many user

Need advice for Parent Child insert in transaction mode
I have Parent child table as decribe below:
Parent Table name = TESTPARENT
1. counter bigint isIdentity=Yes Increment=1 Seed=1
2. customer nChar(10)
Child Table name = TESTCHILD
1. counter bigint
2. qty numeric(3,0)
I want to insert new record with code below:
void button1_Click(object sender, EventArgs e)
{
// Define object to catch @.@.indentity
object myCounter;
// Connect to database & open
myConnection = new SqlConnection("Data Source=SQL2005;Initial
Catalog=XXX;User ID=sa; Password=YYY");
myConnection.Open();
// define transaction
SqlTransaction myAtom = myConnection.BeginTransaction();
SqlCommand myAtomCmd = myConnection.CreateCommand();
myAtomCmd.Transaction = myAtom;
// Start insert to database with transaction mode
try
{
// Insert parent new record
myAtomCmd.CommandText = string.Format("insert into TESTPARENT (customer)
values ('{0}')", tbCustomer.Text);
myAtomCmd.ExecuteNonQuery();
// Get Indentity
myAtomCmd.CommandText = "SELECT @.@.identity from testParent";
myCounter = myAtomCmd.ExecuteScalar();
// insert child new record
myAtomCmd.CommandText = string.Format("insert into TESTCHILD (counter,
qty) values ('{0}', {1})", Convert.ToInt64(myCounter.ToString()),
tbQty.Value);
myAtomCmd.ExecuteNonQuery();
// Commit transaction
myAtom.Commit();
}
catch
{
myAtom.Rollback();
MessageBox.Show("Data not inserted");
}
}
I already try with 2 workstation and 1 server, that code working well
(not duplicate in parent and insert right relation child parent record
in child table ).
I am not sure that code will stay stable when the table inserted
simultaneusly by many user.
Please advice, that code is the right way.
*** Sent via Developersdex http://www.examnotes.net ***The DBA side of me is cringing, becuae you're not using stored
procedures to insert data into a database; you really should use a
stored procedure when running against SQL Server. I realize that there
may be reasons to not use a stored procedure, but thos should be the
exception, and not the rule. Oh, and if you're going to use IDENTITY
columns, you should probably be using SCOPE_IDENTITY, and not
@.@.IDENTITY to return the last value inserted.
The developer side of me (recognizing that this might be one of those
rare times when a stored procedure is not appropriate) is cringing
because you are not using parameters to issue commands to your
database, thereby opening yourself up to SQL injection. You are also
connecting to your database as "sa", which means that if someone
figures out your connection string, they own your server.
Try replacing the value of tbCustomer.Text with "1'); DROP TABLE
TestParent--" and see what happens.
You need to make the following changes:
1. Don't connect to a database using sa; create a user with the
appropriate permissions.
2. Use a stored procedure for all CRUD operations (if you don't know
what CRUD stands for, Google it; it'll help you understand a lot more
that this single post will explain).
3. If you can't use a stored procedure (vendor issue, server doesn't
support stored procedures, etc), then use parameters on your client
side; don't build strings.
HTH,
Stu

Need a good insert statement

Hi, I need some help. I've spent nearly a w of work and lunch time trying
to crack this nut, and I guess I am finally stuck.
I have a table with financial data like invoices. The tables are huge.
Hundreds of millions of rows per year.
I want to store some additional information related to gambling in one of
these tables.
I want to put this new data way off in the corner of the table temporarily
until I am able to come back to work.
For reasons that are private, I do not want to create a new table.
So if I could stick this information out starting at say row 104,000 and
column 333,000 that would be great. No one's going to look there. And if
they do it will look like some kind of mistake. I can't figure out how to
get the data there.
Any good Ideas would naturally be of great help. Also, since I am in sort of
a hurry (tax day is april 15) I would really appreciate it if I could just
get some of those "only the quality answers." I know a lot of people like to
yik yak, but I just need da facts. Thanks a lot!Hi
I'm not sure I understand you. Do you want to add additional column? If you
do , use ALTER TABLE TableName ADD Column.....
If you want to do paging I'd recomend you vist Aaron's web site
www.aspfaq.com .
"TargetBleigh" <ddsd*f.com> wrote in message
news:2c6dnRzmUNP2ec3fRVn-1g@.comcast.com...
> Hi, I need some help. I've spent nearly a w of work and lunch time
trying
> to crack this nut, and I guess I am finally stuck.
> I have a table with financial data like invoices. The tables are huge.
> Hundreds of millions of rows per year.
> I want to store some additional information related to gambling in one of
> these tables.
> I want to put this new data way off in the corner of the table temporarily
> until I am able to come back to work.
> For reasons that are private, I do not want to create a new table.
> So if I could stick this information out starting at say row 104,000 and
> column 333,000 that would be great. No one's going to look there. And if
> they do it will look like some kind of mistake. I can't figure out how to
> get the data there.
> Any good Ideas would naturally be of great help. Also, since I am in sort
of
> a hurry (tax day is april 15) I would really appreciate it if I could just
> get some of those "only the quality answers." I know a lot of people like
to
> yik yak, but I just need da facts. Thanks a lot!
>

Need 2005 Wizard to create INSERT and UPDATE stored procedures.

In SQL Server 2000, the database wizard would create insert and update stored procedures for all of my tables.

I have a large SQL Server 2005 database with many tables and columns. I need wizard support to create insert and update stored procedures for them. Typing them all in manually is out of the question.

The 2005 Generate SQL Server Scripts Wizard does not create stored procuedure scripts. It only scripts tables.

Is there some other way to make Management Studio create stored procedure scripts the way Enterprise Manager did?

Are there any plans to restore this critical functionality that was left out of SQL Server 2005 and the service pack?

Do you know of any utilities that can fill this gap?

Any help would be appreciated.

Please file a suggestion for this functionality on http://connect.microsoft.com/sqlserver. We use customer feedback to prioritize future work. Suggestions and defect reports filed on the Connect site are placed directly into our internal issue tracking system so the appropriate team will see your request.

I don't think there is an easy way to do what you want using built-in functionality in SSMS. If you are comfortable writing .NET code, you could write a small Visual Basic or C# application using the SQL Management Objects to iterate through all your tables and generate the stored procedure code you need.

Thanks,
Steve

Need "refresh connection" or something ?

HI,

I have a problem with the data that I want to delete and insert new one on my SQL.
It will work if I just delete a row. And work well if I just insert a row.
But it will not work if I delete and insert at once on one procedure.

Here's the code :

Protected Sub RolesRadioButtonList_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles RolesRadioButtonList.SelectedIndexChanged
Session("CurrentRoleId") = Me.RolesRadioButtonList.SelectedValue
' Call Sub RemoveUsersInRoles
RemoveUsersInRoles()
' Call Sub AddNewUsersInRoles
AddNewUsersInRoles()
End Sub

Sub RemoveUsersInRoles()
' Create SQL database connection
Dim sqlConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.Text
' Delete that row with correct UserId
cmd.CommandText = "DELETE aspnet_UsersInRoles WHERE (UserId = '" & Session("CurrentUserId") & "')"
cmd.Connection = sqlConn
sqlConn.Open()
cmd.ExecuteNonQuery()
' Close SQL connecton
sqlConn.Close()
End Sub

Sub AddNewUsersInRoles()
' Create SQL database connection
Dim sqlConn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
sqlConn.Open()
' Create new row with new UserId
Dim sqlString As String = "INSERT INTO aspnet_UsersInRoles (UserId, RoleId) VALUES( '" & Session("CurrentUserId") & "','" & Session("CurrentRoleId") & "')"
Dim sqlComm As New SqlCommand(sqlString, sqlConn)
Dim sqlExec As Integer = sqlComm.ExecuteNonQuery
' Close SQL connection
sqlConn.Close()
End Sub

--------------

I can delete the row if I call 'RemoveUsersInRoles'
And I can insert new one if I call 'AddNewUsersInRoles'

But if 'RolesRadioButtonList_SelectedIndexChanged' is called, it will not work well.
If the row not exist, it will insert new row. And that what I want.
But If the row is exist, It wont delete that row and wont insert the new one. That's the problem.

Do I need some 'pause' or 'refresh connection' here ?

Thank You.

Why don't you create one procedure that checks if the row exists, then delete and insert if it does (or just amends/updates it), otherwise it just performs an insert?

|||

uh, Mike, thanks for your respon.

My mistake. I put this at my page load. It always reset the variable when the page is reload.

If Len(Session("CurrentRoleId")) Then
Me.RolesRadioButtonList.SelectedValue = Session("CurrentRoleId")
End If

And Mike, thanks for the advise : use update. I already use it.

And thank you all at that 2nd class, keep programming ...