Re: Permissions required to use Bulk Load with SQL Server 2005
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 17 Feb 2006 06:27:51 -0600
Is there a way of granting the SQL Server User access rights to read from
the file system of another server?
Yes but this is problematic when the servers aren't in a domain.
Rather than use OPENROWSET...BULK for this task, you might find it easier to
read the file contents and insert from your application code. If the file
is reasonably sized, you can do this in a single insert:
cmd2.CommandText = new SqlCommand(
"INSERT INTO RMXemail VALUES(@FileContents);", connection);
SqlParameter parameter = command.Parameters.Add("@FileContents",
SqlDbType.Xml);
System.IO.StreamReader sr = new
StreamReader(@"c:/INetPub/wwwroot/LCNETSQL/Emails/sample.xml");
parameter.Value = sr.ReadToEnd();
sr.Close();
cmd2.ExecuteNonQuery();
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Andrew Chilcott" <Andrew Chilcott@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:2A1B7DDC-A2F6-43FF-ADA2-E5010476C49D@xxxxxxxxxxxxxxxx
I have successfully created a new table in SQL Server 2005 that holds just
two fields, an identity field and an XML field. Using the following code,
I
have also successfully loaded an XML file from the local file system into
the table:
cmd2.CommandText = "INSERT INTO RMXemail SELECT rmxemail FROM (SELECT *
FROM
OPENROWSET (BULK 'c:/INetPub/wwwroot/LCNETSQL/Emails/sample.xml',
SINGLE_BLOB) AS rmxemail) AS R(rmxemail);"
So far so good. Now to deploy it.
The server on which the website is loaded does not have SQL Server 2005
installed - it is on a separate server. My ISP has provided a connection
string to access the database from the website and this works perfectly
with
me being able to access the data using ADO. However, when the above code
is
run I get the following error message:
Microsoft OLE DB Provider for SQL Server error '80040e14'
You do not have permission to use the bulk load statement.
/rmxemail.asp, line 35
and the following comment from my ISP
"I think the problem is that the SQL Server user needs permissions on the
file you are trying to read from. Unfortunately, because the SQL Server
user
is local to the server and the servers do not share a common domain, I
can't
grant these permissions.
I think we will need to move your site to the same server as the SQL
database, unless you can find some documentation which states how we can
set
permissions for the SQL 2005 user on one server to access files on another
server."
Is there a way of granting the SQL Server User access rights to read from
the file system of another server?
.
- Prev by Date: [SQL2000] permissions to use view based on tables from many databases
- Next by Date: Re: Move 10 DBs and provide dev access thru EM?
- Previous by thread: [SQL2000] permissions to use view based on tables from many databases
- Next by thread: Backup-Restore on Encrypted Columns
- Index(es):
Relevant Pages
|
|
