Stored Procedures SQL Server - Cannot get PDF to store locally

Question

Stored Procedures SQL Server - Cannot get PDF to store locally

430 Views Asked by Dylan At 21 October 2024 at 12:36

I have created an asp.net web form with master page on visual studio for my final project in university. The last thing I have to do is create a functional report button and so when clicked, the user should be able to view a pdf report which is saved locally. I don't have much experience with databases and so I know very little about stored procedures.

This is my stored procedure code:

USE [assetDB]
GO
/****** Object:  StoredProcedure [dbo].[SPGeneratePDFReport]    Script Date: 25/04/2022 12:48:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Dylan McDaid>
-- Create date: <15/04/2022>
-- Description: <Stored Procedure to generate PDF>
-- =============================================
ALTER PROCEDURE [dbo].[SPGeneratePDFReport] 
    -- Add the parameters for the stored procedure here
    @filename as NVARCHAR(50), 
    @filelocation as NVARCHAR(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Create temporary tables
    CREATE TABLE #tempPDFData (code NVARCHAR(200))
    CREATE TABLE #pdf (idnumber INT IDENTITY(1,1)
    ,code NVARCHAR(200))
    CREATE TABLE #xref (idnumber INT IDENTITY(1,1)
    ,code VARCHAR(30))
    CREATE TABLE #text (idnumber INT IDENTITY(1,1)
    ,code VARCHAR(200))END

    DECLARE @end VARCHAR(7),
@beg VARCHAR(7),
@a1 VARCHAR(3),
@a2 VARCHAR(3),
@ad VARCHAR(5),
@cr VARCHAR(8),
@pr VARCHAR(9),
@ti VARCHAR(6),
@xstr VARCHAR(10),
@page VARCHAR(8000),
@pdf VARCHAR(100),
@trenutniRed NVARCHAR(200),
@rows INT,
@ofset INT,
@len INT,
@nopg INT,
@fs INT,
@ole INT,
@x INT,
@file INT,
@object INT

-- Set PDF File Location
SELECT @pdf = @fileLocation + @filename + '.pdf'

-- Format PDF File
SET @page = ''
SET @nopg = 0
SET @object = 6
SET @end = 'endobj'
SET @beg = ' 0 obj'
SET @a1 = '<<'
SET @a2 = '>>'
SET @ad = ' 0 R'
SET @cr = CHAR(67) + CHAR(114) + CHAR (101) + CHAR(97) + CHAR(116) + CHAR (111) + CHAR(114)
SET @pr = CHAR(80) + CHAR(114) + CHAR (111) + CHAR(100) + CHAR(117) + CHAR (99 ) + CHAR(101) + CHAR(114)
SET @ti = CHAR(84) + CHAR(105) + CHAR (116) + CHAR(108) + CHAR(101)
SET @xstr = ' 00000 n'
SET @ofset = 396



INSERT INTO #xref(code) VALUES ('xref')
INSERT INTO #xref(code) VALUES ('0 10')
INSERT INTO #xref(code) VALUES ('0000000000 65535 f')
INSERT INTO #xref(code) VALUES ('0000000017' + @xstr)
INSERT INTO #xref(code) VALUES ('0000000790' + @xstr)
INSERT INTO #xref(code) VALUES ('0000000869' + @xstr)
INSERT INTO #xref(code) VALUES ('0000000144' + @xstr)
INSERT INTO #xref(code) VALUES ('0000000247' + @xstr)
INSERT INTO #xref(code) VALUES ('0000000321' + @xstr)
INSERT INTO #xref(code) VALUES ('0000000396' + @xstr)
INSERT INTO #pdf (code) VALUES ('%' + CHAR(80) + CHAR(68) + CHAR (70) + '-1.2')
INSERT INTO #pdf (code) VALUES ('%\d3\d3')
INSERT INTO #pdf (code) VALUES ('1' + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES ('/' + @cr + ' (Ivica Masar ' + CHAR(80) + CHAR(83) + CHAR (79) + CHAR(80) + CHAR(68) + CHAR (70) + ')')
INSERT INTO #pdf (code) VALUES ('/' + @pr + ' (stored procedure for ms sql [email protected])')
INSERT INTO #pdf (code) VALUES ('/' + @ti + ' (SQL2' + CHAR(80) + CHAR(68) + CHAR (70) + ')')
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)
INSERT INTO #pdf (code) VALUES ('4' + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES ('/Type /Font')
INSERT INTO #pdf (code) VALUES ('/Subtype /Type1')
INSERT INTO #pdf (code) VALUES ('/Name /F1')
INSERT INTO #pdf (code) VALUES ('/Encoding 5' + @ad)
INSERT INTO #pdf (code) VALUES ('/BaseFont /Courier')
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)
INSERT INTO #pdf (code) VALUES ('5' + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES ('/Type /Encoding')
INSERT INTO #pdf (code) VALUES ('/BaseEncoding /WinAnsiEncoding')
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)
INSERT INTO #pdf (code) VALUES ('6' + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES (' /Font ' + @a1 + ' /F1 4' + @ad + ' ' + @a2 + ' /ProcSet [ /' + CHAR(80) + CHAR(68) + CHAR (70) + ' /Text ]')
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)



-- Build PDF Data into Temp Table
INSERT #tempPDFData(code) SELECT SPACE(34) + 'ASSETS REPORT'
INSERT #tempPDFData(code) SELECT ' '
INSERT #tempPDFData(code) SELECT 'Serial No' + SPACE(6) + 'Asset Type' + SPACE(10) + 'Asset Status' + SPACE(8) + 'Clock In' + SPACE(2) + 'Name'
INSERT #tempPDFData(code) SELECT REPLACE(SPACE(80), ' ', '_')
INSERT #tempPDFData(code) SELECT [asset_serial_no] + SPACE(15-len([asset_serial_no])) + [asset_type] + SPACE(20-len([asset_type])) + [status] + SPACE(20-len([status])) + CAST(member_details.[staff_id] AS NVARCHAR) + SPACE(10-len(member_details.[staff_id])) + member_details.[fullname] + SPACE(20-len(member_details.[fullname])) FROM asset_assignment INNER JOIN member_details ON member_details.staff_id = asset_assignment.staff_id RIGHT OUTER JOIN asset_register ON asset_serial_number = asset_serial_no 
INSERT #tempPDFData(code) SELECT REPLACE(SPACE(80), ' ', '_')
INSERT #tempPDFData(code) SELECT ' '
INSERT #tempPDFData(code) SELECT REPLACE(SPACE(30), ' ', '-')
INSERT #tempPDFData(code) SELECT 'REPORT TOTALS'
INSERT #tempPDFData(code) SELECT REPLACE(SPACE(30), ' ', '-')
INSERT #tempPDFData(code) SELECT 'Equipment Totals'
INSERT #tempPDFData(code) SELECT REPLACE(SPACE(30), ' ', '_')
INSERT #tempPDFData(code) SELECT [asset_type] + SPACE(20-len([asset_type])) + CAST(COUNT([asset_type]) AS NVARCHAR) FROM asset_register GROUP BY [asset_type]
INSERT #tempPDFData(code) SELECT SPACE(20) + REPLACE(SPACE(10), ' ', '_')
INSERT #tempPDFData(code) SELECT 'Total' + SPACE(15) + CAST(COUNT([asset_type]) AS NVARCHAR) FROM asset_register
INSERT #tempPDFData(code) SELECT SPACE(20) + REPLACE(SPACE(10), ' ', '_')
INSERT #tempPDFData(code) SELECT ' '
INSERT #tempPDFData(code) SELECT 'Status Totals'
INSERT #tempPDFData(code) SELECT REPLACE(SPACE(30), ' ', '_')
INSERT #tempPDFData(code) SELECT [status] + SPACE(20-len([status])) + CAST(COUNT([status]) AS NVARCHAR) FROM asset_register GROUP BY [status]
INSERT #tempPDFData(code) SELECT SPACE(20) + REPLACE(SPACE(10), ' ', '_')
INSERT #tempPDFData(code) SELECT 'Total' + SPACE(15) + CAST(COUNT([status]) AS NVARCHAR) FROM asset_register
INSERT #tempPDFData(code) SELECT SPACE(20) + REPLACE(SPACE(10), ' ', '_')




-- Insert data from Temp table into PDF table
INSERT INTO #text(code) (SELECT code FROM #tempPDFData)

-- Build PDF File
SELECT @x = COUNT(*) FROM #text
SELECT @x = (@x / 60) + 1

WHILE @nopg < @x
BEGIN
DECLARE SysKursor INSENSITIVE SCROLL CURSOR FOR
SELECT
SUBSTRING((code + SPACE(81)), 1, 80)
FROM #text
WHERE idnumber BETWEEN ((@nopg * 60) + 1) AND ((@nopg + 1) * 60 )
FOR READ ONLY

OPEN SysKursor
FETCH NEXT FROM SysKursor INTO @trenutniRed

SELECT @object = @object + 1
SELECT @page = @page + ' ' + CAST(@object AS VARCHAR) + @ad
SELECT @len = LEN(@object) + LEN(@object + 1)
INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR) + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES ('/Type /Page')
INSERT INTO #pdf (code) VALUES ('/Parent 3' + @ad)
INSERT INTO #pdf (code) VALUES ('/Resources 6' + @ad)
SELECT @object = @object + 1
INSERT INTO #pdf (code) VALUES ('/Contents ' + CAST(@object AS VARCHAR) + @ad)
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)
SELECT @ofset = @len + 86 + @ofset
INSERT INTO #xref(code) (SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR),
LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9,
LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr)
INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR) + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
SELECT @object = @object + 1
INSERT INTO #pdf (code) VALUES ('/Length ' + CAST(@object AS VARCHAR) + @ad)
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES ('stream')
INSERT INTO #pdf (code) VALUES ('BT')
INSERT INTO #pdf (code) VALUES ('/F1 10 Tf')
INSERT INTO #pdf (code) VALUES ('1 0 0 1 50 802 Tm')
INSERT INTO #pdf (code) VALUES ('12 TL')



WHILE @@Fetch_Status = 0
BEGIN
INSERT INTO #pdf (code) VALUES ('T* (' + @trenutniRed + ') Tj')
FETCH NEXT FROM SysKursor INTO @trenutniRed
END



INSERT INTO #pdf (code) VALUES ('ET')
INSERT INTO #pdf (code) VALUES ('endstream')
INSERT INTO #pdf (code) VALUES (@end)

SELECT @rows = (SELECT COUNT(*) FROM #text WHERE idnumber BETWEEN ((@nopg * 60) + 1) AND ((@nopg + 1) * 60 ))* 90 + 45
SELECT @nopg = @nopg + 1
SELECT @len = LEN(@object) + LEN(@object - 1)
SELECT @ofset = @len + 57 + @ofset + @rows

INSERT INTO #xref(code) (SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR), LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9, LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr)
INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR) + @beg)
INSERT INTO #pdf (code) VALUES (@rows)
INSERT INTO #pdf (code) VALUES (@end)

SELECT @len = LEN(@object) + LEN(@rows)
SELECT @ofset = @len + 18 + @ofset

INSERT INTO #xref(code) (SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR), LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9, LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr)
CLOSE SysKursor
DEALLOCATE SysKursor
END


INSERT INTO #pdf (code) VALUES ('2' + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES ('/Type /Catalog')
INSERT INTO #pdf (code) VALUES ('/Pages 3' + @ad)
INSERT INTO #pdf (code) VALUES ('/PageLayout /OneColumn')
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)

UPDATE #xref SET code = (SELECT code FROM #xref WHERE idnumber = (SELECT MAX(idnumber) FROM #xref)) WHERE idnumber = 5

DELETE FROM #xref WHERE idnumber = (SELECT MAX(idnumber) FROM #xref)

INSERT INTO #pdf (code) VALUES ('3' + @beg)
INSERT INTO #pdf (code) VALUES (@a1)
INSERT INTO #pdf (code) VALUES ('/Type /Pages')
INSERT INTO #pdf (code) VALUES ('/Count ' + CAST(@nopg AS VARCHAR))
INSERT INTO #pdf (code) VALUES ('/MediaBox [ 0 0 595 842 ]')
INSERT INTO #pdf (code) VALUES ('/Kids [' + @page + ' ]')
INSERT INTO #pdf (code) VALUES (@a2)
INSERT INTO #pdf (code) VALUES (@end)

SELECT @ofset = @ofset + 79

UPDATE #xref SET code =(SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR), LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9, LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr) WHERE idnumber = 6

INSERT INTO #xref(code) VALUES ('trailer')
INSERT INTO #xref(code) VALUES (@a1)

SELECT @object = @object + 1

UPDATE #xref SET code = '0 ' + CAST(@object AS VARCHAR) WHERE idnumber = 2

INSERT INTO #xref(code) VALUES ('/Size ' + CAST(@object AS VARCHAR))
INSERT INTO #xref(code) VALUES ('/Root 2' + @ad)
INSERT INTO #xref(code) VALUES ('/Info 1' + @ad)
INSERT INTO #xref(code) VALUES (@a2)
INSERT INTO #xref(code) VALUES ('startxref')

SELECT @len = LEN(@nopg) + LEN(@page)
SELECT @ofset = @len + 86 + @ofset

INSERT INTO #xref(code) VALUES (@ofset)
INSERT INTO #xref(code) VALUES ('%%' + CHAR(69) + CHAR (79) + CHAR(70))
INSERT INTO #pdf (code) (SELECT code FROM #xref)

--SELECT code FROM #pdf\
SELECT @trenutniRed = 'del '+ @pdf
EXECUTE @ole = sp_OACreate 'Scripting.FileSystemObject', @fs OUT
EXEC master..xp_cmdshell @trenutniRed, NO_OUTPUT



EXECUTE @ole = sp_OAMethod @fs, 'OpenTextFile', @file OUT, @pdf, 8, 1



DECLARE SysKursor INSENSITIVE SCROLL CURSOR
FOR SELECT code FROM #pdf ORDER BY idnumber
FOR READ ONLY
OPEN SysKursor
FETCH NEXT FROM SysKursor INTO @trenutniRed
WHILE @@Fetch_Status = 0
BEGIN
EXECUTE @ole = sp_OAMethod @file, 'WriteLine', Null, @trenutniRed
FETCH NEXT FROM SysKursor INTO @trenutniRed
END
CLOSE SysKursor
DEALLOCATE SysKursor
DELETE FROM #tempPDFData
EXECUTE @ole = sp_OADestroy @file
EXECUTE @ole = sp_OADestroy @fs

And this is the query I run afterwards:

USE [assetDB]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[SPGeneratePDFReport]
        @filename = N'Test_Report',
        @filelocation = N'C:\Users\Admin\Documents'

SELECT  'Return Value' = @return_value

GO

When executed, I am notified with the return value of '0' and there are no errors, so it seems to have executed something, but the file is nowhere to be found on my laptop. Is it something to do with the file location I am trying to save it to? or is it to do with the general syntax/ formatting of the stored procedure. Any help would be greatly appreaciated.

Thank you in advance.

Original Q&A

There are 1 best solutions below

**diiN__________** · Answer 1

I'd say that the problem lies in the following line:

SELECT @pdf = @fileLocation + @filename + '.pdf'

The way you're passing the parameters, @pdf is missing a backslash between the path and the name:

'C:\Users\Admin\DocumentsTest_Report.pdf'

Stored Procedures SQL Server - Cannot get PDF to store locally

There are 1 best solutions below

Related Questions in SQL

Related Questions in SQL-SERVER

Related Questions in DATABASE

Related Questions in STORED-PROCEDURES

Related Questions in PDF-FORM

Trending Questions

Popular # Hahtags

Popular Questions