bulk load from a stream

The docs for sqlxml bulk loader indicate that the execute method
supports a stream for the document to load.
This code works:
xslTransform.Load("map.xsl")
textWriter = New XmlTextWriter("out.xml", Nothing)
xslTransform.Transform(xmlRsp, Nothing, textWriter)
textWriter.Flush()
textWriter.Close()
With xmlBulkLoader
.ConnectionString = "provider=SQLOLEDB;..."
.ErrorLogFile = "bulkLoaderErrors.txt"
.KeepIdentity = False
.XMLFragment = True
.Execute("schema.xml", "out.xml")
End With
When the transform is sent into a memory stream, the stream fills as
expected
dim memStream as New System.IO.MemoryStream
xslTransform.Transform(xmlRsp, Nothing, memStream)
but I can't figure out how to use memStream in
xmlBulkLoader.Execute("schema.xml", memStream)
I've tried using various properties of the memStream, and tried using a
stream reader, but the execute always returns "Error opening data file."
Check out the article here:
http://msdn.microsoft.com/library/de...exchsqlxml.asp
It shows how to do this in .NET
Irwin
<jmeerdink@.synergy.gs> wrote in message
news:1110571753.372547.264070@.f14g2000cwb.googlegr oups.com...
> The docs for sqlxml bulk loader indicate that the execute method
> supports a stream for the document to load.
> This code works:
> xslTransform.Load("map.xsl")
> textWriter = New XmlTextWriter("out.xml", Nothing)
> xslTransform.Transform(xmlRsp, Nothing, textWriter)
> textWriter.Flush()
> textWriter.Close()
> With xmlBulkLoader
> .ConnectionString = "provider=SQLOLEDB;..."
> .ErrorLogFile = "bulkLoaderErrors.txt"
> .KeepIdentity = False
> .XMLFragment = True
> .Execute("schema.xml", "out.xml")
> End With
> When the transform is sent into a memory stream, the stream fills as
> expected
> dim memStream as New System.IO.MemoryStream
> xslTransform.Transform(xmlRsp, Nothing, memStream)
> but I can't figure out how to use memStream in
> xmlBulkLoader.Execute("schema.xml", memStream)
> I've tried using various properties of the memStream, and tried using a
> stream reader, but the execute always returns "Error opening data file."
>
|||Thanks Irwin - wow that's a lot of code. The article mentioned that
ADODB.Stream is directly supported. Would that easier to work with?
|||That's in native code, looked like you were doing managed. If you were
doing native code it might be.
"jayMeer" <jmeerdink@.synergy.gs> wrote in message
news:1112792519.498993.310540@.z14g2000cwz.googlegr oups.com...
> Thanks Irwin - wow that's a lot of code. The article mentioned that
> ADODB.Stream is directly supported. Would that easier to work with?
>

bulk load from a stream

The docs for sqlxml bulk loader indicate that the execute method
supports a stream for the document to load.
This code works:
xslTransform.Load("map.xsl")
textWriter = New XmlTextWriter("out.xml", Nothing)
xslTransform.Transform(xmlRsp, Nothing, textWriter)
textWriter.Flush()
textWriter.Close()
With xmlBulkLoader
.ConnectionString = "provider=SQLOLEDB;..."
.ErrorLogFile = "bulkLoaderErrors.txt"
.KeepIdentity = False
.XMLFragment = True
.Execute("schema.xml", "out.xml")
End With
When the transform is sent into a memory stream, the stream fills as
expected
dim memStream as New System.IO.MemoryStream
xslTransform.Transform(xmlRsp, Nothing, memStream)
but I can't figure out how to use memStream in
xmlBulkLoader.Execute("schema.xml", memStream)
I've tried using various properties of the memStream, and tried using a
stream reader, but the execute always returns "Error opening data file."Check out the article here:
http://msdn.microsoft.com/library/d... />
sqlxml.asp
It shows how to do this in .NET
Irwin
<jmeerdink@.synergy.gs> wrote in message
news:1110571753.372547.264070@.f14g2000cwb.googlegroups.com...
> The docs for sqlxml bulk loader indicate that the execute method
> supports a stream for the document to load.
> This code works:
> xslTransform.Load("map.xsl")
> textWriter = New XmlTextWriter("out.xml", Nothing)
> xslTransform.Transform(xmlRsp, Nothing, textWriter)
> textWriter.Flush()
> textWriter.Close()
> With xmlBulkLoader
> .ConnectionString = "provider=SQLOLEDB;..."
> .ErrorLogFile = "bulkLoaderErrors.txt"
> .KeepIdentity = False
> .XMLFragment = True
> .Execute("schema.xml", "out.xml")
> End With
> When the transform is sent into a memory stream, the stream fills as
> expected
> dim memStream as New System.IO.MemoryStream
> xslTransform.Transform(xmlRsp, Nothing, memStream)
> but I can't figure out how to use memStream in
> xmlBulkLoader.Execute("schema.xml", memStream)
> I've tried using various properties of the memStream, and tried using a
> stream reader, but the execute always returns "Error opening data file."
>|||Thanks Irwin - wow that's a lot of code. The article mentioned that
ADODB.Stream is directly supported. Would that easier to work with?|||That's in native code, looked like you were doing managed. If you were
doing native code it might be.
"jayMeer" <jmeerdink@.synergy.gs> wrote in message
news:1112792519.498993.310540@.z14g2000cwz.googlegroups.com...
> Thanks Irwin - wow that's a lot of code. The article mentioned that
> ADODB.Stream is directly supported. Would that easier to work with?
>

Bulk Insert won't compile if file doesn't exist, no Try Catch either

I first noticed that the try/catch did not seem to work when the text file did not exist and I tried to execute this block of code from a stored proc.

Then, when I tried to compile the proc using a file that did not exist at the time of compilation, I can't even get it to compile.

If I compile, then remove the file, the try/catch does not work.

TRUNCATE TABLE LoadDaily;

BEGIN TRY

BULK INSERT LoadDaily
FROM 'c:\temp\daily.txt'
WITH
(
BATCHSIZE = 100,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '0x0A',
FIRSTROW = 2
);

END TRY
BEGIN CATCH

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

END CATCH

Instead of saying "compile" I mean that I can't use either ALTER PROCEDURE or CREATE PROCEDURE when the text file does not exist.

Fortunately, using EXEC works and the try/catch works as directed.

This will be run from a service so I don't think I will have any permissions issues.

I tested in SQL 2000 and did not have any problems editing the SP using EM with a non-existant text file.

It does not work if the format file is missing either.
TRUNCATE TABLE LoadDaily;

BEGIN TRY

DECLARE @.sql nvarchar(300), @.Fname nvarchar(300)

SET @.Fname = 'c:\_temp\daily.txt'

SET @.sql = 'BULK INSERT LoadDaily FROM ''' + @.Fname + ''''
+ ' WITH '
+ ' ( '
+ ' BATCHSIZE = 100, '
+ ' FIELDTERMINATOR = ''|'', '
+ ' ROWTERMINATOR = ''0x0A'', '
+ ' FIRSTROW = 2'
+ ' );'

EXEC (@.sql)

END TRY
BEGIN CATCH

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

END CATCH

|||This is a known limitation. TRY...CATCH cannot catch all errors. Some of the errors that happen at compile time can be caught only in the outer TRY..CATCH. This is what you kind of achieved by using dynamic SQL for the BULK INSERT. Please see the TRY...CATCH topic in BOL for more details.|||Sorry, this is a level 16 error so it should be caught by definition (BOL says the range is 10 to 20).

I think I'm more concerned about the regression error. I may be doing something wrong because the environment has changed but I found no way to create the Stored Procedure if the the text file does not exist. I went back and tested it in SQL 2000 and it worked fine.

Since there is now "run as" functionality with EXECUTE, it won't be a big deal but the issue should be addressed.

The new OPENROWSET has the same problem for the text file and the format file.|||Not all errors of a particular severity can be caught. In your examples, these errors are raised at compile time so there is no code execution involved at all for the TRY...CATCH to work. OPENROWSET(BULK) and BULK INSERT pretty much uses the same infrastructure so both will error out if the data file/format file is invalid or missing.|||

OK, let's forget the Try/Catch issue for now.

Just see if you can compile the test proc below in SQL 2005 when daily.txt does NOT exist.

I just found an interesting side effect: Make sure that LoadDaily is a valid table. If LoadDaily is invalid, the code WILL compile.

Now that's really weird.

CREATE PROCEDURE test
AS

BEGIN
BULK INSERT LoadDaily
FROM 'c:\temp\daily.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
);
END
GO

|||The reason is deferred name resolution. If any object is missing at the time of creation of a stored procedure then the statement is not compiled or parsed completely (note that the syntax is still validated) and deferred until run-time or execution. This explains the behavior you are seeing. This is also true for say references to temporary tables - this is the reason why you can reference temporary tables that are not created in a SP and call it later from another that creates the temporary table or a trigger referencing a temporary table created by a stored procedure that issues the insert/update/delete statement. This is a behavior that we introduced from SQL Server 7.0. Note that this can lead to performance issues due to recompilation of the entire stored procedure at run-time when it hits the statement. This is alleviated to some extent with statement level recompilation in SQL Server 2005. For a more complete discussion, please refer to the whitepaper http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx.|||

Deferred name resolution really DOES explain why it worked in 2000.

It seems to be partially broken now:
If the table is VALID but the text file is NOT VALID then the proc will NOT compile.

I can't believe that this was the intention. This is simply an external text file so it should not affect compilation.

|||Unfortunately, this is a side-effect of BULK INSERT using the OPENROWSET(BULK) infrastructure internally. OPENROWSET(BULK) validates parameters at compile-time itself and hence the error. This behavior is same as other OPENROWSET commands. For example, if you try OPENROWSET with Jet provider and missing/invalid mdb file you will get same behavior in SQL Server 2000/2005. The reality is that most people will use dynamic SQL since the filenames cannot be parameterized directly so they will not hit the issue.sql

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."
Im logged in a Microsoft SQL Server 2000, with a login account that
belogs to the role db_owner of the database Im 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.codecomments.com ***
Hi
You may wantr to check out
http://support.microsoft.com/default...b;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.codecomments.com ***
>

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."
Im logged in a Microsoft SQL Server 2000, with a login account that
belogs to the role db_owner of the database Im 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.codecomments.com ***Hi
You may wantr to check out
http://support.microsoft.com/defaul...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.codecomments.com ***
>

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 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 error file does not exist

Hello

I am trying to bulk insert a text file into SQL 2005 table. When I execute the bulk insert I get the error

"Msg 4860, Level 16, State 1, Line 1. Cannot bulk load. The file "\\ENDUSER-SQL\EnduserText\B1020063.txt" does not exist."

The text file that it is saying does not exist I recently created thru my code. I can open the file but only when I rename the file will the Bulk Insert work. After creating the text file I am moving it to the server that SQL server is running on. Also if I run sp_FileExists it also says the file does not exist unless again I rename the file then this stored procedure recognizes the file. I dont' know if I have a permission issue or what is the problem. Any help would be appreiated.

Thanks

Chris

What is your bcp command? What is the original and new name of the file?|||This is always a rights or login issue.

How are you "running" the bulk insert, is it a job or a dts or a query or what? Is it "Bulk Insert" or "BCP.EXE".

Assuming it is Bulk Insert, like you said, it is running on the SERVER as the logged in user. Can the server access \\ENDUSER-SQL...?

What are you "renaming" the file too? Are you actually moving the file to another location or just renaming it? I don't see anything wrong with the name.|||

I am running the bulk insert from a query. The original file name is B120049.txt, I then rename the file to say B120049BLK.txt save it then the bulk insert runs fine.

Thanks

Chris

|||

I am running the bulk insert in a query

BULK INSERT B1020063 FROM '\\ENDUSER-SQL\EnduserText\B1020063.txt' WITH (FIELDTERMINATOR = '|')

ENDUSER-SQL is the server that MS SQL 2005 is running on. I am running the query thru my Delphi 2005 code using ADO components. What I am doing is running my Delphi code to create the text file from a paradox table (don't laugh) then trying to use the bulk insert to take the text file into SQL table. I am running the code on my desktop and creating the text file on a shared folder on the SQL Server (\\ENDUSER-SQL\EnduserText\..)

When I rename the file I am taking B1020063.txt renaming to B1020063BLK.txt and it works. Also if after saving the file once I save it back to orginal name it also works. I am not moving the file from the orginal location just changing the name.

Thanks for the help.

Chris

|||BULK INSERT will be running from the server, so it doesn't matter where you ran it from.

Try changing the file name to the actual physical directory "C:\EndUser...." or whatever. See if that works.

It has to be a rights issue to the file. When you rename it, it is getting your rights, then running as you so it works.|||Do an xp_cmdshell 'dir \\ENDUSER-SQL\EnduserText' to see if SQL Server can access the folder.|||

I tried renaming the file to C:EnduserText\B1020049.txt and go the same results. Any ideas on how I can change the rights on the file so I can use it without having to renam it?

Thanks

Chis

|||

I ran this stored procedure and here are the results :

Volume in drive \\ENDUSER-SQL\EnduserText has no label.
Volume Serial Number is 547D-E293
NULL
Directory of \\ENDUSER-SQL\EnduserText
NULL
10/29/2006 01:32 PM <DIR> .
10/29/2006 01:32 PM <DIR> ..
10/27/2006 03:27 PM 77,087 AcctRpts.txt
10/27/2006 03:27 PM 826,363 AcctSql.txt
10/27/2006 03:27 PM 217 AcctTypes.txt
10/27/2006 03:27 PM 121 AcctTypes2.txt
10/27/2006 03:27 PM 1,411,007 Address.txt
10/27/2006 03:27 PM 7,350 Addrlink.txt
10/27/2006 03:27 PM 185,409 AllFeat.txt
10/27/2006 03:27 PM 574 Allocation.txt
10/27/2006 03:27 PM 13,954 ANPI.txt
10/27/2006 03:27 PM 617 AREACODE.txt
10/27/2006 03:27 PM 1,882 ARSETUP.txt
10/27/2006 03:27 PM 3,613 B1020049.txt
10/27/2006 03:27 PM 7,243 B1020063.txt
10/27/2006 03:30 PM 50,497,983 B1120022.txt
10/27/2006 03:32 PM 46,926,455 B1120023.txt
10/27/2006 03:35 PM 54,188,893 B1120024.txt
10/27/2006 03:37 PM 50,094,853 B1120025.txt
10/27/2006 03:39 PM 54,233,552 B1120026.txt
10/27/2006 03:41 PM 54,900,297 B1120027.txt
10/27/2006 03:41 PM 72 BILCYCLE.txt
10/27/2006 03:41 PM 4,549 Billdate.txt
10/27/2006 03:41 PM 0 BILLERR.txt
10/27/2006 03:41 PM 3,765,367 BILLMAIN.txt
10/27/2006 03:41 PM 10,293 Billplan.txt
10/27/2006 03:44 PM 35,721,280 Billsum.txt
10/27/2006 03:41 PM 261,183 BILLTOBK.txt
10/27/2006 03:44 PM 152,994 Bilmesg.txt
10/27/2006 03:44 PM 2,574 BundledService.txt
10/27/2006 03:44 PM 1,759 BusCalendar.txt
10/29/2006 11:27 AM 49,507,562 C200110.txt
10/29/2006 11:29 AM 45,172,115 C200111.txt
10/29/2006 11:31 AM 47,562,230 C20017.txt
10/29/2006 11:33 AM 50,695,011 C20018.txt
10/29/2006 11:36 AM 56,648,282 C20019.txt
10/29/2006 11:38 AM 50,773,887 C20021.txt
10/29/2006 11:40 AM 46,550,340 C200210.txt
10/29/2006 11:42 AM 43,977,305 C200211.txt
10/29/2006 11:44 AM 47,850,286 C200212.txt
10/29/2006 11:46 AM 45,525,197 C20022.txt
10/29/2006 11:48 AM 41,312,655 C20023.txt
10/29/2006 11:50 AM 48,118,979 C20024.txt
10/29/2006 11:52 AM 45,851,116 C20025.txt
10/29/2006 11:54 AM 48,136,512 C20026.txt
10/29/2006 11:57 AM 47,693,599 C20027.txt
10/29/2006 11:59 AM 48,643,674 C20028.txt
10/29/2006 12:01 PM 49,560,178 C20029.txt
10/29/2006 12:03 PM 47,320,372 C20031.txt
10/29/2006 12:05 PM 44,242,516 C200310.txt
10/29/2006 12:07 PM 43,453,596 C200311.txt
10/29/2006 12:09 PM 48,399,934 C200312.txt
10/29/2006 12:11 PM 42,166,984 C20032.txt
10/29/2006 12:13 PM 42,449,679 C20033.txt
10/29/2006 12:15 PM 42,856,103 C20034.txt
10/29/2006 12:17 PM 44,666,989 C20035.txt
10/29/2006 12:19 PM 45,443,402 C20036.txt
10/29/2006 12:21 PM 47,432,232 C20037.txt
10/29/2006 12:23 PM 46,027,801 C20038.txt
10/29/2006 12:25 PM 49,730,365 C20039.txt
10/29/2006 12:27 PM 45,443,543 C20041.txt
10/29/2006 12:29 PM 44,342,955 C200410.txt
10/29/2006 12:31 PM 42,923,015 C200411.txt
10/29/2006 12:33 PM 43,391,572 C200412.txt
10/29/2006 12:35 PM 41,510,324 C20042.txt
10/29/2006 12:37 PM 45,968,110 C20043.txt
10/29/2006 12:38 PM 42,195,260 C20044.txt
10/29/2006 12:41 PM 47,489,289 C20045.txt
10/29/2006 12:43 PM 44,718,583 C20046.txt
10/29/2006 12:45 PM 47,564,502 C20047.txt
10/29/2006 12:47 PM 46,161,036 C20048.txt
10/29/2006 12:49 PM 47,575,248 C20049.txt
10/29/2006 12:51 PM 45,312,859 C20051.txt
10/29/2006 12:53 PM 44,072,464 C200510.txt
10/29/2006 12:55 PM 43,128,828 C200511.txt
10/29/2006 12:57 PM 42,414,577 C200512.txt
10/29/2006 01:34 PM 48,520,020 c20052.txt
10/29/2006 12:59 PM 42,046,826 C20053.txt
10/29/2006 01:00 PM 40,829,562 C20054.txt
10/29/2006 01:02 PM 46,261,908 C20055.txt
10/29/2006 01:04 PM 44,259,999 C20056.txt
10/29/2006 01:06 PM 44,248,580 C20057.txt
10/29/2006 01:08 PM 44,001,686 C20058.txt
10/29/2006 01:10 PM 44,345,187 C20059.txt
10/29/2006 01:12 PM 44,186,657 C20061.txt
10/29/2006 01:14 PM 30,604,652 C200610.txt
10/29/2006 01:16 PM 42,462,188 C20062.txt
10/29/2006 01:18 PM 44,203,999 C20063.txt
10/29/2006 01:20 PM 42,164,513 C20064.txt
10/29/2006 01:22 PM 51,782,537 C20065.txt
10/29/2006 01:24 PM 46,269,771 C20066.txt
10/29/2006 01:27 PM 51,892,690 C20067.txt
10/29/2006 01:29 PM 48,158,790 C20068.txt
10/29/2006 01:32 PM 50,942,048 C20069.txt
08/22/2006 04:10 AM 3,927,036 CASS060817done.txt
08/24/2006 12:32 PM 3,485 vod082406.txt
10/19/2006 12:13 PM 3,522 vod101306.txt
10/19/2006 12:13 PM 8,695 vod101606.txt
96 File(s) 3,236,402,958 bytes
2 Dir(s) 130,462,183,424 bytes free
NULL

It looks like it sees all the text files I have created.

Thanks for the help.

Chris

Bulk Insert

Hi All,
Suppose I will performa a Bulk Insert from Client side, Mean using
Con.Execute, Does that can raise any Time out?
Thanks
PrabhatIf you have a conn.Timeout value set, it can cause a timeout for long
running operations.
Anith

Bulk Insert

Hi All,
Suppose I will performa a Bulk Insert from Client side, Mean using
Con.Execute, Does that can raise any Time out?
Thanks
PrabhatIf you have a conn.Timeout value set, it can cause a timeout for long
running operations.
--
Anith

Bulk Insert

Hi All,
Suppose I will performa a Bulk Insert from Client side, Mean using
Con.Execute, Does that can raise any Time out?
Thanks
Prabhat
If you have a conn.Timeout value set, it can cause a timeout for long
running operations.
Anith

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