.NET GENES

Hello! Below are the scripts to shrink a specific log file assuming log files take up so much disk space. -- View all log files in db and their log sizes DBCC SQLPERF(LOGSPACE); -- Backup log file first before shrinking BACKUP LOG [SandboxDb] TO DISK = 'Z:\SQL\Backups\Customer_DB\SandboxDb_Log.trn' WITH INIT, COMPRESSION; GO -- View Log File name of DB USE SandboxDb GO SELECT name FROM sys.master_files WHERE database_id = db_id() AND type = 1 /* Commands to shrink the db */ USE [SandboxDb]; GO CHECKPOINT ; GO -- Change the database recovery model to SIMPLE if FULL is the current recovery mode. ALTER DATABASE SandboxDb SET RECOVERY SIMPLE ; GO -- Shrink log file to 10240 MB. -- 10240 means 10,240 MB = 10 GB. DBCC SHRINKFILE (SandboxDb_Log, 10240 ); GO -- Reset the database recovery model. ALTER DATABASE SandboxDb SET RECOVERY FULL ; GO Cheers!

Hello, Here's some of my compiled T-SQL scripts to convert ordinary tables to System Versioned and data manipulation scripts. See below the scripts using ficticious tables and columns. How To Convert An Ordinary Table To A System Versioned Table Use TEST_DB GO ALTER TABLE dbo.tblFileFolders ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START CONSTRAINT DF_tblFileFolders_StartTime DEFAULT SYSUTCDATETIME(), EndTime DATETIME2 GENERATED ALWAYS AS ROW END CONSTRAINT DF_tblFileFolders_EndTime DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59.9999999' ), PERIOD FOR SYSTEM_TIME (StartTime, EndTime); ALTER TABLE dbo.tblFileFolders SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.tblFileFoldersHistory ) ); How To A Drop System Versioned Table Use TEST_DB GO -- 1. Disable system versioning ALTER TABLE [dbo].[tblFileFolders] SET (SYSTEM_VERSIONING = OFF ); GO -- 2. Dr...

Hello, I've been given a task to get the superseded partnumber of a certain part. The scenario is, the chain of the superseded is undetermined and could go as has high as four or more levels. See screenshot below of an example of product supersession. It illustrates that the part supersession of part M03971 is M32887 instead of its direct parent M04880. Trying out the concept of recursive CTE gets only the lowest level instead without it's parent part supersessions. WITH SupersededBy_Cte AS ( SELECT [StdDescID], [PartNumber], [SupersededBY], 0 AS [ level ] FROM [tblStdDesc] std1 WHERE std1.SupersededBY IS NULL UNION All SELECT std2.[StdDescID], std2.[PartNumber], std2.[SupersededBY], cte.[ level ] + 1 FROM SupersededBy_Cte cte INNER JOIN [tblStdDesc] std2 ON std2.SupersededBY = cte.StdDescID ) SELECT cte.[StdDescID], cte.[PartNumber], cte.[SupersededBY], cte.[ level ] FROM SupersededBy_Cte cte WH...

Aloha fellow developers! I have this scenario wherein I need to display row numbers in my result set given that the rows don't have a ID column and each row number represents the month number of a specific month. SELECT A.fsyear_num as 'Fiscal Year' , [MonthName] FROM CTE_GrossProfit_SalesByClass_Current_FSYear A I know that row number is the solution but I need to tweak this to handle my requirement. After reading the forums and docs, I found out that adding a select statement with COUNT() inside the Over By clause is the solution. SELECT A.fsyear_num as 'Fiscal Year' , ROW_NUMBER() Over( Order BY ( Select Count ( * ))) AS MonthNum, [MonthName] FROM CTE_GrossProfit_SalesByClass_Current_FSYear A ORDER BY MonthNum ASC

Hello fellow programmers! Here's a simple T-SQL statement that will check if a column value is null or empty. IIF( c .BillPhone is null , '' , IIF(Len( c .BillPhone) > 0, c .BillPhone , '' )) as BillingPhone First, it will check if the field is null. If true, it will return empty string. Else, another checking will be performed and this time if it's an empty string. If not an empty string, show the column value. Else show an empty string. That's it! :-)

Good evening Gents! I was given a task to export 100+GB of files from an MSSQL DB of which these files are saved in a table using Varbinary/Blob column. I've made some spike applications using BCP and .NET CLR but to no avail all the spike applications don't work since I don't have full permission to the database. The only solution that work for me was using the OLE Automation Procedures. So in order to export files from Blob, I'll present the steps below using AdventureWorks2012 database. 1. Enable OLE Automation Procedures by running script below. Use AdventureWorks2012 Go EXEC sp_configure 'show advanced options' , 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures' , 1; GO RECONFIGURE; GO 2. Run the stored procedure script below. The script will return the LargePhoto of a specific product. USE AdventureWorks2012 GO IF OBJECT_ID( 'SP_AdventureWorks_Export_Blob' , 'P' ) IS NOT NULL DROP PROCEDURE S...

I have been generating insert data scripts through SQL Server Management Studio on the fly. However if you don't want to include the ID which is an identity column,this feature is not defined in the IDE. The workaround for this is to insert the records without the ID column into a temporary table and generate insert data scripts using that temp table. Select PartNum, PartDescription, Model, Category, SaleQTY, Price Into tmptblParts From tblParts The query above will insert records from tblParts to temp table tmptblParts. The column ID is omitted. After you have executed that statement, then generate insert scripts using tmptblParts.

Given that you'll have to delete records from an SQL server history table, you might come across with the issue as mentioned in the title of the post. After doing some research and experiments, there are three steps to delete records from a temporal history table. a. First is to remove the main table's System Versioning option. ALTER TABLE [dbo].[tblParts] SET ( SYSTEM_VERSIONING = OFF ) b. Next is to delete the records of the temporal history table. Delete from dbo.tblPartsHistory WITH (TABLOCKX); c. Then Set again the system versioning and history table of the main table. ALTER TABLE [dbo].[tblParts] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[tblPartsHistory])) Solved.. :-)

In the event that you need to check a T-SQL field value if it's a GUID or UniqueIdentifier, the same procedure is used in applying the logic in a where clause. SELECT CartObjectID, CartName, CartQuantity, CASE When TDD.FieldData like REPLACE ( '00000000-0000-0000-0000-000000000000' , '0' , '[0-9a-fA-F]' ) Then ( Select CartValue From tblCartList where ListID = TDD.FieldData ) ELSE TDD.FieldData END As FieldData

Here's how to update multiple rows in one SQL statement using MSSQL. UPDATE tblEmp SET empSalary = empSrc.empSalary, empLevel = empSrc.empLevel FROM dbo.tblEmployees AS tblEmp INNER JOIN ( VALUES ( 001 , 10000 . 00 , 10 ), ( 002 , 32000 . 00 , 15 ), ( 003 , 9500 . 00 , 3 ) ) AS empSrc (empID, empSalary, empLevel) ON tblEmp.empID = empSrc.empID; Reference: How to update multiple columns of multiple rows in one SQL statement

Good morning! In the event that you need to replace your queries using Union such as below: Use DBJersonsHW go SELECT DISTINCT [EmpName] AS [EmployeeName], EmpAddress As [Address] FROM tblEmployeesMain UNION SELECT DISTINCT [EmpName] AS [EmployeeName], EmpAddress As [Address] FROM tblEmployeesMetro ORDER BY [EmpName]; The alternative for that is to use Full Outer Join as presented below: Use DBJersonsHW go SELECT Distinct Coalesce(tblEmployeesMain.EmpName, tblEmployeesMetro.EmpName) AS EmployeeName, Coalesce(tblEmployeesMain.EmpAddress, tblEmployeesMetro.Address) AS Address, FROM tblEmployeesMain FULL Outer JOIN tblEmployeesMetro on tblEmployeesMain.EmpName = tblEmployeesMetro.EmpName order by EmpName Cheers! :-)

Good evening! An issue was brought up by a developer on how to convert rows of DateTime values into columns using MS Access DB with functionality similar to pivoting of MSSQL.The problem has an added complexity since specific portions of the DateTime will be extracted too. See original post here: Converting Columns to Rows in MS Access . The data presented is similar to a Timesheet entry wherein an employee has login/logout records. The solution presented in the thread involves usage of subquery and joins designed for MS Access Db. I modified the accepted answer to a T-SQL query using Group By, Max() function, Coalesce() and without subquery. SELECT LogInfo.fldMachineId, Year (LogInfo.fldLogDate) as [ Year ], Month (LogInfo.fldLogDate) as [ Month ], Day (LogInfo.fldLogDate) as [ Day ], Max (Coalesce( Convert ( varchar ( 5 ), T2.fldLogDate, 108 ), '' )) as TimeIn1, Max (Coalesce( Convert ( varchar ( 5 ), T3.fldLogDate, 108 ), '' )) as TimeOut1, ...

Good day! Here's an ASP.NET MVC example of a CRUD(Create/Update/Delete) application using stored procedures and Entity Framework 6.First, you need to perform steps 1-3 from this link Call Stored Procedures from Entity Framework 6 in C# (Part 1) . For step 3, instead of creating a console application use ASP.NET MVC Empty project. Once done, the code for the controller and views are shown below: CustomersController private CustomerEntities db = new CustomerEntities(); // GET: Customers public ActionResult Index() { return View(db.Database.SqlQuery<Customer>( "GetAllCustomers" ).ToList()); } // GET: Customers/Create public ActionResult Create() { return View(); } // POST: Customers/Create [HttpPost] [ValidateAntiForgeryToken] public ActionResult Create([Bind(Include = "CompanyName,ContactName,Address,Country,Phone" )] Customer customer) { if (ModelState.IsValid) { db.Database.ExecuteSqlCommand( "EXEC dbo.InsertCustomer @Compan...

Hello, In this second part of the series on how to call stored procedures from Entity Framework 6, I will demonstrate on executing them using context.Database methods. To accomplish this, you need to perform steps 1 through 3 from this article Call Stored Procedures from Entity Framework 6 in C#. If you're done, the codes for the CRUD(Create, Update and Delete) functionalities are presented below. private static CustomerEntities ce = new CustomerEntities(); private static void InsertCustomer() { try { var result = ce.Database.ExecuteSqlCommand( "EXEC dbo.InsertCustomer @CompanyName,@ContactName,@Address,@Country,@Phone" , new SqlParameter( "CompanyName" , "TNT Bookstore" ), new SqlParameter( "ContactName" , "Mr T." ), new SqlParameter( "Address" , "Lincoln Village" ), new SqlParameter( "Country" , "UK" ), new SqlParameter( "Phone" , "42333...

Hello, Here's a tutorial on how to call stored procedures from Entity Framework 6.0 through the context object using the stored procedure name. In the second part of the series, I'll demonstrate how to call the stored procedures using methods like ExecuteSqlCommand() and SqlQuery() from context.Database class. To start with here are the steps to complete this example. Step 1 Add a Customers table in your database with fields. => CustomerID (int and identity set to true) => CompanyName(nvarchar) => ContactName(nvarchar) => Address(nvarchar) => Country(nvarchar) => Phone(nvarchar) Step 2 Create stored procedures that will perform insert, update, delete and get all records operations. Insert ALTER Procedure [dbo].[InsertCustomer]( @ CompanyName nvarchar( 40 ), @ ContactName nvarchar( 30 ), @ Address nvarchar( 60 ), @ Country nvarchar( 15 ), @ Phone nvarchar( 24 )) As Begin Insert Into dbo.Customers (CompanyName, ContactName, [Address...

Hi all, In this demo, I have dummy XML files saved to an XML column in a table. The files have the same structure of nodes, except that some files have altered price value of 104.95. The is the structure of the dummy XML file. <catalog> <book id= "bk101" > <author>Gambardella, Matthew</author> <title>XML Developer's Guide</title> <genre>Computer</genre> <price>44.95</price> <publish_date>2000-10-01</publish_date> <description> An in-depth look at creating applications with XML. </description> </book> <book id= "bk102" > <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <price>5.95</price> <publish_date>2000-12-16</publish_date> <description> A former architect battles corporate zombies, an ev...