bulk insert headache

Question

Post reply

bulk insert headache

fizzleme

SSCertifiable

Points: 6906
More actions
September 12, 2019 at 11:11 pm

#3679671

windows 2016 server with sql 2016
doing a bulk insert on a space delimited text file. The file resides on another server. very routine.
Works all day long when I remote into the sql server with an admin acct and run the code from there.
It can't find the file when I run it from my desktop using a non-admin acct. The file is shared by both of my user accts and the sql agent server acct. My Sql standard security acct has owner rights on the database I am using. I need this to run from my pc. I'm using the full file path with double quotes due to embedded spaces in the file name. Again it works fine when remoteing into the sql server using my local admin acct. I'm ready to jump...
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.

Viewing 10 posts - 1 through 9 (of 9 total)

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

are you using a fully qualified UNC path? such as

\\server\folder1\file1

or a local path

H:\file1

I've had issues with local paths and now everything is UNC for me

MVDBA

fizzleme SSCertifiable Points: 6906 More actions · Answer 2

I have tried mapped drives and the full unc path. Life continues to be unkind. I am considering trying BCP after my root canal without nova Caine.

When the snows fall and the white winds blow,The lone wolf dies but the pack survives.

Once you've accepted your flaws, no one can use them against you.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

And hopefully you aren't referencing an admin share - such as C$, D$. You may want to try using RunAs with SSMS or whatever you are using for the bulk insert and using your admin account. Just to see if it works. You could try having the bulk insert in a job on that server but that's not an option if you need it to run from your PC.

Sue

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

have you checked both the file security for the shared folder AND the share permissions.

i'm not saying this is best practice, but what I tend to do is set "everyone" to "all permissions" to start with and then start trimming it down to what is needed.

MVDBA

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

fizzleme wrote:

I have tried mapped drives and the full unc path. Life continues to be unkind. I am considering trying BCP after my root canal without nova Caine.

Bulk Insert uses the privs of the login using it. You have to make sure the login being used as the privs in the source directory.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

Jeff Moden wrote:

Bulk Insert uses the privs of the login using it. You have to make sure the login being used as the privs in the source directory.

Hehe - until you put it in a scheduled job and someone has set up sql agent to use the default NT/Anonymous

you never know with server admins... they set up things and hand them over 🙂

MVDBA

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

MVDBA (Mike Vessey) wrote:

Jeff Moden wrote:
Bulk Insert uses the privs of the login using it. You have to make sure the login being used as the privs in the source directory.
Hehe - until you put it in a scheduled job and someone has set up sql agent to use the default NT/Anonymous
you never know with server admins... they set up things and hand them over 🙂

Heh... oh my. I've not seen someone do that before. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 8

I know this sounds obvious, but have you tried putting it into an SSIS package? (for the love of god please bring back DTS)

you schedule that in SQL agent and if the job fails then you know its a permissions issue on the service account

MVDBA

fizzleme SSCertifiable Points: 6906 More actions · Answer 9

I gave up on Bulk Insert. Used the SQL Import function. In a few hours, my life was fulfilled once again. My camper is happy.

When the snows fall and the white winds blow,The lone wolf dies but the pack survives.

Once you've accepted your flaws, no one can use them against you.