API calls from within a Stored Procedure?

Folks, I would like to convert some macros into a stored procedure. The ide
a
is to read file names out of a database and copy them from their current
location to specified directories. Can an API call be made from a SP to
prompt for a root directior (via file dialog), create a directory, and copy
a
file as its steping through a record set?
Suggestions will be greatly appreciated on how to do this.You can use extended stored procedures, although I believe you're not
supposed to have any UI on an XP. You could write a stored proc to read the
filenames from your database, and take advantage of the xp_cmdshell stored
procedure to move them to a destination directory. UI implementation is
really a whole 'nother layer from SQL Server, and you'd probably do better
to write a little .NET or other program to select the directory; although
you could create a stored proc to do the actual copy for you.
"Crisp" <Crisp@.discussions.microsoft.com> wrote in message
news:5E816E07-01D6-43F6-8E6C-E54E02BDB943@.microsoft.com...
> Folks, I would like to convert some macros into a stored procedure. The
> idea
> is to read file names out of a database and copy them from their current
> location to specified directories. Can an API call be made from a SP to
> prompt for a root directior (via file dialog), create a directory, and
> copy a
> file as its steping through a record set?
> Suggestions will be greatly appreciated on how to do this.|||You want to use SQL Server to move files around ?
including prompting a user for input?
This would be a gross misuse of the tool, it would be more appropriate and
much easier to do this in any ordinary programming language. You can still
put the file list in SQL if you want, and have application code get the list
from the SQL database, but having SQL do the File operations, or prompt user
s
for input, would be much too difficult, and inappropriate.
In fact, the only solution I can think of, is really a combination of having
SQL kick off an external process that does the actual "prompting and gather
user input" function, and then does the FIle IO... to do that you need to
write a COM-Component tool in a COM-Capable language, and call it from SQL
using built-in specially designed System procesdures. (Investigate the set
of built-in Stored Procs called sp_OACreate, sp_OAMethod, etc. that allow
you to create, use, and destroy COM components from inside SQL.)
But don't use these for any application where scaleability is a concern,
because they are notoriously NOT scaleable.
"Crisp" wrote:

> Folks, I would like to convert some macros into a stored procedure. The i
dea
> is to read file names out of a database and copy them from their current
> location to specified directories. Can an API call be made from a SP to
> prompt for a root directior (via file dialog), create a directory, and cop
y a
> file as its steping through a record set?
> Suggestions will be greatly appreciated on how to do this.|||I agree with you that the user input function and prompting would be a
misuse of SQL Server. The OP didn't specify this, but I was thinking along
the lines of administering the file move from a client machine? I.e., on my
desktop computer I want to move files on my SQL Server from C:\test_data to
D:\new_data. SQL Server makes it relatively easy to get a directory listing
and perform command shell operations via extended proc's, without setting up
and administering shares all over your network, or creating your own
specialized client/server app.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:D04DB710-B550-4F04-A98D-19FA8F2B3688@.microsoft.com...
> You want to use SQL Server to move files around ?
> including prompting a user for input?
> This would be a gross misuse of the tool, it would be more appropriate and
> much easier to do this in any ordinary programming language. You can
> still
> put the file list in SQL if you want, and have application code get the
> list
> from the SQL database, but having SQL do the File operations, or prompt
> users
> for input, would be much too difficult, and inappropriate.
> In fact, the only solution I can think of, is really a combination of
> having
> SQL kick off an external process that does the actual "prompting and
> gather
> user input" function, and then does the FIle IO... to do that you need to
> write a COM-Component tool in a COM-Capable language, and call it from SQL
> using built-in specially designed System procesdures. (Investigate the
> set
> of built-in Stored Procs called sp_OACreate, sp_OAMethod, etc. that allow
> you to create, use, and destroy COM components from inside SQL.)
> But don't use these for any application where scaleability is a concern,
> because they are notoriously NOT scaleable.
>
> "Crisp" wrote:
>|||Well then it is certainly possible to write a SP that you would call from
your desktop, passing in acomplete source FileSpec, and destination FileSpec
,
as Local (Local to SQL Serevr) File Specfications, that would copy the file
from one folder \FileName to aspecified Folder\Filename... S(Sounds like yo
u
already know about xp_cmdShell)
You would still need to ensure that the Process ID the SQL Server was
running under had the appropriate permissions on the local file system.
The only thing this approach saves you is creating of user access
permissions and controlled network shares on the SQL Server across the
network...
"Michael C#" wrote:

> I agree with you that the user input function and prompting would be a
> misuse of SQL Server. The OP didn't specify this, but I was thinking alon
g
> the lines of administering the file move from a client machine? I.e., on
my
> desktop computer I want to move files on my SQL Server from C:\test_data t
o
> D:\new_data. SQL Server makes it relatively easy to get a directory listi
ng
> and perform command shell operations via extended proc's, without setting
up
> and administering shares all over your network, or creating your own
> specialized client/server app.
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:D04DB710-B550-4F04-A98D-19FA8F2B3688@.microsoft.com...
>
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:12A44395-01F8-44C7-996E-EDAC39490BCF@.microsoft.com...
> Well then it is certainly possible to write a SP that you would call from
> your desktop, passing in acomplete source FileSpec, and destination
> FileSpec,
> as Local (Local to SQL Serevr) File Specfications, that would copy the
> file
> from one folder \FileName to aspecified Folder\Filename... S(Sounds like
> you
> already know about xp_cmdShell)
> You would still need to ensure that the Process ID the SQL Server was
> running under had the appropriate permissions on the local file system.
> The only thing this approach saves you is creating of user access
> permissions and controlled network shares on the SQL Server across the
> network...
Designated network shares restrict the OP's solution to copying files from
pre-defined Point A's to pre-defined Point B's, and he seemed to hint that
he wanted to be able to have a choice of source and destination; although
I'm not sure exactly how many choices he wants. For only one or two
destinations, the network shares solution would definitely be the way to go.
But if you know there are going to be a lot of sources and destinations,
like each user has his/her own set of source and destination folders, a more
dynamic approach might be needed. A front-end file selection utility like
the File Attach interface in EM is not that hard to create in VB.NET or
C#.NET using SQL Server xp's. I guess it all really depends on a more
specific set of requirements from the OP to decide what solution would be
best for his/her needs. I think we both agree that trying to use SQL Server
as a UI engine is a horrible idea.