Tag: mssql

Create a Partitioned Table in SQL Server (T-SQL)

Posted on by Ian

SQL Server supports partitioned tables and indexes. When a partitioned table or index is partitioned, its data is divided into units that can be spread across more than one filegroup.

Therefore, to create a partitioned table in SQL Server, you first need to create the filegroup/s that will hold each partition. You also need to create a partition function and a partition scheme.

So it goes like this:

  1. Create filegroup/s
  2. Create a partition function
  3. Create a partition scheme
  4. Create the partitioned table

Below is an example of using these steps to create a table with four partitions.

Continue reading →

SQL Server Error 213: Column name or number of supplied values does not match table definition.

Posted on by Ian

Error message 213 is a common error that happens when you try to insert values into a table without explicitly specifying the column names.

The error looks like this:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

It occurs when you specify the wrong number of values for that table. In other words, the number of values you provide doesn’t match the number of columns in the table.

Continue reading →

Change the Separator to a Comma when Emailing Query Results in SQL Server (T-SQL)

Posted on by Ian

If you’ve ever used SQL Server’s Database Mail to email the results of a query, you might have noticed that the results are separated by a space by default.

This is fine if you want a space-separated result set, but what if you want it to be comma-separated?

Fortunately you can use the @query_result_separator argument to do just that. You can use this argument to specify any separator (as long as it’s a char(1)).

Continue reading →

Remove Padding When Sending Query Results in an Email from SQL Server (T-SQL)

Posted on by Ian

When you use the sp_send_dbmail stored procedure to send emails from SQL Server, you have the option of adding query results to the email.

When you do this, you might find that unwanted padding has been added to some columns. Fortunately, you can eliminate this padding with the @query_result_no_padding argument.

Continue reading →

“Query not allowed in Waitfor” Error 101 in SQL Server

Posted on by Ian

If you’re using the WAITFOR statement in SQL Server, and you get the following error, it’s probably because you’re providing a query as its argument.

Msg 101, Level 15, State 1, Line 1
Query not allowed in Waitfor.

The WAITFOR statement doesn’t accept queries for its “wait for” period. It only accepts a specific time or an interval.

Actually, it does accept RECEIVE statements, but this is only applicable to Service Broker messages, so if you’re not using Service Broker messages, the above error is quite self-explanatory.

Continue reading →

What is STATISTICS XML in SQL Server?

Posted on by Ian

In SQL Server, you can use the SET STATISTICS XML statement to generate detailed information about how a T-SQL statement was executed.

This information is provided in the form of a well formed XML document.

Once SET STATISTICS XML is set to ON, each subsequent statement is reflected in the output by a single XML document.

Each XML document contains the text of the statement, followed by the details of the execution steps.

The output shows run-time information such as the costs, accessed indexes, and types of operations performed, join order, the number of times a physical operation is performed, the number of rows each physical operator produced, and more.

Continue reading →

Create a Default Public Profile for Database Mail in SQL Server (T-SQL)

Posted on by Ian

When you use the sp_send_dbmail stored procedure to send emails in SQL Server, you would typically include the @profile_name argument to specify which profile to use.

However, if you omit this argument, sp_send_dbmail will use the default private profile for the current user. If the user does not have a default private profile, sp_send_dbmail will use the default public profile for the msdb database.

If none of these are configured, you’ll get the following error:

Continue reading →

How to Change the Default Database Mail Profile for a User in SQL Server (T-SQL)

Posted on by Ian

If you’ve already created an association between a principal and a Database Mail profile, but you now want to change whether the profile is the default profile for that user, you can do that with the sysmail_update_principalprofile_sp stored procedure.

When doing this, you need to provide the name or ID of both the user and the profile. This is because you need to specify exactly which profile/principal association you want to update.

Continue reading →