Click here to monitor SSC
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
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 396
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45307 Visits: 39934
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Kavic62
Kavic62
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 396
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
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7809 Visits: 8746
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