SSRS (4) --- SQL Server Database Project in Visual Studio

Note: this article is published on 08/25/2024.

This series of articles will discuss SQL Server Reporting Services, including the related Services:

A - Introduction

The services discussed include:

SSIS --- SQL Server Integration Services
SSAS --- SQL Server Analysis Services
SSRS --- SQL Server Reporting Services
SSDT --- SQL Server Data Tools

This article actually is a Visual Studio Project, with the pre-condition to install the SSDT components for Visual Studio. Use SQL Server Database Project, we can save SQL Server objects, such as Stored Procedure and Function, into Source Control (Repository) easily. The content of this article:

A - Introduction
B - Create a SQL Server Database Project in Visual Studio
C - Setup Database Connection
D - Modify Database Schema in Project
E - Deploy the Change to Server

B - Create a SQL Server Database Project in Visual Studio

The pre-condition is to install the SSDT components in your Visual Studio installation process:

Create a SQL Server Database Project --- you may search for SQL Server....

Name the Project as TradeDev:

Project created:

C - Setup Database Connection

Right Click Project => Import => Database

Open the Import Database window => Select Connection:

in the Connect page, you can either setup a new database connection or use the existing ones:

After setup the database connectioin, check all boxes => Start

Import the database into the Project:

Notice that the tables and function are grouped under their schema folders shown in the project:

The layout is different from the one in SSMS:

On the other hand, we can see them both from the SQL Server Object Explorer:

In SQL Server Object Explorer, on the upper part, AdventureWorks2022 is the SQL Server Database while on the bottom panel, Project TradeDev is what we created:

D - Modify Database Schema in Project

To demo the process, I create a simple database in SQL Server, named as Database_Project with a new table named as Table_1:

Definition of Table_1 as

Make a new project named as Database_Project:

Import database. Database_Project, from SQL Server:

we have

Double click Table_1, we got the table schema:

Modify the schema:

Make Name Not Null;
Add one column: Address

after change, build the project.

E - Deploy the Change to Server

Now we deploy the change to server: Right Click the project => Publish

Setup the publish Database => Click Edit:

Choose database: Database_Project:

Test successful:

Ready to publish:

Publish:

After done: Checl the database from SQL Server Management Console:

Use SQL Server Database Project, we can save SQL Server objects, such as Stored Procedure and Function, into Source Control (Repository) easily.

Referebces:

2 Comments

Join the conversation! Your thoughts help the community grow.

mbasit2y ago
You can generate the script of database in sql server 2014
- George2y ago
  Sorry, just saw your message. I am not sure if I can generate the script of database in sql server 2014. This work is against to SQL Server 2022.