The SQL Server and .NET Hub

Search our hub for SQL Server and .NET knowledge!

Tuesday, May 16, 2017

Setup failed to start on the remote machine. Check the Task scheduler event log on the remote machine.

When you run the SQL Server 2005 setup on a Windows cluster (i.e. installing or uninstalling SQL Server), you might get an error message:

Setup failed to start on the remote machine. Check the Task scheduler event log on the remote machine.

I have experienced this error message running SQL Server 2005 cluster setup on a Windows Server 2003 R2 cluster (yes, I know it's old, I was uninstalling SQL Server :)

If you get this error make sure that:
  • The "Task Scheduler" Windows service is running on all cluster nodes.
  • You are not logged in remotely on any of the cluster nodes except from the one on which you are running the SQL Server cluster setup.

After you ensure the above, you can re-run the setup and try again.

P.S.: Note that extended support for SQL Server 2005 ended on April 12, 2016. As of that, you are interested in installing or upgrading SQL Server, consider using SQL Server 2014 or SQL Server 2016 and take advantage of all those amazing benefits and performance improvements they offer.

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (

What are your views on the subject? Have something to share? Feel free to leave your comment!

Wednesday, May 10, 2017

The TempDB System Database in SQL Server

This article is from the book:
Tuning SQL Server (eBook)

Tuning SQL Server (second Edition)

Table of Contents | Sample ChapterLearn More

The tempdb system database in SQL Server is one of the databases that are shipped with every SQL Server installation and it is used for specific tasks as a temporary storage. Examples of when tempdb is used are:
  • Use of local/global temporary tables, stored procedures and variables.
  • Use of cursors.
  • When the SORT_IN_TEMPDB option is used during the creation or rebuild of indexes.
  • Use of local/global stored procedures.
  • When row versions are generated by different transactions.

TempDB is protected with the use of certain built-in restrictions in SQL Server. These are:
  • You cannot drop it.
  • You cannot back it up/restore it.
  • You cannot change the database owner (it is always the dbo).
  • You cannot create a database snapshot.
  • You cannot drop the guest user.
  • You cannot add filegroups.
  • You cannot change the collation (default collation is server’s collation).
  • You cannot take the database offline.
  • You cannot set the database or primary filegroup to READ_ONLY.
  • You cannot rename the database or its primary filegroup.
  • You cannot remove the primary filegroup, data or log file.
  • TempDB cannot participate in database mirroring.
  • You cannot enable change data capture on TempDB.

TempDB is heavily used in a variety of SQL Server operations and contributes a lot to performance of database operations. To this end, you need to maintain the good health of TempDB in order to contribute towards a performant SQL Server instance. For example, you must ensure that there is an adequate amount of available free disk space as TempDB expands when used. Also, using multiple data files can help applying parallelism in TempDB database.

Learn more about SQL Server Performance Tuning in my latest eBook "Tuning SQL Server (second edition)"!

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (

What are your views on the subject? Have something to share? Feel free to leave your comment!

Monday, May 8, 2017

The Importance of Database Indexes

This article is from the book:
Tuning SQL Server (eBook)

Tuning SQL Server (second Edition)

Table of Contents | Sample ChapterLearn More | Buy

Imagine that you have a large book and you are looking for a specific piece of information. If the book has 500 pages and has no index, you will have to go through page by page until to find the information you are looking for. The worst-case scenario would be to check all 500 pages. That would be a full “scan” of the book. However, if the book has an index, you will just go through the index list and find the page that contains the information you are looking for.

The same thing happens with databases. In the database world, seeking specific information in a frequently-used large table without using an index, could end-up scanning the entire table thus taking a considerable amount of time. However, when having an index, eventually the data retrieval time would be much faster as the operation will not have to search every row in the database table every time the database table is accessed.

Even though indexes are key data structures in databases, they bring the cost of additional writes and the use of more storage space to maintain the extra copy of data. In addition, they need to be frequently maintained in order to be as performant as possible. Nevertheless, the performance gain when using indexes is significant thus making the overheads of maintaining them negligible.

In order to have a healthy set of indexes you need to run frequent maintenance operations. Such operations include index fragmentation checks, reorganization and rebuild actions.

Learn more about SQL Server Performance Tuning!

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (

What are your views on the subject? Have something to share? Feel free to leave your comment!

Friday, March 31, 2017

Sneak Peek of SQLArtBits DBA Security Advisor v2.0

On April 5 2017, SQLArtBits releases DBA Security Advisor v2.0.

This article gives a sneak peek of the upcoming new release of DBA Security Advisor v2.0.

DBA Security Advisor is a powerful tool developed by SQLArtBits that allows Database Administrators (DBAs) to run comprehensive security assessments against multiple SQL Server instances, using a rich set of security risks.

Along with the generated reports, DBA Security Advisor provides recommendations for the detected security risks, as well as remediation T-SQL scripts. The recommendation engine of DBA Security Advisor is very powerful as it provides comprehensive explanations for the detected security risks, as well as references to official SQL Server documentation.

Besides its use as a SQL Server security auditing tool, DBA Security Advisor can be also used as a SQL Server administration tool. With DBA Security Advisor you can check things like the service pack level, latest database backup times, service accounts, and much more for all the connected SQL Server instances. This can help the DBAs, especially in enterprise environments, to monitor and administer multiple SQL Server instances much easier.

What's new in DBA Security Advisor v2.0:

  • New, more user-friendly Graphical User Interface (GUI)
  • Security check enhancements
  • Improved performance
  • Bug fixes

Just for the launch date (April 5, 2017), SQLArtBits gives a 20% discount for the Enterprise Edition of DBA Security Advisor v2.0 using the coupon code DBASECADVISORV2.

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (

Thursday, March 23, 2017

New Article Series: Beginner's Corner

We are all beginners.

We are not experts or gurus. There is no such thing. This is just an illusion.

In the end of the day, we are all beginners who constantly learn and evolve.

The majority of articles I publish on my blog target topics that could be characterized as intermediate and sometimes advanced.

Yesterday I thought "hey, why not starting also a new series of articles on my blog aiming at helping people who just started working -or trying to- on SQL Server"?

So here I am announcing this new series of articles and the name of it is: "Beginner's Corner"!

Beginner's Corner will be featuring articles which will be discussing simple SQL Server topics in a logical order. For example the first article which will be published in the coming days it will be introducing SQL Server in simple words, etc.

In order to better help you finding all the articles of Beginner's Corner, on the top right of my blog, you can find a navigation button titled "Beginner's Corner". Whenever you click on the button you will be able to access all the relevant articles.

So please do join my excitement, share the news and any feedback you may have and above all, for any of my articles, don't hesitate to make questions and start discussions because in the day, hey we are all beginners!

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (

What are your views on the subject? Have something to share? Feel free to leave your comment!

Friday, February 24, 2017

Executing T-SQL Statements Against All Databases

There are many cases where a DBA needs to perform a task against all databases in a SQL Server instance. In order to avoid the process of just changing a part of the statement or switching to the target database and executing the T-SQL statement each time, there are some options that can help you.

One option is the undocumented stored procedure "sp_MSforeachdb" which executes the given T-SQL statements against all databases within a SQL Server instance.

Let's see an example. In this example we want to find the physical location of all database files in a SQL Server instance.

In this case we can execute the below command:

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; SELECT DB_NAME() as DBName,[name],[filename] FROM sysfiles'

The above command will execute the encapsulated T-SQL statement against all databases within the SQL Server instance.

Here's the sample output:

A second option is to run the below command that queries the sys.databases catalog and dynamically builds the individual T-SQL statements for all databases:

SELECT 'SELECT '''+[Name]+''' AS DBName,[name],[filename] FROM '+[name]+'..sysfiles'
FROM sys.databases

You can then execute these statements one by one and thus have more control:

* Note: Massively executing T-SQL statements against all databases is something that should be avoided because it is easy to do a mistake and affect all databases. Always carefully test your T-SQL scripts on Test environments. Always take backups of your data.

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (

What are your views on the subject? Have something to share? Feel free to leave your comment!

Monday, February 6, 2017

A connection was successfully established with the server, but then an error occurred during the login process.

When you try to connect to a SQL Server database from a client application, under certain circumstances, you might get the below error message:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)

A connection was successfully established with the server, but then an error occurred during the login process

In order to -possibly- overcome this problem you can perform the below two checks:

[First Check]:
Ensure that the maximum number of concurrent connections is set to 0 (in SSMS right-click on instance --> connections --> maximum number of concurrent connections), which means unlimited.

[Second Check]:
Ensure that in your connection string, in the client application, along with the rest of the settings, the database name is correct:

string connString="Server=[SERVER_NAME]; Database=[DATABASE_NAME]; User ID = [USER_NAME]; Password=[PASSWORD]";

If you specify a database that does not exist, even though the connection to the SQL Server instance will be established, the provider will not be able to find the database thus resulting to the above error message.

Hope this helps!

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (

What are your views on the subject? Have something to share? Feel free to leave your comment!

Wednesday, February 1, 2017

Getting Started with SQL Server In-Memory OLTP – Part 2

In the previous article of this blog series, I introduced In-Memory OLTP and explained how it is possible to create an In-Memory OLTP - enabled database as well as memory-optimized tables (durable and non-durable).

Today we are going to talk about naively-compiled stored procedures as well as see a full example of performance improvement when using In-Memory OLTP. 

Natively-compiled stored procedures are SQL Server objects that can access only memory-optimized data structures such as memory-optimized tables, table variables, etc.

The main features of natively-compiled stored procedures are:
  • They are compiled to native code (DLL) upon their reation (the interpreted stored procedures are compiled at first execution)
  • Their call is actually the invokation of a DLL
  • Aggressive optimizations take time at compile time
  • They can only interact with memory-optimized tables and other memory-optimized data structures (i.e. memory-optimized table variables, etc.)
To define a natively-compiled stored procedure, you first need to create the database objects that will be referenced in the procedure.

Now let's see a comprehensive example where I'm going to create a disk-based table, two durable memory-optimized tables and a natively-compiled stored procedure. Then, I'm going to run 100.000 insert statements against each one of those tables and measure the execution times.

* DDL T-SQL Code for Tables and Natively-Compiled SP
--Create Disk-Based Table
CREATE TABLE [dbo].[Person_DiskBased]
FirstName VARCHAR(50) NOT NULL ,
Remarks VARCHAR(50) NOT NULL,  

--Create Durable Memory-Optimized Table
CREATE TABLE [dbo].[Person_MemOpt_Durable]
FirstName VARCHAR(50) NOT NULL ,
Remarks VARCHAR(50) NOT NULL,  

 --Create Durable Memory-Optimized Table for natively-compiled SP
CREATE TABLE [dbo].[Person_MemOpt_Durable_NativeSP]
FirstName VARCHAR(50) NOT NULL ,
Remarks VARCHAR(50) NOT NULL,  

--Create Natively-Compiled Stored Procedure
 @rowcount INT,  
 @firstName VARCHAR(50),
 @lastName VARCHAR(50),
 @remarks VARCHAR(50)
  DECLARE @i INT = 1;  
  WHILE @i <= @rowcount  
    INSERT INTO [dbo].[Person_MemOpt_Durable_NativeSP] VALUES (@i, @firstName, @lastName, @remarks);  
    SET @i += 1;  

Now that the necessary data structures and natively-compiled stored procedure are created, let's run the workload against each table.

--We are working with the database created in part 1 of this series


DECLARE @starttime DATETIME2 = sysdatetime();  
DECLARE @timeDiff INT;  
DECLARE @i INT = 1;  
DECLARE @rowcount INT = 100000;  
DECLARE @firstname VARCHAR(50) = N'Firstname';  
DECLARE @lastname VARCHAR(50) = N'Lastname';  
DECLARE @remarks VARCHAR(50) = N'Remarks';  

--Clear buffers for disk-based execution

--Run disk-based execution
  WHILE @i <= @rowcount  
INSERT INTO [dbo].[Person_DiskBased] VALUES (@i,@firstname,@lastname,@remarks);
    SET @i += 1;  
SET @timeDiff = datediff(ms, @starttime, sysdatetime());  
SELECT 'Disk-Based Execution: ' + cast(@timeDiff AS VARCHAR(10)) + ' ms';  

--Clear buffers for memory-optimized/interop execution

-- Run memory-optimized execution/interop execution
SET @i = 1;  
SET @starttime = sysdatetime();  
  WHILE @i <= @rowcount  
 INSERT INTO [dbo].[Person_MemOpt_Durable] VALUES (@i,@firstname,@lastname,@remarks);
      SET @i += 1;  
SET @timeDiff = datediff(ms, @starttime, sysdatetime());  
SELECT 'Memory-optimized execution (interop): ' + cast(@timeDiff as VARCHAR(10)) + ' ms';  

--Clear buffers for memory-optimized with natively-compiled stored procedure execution

--Run memory-optimized with natively-compiled stored procedure execution
SET @starttime = sysdatetime();   
EXECUTE uspInsertRecords @rowcount, @firstname, @lastname, @remarks; 
SET @timeDiff = datediff(ms, @starttime, sysdatetime());  
SELECT 'Memory-optimized with natively-compiled stored procedure execution: ' + cast(@timeDiff as varchar(10)) + ' ms';  
--Count records-check result in each table
SELECT COUNT(*) FROM dbo.Person_DiskBased (NOLOCK)
SELECT COUNT(*) FROM dbo.Person_MemOpt_Durable (NOLOCK)
SELECT COUNT(*) FROM dbo.Person_MemOpt_Durable_NativeSP (NOLOCK)

--Re-initialize tables for new run (optional)
DELETE dbo.Person_DiskBased;  
DELETE dbo.Person_MemOpt_Durable;
DELETE dbo.Person_MemOpt_Durable_NativeSP;

Execution Results:

In-Memory OLTP Example Execution Results

As you can see from the above screenshot:
  • Disk-based execution took 2408 ms to complete.
  • Memory-Optimized/interop execution took 297 ms to complete (8x speedup).
  • Memory-Optimized with natively-compiled stored procedure took only 94 ms to complete (25.6x speedup).

Below you can also find some graphs illustrating the execution results:

In-Memory OLTP Execution Results

In-Memory OLTP Execution Results

In this article (part 2 of the series dedicated to SQL Server In-Memory OLTP) we talked about natively-compiled stored procedures and saw a full example comparing the execution of 100.000 insert statements against three modes:
  • Disk-Based
  • Memory-Optimized/Interop
  • Memory-Optimized with Natively-Compiled Stored Procedure
For the above scenario, we witnessed a performance improvement of 8x for the Memory-Optimized/Interop mode and 25.6x for Memory Optimized with Natively Compiled stored procedure.

The above is just a small indication on what you can achieve with SQL Server In-Memory OLTP.

Stay tuned as subsequent articles in this series will discuss more specialized topics of In-Memory OLTP!

Monday, January 16, 2017

Getting Started with SQL Server In-Memory OLTP – Part 1

In-Memory OLTP in SQL Server 2014 or later is a powerful engine integrated into the SQL Server Engine, optimized for Online Transaction Processing (OLTP).

The SQL Server and .NET Hub

In-Memory OLTP (codenamed “Hekaton”) is a powerful engine which is fully integrated into SQL Server’s Database Engine and introduces new data structures for optimizing the performance of OLTP workloads. So, you can have memory-optimized tables, natively-compiled stored procedures, memory-optimized table variables, etc.

In-Memory OLTP offers a significant performance boost when it comes to processing large amounts of information, especially in data environments with high levels of concurrency.

In this series of articles, we will see in plain words, how we can take advantage of this technology and start using it for boosting performance of heavy workloads.

In this first article, we are going to see how easy is to create an In-Memory OLTP-enabled database and memory-optimized tables.

OK, enough talking, let's see some code!

--create database

--use the database


--add memory-optimized file group to the database


--add file for memory-optimized objects

--you can set any path you like - in this example: c:\memoptdata
--if the directory does not exist you will need to create it
ALTER DATABASE InMemOLTPDB ADD FILE (name='InMemOLTPDB_mofg1', filename='C:\memoptdata\InMemOLTPDB_mofg1') TO FILEGROUP InMemOLTPDB_mofg;

--set the isolation level for memory-optimized tables to SNAPSHOT 


Now you can create memory-optimized tables.

When creating memory-optimized tables you have two options:
  • Option 1: You can create a durable memory-optimized table which means that in the case of server crash or failover the data will be available as they will be recovered from transaction logs.
  • Option 2: You can create a non-durable table which means that in the case of a server crash or failover the data in the table will be lost.

The below script creates a sample durable memory-optimized table:

--Memory-Optimized Table: Durable
CREATE TABLE [dbo].[Person_Durable]
     FirstName VARCHAR(50) NOT NULL,
     LastName VARCHAR(50) NOT NULL,
 Remarks VARCHAR(50) NOT NULL,  

The below script creates a sample non-durable memory-optimized table:

--Memory-Optimized Table: Non-Durable
CREATE TABLE [dbo].[Person_Non_Durable]
     FirstName VARCHAR(50) NOT NULL,
     LastName VARCHAR(50) NOT NULL, 
 Remarks VARCHAR(50) NOT NULL, 

Stay tuned as in the next article we are going to run some simple experiments with In-Memory OLTP and get an indication on the performance benefits.

Check out Part 2 of this series - Now Available.

See also:

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (

What are your views on the subject? Feel free to comment!

Thursday, January 5, 2017

Frequent Password Expiration: Time to Revise it?

Until recently, enabling the password expiration option in SQL Server was included in many security best practices. However, recent studies, revised this recommendation and support that it should not be further included in SQL Server’s security best practices. However, if this is the case, how can this recommendation be replaced with new, modern best practices that would take into consideration users’ behavior and habits?

The SQL Server and .NET Hub

Having the password expiration set to ON means that SQL Server checks for all logins that use SQL Server Authentication and contained database users with a password, if their password has expired, and if so, it prompts these logins and users to enter a new password.

Password expiration is part of SQL Server's Password Policy. SQL Server’s Password Policy can use Windows password policy mechanisms. Based on that, SQL Server can apply the same complexity and expiration policies just like in the case of Windows logins. SQL Server's security policy can be based either in Windows, on the database server (local security policy), or on the domain.

In the below example, I’m using the Local Security Policy MMC snap-in (secpol.msc) on my database server, in order to check the password policy settings.

Windows Local Security Policy

As you can see from the above screenshot, the maximum password age is set to 90 days. This means that if I have a login that uses SQL Server Authentication or a contained database user with a password, if the login's or user's password was last changed 90 days (or more) ago, then the login/user will be prompted by SQL Server to change her password.

Even though this practice was used for many years, not only in SQL Server but similarly, on Windows-level, as well as in other systems and applications, recent studies argue that it should not be a recommended practice anymore. On the contrary, these studies suggest that user passwords should not be regularly changed but rather change only when there is a specific and justified reason to do so. Furthermore, new security standards are being formulated that contain new recommendations on password change.

The concept behind the need for new recommendations on password change, is that the whole process must be more user-friendly because as it is today, prompting the user to change her password every X days with no apparent reason, it only causes frustration to the user and has as an effect the user to set similar passwords on every password change or, set passwords that are easy to guess and this is the actual risk.

So, how can you apply this concept in action? How can you ensure to the maximum possible level that you have a healthy and robust password management policy in place? This can be achieved with a combination of actions and policies.

Examples of such practices could be the below:
  • Do not allow the user to set passwords that are easy to guess (you can use password dictionaries with easy-to-guess passwords for that).
  • Ensure that the minimum password length is set to 8 characters but do not limit the maximum length to a small number.
  • If applicable, present the user's in an easily readable way, when her last login took place so in order if she sees a strange login date/time to contact the administrator.
  • Monitor login auditing for "abnormal" login failures (i.e. consecutive login failures) and in this case prompt the user to change her password.
I'm sure that more sophisticated recommendations will be published on the subject along the way, as this is not really a new discussion but rather something that has been thoroughly discussed for years. I believe that now the time is mature to introduce flexible, new best practices regarding password management, that will eventually replace or improve old recommendations and offer a better security framework on the subject.

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (

What are your views on the subject? Feel free to comment!

Thursday, December 29, 2016

Selected Tweets of Year 2016!

Another year has always, full of everything! Events, blog posts, articles on well-known online SQL Server journals, software tools, and of course direct communication with you, my fellow SQL Server community members, via all available online and offline channels!

This year was very important for me. Besides my blogs and user group events, I have also authored many articles for well-respected online technology journals such as: MSSQLTips, SimpleTalk and SQLShack. Furthermore, I had the honor to be nominated by MSSQLTips committee for the "Rookie of the Year" contest.

Another major contribution was transforming my initiative "Artemiou Data Tools" to a more powerful initiative called "SQLArtBits". Via SQLArtBits I develop cool SQL Server tools and services which can help the user have an even richer experience with SQL Server and be more productive with less effort. The highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator.

Furthermore, I have transformed my personal blog from "The SQL Server and .NET Blog" to "The SQL Server and .NET Hub". The change in the name reflects also the change in the design. Now, via the new design you can easily search through hundreds of articles and find the SQL Server and .NET knowledge that you seek.

Last but not least, I have started a brand new initiative called "Tech How To's" via which I provide how-to's on just about any technology topic but mainly focusing on software such as: Databases, Programming, Windows, etc.

All the above, provided me with a great interaction with you, my fellow SQL Server community members, for one more year. Thank you for making the SQL Server community an amazing place to be! You rock!

I'm really looking forward for 2017 having already scheduled a variety of exciting community activities!

Wishing to all of you a Happy New Year 2017!

As every year's end, below you can find my selected tweets for the year 2016.

Blog Posts/Articles/Videos/Services/Books and Announcements

Dynamic Data Masking is one of the key #security features in #SQLServer 2016. Learn more at:  #MVPBuzz

How to get started with #SQLServer  #HowTo

How To Show “This PC” Icon on #Windows 10 Desktop -  #Windows10

Temporal Tables in #SQLServer 2016 is a handy way for auditing data changes!

An amazing collection of #SQLServer tips by @SQLArtBits now available as a #free #download - Get it here!

Have you checked Row-Level #Security in #SQLServer?

In #DotNet, you can connect to and retrieve data from #SQLServer with just a few lines of code  #MVPBuzz

[Blog] How to Establish a Simple Connection from a C# Program to SQL Server  #SQLServer #DotNet #MVPBuzz

Have you checked Entity Framework: Code First? It's quite cool!  #DotNet #SQLServer

What are exactly orphaned users in #SQLServer and how can we handle them?

Weekly SQL Server Tip 17: The STRING_SPLIT Function in SQL Server 2016  #SQLServer #Tips

My latest article on @sqlnethub! This one is about .NET error handling #MVPBuzz
Cannot implicitly convert type 'string' to 'System.Windows .Forms.DataGridViewTextBoxColumn' -  #DotNet

Article in which I'm suggesting some best practices for when patching a #SQLServer failover clustered instance

How to Patch a SQL Server Failover Cluster -  #SQLServer

Weekly #SQLServer Tip 16 with a little bit of #DotNet!  #SQLArtBits #TipOfTheWeek

MSSQL Tiger Team Blog - SQL Server 2016 SP1: Know your limits -  #SQLServer

[Bog] How to Suppress the "N Row(s) Affected" Output Message in #SQLServer  #MVPBuzz

[Blog] Vote for me for the MSSQLTips Rookie of the Year Award!  #SQLServer #MVPBuzz #Community

[Blog] The timeout period elapsed prior to obtaining a connection from the pool -  #MVPBuzz #DotNet

My blog is being transformed into a central hub of #SQLServer and .NET knowledge - Read the announcement!  #MVPBuzz

Read my brand new article on @SQLShack and learn more about #SQLServer consolidation best practices! #MVPBuzz

This week's #SQLServer tip by @SQLArtBits
Weekly #SQLServer Tip 15: T-SQL Enhancements & Other in SQL Server 2016 SP1   #SQLArtBits #TipOfTheWeek

Get your weekly bits of #SQLServer knowledge by @SQLArtBits #TipOfTheWeek series! This week's tip: Significant Changes in SQL Server 2016 SP1
Weekly #SQLServer Tip 14 - Significant Changes in SQL Server 2016 SP1  #SQLArtBits #TipOfTheWeek

[Blog] SQL Server 2016 SP1: Breakthrough Changes  #SQLServer #MVPBuzz

[Blog] Updated: Main Limitations of #SQLServer Express Editions  #SQLServer #MVPBuzz

[Blog] SQL Server 2016 SP1: A Pleasant Surprise -  #SQLServer #SQLArtBits #performance

A brand new weekly #SQLServer tip by @SQLArtBits is out! This time is about #Security! #SQLArtBits
Weekly #SQLServer Tip 13: #Security Advice on Failed Login Attempts in SQL Server -  #SQLArtBits #TipOfTheWeek

[Video] Introducing DBA Security Advisor in 75 Seconds -  #SQLArtBits #SQLServer #Security #Tools

This week's #SQLServer tip by @SQLArtBits is about durability options for memory-optimized tables in SQL Server #SQLArtBits #TipOfTheWeek
Weekly #SQLServer Tip 12 - Durability Options for Memory-Optimized Tables in SQL Server  #SQLArtBits #TipOfTheWeek

[Video] Introducing In-Memory OLTP Simulator in 60 Seconds -  #SQLServer #Software #InMemoryOLTP #Hekaton #SQLArtBits

[Blog] In-Memory OLTP Simulator v1.1 is Now Out!  #SQLServer #Software #Tools #InMemoryOLTP #Hekaton

Get backward compatibility info for any #SQLServer version with this #free online service:  #mvpbuzz

Weekly #SQLServer Tip 11: T-SQL Code Snippets -  #SQLArtBits #TipOfTheWeek

My new article on @SQLShack is about T-SQL code snippets in #SQLServer and a special way of managing them!  #MVPBuzz

Check out my new article on @SQLShack! #SQLServer #MVPBuzz
How to create and manage T-SQL code snippets  via @SQLShack w/ @artemakis

My latest article on @SQLShack is all about #SQLServer #Security. Check it out!  #MVPBuzz

This week's #sqlserver tip by @SQLArtBits is all about #security!
Weekly #SQLServer Tip 10: Secure SQL Server  #Security #InfoSec #TipOfTheWeek #SQLArtBits

New #Free online #SQLServer Service by @SQLArtBits: SQL Server Backward Compatibility Checker -  #SQLArtBits

This week's tip is all about #SQLServer #security #infosec #mvpbuzz
Weekly #SQLServer Tip 9 - Password Expiration Option -  #SQLArtBits #TipOfTheWeek #Security #infosec

[Useful Table] Main Limitations of SQL Server Express Editions -  #SQLServer #MVPBuzz

SQLServerAgent could not be started (reason: Unable to connect to server '(local)'...  #sqlserver #mvpbuzz

Top 10 security considerations for your SQL Server instances -  #SQLServer #MVPBuzz

Weekly #SQLServer Tip 8 by @SQLArtBits
Weekly #SQLServer Tip 8 is Out!  #TipOfTheWeek #SQLArtBits

[Blog] Main Limitations of #SQLServer Express Editions -  #SQL #MVPBuzz

Get your weekly #SQLServer tip! #TipOfTheWeek 7 - Query Store  #SQL #SQLArtBits

How to resolve the "cannot connect to WMI provider" issue -  A very helpful support article by #Microsoft #SQLServer

My new article on @mssqltips: Script to Manage #SQLServer Rebuilds and Reorganize for Index Fragmentation -  #MVPBuzz

[Blog] What are Exactly Orphaned Users in #SQLServer?  #MVPBuzz

It's time for you weekly #SQLServer bits of knowledge by @SQLArtBits! Weekly SQL Server Tip 6 - Always Encrypted -

Retweeted @SQLArtBits:
[Blog] Why We Build SQL Server Tools -  #SQLServer #Software #Tools #SQL #MVPBuzz

Why we built In-Memory OLTP Simulator: #whywebuiltit  #MVPBuzz #SQLServer

My latest article on @mssqltips: Simple Experiment with #SQLServer In-Memory OLTP is 79 Times Faster!  #MVPBuzz #SQL

Get your weekly bits of #sqlserver knowledge! Tip of the week 5! #MVPBuzz
Ready for some bits of #sqlserver knowledge? #TipOfTheWeek 5:  #SQLArtBits

Weekly #SQLServer Tip 4 -  #ArtDataTools #SQL #Tools

If you are just getting started with #InMemoryOLTP in #SQLServer you should read my article on #SimpleTalk  #MVPBuzz

It's time for this week's #SQLServer Tip!  #MVPBuzz #SQL

Easily get the latest service pack info for any #SQLServer instance with this #free online service -  #mvpbuzz

My #free ebooks on #SQLServer have now over 6300 downloads! Learn more about SQL Server today!  #SQL #MVPBuzz

Weekly #SQLServer Tip 2 -  #SQL #MVPBuzz

[Blog] The "Public" Database Role in SQL Server -  #SQLServer #SQL #MVPBuzz

New #sqlserver #community initiative! Weekly SQL Server Tips! Tip 1 -  #community #mvpbuzz

[Blog] SQLServerAgent could not be started (reason: Unable to connect to server '(local)'... -  #MVPBuzz #SQLServer

[Blog] Why Secure Your #SQLServer Instances?  #MVPBuzz #Security

[Blog] Artemiou Data Tools: Free Online SQL Services -  #MVPBuzz #SQLServer #Community #Software

My latest presentation on #SQLServer #Security: How to Secure Your SQL Server Instances -  #MVPBuzz

[Webcast] Learn how you can secure your #SQLServer instances by watching my latest #webcast -  #Security #mvpbuzz

[Blog] Why You Need to Secure Your SQL Server Instances -  #SQLServer #Security #MVPBuzz

How can you export #SQLServer records into individual text files? Read my latest article on @mssqltips:  #MVPBuzz

[Blog] Operating System Requirements for all SQL Server Versions and Editions -  #SQLServer #MVPBuzz

Learn how you can use #SQLServer Model system database. Read my my latest article on @mssqltips -  #MVPBuzz

[Blog] Entity Framework: Code First -  #MVPBuzz #DataAccess

Learn how to implement #SQLServer In-Memory OLTP. Read my latest article on Simple-Talk Journal:  #MVPBuzz #Hekaton

[Blog] SQL Server 2016: Dynamic Data Masking -  #MVPBuzz #SQLServer2016 #SQLServer #Security

[Blog] SQL Server 2016: The STRING_SPLIT Function -  #MVPBuzz #SQLServer

A must-read: Guidelines for Using Indexes on Memory-Optimized Tables -  #Hekaton #SQLServer

Learn more about my experiment. Get the paper at:  #MVPBuzz #Hekaton #SQLServer

My article in @mssqltips: Assessing the SQL Server In-Memory OLTP Performance Benefits -  #MVPBuzz #SQLServer #Hekaton

See how I achieved breakthrough performance (26x speedup) with #SQLServer #InMemoryOLTP! Get the paper at:  #MVPBuzz

My technical article 'In-Memory OLTP Simulator:The Experiment' is now available for download!  #SQLServer #MVPBuzz

[Blog] SQL Server 2016: Row Level Security -  #MVPBuzz #SQLServer

My whitepaper titled 'In-Memory OLTP Simulator:The Experiment' to be published on March 2, 2016!  #SQLServer #MVPBuzz


Hi #sqlfamily! Follow my blog's twitter account @sqlnethub and stay up to date about my latest blog articles! Cheers, Artemakis.

Hi folks! Make sure you check out my blog which was recently transformed into something more powerful!  #SQLServer

Join our Facebook group and stay up to date with our latest articles and how-to's!  #SQLServer #DotNet #Community

Looking for a solution to a technical problem having to do with #SQLServer or #DotNet? Just run a search!

The SQL Server and .NET Hub just got its brand new URL!  #MVPBuzz #SQLServer #DotNet #Community

Cool #free online service: #sqlserver supported Operating systems -

Working on some new articles on #sqlserver #security, architecture and #performance via #InMemoryOLTP! #MVPBuzz

Check out our #free online #SQLServer services!  #SQLArtBits #sql #community

Want to know fast which Operating Systems are supported by a #SQLServer version? Use this simple, #free #service -

[News] Next week I'm starting a new series of technical journal articles which will be featuring interesting solutions! #SQLServer #MVPBuzz

Retweeted @SQLArtBits:
Too busy to visit our website? No problem, here's our rss feed:  #SQLServer #Software #Tools

Retweeted SQLArtBits:
We are adding a special #benchmarking mode to In-Memory OLTP Simulator! More news soon!  #SQLServer #InMemoryOLTP

Retweeted SQLArtBits:
Make sure you check out our weekly #SQLServer tips for cool bits of #SQL knowledge!  #TipOfTheWeek #SQLArtBits

Retweeted @SQLArtBits:
All brand new Snippets Generator v3.1 to be released next Wed. Sep 21 2016! Check often:  #SQLServer #SQL #SQLArtBits

Retweeted @SQLArtBits:
Subscribe to our RSS feed and stay up to date with our latest news, releases and articles!  #sql #sqlserver #software

DBA Security Advisor now on Facebook!  #SQLServer #Security #Software #MVPBuzz

Easily test #SQLServer In-Memory OLTP with my software tool In-Memory OLTP Simulator!  #MVPBuzz

Another successful #CDNUG event took place! I hope you enjoyed my session on Entity Framework Code First! Thank you for attending! #MVPBuzz

Playing with Entity Framework Code First for my session in an upcoming event. A quite cool feature in the Data Access world! #MVPBuzz

Preparing my session for the upcoming #CDNUG event titled: Entity Framework: Lets Code First! #MVPBuzz #DotNet #SQLServer #DataAccess

Running hundreds of simulations against Hekaton via In-Memory OLTP Simulator!  #MVPBuzz #SQLServer

You really have to play with indexes against your workload in order to conclude on the best index type for your specific case. #SQLServer


Enjoy some real discounts by @SQLArtBits!
Who would like to see some real #holiday #discounts? Visit our store! 70% off on all our products!  #SQLServer

Working on a new article about #SQLServer AlwaysOn Availability Groups. To be published tomorrow on my blog  #MVPBuzz

Proudly announcing: "The SQL Server and .NET Hub" - Your central hub for #SQLServer and .NET knowledge!  #MVPBuzz

Next week we will be publishing a brand new article about #SQLServer #Security - Check our blog often!  #SQLArtBits

[News] I'm in the process of transforming my main blog into something much much bigger! Stay tuned #SQLFamily! #SQLServer #MVPBuzz

In-Memory OLTP Simulator 1.1 (Build_20161120_1) on Softpedia - Read the review - Try it out!  #SQLArtBits #SQLServer

The new build of In-Memory OLTP Simulator, made available today,fully supports #SQLServer 2016 with SP1!  #SQLArtBits

Now everybody can experience the full power of #SQLServer with Server 2016 SP1! Many Enterprise features available!

This is cool!  #SQLServer

Coming up next week on Nov 1, with a cool new feature: Benchmark Mode #SQLServer #Hekaton #Software
SQLArtBits @SQLArtBits
[Announcement] In-Memory OLTP Simulator v1.1 will be released on Tuesday, Nov 1, 2016! Stay tuned!  #SQLServer

DBA Security Advisor v1.0 Build 20161020_1 is Now Out! #Secure your #SQLServer instances today!  #security

So excited about this! #SQLServer #MVPBuzz
[News] Tomorrow, we are releasing a new build of DBA Security Advisor v1.0!  #SQLServer #Security #Tools #SQLArtBits

Deep diving into #SQLServer #security. Expect a series of some really interesting articles on security & hardening best practices! #MVPBuzz

Retweeted @SQLArtBits:
[News] A new build of our Enterprise product DBA Security Advisor is scheduled for release in the coming days. Stay tuned! #SQLServer #Tools

Do you like high-quality #SQLServer tools that just work? Do you enjoy simplicity? Check out @SQLArtBits:  #Software

Dynamic T-SQL Generator v2.0 is now out! It's #free and full of new features! Get it today!  #SQLServer #MVPBuzz

[News] Dynamic T-SQL Generator v2.0 will be released on Wed October 5 2016! Stay tuned!  #SQLServer #Free #Software

Coming up in mid-October: Dynamic T-SQL Generator v2.0 and it's full of brand new features! Check often:  #SQLServer

Snippets Generator v3.1 is now out and it's free! Get it at:  #MVPBuzz #SQLServer #SQL #Tools

Artemiou Data Tools is now @SQLArtBits! Some really cool stuff is coming up next week!  #mvpbuzz #community

A new build of DBA Security Advisor is available. See what's new at:  #SQLServer #Security #MVPBuzz

Securing your #SQLServer instances has just been made easier: Check out my latest tool named 'DBA Security Advisor':

DBA Security Advisor is Now Out! Easily assess your #SQLServer instances against potential #security risks!  #MVPBuzz

Working on my #free tool 'Snippets Generator' which supports #SQLServer 2012 or later. A major upgrade to be released this week! #MVPBuzz

I am a Microsoft Data Platform MVP for the 8th Year!  #MVPBuzz #Community #SQLServer

Working on a new technical article on #SQLServer #InMemoryOLTP. More news about it next week! #Hekaton #MVPBuzz

New section on In-Memory OLTP Simulator's website: Publications  #MVPBuzz #SQLServer #Hekaton

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (

Tuesday, December 20, 2016

How to Establish a Simple Connection from a C# Program to SQL Server

This is a basic example on how to establish a connection to SQL Server from a C# program.

Use the System.Data.SqlClient namespace in the top section of your code as below:
using System.Data.SqlClient;

Set the connection string - Trusted connection (Windows Authentication). In the below example I'm using "master" as the default database. You can replace "master" with the default database you wish to set:

string connString = @"Server=INSTANCE_NAME;Database=master;Trusted_Connection = True;";

In case you want to set a connection string with standard security (username/password - not recommended) then you can set the connection string based on the below example:

string connString = @"Server=INSTANCE_NAME;Database=master;User ID=USERNAME;Password=PASSWORD";

With the "using" keyword, .NET automatically manages the task of closing the connection to the database. This an easy way to avoid "connection leak" issues. Here's the code example:

 using (SqlConnection conn = new SqlConnection(connString))
                //set the command to execute against SQL Server (this is where you set your query)
                string query = @"SELECT [fileid],[filename] FROM sysfiles";
                SqlCommand cmd = new SqlCommand(query, conn);

                //open connection

                //the actual command execution
                SqlDataReader dr = cmd.ExecuteReader();

                //if reader has any rows retrieve them
                if (dr.HasRows)
                    while (dr.Read())
                        //handle the retrieved record (i.e. display it)
                        MessageBox.Show(dr.GetInt16(0) + " - " + dr.GetString(1));
                    MessageBox.Show("No data found.");

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (

Featured Documents

SQL Server Development

View more

SQL Server Administration

View more

SQL Server Troubleshooting

View more


View more

SQL Server Performance

View more

SQL Server Programmability

View more

SQL Server Security

View more