Project 31-A

Symptom The issue was that code that previously worked, update property then save, wasn't working any more. The application is a .NET 4.6 Web API project using Entity Framework 6.  Issue The fix isn't in the code which saved (updated) the entity, but was caused because the entity that is updated isn't correctly formed by the Stored Procedure. Before the SP was added, the entity was returned from EF directly.   Fix After the stored procedure completes, refetch the entity using straight Entity Framework code such as ` await TwitterAccounts.FirstAsync(table => table.TwitterHandle == twitterHandle); ` where TwitterAccounts is a table in the EF Context.  Step to debug issue: capture save result The original EF code to save didn't capture the returned int of number of rows changed.  Before account.IsProperty = true; _context.SaveChanges(); return OK(); After account.IsProperty = true; int result= _context.SaveChanges(); if(result==0) { throw new Exception("proper...

There are several blog posts on this site answering how to copy an Azure SQL database. Since they are older or in some cases lost (Wayne's Microsoft blog posts are gone), I'll link to the Microsoft docs.  Enterprise data or functionality? Don't use these processes if you need change detection or other enterprise-level requirements. Copy the Azure SQL database and keep it in the cloud. Make a copy of your Azure Cloud database To copy an existing Azure SQL Database in the Azure portal, you can copy or export. Generally, I choose to export to Azure Storage Blob container as a bacpac file . This allows me to use it as either a cloud or local database. The bacpac file includes data and schema. Watch for export completion To watch for export completion, from the Azure Server (not database) in the portal, use the  Import/Export History in the Data Management section. Download bacpac file from Azure Storage To download the bacpac file, Azure Storage container in the Azure portal...

I recently moved an Azure SQL database back to a local development database and ran into a few issues. I took these notes so that they might help the next person that hits the problem. In my local SSMS, I use the Import/Export wizard with the datasource using SQL Server native client 11.0. This moves the tables and data. Any destination tables will not have IDENTITY as the source tables did.  Solution # 1  Move away from INT Identity to use GUIDS. This requires work in the database and client code but is the better choice if you need to move data out of the source datatabase then back into the source database.  Solution #2 More immediate fix to get past the block that your inserts don't autoincrement.  Steps: The following steps are completed in SSMS in the destination (local) database and should have all the data but not the IDENTITY column. Rename the mytable to mytable2. Generate CREATE, INSERT, and SELECT scripts for the table. Modify the CREATE script to use the...

Summary Recently I was assigned the task of removing all dependencies on SYSPRO business objects inside an Asp.Net 2.0 application. These are the steps to find underlying SQL calls made by the business objects.    SYSPRO Business Objects SYSPRO Business objects are contained in the Interop.Encore.dll. The returned result is XML for the calls I dealt with.   The data items were, eventually, pulled out of the XML and used as single data points or as lists. You may choose to return XML from the new SQL calls you write.  This allows you to plug in the results and leave the rest of the legacy code.   Since the purpose of my task was to find any and all speed improvements, I removed the XML and dealt directly with SQL results. This required more data layer build-up but the benefit, beyond speed, was that I learned exactly what data was required and how it was ultimately used since I had to reverse engineer all the way to the level of usage.  ...

I recall being asked that question at an interview long ago. In the circles that I had been working we had never used that (academic) term and had been using the diagramming tools in SQL Server Management Studio(SSMS). It was simply what we used. So what are crow’s feet?   It is a diagramming notation which, incidentally, is not supported in SQL Server Management Studio but is supported in third party tools. Since I had been working for Microsoft’s ITG SQL Server Team for several years prior without any third party tools --- I could not return a snappy answer. The origin of this was that the early adapters moved to Oracle, so this because an attitude issue with Microsoft.   Today, crow’s foot notation has become the non-Microsoft industry standard in stead of SSMS infinity key notation. The comical thing is that Microsoft Visio does support crow’s foot notation. A few example tools that uses this notation are: ARIS , System Architect , PowerDesigner , Toad Data Modele...

This blog post by Wayne Berry shows how to migrating your Windows Azure SQL Database Using Data-Tier Application Framework (DacFX) Technology to an on-premise SQL Server. With the Window Azure Portal, you can easily create a Data-Tier Application logical backup package (BACPAC), store it to your Windows Azure Blog Storage; and then, using SQL Server Management Studio 2012, you can import that package to your local database server. Read: Migrating your Windows Azure SQL Database Using Data-Tier Application Framework (DacFX) Technology {6230289B-5BEE-409e-932A-2F01FA407A92}

The Idiot ones I have gathered a few from some interview sites, and the ones below are so trivial that it’s shocking that they were asked!   WriteTSSL to find all the duplicate email address in a table which contains only one column "email" Code Snippet select [email] from [sometable] where [email] is not null OR len ( [email] )= 0 group by [email] having count ( [email] ) > 1   Many answers failed to exclude no email (could be recorded as null or an empty string), or return the count with it (which was not asked for).   Given a table: CustomerOrders: (Customer ID | Order ID | Order Date) 1. Write a SQL to find all customers who has placed an order today. 2. Write a SQL to find all customers who has placed an order today AND yesterday. Again, a trivial one Code Snippet Select CustomerId from CustomerOrders Where OrderDate >= Cast ( GetDate as Date ) Select CustomerId from CustomerOrde...

There are times when you are wanting to produce bar charts that you wish to round times to the quarter hour and keep it as a time. The typical solution is to cast as a varchar() and use the string, unfortunately this works well only for some intervals. Doing this properly is not hard, but you need to be careful not to have numeric calculation artifacts creeping in. Below, I am trying to round everything to the closest quarter hour, other periods just require some change of numbers. 15 minutes –> 24 x 4 =96 Intervals 10 minutes –> 24 x 6 = 144 Intervals 1 minute –> 24 x 60 =1440 Intervals.   The Oops not perfect solution The function below seems to create the needed result – until you test it and discover that the times are not always precise (arithmetic approximation  issues). This can cause problems with some usages.   ALTER FUNCTION QtrHr ( @When DateTime ) RETURNS DateTime AS BEGIN DECLARE @est numeric( 38 , 30 ) ...

I was recently asked for comments on LAMP versus SQL Server. LAMP is a suite of products and SQL Server is a RDBMS, LAMP usually mean MySQL is being used as a RDBMS and thus I will give my thoughts on SQL Server versus MySQL. Often the issue boils down to business factors and not technical issues. License Cost : If the database size is less than 4GB -- same cost -- Zero. SQL Server Express is free. Some existing opinions worth reviewing (i.e. technical nit-picking) http://blog.sqlauthority.com/2009/09/09/sql-server-difference-between-sql-server-express-and-mysql/ http://www.tometasoftware.com/MySQL-5-vs-Microsoft-SQL-Server-2005.asp There's a MySQL synposis at: http://www.mysql.com/news-and-events/on-demand-webinars/display-od-69.html http://stackoverflow.com/questions/336275/mysql-versus-sql-server-express Finding and costs of human resources with expertise level 'X' is a business factor. I used InDeed.Com to get an ideal of salary ranges in the DC area to get a large a...

Often with SQL Server you are faced with the situation of finding records (event) that overlap a search interval as illustrated below. Often I have seen this code up as something complex that follows the above diagram: WHERE (EndAt BETWEEN @StartDate and @EndDate AND StartAt =< @StartDate ) OR (StartAt BETWEEN @StartDate and @EndDate AND EndAt >= @EndDate ) OR (StartAt BETWEEN @StartDate and @EndDate AND EndAt BETWEEN @StartDate AND @EndDate ) OR (StartAt =< @StartDate AND EndAt >= @EndDate ) Needless to say,  8 expressions and 3 ORs is a bit of pain…  An alternative solution is to interchanges the search and event time intervals resulting in a simpler evaluation consisting of 6 expressions and 3 ORs, some improvement.. but still not ideal WHERE ( @StartDate BETWEEN StartAt and EndAt) OR ( @EndDate BETWEEN StartAt and EndAt) OR ( @StartDate =...

  I suddenly noticed that replace only works on ONE CHARACTER strings ….  at least that is what the tooltip claims…

Knowing your data might be the most important quality of the new database administrator (DBA). Just like a marketing person needs to know their customers, a CEO needs to know their market; a DBA needs to truly understand their data. This article is about how knowing your data becomes even more important with cloud databases like SQL Azure Database , and NoSQL style data stores like Cassandra and Windows Azure Storage . SQL Server Using a scale up approach for your on-premise SQL Server database, the SQL Server DBA needed: A good working understanding behind the relationships of the data, i.e. how tables related to each other. This allowed the DBA to develop foreign key to primary key relationships, one of the mantras of a relational database. Understanding how data was being inserted and updated into the table allowed the DBA to optimize their queries for locking, partitions for performance, and transaction logs for growth. Understanding how data was queried from th...

  Scaling up an on-premise SQL Server is the concept of adding more computing resources to your existing hardware, like memory, processing power or storage. The purpose is to allow your SQL Server to handle more queries, gain performance or store more data. When you do decided to go to the cloud you should realize that SQL Azure doesn’t scale up like SQL Server – it scales out. Scaling out is adding additional machines with the same data and dividing the computation between them. This article will discuss the differences between scaling up and scaling out and how you need to plan for scale out on SQL Azure to gain performance. Due to hardware and licensing costs of SQL Server, it is much easier to add resources to existing hardware, than it is to deploy an additional replicated SQL Server on new hardware to gain performance.   The problem with scaling out (adding more machines) in an on-premise SQL Server installation is that this adds a degree of complexity to the des...

Often you have a table where different columns needs to be updated by different calls.  Traditionally, you wrote a stored procedure for every variation which can result in a mountain of code (increases your LOC/hr but drives maintenance cost up more!)   IF you can eliminate NULL as being a valid value (use ‘’ instead?) then you can reduce everything to a single stored procedure as illustrated below.   Create proc harvest.P_UpsertEvent @ID uniqueidentifier = null , @ParentID uniqueidentifier = null , @ConsolidationSequenceNo bigint = null , @Level smallint = null , @EventFulId varchar ( 50 ) = null , @VenueID uniqueidentifier = null , @Title nvarchar ( 120 ) = null , @Text nvarchar ( max ) = null , @Html nvarchar ( max ) = null , @StartAt datetime = null , @EndAt datetime = null , @ExtendedXml xml = null AS ...

In my prior post I described how you can identify the different between a prior production database and a developer’s evolution of the database. You could make the developer responsible for developing the scripts but that is not always the best use of such a resource (do you make the developer responsible for writing all of the automated unit tests for their code and thus do not need testing resources? The appropriate best use of a developer issue is similar). Whether the developer’s does it, the database developer does it or a contracted resource does it – you end up with a batch of TSQL for each ‘upgrade’ (there may be dozens of developer’s upgrades in a product release cycle). This post looks at the management issue.   We have the TSQL to go from Prod –> Dev We want to apply it to some other Prod system   The basic pattern is a series of update files and a console application that reads the TSQL in , configuration information and then apply the informatio...