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)).
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.
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.
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 readingWhen 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 readingIf 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 readingIf you’ve already added a Database Mail account to a profile, but you now want to change the sequence number, you can do that with the sysmail_update_profileaccount_sp stored procedure.
When doing this, you need to provide the name or ID of both the account and the profile. This is because you need to specify exactly which profile/account association you want to update.
Continue readingIn SQL Server, you can use the SET SHOWPLAN_XML statement to return detailed information about how a T-SQL statement will be executed, in the form of a well-defined XML document.
It’s similar to SHOWPLAN_ALL, except that SHOWPLAN_ALL returns its data set of rows that form a hierarchical tree.
You can set SHOWPLAN_XML to either ON or OFF.
When SHOWPLAN_XML is ON, all subsequent T-SQL statements are not executed. Instead SQL Server returns execution information for the statement (without executing it).
If you get an error telling you that the “The multi-part identifier could not be bound.”, it usually means that you’re prefixing one or more columns with either a table that isn’t included in your query, or an alias that you haven’t actually assigned to a table.
Fortunately, the error message shows you which multi-part identifier is causing the problem.
Continue readingIf you use Azure Data Studio for your database admin tasks, you might be wondering how you can view the execution plan for your queries?
Fortunately, this is easy!
You have the option of viewing: