Posts

Showing posts with the label Sql Server

Log exception to file in c# .net core application explained

In production environment when any bug gets reported at that time as developer wants to get the exception details of bug to resolve the issue on high priority.
In some case there might be situation where exception is not logged in table but developer wants to check the bug immediately. for that purpose we need to write the
exception details including stack trace of bug in file. To implement this easily i am going to demonstrate the implementation of custom logging in file as below.

First we need to construct the exception and logger method in custom logger class as shown below.


public class CustomLogger { public static List<string> ConstructExceptionDetails(Exception ex,string FunctionName) { return new List<string>() { $"{DateTime.Now} - {FunctionName}", $"Exception: {ex.Message}", $"StackTrace: {ex.StackTrace}", $"InnerException: {ex.InnerException?.…

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

Image
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…