December 29, 2003 at 9:32 am
I need some help designing a new stored procedure. I keep getting a syntax error 170 on the EXEC statement. I am new to T-SQL and need some help designing the xp_cmdshell command correctly.
I want to load the data from the xp_fixeddrives into a temporary table and then export this table to a text file. I am not exporting directly since I use the table data later in the sp to send a message when disk space is low.
CREATE PROCEDURE usp_FreeDiskSpace
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #tblTemp(
chrDrvLetter CHAR(1),
intDrvSpace INT
)
INSERT INTO #tblTemp EXEC master.dbo.xp_fixeddrives
IF (SELECT COUNT(*) FROM #tblTemp) > 0
BEGIN
DECLARE
@rcpt VARCHAR(500),
@limit INT,
@MSG VARCHAR(400),
@dletter VARCHAR(5),
@dspace INT
-- Export the disk drive information
EXEC xp_cmdshell 'bcp.exe master..#tblTemp out findme.txt -c'
GO
I am not sure if it's because of missing quotes, do I use single or double?, maybe missing parenthesis, maybe it's the combination of xp, I don't know. I have tried using a varchar to store the command string (this is the way i originally wanted it) but no matter what I do I keep getting this error.
Hopefully an your objectivity & experience can help.
Thanx in advance for your help.
December 29, 2003 at 9:47 am
Use a permanent table (clear it each time). The xp_cmdshell changes context, so the temp table isn't visible.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 30, 2003 at 1:33 pm
Your missing a couple of ENDs as well:
--Create Table
if object_ID('tblTemp') is not null drop table tblTemp
go
CREATE TABLE tblTemp(
chrDrvLetter CHAR(1),
intDrvSpace INT
)
go
--create Proc
CREATE PROCEDURE usp_FreeDiskSpace
AS
BEGIN
DECLARE
@rcpt VARCHAR(500),
@limit INT,
@MSG VARCHAR(400),
@dletter VARCHAR(5),
@dspace INT
SET NOCOUNT ON
truncate TABLE tblTemp
INSERT INTO tblTemp EXEC master.dbo.xp_fixeddrives
IF exists (SELECT 1 FROM tblTemp)
BEGIN
-- Export the disk drive information
EXEC xp_cmdshell 'bcp.exe master..tblTemp out findme.txt -c'
END
END
GO
Signature is NULL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply