-
Stages of Data: Some Basic SQL Server Patterns and Practices
By:
Kevin Goff
Last updated: Wednesday, January 31, 2024
Published in: CODE Magazine: 2024 - January/February
This article explores various scenarios in SQL Server data handling, providing recommendations and identifying potential pitfalls. Topics covered include identifying patterns in data scenarios, using subqueries when dealing with multiple one-to-many relationships, logging database changes, handling null values versus empty values, using functions in WHERE and HAVING clauses, cumulative sums, using the PIVOT statement, and dealing with rounding errors.
-
Create Your Own SQL Compare Utility Using GetSchema()
By:
Paul Sheriff
Last updated: Friday, February 24, 2023
Published in: CODE Magazine: 2023 - March/April
Learn how to use the GetSchema() method on the DbConnection class to retrieve tables, views, columns, index, stored procedures, and more from any database system. Create an instance of the SqlConnection or OracleConnection, or your own provider's implementation of the DbConnection class, and pass a valid connection string to the constructor to use the GetSchema() method.
-
Putting Data Science into Power BI
By:
Helen Wall
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2022 - September/October
Power BI seems to have everything you need for data analytics. Helen shows you how to get the most out of it and how to make some cool charts, too.
-
.NET 6: Date and Time
By:
Mike Yeager
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2022 - March/April
Every day, you need to know the date and the time for a myriad of reasons. Mike walks through the current state for using Data and Time types from SQL Server in .NET code.
-
Using Scripting for Working with SQL Server in C#
By:
Vassili Kaplan
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2021 - May/June
Vassili demonstrates how to use scripting in C# projects with SQL Server; learn advantages to write less code. Works well for macOS setup.
-
Tapping into EF Core’s Pipeline
By:
Julie Lerman
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2021 - March/April
Julie describes new EF Core 5 metadata features and how to use them, including ToQueryString, DbCloggerCategory, DbContext.SavingChanges, DbContext.SavedChanges, DbContext.SaveChangesFailed, Dotnet-counters, ChangeTracker.DebugView and Model.DebugView.
-
Calling Stored Procedures with the Entity Framework in .NET 5
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2021 - January/February
Paul shows you how to avoid the frustration of having to determine a stored procedure’s function before you call it by using Entity Framework in .NET 5. Learn how to make calls to stored procedures using Entity Framework in .NET 5.x.
-
Entity Framework Core 3.0: A Foundation for the Future
By:
Last updated: Tuesday, September 22, 2020
Published in: CODE Focus Magazine: 2019 - Vol. 16 - Issue 1 - .NET Core 3.0
The latest release of Entity Framework gets it geared up for some big future changes. Julie shows you that the changes are nothing to sneeze at.
-
Best Practices for Data Visualizations: A Recipe for Success
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2019 - November/December
Helen shows you the ins and outs of creating really useful charts and graphs with Tableau. You’ll never make a boring old pie chart again.
-
Stone Soup: Cooking Up Custom Solutions with SQL Server Machine Learning
By:
Jeannine Takaki-Nelson (free-lance)
Last updated: Tuesday, March 16, 2021
Published in: CODE Magazine: 2019 - November/December
SQL Server 2017 has machine learning services baked right in. If you’ve been wondering how to use it, you’ll be fascinated by what Jeannine’s serves up.
-
Stages of Data #1: A New Beginning
By:
Last updated: Tuesday, April 13, 2021
Published in: CODE Magazine: 2018 - November/December
Kevin leaves his SQL Server-focused Baker’s Dozen behind and launches into a new series looking at the various stages of data warehousing. His first installment includes a look at reporting and storage layers, handy tips, and dealing with the business side of development.
-
Refactoring a Reporting Services Report with Some SQL Magic
By:
Last updated: Monday, April 26, 2021
Published in: CODE Magazine: 2018 - May/June
The point of showing data in graphical form is to make things clear, right? Keven shows you how the best of intentions can go wrong and how to repair the damage.
-
Better Extract/Transform/Load (ETL) Practices in Data Warehousing (Part 2 of 2)
By:
Last updated: Thursday, April 29, 2021
Published in: CODE Magazine: 2018 - March/April
Kevin shows us how to solve tangles in SQL Server, and in this article, he looks at some questions that have come up since his last article on ETL practices in Data Warehousing.
-
A SQL Programming Puzzle: You Never Stop Learning
By:
Last updated: Thursday, May 6, 2021
Published in: CODE Magazine: 2018 - January/February
Kevin learns the hard way how to estimate a process’ duration and he uses a clever bit of code to figure it out.
-
The Baker’s Dozen: 13 Tips for Better Extract/Transform/Load (ETL) Practices in Data Warehousing (Part 1 of 2)
By:
Last updated: Wednesday, May 12, 2021
Published in: CODE Magazine: 2017 - September/October
In this first installment of his ETL articles, Kevin shows you what to do—and even what not to do—when designing your Data Warehouse.
-
SQL Server Reporting Services: Seven Power Tips
By:
Last updated: Wednesday, May 19, 2021
Published in: CODE Magazine: 2017 - May/June
You already know that SQL Server’s Reporting Services tools are useful. Kevin shows you some nifty work-arounds and extra-credit tricks that will make your reports sing.
-
Processing Large Datasets Using C# and SQL Server Table Data Types
By:
Rod Paddock
Last updated: Friday, May 28, 2021
Published in: CODE Magazine: 2017 - January/February
If you’ve got large datasets, you don’t want to slow the system down every time you save them to your SQL Server database. Rod shows you an uncomplicated way to keep your records zipping along.
-
The Baker’s Dozen: 13 Great Things to Know About SQL Server 2016
By:
Last updated: Monday, June 7, 2021
Published in: CODE Magazine: 2016 - September/October
All eyes were on Microsoft when the latest version of SQL Server came out a couple of months ago. If you want to know what’s so great about it, you need to read Kevin’s handy summary.
-
SQL Server Reporting Services: Eight Power Tips
By:
Last updated: Wednesday, June 23, 2021
Published in: CODE Magazine: 2016 - May/June
SQL Server’s Reporting Services is still the best workhorse for getting data to your users. Kevin tells you how to take advantage of some of its more subtle points.
-
Azure Skyline: Azure’s SQL Database—SQL Server in the Cloud
By:
Last updated: Thursday, June 24, 2021
Published in: CODE Magazine: 2016 - March/April
Mike explores Azure SQL Database and what it has the potential to do for large and small businesses.
-
The Baker’s Dozen: 13- SQL Server Interview Questions
By:
Last updated: Friday, June 25, 2021
Published in: CODE Magazine: 2016 - January/February
Kevin uses his experience on both sides of the interview table to help you wow at your next interview. Not only that, but you’ll probably pick up a few pointers, too!
-
The Baker’s Dozen: A 13-Question Pop Quiz of SQL Server Items
By:
Last updated: Tuesday, June 29, 2021
Published in: CODE Magazine: 2015 - November/December
If you’ve ever wondered how your SQL Server knowledge stacked up, you’ll want to take Kevin’s unofficial test. He explains both the right and wrong answers, so no matter what your skills are, you’re bound to learn something new.
-
The Baker’s Dozen: 13 Miscellaneous Transact-SQL Tips
By:
Last updated: Friday, July 2, 2021
Published in: CODE Magazine: 2015 - September/October
Whether you’re new to T-SQL or not, you’re sure to find something interesting among Kevin’s tips for using it, no matter which version of SQL Server you’re using.
-
Baker's Dozen--Business Intelligence Developers
By:
Last updated: Monday, December 27, 2021
Published in: CODE Magazine: 2014 - March/April
If you want to be a great SQL Server developer, you’ll want to pay attention to these 13 key points. Kevin Goff covers both best practices and common pitfalls of Business Intelligence.
-
The Baker’s Dozen: 13 Examples Using XMLA to Update Analytic Databases
By:
Last updated: Tuesday, April 26, 2022
Published in: CODE Magazine: 2013 - September/October
Database developers who learn Microsoft SQL Server Analysis Services (SSAS) know that they face a number of learning curves. In prior Baker’s Dozen articles, I’ve covered many of the steps for creating both SSAS OLAP and SSAS Tabular databases. In this article, I’m going to cover another topic: How to add or change data in analytic databases.
-
Building a CODE Framework Service and Consuming It on an iPhone Application
By:
Milton Abe
Last updated: Tuesday, May 31, 2022
Published in: VFP Conversion Papers, CODE Magazine: 2013 - May/June
In this article, you will create a CODE Framework RESTful service and an iPhone application from scratch. For the client side, you will utilize XCode (yes, you’ll need a Mac!), which uses Objective-C as the primary language. This article won’t teach you the language; you need to know the basics of Objective-C. Even if you don’t know anything about it but want to code right away, read the article “Building a Twitter Search Client on iOS,” by Ben Scherman, available for all CODE readers in the Xiine application for Windows, Android, and iPhone. For the database, you’ll use any instance of SQL Server 2008. The service will be written in C#, using the latest version of CODE Framework, available on http://codeframework.codeplex.com, where you will find not only the download link, but also a lot of useful information.
-
The Baker’s Dozen: 13 Tips for Basics of Data Warehousing and Dimensional Modeling
By:
Last updated: Wednesday, June 8, 2022
Published in: CODE Magazine: 2013 - March/April
Imagine you’re working for a company that has accumulated a tremendous amount of transaction data. The business users want to perform all sorts of analysis, monitoring and analytics on the data. Some OLTP developers might reply with, “Just create views or stored procedures to query all the data the way the users want.” Many companies initially take that approach - however, just like certain technologies and system hardware configurations don’t scale well, certain methodologies don’t scale well either. Fortunately, this is where data warehousing and dimensional modeling can help. In this article, I’ll provide some basic information for developers on the basics of data warehousing and dimensional modeling - information that might help you if you want to provide even more value for your company.
-
The Baker’s Dozen: 13 Steps for Building a SQL Server Analysis Services 2012 Tabular Model
By:
Last updated: Thursday, November 30, 2023
Published in: CODE Magazine: 2013 - January/February
For several years, database developers have created analytic (OLAP) databases using tools such as Microsoft SQL Server Analysis Services. SSAS includes the MDX programming language for retrieving data and writing custom expressions. These tools remain very powerful for creating analytic applications. However, some view the tools in SSAS/MDX as difficult to learn. In SQL Server Analysis Services 2012, Microsoft has created a second model (known as the tabular model) for creating analytic databases. The new model brings the promise of simplified features and (in some cases) even better performance than traditional OLAP. In this article, I’ll provide an overview for this new model and will walk through a basic example of creating an SSAS 2012 tabular model database.
-
The Baker’s Dozen Doubleheader: 26 Productivity Tips for Optimizing SQL Server Queries (Part 2 of 2)
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2012 - November/December
In part two of this series on optimizing SQL Server queries I’m going to continue with some T-SQL scenarios that pit one approach versus another. I’ll also look at what SQL developers can do to optimize certain data access scenarios. I’ll also compare approaches with temporary tables versus table variables, and stored procedures versus views.
-
The Baker’s Dozen: 26 Productivity Tips for Optimizing SQL Server Queries (Part 1 of 2)
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2012 - July/August
There’s an old programmer adage: “First you make it work, then you make it work fast.” Well, when writing T-SQL queries, you can do both, if you have some knowledge about how the SQL Server optimizer works. This will be a two-part article. In part one, I’ll start with fairly basic optimization tips and techniques for writing SQL queries. In part two, I’ll cover more advanced techniques.
-
The Baker’s Dozen Doubleheader: 26 New Features in SQL Server 2012 (Part 1 of 2)
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2012 - March/April
When I was a kid, I loved baseball. I lived it 24/7. In the summertime, happiness meant a pickup game during the day and a Phillies doubleheader at night. I’m still a kid at heart and I still love baseball - and I also love SQL Server. And right now, happiness means seeing all the cool new features in SQL Server 2012. There are so many of them that I can’t list them in a single article. So, I’m penning a two-part Baker’s Dozen. The first part of this “twin-bill” (yes, expect a few baseball analogies!) will be 13 new T-SQL and database engine features in SQL Server 2012. The “night-cap” in the next issue will be 13 new features in SQL Server Integration Services and the new Business Intelligence Semantic Model.
-
The Baker’s Dozen: 13 Miscellaneous SQL Server Tips
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2012 - January/February
To use a music analogy, many installments of “The Baker’s Dozen” have been like “concept albums,” where most or all of the tips work towards a big picture. Then there are times where I present a series of random tips that are largely standalone and don’t form a pattern. In this article, I’m going to present 13 random tips for SQL Server and T-SQL programming.
-
The Baker’s Dozen: 13 Transact SQL Programming Tips
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2011 - November/December
Even with all the new features in the Microsoft SQL Server Business Intelligence (BI), sometimes the only way to accomplish a task is with good old fashioned T-SQL code. (Fortunately, “code” is the acronym for this great magazine!) In this latest installment of The Baker’s Dozen, I’ll present 13 T-SQL programming tips that could help you accomplish different database tasks.
-
Using Configurations to Manage SQL Server Extended Properties
By:
John Petersen
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2011 - May/June
Before you start hard coding parts of your project to work with metadata in SQL Server, make sure that the functionality you want isn’t already part of the product.
-
The Baker’s Dozen: 13 Examples of Functionality in SQL Server 2008 Integration Services
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2010 - July/August
A skilled database developer might find it difficult to accept that other tools can increase productivity. When Microsoft released SQL Server 2005 Integration Services (SSIS 2005), I did not think that any tool could possibly make me more productive than the C# and T-SQL code I was writing by hand. After some reconsideration (and subtle persuasion from peers), I discovered that SSIS 2005 contained many features that indeed reduced my development time - WITHOUT sacrificing flexibility. Microsoft added new functionality in SSIS 2008 to make a strong product even better. In this article, I’ll present 13 different examples that demonstrate the power of Integration Services.
-
Developing Database Agnostic Applications with Entity Framework
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2010 - March/April
Microsoft released Entity Framework, an ORM (Object Relational Mapping) tool, in 2008.Entity Framework gives developers the ability to be abstracted from the underlying relational database management system and allows them to talk to a database using familiar LINQ-based syntax.
-
Handling Business-Related Date Tasks in T-SQL
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2010 - March/April
How many times have you found yourself in need of knowing what the next or previous business date is?What about the dates when a particular quarter begins and ends? Have you had to handle the thorny issue of daylight savings time (DST); when does DST begin and end for this or another year in the US, UK or some other region? And finally, once you have determined you have a need to calculate these various date values, where do you host that logic? This article answers those questions.
-
Using the Amazon Web Service SDK for .NET
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2010 - March/April
The richest set of cloud computing services comes from a little e-commerce company known as Amazon.com. Developers can access the Amazon Web Services (AWS) platform using numerous tools including the .NET platform.Amazon.com is a major player in the cloud computing space and has numerous services available to developers. In late 2009, Amazon released the AWS SDK for .NET. This article will demonstrate using the AWS SDK to create a custom backup service using the Amazon Simple Storage Service (S3).
-
What’s New in Entity Framework 4, Part 2: Modeling Changes
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2009 - November/December
If you have been working with the ADO.NET Entity Framework, you have probably been extremely eager to get your hands on the next version that is now part of Visual Studio 2010 and .NET 4.0. Long referred to as “EF Version 2,” this version is now called Entity Framework 4 or EF4, to align with the .NET Framework 4.0 version.
-
Post Mortem: Tower48 Software Escrow
By:
Markus Egger
Last updated: Saturday, January 18, 2020
Published in: CODE Magazine: 2009 - September/October, Markus Egger Talks Tech
-
SQL Server 2008: Two Winning Features
By:
Talmage, Ron
Last updated: Friday, February 22, 2019
Published in: CODE Magazine: 2009 - September/October
SQL Server 2008 has been out for more than a year, and perhaps an overview and assessment is due. From a database administrator’s point of view, what features are standing the test of time?
-
SQL Server Reporting Services Hits its Stride
By:
Litwin, Paul
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2009 - September/October
We’ve all heard that version 3 of a Microsoft product is when that product really hits its stride.And while I’m not sure of the truth of that software urban legend, I do have to say that Microsoft, with the release of SQL Server 2008 Reporting Services (which happens to be its 3rd major version), has released a gem of a reporting solution that is sure to please users of prior versions and bring more converts into the Reporting Services fold. The newest version of SQL Server Reporting Services is faster, can handle larger reports, supports a wider variety of browsers, and is more flexible thanks to the new Tablix data region, support for rich text content, and Microsoft Word as an output format.
-
What’s New in Entity Framework 4? Part 1: API Changes
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2009 - September/October
If you have been working with the ADO.NET Entity Framework, you have probably been extremely eager to get your hands on the next version that is now part of Visual Studio 2010 and .NET 4.0. Long referred to as “EF Version 2,” this version is now called Entity Framework 4 or EF4, to align with the .NET Framework 4.0 version.
-
The Baker’s Dozen: 13 Miscellaneous C#, SQL, and Business Intelligence Development Tips
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2009 - January/February
No, the title isn’t a misprint-this installment of the Baker’s Dozen will visit both sides of the planet. These days, many .NET user group meetings focus on database and business intelligence topics as well as hardcore .NET content. Over the last several months, I’ve spent roughly half my time modifying my own development framework for WCF. The result is some basic but functional factory classes I’d like to share. The other half of the time, I’ve been looking at different capabilities in the SQL Server 2005 and 2008 Business Intelligence stack, and solving requirements that BI developers often face. So rather than pick one side and make the other side wait two months, I decided to combine the two.
-
Ask the Doc Detective
By:
Doc Detective
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2008 - November/December
Nov/Dec 08 Doc Detective column
-
Data Access Options in Visual Studio 2008
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2008 - September/October
With Visual Studio 2008 and .NET 3.5, developer’s data access options have increased substantially. In addition to using ADO.NET to create DataReaders or DataSets, Microsoft has added LINQ to SQL and Entity Framework as well as ADO.NET Data Services, which leverages those two. In addition to these new options, there are new syntaxes to learn. LINQ, which is built into Visual Basic and C#, has one implementation for LINQ to SQL and another for LINQ to Entities. In Entity Framework, you have the option to use LINQ to Entities as well as two other ways of querying with Entity SQL, as you can see in Figure 1.
-
The Baker’s Dozen: 13 Tips for SQL Server 2008 and SSRS 2008
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2008 - July/August
A major software release either contains a substantial number of enhancements, or a small number of substantial enhancements.The last two releases of SQL Server remind me of the two types of Christmas: SQL 2005 was like receiving a large number of presents, and SQL Server 2008 is like receiving a smaller number of big gifts. Those of you who asked Santa for the ability to INSERT/UPDATE/DELETE in one SQL statement, or to specify multiple GROUP BY statements in a single SQL query, you will be very happy. If you wanted a hierarchical data type, you will be ecstatic. SQL Server 2008 also includes a new and completely re-written version of SQL Server Reporting Services (SSRS)-complete with a new interface, new charting features, and (get ready to applaud) no more reliance on Internet Information Services (IIS). So while the temperatures are getting warmer, it’s Christmas Day here at the Baker’s Dozen, and we’re about to unwrap thirteen major SQL Server/SSRS 2008 gifts from Microsoft.
-
Heard on .NET Rocks! Andy Leonard on Unit Testing Your Database
By:
Franklin, Carl
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2008 - May/June
May/June 2008 .NET Rocks! column
-
The Baker’s Dozen: 13 Tips for Building Database Web Applications Using ASP.NET 3.5, LINQ, and SQL Server 2005 Reporting Services
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2008 - May/June
Are you moving a Windows desktop application to the browser, and sweating bullets, or perhaps just not quite sure about how all the new Web and data tools work together?With each passing year, Microsoft offers newer and more powerful tools for building rich database applications on the Web. So many and so frequently, in fact, that it can be hard to keep up with the new tools and still meet the requirements of your job! This article will show you how to get the most out of the new features in ASP.NET 3.5. The article will also show how you can use features in LINQ, even if you only use stored procedures for data access. And finally, since most applications use reporting, I’ll throw in a few nuggets on using SQL Server Reporting Services.
-
The Baker’s Dozen: 13 Tips for Building Dashboards with Microsoft BI Tools
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2008 - March/April
Scorecards, test results, report cards, summaries-nearly everyone wants to skip past the details and see the bottom line. Whether it is “pass or fail”, “go or no go”, managers evaluate professional efforts based on performance. Microsoft’s Business Intelligence tools provide developers and power users with the tools and methodologies to produce scorecards and other summaries that graphically represent performance. This article will create a Web-based Dashboard using several Microsoft products, including the newest product in the BI stack, PerformancePoint Server.
-
Building WCF Services for Deployment in Transiently Connected Networks
By:
Rick Garibay
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2008 - January/February
Distributed applications are now prolific in the enterprise and more and more users are relying on network connectivity both on site and on the go so they can remain productive anywhere and at any time. Since network connectivity cannot always be guaranteed, what happens when the network goes down or a network connection is simply unavailable? How can you provide your users with the best connected experience regardless of the state of the network?
-
C# 3.0 Syntax Additions-Design Guidelines
By:
Ritchie, Peter
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2008 - January/February
C# 3.0 includes a few syntactical additions to the language. For the most part, Microsoft added these language additions to support Language Integrated Query (LINQ). These features include (but are not limited to) lambda expressions, extensions methods, anonymous types, implicitly typed local variables, automatic properties, and object initializers.
-
The Baker’s Dozen: 13 Tips for Querying OLAP Databases with MDX
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2008 - January/February
MDX is just like Transact-SQL, right? MDX is English-like and therefore easy to learn, right? Different software tools offer MDX generators and therefore I don’t need to learn MDX, right? MDX isn’t that popular, right?Well, just like the punch-line of the old Hertz car rental commercial, “Not exactly.” If your organization uses OLAP databases, you can add great value by knowing how to query OLAP databases using MDX queries. This article will cover common OLAP query requirements and MDX code solutions.
-
Caching with SQL Server Compact and the Microsoft Sync Framework for ADO.NET
By:
Lasker, Steve
Last updated: Wednesday, August 31, 2022
Published in: CODE Focus Magazine: 2007 - Vol. 4 - Issue 3 - Data Programability
With Sync Services for ADO.NET, developers can easily optimize their online experience by caching data locally within the easy-to-deploy SQL Server Compact embedded database engine.In this article I’ll cover how Sync Services for ADO.NET was designed to fit the growing developer needs for caching data locally in online-optimized, offline-enabled applications.
-
Introducing the Microsoft Sync Framework: Next Generation Synchronization Framework
By:
Khosravy, Moe
Last updated: Tuesday, February 19, 2019
Published in: CODE Focus Magazine: 2007 - Vol. 4 - Issue 3 - Data Programability
The Microsoft® Sync Framework is the new framework and runtime for adding synchronization, roaming, and offline capabilities to applications. It supports peer-to-peer scenarios, works with devices and services, and is agnostic of data types, stores, and protocols. In this article, I’ll cover the high-level vision for the platform as well as the enabled scenarios made possible by the framework for developers, ISVs, and OEMs.
-
LINQ to Relational Data: Who’s Who?
By:
Flasko, Elisa
Last updated: Tuesday, February 19, 2019
Published in: CODE Focus Magazine: 2007 - Vol. 4 - Issue 3 - Data Programability
With the combined launch of Visual Studio 2008, SQL Server 2008, and Windows Server 2008, Microsoft is introducing five implementations of .NET Language Integrated Query (LINQ).Of these five implementations, two specifically target access to relational databases: LINQ to SQL and LINQ to Entities.
-
ODBC Rocks!
By:
Lee, Chris
Last updated: Tuesday, February 19, 2019
Published in: CODE Focus Magazine: 2007 - Vol. 4 - Issue 3 - Data Programability
Fifteen years after its launch, ODBC is a firmly entrenched cornerstone of the software industry. This article explains why and will explore the relationship between Microsoft SQL Server and ODBC and discuss where ODBC may go in the future.
-
Programming SQL Server 2008
Last updated: Wednesday, February 20, 2019
Published in: CODE Focus Magazine: 2007 - Vol. 4 - Issue 3 - Data Programability
SQL Server Katmai, now officially announced as SQL Server 2008, introduces a significant amount of new and improved functionality, including enhanced data types and greater programming flexibility for database application developers.
-
The Data Dude Meets Team Build
By:
Drapers, Gert
Last updated: Wednesday, February 20, 2019
Published in: CODE Focus Magazine: 2007 - Vol. 4 - Issue 3 - Data Programability
“Integrate the data tier developer in to the core development life cycle and process.”That is one of the main objectives of Visual Studio Team Edition for Database Professionals, also known under its project name “Data Dude”. Bringing the data tier developer into Visual Studio is the first step in enabling closer integration between the application and data tier developer. Having both environments leverage the same Team Foundation Build (Team Build) system enables daily and automatic integration of changes into the build process, enforcing closer integration and shorter feedback cycles between the two originally disjoint disciplines.
-
Introducing ADO.NET Entity Framework
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2007 - November/December
The challenge of bringing data from efficient storage engines such as SQL Server into object-oriented programming models is hardly a new one. Most developers address this challenge by writing complex data access code to move data between their applications and the database. This requires an understanding of the database so that you can access data either from the raw tables, from views, or from stored procedures.</b>
-
The Provider Model
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2007 - November/December
In this article you will learn how to isolate yourself from change by taking advantage of the Provider Model.Designing your applications using the Provider Model will allow you to swap components out at runtime, thus allowing you to upgrade them easily.
-
The Baker’s Dozen: 13 Productivity Tips for Transact-SQL 2005
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2007 - September/October
Thinking of upgrading to SQL Server 2005? Or are you using SQL 2005 already and you want to learn more about the language features? Then this article may be for you!Microsoft implemented many new features in SQL Server 2005, including an impressive set of language enhancements. From new language statements for SQL-99 compatibility to new features in response to customer requests, Transact-SQL 2005 helps to increase developer productivity. In this article, I’ll cover most of the new language features by posing a statement/scenario and then provide some code samples to show how you can use T-SQL 2005 to address the problem. At the end of the article, I’ll talk briefly about Visual Studio Team Edition for Database Professionals, a product that helps a development team to manage databases. Finally, I’ll give you a sneak preview of some features in the next scheduled version for SQL Server (SQL Server 2008, “Katmai”).
-
It All Adds Up
By:
Moreau, Tom
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2007 - July/August
Tom’s having a lot of fun playing with SQL Server 2005’s T-SQL enhancements.In this article he combines a bunch of them in concert to solve an interesting problem.
-
Heard on .NET Rocks!: Paul Randal on SQL Server 2005 Performance and Recovery
By:
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2007 - May/June
May/June .Net Rocks Column
-
SQLCLR Security and Designing for Reuse
By:
Machanic, Adam
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2007 - May/June
An important principal of software design is that of “least privilege.”Basically, in any given layer of a program, you should only grant minimal access such that the code has rights to only exactly the resources it needs to get its job done-and nothing more. Most SQL Server developers understand this concept: one of the main reasons to use stored procedures is to encapsulate permission to data behind controlled and auditable interfaces, thereby not giving the caller direct access.
-
The Baker’s Dozen: 13 Steps for Building a Lookup Page Using ASP.NET 2.0, SQL 2005, and C# 2.0
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2007 - March/April
Lookup and navigation screens initially seem like no-brainers, when compared to other parts of an application-yet by the time a developer has met all the user requirements and requests, he/she has expended much more time than expected. This issue of The Baker’s Dozen will build a lookup Web page using ASP.NET 2.0, SQL Server 2005, and C# 2.0. The lookup and results page will contain optional lookup criteria and custom paging of the result set. The solution will utilize new language features in SQL 2005 for providing ranking numbers to correlate with custom paging, and new capabilities in .NET generics to pump the results of a stored procedure into a custom collection. Just like Mr. Mayagi taught Daniel the martial arts by doing exercises, the example in this article will demonstrate some common design patterns, such as factory creation patterns, using .NET generics. The article also subtly presents a general methodology for building database Web pages.
-
Chapter 3: Enterprise Data Management
By:
Eric L. Brown
Last updated: Wednesday, February 20, 2019
Published in: Book Excerpts
SQL Server2005 Distilled Need to get your arms around Microsoft SQL Server 2005 fast, without getting buried in the details? Need to make fundamental decisions about deploying, using, or administering Microsoft’s latest enterprise database? Need to understand what’s new in SQL Server 2005, and how it fits with your existing IT and business infrastructure? SQL Server 2005 Distilled delivers the answers you need–quickly, clearly, and objectively. Former SQL Server team member Eric L. Brown offers realistic insight into every significant aspect of SQL Server 2005: its new features, architecture, administrative tools, security model, data management capabilities, development environment, and much more. Brown draws on his extensive experience consulting with enterprise users, outlining realistic usage scenarios that leverage SQL Server 2005’s strengths and minimize its limitations.
-
Chapter 3: Relational Databases 101
Last updated: Tuesday, February 19, 2019
Published in: Book Excerpts
An excerpt from HitchhikerGuide to Visual Studio and SQL Server Since 1994 when he wrote his first "Hitchhiker's Guide," William Vaughn has been providing developers all over the world the intimate details of how SQL Server can be accessed and managed from RAD languages like Visual Basic and Visual Basic .NET. With the 7th Edition, Bill has completely rewritten this encyclopedic work from cover to cover–giving readers his insightful views on how applications should be built to maximize both developer and code performance. Visual Studio and the languages it hosts have never been as sophisticated as they are today–the same can be said for SQL Server. This makes it even more important for developers to understand how to best leverage their features without being held back by their complexity. That’s what this book is all about–making it easier for developers regardless of their know-how. Key topic coverage includes: • Data access architectures and how to choose the best strategy for Windows Forms, ASP.NET, XML Web Services, and SQL Server CLR executables. Where do these make sense and how much will they cost to build and maintain? • SQL Server and relational database fundamentals and inner-machinery. How does SQL Server work and why is it important that developers know? • Making the development experience more productive through judicious use of the Visual Studio toolset, and how to know when the wizards can help. • Using the latest ADO.NET data provider efficiently and safely. • How to protect the security of your database–and your job–by avoiding common mistakes. • How to build secure, efficient, scalable applications in less time with fewer resources–how to create faster code faster. • How to leverage the potential of SQL Server CLR executables and knowing when these features make sense. • How to work with your DBA to maintain database integrity and security. • Working with the new Visual Studio report controls to expose your organization’s data safely and easily with or without leveraging existing SQL Server Reporting Services technology.
-
Chapter 3: Reporting Services Architecture
Last updated: Tuesday, February 19, 2019
Published in: Book Excerpts
Excerpt from Microsoft SQL Server 2005 Reporting Services by Michael Lisin and Jim Joseph
-
Essential LINQ
Last updated: Saturday, January 18, 2020
Published in: Book Excerpts
LINQ is one of Microsoft’s most exciting, powerful new development technologies. Essential LINQ is the first LINQ book written by leading members of Microsoft’s LINQ and C# teams. Writing for architects, developers, and development managers, these Microsoft insiders share their intimate understanding of LINQ, revealing new patterns and best practices for getting the most out of it.
-
Microsoft SQL Server 2012 Management and Administration, 2nd Edition, Chapter 2 - Administering Storage, I/O, and Partitioning in Microsoft SQL Server 2012
Last updated: Tuesday, February 19, 2019
Published in: Book Excerpts
By Ross Mistry, Shirmattie Seenarine, Published Sep 18, 2012 by Sams. Copyright 2013, Dimensions: 5-3/8" x 8-1/4", ISBN-10: 0-672-33600-6, ISBN-13: 978-0-672-33600-3. Sample Chapter is provided courtesy of Sams Publishing. Click here to purchase book. http://www.informit.com/store/microsoft-sql-server-2012-management-and-administration-9780672336003
-
Microsoft® SQL Server® 2012 Step by Step - Chapter 1
Last updated: Wednesday, February 20, 2019
Published in: Book Excerpts
By Patrick LeBlanc, Publisher: Microsoft Press, Released: January 2013, http://shop.oreilly.com/product/0790145338273.do?sortby=publicationDate
-
More Effective C#: 50 Specific Ways to Improve Your C# - Chapter 5 - Working with LINQ
Last updated: Wednesday, February 20, 2019
Published in: Book Excerpts
The driving force behind the language enhancements to C# 3.0 was LINQ. The new features and the implementation of those features were driven by the need to support deferred queries, translate queries into SQL to support LINQ to SQL, and add a unifying syntax to the various data stores. Chapter 4 shows you how the new language features can be used for many development idioms in addition to data query. This chapter concentrates on using those new features for querying data, regardless of source.
-
Oracle SQL and PL/SQL 2nd Edition for Developers; Chapter 2
Last updated: Tuesday, February 19, 2019
Published in: Book Excerpts, Newsletters
In the last chapter, you learned about some of the SQL statements that you can use to work with the data in a relational database. Before you learn the details of coding these statements, however, you need to learn how to work with an Oracle database and how to use Oracle SQL Developer to enter and execute SQL statements. It's also helpful to know how to use the SQL Reference manual.
-
Oracle SQL and PL/SQL 2nd Edition for Developers; Chapter 3
Last updated: Tuesday, February 19, 2019
Published in: Book Excerpts, Newsletters
This section teaches you the essential SQL coding skills for working with the data in an Oracle database. The first four chapters show you how to retrieve data from a database using the SELECT statement. In chapter 3, you’ll learn how to code the basic clauses of the SELECT statement to retrieve data from a single table.
-
What Is Integration Services and Why Do I Need It?
Last updated: Wednesday, February 20, 2019
Published in: Book Excerpts
This chapter is a brief introduction to Integration Services, its origins, its history, and a high-level view of what Integration Services is and how it can be used.
-
Enterprise Reporting with Excel
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2007 - January/February
When it comes to analysis and reporting, managers love Excel. Just give them the raw data and they have a field day. For enterprise-level reporting, however, you want everybody to have the same data and the same interpretation of that data. With some effort this can be achieved without having to say goodbye to Excel.
-
Integrating .NET Code and SQL Server Reporting Services
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2007 - January/February
SQL Server Reporting Services versions 2000 and 2005 (SSRS) has many powerful features. SSRS has a well-designed data access engine, a great set of layout tools, and an excellent expression system for creating complex formulas. While the expression system is quite powerful it is not suitable for all applications. This is where SSRS shines. SSRS gives developers the ability to add custom code to their report layouts. This article demonstrates adding custom code to SQL Server Reporting Services reports.
-
Designing for Database Movability
By:
Last updated: Wednesday, February 20, 2019
Published in: SQL Server Observer
Eventually SQL Server databases in a production environment must move from one server to another. It’s important to plan for eventual database moves when architecting a database solution.Designers and developers often overlook the eventuality that a database will need to move to a different server. This month’s column is the first of three dealing with the issues surrounding database moves. In this first part, you’ll learn about the reasons why you should plan for moving a database when you design and architect your application.
-
How Developers Can Avoid Transact-SQL Cursors
By:
Last updated: Wednesday, February 20, 2019
Published in: SQL Server Observer
Ron Talmage discusses how developers can avaiod Transact-SQL Cursors
-
Methods for Moving a Database
By:
Last updated: Wednesday, February 20, 2019
Published in: SQL Server Observer
Moving a SQL Server database from one server to another is simple-initially. But there are various methods for moving a database, and some have more advantages than others. Investigating the types of moves ahead of time can make planning for a database migration easier.It takes essentially three steps to move a database from one server to another: take the database out of production, copy it to another server, and then bring the copy back into production. While you can choose from many methods to accomplish these steps, it can be difficult to complete the move given all the database’s dependencies. In this article I’ll describe the methods for moving the database. In my next installment I’ll discuss the dependencies and additional related objects that you must also move along with the database data.
-
Moving a Database: Dependencies
By:
Last updated: Wednesday, February 20, 2019
Published in: SQL Server Observer
xIn the past two installments of this series I’ve discussed two key aspects of moving a SQL Server database from one server (or instance) to another. First I covered the advantages of designing databases to make them easier to move, and then I looked at the methods that you can use to move a database. Now it’s time to look specifically at the types of database dependencies that you must account for after you move a database.
-
Performance Counters on the 64-bit Road Less Travelled
By:
Last updated: Wednesday, May 11, 2022
Published in: SQL Server Observer
Ron Talmage discusses performance counters in SQL Server.
-
Scaling Out Database Writes
By:
Last updated: Tuesday, February 19, 2019
Published in: SQL Server Observer
Ron Talmage discusses scaling out database writes.
-
SQL Server 2005: Scaling Up and Out
By:
Last updated: Friday, February 22, 2019
Published in: SQL Server Observer
Ron Talmage discusses SQL Server 2005 in this introductory SQL Server Observer Newsletter
-
SQL Server 2008 Under the Hood: Compression Technologies
By:
Last updated: Saturday, January 18, 2020
Published in: SQL Server Observer
-
SQL Server Database Mirroring Roles: Instance versus Database
By:
Last updated: Wednesday, February 20, 2019
Published in: SQL Server Observer
Ron Talmage discusses the roles of SQL Server databases and instances in high availability scenarios.
-
The x64 Path Less Travelled
By:
Last updated: Wednesday, May 11, 2022
Published in: SQL Server Observer
Ron Talmage discusses 32-bit vs. 64-bit version of SQL Server 2005
-
Heard on .NET Rocks!: Kimberly Tripp on SQL Server 2005 SP1 and More
By:
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2006 - September/October
Heard on .NET Rocks!: Kimberly Tripp on SQL Server 2005 SP1 and More
-
The Baker’s Dozen Doubleheader: 26 Productivity Tips for Managing Data (Part 2 of 2)
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2006 - September/October
In the conclusion of this two-part series on managing data, I will focus on .NET generics, the ASP.NET 2.0 ObjectDataSource, and some more T-SQL 2005 capabilities. Regardless of whether you work in C# or VB.NET, or whether you spend more time on the front-end or back-end of an application, the ability to manage data will directly impact your contributions to a project. The new .NET generics classes provide you with near-quantum leap in efficiency by allowing you to write simpler code that accomplishes more. ASP.NET 2.0’s ObjectDataSource class helps you to easily integrate middle-tier data-aware classes to data-bound controls on a Web page. Finally, as you saw in Part 1 of this series (and will see again here in Part 2), T-SQL 2005 provides new language capabilities to help database developers be more productive.
-
Making Sense of the XML DataType in SQL Server 2005
By:
Shawn Wildermuth
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2006 - May/June
As database developers, many of us have had to dip our feet into the wide ocean of XML.It should come as good news that in SQL Server 2005, you can store XML in the database with a new XML datatype. Although this is good news, many developers have been storing XML in the database for some time now. Without implicit support for XML, developers have been shoving XML documents into text fields since XML’s inception.
-
SQL Server 2005 Query Notifications Tell .NET 2.0 Apps When Critical Data Changes
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2006 - May/June
One of the classic problems with database applications is refreshing stale data. Imagine a typical e-commerce site with products and categories. A vendor’s product list most likely does not change very often and their category list changes even less frequently. However, those same lists must be queried from the database over and over again every time a user browses to that Web site. This is an annoyingly inefficient use of resources and developers and architects have been stuck playing cat-and-mouse trying to reduce the waste.
-
Security in the CLR World Inside SQL Server
By:
Kiely, Don
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2006 - March/April
One of the major benefits of writing .NET code to run in the Common Language Runtime (CLR) hosted in any environment is code access security (CAS).CAS provides a code-based-rather than user-based-authorization scheme to prevent various kinds of luring and other code attacks. But how does that security scheme coexist with SQL Server 2005’s own, newly enhanced security features? By default your .NET code is reasonably secure, but it’s all too easy for the two security schemes to butt heads and cause you grief. In this article I’ll look briefly at the concept behind CAS and a few new security features in SQL Server 2005, then explore how to make the two systems work for you instead of against you as you take advantage of these advanced programming features in SQL Server.
-
Ink And The Database
By:
O'Brien, Larry
Last updated: Thursday, February 21, 2019
Published in: CODE Focus Magazine: 2005 - Vol. 3 - Issue 1 - Tablet PC and Mobile PC
Unless your battery is really, really good, you’ll eventually want to store your Ink.In some situations, simple file storage or XML serialization is sufficient for your needs, but in the majority of business applications, you’ll want to move Ink into and out of a relational database. Here’s how.
-
Building a Stored Procedure Generator
By:
Deborah Kurata
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2005 - September/October
Creating basic data access stored procedures is time consuming and boring work. Relieve the tedium by writing code that writes these stored procedures for you.
-
SQL Server 2005 Secures Your Data Like Never Before
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2005 - September/October
If you care about your data, you must upgrade to SQL Server 2005 the day it is released. There simply is no other option.An outrageous assertion? Perhaps. I tend to split my time equally between praising and bashing Microsoft, but the new security features and tools in SQL Server 2005 will be mandatory for protecting your data from today's increasingly sophisticated attacks. Most importantly, SQL Server 2005's many layers of security provide for defense in depth in which layer after layer of protection helps keep data safe.
-
Two Dozen of My Favorite System Stored Procedures
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2005 - May/June
SQL Server 2000 is packed full of useful system stored procedures there for its own use that you can use as well.This article will explore a couple dozen of the most useful hidden gems you can use to more easily manage your databases, improve the applications that use the data, and provide new ways to impress potential mates at parties. You'll learn when to use these system stored procedures, explore when to use them, and see lots of code.
-
The Baker's Dozen: 13 Productivity Tips for Database Development Using Transact-SQL
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2005 - March/April
Many application developers face the challenges of working with SQL Server 2000.These challenges include retrieving and grouping large amounts of data, building result sets, and tracking changes to data. All require professional strategies that hold water against a seemingly endless number of possibilities. This installment of "The Baker's Dozen" presents a variety of real-world database situations and how you can use Transact-SQL and SQL-92 to tackle these issues.
-
SNAPSHOT Isolation in SQL Server 2005, Part II
By:
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2005 - January/February
In addition to the transaction-based SNAPSHOT isolation level, SQL Server 2005 Beta 2 also introduces a statement-level variation of the READ COMMITTED isolation level called READ_COMMITTED_SNAPSHOT.Part I of this article (see "Snapshot Isolation in SQL Server 2005," July/August, Volume 5, Issue 4) described the transaction-based SNAPSHOT isolation level. At the time Part I was written, just prior to the release of Beta 2, only the SNAPSHOT isolation level was fully documented. With the release of SQL Server 2005 Beta 2, a fuller explanation of a second type of SNAPSHOT isolation behavior has come to light.
-
SQL Server 2005 T-SQL Enhancements
By:
Jim Duffy
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2005 - January/February
SQL Server 2005 or "Yukon" is going to be a major SQL Server update containing updates to nearly every facet of the program, including T-SQL.In this article I am going to explore some of the new T-SQL features, commands, and capabilities in SQL Server 2005. Because covering everything new in T-SQL would require an entire chapter in a book, I am going to cover some of the more useful and mainstream enhancements.
-
Creating Custom Data Extensions for SQL Server 2000 Reporting Services
By:
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2004 - July/August
In early 2004, Microsoft released a new set of extensions to SQL Server 2000 called SQL Server 2000 Reporting Services.SQL Server Reporting Services provides a comprehensive platform for creating, managing, securing, scheduling, and outputting data from SQL Server, Oracle, OLE DB and ODBC data sources. When Microsoft created SQL Server Reporting Services they created it as an extendable platform. You can create your own data extensions and your own output extensions. In this article, I'll show you how to create your own custom data extensions for SQL Server Reporting Services.
-
Snapshot Isolation in SQL Server 2005 "Yukon"
By:
Last updated: Friday, February 22, 2019
Published in: CODE Magazine: 2004 - July/August
Snapshot isolation is a new isolation level that reduces blocking reads and allows transactions to read previously committed versions of data, instead of uncommitted data.SQL Server's default isolation level of READ COMMITTED restricts transactions to reading only data that has been committed. This isolation level has the effect of blocking a transaction's read operation when the data is exclusively locked by another transaction. A lower isolation level called READ UNCOMITTED allows your transactions to read another transaction's uncommitted data while it is locked, but the values are not stable because the data is uncommitted. SQL Server 2005 introduces a new isolation level that provides a middle way between READ COMMITTED and READ UNCOMMITTED. The SNAPSHOT isolation level allows your transaction to read the previously committed version of the data that is currently being changed, so that your transaction's view of the data is consistent with the state of the data when your transaction began without being blocked by the other transaction. Snapshot isolation has the benefit of reducing blocking and deadlocking in your transactions that is due to blocking reads, without having to let your transactions read uncommitted data. (Note: this article is based on a pre-Beta 2 version of SQL Server called IDW3, build 9.00.790. Some details about the operation and behavior of snapshot isolation may change before the final version of SQL Server 2005 ships.)
-
Creating User-Defined Data Types in Yukon
By:
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2004 - January/February, CODE Focus Magazine: 2003 - Vol. 1 - Issue 3 - Whidbey and Yukon PDC Special, VFP Conversion Papers
The next version of SQL Server (code name Yukon) has extensive support of the Common Language Runtime (CLR).Previous versions of SQL Server (2000 and earlier) had a mechanism for creating custom data types. These data types were nothing more than aliases to system data types. In Yukon, you can create your own fully functional custom data types.
-
Great Reasons to Ring In 2005!
By:
Last updated: Tuesday, November 15, 2022
Published in: Publisher's Point, VFP Conversion Papers, Markus Egger Talks Tech
Wow, another year has gone by, and as you read this, you are probably returning to the office after a few more or less relaxing holiday time spent with friends and family and a New Year's celebration. Interesting things have happened in our industry in the last 12 months, but I predict that the next 12 months will be quite a bit more interesting! Seldom before have I been as excited about new technologies and developments as I am now.
-
Handling SQL Server Errors in Nested Procedures
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2003 - May/June, VFP Conversion Papers
Basic error handling in SQL Server's programming language, Transact-SQL, is straightforward.But when you nest calls to stored procedures, and the procedures have SQL transactions, error handling becomes much more complex. In this article I'll show you some tried-and-true models for how to handle errors in nested stored procedures with transactions.
-
Scripting SQL Server 2000 Indexes
By:
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2003 - January/February, VFP Conversion Papers
Adding indexes to tables is the most effective way to optimize your SQL Server query performance. Tables that you query often may require several indexes in order to handle the various search arguments you send. There is one particular situation where indexes on large tables are a hindrance, though. When you're bulk-copying data into the table, it's best to have either no index at all, or just a clustered index. For those situations it is best to use Transact-SQL scripts to remove and add those indexes. In this article you'll learn how to use Transact-SQL stored procedures to generate Transact-SQL indexing scripts that are more robust and less laborious than SQL Server's Enterprise Manager scripting tool.
-
SQL Server 2000 Replication 101: Terminology, Types, and Configuration
By:
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2003 - November/December, VFP Conversion Papers
Replication figures as one of the more prominent features of SQL Server 2000.Replication is a complex application that uses a combination of stored procedures and executables to distribute and copy data between SQL Server databases. If you take care not to get lost in the details and confused by occasionally misleading terms, replication can be an important component in the database architecture of a SQL Server application. In this article, you'll learn about the terminology surrounding replication, the types of replication available in SQL Server, and how to configure (that is, install) replication.
-
SQL Server 2000 Replication 101: Replication Agents
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2004 - May/June
Replication agents are the basic components of SQL Server 2000 replication.Before you settle on the type of replication you want in your application architecture, it is important to understand how each type of replication works and what agents they use. Since the different types of replication use different combinations of replication agents, understanding what those agents do helps clarify the resulting SQL Agent jobs when you install replication. For example, snapshot, transactional, and merge replication all use the Snapshot Agent to initialize the subscriber's data. Then they employ other agents to implement the replication process. In this article, you'll learn about the roles of the replication agents and learn how to inspect the properties and profiles of the Snapshot and other agents.
-
ADO.NET Best Practices - Part II
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2004 - January/February
ADO.NET has a strong and powerful disconnected model.It allows programmers to build a web of in-memory objects and relate contents coming from different tables and even from different data sources. When inter-related tables are involved with the process of query and update, code strategies are important to preserve scalability and maintain high performance. Sometimes compound queries can be more effectively accomplished splitting queries; sometimes not. Submitting changes in batch mode, exploiting the DataSet and its disconnected model, often appears as the perfect solution. However, what if you need to move dozens of MB? The serialization mechanism of the DataSet would increase that by a factor. Tradeoffs is the magic word.
-
Resolving Deadlocks in SQL Server 2000
By:
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2003 - September/October
Your application can detect a deadlock and resubmit its transaction, but a better approach is to resolve a deadlock by changing the conditions that lead to it in the first place. In this article, you'll learn how SQL Server deadlocks arise, what types of deadlocks there are, and how you can resolve them.
-
Using Stored Procedures in Conjunction with the SqlDataAdapter
By:
Clark, Daniel
Last updated: Wednesday, August 31, 2022
Published in: CODE Magazine: 2003 - March/April
A well-designed application that uses a relational database management system in the backend should make extensive use of stored procedures.A stored procedure is a named collection of SQL statements that you store in a database. To the client, a stored procedure acts similar to a function. You call the stored procedure by name, you can pass it parameter values, and it can return parameter values back to your client.
-
Microsoft Exception Management Application Blocks
By:
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2002 - November/December
A new feature of .NET is its ability to handle exceptions. This article demonstrates implementing Microsoft's Exception Management Application Blocks.
-
Securing Your SQL Server
By:
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2002 - November/December
SQL Server, like most complex databases, has potential security holes. This article discusses these security holes and how to close them.
-
Testing SQL Server 2000: Testing Database Options
By:
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2002 - July/August
Testing SQL Server 2000 applications begins with making sure your production server is configured properly.You generally do this by verifying that the server's settings match a properly configured target computer. After configuring your server, you can go to work on validating your database configuration. This article will continue the theme of creating and implementing a Transact-SQL stored procedure to test a database's options for deviation from the default settings.
-
Testing SQL Server 2000: Inspecting Configuration Information
By:
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2002 - March/April
Testing SQL Server is an often-overlooked area of the software development process, because programmers primarily place testing focus on code residing in the middle tier or the client desktop, rather than the database. However, as SQL Server databases become a more important component of applications, they cannot be left out of the developer's testing process. An important place to start, to ensure your code works the way you want it to, is with SQL Server configuration.The focus of this column is to examine issues dealing with testing SQL Server 2000 databases using SQL Server's built in language, Transact-SQL, as the primary testing tool.
-
Passing Data Over .NET Web Services
By:
Rick Strahl
Last updated: Tuesday, February 19, 2019
Published in: CODE Magazine: 2002 - January/February
Web Services is a powerful technology, even in its basic form.However, with .NET, you can easily couple Web Services with .NET's new data services to provide a powerful data delivery mechanism that works over the Web, making it possible to build distributed applications that work easily without a local data store. In this article, Rick describes various ways you can use Web Services and ADO.NET DataSets to pass data between client and server applications to build truly disconnected applications.
-
XML to Database using .NET's XmlTextReader
By:
Wahlin, Dan
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2002 - January/February
As XML becomes more and more prolific in the world of data exchange it's increasingly important that data can be quickly and easily extracted from XML documents and moved into more permanent data stores.Although .NET offers several different ways for performing this task, the XmlTextReader represents the most efficient and scalable solution.
-
XML UpdateGrams in SQL Server
Last updated: Wednesday, February 20, 2019
Published in: CODE Magazine: 2002 - January/February
The new XML features in SQL Server 2000 give the developer more power to implement distributed solutions.One of the newest features, XML UpdateGrams, allows the developer to handle the inserting, updating and deleting of records while getting around some of the limitations of URL queries and OPENXML. XML UpdateGrams perform their operations against an XML view, which is provided by an annotated XDR schema that contains the necessary information to map elements and attributes back to their corresponding tables and fields.
-
Building Data Access Components in C#
By:
Myers, Wayne,
Cote, Aubrey T
Last updated: Wednesday, November 30, 2022
Published in: CODE Magazine: 2001 - Issue 1
With the functionality provided by the .NET runtime, building data access components with C# (pronounced "c-sharp") is not difficult.In this article, we discuss how to access data stored in a SQL Server database. We will then review the steps necessary to build a Dynamic Link Library and an Executable file in C# to query a table and display the results.
-
XPath Queries in SQL Server 2000
Last updated: Wednesday, December 8, 2021
Published in: CODE Magazine: 2001 - Issue 1
Imagine the potential of accessing your database without needing to know any specifics about how that data is stored.Imagine the possibility of allowing your DBA the freedom to change the database structure without affecting your software code. Impossible? Not with XPath Queries and XDR Schemas. Using these two methods, practically any developer can write a program to access data in SQL Server 2000, just by knowing the structure of the XML documents returned by the server.
-
Query XML from SQL Server 2000
Last updated: Wednesday, November 30, 2022
Published in: CODE Magazine: 2000 - Fall
XML is quickly becoming the preferred method of passing information, not only for the Internet, but also across applications and within applications.Until now, developers have been forced to create our own routines to convert data stored in a database system into XML. With the release of Microsoft SQL Server 2000, however, you can query data directly from SQL Server in XML format.
-
N-Tier Application Design
By:
Doug Hennig
Last updated: Thursday, December 9, 2021
Published in: CODE Magazine: 2000 - Summer
As applications become more complex, designing with components becomes a very important factor in the successful completion of projects.This article discusses n-tier application design, why it's important to modern application development, and shows strategies for breaking out the tiers using Visual FoxPro.
- Home
Articles filed in category 'SQL Server'