Import Excel using OPENROWSET only works for SA

Question

Import Excel using OPENROWSET only works for SA

Viewing 6 posts - 16 through 20 (of 20 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Crud. I completely forgot about such a thing. The last time that happened to me it was back when it all went to C:\Temp.

Obviously, there is a real reason to be concernined with the fragility of the solution. I just told you it used to be in a different directory. But I wouldn't be too awefully concerned because there's not much you can do to control it. It's just like anything else. Although it's not the norm, any service pack or CU to any software product can really put the screws to you especially if you upgrade "in situ" instead of in parallel.

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

I just had a great thought on this, ST... it's a nice short subject so you could go either way on it. You should write a short "SQL Spackle" article on the problem or, perhaps, a longer article that chronicles the steps you took to find and fix this problem. It would be a great way for you to get published for the first time.

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

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 3

Thanks, Jeff. I will really consider doing that.

The funny part is, early in my attempts to resolve this, I read many articles that said the issue could be related to permissions on the file system. Windows/temp, program files/SQ Server/temp, etc. were all mentioned... and I tried all of them. It never dawned on me that it's just a different temp directory that was causing the issue.

Thanks again for your help and thoughts.

ST

Hemant.R Ten Centuries Points: 1286 More actions · Answer 4

i had face similar problem one of my server.

I got solution for this.

please find below link.

http://stackoverflow.com/questions/3389312/allow-ad-hoc-distribution-queries-for-all-users

hope this will work for you.

before doing change in registry first take backup of registry

regards,

HEMANT

danpousson SSC Rookie Points: 45 More actions · Answer 5

Try assigning yourself or the relevant user the Bulkadmin Server Role

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 6

The issue has been resolved (see above) but this is an update.

ProcessMon is your friend. Seems that in Win Server2008 SQL Server 2008 R2 was impersonating the login that was initiating the process. My spreadsheet was being copied to:

C:\Windows\ServiceProfiles\NetworkService\AppData\Local\temp

Allowing my web server to read/write to this directory solved the issue.

UPDATE-- In production, a different account was being used. SQL Server was not running as Network Service but as a dedicated Windows user SQLDBSVC. Again, Process Mon saved me. I found that in production the temp directory being accessed was:

C:\Users\sqldbsvc\AppData\Local\Temp

All was fine after I updated the permissions on this folder to include my web server.

ProcessMon

http://technet.microsoft.com/en-us/sysinternals/bb896645