bulk insert into a linked server fails

Hello,

I am trying to perform bulk insert operation on a linked server.

Here is the T-sql code for the same:

Declare @.dynamic_sql nvarchar(1000)
Declare @.file_name varchar(100)

set @.file_name = 'C:\mvam\calls\11182003.txt'

set @.dynamic_sql = 'bulk insert
[MUMBAI\AMIT_DATABASE]...cdr_repositroy from ' + '''' + @.file_name + '''' + ' with (FIELDTERMINATOR = ' + '''' + ',' + '''' + ', ROWTERMINATOR = ' + '''' + '\n' + '''' + ', FIRSTROW =3, DATAFILETYPE = ' + '''' + 'char' + '''' + ')'

execute sp_executesql @.dynamic_sql

On executing the above posted code I get the following error

Invalid object name 'MUMBAI\AMIT_DATABASE...cdr_repositroy'

Any suggestions would be helpful

ThanxAmit,
Unless you specifically configured your linked server to point to the appropriate database it defaults to the master DB.

To remove ambiguity you need to specify the full 4 part name
e.g

insert [DBDEVSERVER\DEVELOP].pubs.dbo.test
select 'a'

For your bulk insert specify the database name

set @.dynamic_sql = 'bulk insert
[MUMBAI\AMIT_DATABASE].databasename.ownername.cdr_repositroy from ' + '''' + @.file_name + '''' + ' with (FIELDTERMINATOR = ' + '''' + ',' + '''' + ', ROWTERMINATOR = ' + '''' + '\n' + '''' + ', FIRSTROW =3, DATAFILETYPE = ' + '''' + 'char' + '''' + ')'

let us know if you are still having problem|||I tried with the option you mentioned in your reply i.e. using the complete naming convention but of no use. I still get the same error.

Also, I checked if the Database is correct on linked server and its correct.

Anyways, thanx for the help.

Amit|||Does the link work with a select statement to the linked table

ie
Select * from servername.databasename.ownername.objectname.

Bulk Insert fails. Column is too long in the data file

Hi,

for testing purposes I'm inserting a flat file into a sql-server table using BULK INSERT unsig the following code:

BULK INSERT rsk_staging
FROM 'c:\temp\bulk\rsk.txt'
WITH (
FIELDTERMINATOR = '\n',
ROWTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
DATAFILETYPE = 'char',
BATCHSIZE = 100000,
ROWS_PER_BATCH = 1925604,
TABLOCK
)

I have two versions of "rsk.txt" one with 1.9mill rows and one with the first 2000 rows only. The files have one column only with 115 characters that I'll split in to several columns later using SUBSTRING. The one with 2000 rows fires in to the database with no problems whatsoever using this exact code, the other one throws the following error:

Server: 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.

How can I resolve this problem?

EDIT: I tried several different row- and fieldterminators but this exact one works for the small data-file so I assume it should also work for the large one...the large one is however copyed directly using binary ftp from a unix-filesystem and the small one is manually copied into a new txt-file using UltraEdit.

Problem solved...when I used ascii-ftp instead of binary everything worked well. 1.9 mill records from a flat file on a network share into my local db WITH substring conversion in 2 mins. No need to say I'm a happy camper at the moment

Bulk Insert fails to import data files created on Unix

It seems to me that files created on Unix machines with line terminator \n, or chr(10), cannot be imported using the Bulk Insert statement. Is this a bug, or an oversight by Microsoft? Does this mean that unless one replaces all \n with \r\n, there is no way to use Bulk Insert to import Unix files? This is a very strange behavior by MSSQL. Even lessor programs such as Excel and Word automatically recognize chr(10) as a line termination character. Am I missing something, or is this just the way MSSQL is?

You will need to use a format file, in this you can specify the terminator for the last column in a row.

Have a look in BOL. This page shows an example of a file using /r/n which you can obviously change

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ecfc546d-f708-45f4-878d-fb71b5fd1a0a.htm

|||

Of if you are using the BULK INSERT TSQL statement look at this page

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/be3984e1-5ab3-4226-a539-a9f58e1e01e2.htm

|||I figured out that the problem has to do with MSSQL's native behavior. It turns out that whenever it sees \n, it automatically converts it to \r\n, without notifiying the user. There are at least three ways to work around this strange behavior: 1) replace every \n in your file with \r\n before using Bulk Insert, 2) build your sql statement dynamically either in a stored procedure or VB.net, i.e., use & chr(10) & ,or + chr(10) +, instead of '\n' as the line terminator in your statement, and 3) load your file into a Datatable via ADO.net, and then insert the entire Datatable into MSSQL.|||

Note sure what figuring out was required, as BOL has an example that just works.

|||

I have never succeeded to convince BULK INSERT to read Unix

files, and I find one of these solutions usually works:

1. See if the process that moved the files from the Unix

machine can correct the line ends (for example, ftp can do

this)

2. Run the Unix utility unix2dos on the files before they

leave the Unix machine, or afterwards, under the Cygwin

Unix shell for Windows. (Or write a tiny command-line

Windows program to do this.)

Steve Kass

Drew University

ktto@.discussions.microsoft.com wrote:

> I figured out that the problem has to do with MSSQL's native behavior.

> It turns out that whenever it sees \n, it automatically converts it to

> \r\n, without notifiying the user. There are at least three ways to work

> around this strange behavior: 1) replace every \n in your file with \r\n

> before using Bulk Insert, 2) build your sql statement dynamically either

> in a stored procedure or VB.net, i.e., use & chr(10) & ,or + chr(10) +,

> instead of '\n' as the line terminator in your statement, and 3) load

> your file into a Datatable via ADO.net, and then insert the entire

> Datatable into MSSQL.

>

Bulk Insert fails to import data files created on Unix

It seems to me that files created on Unix machines with line terminator \n, or chr(10), cannot be imported using the Bulk Insert statement. Is this a bug, or an oversight by Microsoft? Does this mean that unless one replaces all \n with \r\n, there is no way to use Bulk Insert to import Unix files? This is a very strange behavior by MSSQL. Even lessor programs such as Excel and Word automatically recognize chr(10) as a line termination character. Am I missing something, or is this just the way MSSQL is?

You will need to use a format file, in this you can specify the terminator for the last column in a row.

Have a look in BOL. This page shows an example of a file using /r/n which you can obviously change

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ecfc546d-f708-45f4-878d-fb71b5fd1a0a.htm

|||

Of if you are using the BULK INSERT TSQL statement look at this page

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/be3984e1-5ab3-4226-a539-a9f58e1e01e2.htm

|||I figured out that the problem has to do with MSSQL's native behavior. It turns out that whenever it sees \n, it automatically converts it to \r\n, without notifiying the user. There are at least three ways to work around this strange behavior: 1) replace every \n in your file with \r\n before using Bulk Insert, 2) build your sql statement dynamically either in a stored procedure or VB.net, i.e., use & chr(10) & ,or + chr(10) +, instead of '\n' as the line terminator in your statement, and 3) load your file into a Datatable via ADO.net, and then insert the entire Datatable into MSSQL.|||

Note sure what figuring out was required, as BOL has an example that just works.

|||

I have never succeeded to convince BULK INSERT to read Unix

files, and I find one of these solutions usually works:

1. See if the process that moved the files from the Unix

machine can correct the line ends (for example, ftp can do

this)

2. Run the Unix utility unix2dos on the files before they

leave the Unix machine, or afterwards, under the Cygwin

Unix shell for Windows. (Or write a tiny command-line

Windows program to do this.)

Steve Kass

Drew University

ktto@.discussions.microsoft.com wrote:

> I figured out that the problem has to do with MSSQL's native behavior.

> It turns out that whenever it sees \n, it automatically converts it to

> \r\n, without notifiying the user. There are at least three ways to work

> around this strange behavior: 1) replace every \n in your file with \r\n

> before using Bulk Insert, 2) build your sql statement dynamically either

> in a stored procedure or VB.net, i.e., use & chr(10) & ,or + chr(10) +,

> instead of '\n' as the line terminator in your statement, and 3) load

> your file into a Datatable via ADO.net, and then insert the entire

> Datatable into MSSQL.

>

Bulk Insert Fails !

Hello

I am trying to execute a BULK INSERT ... this is my code
BULK INSERT myTableSQL.dbo.[Daily_ss] FROM 'c:\daily_ss.txt'
WITH (
DATAFILETYPE='native',
FIELDTERMINATOR = 'char(9)',
ROWTERMINATOR = '\n'
)

But I get an Error

Server: 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.
Server: 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.

1. The FIELDTERMINATOR in my file is the tab ...is it correct that i
declare it as char(9)?

2. the date format in the text file are dd/mm/yyyy will i have a
problem with this? Because SQL uses mm/dd/yyyy

3. I used this with DATAFILETYPE='char' and i get the same error

Please HELP :)
ThanksHi savvaschr,

I guess you are right at you 1st doubt as you have specified 'char(9)' and
not actual tab. So I guess you need to paste actual tab charecter inthe
quotes, also make sure that you have dropped indexes. I am a bit doubtful
about the 2nd # as it shouldn't be a problem.

Please let us know the solution.

Best regards,
Tushar.|||Will the data fit in the column?
Is your column varchar(50) and the data is greater than 50 characters?

http://sqlservercode.blogspot.com/|||I copy the 'tab' character from the file and i put it as FIELDSEPARETOR
but it still dont work and i 've tested it with another file and table
with no date fields.

So maybe is the tab character OR is the indexes of the table . Shall i
remove the indexes?
And if i remove them in order for the BULK INSERT to work and I have
to add them again whats the purpose of BULK INSERTing them instead of
adding row by row ?|||<savvaschr@.nodalsoft.com.cy> wrote:

> Hello
> I am trying to execute a BULK INSERT ... this is my code
> BULK INSERT myTableSQL.dbo.[Daily_ss] FROM 'c:\daily_ss.txt'
> WITH (
> DATAFILETYPE='native',
> FIELDTERMINATOR = 'char(9)',
> ROWTERMINATOR = '\n'
> )
> But I get an Error
> Server: 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.
> Server: 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.
> 1. The FIELDTERMINATOR in my file is the tab ...is it correct that i
> declare it as char(9)?
> 2. the date format in the text file are dd/mm/yyyy will i have a
> problem with this? Because SQL uses mm/dd/yyyy
> 3. I used this with DATAFILETYPE='char' and i get the same error
> Please HELP :)
> Thanks

I believe the command you want is...

BULK INSERT myTableSQL.dbo.[Daily_ss] FROM 'c:\daily_ss.txt'
WITH (
DATAFILETYPE='char',
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)

Craig|||i Use the above and i got an error

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 22. Make sure the field terminator and row terminator are
specified correctly.
Server: 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.

I am openign a unix file and read from it ...is there a chance that the
'\n' is not the RowTerminator ? What posible character except '\n'
might be?

Thanks a lot
Savvas|||(savvaschr@.nodalsoft.com.cy) writes:
> i Use the above and i got an error
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 22. Make sure the field terminator and row terminator are
> specified correctly.
> Server: 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.
>
> I am openign a unix file and read from it ...is there a chance that the
> '\n' is not the RowTerminator ? What posible character except '\n'
> might be?

\n as terminator has always been problematic, because \n in the format
specification is interpreted as \r\n. I can't recall that I have ever
been able to get it to work.

A work around is to open the file from Windows with an editor, and make
sure that when you save again that lines are terminated with \r\n.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||<savvaschr@.nodalsoft.com.cy> wrote:

>i Use the above and i got an error
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 22. Make sure the field terminator and row terminator are
> specified correctly.
> Server: 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.
>
> I am openign a unix file and read from it ...is there a chance that the
> '\n' is not the RowTerminator ? What posible character except '\n'
> might be?
> Thanks a lot
> Savvas

As Erland already pointed out, the most likely culprit is the fact that your
file is from a Unix server. This is definitely the problem if the records
in your file have 22 fields. As far as I can tell, you have 2 options:

1. Do as Erland suggested and "fix" the file before you import it... either
with a text editior or little utility program you write (please see below).

2. See if the code running on the Unix host can be changed to use \r\n
instead of \n.

Craig|||Ok Guys
I forced unix to use chr(13) at the ena of line and now its ok
but in date fields i am geting an error saying:

code page 737 doesnt exist

I have tried both dd/mm/yyyy and mm/dd/yyyy formats and i still take
the same error|||(savvaschr@.nodalsoft.com.cy) writes:
> Ok Guys
> I forced unix to use chr(13) at the ena of line and now its ok
> but in date fields i am geting an error saying:
> code page 737 doesnt exist
> I have tried both dd/mm/yyyy and mm/dd/yyyy formats and i still take
> the same error

For date formats, you are best of using YYYY-MM-DD (or YYYYMMDD). However
that message, which I have never seen, is something different, and not
related to date format.

Going back to your first post, I see that you have specified
DATAFILETYPE='native'. Native here means that the data is the binary
form of the SQL Server data types. So a datetime value, should be an
8-bit binary value. I don't think your Unix system produces that.

Try DATAFILETYPE='character' instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||<savvaschr@.nodalsoft.com.cy> wrote:

> Ok Guys
> I forced unix to use chr(13) at the ena of line and now its ok
> but in date fields i am geting an error saying:
> code page 737 doesnt exist
> I have tried both dd/mm/yyyy and mm/dd/yyyy formats and i still take
> the same error

It's terrible to admit this, but I've been in a shop that only does American
English for years, so my experience with code pages is limited. However, I
would suggest using the format yyyymmdd. I believe this is guaranteed to be
interpreted correctly by SQL Server regardless of locale or codepage.

Good luck,

Craig|||OK

I forced put ^m as line terminator at unix files and i put the date
field format as
yyyy-mm-dd and its working ok

Thanks a lot to everybody
Savvas

Bulk Insert fails

I am using SQL Server 2005 Management Studio Express.I am trying to insert records in bulk into "students" table.

Students:
uniqueid varchar(9) (primary Key)
lname varchar(35)
fname varchar(35)

My problem is when one record from the bulk record already exists in Students table all the bulk insert fails. I dont want that. I want rest of the records to be inserted smoothly with no error.

How can I do it....Is there any disadvantage if i do this?

You could bulk insert into a staging table and then do the logic to insert only non-duplicated keys. I rarely (if ever) insert directly into a "master table". Once data is in the staging table you can do a simple insert.

Insert into Students
Select uniqueid, lname, fname, ....
from staging_Students
where uniqueid not in (select uniqueid from Students)

Drop the staging table after you are done, or leave it there to determine why you are getting duplicated keys from the insert file...

|||

I am using staging table and inserting using not in condition...but i am trying to see if there is a way around...the reason is bc i think the performance of query will come down if i do again select....will the performance go down?students table will have 60000 records...

|||

You could also use Openrowset with the BULK option (see books online for details). You would have to define a format file to use for the BULK option.

Insert into Students
Select uniqueid, lname, fname, ....
From Openrowset(BULK bulk parameters) as A
Where uniqueid not in (select uniqueid from Students)

|||

Yes, the performance depends on the number of rows in the main table and the inserted rwos. But another option is to remove the primary key on the uniqueid column and create an unique index with the ignore_dup_key option ON. This will result in the duplicate rows from being ignored during BULK INSERT. This will eliminate the additional join using staging table approach. In any case, you should use the staging table if you have more complex data cleaning tasks to perform.

So do the following:

1. Drop primary key on uniqueid column

2. Add unique index like:

create unique index ix_uq_uniqueid on Students(uniqueid) with ignore_dup_key = on

3. Now, bulk insert as before and the duplicate rows will be ignored automatically.

|||

umachandar...students table can have 60000 records. At a time I can insert 300 records into that. This is my scenario...can you tell which approach is better to follow( using ignore_dup_key or use not in condition to check duplicates). Do I need staging table if i user ignore_dup_key option.? I am having clustered index on uniqueid column in students table rite now.

Scenario:

60 users will be using my application. each user can upload a .csv file with 300 records at maximum.

This is what i am doing rite now....when the user uploads a .csv file from front end the .net application will invoke sqlbulkcopy class built into .net 2.0 and does a bulk insert ot 300 records into staging table. After that i invoke a stored procedure which puts the data into 3 different tables. so my procedure will have 3 insert statements(with not in conditions). After successful insertion i delete the data from the staging table for that user. the number of users and records in students table can grow up further in furture...

or should I user the ignore_dup_key option and avoid the staging table?

Thanks...

bulk insert fails

I'm setting up a new 2005 server and bulk insert from a client workstation (using windows authentication) is failing with:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\FILESERVERNAME\sharedfolder\filename.txt" could not be opened. Operating system error code 5(Access is denied.).

Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it):

BULK INSERT #FIRSTROW FROM '\\FILESERVERNAME\sharedfolder\filename.txt'
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n',
LASTROW = 1
)

If I run the same transact SQL when remote desktopped into the new server (under the same login as that used in the client workstation), it imports the file without errors.

If I use the sa client login from the client workstation (sql server authentication) the bulk insert succeeds.

My old SQL 2000 server lets me bulk insert the file without errors even from my client workstations using windows authentication.

I have followed the instructions on this site: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1
, but still no luck and same error.

I'm pretty sure it is being caused by the increased constraints on bulk insert in 2005. Hoping someone can help. The more specific the better. If you need more info, let me know.

Oh and I've also made sure that the SQL service uses a domain logon account rather than the local system account (this would work on the 2000 server, but not 2005).

Note that the file server (source file resides there) is a DIFFERENT machine than the 2005 SQL server. If I move the source file to the sql server machine the error goes away (not a preferred solution though).

I'm almost positive that what I need to do is make sure that the SQL server is setup for delegation when a windows authenticated user attempts to bulk load a file from a second server.

Can someone provide instructions?

Thanks!

Can you access the file using thew full path \\FILESERVERNAME\sharedfolder\filename.txt ?

of course, with the user who execute the bulk insert.

Did you alread verify the share and NTFS permition?

|||Yes, I can access file. I have given both that user and the sql service user account full access.

Note that this user has no problem running the same bulk load from my old 2000 server.

I'm pretty sure that my user is not being delegated through to the file server.
Is there a way to catch the user information that BULK INSERT is using to access a file? A SQL trace doesn't catch it.

I'm not a windows system admin, so forgive my ignorance, but what's an ntfs permission? I thought NTFS was just the hard drive file system format.
.
Do you know how to setup delegation for users?

|||

When a told NTFS permission I,d mean the file system permission.

|||Yes, the user has full network and local permission to the file.
|||

Can you try to make lower the authentication level of NTLM protocol?

I saw this posts, try to verify your solution: http://forums.microsoft.com/msdn/showpost.aspx?postid=270868&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

bulk insert fails

I'm setting up a new 2005 server and bulk insert from a client workstation (using windows authentication) is failing with:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\FILESERVERNAME\sharedfolder\filename.txt" could not be opened. Operating system error code 5(Access is denied.).

Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it):

BULK INSERT #FIRSTROW FROM '\\FILESERVERNAME\sharedfolder\filename.txt'
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n',
LASTROW = 1
)

If I run the same transact SQL when remote desktopped into the new server (under the same login as that used in the client workstation), it imports the file without errors.

If I use the sa client login from the client workstation (sql server authentication) the bulk insert succeeds.

My old SQL 2000 server lets me bulk insert the file without errors even from my client workstations using windows authentication.

I have followed the instructions on this site: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1
, but still no luck and same error.

I'm pretty sure it is being caused by the increased constraints on bulk insert in 2005. Hoping someone can help. The more specific the better. If you need more info, let me know.

Oh and I've also made sure that the SQL service uses a domain logon account rather than the local system account (this would work on the 2000 server, but not 2005).

Note that the file server (source file resides there) is a DIFFERENT machine than the 2005 SQL server. If I move the source file to the sql server machine the error goes away (not a preferred solution though).

I'm almost positive that what I need to do is make sure that the SQL server is setup for delegation when a windows authenticated user attempts to bulk load a file from a second server.

Can someone provide instructions?

Thanks!

Can you access the file using thew full path \\FILESERVERNAME\sharedfolder\filename.txt ?

of course, with the user who execute the bulk insert.

Did you alread verify the share and NTFS permition?

|||Yes, I can access file. I have given both that user and the sql service user account full access.

Note that this user has no problem running the same bulk load from my old 2000 server.

I'm pretty sure that my user is not being delegated through to the file server.
Is there a way to catch the user information that BULK INSERT is using to access a file? A SQL trace doesn't catch it.

I'm not a windows system admin, so forgive my ignorance, but what's an ntfs permission? I thought NTFS was just the hard drive file system format.
.
Do you know how to setup delegation for users?

|||

When a told NTFS permission I,d mean the file system permission.

|||Yes, the user has full network and local permission to the file.
|||

Can you try to make lower the authentication level of NTLM protocol?

I saw this posts, try to verify your solution: http://forums.microsoft.com/msdn/showpost.aspx?postid=270868&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

bulk insert fails

Hi

I have a page that bulkinsert data to my sql server, I build up the bulk insert part like this...

1 sb.Append("Exec p_BulkInsertPDI'<ROOT><PROT>")23 sb.Append("<PDI NID=""" & HiddenField1.Value & """ AID=""" & HiddenField1a.Value & """ MID="" GID="" UID=""" & UserID & """/>")45 sb.Append("</PROT></ROOT>'")

The problem I have here is that sometimes the AID value doesn't have any value beacuse on the previous page haven't sent any value to that hiddenfield.
So when I try to run this, I get a error message like this... "Conversion failed when converting the nvarchar value 'AID=' to data type int".

It would be the best if I could insert Null values if no value have been provided. Is this possible to do?

Regards

You could use the IF and Only If , e.g

sb.append("...." & _

IIF(hiddenvalue.value = "", "NULL", hiddenvalue.value) & _

".........")

EDIT: Got the syntax slightly wrong - it has been corrected.

|||Ahhh.. This sounds very promising, I'll try it and get back...|||

Hi Again

I replaced it so it look like this ...

sb.Append("<PD NID=""" & HiddenField7.Value &""" AID=""" & HiddenField7a.Value &""" FID=""" & (IIf(HiddenField7b.Value ="","NULL", HiddenField7b.Value)) &""" GID=""" & (IIf(HiddenField7d.Value ="","NULL", HiddenField7d.Value)) &""" MID=""" & (IIf(HiddenField7c.Value ="","NULL", HiddenField7c.Value)) &""" PID=""" & PID &"""/>")sb.Append("<PD NID=""" & HiddenField12.Value &""" AID=""" & HiddenField12a.Value &""" FID=""NULL"" GID=""NULL"" MID=""NULL"" UID=""" & UserID &"""/>")

And this is my sp...
 
p_BulkInsertPDI (@.FormData ntext)ASDECLARE @.hDoc int exec sp_xml_preparedocument @.hDoc OUTPUT,@.FormData BEGINSET NOCOUNT ON;INSERT INTO tbl_Form_Answers(NodeID, AprovalID, FaultID, Grade, MeasureID, ProtocolID)SELECT * FROM OPENXML(@.hDoc,'ROOT/PROT/PD',1)WITH ( NIDInteger , AIDInteger , FIDInteger , GID integer, MID integer, PID integer ) XMLEmpEXEC sp_xml_removedocument @.hDocEND
 
 

But now I get this errror message.. "Conversion failed when converting the nvarchar value 'NULL' to data type int"

How can I get this to work? I really don't want to make x number of insertation to the db, I would prefer to just do one.

Regards

|||

Ahhh, I didn't see that one coming. First thing you can try is use the dbnull.value instead:

sb.append("...." (IIf(HiddenField7d.Value ="",DBNull.value, HiddenField7d.Value)) )

Not sure if this will work. Otherwise your best bet is to temporarily assign a number as null. e.g. use a value you know will never occur (such as -1). After the inserts, you can then update the database and change all the -1 to NULL. Not the best method, but it should work.:

sb.append("...." (IIf(HiddenField7d.Value ="",-1, HiddenField7d.Value)) )

 
p_BulkInsertPDI
(@.FormData ntext)
AS
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.FormData
BEGIN
SET NOCOUNT ON;
INSERT INTO tbl_Form_Answers(NodeID, AprovalID, FaultID, Grade, MeasureID, ProtocolID)
SELECT *
FROM OPENXML(@.hDoc,'ROOT/PROT/PD',1)
WITH ( NIDInteger , AIDInteger , FIDInteger , GID integer, MID integer, PID integer ) XMLEmp
UPDATE tbl_Form_Answers SET Grade = NULL WHERE Grade = -1
EXEC sp_xml_removedocument @.hDoc
END

|||

Hi

The DBNull.Value part didn't work so I ended up with your second proposal wich worked fine, I guess I have to live with the update part. It must be better than to make 20 database insert, which would have been the case if this wouldnt work.

Thanks for all your help

Best Regards

Bulk Insert Error

All,
I'm getting the following error when running a BULK INSERT via T-SQL:
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 3. Make sure the field terminator and row terminator are
specified correctly.
Server: 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.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
My table has the following structure:
CREATE TABLE [dbo].[TABLE1] (
[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [bigint] NULL ,
[TimeStamp] [datetime] NULL
) ON [PRIMARY]
GO
This is my T-SQL statement:
BULK INSERT [Database].[dbo].[TABLE1]
FROM 'C:\File.txt'
WITH
(
FORMATFILE='C:\format.fmt'
)
And this is my File Format:
8.0
3
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
Finally, my file is a fixed width format:
11 for the ID,
128 for the Email
33 for the TimeStamp
However, it keeps failing. Can anybody offer any insight to my issue?
Thanks,
Neal> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
The field length specification in the format file describes the field length
in the file, not the table column width. If you intention is to import only
the Email field and truncate, you can either add a dummy field to account
for the entire Email field length or increase the defined Timestamp field
length to 86:
8.0
4
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 53 "" 0 Email_Unused SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 33 "" 0 Timestamp SQL_Latin1_General_CP1_CI_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141158732.676526.308630@.t39g2000cwt.googlegroups.com...
> All,
> I'm getting the following error when running a BULK INSERT via T-SQL:
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 3. Make sure the field terminator and row terminator are
> specified correctly.
> Server: 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.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
> returned 0x80004005: The provider did not give any information about
> the error.].
> The statement has been terminated.
> My table has the following structure:
> CREATE TABLE [dbo].[TABLE1] (
> [Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [bigint] NULL ,
> [TimeStamp] [datetime] NULL
> ) ON [PRIMARY]
> GO
> This is my T-SQL statement:
> BULK INSERT [Database].[dbo].[TABLE1]
> FROM 'C:\File.txt'
> WITH
> (
> FORMATFILE='C:\format.fmt'
> )
> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
> However, it keeps failing. Can anybody offer any insight to my issue?
> Thanks,
> Neal
>|||Dan,
I appreciate your help. You suggestion worked for me. Just curious,
why do I not need a record terminator "\r\n" on my last column?
Thanks again for your help.
Neal|||With a format file, the row terminator is specified after the last field of
the file. This is normally a carriage return/line feed ('\r\n') for text
files created via Windows applications.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141230410.344952.47950@.t39g2000cwt.googlegroups.com...
> Dan,
> I appreciate your help. You suggestion worked for me. Just curious,
> why do I not need a record terminator "\r\n" on my last column?
> Thanks again for your help.
> Neal
>

Bulk Insert Error

All,
I'm getting the following error when running a BULK INSERT via T-SQL:
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 3. Make sure the field terminator and row terminator are
specified correctly.
Server: 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.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
My table has the following structure:
CREATE TABLE [dbo].[TABLE1] (
[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [bigint] NULL ,
[TimeStamp] [datetime] NULL
) ON [PRIMARY]
GO
This is my T-SQL statement:
BULK INSERT [Database].[dbo].[TABLE1]
FROM 'C:\File.txt'
WITH
(
FORMATFILE='C:\format.fmt'
)
And this is my File Format:
8.0
3
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_A
S
Finally, my file is a fixed width format:
11 for the ID,
128 for the Email
33 for the TimeStamp
However, it keeps failing. Can anybody offer any insight to my issue?
Thanks,
Neal> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
The field length specification in the format file describes the field length
in the file, not the table column width. If you intention is to import only
the Email field and truncate, you can either add a dummy field to account
for the entire Email field length or increase the defined Timestamp field
length to 86:
8.0
4
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 53 "" 0 Email_Unused SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 33 "" 0 Timestamp SQL_Latin1_General_CP1_CI_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141158732.676526.308630@.t39g2000cwt.googlegroups.com...
> All,
> I'm getting the following error when running a BULK INSERT via T-SQL:
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 3. Make sure the field terminator and row terminator are
> specified correctly.
> Server: 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.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
> returned 0x80004005: The provider did not give any information about
> the error.].
> The statement has been terminated.
> My table has the following structure:
> CREATE TABLE [dbo].[TABLE1] (
> [Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [bigint] NULL ,
> [TimeStamp] [datetime] NULL
> ) ON [PRIMARY]
> GO
> This is my T-SQL statement:
> BULK INSERT [Database].[dbo].[TABLE1]
> FROM 'C:\File.txt'
> WITH
> (
> FORMATFILE='C:\format.fmt'
> )
> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
> However, it keeps failing. Can anybody offer any insight to my issue?
> Thanks,
> Neal
>|||Dan,
I appreciate your help. You suggestion worked for me. Just curious,
why do I not need a record terminator "\r\n" on my last column?
Thanks again for your help.
Neal|||With a format file, the row terminator is specified after the last field of
the file. This is normally a carriage return/line feed ('\r\n') for text
files created via Windows applications.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141230410.344952.47950@.t39g2000cwt.googlegroups.com...
> Dan,
> I appreciate your help. You suggestion worked for me. Just curious,
> why do I not need a record terminator "\r\n" on my last column?
> Thanks again for your help.
> Neal
>

Bulk Insert Error

All,
I'm getting the following error when running a BULK INSERT via T-SQL:
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 3. Make sure the field terminator and row terminator are
specified correctly.
Server: 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.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
My table has the following structure:
CREATE TABLE [dbo].[TABLE1] (
[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [bigint] NULL ,
[TimeStamp] [datetime] NULL
) ON [PRIMARY]
GO
This is my T-SQL statement:
BULK INSERT [Database].[dbo].[TABLE1]
FROM 'C:\File.txt'
WITH
(
FORMATFILE='C:\format.fmt'
)
And this is my File Format:
8.0
3
1SQLCHAR011""0IDSQL_Latin1_General_CP1_CI_AS
2SQLCHAR075""1EmailSQL_Latin1_General_CP1_CI_AS
3SQLCHAR08"\r\n"0TimeStampSQL_Latin1_General_CP1_CI_AS
Finally, my file is a fixed width format:
11 for the ID,
128 for the Email
33 for the TimeStamp
However, it keeps failing. Can anybody offer any insight to my issue?
Thanks,
Neal
> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
The field length specification in the format file describes the field length
in the file, not the table column width. If you intention is to import only
the Email field and truncate, you can either add a dummy field to account
for the entire Email field length or increase the defined Timestamp field
length to 86:
8.0
4
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 53 "" 0 Email_Unused SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 33 "" 0 Timestamp SQL_Latin1_General_CP1_CI_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141158732.676526.308630@.t39g2000cwt.googlegr oups.com...
> All,
> I'm getting the following error when running a BULK INSERT via T-SQL:
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 3. Make sure the field terminator and row terminator are
> specified correctly.
> Server: 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.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
> returned 0x80004005: The provider did not give any information about
> the error.].
> The statement has been terminated.
> My table has the following structure:
> CREATE TABLE [dbo].[TABLE1] (
> [Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [bigint] NULL ,
> [TimeStamp] [datetime] NULL
> ) ON [PRIMARY]
> GO
> This is my T-SQL statement:
> BULK INSERT [Database].[dbo].[TABLE1]
> FROM 'C:\File.txt'
> WITH
> (
> FORMATFILE='C:\format.fmt'
> )
> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
> However, it keeps failing. Can anybody offer any insight to my issue?
> Thanks,
> Neal
>
|||Dan,
I appreciate your help. You suggestion worked for me. Just curious,
why do I not need a record terminator "\r\n" on my last column?
Thanks again for your help.
Neal
|||With a format file, the row terminator is specified after the last field of
the file. This is normally a carriage return/line feed ('\r\n') for text
files created via Windows applications.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141230410.344952.47950@.t39g2000cwt.googlegro ups.com...
> Dan,
> I appreciate your help. You suggestion worked for me. Just curious,
> why do I not need a record terminator "\r\n" on my last column?
> Thanks again for your help.
> Neal
>

BULK INSERT crashes Stored Procedure ?

I'm trying to BULK INSERT from a series of files, but the files may or may
not exist.
Problem is that if a BULK INSERT command fails (because the file doesn't
exist), the next line of code in the SP is never reached!
>> Server: Msg 4860, Level 16, State 1, Line 1
>> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
>> CODE:
>> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
>> + @.path + @.fn + ''''
>> + ' WITH
>> (
>> FIELDTERMINATOR = ''|''
>> ,ROWTERMINATOR = ''\n''
>> ,TABLOCK
>> )
>> '
>> EXEC( @.cmd ) ; -- BOMBS
>> -- EXEC sp_executesql @.cmd ; -- BOMBS
Naturally I've tried setting XACT_ABORT OFF (among other various
superstitious rituals). Nothing works.
TIA,
--joe
PS,
I don't have control of this server & the current admin & users aren't very
technical... As such I want to avoid using xp_cmdshell... similarly, I don't
want to use a linked text server, and DTS is a problem b/c the file names
change along with the aforementioned administrative issues. I would like to
have something that works stand alone that can run w/out intervention and a
minimum of admin.I googled around for this problem, and it appears that "It's supposed to
work that way" is the answer. Is this the only answer, that this class of
error throws a "fatal" & immediately short-circuits further execution of the
remainder of the SP?
This has become a show stopper at the moment.
I'm trying to run through a directory of ASCII files, so having a fatal on
just one file is a poor option when there are 20 files to import. I'd use
xp_cmdshell, but its permissions seem to get turned off whenever a patch is
applied, and that doesn't guard me against a bad 0-byte ASCII file (no
CR/LF).
Is there perhaps a way to set up DTS so it handles dynamic file names when
importing from ASCII? Any suggestions?
Thanx,
-joe
PS, Just to reiterate from my previous post, I thought of using a linked
OLEDB text server, but that requires the right OLEDB driver and permissions,
along with a capable DBA watching over things (plus if the file doesn't
exist, will the OLEDB driver throw the same kind of fatal?). Using bcp in
DOS from a scheduler doesn't solve the dynamic file name problem (oh yeh,
try programming DOS batch files... it can be done, BUT!!!), and implementing
client software (perl, WSE, etc.) on the server or workstation creates
complexity & other headaches. Please, oh dear God, Why ME!!!!' Tell them
to go back to the @.#$ mainframe, hell, they were better off with 3x5
cards!!!!!!
Previous n.g. thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=28fd4c29145420ba&seekm=OQmAG3VcBHA.1848%40tkmsftngp05#link12
thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3BF9B2DC.65712B51%40drew.edu&rnum=1&prev=/groups%3Fq%3Dhandling%2Berrors%2BCould%2Bnot%2Bbulk%2Binsert.%2BFile%2Bgroup:microsoft.public.sqlserver.programming%2Bgroup:microsoft.public.sqlserver.programming%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dmicrosoft.public.sqlserver.programming%26selm%3D3BF9B2DC.65712B51%2540drew.edu%26rnum%3D1
--
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:W4xsb.34667$Mc.21141@.twister.austin.rr.com...
> I'm trying to BULK INSERT from a series of files, but the files may or may
> not exist.
> Problem is that if a BULK INSERT command fails (because the file doesn't
> exist), the next line of code in the SP is never reached!
> >> Server: Msg 4860, Level 16, State 1, Line 1
> >> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
> >> CODE:
> >> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
> >> + @.path + @.fn + ''''
> >> + ' WITH
> >> (
> >> FIELDTERMINATOR = ''|''
> >> ,ROWTERMINATOR = ''\n''
> >> ,TABLOCK
> >> )
> >> '
> >> EXEC( @.cmd ) ; -- BOMBS
> >> -- EXEC sp_executesql @.cmd ; -- BOMBS
>
> Naturally I've tried setting XACT_ABORT OFF (among other various
> superstitious rituals). Nothing works.
>
> TIA,
> --joe
> PS,
> I don't have control of this server & the current admin & users aren't
very
> technical... As such I want to avoid using xp_cmdshell... similarly, I
don't
> want to use a linked text server, and DTS is a problem b/c the file names
> change along with the aforementioned administrative issues. I would like
to
> have something that works stand alone that can run w/out intervention and
a
> minimum of admin.
>
>|||I just came up with a ghastly hack for a solution... a tad dangerous
perhaps...
In the SP, I log the file in a log table before I run the BULK INSERT. The
SP checks in the log table see if an attempt has been made on that file name
before, and will loop if an attempt has already been made.
I then set up a 2-step job on the server. Both steps call the same SP, and
step 1 calls step 2 on failure, and step 2 calls step 1 on failure. Until
the SP runs out of possible file names (filename is based on date in a
xxxxmmddyy.txt format), it'll keep BULK INSERTing files it finds and failing
on ones it doesn't (the SP succeeds when it can exit without error, when it
runs out of names, the maximum of which is based upon today's date).
Finally, both job steps (whichever one happens to be running at the time)
exit the job on success (which is again, running out of file names to look
for).
As I said, a ghastly hack.
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:W4xsb.34667$Mc.21141@.twister.austin.rr.com...
> I'm trying to BULK INSERT from a series of files, but the files may or may
> not exist.
> Problem is that if a BULK INSERT command fails (because the file doesn't
> exist), the next line of code in the SP is never reached!
> >> Server: Msg 4860, Level 16, State 1, Line 1
> >> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
> >> CODE:
> >> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
> >> + @.path + @.fn + ''''
> >> + ' WITH
> >> (
> >> FIELDTERMINATOR = ''|''
> >> ,ROWTERMINATOR = ''\n''
> >> ,TABLOCK
> >> )
> >> '
> >> EXEC( @.cmd ) ; -- BOMBS
> >> -- EXEC sp_executesql @.cmd ; -- BOMBS
>
> Naturally I've tried setting XACT_ABORT OFF (among other various
> superstitious rituals). Nothing works.
>
> TIA,
> --joe
> PS,
> I don't have control of this server & the current admin & users aren't
very
> technical... As such I want to avoid using xp_cmdshell... similarly, I
don't
> want to use a linked text server, and DTS is a problem b/c the file names
> change along with the aforementioned administrative issues. I would like
to
> have something that works stand alone that can run w/out intervention and
a
> minimum of admin.
>
>|||Before you start using Bulk Insert, can you confirm if the file really exists , you might want to use the command "xp_fileexist"|||"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> Before you start using Bulk Insert, can you confirm if the file really
exists , you might want to use the command "xp_fileexist"
No such xp_ on their machine, although xp_cmdshell is there, and worked at
one point in time. Plus, the admin situation is problematic such that xp_*
priveleges are revoked when service packs get applied.
This has to work w/out any special priveleges or assignments, just a job
that works reliably (nightly) w/out user intervention.
Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so even
if I knew the file existed, it doesn't protect the batch process from
interruption.
I *did* find a workaround to this problem. See my last reply to myself in
the thread. It's too bad that MS never got around to fixing these issues
(poor T-SQL error handling) until Longhorn.
Thanks,|||just a fyi, enhanced error handling has nothing to do with longhorn. it's to
do with yukon.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:HlFsb.36901$Mc.28999@.twister.austin.rr.com...
> "Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
> news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> > Before you start using Bulk Insert, can you confirm if the file really
> exists , you might want to use the command "xp_fileexist"
> No such xp_ on their machine, although xp_cmdshell is there, and worked at
> one point in time. Plus, the admin situation is problematic such that xp_*
> priveleges are revoked when service packs get applied.
> This has to work w/out any special priveleges or assignments, just a job
> that works reliably (nightly) w/out user intervention.
> Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
> BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so
even
> if I knew the file existed, it doesn't protect the batch process from
> interruption.
> I *did* find a workaround to this problem. See my last reply to myself in
> the thread. It's too bad that MS never got around to fixing these issues
> (poor T-SQL error handling) until Longhorn.
> Thanks,
>
>|||ahhhh, you are my oj in the mourning... and yukon correct me any time you
want when i get the code names for MS products mixed up
--
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"oj" <nospam_ojngo@.home.com> wrote in message
news:e3vmQWbqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> just a fyi, enhanced error handling has nothing to do with longhorn. it's
to
> do with yukon.
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
> news:HlFsb.36901$Mc.28999@.twister.austin.rr.com...
> > "Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
> > news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> > > Before you start using Bulk Insert, can you confirm if the file really
> > exists , you might want to use the command "xp_fileexist"
> >
> > No such xp_ on their machine, although xp_cmdshell is there, and worked
at
> > one point in time. Plus, the admin situation is problematic such that
xp_*
> > priveleges are revoked when service packs get applied.
> >
> > This has to work w/out any special priveleges or assignments, just a job
> > that works reliably (nightly) w/out user intervention.
> >
> > Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
> > BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so
> even
> > if I knew the file existed, it doesn't protect the batch process from
> > interruption.
> >
> > I *did* find a workaround to this problem. See my last reply to myself
in
> > the thread. It's too bad that MS never got around to fixing these issues
> > (poor T-SQL error handling) until Longhorn.
> >
> > Thanks,
> >
> >
> >
>|||The SP "xp_fileexist" exists in Master db, not in the User DB.|||Thanks, I gather that it's new in SS2K?
It's not listed in the SS2K BOL in the T-SQL ref. Doesn't require additional
rights, great!
It still doesn't solve the problem with any other kind of fatal error in
BULK INSERT (a 0-byte file, for instance).
The bottom line is that MS SQL 7's & 8's error handling isn't very capable
and I'll still have to either go w/ a hacked workaround in the job manager
or a workaround in client software (Perl, WSE...).
What really makes me wonder is, why in the first place was BULK INSERT
implemented to throw fatal errors *at all?* It's an import utility, that's
all it is - it's not a table reference that is the subject of
latent/deferred binding. Makes me wonder about the underpinning API, the
(familiar) scent of old Sybase code lurking under the hood.
"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:8AD1A570-60F4-48CF-B368-73369419CC27@.microsoft.com...
> The SP "xp_fileexist" exists in Master db, not in the User DB.|||Just to let you know you're not alone,
I posted this a few hours before you.
http://communities.microsoft.com/newsgroups/previewFrame.asp?ICP=msdn&sLCID=
us&sgroupURL=microsoft.public.sqlserver.programming&sMessageID=%253CuaBAWQSq
DHA.1884@.TK2MSFTNGP09.phx.gbl%253E.
At the moment I'm using this technic: in the sp that needs to bulk insert I
assign the text of the format file to a variable and create the file on the
fly (using a wrapper to the file system object), something like this:
/*********/
declare @.txt varchar(8000)
declare @.FormatFilePath varchar(255)
declare @.path varchar(255)
set @.txt='8.0
1
1 SQLCHAR 0 0 "\r\n" 2 Code ""
'
select @.path='Target.dat', @.FormatFilePath='c\winnt\temp\Target.fmt'
exec @.Res=dbo.st_WriteToFile @.FormatFilePath, @.txt --This is a FSO wrapper
that creates or overwrites a file with the specified text
if @.Res<>0
goto ErrorHandler
create table Target(
PKCheck int Identity(1,1),
Code char(15)
)
exec(N'bulk insert Target from '''
+ @.path
+ N''' with (FORMATFILE='''
+ @.TempDirPath
+ N''', TABLOCK)')
/**********/
It works fine, but of course if the data file or the table change their
structures you have to modify the sp, and you can't use third parties format
file without checking for their correctness.
I' was trying to generalize this approch by adding error controls when I
stumbled into your same problem.
Now I'm thinking of using BCP's format option via xp_cmdshell to produce a
valid format file, but of course I have to take into account the cases when
the number of table fields and file fields and their mutual positions
differ, but the problem of using format files not created by you remains.
Still working on.
Salvor
--
++++++++++++++
To e-mail me,
remove ".NO_SPAM" from my e-mail address

BULK Insert

Hi,
I have a stored prod that's doing a bunch of BULK INSERTs. The
problem I'm having is if one the BULK INSERTs fails, then the rest of
the stored proc isn't executed. I'd like it to proceed to the end and
then I can take care of the failed section.
I don't want any transactions here. Tried using SET XACT_ABORT OFF
but didn't help.
Anybody know how to do this?
Cheers
SudheshA suggestion...break it up into pieces via a DTS package. Pieces can be
part of a transaction or not.
HTH
Jerry
"Sudhesh" <Sudhesh@.mail.com> wrote in message
news:1147986812.212738.78350@.j73g2000cwa.googlegroups.com...
> Hi,
> I have a stored prod that's doing a bunch of BULK INSERTs. The
> problem I'm having is if one the BULK INSERTs fails, then the rest of
> the stored proc isn't executed. I'd like it to proceed to the end and
> then I can take care of the failed section.
> I don't want any transactions here. Tried using SET XACT_ABORT OFF
> but didn't help.
> Anybody know how to do this?
> Cheers
> Sudhesh
>|||We'll I have over 200 tables, and I'm really scripting the code for the
SP. So breaking it up isn't really an option.
What happened to all the GURU's of SQL?
Sudhesh

Bulk Import Diacritics Issue

Hi There,
When I do a bulk import from a DTS Package into a table the import fails at
record that contains diacritic characters like C, Í and E. The field type is
nvarchar.
Is it a collaction type I need to assign the field?
Do you have any idea how I can bypass this?
Thanks,
Kevin.Hi Kevin
"Kevin Humphreys" wrote:
> Hi There,
> When I do a bulk import from a DTS Package into a table the import fails at
> record that contains diacritic characters like C, Ã? and E. The field type is
> nvarchar.
> Is it a collaction type I need to assign the field?
> Do you have any idea how I can bypass this?
> Thanks,
> Kevin.
>
This seems that you need to change the codepage (-C) or specify a unicode
(-w or -N) parameters for BCP. Check in books online for the values.
John

Bulk Import Diacritics Issue

Hi There,
When I do a bulk import from a DTS Package into a table the import fails at
record that contains diacritic characters like C, and E. The field type is
nvarchar.
Is it a collaction type I need to assign the field?
Do you have any idea how I can bypass this?
Thanks,
Kevin.Hi Kevin
"Kevin Humphreys" wrote:

> Hi There,
> When I do a bulk import from a DTS Package into a table the import fails a
t
> record that contains diacritic characters like C, í and E. The field type
is
> nvarchar.
> Is it a collaction type I need to assign the field?
> Do you have any idea how I can bypass this?
> Thanks,
> Kevin.
>
This seems that you need to change the codepage (-C) or specify a unicode
(-w or -N) parameters for BCP. Check in books online for the values.
John

BUILTIN\administrators translation

Hi,
I have a line of code in an application
"sp_revokelogin [BUILTIN\Administrators]"
which fails on installations on German XP but works OK for English.
Is there a translation for 'BUILTIN'? I have tried
[BUILTIN\Administratoren] but that doesn't help - always get the message
" Windows NT user or group 'BUILTIN\Administratoren' not
found. Check the name again"
Hope someone with a German server can help!
(I also need the corresponding names for French, Spanish etc.)BUILTIN\Administrators has a "well known" SID so you could use this
declare @.builtin nvarchar(128)
select @.builtin = suser_sname(0x01020000000000052000000020
020000)
exec sp_revokelogin @.builtin
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"quilkin" <quilkin@.discussions.microsoft.com> wrote in message
news:72599A0C-028D-47A1-B63E-120EDC4C1065@.microsoft.com...
> Hi,
> I have a line of code in an application
> "sp_revokelogin [BUILTIN\Administrators]"
> which fails on installations on German XP but works OK for English.
> Is there a translation for 'BUILTIN'? I have tried
> [BUILTIN\Administratoren] but that doesn't help - always get the messa
ge
> " Windows NT user or group 'BUILTIN\Administratoren' not
> found. Check the name again"
> Hope someone with a German server can help!
> (I also need the corresponding names for French, Spanish etc.)
>|||Jasper,
That works fine, thanks.
I knew there'd be a simple answer.
"Jasper Smith" wrote:

> BUILTIN\Administrators has a "well known" SID so you could use this
> declare @.builtin nvarchar(128)
> select @.builtin = suser_sname(0x01020000000000052000000020
020000)
> exec sp_revokelogin @.builtin
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "quilkin" <quilkin@.discussions.microsoft.com> wrote in message
> news:72599A0C-028D-47A1-B63E-120EDC4C1065@.microsoft.com...
>
>

BUILTIN\administrators translation

Hi,
I have a line of code in an application
"sp_revokelogin [BUILTIN\Administrators]"
which fails on installations on German XP but works OK for English.
Is there a translation for 'BUILTIN'? I have tried
[BUILTIN\Administratoren] but that doesn't help - always get the message
" Windows NT user or group 'BUILTIN\Administratoren' not
found. Check the name again"
Hope someone with a German server can help!
(I also need the corresponding names for French, Spanish etc.)
BUILTIN\Administrators has a "well known" SID so you could use this
declare @.builtin nvarchar(128)
select @.builtin = suser_sname(0x01020000000000052000000020020000)
exec sp_revokelogin @.builtin
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"quilkin" <quilkin@.discussions.microsoft.com> wrote in message
news:72599A0C-028D-47A1-B63E-120EDC4C1065@.microsoft.com...
> Hi,
> I have a line of code in an application
> "sp_revokelogin [BUILTIN\Administrators]"
> which fails on installations on German XP but works OK for English.
> Is there a translation for 'BUILTIN'? I have tried
> [BUILTIN\Administratoren] but that doesn't help - always get the message
> " Windows NT user or group 'BUILTIN\Administratoren' not
> found. Check the name again"
> Hope someone with a German server can help!
> (I also need the corresponding names for French, Spanish etc.)
>
|||Jasper,
That works fine, thanks.
I knew there'd be a simple answer.
"Jasper Smith" wrote:

> BUILTIN\Administrators has a "well known" SID so you could use this
> declare @.builtin nvarchar(128)
> select @.builtin = suser_sname(0x01020000000000052000000020020000)
> exec sp_revokelogin @.builtin
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "quilkin" <quilkin@.discussions.microsoft.com> wrote in message
> news:72599A0C-028D-47A1-B63E-120EDC4C1065@.microsoft.com...
>
>

BUILTIN\administrators translation

Hi,
I have a line of code in an application
"sp_revokelogin [BUILTIN\Administrators]"
which fails on installations on German XP but works OK for English.
Is there a translation for 'BUILTIN'? I have tried
[BUILTIN\Administratoren] but that doesn't help - always get the message
" Windows NT user or group 'BUILTIN\Administratoren' not
found. Check the name again"
Hope someone with a German server can help!
(I also need the corresponding names for French, Spanish etc.)BUILTIN\Administrators has a "well known" SID so you could use this
declare @.builtin nvarchar(128)
select @.builtin = suser_sname(0x01020000000000052000000020020000)
exec sp_revokelogin @.builtin
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"quilkin" <quilkin@.discussions.microsoft.com> wrote in message
news:72599A0C-028D-47A1-B63E-120EDC4C1065@.microsoft.com...
> Hi,
> I have a line of code in an application
> "sp_revokelogin [BUILTIN\Administrators]"
> which fails on installations on German XP but works OK for English.
> Is there a translation for 'BUILTIN'? I have tried
> [BUILTIN\Administratoren] but that doesn't help - always get the message
> " Windows NT user or group 'BUILTIN\Administratoren' not
> found. Check the name again"
> Hope someone with a German server can help!
> (I also need the corresponding names for French, Spanish etc.)
>|||Jasper,
That works fine, thanks.
I knew there'd be a simple answer.
"Jasper Smith" wrote:
> BUILTIN\Administrators has a "well known" SID so you could use this
> declare @.builtin nvarchar(128)
> select @.builtin = suser_sname(0x01020000000000052000000020020000)
> exec sp_revokelogin @.builtin
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "quilkin" <quilkin@.discussions.microsoft.com> wrote in message
> news:72599A0C-028D-47A1-B63E-120EDC4C1065@.microsoft.com...
> > Hi,
> > I have a line of code in an application
> > "sp_revokelogin [BUILTIN\Administrators]"
> > which fails on installations on German XP but works OK for English.
> > Is there a translation for 'BUILTIN'? I have tried
> > [BUILTIN\Administratoren] but that doesn't help - always get the message
> > " Windows NT user or group 'BUILTIN\Administratoren' not
> > found. Check the name again"
> > Hope someone with a German server can help!
> > (I also need the corresponding names for French, Spanish etc.)
> >
>
>

Build fails but no errors are returned

hi,

I moved my ssis solution from on dev machine to another.

When building the solution, visual studio keeps saying that the build failed but no errors are returned which is not really helpful...

any clues would be appreciated.

thanks

I don't believe you need to "build" anything.|||

well, the solution is built before starting the debugger process...

anyway, I've sorted my problem... errors where not displayed in the error list but I've found one returned in the output window.

It was complaining that it could not load the project files (note that I had no errors while loading the solution in VS).

I've deleted the .suo and other user files from the solution and it now works... did not realise that project file paths informations could be in the user files... I was expecting this information to be specific to solution and project files ...

|||I set my projects to never build under Tools-Options. Never had a problem.