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

xp_cmdshell not supported in SQL 2005 Express Edition Expand / Collapse
Author
Message
Posted Monday, February 14, 2011 11:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 11, Visits: 285
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~~

Post #1063717
Posted Monday, February 14, 2011 4:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1063931
Posted Tuesday, February 15, 2011 6:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 11, Visits: 285
This particular server is on build 9.00.5000.00 SP4. It is SQL Server 2005 Embedded Edition (Windows Internal Database). Thanks!
Post #1064228
Posted Tuesday, February 15, 2011 10:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:28 AM
Points: 5,584, Visits: 6,380
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1064427
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse