Wednesday, March 7, 2012

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...
> > 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
>
>|||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...
> 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...
> > > 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
> >
> >
> >

No comments:

Post a Comment