xp_cmdshell not supported in SQL 2005 Express Edition

Question

Post reply

xp_cmdshell not supported in SQL 2005 Express Edition

Kavic62

Right there with Babe

Points: 754
More actions
February 14, 2011 at 11:08 am

#144595

Hi Everyone,
I believe this is my first post here. I'm new to SQL DBA and muddling my way through the best I can. I am in need of creating SQL backup jobs for SQL 2005 Express that is installed on several servers. I have an express maintenance script that begins with the following syntax:
+++++++++++++++++++++++++++++++++
use master
GO
-- set required options
exec sp_configure 'show advanced options',1
reconfigure
go
exec sp_configure 'xp_cmdshell',1
reconfigure
go
exec sp_configure 'Ole Automation Procedures',1
reconfigure
go
More script follows...
++++++++++++++++++++++++++++++++
using sqlcmd, I've run the script (above) with the following syntax:
C:\>sqlcmd -S np:\\.\pipe\'Server\Instance'\sql\query -i c:\expressmaint.sql
++++++++++++++++++++++++++++++++
The results are:
Changed database context to 'master'.
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 15392, Level 16, State 1, Server 'Server\Instance', Procedure sp_configure, Line 155
The specified option 'xp_cmdshell' is not supported by this edition of SQL Server and cannot be changed using sp_configure.
Msg 15392, Level 16, State 1, Server 'Server\Instance', Procedure sp_configure, Line 155
The specified option 'Ole Automation Procedures' is not supported by this edition of SQL Server and cannot be changed using sp_configure.
Msg 33003, Level 16, State 1, Server 'Server\Instance', Procedure expressmaint, Line 1270
DDL statement is not allowed.
Stored Procedure created successfully
C:\>
+++++++++++++++++++++++++++++++++++
The following code is lines 146 through 155:
+++++++++++++++++++++++++++++++++++
-- check database exists and is online
IF @database NOT IN ('ALL_USER','ALL_SYSTEM')
BEGIN
IF (DB_ID(@database) IS NULL) OR ((select state from sys.databases where name = @database) <>0)
BEGIN
RAISERROR('Database %s is invalid or database status is not ONLINE',16,1,@database)
SET @ret = 1
GOTO CLEANUP
END
END
+++++++++++++++++++++++++++++++++++
I replaced my server name and instance with 'Server\Instance' above. I do not see the stored procedure. Does anyone have a recommendation of what I should be looking for?
thanks in advance~~

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

Are you sure you're not using CE?

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

Kavic62 Right there with Babe Points: 754 More actions · Answer 2

This particular server is on build 9.00.5000.00 SP4. It is SQL Server 2005 Embedded Edition (Windows Internal Database). Thanks!

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 3

I think embedded edition is even more crippled than the usual SQL Express. Which means certain options that Express has are not available unless you actually install Express.

See this link for more details:

http://social.msdn.microsoft.com/Forums/eu/sqlexpress/thread/2d8dfefb-6928-4a18-8b30-420bd6fde9f9

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.