Languages and coding

Querying remote data sources in SQL Server

June 10, 2016 by Ahmad Yaseen

A common activity when writing T-SQL queries is connecting to local databases and processing data directly. But there will be situations in which you need to connect to a remote database that is located in a different instance in the same server or in a different physical server, and process its data in parallel with the local data processing.

SQL Server provides us with four useful methods to connect to the remote database servers, even other database server types, and query its data within your T-SQL statement. In this article, we will discuss these four methods and how to use it to query remote SQL Server databases.

How to create and customize SQL Server Templates

May 16, 2016 by Marko Zivkovic

SQL Server Templates are SQL scripts, containing SQL code, frequently used by developers and DBAs in their daily work (e.g. Create Table, Add Column, Drop Column, Create Database, Create Logins etc.):

SQL snippets in SQL Server Management Studio

April 22, 2016 by Marko Zivkovic

Snippets are a great productivity feature to speed up typing of repetitive T-SQL. Snippets were introduced in SQL Server 2012 as part of SQL Server Management Studio (SSMS).

Snippets are XML templates with predefined fields and values that render into T-SQL, when processed by the query window.

SSIS and PowerShell – Execute process task

April 11, 2016 by Shawn Melton

Introduction

SQL Server Integration Services (SSIS) and PowerShell (PS) together offer a plethora of opportunities, and some shortcuts when having to import, export, or at times moving data. I have come across packages that contain a Script Task with lines and lines of C# code that, done with PowerShell, could make maintaining that package much easier. Overall, the most common thing I see Script Task doing is accessing the file system or doing some manipulation on a file. One thing I hope that picks up speed in the BI world of SSIS is utilizing PowerShell for these type of actions. This is not to say one is better than the other as you should pick what is best in your eyes, but when I can do operations against the file system with a one-liner in PowerShell it is just easier to maintain that in the package. In this article I will go over how you can use the most common task utilized for executing PowerShell code in an SSIS package: Execute Process Task.

Using SQL Server cursors – Advantages and disadvantages

March 23, 2016 by Sergiu Onet

Intro

In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with a row at a time rather than the entire result set at once. In T-SQL, one way of doing this is using a CURSOR.

Connecting PowerShell to SQL Server

January 13, 2016 by Shawn Melton

Introduction

PowerShell (aka Posh or just PS) is becoming more and more of a tool for operational support and some deployment scenarios. If you need to pull or place data into SQL Server, PS can be a handy way of doing it in both one-off and automated work.

There are a number of ways to connect to SQL Server via PS. In this article I wanted to go over the options that are available to you. If you have used PS for any number of months or years, you know there tends to be multiple ways of performing a task. So it goes without saying if you are building out scripts to use in production to test, test, and test…then test it one more time.

Creating a gap in sequences – TSQL Stored Procedure advisor

January 6, 2016 by Eli Leiba

Introducing the Problem

Gaps existence in automatic sequenced columns occurs all the time. Missing identity values (or other sequencing values) occur for a variety of Reasons.

The most common reasons include: roll backed transactions, failed inserts and Deletes, large row deletes after delete commands that occur after many inserts to a sequenced table and so forth.

Multiple options to transposing rows into columns

January 4, 2016 by Sifiso Ndlovu

Introduction

One of the primary functions of a Business Intelligence team is to enable business users with an understanding of data created and stored by business systems. Understanding the data should give business users an insight into how the business is performing. A typical understanding of data within an insurance industry could relate to measuring the number of claims received vs successfully processed claims. Such data could be stored in source system as per the layout in Table 1:

Using PowerShell to monitor your Azure Machine

July 17, 2015 by Daniel Calbimonte

Introduction

In earlier chapters, I show you how to create your Virtual Machine in Azure using Power Shell.

In this new chapter, I will show you how to get information about your VM machine already created. We will use PowerShell cmdlet commands to retrieve the information.

Bcp and linked servers to Azure

July 13, 2015 by Daniel Calbimonte

Introduction

Sometimes, we need to use the command line to copy tables using the command line. In this new chapter, we will use the BCP command to copy tables from a local machine to the SQL Server in Azure. The BCP is a very fast bulk copy tools used for this purpose.

We will also learn how to create a Linked Server in our local machine to Azure. The Linked Servers are very powerful features of SQL Server to connect SQL Server to different Data Sources like Excel, MS Access, Oracle, MySQL, and several databases. In this example, we will connect to SQL Azure.

How to use PowerShell to create a SQL Virtual Machine in Azure

June 4, 2015 by Daniel Calbimonte

Introduction

The first time that you create your VM in Azure manually using the UI is a very pleasant experience. However, when you have hundred and sometimes thousands of computers it is extremely exhausting and boring to create and configure it.

With PowerShell, it is possible to automate several administrative tasks and create scripts to automatically create VMs, enable ports, download and create remote desktop files, administer services, etc.

In this new chapter, we will show how to create a Virtual Machine in Azure with SQL Server installed using PowerShell.

How to work migrate backups, files and scripts to/from the cloud using the command line

May 26, 2015 by Daniel Calbimonte

Introduction

Sometimes we need to move our local files, SQL scripts, backups from our local machine to Azure or vice versa. This can be done manually by accessing to Azure and using a browser, but there are other methods to automatically do this. This article describes the Microsoft Azure Storage Tool. This is a command line tool used to upload data to Azure from a local machine or to download data from Azure to our local machine. The article will describe step by step how to work with this tool.

How to work handle SQL Azure Databases with PowerShell

May 5, 2015 by Daniel Calbimonte

Introduction

PowerShell is a shell used specially to automate administrative tasks.

It is an incredible tool used to programmatically automate tasks like SQL Server tasks, Exchange tasks, Windows tasks, etc. It is very useful to integrate different Microsoft and sometimes non-Microsoft programs.

In this new tutorial, we will show how to install PowerShell for Azure and then how to use it. We will create some databases, edit database properties and retrieve database information using PowerShell.

How to work with the command line and Azure to automate tasks

April 20, 2015 by Daniel Calbimonte

Introduction

The sqlcmd is a very powerful tool to run SQL Server scripts, T-SQL commands. It is also very useful in disaster recovery situations like restoring the master database.

The sqlcmd can also be used in the Cloud, specifically with SQL Server Azure. In this new article, we will describe how to connect from a local machine to an Azure Virtual Machine (VM) with SQL Server Installed using sqlcmd.

Taking a deeper dive into XPATH queries

March 20, 2015 by Steve Simon

Introduction

In my last article entitled “Which fields do my reports actually use”, we had a quick look at a practical implementation of an XPATH query to obtain a list of database table fields being utilized by our reports. Reporting Services reports are really no more than XML documents. The fields utilized by our reports reside in Reporting Services datasets.

Using a cursor to correctly extract SQL Server data and place it in a Reporting Services matrix

February 23, 2015 by Steve Simon

Introduction

In our last two chats, we discussed enterprises that have had financial years that began in July and ended at the end of June. One of our clients works with this fiscal calendar and their financial folks are Excel “Fundi’s” (Fundisa is a Nguni word for “expert”). Many of their reports contain the current month’s sales, in addition, carrying running totals from the beginning of the fiscal year to date. Read more »

Backup testing with PowerShell – Part 2: Reporting results

October 22, 2014 by Derik Hammer

Now that Karla’s restore process is mature, it is time to pre-stage the means of reporting on these events. Two major actions have been taken for each database tested. First a restore operation occurred. The restore operation validates that the backup file is well formed and that there is nothing wrong with the data which would cause a failure. Next CheckTables was executed to thoroughly inspect for data corruption and any consistency errors. Each of these two actions will have their own methods of providing evidence of successful completion. Recall the requirements set by Karla, in part 1 of this series, for the reporting piece. Read more »

Backup testing with PowerShell – Part 1: The test

October 21, 2014 by Derik Hammer

Karla is a production database administrator and she has a lot in common with you. Being responsible for database backups and recovery, she has implemented a well-structured automated backup system. Maybe she’s using Ola Hallengren’s Maintenance Solution, custom T-SQL stored procedures, or a set of PowerShell scripts. She has heard the saying, “a DBA only needs one thing, either a backup or a resume, but never both,” and is confident that she won’t be dusting off the resume any time soon. Read more »

SQL Server Commands – Dynamic SQL

July 4, 2014 by Evan Barke

Warning: This article contains examples of SQL injection. The example queries can be harmful to data and are run on the AdventureWorks2012 database DO NOT run equivalents on your production database. Backup your AdventureWorks2012 database if you don’t want to lose data. Please note that the techniques are purely for education purposes and we do not condone the use of them for any other purpose. Read more »

SQL Server cursor performance problems

June 18, 2014 by Evan Barke

Introduction

In a previous article we discussed the how to set up a basic cursor. We explained that a cursor is a row-based operation that takes a given SELECT statement and breaks downs the processing of the data into looping executions. People that do not have the background in Database Administration or who did not enjoy database classes while studying will often find SQL Server cursors handy and fun to write. This is because they break free of the shackles of set-based logic that is the normal when writing most T-SQL scripts. It is for this reason that we so often find SQL Server cursors written into the business logic of an application and it is a real pity because they are real performance hogs. Yes, there are times when cursors are OK to use and they can be tuned slightly by playing with their different types but, as a general rule of thumb, one should try to avoid them at all costs. Read more »

SQL Server cursor tutorial

June 4, 2014 by Evan Barke

Introduction

Most people that work with Microsoft SQL Server will have at least heard talk of cursors and often, even if people know on a basic level what SQL Server cursors do, they are not always certain when to use them and how to write the code behind them. So this article takes a step back and provides an explanation as to what SQL Server cursors can be used for as well as a basic example that you can run for yourself to test. Read more »

Common SQL syntax errors and how to resolve them

April 2, 2014 by Milena Petrovic

In the SQL Server Management Studio, errors can be tracked down easily, using the built in Error List pane. This pane can be activated in the View menu, or by using shortcuts Ctrl+\ and Ctrl+E Read more »

Using an XML file to configure an SSIS package

March 16, 2014 by Marko Radakovic

To configure an SSIS package means to choose property/value pairs added to a completed package, and include them in the configuration file for the further modifications. If for any reason some of the values has been changed after the deployment is finished and the package run, they can be added later, opening the package in Business Intelligence Development Studio (BIDS), and changing the values Read more »