build sql script: create

Showing posts with label create. Show all posts

Sunday, March 25, 2012

Bulk insert, skip rows with duplicate key error?

Does sql server have a way to handle errors in a sproc which would allow
one to insert rows, ignoring rows which would create a duplicate key
violation? I know if one loops one can handle the error on a row by row
basis. But is there a way to skip the loop and do it as a bulk insert?
It's easy to do in Access, but I'm curious to know if SQL Server proper
can handle like this. I am guessing that a looping operation would be
slower to execute?nano (nano@.nano.ono) writes:

Quote:

Originally Posted by

I'm a little uncertain what you are talking about. In SQL Server "bulk
insert" is a special operation where you load many rows direct from a
file. Or are you still talking about regular SQL statements?

In the latter case, use

INSERT tbl
SELECT ...
FROM src
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE tbl.keycol = src.keycol)

which should be the normal way to do it in Access - or any other SQL engine
for that matter - as well.

If you are specifically talking bulk load from file, then above is still
possible in SQL 2005 if you use OPENROWSET(BULK) as the table source. If
you use BULK INSERT or BCP (the only options on SQL 2000), I believe it's
possible by using the IGNORE_DUP_KEY option on the index, but a more
common procedure is to load the file to staging table and move on from
there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland. I meant a regular sql operation. I will take a look at
your suggestion, it looks good. Access has another way of handling this
(non-sql) and while the syntax you suggest probably works in Access,
I've never tried it.

Thursday, March 22, 2012

BULK INSERT to a Temporary Table

I wanto to execute a BULK INSERT command into a Temporary Table:
CREATE TABLE #textfile (line varchar(8000))
-- Read the text file into the temp table
BULK INSERT #textfile FROM 'c\Projects.txt'
But when I execute this command a recieve the following error message:
"The current user is not the database or object owner of table
'#textfile'. Cannot perform SET operation."
I´m logged in a Microsoft SQL Server 2000, with a login account that
belogs to the role db_owner of the database I´m working, but does not
belong to the role db_owner of tempdb database. Is it necessary?
What am I doing wrong?
Thanks,
Paulo
*** Sent via Developersdex http://www.developersdex.com ***Hi
You may wantr to check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;302621 regarding
permissions needed.
John
"Paulo Andre Ortega Ribeiro" wrote:
> I wanto to execute a BULK INSERT command into a Temporary Table:
> CREATE TABLE #textfile (line varchar(8000))
> -- Read the text file into the temp table
> BULK INSERT #textfile FROM 'c\Projects.txt'
> But when I execute this command a recieve the following error message:
> "The current user is not the database or object owner of table
> '#textfile'. Cannot perform SET operation."
> I4m logged in a Microsoft SQL Server 2000, with a login account that
> belogs to the role db_owner of the database I4m working, but does not
> belong to the role db_owner of tempdb database. Is it necessary?
> What am I doing wrong?
> Thanks,
> Paulo
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

BULK INSERT SYNTAX

I am setting up a new database using a shopping cart SW, when I create the DB
using there script, I get the following error

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '('.

The line syntax is as follows.

BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:\Inetpub\wwwroot\test\SQL-Admin\Look-Weight.csv';

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ','

)

What is wrong?

hi,

ShaneShowers wrote:

BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:\Inetpub\wwwroot\test\SQL-Admin\Look-Weight.csv';

you have the semicolon (;) in the wrong place... at the end of the first line after the file to be imported.. the semicolon indicates a statement termination so that the next statement just becomes

WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
)

which makes no sense.... remove it and place it at the end of the statement

BULK INSERT testDB.dbo.[Look-Weight] FROM 'C:\Inetpub\wwwroot\test\SQL-Admin\Look-Weight.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ','
);

regards

Tuesday, March 20, 2012

BULK INSERT permissions for non-admin?

I've got a user, "joe", logging into a SqlServer 2005 database using
sqlserver authentication. Joe can create tables, insert, select, drop --
whatever -- but when he tries to do a bulk insert, he gets:
"The current user is not the database or object owner of table 'myTable'.
Cannot perform SET operation."
WARNING: I don't know much about SQL Server permissions administration. I
write queries. :)
When I look at the database in SQL Server Management Studio, I see that the
table is in the schema "dbo". What's the safest way to give Joe permission
to do a bulk insert into this table?
Thanks!"Jesse" <nospam@.nospam.com> wrote in message
news:OI6lteXZGHA.3848@.TK2MSFTNGP05.phx.gbl...
> I've got a user, "joe", logging into a SqlServer 2005 database using
> sqlserver authentication. Joe can create tables, insert, select, drop --
> whatever -- but when he tries to do a bulk insert, he gets:
> "The current user is not the database or object owner of table 'myTable'.
> Cannot perform SET operation."
> WARNING: I don't know much about SQL Server permissions administration. I
> write queries. :)
> When I look at the database in SQL Server Management Studio, I see that
> the table is in the schema "dbo". What's the safest way to give Joe
> permission to do a bulk insert into this table?
>
The user needs INSERT and ADMINISTER BULK OPERATIONS permissions and perhaps
ALTER TABLE. In addition if the user is connected using SQL Server
authentication then the SQL Server account must be able to read the file.
If the user is authenticated by Windows Integrated Authentication, then the
user, not the SQL Server account, needs to be able to read the file.
It's all in the BOL:
BULK INSERT (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188365(SQL.90).aspx
Security Considerations for Using Transact-SQL to Bulk Import Data
http://msdn2.microsoft.com/en-us/library/ms186286.aspx
David|||David --
Thanks for the quick reply! Unfortunately, I must still be missing
something -- I've gone through and granted everything I can think of, but I
still get the same error.
Specifically, here's what I did:
grant alter to [joe]
grant ADMINISTER BULK OPERATIONS to [joe]
grant insert to [joe]
Then, from Server Management Studio,
add the bulkadmin role to joe
add the sy

min role to joe
add the db_ddladmin role to joe
add the db_owner role to joe
Joe now has the power to destroy the whole database -- but not to bulk
insert. I still get, "The current user is not the database or object owner
of table 'myTable'. Cannot perform SET operation."
What else could I be missing?
Thanks again for your help!
Jesse
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OusnToXZGHA.428@.TK2MSFTNGP02.phx.gbl...
> "Jesse" <nospam@.nospam.com> wrote in message
> news:OI6lteXZGHA.3848@.TK2MSFTNGP05.phx.gbl...
>
> The user needs INSERT and ADMINISTER BULK OPERATIONS permissions and
> perhaps ALTER TABLE. In addition if the user is connected using SQL
> Server authentication then the SQL Server account must be able to read the
> file. If the user is authenticated by Windows Integrated Authentication,
> then the user, not the SQL Server account, needs to be able to read the
> file.
> It's all in the BOL:
>
> BULK INSERT (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms188365(SQL.90).aspx
>
> Security Considerations for Using Transact-SQL to Bulk Import Data
> http://msdn2.microsoft.com/en-us/library/ms186286.aspx
>
>
> David
>
>|||One more detail:
- if I log into the database using Server Management Studio, I can run the
bulk insert OK.
- if I try to run it from an ASP.NET script, I get the permissions error.
The asp.net script is connecting to the database with the same credentials I
use to log in via SMS! Something must be working differently, but what?
This is driving me nuts. Thanks for any help you can give!
Here's how my asp.net script connects:
<add name="production" connectionString="data source=myLocalMachine; initial
catalog=myCatalog; user=joe; password=joe;"
providerName="System.Data.SqlClient"/>
"Jesse" <nospam@.nospam.com> wrote in message
news:e8jp30XZGHA.4884@.TK2MSFTNGP02.phx.gbl...
> David --
> Thanks for the quick reply! Unfortunately, I must still be missing
> something -- I've gone through and granted everything I can think of, but
> I still get the same error.
> Specifically, here's what I did:
> grant alter to [joe]
> grant ADMINISTER BULK OPERATIONS to [joe]
> grant insert to [joe]
> Then, from Server Management Studio,
> add the bulkadmin role to joe
> add the sy

min role to joe
> add the db_ddladmin role to joe
> add the db_owner role to joe
> Joe now has the power to destroy the whole database -- but not to bulk
> insert. I still get, "The current user is not the database or object owner
> of table 'myTable'. Cannot perform SET operation."
> What else could I be missing?
> Thanks again for your help!
>
> Jesse|||"Jesse" <nospam@.nospam.com> wrote in message
news:eiNZPSYZGHA.4884@.TK2MSFTNGP02.phx.gbl...
> One more detail:
> - if I log into the database using Server Management Studio, I can run the
> bulk insert OK.
> - if I try to run it from an ASP.NET script, I get the permissions error.
> The asp.net script is connecting to the database with the same credentials
> I use to log in via SMS! Something must be working differently, but what?
> This is driving me nuts. Thanks for any help you can give!
>
> Here's how my asp.net script connects:
> <add name="production" connectionString="data source=myLocalMachine;
> initial catalog=myCatalog; user=joe; password=joe;"
> providerName="System.Data.SqlClient"/>
>
Strange.
This works for me.
Run this as a sy

min. I have a database called test and xp_cmdshell
enabled.
You might run Profiler to see what's actually being sent from the web site.
David
--setup.sql--
use test
master..xp_cmdshell 'dir > c:\data\foo.txt'
master..xp_cmdshell 'type c:\data\foo.txt'
create table LoadTable(data varchar(4000))
bulk insert LoadTable from 'c:\data\foo.txt'
select * from LoadTable
truncate table LoadTable
go
use master
create login Joe with password='Joe'
grant administer bulk operations to Joe
go
use test
create user Joe for login Joe
grant view definition on schema::dbo to Joe
grant select,insert,delete on LoadTable to Joe
--end setup.sql--
then run this .NET program
--Program.cs--
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Data.SqlClient;
namespace csTest
{
public static class Program
{
public static void Main()
{
SqlConnectionStringBuilder cb1 = new SqlConnectionStringBuilder();
cb1.DataSource = "192.168.2.10";
cb1.IntegratedSecurity = false;
cb1.UserID = "Joe";
cb1.Password = "Joe";
cb1.InitialCatalog = "Test";
using (SqlConnection con = new SqlConnection(cb1.ConnectionString))
{
con.Open();
con.FireInfoMessageEventOnUserErrors = true;
con.InfoMessage += new SqlInfoMessageEventHandler(con_InfoMessa
ge);
new SqlCommand(@."delete from LoadTable", con).ExecuteNonQuery();
new SqlCommand(@."bulk insert LoadTable from
'c:\data\foo.txt'",con).ExecuteNonQuery();
using (SqlDataReader rdr = new SqlCommand("select * from
LoadTable",con).ExecuteReader())
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
Console.ReadLine();
}
static void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
Console.WriteLine(e.Message);
}
}
}
--end Program.cs---

Sunday, March 11, 2012

BULK INSERT HELP!

Hello,
This is my table
CREATE TABLE [dbo].[TDE018](
[no_empl] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
[no_dexp] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
[no_doss_ir] [int] NOT NULL,
[no_even_ir] [smallint] NOT NULL,
[dat_even_orig_ir] [datetime] NULL,
[dat_even_ir] [datetime] NULL,
[cod_loi_repa] [varchar](2) COLLATE SQL_Latin1_General_CP850_CI_AI NULL,
[dat_inscr_decis_r] [datetime] NULL,
[cod_decis_admis_r] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
NULL,
[cod_motif_deci_adm] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
NULL,
[dat_presc_drt] [datetime] NULL,
[dat_cess_1er_emplo] [datetime] NULL,
[dat_consol] [datetime] NULL,
[dat_rtr_1er_emploi] [datetime] NULL,
[text_desc_diagn] [varchar](80) COLLATE SQL_Latin1_General_CP850_CI_AI
NULL,
[taux_pe_enga_ap] [float] NULL,
[dat_enrg_oper] [datetime] NULL
) ON [PRIMARY]
This is the command that I use
BULK INSERT TDE018 FROM 'C:\TDE018.csv' WITH (FORMATFILE = 'C:\TDE018.fmt',
KEEPNULLS, CODEPAGE ='1252')
This is my format file
8.0
17
1 SQLCHAR 2 12 ";" 1
no_empl SQL_Latin1_General_CP850_CI_AI
2 SQLCHAR 2 12 ";" 2
no_dexp SQL_Latin1_General_CP850_CI_AI
3 SQLINT 0 9 ";"
3 no_doss_ir ""
4 SQLSMALLINT 0 4 ";" 4
no_even_ir ""
5 SQLDATETIME 1 10 ";" 5
dat_even_orig_ir ""
6 SQLDATETIME 1 10 ";" 6
dat_even_ir ""
7 SQLCHAR 2 2 ";" 7
cod_loi_repa SQL_Latin1_General_CP850_CI_AI
8 SQLDATETIME 1 10 ";" 8
dat_inscr_decis_r ""
9 SQLCHAR 2 3 ";" 9
cod_decis_admis_r SQL_Latin1_General_CP850_CI_AI
10 SQLCHAR 2 3 ";" 10
cod_motif_deci_adm SQL_Latin1_General_CP850_CI_AI
11 SQLDATETIME 1 10 ";" 11
dat_presc_drt ""
12 SQLDATETIME 1 10 ";" 12
dat_cess_1er_emplo ""
13 SQLDATETIME 1 10 ";" 13
dat_consol ""
14 SQLDATETIME 1 10 ";\"" 14
dat_rtr_1er_emploi ""
15 SQLCHAR 2 70 "\";" 15
text_desc_diagn SQL_Latin1_General_CP850_CI_AI
16 SQLFLT8 1 6 ";" 16
taux_pe_enga_ap ""
17 SQLDATETIME 1 10 "\n" 17
dat_enrg_oper ""
This is an exemple of row in my data file
ENL83946781;73655112;110235140;900;1997-04-03;1997-04-03;LP;1997-04-25;ACC;REG;1998-04-03;1997-04-03;1997-04-22;1997-04-22;"a
laceration to the left hand";1.2;2001-01-01
I have this error and I don't know where to look because it is the first
time I use BULK INSERT.
This is the error
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
The statement has been terminated.
Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1.
Make sure the field terminator and row terminator are specified correctly.
I need help please
Thank you
Marc R.> This is an exemple of row in my data file
> ENL83946781;73655112;110235140;900;1997-04-03;1997-04-03;LP;1997-04-25;ACC;REG;1998-04-03;1997-04-03;1997-04-22;1997-04-22;"a
> laceration to the left hand";1.2;2001-01-01
This record is in character format and has no length prefixes. The format
file below describes the source file according this sample data. I also
increased the maximum field lengths to accommodate the target SQL data types
in case other records have longer values.
8.0
17
1 SQLCHAR 0 12 ";" 1 no_empl SQL_Latin1_General_CP850_CI_AI
2 SQLCHAR 0 12 ";" 2 no_dexp SQL_Latin1_General_CP850_CI_AI
3 SQLCHAR 0 9 ";" 3 no_doss_ir ""
4 SQLCHAR 0 4 ";" 4 no_even_ir ""
5 SQLCHAR 0 10 ";" 5 dat_even_orig_ir ""
6 SQLCHAR 0 10 ";" 6 dat_even_ir ""
7 SQLCHAR 0 2 ";" 7 cod_loi_repa SQL_Latin1_General_CP850_CI_AI
8 SQLCHAR 0 10 ";" 8 dat_inscr_decis_r ""
9 SQLCHAR 0 3 ";" 9 cod_decis_admis_r
SQL_Latin1_General_CP850_CI_AI
10 SQLCHAR 0 3 ";" 10 cod_motif_deci_adm
SQL_Latin1_General_CP850_CI_AI
11 SQLCHAR 0 10 ";" 11 dat_presc_drt ""
12 SQLCHAR 0 10 ";" 12 dat_cess_1er_emplo ""
13 SQLCHAR 0 10 ";" 13 dat_consol ""
14 SQLCHAR 0 10 ";\"" 14 dat_rtr_1er_emploi ""
15 SQLCHAR 0 70 "\";" 15 text_desc_diagn
SQL_Latin1_General_CP850_CI_AI
16 SQLCHAR 0 38 ";" 16 taux_pe_enga_ap ""
17 SQLCHAR 0 10 "\n" 17 dat_enrg_oper ""
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Marc Robitaille" <marc.marie AT globetrotter.net.del> wrote in message
news:e18Mq8lUHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Hello,
> This is my table
> CREATE TABLE [dbo].[TDE018](
> [no_empl] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
> [no_dexp] [varchar](12) COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL,
> [no_doss_ir] [int] NOT NULL,
> [no_even_ir] [smallint] NOT NULL,
> [dat_even_orig_ir] [datetime] NULL,
> [dat_even_ir] [datetime] NULL,
> [cod_loi_repa] [varchar](2) COLLATE SQL_Latin1_General_CP850_CI_AI NULL,
> [dat_inscr_decis_r] [datetime] NULL,
> [cod_decis_admis_r] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
> NULL,
> [cod_motif_deci_adm] [varchar](3) COLLATE SQL_Latin1_General_CP850_CI_AI
> NULL,
> [dat_presc_drt] [datetime] NULL,
> [dat_cess_1er_emplo] [datetime] NULL,
> [dat_consol] [datetime] NULL,
> [dat_rtr_1er_emploi] [datetime] NULL,
> [text_desc_diagn] [varchar](80) COLLATE SQL_Latin1_General_CP850_CI_AI
> NULL,
> [taux_pe_enga_ap] [float] NULL,
> [dat_enrg_oper] [datetime] NULL
> ) ON [PRIMARY]
> This is the command that I use
> BULK INSERT TDE018 FROM 'C:\TDE018.csv' WITH (FORMATFILE => 'C:\TDE018.fmt', KEEPNULLS, CODEPAGE ='1252')
> This is my format file
> 8.0
> 17
> 1 SQLCHAR 2 12 ";" 1
> no_empl SQL_Latin1_General_CP850_CI_AI
> 2 SQLCHAR 2 12 ";"
> 2 no_dexp SQL_Latin1_General_CP850_CI_AI
> 3 SQLINT 0 9 ";" 3 no_doss_ir
> ""
> 4 SQLSMALLINT 0 4 ";" 4
> no_even_ir ""
> 5 SQLDATETIME 1 10 ";" 5
> dat_even_orig_ir ""
> 6 SQLDATETIME 1 10 ";" 6
> dat_even_ir ""
> 7 SQLCHAR 2 2 ";" 7
> cod_loi_repa SQL_Latin1_General_CP850_CI_AI
> 8 SQLDATETIME 1 10 ";" 8
> dat_inscr_decis_r ""
> 9 SQLCHAR 2 3 ";" 9
> cod_decis_admis_r SQL_Latin1_General_CP850_CI_AI
> 10 SQLCHAR 2 3 ";" 10
> cod_motif_deci_adm SQL_Latin1_General_CP850_CI_AI
> 11 SQLDATETIME 1 10 ";" 11
> dat_presc_drt ""
> 12 SQLDATETIME 1 10 ";" 12
> dat_cess_1er_emplo ""
> 13 SQLDATETIME 1 10 ";" 13
> dat_consol ""
> 14 SQLDATETIME 1 10 ";\"" 14
> dat_rtr_1er_emploi ""
> 15 SQLCHAR 2 70 "\";" 15
> text_desc_diagn SQL_Latin1_General_CP850_CI_AI
> 16 SQLFLT8 1 6 ";" 16
> taux_pe_enga_ap ""
> 17 SQLDATETIME 1 10 "\n" 17
> dat_enrg_oper ""
> This is an exemple of row in my data file
> ENL83946781;73655112;110235140;900;1997-04-03;1997-04-03;LP;1997-04-25;ACC;REG;1998-04-03;1997-04-03;1997-04-22;1997-04-22;"a
> laceration to the left hand";1.2;2001-01-01
> I have this error and I don't know where to look because it is the first
> time I use BULK INSERT.
> This is the error
> Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> The statement has been terminated.
> Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1, column
> 1. Make sure the field terminator and row terminator are specified
> correctly.
> I need help please
> Thank you
> Marc R.
>

Bulk Insert for non admins

Thursday, March 8, 2012

Bulk Insert for non admins

Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
instance of SQL on my desktop. I create an SQL Login named BITest and allow
him into my Pubs DB. I then add him to the BulkAdmin fixed server role. When
I have him try to Bulk Insert, I get:
Server: Msg 8104, Level 16, State 2, Line 1
The current user is not the database or object owner of table
'pubs.dbo.t_access'. Cannot perform SET operation.
Which sort of indicates he needs to be in the db_owner role? Even though I
think thats crap, I add him to db_owner, and then try to run my Bulk Insert
again. This time I get:
Server: Msg 4834, Level 16, State 1, Line 1
You do not have permission to use the BULK INSERT statement.
So, not only does it really appear that he nees to be part of db_owner, it
still doesnt work. What gives?
--
TIA,
ChrisRChris,
From BOL:
Permissions
Only members of the sysadmin and bulkadmin fixed server roles can execute
BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
> instance of SQL on my desktop. I create an SQL Login named BITest and
> allow
> him into my Pubs DB. I then add him to the BulkAdmin fixed server role.
> When
> I have him try to Bulk Insert, I get:
> Server: Msg 8104, Level 16, State 2, Line 1
> The current user is not the database or object owner of table
> 'pubs.dbo.t_access'. Cannot perform SET operation.
> Which sort of indicates he needs to be in the db_owner role? Even though
> I
> think thats crap, I add him to db_owner, and then try to run my Bulk
> Insert
> again. This time I get:
> Server: Msg 4834, Level 16, State 1, Line 1
> You do not have permission to use the BULK INSERT statement.
> So, not only does it really appear that he nees to be part of db_owner, it
> still doesnt work. What gives?
> --
> TIA,
> ChrisR|||Jerry, as indicated in my post, I added him to that role.
--
TIA,
ChrisR
"Jerry Spivey" wrote:
> Chris,
> From BOL:
> Permissions
> Only members of the sysadmin and bulkadmin fixed server roles can execute
> BULK INSERT.
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a local
> > instance of SQL on my desktop. I create an SQL Login named BITest and
> > allow
> > him into my Pubs DB. I then add him to the BulkAdmin fixed server role.
> > When
> > I have him try to Bulk Insert, I get:
> >
> > Server: Msg 8104, Level 16, State 2, Line 1
> > The current user is not the database or object owner of table
> > 'pubs.dbo.t_access'. Cannot perform SET operation.
> >
> > Which sort of indicates he needs to be in the db_owner role? Even though
> > I
> > think thats crap, I add him to db_owner, and then try to run my Bulk
> > Insert
> > again. This time I get:
> >
> > Server: Msg 4834, Level 16, State 1, Line 1
> > You do not have permission to use the BULK INSERT statement.
> >
> > So, not only does it really appear that he nees to be part of db_owner, it
> > still doesnt work. What gives?
> >
> > --
> > TIA,
> > ChrisR
>
>|||Chris,
I got it to work with BULKADMIN and DB_OWNER. How many records to you have?
Do you really need to use BCP/BULK INSERT or would DTS possibly work?
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
> Jerry, as indicated in my post, I added him to that role.
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
>> Chris,
>> From BOL:
>> Permissions
>> Only members of the sysadmin and bulkadmin fixed server roles can execute
>> BULK INSERT.
>> HTH
>> Jerry
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
>> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a
>> > local
>> > instance of SQL on my desktop. I create an SQL Login named BITest and
>> > allow
>> > him into my Pubs DB. I then add him to the BulkAdmin fixed server
>> > role.
>> > When
>> > I have him try to Bulk Insert, I get:
>> >
>> > Server: Msg 8104, Level 16, State 2, Line 1
>> > The current user is not the database or object owner of table
>> > 'pubs.dbo.t_access'. Cannot perform SET operation.
>> >
>> > Which sort of indicates he needs to be in the db_owner role? Even
>> > though
>> > I
>> > think thats crap, I add him to db_owner, and then try to run my Bulk
>> > Insert
>> > again. This time I get:
>> >
>> > Server: Msg 4834, Level 16, State 1, Line 1
>> > You do not have permission to use the BULK INSERT statement.
>> >
>> > So, not only does it really appear that he nees to be part of db_owner,
>> > it
>> > still doesnt work. What gives?
>> >
>> > --
>> > TIA,
>> > ChrisR
>>|||I have 48 reconds. DTS may work, but I'd really like to try to get this
going. You're user is NOT a member of either SA of Admins, right?
--
TIA,
ChrisR
"Jerry Spivey" wrote:
> Chris,
> I got it to work with BULKADMIN and DB_OWNER. How many records to you have?
> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
> > Jerry, as indicated in my post, I added him to that role.
> > --
> > TIA,
> > ChrisR
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Chris,
> >>
> >> From BOL:
> >>
> >> Permissions
> >> Only members of the sysadmin and bulkadmin fixed server roles can execute
> >> BULK INSERT.
> >>
> >> HTH
> >>
> >> Jerry
> >>
> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> >> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> >> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a
> >> > local
> >> > instance of SQL on my desktop. I create an SQL Login named BITest and
> >> > allow
> >> > him into my Pubs DB. I then add him to the BulkAdmin fixed server
> >> > role.
> >> > When
> >> > I have him try to Bulk Insert, I get:
> >> >
> >> > Server: Msg 8104, Level 16, State 2, Line 1
> >> > The current user is not the database or object owner of table
> >> > 'pubs.dbo.t_access'. Cannot perform SET operation.
> >> >
> >> > Which sort of indicates he needs to be in the db_owner role? Even
> >> > though
> >> > I
> >> > think thats crap, I add him to db_owner, and then try to run my Bulk
> >> > Insert
> >> > again. This time I get:
> >> >
> >> > Server: Msg 4834, Level 16, State 1, Line 1
> >> > You do not have permission to use the BULK INSERT statement.
> >> >
> >> > So, not only does it really appear that he nees to be part of db_owner,
> >> > it
> >> > still doesnt work. What gives?
> >> >
> >> > --
> >> > TIA,
> >> > ChrisR
> >>
> >>
> >>
>
>|||I'd recommend DTS but it is your choice. No...created new login and user to
test for your example.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...
>I have 48 reconds. DTS may work, but I'd really like to try to get this
> going. You're user is NOT a member of either SA of Admins, right?
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
>> Chris,
>> I got it to work with BULKADMIN and DB_OWNER. How many records to you
>> have?
>> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
>> HTH
>> Jerry
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
>> > Jerry, as indicated in my post, I added him to that role.
>> > --
>> > TIA,
>> > ChrisR
>> >
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Chris,
>> >>
>> >> From BOL:
>> >>
>> >> Permissions
>> >> Only members of the sysadmin and bulkadmin fixed server roles can
>> >> execute
>> >> BULK INSERT.
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >>
>> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> >> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
>> >> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a
>> >> > local
>> >> > instance of SQL on my desktop. I create an SQL Login named BITest
>> >> > and
>> >> > allow
>> >> > him into my Pubs DB. I then add him to the BulkAdmin fixed server
>> >> > role.
>> >> > When
>> >> > I have him try to Bulk Insert, I get:
>> >> >
>> >> > Server: Msg 8104, Level 16, State 2, Line 1
>> >> > The current user is not the database or object owner of table
>> >> > 'pubs.dbo.t_access'. Cannot perform SET operation.
>> >> >
>> >> > Which sort of indicates he needs to be in the db_owner role? Even
>> >> > though
>> >> > I
>> >> > think thats crap, I add him to db_owner, and then try to run my Bulk
>> >> > Insert
>> >> > again. This time I get:
>> >> >
>> >> > Server: Msg 4834, Level 16, State 1, Line 1
>> >> > You do not have permission to use the BULK INSERT statement.
>> >> >
>> >> > So, not only does it really appear that he nees to be part of
>> >> > db_owner,
>> >> > it
>> >> > still doesnt work. What gives?
>> >> >
>> >> > --
>> >> > TIA,
>> >> > ChrisR
>> >>
>> >>
>> >>
>>|||Curious, why DTS? Also, Im running SQL2K, and you?
--
TIA,
ChrisR
"Jerry Spivey" wrote:
> I'd recommend DTS but it is your choice. No...created new login and user to
> test for your example.
> HTH
> Jerry
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...
> >I have 48 reconds. DTS may work, but I'd really like to try to get this
> > going. You're user is NOT a member of either SA of Admins, right?
> > --
> > TIA,
> > ChrisR
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Chris,
> >>
> >> I got it to work with BULKADMIN and DB_OWNER. How many records to you
> >> have?
> >> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
> >>
> >> HTH
> >>
> >> Jerry
> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> >> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
> >> > Jerry, as indicated in my post, I added him to that role.
> >> > --
> >> > TIA,
> >> > ChrisR
> >> >
> >> >
> >> > "Jerry Spivey" wrote:
> >> >
> >> >> Chris,
> >> >>
> >> >> From BOL:
> >> >>
> >> >> Permissions
> >> >> Only members of the sysadmin and bulkadmin fixed server roles can
> >> >> execute
> >> >> BULK INSERT.
> >> >>
> >> >> HTH
> >> >>
> >> >> Jerry
> >> >>
> >> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> >> >> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
> >> >> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I have a
> >> >> > local
> >> >> > instance of SQL on my desktop. I create an SQL Login named BITest
> >> >> > and
> >> >> > allow
> >> >> > him into my Pubs DB. I then add him to the BulkAdmin fixed server
> >> >> > role.
> >> >> > When
> >> >> > I have him try to Bulk Insert, I get:
> >> >> >
> >> >> > Server: Msg 8104, Level 16, State 2, Line 1
> >> >> > The current user is not the database or object owner of table
> >> >> > 'pubs.dbo.t_access'. Cannot perform SET operation.
> >> >> >
> >> >> > Which sort of indicates he needs to be in the db_owner role? Even
> >> >> > though
> >> >> > I
> >> >> > think thats crap, I add him to db_owner, and then try to run my Bulk
> >> >> > Insert
> >> >> > again. This time I get:
> >> >> >
> >> >> > Server: Msg 4834, Level 16, State 1, Line 1
> >> >> > You do not have permission to use the BULK INSERT statement.
> >> >> >
> >> >> > So, not only does it really appear that he nees to be part of
> >> >> > db_owner,
> >> >> > it
> >> >> > still doesnt work. What gives?
> >> >> >
> >> >> > --
> >> >> > TIA,
> >> >> > ChrisR
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Its fast, friendly (permissions issue in your case) and more functional.
SQL 2000 as well.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:B4925F9E-6BBB-477E-B4B7-9817D10CB89B@.microsoft.com...
> Curious, why DTS? Also, Im running SQL2K, and you?
> --
> TIA,
> ChrisR
>
> "Jerry Spivey" wrote:
>> I'd recommend DTS but it is your choice. No...created new login and user
>> to
>> test for your example.
>> HTH
>> Jerry
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:63A4B6D4-EABD-409A-BD49-E39B0CC7FF6F@.microsoft.com...
>> >I have 48 reconds. DTS may work, but I'd really like to try to get this
>> > going. You're user is NOT a member of either SA of Admins, right?
>> > --
>> > TIA,
>> > ChrisR
>> >
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Chris,
>> >>
>> >> I got it to work with BULKADMIN and DB_OWNER. How many records to you
>> >> have?
>> >> Do you really need to use BCP/BULK INSERT or would DTS possibly work?
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> >> news:1B350D2E-A85B-4E25-B3C9-C19620DF0ACF@.microsoft.com...
>> >> > Jerry, as indicated in my post, I added him to that role.
>> >> > --
>> >> > TIA,
>> >> > ChrisR
>> >> >
>> >> >
>> >> > "Jerry Spivey" wrote:
>> >> >
>> >> >> Chris,
>> >> >>
>> >> >> From BOL:
>> >> >>
>> >> >> Permissions
>> >> >> Only members of the sysadmin and bulkadmin fixed server roles can
>> >> >> execute
>> >> >> BULK INSERT.
>> >> >>
>> >> >> HTH
>> >> >>
>> >> >> Jerry
>> >> >>
>> >> >> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> >> >> news:8A4D0077-8989-4CA0-90AD-98EE96C9CEB8@.microsoft.com...
>> >> >> > Since Im pretty sure BCP is out, Ill look into Bulk Insert. I
>> >> >> > have a
>> >> >> > local
>> >> >> > instance of SQL on my desktop. I create an SQL Login named BITest
>> >> >> > and
>> >> >> > allow
>> >> >> > him into my Pubs DB. I then add him to the BulkAdmin fixed
>> >> >> > server
>> >> >> > role.
>> >> >> > When
>> >> >> > I have him try to Bulk Insert, I get:
>> >> >> >
>> >> >> > Server: Msg 8104, Level 16, State 2, Line 1
>> >> >> > The current user is not the database or object owner of table
>> >> >> > 'pubs.dbo.t_access'. Cannot perform SET operation.
>> >> >> >
>> >> >> > Which sort of indicates he needs to be in the db_owner role?
>> >> >> > Even
>> >> >> > though
>> >> >> > I
>> >> >> > think thats crap, I add him to db_owner, and then try to run my
>> >> >> > Bulk
>> >> >> > Insert
>> >> >> > again. This time I get:
>> >> >> >
>> >> >> > Server: Msg 4834, Level 16, State 1, Line 1
>> >> >> > You do not have permission to use the BULK INSERT statement.
>> >> >> >
>> >> >> > So, not only does it really appear that he nees to be part of
>> >> >> > db_owner,
>> >> >> > it
>> >> >> > still doesnt work. What gives?
>> >> >> >
>> >> >> > --
>> >> >> > TIA,
>> >> >> > ChrisR
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Bulk Insert fail for TempTable (bulkAdmi)

Hi,
I am trying to create a temp table and bulk insert data into that temp table
.
I am using the 'bulkinsert' login is a part of a server role bulkadmin and
has read access to databaseA.
Create table #My_Table
( Col1 varchar(14),
Col2 varchar(60))
then trying to bulk insert using
BULK INSERT #My_Table FROM '\\Network1\Data\TestDoc.txt' WITH
(Rowterminator='\n', FieldTerminator=',')
But it is giving me following error.
The current user is not the database or object owner of table '#My_Table ".
Cannot perform SET operation.
My 'bulkinsert' login has Access to DatabaseA, and also to tempdb.
Anyone have any idea why this is happening.. ? Your help is much appreciated
.
Thank youbluefish (bluefish@.discussions.microsoft.com) writes:
> I am trying to create a temp table and bulk insert data into that temp
> table.
> I am using the 'bulkinsert' login is a part of a server role bulkadmin
> and has read access to databaseA.
> Create table #My_Table
> ( Col1 varchar(14),
> Col2 varchar(60))
> then trying to bulk insert using
> BULK INSERT #My_Table FROM '\\Network1\Data\TestDoc.txt' WITH
> (Rowterminator='\n', FieldTerminator=',')
> But it is giving me following error.
> The current user is not the database or object owner of table
> '#My_Table ".
> Cannot perform SET operation.
> My 'bulkinsert' login has Access to DatabaseA, and also to tempdb.
> Anyone have any idea why this is happening.. ? Your help is much
> appreciated.
This is a flaw in SQL Server. Temp tables are "owned" by uid = 1 in
sysobjects, so the user needs dbo rights in tempdb (which I don't
recommend.) You will have to stick to a permanent table.
See also
5cc04c2cf" target="_blank">http://groups.google.com/group/comp...r />
5cc04c2cf
ab18661
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you very much!
"Erland Sommarskog" wrote:

> bluefish (bluefish@.discussions.microsoft.com) writes:
> This is a flaw in SQL Server. Temp tables are "owned" by uid = 1 in
> sysobjects, so the user needs dbo rights in tempdb (which I don't
> recommend.) You will have to stick to a permanent table.
> See also
> http://groups.google.com/group/comp...3&hl=sv#5cc04c2
cfab18661
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Also found out that ddl_admin permission to tempdb works as well.
Thank you
Sam
"Erland Sommarskog" wrote:

> bluefish (bluefish@.discussions.microsoft.com) writes:
> This is a flaw in SQL Server. Temp tables are "owned" by uid = 1 in
> sysobjects, so the user needs dbo rights in tempdb (which I don't
> recommend.) You will have to stick to a permanent table.
> See also
> http://groups.google.com/group/comp...3&hl=sv#5cc04c2
cfab18661
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||bluefish (bluefish@.discussions.microsoft.com) writes:
> Also found out that ddl_admin permission to tempdb works as well.
OK, that slightly better than db_owner rights, but not much.
It appears that this issue has been addressed in SQL 2005.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BULK INSERT datafile parameter

Hi everyone,
how can I pass a variable to the command "BULK INSERT", to the datafile
parameter? The following script:
create procedure some_procedure (@.filename varchar(256)) as
begin
bulk insert some_table from @.filename with(codepage='raw');
end;
fails with error "Incorrect syntax near '@.filename'". Any idea?
Thanks,
Tamas BeriCreate a string with the bulk insert command and exec it ie
exec('bulk insert sometable from ' + @.filename + yad yada)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gfoyle" <gfoyle@.discussions.microsoft.com> wrote in message
news:546E659E-ABC3-4B34-B6D5-2EB647E3A6A9@.microsoft.com...
> Hi everyone,
> how can I pass a variable to the command "BULK INSERT", to the datafile
> parameter? The following script:
> create procedure some_procedure (@.filename varchar(256)) as
> begin
> bulk insert some_table from @.filename with(codepage='raw');
> end;
> fails with error "Incorrect syntax near '@.filename'". Any idea?
> Thanks,
> Tamas Beri
>|||Thanks,
it's finally working with the exec...
"Wayne Snyder" wrote:
> Create a string with the bulk insert command and exec it ie
> exec('bulk insert sometable from ' + @.filename + yad yada)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com

Wednesday, March 7, 2012

Bulk Insert and Decimal type

Hi there

I am trying to write a program which will bulk load data from a bcp file into a newly made database on the users PC.

I create the data from an existing DB using SQL-DMO BulkCopy.
I then load it into the users DB using "Bulk Insert " transact SQL.

It all works fine on SQL Server 2000. However on SQL Server 7.0 whenever the .bcp file is being loaded into a table with a field of type decimal, it throws an OLEDB stream error. Even when the .bcp file is empty.

I have tried exporting/importing the data as tab delimited and as native, but it seems to make no difference.

This has really got me stumped and I am running out of time. Can anyone help?

Thanks.

justinOK I just discovered from the Microsoft web site that there is a bug in SQL Server 7.0. Using Bulk Insert on a table that includes a default value for decimal or numeric data typed fields, throws an error.

There is no solution. It is incurable. The workaround is to "use bcp instead."

Programatically that would be an issue, so i will have to use DMO.

Saturday, February 25, 2012

BULK INSERT - escape " - .fmt

Hi All,
Simple table:
TABLE:
create table employee (
name char(20),
title char(120)
)
DATAFILE(.csv):
name;title
Gustavo;"Marketing Assistant"
Catherine;"Engineering Manager"
I'd like use BULK INSERT command to insert this data. Tell me please,
how should look formatfile (.fmt) for this example (SQL 2005). I need
data after BULK INSERT looks like this:
name title
-- --
Gustavo Marketing Assistant
Catherine Engineering Manager
NOT like this:
name title
-- --
Gustavo "Marketing Assistant"
Catherine "Engineering Manager"
So, " should be escape.
Thank you
--
RegardsHello,
I've done this, it's almost finished. You can tray this:
/
***************************************************************************************************/
--table
CREATE TABLE [dbo].[ImportTest](
[fname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[lname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[organization] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[address] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[zip] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[city] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[state] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[email] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
--data file (ImportTest.csv), REMEMBER at the end you HAVE TO add
ENTER
fname;lname;organization;address;zip;city;state;email;
Shirley;Birosik;;;90013;"Los Angeles";"CA";"sbirosik@.gmail.com";
Dennis;Eschen;;;94920;"Alhambra";"CA";"de@.hotmail.com";
Valerie;Chambers;;;91803;"Long Beach";"CA";"vweber@.verizon.net";
--fmt file(ImportTest.Fmt), add ENTER at the end
9.0
8
1 SQLCHAR 0 510 ";" 1
fname Latin1_General_CI_AS
2 SQLCHAR 0 510 ";" 2
lname Latin1_General_CI_AS
3 SQLCHAR 0 510 ";" 3
organization Latin1_General_CI_AS
4 SQLCHAR 0 510 ";" 4
address Latin1_General_CI_AS
5 SQLCHAR 0 510 ";\"" 5
zip Latin1_General_CI_AS
6 SQLCHAR 0 510 "\";\"" 6
city Latin1_General_CI_AS
7 SQLCHAR 0 510 "\";\"" 7
state Latin1_General_CI_AS
8 SQLCHAR 0 510 "\r\n" 8
email Latin1_General_CI_AS
bulk insert ImportTest
from 'd:\bulk\ImportTest.csv'
with (
formatfile = 'd:\bulk\ImportTest.Fmt',
firstrow = 2
)
--(2 row(s) affected)
select*
from ImportTest
Dennis Eschen NULL NULL 94920 Alhambra CA
de@.hotmail.com";
Valerie Chambers NULL NULL 91803 Long Beach CA
vweber@.verizon.net";
/
***************************************************************************************************/
For me now most important is why didn't insert first row (data row,
not column definition)?
When I added quotation mark to column definition works fine, but I
can't modify .csv file, I received it from third part company :( is it
possible add all rows without modification .csv file?
Second, how remove quotation mark and semicolon from last column?
Regards,
anxcomp

BULK INSERT - error column too long

Hi,
I need to load a data file with bulk load.
I have these table:
CREATE TABLE [dbo].[test_db_import](
[campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
[campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
the input file (a file .txt) is like that:
"value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
13 value, with text qualifier double quote, in one row
and the format file:
9.0
13
1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
I have these error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]
error bulk load. The data file contains a column too long for the row
1, column 13. Check the rowterminator and the field terminator.
(sorry, that is a translation from error message in other languages)
I have SQL Server 2005 St. edition, and I don't have direct access to
the db server.
Can you help me?
Thank's!
FrancI would guess that the data file is missing row terminators on some columns.
You may have to visually inspect and/or correct the data file.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Franc" <webmasterpoint@.gmail.com> wrote in message
news:1166518533.669439.310530@.t46g2000cwa.googlegroups.com...
> Hi,
> I need to load a data file with bulk load.
> I have these table:
> CREATE TABLE [dbo].[test_db_import](
> [campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> [campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> [tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> the input file (a file .txt) is like that:
> "value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
> 13 value, with text qualifier double quote, in one row
> and the format file:
> 9.0
> 13
> 1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
> 2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
> 3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
> 4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
> 5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
> 6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
> 7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
> 8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
> 9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
> 10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
> 11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
> 12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
> 13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
>
> I have these error:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> error bulk load. The data file contains a column too long for the row
> 1, column 13. Check the rowterminator and the field terminator.
> (sorry, that is a translation from error message in other languages)
> I have SQL Server 2005 St. edition, and I don't have direct access to
> the db server.
> Can you help me?
> Thank's!
> Franc
>|||Hi,
Thank's!
I try with only one row:
"10092itid2011";"Batterie Notebook e Computers Portatili HP,
COMPAQ";"125.04";"EUR";""Cod CBI0823A; Casa 2-Power; Descrizione
Batteria notebook 14.8v 4400mAh per HP Pavilion NX,
XT1-XT2-XT3-XT4-XT5, ZE4000, 5000, ZE4300-5185, HP e Compaq Business
Notebook NX9000-NX9005-NX9010, N1050V, Compaq Presario 1110,1115,1120,
2100, 2500
serie,";"value";"658";"Accumulatori";"value";"value";"value";"value";"HP,
COMPAQ"
and import correctly.
But I have a problem:
in the import skip the first column of table (field: 'campo_0')
and insert the last two field
"value";"HP, COMPAQ"
in 'campo_12' column
Thank's!
Franc
Arnie Rowland ha scritto:
> I would guess that the data file is missing row terminators on some columns.
> You may have to visually inspect and/or correct the data file.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Franc" <webmasterpoint@.gmail.com> wrote in message
> news:1166518533.669439.310530@.t46g2000cwa.googlegroups.com...
> > Hi,
> > I need to load a data file with bulk load.
> >
> > I have these table:
> >
> > CREATE TABLE [dbo].[test_db_import](
> > [campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> > [campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
> > [campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> > [tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> >
> > the input file (a file .txt) is like that:
> >
> > "value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
> >
> > 13 value, with text qualifier double quote, in one row
> >
> > and the format file:
> >
> > 9.0
> > 13
> > 1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
> > 2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
> > 3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
> > 4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
> > 5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
> > 6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
> > 7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
> > 8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
> > 9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
> > 10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
> > 11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
> > 12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
> > 13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
> >
> >
> > I have these error:
> >
> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> > [Microsoft][ODBC SQL Server Driver][SQL Server]
> > error bulk load. The data file contains a column too long for the row
> > 1, column 13. Check the rowterminator and the field terminator.
> > (sorry, that is a translation from error message in other languages)
> >
> > I have SQL Server 2005 St. edition, and I don't have direct access to
> > the db server.
> >
> > Can you help me?
> >
> > Thank's!
> > Franc
> >|||I would guess that the data file is missing row terminators on some columns.
You may have to visually inspect and/or correct the data file.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Franc" <webmasterpoint@.gmail.com> wrote in message
news:1166518533.669439.310530@.t46g2000cwa.googlegroups.com...
> Hi,
> I need to load a data file with bulk load.
> I have these table:
> CREATE TABLE [dbo].[test_db_import](
> [campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> [campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
> [campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
> [tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> the input file (a file .txt) is like that:
> "value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
> 13 value, with text qualifier double quote, in one row
> and the format file:
> 9.0
> 13
> 1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
> 2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
> 3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
> 4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
> 5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
> 6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
> 7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
> 8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
> 9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
> 10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
> 11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
> 12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
> 13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
>
> I have these error:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]
> error bulk load. The data file contains a column too long for the row
> 1, column 13. Check the rowterminator and the field terminator.
> (sorry, that is a translation from error message in other languages)
> I have SQL Server 2005 St. edition, and I don't have direct access to
> the db server.
> Can you help me?
> Thank's!
> Franc
>|||This looks 'odd' to me:
;""
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Franc" <webmasterpoint@.gmail.com> wrote in message
news:1166520934.333254.285360@.48g2000cwx.googlegroups.com...
> Hi,
> Thank's!
> I try with only one row:
> "10092itid2011";"Batterie Notebook e Computers Portatili HP,
> COMPAQ";"125.04";"EUR";""Cod CBI0823A; Casa 2-Power; Descrizione
> Batteria notebook 14.8v 4400mAh per HP Pavilion NX,
> XT1-XT2-XT3-XT4-XT5, ZE4000, 5000, ZE4300-5185, HP e Compaq Business
> Notebook NX9000-NX9005-NX9010, N1050V, Compaq Presario 1110,1115,1120,
> 2100, 2500
> serie,";"value";"658";"Accumulatori";"value";"value";"value";"value";"HP,
> COMPAQ"
> and import correctly.
> But I have a problem:
> in the import skip the first column of table (field: 'campo_0')
> and insert the last two field
> "value";"HP, COMPAQ"
> in 'campo_12' column
> Thank's!
> Franc
> Arnie Rowland ha scritto:
>> I would guess that the data file is missing row terminators on some
>> columns.
>> You may have to visually inspect and/or correct the data file.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to
>> the
>> top yourself.
>> - H. Norman Schwarzkopf
>>
>> "Franc" <webmasterpoint@.gmail.com> wrote in message
>> news:1166518533.669439.310530@.t46g2000cwa.googlegroups.com...
>> > Hi,
>> > I need to load a data file with bulk load.
>> >
>> > I have these table:
>> >
>> > CREATE TABLE [dbo].[test_db_import](
>> > [campo_0] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_1] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_2] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_3] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_4] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_5] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_6] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_7] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_8] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
>> > [campo_9] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_10] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_11] [ntext] COLLATE Latin1_General_CI_AS NULL,
>> > [campo_12] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
>> > [tappo] [nvarchar](1) COLLATE Latin1_General_CI_AS NULL
>> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>> >
>> > the input file (a file .txt) is like that:
>> >
>> > "value1";"value2";"value3";"value4";"value5";"value6";"value7";"value8";"value9";"value10";"value11";"value12";"value13"
>> >
>> > 13 value, with text qualifier double quote, in one row
>> >
>> > and the format file:
>> >
>> > 9.0
>> > 13
>> > 1 SQLCHAR 0 0 "\"" 1 campo_0 "SQL_Latin1_General_CP1_CI_AS"
>> > 2 SQLCHAR 0 0 "\";\"" 2 campo_1 "SQL_Latin1_General_CP1_CI_AS"
>> > 3 SQLCHAR 0 0 "\";\"" 3 campo_2 "SQL_Latin1_General_CP1_CI_AS"
>> > 4 SQLCHAR 0 0 "\";\"" 4 campo_3 "SQL_Latin1_General_CP1_CI_AS"
>> > 5 SQLTEXT 0 0 "\";\"" 5 campo_4 "SQL_Latin1_General_CP1_CI_AS"
>> > 6 SQLCHAR 0 0 "\";\"" 6 campo_5 "SQL_Latin1_General_CP1_CI_AS"
>> > 7 SQLCHAR 0 0 "\";\"" 7 campo_6 "SQL_Latin1_General_CP1_CI_AS"
>> > 8 SQLCHAR 0 0 "\";\"" 8 campo_7 "SQL_Latin1_General_CP1_CI_AS"
>> > 9 SQLTEXT 0 0 "\";\"" 9 campo_8 "SQL_Latin1_General_CP1_CI_AS"
>> > 10 SQLCHAR 0 0 "\";\"" 10 campo_9 "SQL_Latin1_General_CP1_CI_AS"
>> > 11 SQLCHAR 0 0 "\";\"" 11 campo_10 "SQL_Latin1_General_CP1_CI_AS"
>> > 12 SQLCHAR 0 0 "\";\"" 12 campo_11 "SQL_Latin1_General_CP1_CI_AS"
>> > 13 SQLCHAR 0 0 \"" 13 campo_12 "SQL_Latin1_General_CP1_CI_AS"
>> >
>> >
>> > I have these error:
>> >
>> > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
>> > [Microsoft][ODBC SQL Server Driver][SQL Server]
>> > error bulk load. The data file contains a column too long for the row
>> > 1, column 13. Check the rowterminator and the field terminator.
>> > (sorry, that is a translation from error message in other languages)
>> >
>> > I have SQL Server 2005 St. edition, and I don't have direct access to
>> > the db server.
>> >
>> > Can you help me?
>> >
>> > Thank's!
>> > Franc
>> >
>

Friday, February 24, 2012

bulk insert

I create format file with DTS then I execute the bulk insert statement:
BULK INSERT tableTest FROM 'c:\testData.txt'
WITH (FORMATFILE = 'c:\testFormat.fmt')
but I get error:
string or binary data would be truncated
Why? What could be the reason?
Format file seems to be ok.
Regards,SSimon,
You receive an error like this if the destination column is smaller than the
value you are trying to INSERT.
HTH
Jerry
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:etBsrYoyFHA.3180@.TK2MSFTNGP14.phx.gbl...
>I create format file with DTS then I execute the bulk insert statement:
> BULK INSERT tableTest FROM 'c:\testData.txt'
> WITH (FORMATFILE = 'c:\testFormat.fmt')
> but I get error:
> string or binary data would be truncated
> Why? What could be the reason?
> Format file seems to be ok.
> Regards,S
>|||You get a "string or binary data would be truncated" when the data you are
trying to insert in a column is longer than the maximum length allowed in
that column. You can compare the format file with the table definition to
see if that is the case. If it seems to be ok, but you still get the error,
please post both the table definition and the format file.
Jacco Schalkwijk
SQL Server MVP
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:etBsrYoyFHA.3180@.TK2MSFTNGP14.phx.gbl...
>I create format file with DTS then I execute the bulk insert statement:
> BULK INSERT tableTest FROM 'c:\testData.txt'
> WITH (FORMATFILE = 'c:\testFormat.fmt')
> but I get error:
> string or binary data would be truncated
> Why? What could be the reason?
> Format file seems to be ok.
> Regards,S
>|||Check the data itself. One of the fields is too long on one of the lines
causing an overflow. Try importing with the Import/Export tool and you shoul
d
find the culprit rather quickly.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"simon" wrote:

> I create format file with DTS then I execute the bulk insert statement:
> BULK INSERT tableTest FROM 'c:\testData.txt'
> WITH (FORMATFILE = 'c:\testFormat.fmt')
> but I get error:
> string or binary data would be truncated
> Why? What could be the reason?
> Format file seems to be ok.
> Regards,S
>
>|||everything seems OK to me.
FORMAT FILE:
7.0
38
1 SQLCHAR 0 10 "," 1 column1
2 SQLCHAR 0 4 "," 2 column2
3 SQLCHAR 0 10 "," 3 column3
4 SQLCHAR 0 100 "," 4 column4
5 SQLCHAR 0 8 "," 5 column5
6 SQLCHAR 0 8 "," 6 column6
7 SQLCHAR 0 8 "," 7 column7
8 SQLCHAR 0 8 "," 8 column8
9 SQLCHAR 0 8 "," 9 column9
10 SQLCHAR 0 8 "," 10 column10
11 SQLCHAR 0 2 "," 11 column11
12 SQLCHAR 0 2 "," 12 column12
13 SQLCHAR 0 4 "," 13 column13
14 SQLCHAR 0 2 "," 14 column14
15 SQLCHAR 0 8 "," 15 column15
16 SQLCHAR 0 2 "," 16 column16
17 SQLCHAR 0 10 "," 17 column17
18 SQLCHAR 0 2 "," 18 column18
19 SQLCHAR 0 10 "," 19 column19
20 SQLCHAR 0 2 "," 20 column20
21 SQLCHAR 0 100 "," 21 column21
22 SQLCHAR 0 16 "," 22 column22
23 SQLCHAR 0 100 "," 23 column23
24 SQLCHAR 0 16 "," 24 column24
25 SQLCHAR 0 2 "," 25 column25
26 SQLCHAR 0 16 "," 26 column26
27 SQLCHAR 0 2 "," 27 column27
28 SQLCHAR 0 16 "," 28 column28
29 SQLCHAR 0 2 "," 29 column29
30 SQLCHAR 0 16 "," 30 column30
31 SQLCHAR 0 20 "," 31 column31
32 SQLCHAR 0 2 "," 32 column32
33 SQLCHAR 0 8 "," 33 column33
34 SQLCHAR 0 8 "," 34 column34
35 SQLCHAR 0 8 "," 35 column35
36 SQLCHAR 0 2 "," 36 column36
37 SQLCHAR 0 8 "," 37 column37
38 SQLCHAR 0 8 "\r\n" 38 column38
table definition:
CREATE TABLE [T_TABLE] (
[column1] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[column2] [int] NOT NULL ,
[column3] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[column4] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
[column5] [float] NOT NULL CONSTRAINT [DF_T_column5] DEFAULT (0),
[column6] [float] NULL CONSTRAINT [DF_T_column6] DEFAULT (0),
[column7] [float] NOT NULL CONSTRAINT [DF_T_column7] DEFAULT (0),
[column8] [float] NOT NULL CONSTRAINT [DF_T_column8] DEFAULT (0),
[column9] [float] NULL CONSTRAINT [DF_T_column9] DEFAULT (0),
[column10] [float] NOT NULL CONSTRAINT [DF_T_column10] DEFAULT (0),
[column11] [smallint] NULL CONSTRAINT [DF_T_column11] DEFAULT (0),
[column12] [Boolean] NOT NULL ,
[column13] [int] NULL ,
[column14] [smallint] NULL CONSTRAINT [DF_T_column14] DEFAULT (0),
[column15] [float] NULL CONSTRAINT [DF_T_column15] DEFAULT (0),
[column16] [Boolean] NOT NULL ,
[column17] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
[column18] [Boolean] NOT NULL ,
[column19] [varchar] (10) COLLATE Slovenian_CI_AS NULL ,
[column20] [smallint] NULL CONSTRAINT [DF_T_column20] DEFAULT (0),
[column21] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
[column22] [smalldatetime] NULL ,
[column23] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
[column24] [smalldatetime] NULL ,
[column25] [Boolean] NOT NULL ,
[column26] [smalldatetime] NULL CONSTRAINT [DF_T_column26] DEFAULT
(getdate()),
[column27] [varchar] (2) COLLATE Slovenian_CI_AS NOT NULL CONSTRAINT
[DF__T_column27] DEFAULT ('**'),
[column28] [smalldatetime] NOT NULL CONSTRAINT [DF__T_column28] DEFAULT
(getdate()),
[column29] [varchar] (2) COLLATE Slovenian_CI_AS NOT NULL CONSTRAINT
[DF__T_column29] DEFAULT ('**'),
[column30] [smalldatetime] NOT NULL CONSTRAINT [DF_T_column30] DEFAULT
(getdate()),
[column31] [varchar] (20) COLLATE Slovenian_CI_AS NULL ,
[column32] [Boolean] NOT NULL CONSTRAINT [DF_T_column32] DEFAULT (0),
[column33] [float] NULL CONSTRAINT [DF_T_column33] DEFAULT (0),
[column34] [timestamp] NULL ,
[column35] [varbinary] (8) NULL ,
[column36] [char] (2) COLLATE Slovenian_CI_AS NULL ,
[column37] [float] NULL CONSTRAINT [DF_T_column37] DEFAULT (0),
[column38] [float] NULL CONSTRAINT [DF_T_column38] DEFAULT (0),
CONSTRAINT [PK_T_TABLE] PRIMARY KEY CLUSTERED
(
[column1],
[column2]
) ON [PRIMARY]
) ON [PRIMARY]
I just change column names.
And the data file was exported directly from Enterprise manager. One line
looks like:
"_000000281",1156055,"I0121","product",-1,0,39960,0,0,-39960,0,0,0,0,0,0,"AV
",0,"",0,"",,"",,0,2003-01-23
20:50:00,"NS",2000-01-02 09:25:00,"99",2000-01-02
09:25:00,"#NS:2000000281",-1,0,000000018AC09D10,,"",,
If I import this data without format file it works (not for all lines).
If I include only good lines in other file and try with format file, I get
the following error.
Without format file, works.
Regards,
Simon
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eU4cRioyFHA.2960@.tk2msftngp13.phx.gbl...
> You get a "string or binary data would be truncated" when the data you are
> trying to insert in a column is longer than the maximum length allowed in
> that column. You can compare the format file with the table definition to
> see if that is the case. If it seems to be ok, but you still get the
> error, please post both the table definition and the format file.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "simon" <simon.zupan@.stud-moderna.si> wrote in message
> news:etBsrYoyFHA.3180@.TK2MSFTNGP14.phx.gbl...
>|||I think the problem could be with the varbinary column. The implicit
conversion from strings to binaries is not very intuitive. I can quickly
pinpoint the exact problem though.
Jacco Schalkwijk
SQL Server MVP
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:eEUNbqwyFHA.1256@.TK2MSFTNGP09.phx.gbl...
> everything seems OK to me.
> FORMAT FILE:
> 7.0
> 38
> 1 SQLCHAR 0 10 "," 1 column1
> 2 SQLCHAR 0 4 "," 2 column2
> 3 SQLCHAR 0 10 "," 3 column3
> 4 SQLCHAR 0 100 "," 4 column4
> 5 SQLCHAR 0 8 "," 5 column5
> 6 SQLCHAR 0 8 "," 6 column6
> 7 SQLCHAR 0 8 "," 7 column7
> 8 SQLCHAR 0 8 "," 8 column8
> 9 SQLCHAR 0 8 "," 9 column9
> 10 SQLCHAR 0 8 "," 10 column10
> 11 SQLCHAR 0 2 "," 11 column11
> 12 SQLCHAR 0 2 "," 12 column12
> 13 SQLCHAR 0 4 "," 13 column13
> 14 SQLCHAR 0 2 "," 14 column14
> 15 SQLCHAR 0 8 "," 15 column15
> 16 SQLCHAR 0 2 "," 16 column16
> 17 SQLCHAR 0 10 "," 17 column17
> 18 SQLCHAR 0 2 "," 18 column18
> 19 SQLCHAR 0 10 "," 19 column19
> 20 SQLCHAR 0 2 "," 20 column20
> 21 SQLCHAR 0 100 "," 21 column21
> 22 SQLCHAR 0 16 "," 22 column22
> 23 SQLCHAR 0 100 "," 23 column23
> 24 SQLCHAR 0 16 "," 24 column24
> 25 SQLCHAR 0 2 "," 25 column25
> 26 SQLCHAR 0 16 "," 26 column26
> 27 SQLCHAR 0 2 "," 27 column27
> 28 SQLCHAR 0 16 "," 28 column28
> 29 SQLCHAR 0 2 "," 29 column29
> 30 SQLCHAR 0 16 "," 30 column30
> 31 SQLCHAR 0 20 "," 31 column31
> 32 SQLCHAR 0 2 "," 32 column32
> 33 SQLCHAR 0 8 "," 33 column33
> 34 SQLCHAR 0 8 "," 34 column34
> 35 SQLCHAR 0 8 "," 35 column35
> 36 SQLCHAR 0 2 "," 36 column36
> 37 SQLCHAR 0 8 "," 37 column37
> 38 SQLCHAR 0 8 "\r\n" 38 column38
> table definition:
> CREATE TABLE [T_TABLE] (
> [column1] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [column2] [int] NOT NULL ,
> [column3] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [column4] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
> [column5] [float] NOT NULL CONSTRAINT [DF_T_column5] DEFAULT (0),
> [column6] [float] NULL CONSTRAINT [DF_T_column6] DEFAULT (0),
> [column7] [float] NOT NULL CONSTRAINT [DF_T_column7] DEFAULT (0),
> [column8] [float] NOT NULL CONSTRAINT [DF_T_column8] DEFAULT (0),
> [column9] [float] NULL CONSTRAINT [DF_T_column9] DEFAULT (0),
> [column10] [float] NOT NULL CONSTRAINT [DF_T_column10] DEFAULT (0),
> [column11] [smallint] NULL CONSTRAINT [DF_T_column11] DEFAULT (0),
> [column12] [Boolean] NOT NULL ,
> [column13] [int] NULL ,
> [column14] [smallint] NULL CONSTRAINT [DF_T_column14] DEFAULT (0),
> [column15] [float] NULL CONSTRAINT [DF_T_column15] DEFAULT (0),
> [column16] [Boolean] NOT NULL ,
> [column17] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
> [column18] [Boolean] NOT NULL ,
> [column19] [varchar] (10) COLLATE Slovenian_CI_AS NULL ,
> [column20] [smallint] NULL CONSTRAINT [DF_T_column20] DEFAULT (0),
> [column21] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
> [column22] [smalldatetime] NULL ,
> [column23] [varchar] (100) COLLATE Slovenian_CI_AS NULL ,
> [column24] [smalldatetime] NULL ,
> [column25] [Boolean] NOT NULL ,
> [column26] [smalldatetime] NULL CONSTRAINT [DF_T_column26] DEFAULT
> (getdate()),
> [column27] [varchar] (2) COLLATE Slovenian_CI_AS NOT NULL CONSTRAINT
> [DF__T_column27] DEFAULT ('**'),
> [column28] [smalldatetime] NOT NULL CONSTRAINT [DF__T_column28] DEFAULT
> (getdate()),
> [column29] [varchar] (2) COLLATE Slovenian_CI_AS NOT NULL CONSTRAINT
> [DF__T_column29] DEFAULT ('**'),
> [column30] [smalldatetime] NOT NULL CONSTRAINT [DF_T_column30] DEFAULT
> (getdate()),
> [column31] [varchar] (20) COLLATE Slovenian_CI_AS NULL ,
> [column32] [Boolean] NOT NULL CONSTRAINT [DF_T_column32] DEFAULT (0),
> [column33] [float] NULL CONSTRAINT [DF_T_column33] DEFAULT (0),
> [column34] [timestamp] NULL ,
> [column35] [varbinary] (8) NULL ,
> [column36] [char] (2) COLLATE Slovenian_CI_AS NULL ,
> [column37] [float] NULL CONSTRAINT [DF_T_column37] DEFAULT (0),
> [column38] [float] NULL CONSTRAINT [DF_T_column38] DEFAULT (0),
> CONSTRAINT [PK_T_TABLE] PRIMARY KEY CLUSTERED
> (
> [column1],
> [column2]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> I just change column names.
> And the data file was exported directly from Enterprise manager. One line
> looks like:
> "_000000281",1156055,"I0121","product",-1,0,39960,0,0,-39960,0,0,0,0,0,0,"
AV",0,"",0,"",,"",,0,2003-01-23
> 20:50:00,"NS",2000-01-02 09:25:00,"99",2000-01-02
> 09:25:00,"#NS:2000000281",-1,0,000000018AC09D10,,"",,
> If I import this data without format file it works (not for all lines).
> If I include only good lines in other file and try with format file, I get
> the following error.
> Without format file, works.
> Regards,
> Simon
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:eU4cRioyFHA.2960@.tk2msftngp13.phx.gbl...
>

BULK IMPORT Stress

Here's the problem: The columns I am trying to import comprise ID (an
int identity column), Name (a varchar(255) column and Status (a small
int column). The data file contains identity values for the first
column, so I am using the KEEPIDENTITY modifier. The Status column is
mandatory, so I have set all rows in the data file to zero for that
column. All of the other columns in the destination table either allow
NULL or have default values. When I BULK INSERT the file using the
format file the identity columns are NOT imported and the Status column
gets value 3376. The Name column is the only one that gets imported
correctly. Here's the format file:

8.0
3
1 SQLINT 0 4 "\t" 1 ID
""
2 SQLCHAR 0 0 "\t" 2 Name
SQL_Latin1_General_CP1_CI_AS
3 SQLSMALLINT 0 2 "\n" 4 Status
""

Sorry it's a bit messy.

Where is 3376 coming from, and why are my identity values for column ID
not being imported?Davy B (david.bridge@.itv.com) writes:

Quote:

Originally Posted by

I am trying to import a data file, which is tab delimited, using BULK
INSERT. I have used BCP to create a format file, since the destination
table has around 20 columns, but the data file has only three.
>
Here's the problem: The columns I am trying to import comprise ID (an
int identity column), Name (a varchar(255) column and Status (a small
int column). The data file contains identity values for the first
column, so I am using the KEEPIDENTITY modifier. The Status column is
mandatory, so I have set all rows in the data file to zero for that
column. All of the other columns in the destination table either allow
NULL or have default values. When I BULK INSERT the file using the
format file the identity columns are NOT imported and the Status column
gets value 3376. The Name column is the only one that gets imported
correctly. Here's the format file:
>
8.0
3
1 SQLINT 0 4 "\t" 1 ID
""
2 SQLCHAR 0 0 "\t" 2 Name
SQL_Latin1_General_CP1_CI_AS
3 SQLSMALLINT 0 2 "\n" 4 Status
""

Is the file a text file? That format file is a for a binary file of some
sort. That is, the data type should be SQLCHAR, if it is a text file, as
that is the type for data in the file.

I would also recommend changing the lngths (4 and 2) to 0, and finally
changing \n to \r\n. (Unless the file comes from a Unix-type of system and
really has \n as the row terminator.)

Note: if the file is a Unicode file, the type should be SQLNCHAR and
the terminators should be "\t\0" and "\r\0\n\0" respectively.

The data file is a tab-delimited file created from an Excel "save as".
If I had been importing a unicode file I am sure the Name column would
have been messed up, but that's the only column that imported
correctly. I tried changing the SQLCHAR to SQLNCHAR, but that breaks.
I tried changing the "\t" delimiter to "\t\0" and that breaks too. I
also tried changing the sizes of cols 1 and 3 to 0 as you suggest, but
get exactly the same result.

I also tried using BCP to export data from a similar table, and the
result included wingdings-type characters for the numeric columns.

BUT SUCCESS!!!! I didn't notice in the BOL example, but the field
types for importing must all be SQLCHAR, since the numbers are, of
course, text. So why does BCP create format files that have the
database column type? When I changed both SQLINT and SQLSMALLINT to
SQLCHAR, and set the column widths to 0 as you suggested it all worked
perfectly.

Kind regards,

Davy B

Erland Sommarskog wrote:

Quote:

Originally Posted by

Davy B (david.bridge@.itv.com) writes:

Quote:

Originally Posted by

8.0
3
1 SQLINT 0 4 "\t" 1 ID
""
2 SQLCHAR 0 0 "\t" 2 Name
SQL_Latin1_General_CP1_CI_AS
3 SQLSMALLINT 0 2 "\n" 4 Status
""

>
Is the file a text file? That format file is a for a binary file of some
sort. That is, the data type should be SQLCHAR, if it is a text file, as
that is the type for data in the file.
>
I would also recommend changing the lngths (4 and 2) to 0, and finally
changing \n to \r\n. (Unless the file comes from a Unix-type of system and
really has \n as the row terminator.)
>
Note: if the file is a Unicode file, the type should be SQLNCHAR and
the terminators should be "\t\0" and "\r\0\n\0" respectively.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Davy B (david.bridge@.itv.com) writes:

Quote:

Originally Posted by

SQLNCHAR is what you should use if you had a Unicode file. Since you
did not specify that part, I had to cover it in case.

Quote:

Originally Posted by

So why does BCP create format files that have the database column
type?

Because it guessed that you wanted to create a native file? Did you tell it
that you had a text file?

Sunday, March 25, 2012

Bulk insert, skip rows with duplicate key error?

Thursday, March 22, 2012

BULK INSERT to a Temporary Table

BULK INSERT SYNTAX

Tuesday, March 20, 2012

BULK INSERT permissions for non-admin?

Sunday, March 11, 2012

BULK INSERT HELP!

Bulk Insert for non admins

Thursday, March 8, 2012

Bulk Insert for non admins

Bulk Insert for non admins

Bulk Insert fail for TempTable (bulkAdmi)

BULK INSERT datafile parameter

Wednesday, March 7, 2012

Bulk Insert and Decimal type

Saturday, February 25, 2012

BULK INSERT - escape " - .fmt

BULK INSERT - error column too long

Friday, February 24, 2012

bulk insert

BULK IMPORT Stress

build sql script

Blog Archive

About Me