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


xp_cmdshell not supported in SQL 2005 Express Edition


xp_cmdshell not supported in SQL 2005 Express Edition

Author
Message
Kavic62
Kavic62
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 413
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~~
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211145 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Kavic62
Kavic62
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 413
This particular server is on build 9.00.5000.00 SP4. It is SQL Server 2005 Embedded Edition (Windows Internal Database). Thanks!
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37135 Visits: 9268
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/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.
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