BULK INSERT from secured share

Hello,
I am trying to use BULK INSERT to import data from a file that resides
on a shared drive. I've read many posts about this, but my situation
has a slight wrinkle (or two). The network people here have granted one
account that has access to this shared drive and I can map to that
drive using that user name and password:
NET USE X: \\PCName\Share password /user:username.
When run this command through a job I can then access the files through
DTS. Of course, for BULK INSERT it uses the SQL Server service account,
which is different from the SQL Server Agent service account, so it
can't access that drive (even as a UNC) because it has been mapped by a
different user. Am I correct so far?
I suspect that if I could use xp_cmdshell that I could map it with that
and the share would be available through BULK INSERT. I'm going to set
up a test on a local box to test this just for my own knowledge, but of
course on the production server xp_cmdshell is forbidden.
Possible solutions that I've come up with are:
1. Convince the network people to grant access to that drive to the SQL
Server service account (wish me LOTS of luck on that one
2. Convince the SQL Server guys that they should allow xp_cmdshell (ok,
that is impossible no matter how much luck you wish me)
3. Copy the files to the local box using a job (I'd like to avoid that
if possible)
Any other ideas?
Oh yeah, this is SQL Server 2000 on a Windows 2003 server. I don't
really have any access to the OS of the server and even my access to
the SQL side of things is pretty limited.
Thanks!
-Tom.You might be able to make use of OPENROWSET to read the text file,
instead of using DTS.|||Tracy McKibben wrote:
> You might be able to make use of OPENROWSET to read the text file,
> instead of using DTS.
Thanks, I'll look into that. Any idea on how I would set up the
connection string? I'm not familiar with doing a "connect as" through
an OLE DB provider for text files.
Thanks,
-Tom.|||Offhand, no, I'd have to look around for some examples - lots of them
on the web.
I don't think you'll need to do any sort of "connect as". You should
be able to specify the UNC path to the text file, and when you run the
code from an Agent job, it will run (and connect) under the context of
the SQL Agent user.

Bulk Copy Terror

Hi there,

I'm in dire need for a solution to a "Bulk Insert" problem.

Environmental: SQL 7
The unc path used is a shared folder on the local machine with full rights.

This is the SQL Table:

CREATE TABLE [dbo].[ArgiefTransaksies] (
[TakDepotKodeRef] [char] (4) NULL ,
[HoofraamNommer] [varchar] (2) NULL ,
[Dokumentnommer] [decimal](2, 0) NULL ,
[DataLyn] [varchar] (133) NULL)

Here is the file that I try to upload, TESTFILE.PRN

RZGB|FT|12| |\n
RZGB|FT|12| |\n
RZGB|FT|12| |\n
RZGB|FT|12| |\n
RZGB|FT|12|This is a test|\n
RZGB|FT|12| |\n
RZGB|FT|12|Zone 1 Zone 2|\n
RZGB|FT|12|Hello World||\n
RZGB|FT|12| 5 leading spaces|\n
RZGB|FT|12| Tab(10) - Hello|\n

Here is the SQL statement that I try to use:

Exec sp_dboption 'VKBTakDev', 'select into/bulkcopy', 'TRUE'

BULK INSERT ArgiefTransaksies
FROM '\\HKDATA060XP\Public\TESTFILE.PRN'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n',
MAXERRORS = 0,
CODEPAGE = 'raw'
)

Here is the error that I keep getting...

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.

Regardshttp://support.microsoft.com/default.aspx?scid=kb;en-us;q324122 to workaround the issue.|||Have you tried BCP? See what happens when you go through the BCP wizard and create a format file. If it succeeds try to use it in BULK INSERT.