I am attempting to populate a table that has a few columns, one of which must contain a PDF file (in binary).
Column names in order are:
id, int, NOT NULL IDENTITY(1,1) PRIMARY KEY,
BROKER_LOAD_NUMBER nvarchar(15) NOT NULL,
PDF_FILENAME,nvarchar(50) NOT NULL,
PETS_LOAD_NUMBER nvarchar(10) NOT NULL,
PAPERWORK varbinary(MAX) NOT NULL
I am failing miserably at this.
The code I have thus far is below but I'm receiving the following error:
Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query. I do not understand where the implicit conversion is taking place.
DECLARE @bl nvarchar
DECLARE @fn nvarchar
DECLARE @pl nvarchar
DECLARE @fp nvarchar
DECLARE @pdf varbinary(max)
SET @bl = '25373106'
SET @fn = 'TRIP - 2022001.PDF'
SET @pl = '2022001'
SET @fp = '\\DAWN-DESKTOP\COMPLETED TRIPS\2022\'
SET @pdf = ('SELECT * FROM OPENROWSET(BULK '+ @fp + @fn +', SINGLE_BLOB) as pdffile')
INSERT INTO Documents_Table (BROKER_LOAD_NUMBER, PDF_FILENAME, PETS_LOAD_NUMBER, PAPERWORK)
VALUES ('@bl', '@fn', '@pl', '@pdf');
Cannot bulk load because the file "\DAWN-DESKTOP\COMPLETED TRIPS\2022\TRIP - 2022001.PDF" could not be opened. Operating system error code 5(Access is denied.).
2: Dawn is my wife and 1/2 owner so I doubt she is leaving anytime soon!! LOL
3: Not sure what you mean by "How is this process being run?"
I will indeed try it local. I did try to take out all the filename spaces and ended with the same result but maybe that will change if I put it local.
It is going to end up being in a WinForm (part of my master Business software) and run manually (via a button) as required.
UPDATE: Renamed the file using no spaces. Placed test file on my desktop. Ensured no spaces in filename or directory structure. Still receiving: the 'Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
This statement is not executing the code, it is trying to set @pdf to a string:
SET @pdf = ('SELECT * FROM OPENROWSET(BULK '+ @fp + @fn +', SINGLE_BLOB) as pdffile')
The variables @fp and @fn are declared as nvarchar (with no defined length which also needs to be addressed). Since they are nvarchar - what you are trying to do here is convert the nvarchar string to a varbinary(max) which cannot be done implicitly.
There are other problems - your insert statement is attempting to insert strings and not the values of the variables.
What you need to do is create the OPENROWSET statement as a string, then use that string in sp_executesql to execute the statement and return the results of that statement in your variable @pdf.
Then - you can insert the results into your table. To do that you need to use:
OK, I understand defining the nvarchar fields to a defined length. I have literally no experience with sp_executesql so the remaining help is not fully understood. I understand what the issue is now that you explained it and it makes 100% sense but I have not the knowledge or understanding how to fix it.
Here is what I did, based off your assistance but it is still not working so I am still missing something and to be honest not fully understanding how to resolve the 2nd piece of your answer. I put bold text (ended up being 2 *) around what I did.
DECLARE @bl nvarchar(10)
DECLARE @fn nvarchar(15)
DECLARE @pl nvarchar(10)
DECLARE @fp nvarchar(150)
DECLARE @st nvarchar(max) = ''
DECLARE @pdf varbinary(max)
SET @bl = '25373106'
SET @fn = 'TRIP - 2022001.PDF'
SET @pl = '2022001'
SET @fp = '\\DAWN-DESKTOP\COMPLETED TRIPS\2022\'
SET @st = 'SELECT * FROM OPENROWSET(BULK '+ @fp + @fn +', SINGLE_BLOB) as pdffile'
SET @PDF = @st
exec sp_executesql @st
INSERT INTO Documents_Table (BROKER_LOAD_NUMBER, PDF_FILENAME, PETS_LOAD_NUMBER, PAPERWORK)
VALUES (@bl, @fn, @pl, @pdf);
By trying to set @PDF to @st - you just created the same issue. You need to execute the SQL statement to populate the @PDF variable.
Without using dynamic SQL - the statement would be:
SET @PDF = (SELECT * FROM OPENROWSET(BULK, '\\DAWN-DESKTOP\COMPLETED TRIPS\2022\TRIP - 2022001.PDF', SINGLE_BLOB) AS document
What you need to do is create the sql statement that you want executed. And - you need to define the variable/output you want to be returned. You can review the documentation here: sp_executesql (Transact-SQL) - SQL Server | Microsoft Learn
Inputting your SET @PDF statement into MSSMS just to test it shows a syntax error... it does not like the " '\ " for whatever reason...
Further research states that to run OPENROWSET "The operating system level file operations to read the file are executed with the privileges of the account that the SQL Server data engine is using. Therefore, only files accessible to that account may be read. This includes network drives or UNC paths, which are permitted if the account has the privileges."
I'll look over the reference you gave me but Microsoft is notorious for making things extremely hard to comprehend to the lay person with limited knowledge. A more clearer 'dummies' explanation reference would be greatly appreciated if you know of any.
I can't test the code - so that is to be expected. Embedding the slash character in a string is an issue because it is also the escape character, so putting in \ results in a single back slash which is not a valid UNC path.
What you can do to get around that is use a variable and concatenate. I would use CONCAT:
Validate the command by putting a print statement before the execute:
PRINT @sql;
As for the issue with location/permissions - if SQL Server is running as a domain account then you grant share permissions to the domain account. If SQL Server is running as the default NT SERVICE\MSSQLSERVER account - that will need to be changed to an actual domain account or you need to place the files on the server.
If SQL Server is actually running on your desktop - then you can grant the NT SERVICE\MSSQLSERVER account access to the local drives and reference the local drive path instead of UNC.
Curious:
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@pdfFile".
Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@sql".
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@sql".
Are these not already declared in the preceding lines or must I use a SET command?
