NULL output when trying to script objects with SQL-DMO

The following statement SHOULD generate a script of the specified stored
procedure. Instead, it returns NULL. Please tell me what I'm doign wrong:
DECLARE @.oServer int
DECLARE @.method varchar(300)
DECLARE @.TSQL varchar(4000)
DECLARE @.ScriptType int
EXEC sp_OACreate 'SQLDMO.SQLServer', @.oServer OUT
EXEC sp_OASetProperty @.oServer, 'loginsecure', 'true'
EXEC sp_OAMethod @.oServer, 'Connect', NULL, 'US1SQLDEV'
SET @.ScriptType = 1|4|32|64|256|262144
SET @.method = 'Databases("Compass").' +
'StoredProcedures("p_rpt_Distance").Script' +
'(' + CAST (@.ScriptType AS VARCHAR) + ')'
EXEC sp_OAMethod @.oServer, @.method ,
@.TSQL OUTPUT
SELECT @.TSQL
EXEC sp_OADestroy @.oServercongratulations, you've found the slowest possible way to do this ;)
if your sql is going to be <=4000 chars, you should just go against
syscomments. otherwise, your variable won't be big enough, anyway.
if you're dead-set on using the object junk, check the script type.
you've got the script type of 64 (To File only) set, but no file name to
output to.
CadeBryant wrote:
> The following statement SHOULD generate a script of the specified stored
> procedure. Instead, it returns NULL. Please tell me what I'm doign wrong
:
> DECLARE @.oServer int
> DECLARE @.method varchar(300)
> DECLARE @.TSQL varchar(4000)
> DECLARE @.ScriptType int
> EXEC sp_OACreate 'SQLDMO.SQLServer', @.oServer OUT
> EXEC sp_OASetProperty @.oServer, 'loginsecure', 'true'
> EXEC sp_OAMethod @.oServer, 'Connect', NULL, 'US1SQLDEV'
> SET @.ScriptType = 1|4|32|64|256|262144
> SET @.method = 'Databases("Compass").' +
> 'StoredProcedures("p_rpt_Distance").Script' +
> '(' + CAST (@.ScriptType AS VARCHAR) + ')'
> EXEC sp_OAMethod @.oServer, @.method ,
> @.TSQL OUTPUT
> SELECT @.TSQL
> EXEC sp_OADestroy @.oServer