Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS 2005 report: Cannot bulk load Operating system error code 5(Access is denied.) Expand / Collapse
Author
Message
Posted Wednesday, March 26, 2014 8:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:23 AM
Points: 48, Visits: 236
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.
Post #1554999
Posted Wednesday, March 26, 2014 8:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 17,700, Visits: 15,564
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1555005
Posted Wednesday, March 26, 2014 9:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:23 AM
Points: 48, Visits: 236
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.
Post #1555021
Posted Wednesday, March 26, 2014 9:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 17,700, Visits: 15,564
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1555024
Posted Wednesday, March 26, 2014 9:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:23 AM
Points: 48, Visits: 236
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.
Post #1555039
Posted Wednesday, March 26, 2014 10:18 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 17,700, Visits: 15,564
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1555064
Posted Wednesday, March 26, 2014 10:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:23 AM
Points: 48, Visits: 236
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
Post #1555067
Posted Wednesday, March 26, 2014 10:28 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 17,700, Visits: 15,564
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1555072
Posted Wednesday, March 26, 2014 10:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:07 AM
Points: 341, Visits: 2,461
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.

Post #1555076
Posted Tuesday, April 8, 2014 5:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:23 AM
Points: 48, Visits: 236
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?
Post #1559433
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse