SQLTeam.com | Weblogs | Forums

Inserting a PDF file and other info into DB

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');
  1. run the select portion by itself
SELECT * FROM OPENROWSET(BULK '\\DAWN-DESKTOP\COMPLETED TRIPS\2022\TRIP - 2022001.PDF', SINGLE_BLOB) as pdffile

and see what error you get

  1. What happens when DAWN leaves the company?
  2. How is this process being run?

Error received running just the portion is:

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?"

:joy::joy::joy:

Say hi to Dawn.

The sql service most probably does not have permission to that folder

I would out it in C drive in a folder that no spaces and file name with no spaces

Are you running this manually or a sql job or some other automation

So, I am to assume the SQL is proper?

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:

VALUES (@bl, @fn, @pl, @pdf)

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

Something like this:

DECLARE @pdf varbinary(max)
      , @sql nvarchar(max) = 'SET @PDF = (SELECT * FROM OPENROWSET(BULK, ''\\DAWN-DESKTOP\COMPLETED TRIPS\2022\TRIP - 2022001.PDF'', SINGLE_BLOB) AS document';

EXECUTE sp_executesql @sql, '@pdf varbinary(max) output', @pdf = @pdf output;

Once you have that working - you can then dynamically build the string to be passed into sp_executesql.

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.

You forgot to move it out to your C drive to a folder the sql service account has access to

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:

DECLARE @pdf varbinary(max)
      , @pdfFile nvarchar(255) = '\\DAWN-DESKTOP\COMPLETED TRIPS\2022\TRIP - 2022001.PDF'
      , @sql nvarchar(max) = CONCAT('SET @PDF = (SELECT * FROM OPENROWSET(BULK, ', @pdfFile, ', SINGLE_BLOB) AS document';

EXECUTE sp_executesql @sql, '@pdf varbinary(max) output', @pdf = @pdf output;

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.

It is all a matter of context.

DECLARE @pdf varbinary(max)
      , @pdfFile nvarchar(255) = '\\DAWN-DESKTOP\COMPLETED TRIPS\2022\TRIP - 2022001.PDF'
      , @sql nvarchar(max) = CONCAT('SET @PDF = (SELECT * FROM OPENROWSET(BULK, ', @pdfFile, ', SINGLE_BLOB) AS document');

PRINT @sql;
EXECUTE sp_executesql @sql, '@pdf varbinary(max) output', @pdf = @pdf output;

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?

Sorry - my fault. You can't reference a variable in the same declare statement.

DECLARE @pdf varbinary(max)
      , @pdfFile nvarchar(255) = '\\DAWN-DESKTOP\COMPLETED TRIPS\2022\TRIP - 2022001.PDF';

DECLARE @sql nvarchar(max) = CONCAT('SET @PDF = (SELECT * FROM OPENROWSET(BULK, ', @pdfFile, ', SINGLE_BLOB) AS document');