rsErrorExecutingCommand from RS using stored proc.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

rsErrorExecutingCommand from RS using stored proc.

Author

Topic

kmistic
Starting Member

40 Posts

Posted - 2005-03-08 : 10:03:24

I have a report in reporting services that uses a stored procedure to generate its dataset.
The stored procedure executes a dts package using xp_cmdshell to pass a dtsrun command.
When i execute the report it works fine, however when non-admins execute it it returns
a rsErrorExecutingCommand. In my troubleshooting i realized that if i don't execute the
dts package the report generates succesfully. Therefore, i thought the problem was with the xp_cmdshell
command. I change my dtsrun command to use /U and /P "password. and still same error.
I am really stuck now, any clues on what i need to do?

here is my stored procedure:
CREATE PROCEDURE PartsResults AS

DECLARE @CMD varchar(200)

SET @CMD = 'DTSRun /S "FINC01" /U "sa" /P "password" /N "HDPOLines" /G "{1DABCA1E-C004-4F56-8E6F-00AB41C34F29}" /W "0"'

exec master..xp_cmdshell @cmd, NO_OUTPUT

SELECT TOP 1000 left(dbo.HDpolines.PO#,10) as po#, left(dbo.HDpolines.QORD,7) AS [Ordered], left(dbo.HDpolines.PART#,10) AS [PO Part#],
left(dbo.HDpolines.NAME,20) as NAME,
left(dbo.HDpolines.INV#,8) as INV#, left(dbo.ServiceLocationsV.Location,20) as Location,
left(dbo.Parts.PART#,10) AS [Prtinv#], left(dbo.Parts.QOH,5) as QOH, left(dbo.Parts.DESCRIPTION,20) as DESCRIPTION,
left(dbo.Parts.BIN1,5) as BIN1,
left(dbo.Parts.BIN2,5) as BIN2
FROM dbo.Parts INNER JOIN
dbo.ServiceLocationsV ON dbo.Parts.Box = dbo.ServiceLocationsV.Box AND
dbo.Parts.Branch = dbo.ServiceLocationsV.[Parts Branch] RIGHT OUTER JOIN
dbo.HDpolines ON dbo.Parts.PART# = dbo.HDpolines.PART# OR dbo.Parts.PART# = LEFT(dbo.HDpolines.PART#, 1)
+ 'T' + SUBSTRING(dbo.HDpolines.PART#, 3, 99) AND dbo.Parts.QOH > 0
WHERE (dbo.Parts.QOH > 0) AND (DATEDIFF(m, dbo.Parts.LSDATE, { fn NOW() }) >= 9) OR
(dbo.Parts.PART# = LEFT(dbo.HDpolines.PART#, 1) + 'T' + SUBSTRING(dbo.HDpolines.PART#, 3, 99))
GO

Here is the error:
Reporing Services Error
An error has occured during report processing.(rsProcessingAborted)Get Online Help
Query execution failed for data set 'Parts'. (rsErrorExecutingCommand)Get Online Help
A severe error occured on the current command. The results, if any, should be discarded.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-08 : 12:41:12

Did you check out the permissions of xp_cmdshell in SQL Server Books Online?

quote:

Permissions
Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.

Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.

Also:

quote:

In earlier versions, a user who was granted execute permissions for xp_cmdshell ran the command in the context of the MSSQLServer service's user account. SQL Server could be configured (through a configuration option) so that users who did not have sa access to SQL Server could run xp_cmdshell in the context of the SQLExecutiveCmdExec Windows NT account. In SQL Server 7.0, the account is called SQLAgentCmdExec. Users who are not members of the sysadmin fixed server role now run commands in the context of this account without specifying a configuration change.

Tara

kmistic
Starting Member

40 Posts

Posted - 2005-03-08 : 15:03:59

Perfect!!!!, I got it to work. I had already granted the user execute permission to xp_cmdshell sp. My problem was I had to uncheck the box in Enterprise Mangers Sql Server Agent Properties that says Only users with SysAdmin privleges can execute CmdExec and ActiveScrping job steps. I then had to setup a Proxy Account.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources