Re: returning success of exec(@sql) into a variable

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 11/12/04 

Date: Fri, 12 Nov 2004 11:20:12 -0500

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