无法批量加载.文件& quot;' + @imagepath +'& quot;不存在或您没有文件访问权限 [英] Cannot bulk load. The file "'+ @imagepath +'" does not exist or you don't have file access rights

问题描述

我正在尝试在SQL Server中插入图像，但出现此错误:

I'm trying to insert an image in SQL server but I got this error :

无法批量加载.文件``'+ @ imagepath +'''不存在，或者您没有文件访问权限.

Cannot bulk load. The file "'+ @imagepath +'" does not exist or you don't have file access rights.

Create procedure insert_test
AS
BEGIN
INSERT test(
   Id,
logo

)
     VALUES (
         @Id,
(SELECT  BulkColumn
    FROM Openrowset(Bulk '''+ @imagepath +''', Single_Blob) as img),
END

执行代码

EXEC    @return_value = [dbo].[test_insert]
    @Id = N'0001',
    @imagepath = 'D:\\heart.png',

SELECT  'Return Value' = @return_value

GO

如何解决此错误?

更新:

这是我为使用它而创建的过程的示例:

This is an example of the procedure that I have created to use it:

USE [SysTest]
GO


ALTER PROCEDURE [dbo].[userinformations_insert]
@UserId nvarchar(55),
@UserName nvarchar(max),
@UserPhone int ,
@UserAdress int,
@UserEmail nvarchar(50),
@imagepath nvarchar(100),


AS
BEGIN

IF EXISTS ( SELECT 1 from User)
BEGIN

   update User  set userPicture = (SELECT  BulkColumn
        FROM Openrowset(Bulk '''+ @imagepath +''', Single_Blob) as img) , userEmail = @userEmail, userPhone = @userPhone, userAdress = @userAdress where userId =@UserId
END
ELSE
BEGIN
    INSERT User(
    userId,
    UserName ,
    userPicture
    userEmail,
    userPhone,
    userAdress,
    )
         VALUES (
             @UserId,
    @UserName ,
    (SELECT  BulkColumn
        FROM Openrowset(Bulk '''+ @imagepath +''', Single_Blob) as img),
    @UserEmail,
    @UserPhone,
    @UserAdress)
END
END

推荐答案

正如我在评论中所说，" OPENROWSET需要文字字符串.您需要使用动态SQL并安全将值注入其中." :

As I said in the comment "OPENROWSET requires a literal string. You'll need to use dynamic SQL and safely inject the value in.":

DECLARE @imagepath nvarchar(255) = N'C:\Temp\YourImage.png', --Obviously you have set elsewhere.
        @Id int = 1; --Obviously you have set elsewhere.

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'INSERT INTO dbo.test(Id, logo)' + @CRLF +
           N'SELECT @Id, BulkColumn' + @CRLF +
           N'FROM OPENROWSET(BULK '''+ REPLACE(@imagepath,'''','''''') +''', SINGLE_BLOB) AS img;';

EXEC sys.sp_executesql @SQL, N'@Id int', @Id; --I have guessed the datatype of @Id

---

OP似乎不熟悉如何CREATE或ALTER一个过程.考虑到他们在问题中有CREATE陈述，但没关系，我必须承认我觉得很奇怪.

---

It seems to OP isn't familiar with how to CREATE or ALTER a procedure; I must admit I find this odd considering that they have a CREATE statement in their question, but nevermind.

要创建或更改过程，请使用CREATE或ALTER命令；在最新版本中，也有CREATE OR ALTER.

To create or alter a procedure you use the CREATE or ALTER command; in more recent versions there is also CREATE OR ALTER as well.

简单来说，在psuedo SQL中是这样的:

In simple terms it's like this in psuedo SQL:

CREATE OR ALTER PROC {Your Procedure Name} {Parameters} AS
BEGIN

    {Stored Procedure SQL statements}
END;

因此，您可能需要一个返回特定客户ID的存储过程.那会是

So, you might want a stored procedure that returns for a specific customer ID; that would be

CREATE OR ALTER PROC dbo.GetCustomer @ID int AS
BEGIN

    SELECT CustomerName,
           CustomerEmail,
           CustomerPhone
    FROM dbo.Customer
    WHERE ID = @ID;
END;

对于我所拥有的，只需执行以下操作:

For what I have you, just do this:

CREATE OR ALTER PROC  dbo.InsertImage @ID int, @imagepath nvarchar(255) AS
BEGIN 
    DECLARE @SQL nvarchar(MAX),
            @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
    SET @SQL = N'INSERT INTO dbo.test(Id, logo)' + @CRLF +
               N'SELECT @Id, BulkColumn' + @CRLF +
               N'FROM OPENROWSET(BULK '''+ REPLACE(@imagepath,'''','''''') +''', SINGLE_BLOB) AS img;';

    EXEC sys.sp_executesql @SQL, N'@Id int', @Id; --I have guessed the datatype of @Id

这篇关于无法批量加载.文件& quot;' + @imagepath +'& quot;不存在或您没有文件访问权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！