If you have a security problem with the BULK Insert command, your best
chance of getting an appropriate answer would be to ask in a newsgroup
dedicated to SQL-Server such as microsoft.public.sqlserver.security or
m.p.sqlserver.server or m.p.sqlserver.programming.
Also, this newsgroup is about ADP and SQL-Server and has nothing to do with
MDB or ACCDB database file and ODBC Linked tables or Passthrough queries.
In your case, if the account that you are using for connecting with
SQL-Server Management Studio works then you should use the same for
executing your passthrough query.
A second point to consider is that drive letters for shared networking (such
as D:\) are strongly associated with the login account and the fact that D:\
has been defined for your login account and/or the account used to run the
sql-server service doesn't mean that it will still be available under the
security context that will run your SP. You should never use a driver
letter for a shared network and always the full network name.
As an example; the following code with create an anonymous query with the
login account of your choice and with a full network share name for the
file:
Dim strSQL As String
Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Dim Qdf As DAO.QueryDef
Dim strConnect As String
strConnect = "ODBC;Driver={SQL Server};Server=YourServer;" & _
"Database=YourDB;uid=you;pwd=secret;"
strSQL = "BULK INSERT CSVTest FROM '\\server\share\path\csvtest.txt' " & _
"WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) "
Set Qdf = CurrentDb.CreateQueryDef("")
With Qdf
.Connect = strConnect
.ReturnsRecords = False
.ODBCTimeout = 0
.SQL = strSQL
.Execute dbSQLPassThrough + dbSeeChanges
.Close
End With
Set Qdf = Nothing
In this case, I've used Driver={SQL Server} as the ODBC driver but you
should take the most recent ODBC for SQL-Server that you can find on your
system, for example the SQL-Server Native Driver if you have installed
SQL-Server 2005. For a list of drivers, see:
http://www.connectionstrings.com/?carrier=sqlserver
http://www.connectionstrings.com/?carrier=sqlserver2005
Another way of solving the security problem would be to set up a linked
server to your CVS file. This way, you can set up the account to open the
file directly on the SQL-Server.
Finally, even 4 minutes can still be a long time to load the file if you
have indexes on this table.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"NKTower" <(E-Mail Removed)> wrote in message
news:5569300F-8B0E-49BF-AB7A-(E-Mail Removed)...
> Synopsis: I'm trying to launch an SP that makes use of BULK INSERT and
> getting permissions errors.
>
> I have a stored proc - dbo.p_Import_Sales_Transactions which takes as its
> single argument the path to where the data resides, having been created by
> another application. (There are 4 tab-delimited files there, ready to
> load.)
> The data has been placed on a drive local to the server. The stored proc
> truncates a set of intermediate tables, then does a set of four BULK
> INSERT
> statements. When run from SQL Server Management Studio, it runs fine.
>
> My need is to launch the proc from an Access application running on a set
> of
> client mahcines, with a pass-through query containing:
>
> EXEC dbo.p_Import_Sales_Transactions 'D:\stage\'
>
> where D:\stage\ is the path to the share from the server's viewpoint.
>
> The Access application is linked to the database via an ODBC connection,
> with SQL Server account authentication. I've no control over this.
>
> When I try to execute the stored proc from Access, I get
>
> You do not have permission to use the bulk load statement (#3834)
>
> I've Google'd MS and various other places but the few things that I found
> were incomprehensible to me. I'm a developer, not a DBA, and the client
> has
> no resident SQL Server DBA - Oracle yes, MS no.
>
> I have tried creating a SQL Server account 'bulk_inserter' and have found
> a 'bulkadmin' role that I have associated with that user. 'bulk_inserter'
> has dbo as default schema. I then added WITH EXECUTE AS 'bulk_inserter'
> to
> the preamble of the stored proc, which it accepts when I execute the ALTER
> but it doesn't like at run-time.
>
> So I'm down to either of two errors:
>
> 'bulk_inserter' oesn't have permission in current security context.
> or
> You do not have permission to use the bulk load statement (#3834)
>
> I feel that I'm close, but not quite there.
>
> Surely someone else is doing this - in testing via Management Studio
> interface, BULK INSERT does in 4 minutes what DoCmd.TransferText takes 40+
> minutes to do.
Similar Threads
