Showing posts with the label Sql Server

Step by step migration of ASP.NET Core 2.2 to 3.1

In order to upgrade/migrate core version to 3.1 following steps to be perform.
Download and install Visual Studio 2019 version 16.4 or higher.Download and install .NET Core 3.1 the projects of solution to .NET Core 3.1 , because ASP.NET Core 3.1 requires it.Update existing Nuget packages to a version compatible with ASP.NET Core 3.1 in each projectUse IHost interface from a IHostBuilder instead of building and running a IWebHost from a IWebHostBuilder in Program.cs public static IHostBuilder CreateWebHostBuilder(string[] args) => Host.CreateDefaultBuilder(args) .ConfigureWebHostDefaults(webBuilder => { webBuilder.UseKestrel() .UseSerilog() .UseStartup<Startup>(); }) Change AddMvc method has been replaced by AddControllers in Startup.cs of each projects.In Startup.cs,UseMvc method to be replaced by…

Database backup from Sql script using cursor in sql server 2008/2010

This is simple example of taking database backup from the T-sql script. In my example i have illustrated the simplest way to take the back up from the database. I have used the cursor for the same. Following is the code of the Backup script.

DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCA…

How to convert rows into columns in Sql Server 2008

How to convert rows into columns? We're going to see how to do that using query, and there is the "Pivot" function in SQL Server 2005.
The easiest way is when you know the columns which are fixed. But most of the times, you want to do that in general not for specific columns.

If you have fixed columns, then all you will be doing is an “if-else” field that holds the column names and set them into different columns:
Example: We have 2 tables: Subject which is a lookup table that holds the subject names, and the StudentSubject which contains the student grades for the different subject.

We are going to build the query having fixed columns:

We use the "if-else" functionality in the query to put them in the defined columns (in SQL, it is "case"):

SELECT StudentId, SUM(Physics) AS Physics, SUM(Chemistry) As Chemistry, SUM(Math) AS Math, SUM(English) As English FROM (SELECT StudentId, (CASE SubjectId WHEN 24 THEN ISNULL(Grade, 0) END) AS Physics, (CASE Su…