Saving a Stored Proc

[andyc209]

i have a stored proc that when i execute it to save it it does not save the whole file, it always saves up to the line:

EXEC sp_configure 'show advanced option', '1'

anything below is removed.


USE [DATAX]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[USP_RUN_POST_TEST]
AS
-- **************************************************
-- WRITE THE FILE TO BE MAILED
EXEC sp_configure 'show advanced option', '1' --STOPS HERE
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE;
GO

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Test.xls;','Select * from [DATA$]')
SELECT name, email FROM A_mailtable where name like '%Andy%'

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
go

DECLARE @FILEDAY AS VARCHAR(50)
DECLARE @FILEMONTH AS VARCHAR(50)
DECLARE @FILEYEAR AS VARCHAR(50)
DECLARE @FILENAME AS VARCHAR(50)
DECLARE @ATTACHMENT AS VARCHAR(500)
SET @FILEDAY = DATEPART("DD",GETDATE())
SET @FILEMONTH = DATEPART("MM",GETDATE())
SET @FILEYEAR = DATEPART("YYYY",GETDATE())
SET @FILENAME = 'COPY /Y D:\Test.xls /B D:\TEST-'+ @FILEDAY +'-'+ @FILEMONTH +'-'+ @FILEYEAR +'.XLS'
SET @ATTACHMENT= 'D:\TEST-'+ @FILEDAY +'-'+ @FILEMONTH +'-'+ @FILEYEAR +'.XLS'
EXEC master..xp_cmdshell @FILENAME
--****************************************
.....

 

[djj55]

Do not use GO

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[SQLSister]

You should never use GO in a stored proc, that ends the batch and therefore the proc.

"NOTHING is more important in a database than integrity." ESquared