Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Posts Tagged "SQL"

Introduction to Power Apps (Part I)

Sean Keaveny

Microsoft Power Apps is a brand new release which allows the user to quickly create low or no-code business applications for the PC, tablet, or mobile devices. Microsoft PowerApps is an enterprise level software service created to connect, create, and share business applications. It offers the ability to connect to data from an on-premises server or SaaS services securely, as well as the ability to build cross-platform applications very quickly. By accessing an exposed connection to a repository, and using the templates provided to us from Microsoft, a developer can produce a sophisticated application in less than an hour.
In this blog post, I will show you how to:
1. Use PowerApps to connect to SalesForce, SQL, or SharePoint
2. Setting up a new PowerApps application
3. How to Design Your PowerApp Application
4. How to Add / Delete Entities
Microsoft PowerApps can connect you to…

Improving performance of inserting multiple parent/child tables in a single SQL Procedure

Dan Field

Tom Babiec wrote a great blog a few months back on inserting multiple parent child tables in a single stored procedure. We use this technique a lot in our data integration work, and it’s proven to be very robust in many contexts. The SQL procedure outlined in that blog is useful not just for BizTalk, but generally speaking for ADO.NET and other applications trying to load multiple rows into SQL Server for multiple parent child tables. However, when dealing with larger datasets (and as the table grows), we’ve noticed some degradation in performance. In some cases, we were seeing variances of 30 seconds to 10+ minutes for loading the same exact data set on the same database. We tried a few different options, including forcing a recompile of the stored procedure between each load
WITH RECOMPILE
, but this did not solve…

BizTalk Patterns: Database Assisted Aggregation

Dan Field

Aggregation is a common pattern used in Enterprise Integration.  System (or systems) A sends many messages that System B expects in a single message, or in several messages grouped on a particular attribute (or set of attributes).
The most common way to approach this in BizTalk is using a Sequential Convoy orchestration to aggregate the message – Microsoft provides a sample of how to do this in the SDK. This is a powerful pattern, but has a few downsides:

Sequential Convoys can become difficult to manage if they’re expected to run for a long time
Complex subgrouping can multiply the headaches here – for example, if you have to aggregate messages for hundreds of destinations concurrently
The destination message may become very large, to the point where BizTalk cannot optimally process it anymore – particularly if it is a large flat file message.
Modifying the aggregate…

Inserting into Multiple Parent-Child SQL Tables in a Single Stored Procedure Call using the BizTalk Server WCF-SQL Adapter

Tom Babiec

This post covers how to load records into several tables that have parent-child relationships, via a single stored procedure call and the use of SQL user-defined table types. You may have seen how to use table types for BizTalk SQL loads before (such as in this post), but the approach detailed below shows how to load multiple parent and child records within the same stored procedure, while also executing no more than one insert statement for each table being loaded. (Note that user-defined table types is only available in SQL Server 2008 or later).
Having BizTalk make just one SQL stored procedure call for all records has multiple benefits:

Reduces the number of BizTalk messages going through the BizTalkMsgBoxDb database, as well as the number of roundtrips between BizTalk and SQL Servers, reducing overhead and improving speed of loads
All record loads can…

Capturing and Debugging a SQL Stored Procedure call from BizTalk

Dan Field

So you design your strongly typed stored procedure to take table types from BizTalk and it’s running great with your test cases.  It works well through the unit testing, but then you start running larger jobs and suddenly SQL is choking on it.
Ever wish you could just run that SQL call directly in SSMS with those exact several thousand rows for the table type parameters, and step through it using the debugger?  Well, you can using SQL Server Profiler (and/or Server Traces).  I used this technique recently to help a client resolve a particularly thorny issue that came up when they tried to process some larger messages.
To walk through the process of doing this, I’ll use a database named BTSTrainDb with a stored procedure (dbo.usp_DemoTableTypeSP) that takes a user-defined Table Type (dbo.DemoTableType) as a parameter and then just selects * from…

MABS EAI Bridge LoB Lookup (Part 2 of 2)

Dan Field

Last week month (sorry about that!), I wrote a post about using MABS to access a LoB system (in the example, SQL Server) behind several layers of firewalls (here).
We looked at the following tasks

Creating the BizTalk services
Setting up BizTalk Adapter Services in a local (or IaaS) environment to run a stored procedure in SQL Server
Creating a sample table and stored procedure
Creating a ServiceBus namespace with ACS
Create the Relay to the LOB system
Creating an EAI bridge to access the LoB system

This week, we’ll look at these tasks:

Testing and debugging the bridge with a Visual Studio add on
Writing a custom component to call the LoB adapter in a EAI Bridge Stage and parse the response
Having the component send an email notification using an Office 365 server

MABS EAI Bridge LoB Lookup (Part 1 of 2)

Dan Field

Microsoft Azure BizTalk Services (MABS) has a lot to offer for companies looking for a PaaS Middleware solution.  EAI bridges provide B2B communication as well as LoB access functionality for EDI, XML, and flat file interchanges.  The new mapping system offers some exciting and powerful new functionality as well, vastly simplifying certain tasks that previously required several functiods, and opening up new possibilities and enhanced performance with lists.
However, it is a new technology, and certain tasks that have been very straightforward in BizTalk Server 2013 require a different way of thinking for MABS.  For example, it is a fairly trivial task to create an orchestration that accesses a LoB adapter (using, for example, WCF slqBinding) to do data validation or enhancement, and publishing this orchestration as a web service for client consumption. If your SQL database is SQL Azure, there…

Retrieving Multiple XML Rows from a single row with an XML Column Type in SQL Server

Overview
I’m attempting to pull rows from a table with an XML Field Type.  I’m having issues getting multiple values from a single row.
Setup
Run the following query to set up the table:

CREATE TABLE CourseData (

CourseId int IDENTITY(1,1) PRIMARY KEY,

XmlData XML

)

INSERT INTO CourseData VALUES

(

‘<Course xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns=”http://someuri.local/CourseRecord.xsd”>

<SchemaVersion>1.1</SchemaVersion>

<CourseData>

<CourseCode>AAA999</CourseCode>

<CampusList>

<campus xmlns=”http://someuri.local/campus.xsd”>

<campusId>1</campusId>

</campus>

<campus xmlns=”http://someuri.local/campus.xsd”>

Leonard Lobel Awarded Microsoft MVP for SQL Server!

milyevsky

This month, Microsoft awarded 143 exceptional technical community leaders with the Most Valuable Professional (MVP) title and re-awarded 764 MVPs worldwide. Tallan is thrilled to announce that Lenni Lobel has been recognized as an MVP in SQL Server!
According to the Microsoft MVP Award Program Blog, there are more than 100 million social and technical community members, but only a small portion are selected to be recognized as MVPs. Members of this highly select group of experts are not Microsoft employees; they voluntarily share their passion and real-world knowledge of Microsoft products with others.
In addition to his role as Principal Consultant, Lenni is currently working on the new edition of Tallan’s SQL Server book, “Programming Microsoft SQL Server 2012″ (O’Reilly Media) and speaks regularly at industry conferences and user groups around the country.
Thank you and congratulations to Lenni for his commitment…

The SQL Server OVER Clause

Dylan Barrett

One useful feature in SQL Server is something called the OVER clause. The OVER clause has been available since SQL Server 2005 and allows you to perform window functions over a set of data.  SQL Server provides two types of window functions: aggregate window functions and ranking window functions.  I will explain what aggregate and ranking window functions are below.  The Adventure Works sample database for SQL Server 2008 R2 will be used for all examples. This database models a retail store that sells biking products. Some may want to review the schema for Adventure works, located here, for better understanding.
Aggregate Window Functions
If you have used SQL‘s GROUP BY clause, then you are probably familiar with aggregates.  Aggregate functions allow you to perform a calculation over a set of data records. Each product sold by Adventure Works belongs to a…

\\\