Bulk Insert Task Failure

Question

Bulk Insert Task Failure

AER

SSCrazy

Points: 2763
More actions
April 26, 2002 at 8:56 am

#104934

Our company run SQLServer7.
We have a problem when trying to set a network path (i.e. //....) for Bulk Insert. It fails.
No problems with the local paths though (i.e. C:/....)
Could you help?
Thank you in advance,
Alex

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Make sure you can open the path in command line such as a DOS window or from the run line. If there are any spaces in the path it will not find the file. Quick way to do this from within SQL is to use the following;

declare

@result int

exec @result = master..xp_cmdshell 'dir \\YourServer\YourShare\YourFile.TXT'

print @result

If @result is = 0 then the file is found if 1 then the file is not found and I would continue to look at the specified path.

You can also enclose your path in "" (double quotes) if there is spaces in the path.

Hope this helps.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

AER SSCrazy Points: 2763 More actions · Answer 2

David,

Thank you for your response.

I wish you would be right but this problem has nothing to do with the spaces.

I’m using browser in Bulk Insert Properties window to specify Source & Format files.

I’m getting the following error message:

“Could not bulk insert because file '\\SomeServer\data\temp\Test.dat' could not be opened. Operating system error code5(Access is denied.)”.

There is no errors if I specify a local directory of this file as D:\temp\Test.dat

This error is reported as a BUG #: 56167 in SQLServer7. And the only way around Microsoft recommends to install SP2 for SQLServer7.

I have SP3 installed on all the machines, but it still doesn’t work.

AER SSCrazy Points: 2763 More actions · Answer 3

I was waiting for 3 days. So, I guess nobody can answer my question.

I will appreciate any response.

Sincerely,

Alex

jloesch Ten Centuries Points: 1004 More actions · Answer 4

Looks to me like you need to check your operating system permissions for that file. Are you able to open the file in any other way, such as Notepad, Wordpad, Excel, etc? If you can open it that way, using the network share, then I would need more detail to help more.

James C Loesch

AER SSCrazy Points: 2763 More actions · Answer 5

James,

Thank you for your response. I can open file using a network share in any of these programs.

To tell you the truth I think that the basis of your idea is correct, but I think this has something to do with the SQL Server permissions, not the operating system ones.

Thank you.

I will be waiting for the answer

goughj SSChasing Mays Points: 640 More actions · Answer 6

How do you have this bulk insert set up to run, command line or dts/sql job? If it is running from a command line the login to sql must have SA privileges.

Good luck.

Jody

AER SSCrazy Points: 2763 More actions · Answer 7

Preview

I'm executing a DTS package which consists of 2 tasks:

1. Microsoft OLE DB Provider for SQL Server Connection

2. Execute SQL Task

In Connection task I was using Windows NT Authentication or SQL Server Authentication with 'sa' Username. Both of them are not working for network share, but work for local paths and persisted mapping files which I provide in Execute SQL Task.

This is a snapshot from Execute SQL Task:

Existing connection:

Microsoft OLE DB Provider for SQL Server

Destination table: [Northwind].[dbo].[ZNetwPath] Refresh

Source data file: D:\Data\temp\Test.dat ...

Use format file: ...

D:\Data\temp\Netw.fmt

This always works, but \\SomeServer\temp... does not.

goughj SSChasing Mays Points: 640 More actions · Answer 8

Thanks for the example. If you try your nt authentication again after setting the nt account in sql server to have 'sa' rights (enterprise manager to security to nt account - second tab). Then confirm that the nt account has read permissions to the share for both the format and data files, I think it should work for you.

I have also encountered problems on a file that even though permissions are granted to the share, the file itself arrived with different permissions. So, you may also need to check the actual files.

Jody

AER SSCrazy Points: 2763 More actions · Answer 9

Jody,

You are probably talking about SQLServer2000. There is no 'nt account' under Security in SQLServer7 Enterprise Manager. I don't think it's so easy (May be it is?).

Look at this article http://support.microsoft.com/default.aspx?scid=kb;EN-US;q238238

Microsoft gives only one way around this problem to install sp2 (I have sp3 which includes all sp2 options). I sent this question to Microsoft guys but didn't get any answer from them.

goughj SSChasing Mays Points: 640 More actions · Answer 10

Sorry, I skipped a step and assumed (uh oh) that you already had established an nt login to sql.

In Enterprise Manager you can create a new login selecting the radial button by 'Windows Authentication'. Fill in the NT Account name that has access to the shares and that will be executing the bulk insert.

Set the account to 'System Administrators' on the Server Role tab and give access to the database(s) required as 'dbo'.

Test the login as that nt user in query analyzer. Then try the bulk insert.

I have SP2 and SP3 on different servers executing bulk inserts without a problem.

The easiest is to place the load files within the mssql7 share as a subtree. It seems to 'understand' the permissions more inherently.

Jody

AER SSCrazy Points: 2763 More actions · Answer 11

Jody,

Sorry I didn't have time yesterday to thank you for your advise.

It worked for SQLServers on WIN-2K & WIN-XP machines, but didn't work on WINNT machines.

Now, it looks like James was not so wrong talking about operating system permissions, although I could edit files on all machines as network share in DOS Prompt and in any programs which he mentioned.

goughj SSChasing Mays Points: 640 More actions · Answer 12

No worries, Alex. That is what this community is all about. It is the little things that we find by our trials and errors that unlock problems for others.

Good Luck