How to Find a Database’s ANSI_NULLS Setting in SQL Server (T-SQL)

Posted on September 25, 2020September 25, 2020 by Ian

SQL Server has an ANSI_NULLS setting that determines how NULL values are evaluated when compared to another value with the Equals (=) and Not Equal To (<>) comparison operators.

While it’s true that you can change the ANSI_NULLS setting at the session level (using SET ANSI_NULLS), each database also has its own ANSI_NULLS setting.

You can check your database to see whether its ANSI_NULLS setting is ON or OFF.

To do this with T-SQL, you can either use the sys.databases catalog view or the DATABASEPROPERTYEX() function.

Continue reading →

Check the Status of the Database Mail Queues in SQL Server (T-SQL)

Posted on September 23, 2020September 23, 2020 by Ian

In SQL Server, you can use the sysmail_help_status_sp stored procedure on the msdb database to check the status of the Database Mail queues.

Continue reading →

Return a List of Emails Sent from SQL Server Database Mail (T-SQL)

Posted on September 22, 2020September 22, 2020 by Ian

If you use Database Mail in SQL Server, you can use the sysmail_sentitems view to return a list of all emails that Database Mail has sent.

Continue reading →

Check the Status of All Database Mail Messages in SQL Server (T-SQL)

Posted on September 22, 2020September 22, 2020 by Ian

If you use Database Mail in SQL Server, you can use the sysmail_allitems view to check the status of all emails that Database Mail has processed.

Continue reading →

Find Out Why an Email Failed to Send in SQL Server (T-SQL)

Posted on September 21, 2020September 21, 2020 by Ian

If you’re trying to send email using Database Mail in SQL Server, but it fails to send, you can check the sysmail_event_log view to see why it failed.

The sysmail_event_log view returns one row for each Windows or SQL Server message returned by the Database Mail system. By “message”, I don’t mean the actual mail message. I mean a message such as the error message that explains why the mail failed.

You can also use the sysmail_configure_sp stored procedure to determine what types of messages are logged.

Continue reading →

Check for Failed Email in SQL Server (T-SQL)

Posted on September 20, 2020September 22, 2020 by Ian

If you’re trying to send mail with SQL Server but it’s not being delivered, try checking the sysmail_faileditems view.

Continue reading →

Check Unsent Mail in SQL Server (T-SQL)

Posted on September 19, 2020September 22, 2020 by Ian

When sending emails from SQL Server, you can check for any unsent mail with the sysmail_unsentitems view.

Continue reading →

SQL Server Error 109: There are more columns in the INSERT statement than values specified in the VALUES clause

Posted on September 18, 2020September 18, 2020 by Ian

This is a commonly encountered error in SQL Server when inserting data into a table. The full error goes like this:

Msg 109, Level 15, State 1, Line 1
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

This happens when you specify more columns in the INSERT statement than the number of values that you’re trying to insert with the VALUES clause.

This will occur if you accidentally omit one or more values from the VALUES clause.

You’d get a similar (but technically different) error if you tried to do the opposite – specify fewer columns in the INSERT statement than you try to insert.

Continue reading →