CLOUD SQL 

How to Query Arrays, Hash Tables and Strings with PowerShell

By: Tim Smith   |   Read Comments   |   Related Tips: More > PowerShell Problem When we develop solutions that require identifying what data we have or don’t have, we will sometimes require an ability to query sets, definitions or find prior to conversion. For these use cases, we may use objects like arrays, hash tables and strings. The use case of these objects can stretch beyond these requirements, though these requirements provide a useful example of how we…

Read More
CLOUD SQL 

Script to Retrieve All Error Numbers and Messages from the SQL Server Error Log

By: Eduardo Pivaral   |   Read Comments   |   Related Tips: More > Monitoring Problem The undocumented function xp_readerrorlog provides us the ability to read the SQL Server error log using T-SQL and perform some basic text searches, but this function is very limited.  For example, if we want to retrieve the error number and related message, since they are located on different rows, and a text search won’t work on those cases. First, let us see how…

Read More
CLOUD SQL 

Create Tabular Model Sample from SQL Server Database – Part 1

By: Haroon Ashraf   |   Read Comments   |   Related Tips: > Analysis Services Development Problem As a SQL Server business intelligence developer, I would like to work directly on a tabular analysis services project using a SQL database sample which must comply with data warehouse sample requirements.  How can I prepare a database to be used for tabular analysis? Solution The solution is to convert a SQL database sample into a tabular model sample by making…

Read More
CLOUD SQL 

Implement a Slowly Changing Type 2 Dimension in SQL Server Integration Services – Part 1

By: Koen Verbeeck   |   Read Comments   |   Related Tips: More > Integration Services Development Problem We’d like to keep history in our data warehouse for several dimensions. We use SQL Server Integration Services (SSIS) to implement the ETL (Extract Transform and Load). We tried the built-in Slowly Changing Dimension wizard, but the performance seems poor. How can we implement the desired functionality with regular SSIS components? Solution Introduction to Slowly Changing Dimensions A slowly…

Read More
CLOUD SQL 

Understanding SQL Server Connection Pooling in ADO.NET

By: Artemakis Artemiou   |   Read Comments   |   Related Tips: More > Application Development Problem This tip will help you to better understand what connection pooling is in the data access world. Also, after reading this tip, you will have more knowledge on how to take advantage of the connection pooling mechanism when writing .NET data access code that communicates with SQL Server. Solution In its simplest explanation, a connection pool is a cache of database connections.…

Read More
CLOUD SQL 

Linear Regression with Python in SQL Server 2017

By: Siddharth Mehta   |   Read Comments   |   Related Tips: More > Python Problem The fundamental process in the machine learning development life cycle is identifying the dependent and independent variables for developing a data model. The two basic categories of supervised machine learning are classification and regression. Regression is arguably the most basic form of machine learning algorithms and suitable for beginners in machine learning. Though it is one of the most basic algorithms for machine…

Read More
CLOUD SQL 

Simple Free Disk Space HTML report for SQL Server

By: Diogo Souza   |   Read Comments   |   Related Tips: More > Monitoring Problem Automated SQL Server database monitoring is a very important task for the DBA, because information about the availability and health of the environment (instances availability, use of data files and transaction logs, resource consumption: CPU, disk and memory, etc.) is obtained when the professional is not in his or her working hours. Obviously, this data is used to alert the administrator of a problem…

Read More
CLOUD SQL 

SQL Server Function to Generate Random Numbers

By: Eli Leiba   |   Read Comments   |   Related Tips: More > T-SQL Problem The request is to expand upon the SQL Server randomizing capabilities by creating a function that will generate a random number in many different ways, thus enabling the user to choose the randomizing generation approach from four different methods.  Solution My solution involves creating a T-SQL function in the SQL Server master database, called GenRandomNumber. This function gets a parameter called @method…

Read More
CLOUD SQL 

Custom Intrusion Detection Reporting for SQL Server

By: Svetlana Golovko   |   Read Comments   |   Related Tips: More > Auditing and Compliance Problem Using Windows Authentication with SQL Server is the recommended (more secure) authentication mode. Windows Authentication uses Kerberos security protocol, passwords are not transferred through the network and Windows Authentication offers additional password policies that are not available for SQL Server logins. Some applications still require SQL Server logins for user authentication or for application service accounts. Read more about the advantages…

Read More
CLOUD SQL 

Determine SQL Server Network Protocol Information using T-SQL and DMVs

By: Eduardo Pivaral   |   Read Comments (1)   |   Related Tips: More > Functions – System Problem You need to determine the network protocols and the enabled/disabled status in your SQL Server instances, but for whatever reason, you cannot access SQL Server configuration manager, or maybe you just want to know that information without having to login to the server, so you need a quick, reliable way to determine this information using T-SQL.  Also, you may need to…

Read More