Add SET NOCOUNT ON at the begining of the script. Did you want to output the result of this select as a text? Did you see my blog - it solves very similar problem.
>>Biggest gotcha is making sure you have permissions enabled on the folder where you are writing the file.
>>
>>HTH
>
>Thank you Charles. There must be other gotchas I don't quite have my brain wrapped around. I end up with a result file that contains:
>
>
>(0 rows affected)
>
>
>the source script is
>
>
>WITH Procs (ProcName) AS
>(
> SELECT
> name
> FROM sys.procedures
> WHERE [type] = 'P'
> AND is_ms_shipped = 0
> AND [name] LIKE 'spMT%'
>)
>SELECT
>'IF EXISTS(
> SELECT *
> FROM sys.objects
> WHERE object_id = OBJECT_ID(N''[dbo].[' + ProcName + ']'')
> AND TYPE IN (N''P'', N''PC''))
>DROP PROCEDURE [dbo].[' + ProcName + ']
>GO
>
>'
>FROM Procs
>
>
>Which works perfectly when executed from SSMS.
>I suspect the problem is with the formatting of my script.
If it's not broken, fix it until it is.
My Blog