Posted by Erland Sommarskog on 06/16/05 00:55

NickName (dadada@rock.com) writes:
> Create 100 or so stored procedure scripts similar to the convention of
> Generating Script from EM automatically. I thought of essentially two
> things of a) using sp_helptext to get the content of a sp; and b) using
> bcp to write such content to a (dynamic) file. What bugs me is really
> the curse of dynamic sql.
>
> process inside a cursor:
> ------------------------
> exec master..xp_cmdshell 'bcp "exec sp_helptext '+@spName+'" queryout
> '+@spName+'.txt -c -SmyServerName -Usa -PmyPwd'
>
> Error = [Microsoft][ODBC SQL Server Driver]Function sequence error

The above is not legal SQL; you cannot have expressions as parameters
to stored procedure.

But you are really in a dead end. You could get the thing to work, but
the files you get could still be mashed. The code is stored in
syscomments as a sequence of varchar(8000), and a token may be split
over two rows. sp_helptext makes some attempts to repair this, but if
there are comments in the beginning, it's a complete failure.

Using SQL-DMO is probably your most efficient way.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]