Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Load table from network drive

3 posts in General Discussion Last posting was on 2006-06-23 14:05:34.0Z
Steve Rhiner Posted on 2006-06-23 02:56:32.0Z
Reply-To: "Steve Rhiner" <rhiner4022000_removethisbit@yahoo.com>
From: "Steve Rhiner" <rhiner4022000_removethisbit@yahoo.com>
Newsgroups: ianywhere.public.general
Subject: Load table from network drive
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
NNTP-Posting-Host: ool-4356539c.dyn.optonline.net
X-Original-NNTP-Posting-Host: ool-4356539c.dyn.optonline.net
Message-ID: <449b5860$1@forums-1-dub>
Date: 22 Jun 2006 19:56:32 -0700
X-Trace: forums-1-dub 1151031392 67.86.83.156 (22 Jun 2006 19:56:32 -0700)
X-Original-Trace: 22 Jun 2006 19:56:32 -0700, ool-4356539c.dyn.optonline.net
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5460
Article PK: 1505

Ok, I get that SQLA runs as a localService and doesn't have access to UNC
drives.

I have an FTP server and I want people to FTP files and then load them into
the database. The FTP server and the SQLA server are on different
machines.

My approach idea had been LOAD TABLE.... FROM '\\\\ftpserver\\....' and
that doesn't work.

So, what is the right way to do this? I don't really want to change the
default user for the SQLA service -- its a stable production server and that
is reason enough not to mess with it.

A good solution is one that actually worked.... a perfect solution would be
one that is triggered upon the receipt of an FTP transfer.

TIA


Nick Elson Posted on 2006-06-23 14:05:34.0Z
From: "Nick Elson" <no_span_nicelson@sybase.com>
Newsgroups: ianywhere.public.general
References: <449b5860$1@forums-1-dub>
Subject: Re: Load table from network drive
Lines: 64
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
NNTP-Posting-Host: nicelson-m20.sybase.com
X-Original-NNTP-Posting-Host: nicelson-m20.sybase.com
Message-ID: <449bf52e$1@forums-1-dub>
Date: 23 Jun 2006 07:05:34 -0700
X-Trace: forums-1-dub 1151071534 10.25.98.215 (23 Jun 2006 07:05:34 -0700)
X-Original-Trace: 23 Jun 2006 07:05:34 -0700, nicelson-m20.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5464
Article PK: 1509


> Ok, I get that SQLA runs as a localService and doesn't have access to UNC
> drives.
. . .
> My approach idea had been LOAD TABLE.... FROM '\\\\ftpserver\\....' and
> that doesn't work.

That's an issue of Windows security. It is pretty common limitation when
running services as (the so called) LocalSystem.Your service is running
with a user account and that user must have permissions in the domain and
on the target system as well. Right off the back that eliminates the local
SYSTEM account, and, usually, any other local 'named' account. Typically
the service needs to run with a domain account (as in yahoo\rhiner with NT
Domains or rhiner@yahoo.com if ADS) and then network access permissions
must be granted to that account for the shares and directories referenced by
the UNC name.

> So, what is the right way to do this? I don't really want to change the
> default user for the SQLA service -- its a stable production server and
> that
> is reason enough not to mess with it.

Then it is MS Windows is stopping you, not anything else.

> A good solution is one that actually worked.... a perfect solution would
> be
> one that is triggered upon the receipt of an FTP transfer.

Another **even better** option would be to schedule runs of dbisql to
INPUT those tables. Since the files are physically remote there may be
little
performance benefit from running LOAD TABLE statements in the server.
Further options may be available in the FTP software [if you are up to using
the GNU projects to write a solution for this yourself] or find 3rd party
FTP server software that supports scripting to detect those arrivals and
launch dbisql.


"Steve Rhiner" <rhiner4022000_removethisbit@yahoo.com> wrote in message
news:449b5860$1@forums-1-dub...
> Ok, I get that SQLA runs as a localService and doesn't have access to UNC
> drives.
>
> I have an FTP server and I want people to FTP files and then load them
> into
> the database. The FTP server and the SQLA server are on different
> machines.
>
> My approach idea had been LOAD TABLE.... FROM '\\\\ftpserver\\....' and
> that doesn't work.
>
> So, what is the right way to do this? I don't really want to change the
> default user for the SQLA service -- its a stable production server and
> that
> is reason enough not to mess with it.
>
> A good solution is one that actually worked.... a perfect solution would
> be
> one that is triggered upon the receipt of an FTP transfer.
>
> TIA
>
>


Rob Waywell Posted on 2006-06-23 13:40:04.0Z
From: "Rob Waywell" <rwaywell_no_spam_please@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <449b5860$1@forums-1-dub>
Subject: Re: Load table from network drive
Lines: 60
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: rwaywell-xp2.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-xp2.sybase.com
Message-ID: <449bef34$1@forums-1-dub>
Date: 23 Jun 2006 06:40:04 -0700
X-Trace: forums-1-dub 1151070004 10.25.98.235 (23 Jun 2006 06:40:04 -0700)
X-Original-Trace: 23 Jun 2006 06:40:04 -0700, rwaywell-xp2.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5462
Article PK: 1511

The ASA server doesn't have to run under LocalSystem. You can set up the
service to run under a specific OS level user ID, you just need to assign
that OS level user ID permissions to log on as a service. If you then assign
that OS level user ID permissions on the file server that you want to import
the data from, you'll be able to run your LOAD TABLE command.

NOTE: Since you will be running the LOAD TABLE against a different machine,
I would recommend including the WITH CHECKPOINT ON clause. Should the
database go down and need to recover across the LOAD TABLE statement, then
it requires access to the original file that was loaded. Since the recovery
process starts from the last checkpoint, forcing a CHECKPOINT as part of the
LOAD TABLE operation minimizes the time period in which you need to keep
that source file available.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Steve Rhiner" <rhiner4022000_removethisbit@yahoo.com> wrote in message
news:449b5860$1@forums-1-dub...
> Ok, I get that SQLA runs as a localService and doesn't have access to UNC
> drives.
>
> I have an FTP server and I want people to FTP files and then load them
> into
> the database. The FTP server and the SQLA server are on different
> machines.
>
> My approach idea had been LOAD TABLE.... FROM '\\\\ftpserver\\....' and
> that doesn't work.
>
> So, what is the right way to do this? I don't really want to change the
> default user for the SQLA service -- its a stable production server and
> that
> is reason enough not to mess with it.
>
> A good solution is one that actually worked.... a perfect solution would
> be
> one that is triggered upon the receipt of an FTP transfer.
>
> TIA
>
>