SQL Convert Function January 29, 2019 by Esat Erkec In this article, we will discuss and learn basics and all details about SQL Server data type converting operations and also we will review the SQL CONVERT and TRY_CONVERT built-in functions with various samples. At first, we will explain and clarify syntax of the SQL CONVERT function and then we will learn how can we make data converting process numerical and date/time data types to character data. Read more »
SQL string functions for Data Munging (Wrangling) September 13, 2018 by Prashanth Jayaram In this article, you’ll learn the tips for getting started using SQL string functions for data munging with SQL Server. In many cases, Machine learning outcomes are only as good as the data they’re built on – but the work of preparing data for analytics (that is, data wrangling) can eat up as much as 80% of your project efforts. Read more »
Introduction to mssql-cli, a new interactive command line tool for SQL Server January 31, 2018 by Bojan Petrovic Microsoft recently released a new and interactive command-line query tool for SQL Server with modern features such as auto-completion and syntax highlighting. It’s called mssql-cli and this fully open source tool works on cross-platform under the BSD-3 license. Read more »
An introduction to the bcp Utility (bulk copy program) in SQL Server November 9, 2017 by Daniel Calbimonte Introduction The Bulk copy program aka bcp is the console application used to export and import data from text files to SQL Server or vice versa. It is very popular because it is fast and easy to download. Read more »
Working with the SQL Server command line (sqlcmd) October 18, 2017 by Daniel Calbimonte Introduction Sqlcmd allows executing queries, T-SQL sentences and SQL Server scripts using the command line. In the previous article How to work with the command line and Azure to automate tasks, we worked with the sqlcmd in Azure. In this new chapter, we will show the following examples in a local SQL Server using sqlcmd: Read more »
An introduction to sp_MSforeachtable; run commands iteratively through all tables in a database August 18, 2017 by Vitor Montalvão Introduction There have always been some undocumented objects in SQL Server that are used internally by Microsoft, but they can be used by anybody that have access to it. One of those objects is a stored procedure called sp_MSforeachtable. sp_MSforeachtable is a stored procedure that is mostly used to apply a T-SQL command to every table, iteratively, that exists in the current database. Read more »
SQL Server Database Recovery Process Internals – database STARTUP Command February 14, 2017 by Prashanth Jayaram A database recovery process is an essential requirement for database systems, It can be a tedious job and the process of recovery varies on lot of scenarios. The desire to improve recovery results has resulted in various procedures, but understood by few and prone to errors. In this article, I’ll illustrate the impact of stopping the database instance in the middle of a large transaction that was running and discuss several techniques and tools that are available for faster and successful recovery. Read more »
Connecting PowerShell to SQL Server – Using a Different Account January 24, 2017 by Shawn Melton Introduction In a previous article on Connecting PowerShell to SQL Server I went over how you use various methods in PowerShell to connect to SQL Server. In those examples though I only touched on using the current user that is running “PowerShell.exe”. In this article I want to pick up and go over how you would use the same methods, but as a different account. I will cover using both Windows Authentication (where supported) and SQL Login authentication. Read more »
How to use SQLCMD commands in the SSMS query editor December 13, 2016 by Marko Zivkovic SQLCMD Mode allows creating, testing, executing SQLCMD commands or scripts in SQL Server Management Studio directly in the query editor. This option is available since SQL Server 2005. Read more »
How to use the xp_cmdshell extended procedure September 12, 2016 by Daniel Calbimonte Introduction The xp_cmdshell is a very powerful extended procedure used to run the command line (cmd). This is very useful to run tasks in the operative system like copying files, create folders, share folders, etc. using T-SQL. In this new article, we will show some useful examples about how to use it. Read more »
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. Read more »
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. Read more »
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. Read more »
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. 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 »
