Proposal Automation Software

Generate PDF Documents from SQL Server

Generate PDF/Word documents from SQL Server, Oracle, and MySQL using SQL queries.

SQL PDF


SQL Server's ease of use, availability, and tight integration with the Windows operating system makes it an easy choice for firms that choose Microsoft products for their enterprises. It is the platform for both on-premises and cloud databases and business intelligence solutions.

SQL Server is helping enterprises to build mission-critical applications with high-performance, in-memory security technology. Some of the large ERPs and CRMs are powered by SQL server.

Businesses create a variety of documents in PDF format using SQL server data. They populate SQL data into PDF templates for bulk or on-demand document creation. But SQL PDF document creation is tricky and complex.


Different approaches for SQL PDF documents


One of the popular approaches is to use macros. First, create a word template with placeholders and then use VBA to parse the data source data to fill the templates. VBA is also used for creating malware and viruses. Hence many organizations have strict policies on the usage of VBA code. Moreover, they are not compatible with different versions of the Office suite. That limits its utility beyond the organization. Every time you upgrade the office version, they may require modifications.

Another popular approach is to use a reporting package such as SSRS that creates PDF documents from SQL server data. You have to import it as a default report into each installed instance thus making it less elegant. Moreover, reporting packages lack in document design and are best suited for creating interactive reports only. So if you have a complex invoice or contract template, this approach may not be of much use.

The problem with templates is, they change frequently. So for every template change, you’ve to seek the developer’s time to make corresponding changes in code. However, getting a developer’s time is difficult and expensive. Both these approaches are not DIY solutions and require development resources thus making them maintenance heavy. EDocGen addresses this problem elegantly.

Using it, business users themselves can populate contracts, reports, letters, etc. with SQL server data.


EDocGen - SQL to PDF Documents


Generation of PDF documents from SQL Server is a simple two-step process.

  1. Connect to your database from the system.
  2. Generate PDF documents from query data.
  3. Optionally, you can choose to send generated PDF documents to individual recipients.

Step1: Connect to the database

In the connection screen, select the SQL server from the drop-down. Then enter the connection URL in "username@hostname[:port]/DatabaseName" format and the password.


SQL Database Connection

In the query either you can use simple SELECT statements or queries that fetch data in JSON/XML formats.

Table data as XML

Fetching data from SQL Server in XML format can be achieved via the SELECT statement. It is used regularly by DBAs and business users alike. The SELECT statement with "FOR XML" clause in the query retrieves table data as XML nodes. FOR XML takes different arguments – RAW, AUTO, or EXPLICIT.

Below is a simple example of the AUTO argument that retrieves all the records from the Person table.

SELECT Title,					
	FirstName,
	MiddleName,					
	LastName,
	Suffix
FROM Person.Person
FOR XML AUTO
GO			      

Table data as JSON

Similarly, fetch data from SQL Server as JSON by adding the "FOR JSON" clause to SELECT statement. When you use the FOR JSON clause, you can specify the structure of the JSON output explicitly, or let the structure of the SELECT statement determine the output.

To maintain full control over the format of the JSON output, use FOR JSON PATH. You can create wrapper objects and nest complex properties. FOR JSON takes different arguments – RAW, AUTO, or EXPLICIT. To format the JSON output automatically based on the structure of the SELECT statement, use FOR JSON AUTO. However, The system accepts any valid JSON and no data transformation is necessary.

SELECT name, surname
FROM emp
FOR JSON AUTO   
Results
JSON
	[{
	"name": "John"
	}, {
	"name": "Jane",
	"surname": "Doe"
	}]

In the latest editions of SQL servers, you can store and query JSON documents as in a NoSQL database.


If you don't want to enter connection details for security reasons, you may download the SQL Server data in XML/JSON format and use it for document generation. Please visit XML to PDF and JSON to PDF for a detailed description of how to go about the XML/JSON population.

The next step is to populate query data into the document template.


Step2: Populate DB Data into Document Template

The system supports the filling of dynamic text, tables, paragraphs, blobs, hyperlinks, and images from SQL Server data into existing PDF/Word templates. Since templates are in PDF/Word format, business users can edit them themselves.

Also, the data population doesn't require any data transformation. Business users can populate data as-is. Thus, database changes would never affect the PDF generation. There is an option to map the template's dynamic fields to the query columns. If both the names match, the system automatically maps them for you.

After mapping, select PDF as output format and click the generate button. The system generates a ZIP that contains generated PDF documents


Step3: Document Distribution

The system allows you to send generated individual documents from your business email. In the distribution screen, select the query column that contains the email field. Then enter the subject and email body. Clicking the generate button distributes the documents to individual recipients as an attachment.



This entire document generation process can be automated by integrating with EDocGen API. You can automate the bulk/on-demand PDF generation from SQL Server. You can also create a workflow. In summary, EDocGen document generation software is the perfect solution for SQL to PDF documents creation.

Schedule a demo for more details.

Popular Posts