SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS 2005 report: Cannot bulk load Operating system error code 5(Access is denied.)


SSRS 2005 report: Cannot bulk load Operating system error code 5(Access is denied.)

Author
Message
nzhang6666
nzhang6666
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 321
I built a SSRS 2005 report, which calls a stored proc on SQL Server 2005. The proc contains following code:

CREATE TABLE #promo (promo VARCHAR(1000))

BULK
INSERT #promo
FROM '\\aseposretail\c$z\promo_names.txt'
WITH
(
--FIELDTERMINATOR = '',
ROWTERMINATOR = ''
)

SELECT * from #promo

It's ok when I manually execute the proc in SSMS.

When I try to run the report from BIDS I got following error:

*Cannot bulk load because the file "\aseposretail\c$z\promo_names.txt" could not be opened. Operating system error code 5(Access is denied.).*

Note: I have gooled a bit and saw many questions on this but they are not relevant because I CAN run the code no problem in SSMS. It's the SSRS having the issue. I am gussing it's related to the security of SSRS but know little about this.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67557 Visits: 18570
When you attempt to run this in BIDS, are you using the same account that you use to run SSMS?

Many people use different accounts to launch SSMS versus to launch BIDS.

Once you confirm that, and get it working in BIDS, then we can move on to getting it to work via SSRS.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

nzhang6666
nzhang6666
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 321
SQLRNNR (3/26/2014)
When you attempt to run this in BIDS, are you using the same account that you use to run SSMS?

Many people use different accounts to launch SSMS versus to launch BIDS.

Once you confirm that, and get it working in BIDS, then we can move on to getting it to work via SSRS.


How to check the account used by BIDS? What I did was I logged on the server then start the BIDS from windows-start. And The same for SSMS and SSMS is using windows authentication.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67557 Visits: 18570
If you are using the same session you are probably using the same account. You didn't use a runas in the shortcut for bids or ssms did you?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

nzhang6666
nzhang6666
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 321
Yes same session and not using a run as.

--update,
Yes, I confirm bids is running as the same user as SSMS. I printed the UserID global variable in a test report.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67557 Visits: 18570
when you past this path into a run window (start->run), does it produce an error or open an explorer window with contents?

'\\aseposretail\c$z\




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

nzhang6666
nzhang6666
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 321
Yes, I can open the location no problem.

Btw, I forgot to mention, the BIDS and SSMS are running on a different server than aseposretail
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67557 Visits: 18570
I wonder if your share name is causing some issues behind the scenes. C$Z might be trying to resolve to the admin share of C



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

patrickmcginnis59 10839
patrickmcginnis59 10839
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4404 Visits: 5913
You could create a test SSRS report, and just as a data source 'SELECT SYSTEM_USER' or some variation of http://technet.microsoft.com/en-us/library/ms191126(v=sql.105).aspx user id system variables to see what SQL (or windows) account is actually going to run that bulk load statement from within ssrs and then go about adjusting privileges from there.

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
nzhang6666
nzhang6666
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 321
SQLRNNR (3/26/2014)
When you attempt to run this in BIDS, are you using the same account that you use to run SSMS?

Many people use different accounts to launch SSMS versus to launch BIDS.

Once you confirm that, and get it working in BIDS, then we can move on to getting it to work via SSRS.


It's been a while, but I re-looked at it today and found it's working in BIDS now (used to be not working in BIDS either). Don't know the reason. So it works in BIDS but not in report server when deployed. Next step?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search