批量插入问题
我有一个存储过程，可以从某个文件中进行批量插入：
CREATE PROCEDURE [dbo].[SP_BulkInsert] @FileName NVARCHAR(200) AS
BEGIN
DECLARE @bulkinsert NVARCHAR(1000)
SET @bulkinsert = N'BULK INSERT TblTemp FROM ''' + @FileName +
N''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'
EXEC sp_executesql @bulkinsert
RETURN @@ROWCOUNT
END
当我从 SQL Server Management Studio 运行该存储过程时，该存储过程运行良好，但是当我尝试使用 ADO.NET 的 ExecuteNonQuery 运行它时，我得到了出现以下错误：
“数据库对象“TblTemp”的 INSERT 权限被拒绝 “TempDB”，架构“dbo”。”
重要提示：所有其他存储过程（使 SELECT/INSERT/DELETE/UPDATE）都可以从 ADO.NET 正常运行。
运行所有内容的用户是 bulkadmin 角色的成员，也是自定义 db_executer 角色的成员（仅具有 EXECUTE 权限）。
该代码对于许多存储过程都运行良好，这是第一次失败.. 功能
public static int BulkInsert(string fileName)
{
SqlParameter paramFileName = new SqlParameter("FileName", fileName);
SqlParameter paramRetValue = new SqlParameter();
paramRetValue.Direction = ParameterDirection.ReturnValue;
SqlParameter[] @parameters = { paramFileName, paramRetValue };
SqlHelper.ExecuteNonQuery(ConnectionSettings.ConnectionString,
CommandType.StoredProcedure, "SP_BulkInsert", parameters, true);
return (int)paramRetValue.Value;
}
这是我使用 ADO.NET 端连接字符串中相同的用户名/密码登录 SSMS 的
。最根本的问题是，为什么在 Management Studio 中存储过程成功，而通过 ADO.NET 却失败（并显示上述错误消息）。
I have a stored procedure that makes bulk insert from some file:
CREATE PROCEDURE [dbo].[SP_BulkInsert] @FileName NVARCHAR(200) AS
BEGIN
DECLARE @bulkinsert NVARCHAR(1000)
SET @bulkinsert = N'BULK INSERT TblTemp FROM ''' + @FileName +
N''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')'
EXEC sp_executesql @bulkinsert
RETURN @@ROWCOUNT
END
This stored procedure runs fine when I run it from SQL Server Management Studio, but when I try to run it with ExecuteNonQuery of ADO.NET I get the following error:
"The INSERT permission was denied on the object 'TblTemp', database
'TempDB', schema 'dbo'."
Important: all other stored procedures (that make SELECT/INSERT/DELETE/UPDATE) run fine from ADO.NET.
The user under which all the things are run is a member of the bulkadmin role, and also a member of a custom db_executer role (that has just EXECUTE permission).
The code runs fine for a lot of stored procedures, it's a first time that it fails..
This is the function
public static int BulkInsert(string fileName)
{
SqlParameter paramFileName = new SqlParameter("FileName", fileName);
SqlParameter paramRetValue = new SqlParameter();
paramRetValue.Direction = ParameterDirection.ReturnValue;
SqlParameter[] @parameters = { paramFileName, paramRetValue };
SqlHelper.ExecuteNonQuery(ConnectionSettings.ConnectionString,
CommandType.StoredProcedure, "SP_BulkInsert", parameters, true);
return (int)paramRetValue.Value;
}
I logging into the SSMS with the same username/password that are in the connection string on ADO.NET side..
The bottom question is, why in Management Studio the stored procedure succeed, while via ADO.NET it fails (with the above error message).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
通过 sp_executesql 运行 SQL 使用的权限与直接在存储过程中运行的权限不同。我建议检查您运行存储过程的用户是否具有（在本例中）针对表“TblTemp”的 INSERT 权限。
要在 Sql Server Management Studio 中执行此操作...
Running SQL via the sp_executesql uses different permissions than directly in the stored procedure. I would advise checking that the user which you are running the stored procedure as has (in this instance) INSERT permissions against the table "TblTemp".
To do this in Sql Server Management Studio...