RE: Function writting challange!!

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 12/06/04 

Date: Mon, 6 Dec 2004 11:51:02 -0800

You can not execute sp_executesql from inside a function (may be there is
back door), but the question here is why a function and not a stored
procedure?

AMB

"Patrick" wrote:

> Hi Freinds,
> SQL 2000
> I need to have this as a function.
>
> -----------------------------
> declare @dr nvarchar(100)
> set @dr = 'c:\compaq\'
>
> create table dirlist (filename varchar(8000))
>
> declare @qq nvarchar(1000)
> set @qq = 'insert into dirlist(filename) exec master..xp_cmdshell ''dir
> '+@dr+''''
> select @qq
> exec sp_executesql @qq
>
>
> delete dirlist where
> substring(filename,1,2) < '00' or
> substring(filename,1,2) > '99' or
> filename is NULL
>
> select * from dirlist
>
>
>

Relevant Pages

  • Re: Application role to access xp_cmdshell
    ... This necessitates that your user procs be owned ... See Cross-database chaining in the SQL 2000 ... You will also need to allow non-sysadmin users to execute xp_cmdshell. ... EXEC sp_dboption 'MyDatabase', 'db chaining', true ...
    (microsoft.public.sqlserver.security)
  • xp_sqlagent_proxy_account: Specified user cannot login
    ... I am trying to configure SQL so that a user without admin privileges can ... execute xp_cmdshell. ... EXEC master..xp_sqlagent_proxy_account N'SET' ...
    (microsoft.public.sqlserver.security)
  • Re: Need to make Sort ORDER in SP defined by Input variable
    ... If I enter 'ISBN' when I execute the SP below, it executes, ... SET NOCOUNT ON ... DECLARE @SQL nvarchar ... > exec sp_executesql @SQL ...
    (microsoft.public.sqlserver.programming)
  • Re: use of INSERT with Dynamic SQL
    ... > I supose the only way is to use dynamic SQL. ... > Is there a way to execute an INSERT statemente with dynamic SQL? ... > EXEC spInsertMyTable ...
    (microsoft.public.sqlserver.programming)
  • set @sql = Ncreate table #tmp_table(guarid int)
    ... When I execute: ... exec sp_executesql @sql ... if I change the temp table to a global table it runs ...
    (microsoft.public.sqlserver.programming)
Loading