returning success of exec(@sql) into a variable

i'm trying to build up the following dynamically
EXEC @.result =master..xp_cmdshell 'dir c:\test\D0004' , no_output
print @.result
so far i have got this..
set @.strsql = 'exec master..xp_cmdshell '+"'"+' dir c:\test\'+@.foldername +
+"'"+' , no_output'
exec(strsql)
this work fine to execute, but i cannot find anyway of returning the
success/failure into the variable @.result
Please helpYou can use sp_executesql with an output parameter for this...
declare @.strsql nvarchar(200)
declare @.result int
set @.strsql = 'exec @.result = master..xp_cmdshell '+"'"+' dir
c:\test'+@.foldername +
+"'"+' , no_output'
exec sp_executesql @.strsql, '@.Result int OUTPUT', @.Result OUTPUT
print @.result
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"mat" <mat@.discussions.microsoft.com> wrote in message
news:ADCB55FF-0912-4AFA-9747-BAEF494F6CAD@.microsoft.com...
> i'm trying to build up the following dynamically
> EXEC @.result =master..xp_cmdshell 'dir c:\test\D0004' , no_output
> print @.result
> so far i have got this..
> set @.strsql = 'exec master..xp_cmdshell '+"'"+' dir c:\test'+@.foldername
+
> +"'"+' , no_output'
> exec(strsql)
> this work fine to execute, but i cannot find anyway of returning the
> success/failure into the variable @.result
> Please help|||Thanks, I'm running this and butt i'm getting the error message
Server: Msg 214, Level 16, State 3, Procedure sp_executesql, Line 38
Procedure expects parameter '@.parameters' of type 'ntext/nchar/nvarchar'.
"Adam Machanic" wrote:

> You can use sp_executesql with an output parameter for this...
> declare @.strsql nvarchar(200)
> declare @.result int
> set @.strsql = 'exec @.result = master..xp_cmdshell '+"'"+' dir
> c:\test'+@.foldername +
> +"'"+' , no_output'
> exec sp_executesql @.strsql, '@.Result int OUTPUT', @.Result OUTPUT
> print @.result
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "mat" <mat@.discussions.microsoft.com> wrote in message
> news:ADCB55FF-0912-4AFA-9747-BAEF494F6CAD@.microsoft.com...
> +
>
>|||Should have used the 'national' designator for the @.parameters string
literal:
exec sp_executesql @.strsql, N'@.Result int OUTPUT', @.Result OUTPUT
That tells SQL Server that the string is Unicode.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mat" <Mat@.discussions.microsoft.com> wrote in message
news:02ACC243-14F2-4A23-A5E4-C88CFE09EF74@.microsoft.com...[vbcol=seagreen]
> Thanks, I'm running this and butt i'm getting the error message
> Server: Msg 214, Level 16, State 3, Procedure sp_executesql, Line 38
> Procedure expects parameter '@.parameters' of type 'ntext/nchar/nvarchar'.
> "Adam Machanic" wrote:
>
c:\test'+@.foldername[vbcol=seagreen]