.NET GENES

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&qu

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

Hello, Given that you are required to show the results of a query into an XML format, T-SQL has a function called For XML to achieve that output as shown below. Screenshot I have created two scripts to achieve the desired result as above. The first script will alias a column name with @AttributeName that signifies an attribute which will then be added to Assigned node using FOR XML Path() function. Use NorthwindCrudApp Go SELECT TOP 3 ProductID as "@ProductID" , ProductName as "@ProductName" , UnitsInStock as "@UnitsInStock" , UPPER (ProductName) as [text()] FROM Products As Products ORDER BY ProductID FOR XML PATH( 'Assigned' ), Root( 'DATA' ); The second script also creates column alias for each column using the syntax NodeName/@Attribute similar to an XPath expression. SELECT TOP 3 ProductID as "Assigned/@ProductID" , ProductName as "Assigned/@ProductName" , UnitsIn

I have this old T-SQL script that retrieves previous row value of a certain order year which uses a combination of derived tables, left join and group by to achieve the desired result. Upon visiting this script and doing some research on new T-SQL functions, I encountered an article from SQL Authority on How To Access Previous Row and Next Row in Select statement . So, given the time I quickly converted my script using Lag() function and was surprised to see that my new script looks clean and orderly compared to the old one. Old T-SQL Script SELECT Curr_Data.Year_Order, Curr_Data.Customer_Count AS Customer_Count, Prev_Data.Customer_Count AS Previous_Customer_Count, Curr_Data.Customer_Count - Prev_Data.Customer_Count AS Growth FROM ( SELECT YEAR (Orderdate) AS Year_Order, COUNT ( DISTINCT CustomerID) AS Customer_Count FROM CustomerOrders GROUP BY YEAR (Orderdate)) AS Curr_Data LEFT OUTER JOIN ( SELECT YEAR (Orderdate) A

Given the following records below, you may want to show them in a vertical manner instead of horizontal view of columns. To achieve the desired result, you use UNPIVOT clause in your query. 1 2 3 4 5 6 7 8 Use testdatabase Go SELECT [Subject], Grade FROM StudentReportCard UNPIVOT (Grade FOR [Subject] IN (English, Math, Literature, Biology)) AS U_SubjectGrade WHERE StudentId = 1 Screenshot

I recently created a copy of Northwind database and called it NorthwindBootstrap. This database has a built-in membership tables added to it. The copy includes both of mdf and ldf files. My next step is to create a new database based from the recently copied/renamed files in which I have no idea on how to do it. After doing some research, I stumbled upon a t-sql script based from my requirement. USE [master] GO CREATE DATABASE [NorthwindBootstrap] ON ( FILENAME = N 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\NorthwindBootstrap.mdf' ), ( FILENAME = N 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\NorthwindBootstrap.ldf' ) FOR ATTACH GO Cheers! :)

In programming, it's a common exercise to show triangle shapes using the asterisk character. Well, this can be achieved using TSQL as well. Script #1 DECLARE @Total1 INT DECLARE @ctrOuter1 INT DECLARE @ctrInner1 INT DECLARE @strPrint1 VARCHAR(100) SET @Total1 = 5 SET @ctrOuter1 = @Total1 SET @strPrint1 = '' WHILE @ctrOuter1 >= 1 --outer loop BEGIN SET @strPrint1 = '' SET @ctrInner1 = 0 WHILE @ctrInner1 <= (@Total1 - @ctrOuter1) BEGIN SET @strPrint1 = @strPrint1 + '*' SET @ctrInner1 = @ctrInner1 + 1 END PRINT @strPrint1 SET @strPrint1 = @strPrint1 + CHAR(13) SET @ctrOuter1 = @ctrOuter1 - 1 END Output: Script #2 DECLARE @Total INT DECLARE @ctrOuter INT

Good Day! I forgot to set the identity of the primary key of field Id in table Products. After inserting records through insert statements, an error occurs since the column Id does not allow null values. So, I tried changing the Is Identity to true but unluckily I'm stuck with the an error "Prevent saving changes that require the table re-creation". Additional details are as follows: ID field has a constraint called PK_Products. So, after googling a while I came up with series of steps. Note: You have to close the design view of the table in the Management Studio to perform the steps below. -- 1 ALTER TABLE Products DROP CONSTRAINT PK_Products GO -- 2 Alter Table Products Drop Column Id Go -- 3 Alter Table Products Add Id_new Int Identity (1,1) Go -- 4 Exec sp_rename 'Products.Id_new' , 'Id' , 'Column' Go -- 5 ALTER TABLE Products Add CONSTRAINT PK_Products Primary Key (ID) GO

This was based on a post from SQL Team on how to grab a 5 digit number from the string field which could utilize a regular expression like this: [0-9 ]{5}. The initial solution was given by a forum member named Kristen. It could extract the 5 digit mark on a string field as shown below: DECLARE @strTest varchar(8000) SELECT @strTest = '\\servername\foldername99999\filename.ext' SELECT [ Position ]=PATINDEX( '%[0-9][0-9][0-9][0-9][0-9]%' , @strTest), [ Before ]= SUBSTRING (@strTest, 1, PATINDEX( '%[0-9][0-9][0-9][0-9][0-9]%' , @strTest)-1), [ After ]= SUBSTRING (@strTest, PATINDEX( '%[0-9][0-9][0-9][0-9][0-9]%' , @strTest), LEN(@strTest)) What if the string field would contain two or more 5 digit information, how can you achieve that? The answer is to include while loop in TSQL and some additional string functions to achieve the desired output. Here's a sample stored procedure: USE TESTDB; GO CREATE PROCEDUR