Hi All
Another simple one perhaps?
We occasionally load up a text file with a lenghty series of one-time SPs,
UPDATEs, etc that we need to run on customer dbs. We have been using
Interactive SQL's Read command to manually execute this 'external' SQL (i.e.
keying in 'read from c:\command_file.txt' ), but life would be better if we
could use a SP to process the commands in the text file-- like the 'Read'
command, but without the need for ISQL tool. To date, we haven't found the
silver bullet...is there one?

BH

I think the first question to ask is what the contents of the SQL file would
be. iSQL does quite a bit of parsing and processing of some SQL commands
before passing them to the engine, for instance the INPUT command involves
iSQL loading the contents of the file and sending it to the engine. Compare
this with LOAD TABLE, which is done without any iSQL involvement.

There are ways for SP's to access the outside world, such as through
xp_read_file and use of the EXECUTE IMMEDIATE command. Some things to think
about before doing something like this:

(*) iSQL-specific commands will not be available (as described earlier)
(*) SP's execute with the authority of it's owner, if you are using
xp_read_file then your SP is probably owned by DBA and running with DBA
rights, you want to be very careful when you execute arbitrary commands with
DBA authority.
(*) SP's run from the context of the server machine, if you access
"c:\something", then you will be trying to access something on the
_server's_ local hard drive.
(*) If your database server is installed as a Win32 service, it most likely
has no rights to access anything on network drives.

"BH" <bohiggy (AT) yahoo (DOT) com> wrote